Archive for August, 2012

Problem

We have some SQL Server 2000 instances where Identity Checks and Index Reorganisations were failing. The error stated that Quoted Identifier was set to OFF.

Solution

Open the job and add this -S serverName\Instance Name before -PlanId.

Then at the end add –SupportComputedColumn

Note

If the maintenance plan itself is changed then the job definition will change and you’ll need to re-fix them.

This solution comes from an MS article which you can read in full here:

http://support.microsoft.com/kb/902388

Advertisements

This happened on a SQL 2000 system. I wanted to change the autogrowth on a file from 10% to a MB setting. I did this through the GUI and got an error saying the system couldn’t find the file I was trying to change.

I then tried to change it using ALTER Database. It still said there was no such file.

I right-clicked on the DB and checked the files again. Then I had a look in dbo.sys.files and could see it. I went through windows Explorer and took a look at the file. It was there and in the place where SQL was telling me it was – yet it still insisted it wasn’t there.

Solution

I ran this script:

SELECT *
FROM master..sysaltfiles
WHERE dbid= DB_ID();

This time the file had a different logical name. It was actually a better name than the one sysfiles was claiming. So I ran a script to change th efile’s name to the one sysAltFiles had:

ALTER Database DBName
MODIFY FILE (NAME = ‘DBName_data’, NEWNAME = ‘ DBName _Data’);

Now I was able to get in and change the autogrow setting.