Archive for the ‘Maintenance’ Category

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

I separate System database backups and User database backups – although the code is essentially the same.

On Some servers extra databases are added to the not in list – update statistics takes a long time to run on a large database so I will run that separately once a week.

These steps are run nightly.

Each Step is step to progress even if it fails – I don’t want to stop all backups just because one db failed something. I have emails sent to me and I also read the job history log each working day so I pick up these failures.

Step 1: Shrink log file if recovery is simple
This only shrinks the log if the database is in simple recovery mode. You shouldn’t really have to do this but I have had some problems which this cleared up for me.

DECLARE @dbname sysname, @cmd varchar(1000);
DECLARE db_recovery_cursor CURSOR FOR
SELECT [name]
from sys.databases
WHERE [name] not in(‘master’,’tempdb’,’model’,’distribution’,’msdb’)
AND is_read_only = 0
AND recovery_model_desc = ‘Simple’;
OPEN db_recovery_cursor;
FETCH NEXT FROM db_recovery_cursor INTO @dbname;
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @databaseId as int;
SELECT @databaseId = database_id
FROM sys.databases
WHERE [name] = @dbname;

DECLARE @LogName as varchar(250);
SELECT @LogName = name
FROM sys.master_files
Where type_Desc = ‘log’
AND database_id = @databaseId;

DECLARE @sql as Varchar(2000);
SET @sql = ‘Use [‘ + @dbName + ‘];’;
SET @sql = @sql + ‘Checkpoint; ‘;
SET @sql = @sql + ‘DBCC ShrinkFile([‘+ @LogName + ‘],30);’;

EXEC(@sql);
FETCH NEXT FROM db_recovery_cursor INTO @dbname;
END;
CLOSE db_recovery_cursor;
DEALLOCATE db_recovery_cursor;

Step 2: Update Usage and Checkpoint and turn off autoshrink
Update usage helps fix problems in space reporting on the database.
Some of our databases were created with auto shrink on – a big no no!

USE Master;
DECLARE @dbName varchar(255);
DECLARE @SQL varchar(255);
DECLARE dbCursor CURSOR Local Fast_Forward FOR
select [name]
From master.sys.databases
WHERE Name not in (‘master’,’msdb’,’tempdb’,’model’,’Distribution’,’Resource’)
And is_read_only = 0
And state_desc = ‘ONLINE’
Order by [name];
OPEN dbCursor;
FETCH NEXT FROM dbCursor INTO @dbName;
WHILE @@FETCH_STATUS = 0
BEGIN
Set @sql = ‘dbcc updateusage ([‘ + @dbName + ‘]) WITH NO_INFOMSGS;’;
Exec (@sql);
Set @sql = ‘Use ‘ + QuoteName(@dbName) + ‘; Checkpoint;’ ;
Exec (@sql);
SET @sql = ‘EXEC sp_dboption ”’ + @dbname + ”’, ”autoshrink”, ”False”;’;
EXEC(@sql);
Use Master;
FETCH NEXT FROM dbCursor INTO @dbName;
END;
CLOSE dbCursor;
DEALLOCATE dbCursor;

Step 3: Integrity Check
Here you may not want to run this against every database every night but if you can you should. I use the Data_purity option as we have a lot of databases that are getting imported from sql 2000

USE Master;
DECLARE @dbName varchar(255);
DECLARE @SQL varchar(255);
DECLARE dbCursor CURSOR Local Fast_Forward FOR
select [name]
From master.sys.databases
WHERE Name not in (‘master’,’msdb’,’tempdb’,’model’,’Distribution’,’Resource’)
And is_read_only = 0
And state_desc = ‘ONLINE’
Order by [name];
OPEN dbCursor;
FETCH NEXT FROM dbCursor INTO @dbName;
WHILE @@FETCH_STATUS = 0
BEGIN
Set @sql = ‘dbcc CHECKDB ([‘ + @dbName + ‘]) WITH NO_INFOMSGS , DATA_PURITY ;’;
Exec (@sql);
FETCH NEXT FROM dbCursor INTO @dbName;
END;
CLOSE dbCursor;
DEALLOCATE dbCursor;

Step 4: Update Stats
Sp_updatestats will only update the stats SQL thinks needs updating. Update statistics will up date stats whether they need it or not. Updating stats will cause stored procs to recompile – which is ok if the stats were out of date but not if they werern’t.

USE Master;
DECLARE @dbName varchar(255);
DECLARE dbCursor CURSOR Local Fast_Forward FOR
select [name] From master.sys.databases
WHERE Name not in (‘master’,’msdb’,’tempdb’,’model’,’Distribution’,’Resource’)
AND is_read_only = 0
AND state_desc = ‘ONLINE’
Order by [name];
OPEN dbCursor;
FETCH NEXT FROM dbCursor INTO @dbName;
WHILE @@FETCH_STATUS = 0
BEGIN
Exec (‘Use [‘ + @dbName + ‘] EXEC SP_updatestats ‘);
FETCH NEXT FROM dbCursor INTO @dbName;
END;
CLOSE dbCursor;
DEALLOCATE dbCursor;

Step 5: Backup and remove old backups
I delete files older than 96 hours – more accurate than x days. On some servers where space is more of a constraint this time is reduced. On out live servers backups are also copied to tape but this process runs outside of sql. Backups will only be removed if there is a corresponding live database. This means if a database is dropped backups will stay on the server until separately dropped – I like having this in case someone wants to restore it. I’ll normally manually delete them after 6 months or so.

If a database is over a certain size I stripe the backup to 4 files.

USE Master;

Declare @delDate as VARCHAR(50);
Set @delDate = CAST(DATEADD(hh, -96, GETDATE()) AS VARCHAR(50));

DECLARE @rootDir as Varchar(255);
SET @rootDir = ‘M:\Backups\User\’; /*Enter path to user backups here*/

DECLARE @dbName varchar(255);
DECLARE @sqlCreateDir varchar(255);

Declare @y as char(4);
Set @y = Cast(Year(getDate()) as char(4));
Declare @m as char(2);
Set @m = Right(‘0’ + Cast(Month(getDate()) as varchar(2)),2);
Declare @d as char(2);
Set @d = Right(‘0’ + Cast(Day(getDate()) as varchar(2)),2);
Declare @date as char(10);
Set @date = @y + ‘_’ + @m + ‘_’ + @d;

Declare @h as char(2);
Set @h = Right(‘0’ + Cast(DatePart(hh,getDate()) as varchar(2)),2);
Declare @mi as char(2);
Set @mi = Right(‘0’ + Cast(DatePart(mi,getDate()) as varchar(2)),2);
Declare @time as char(5);
Set @time = @h + ‘_’ + @mi;

DECLARE dbCursor CURSOR Local Fast_Forward FOR
select [name] From master.sys.databases
WHERE state_desc = ‘ONLINE’
AND Name not in (‘master’,’msdb’,’tempdb’,’model’,’Distribution’,’Resource’)
Order by [name];
OPEN dbCursor;
FETCH NEXT FROM dbCursor INTO @dbName;
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @dirPath as varchar(250);
Set @dirPath = @rootDir + @dbName;

Set @sqlCreateDir = ‘master.dbo.xp_create_subdir N”’ + @dirPath + ””;
Exec (@sqlCreateDir);

EXECUTE master.dbo.xp_delete_file 0, @dirPath,N’bak’,@delDate;

DECLARE @size as numeric(25,0);

SELECT @size = b.backup_size
FROM master..sysdatabases d with (nolock)
left join msdb..backupset b with (nolock)
on d.name = b.database_name
AND b.backup_start_date = (select max(backup_start_date)
from msdb..backupset b2
where b.database_name = b2.database_name
and b2.type = ‘D’)
WHERE d.name = @dbName;

Declare @BakName as Varchar(250);
If @size < 2147483648BEGINSet @BakName = @dbName + '_' + @date + '_' + @time + '.bak';–Now run a backup commandDECLARE @BakCommand as varchar(1024);SET @BakCommand = 'BACKUP Database ' + QuoteName(@dbName);SET @BakCommand = @BakCommand + ' TO DISK=''' + @dirPath + '\' + @BakName + '''';SET @BakCommand = @BakCommand + ' WITH INIT, Checksum;';exec (@BakCommand);ENDELSEBEGINSet @BakName = @dbName + '_' + @date + '_' + @time;–Now run a backup commandSET @BakCommand = 'BACKUP Database ' + QuoteName(@dbName);SET @BakCommand = @BakCommand + ' TO DISK=''' + @dirPath + '\' + @BakName + 'a.bak''';SET @BakCommand = @BakCommand + ' ,DISK=''' + @dirPath + '\' + @BakName + 'b.bak''';SET @BakCommand = @BakCommand + ' WITH INIT, Checksum;';exec (@BakCommand);END;FETCH NEXT FROM dbCursor INTO @dbName; END;CLOSE dbCursor; DEALLOCATE dbCursor;

References:
Update Usage: http://msdn.microsoft.com/en-us/library/ms188414.aspx
Check Db with Data_purity: http://www.sqlskills.com/BLOGS/PAUL/post/CHECKDB-From-Every-Angle-How-to-tell-if-data-purity-checks-will-be-run.aspx

I don’t like the system generated maintenance plans so use this instead.

I normally go from one step to the next with total disregard as to it’s outcome. This is because I check job histories and logs each morning so if update stats or integrity checks failed I will know about it and fix it then. I also want my backups to run regardless – they stay on disk for 3 days but are also copied to tape so if a problem with integrity creeps in and isn’t noticed for > 3 days (I am on holiday) then if I can’t fix it I can use tape backups to repair almost as easily as disk backups. If this doesn’t suit you you could add an extra step which takes a backup but does not delete old backups – you just need the space for this if you can’t check for a while.

The most unsatisfying thing with this is detecting read only databases – it is easy in 2005 but haven’t found a satisfactory easy method for 2000.

I have a separate job for system dbs where NOT IN is replaced with IN. This runs at a different time of the evening

 

Step 1: Update Usage and Checkpoint

USE Master;

DECLARE @dbName varchar(255);

DECLARE @SQL varchar(255);

DECLARE dbCursor CURSOR Local Fast_Forward FOR

 select [name]

 From master..sysdatabases

 WHERE [Name] not in (‘master’,‘msdb’,‘tempdb’,‘model’,‘Distribution’,‘Resource’)

 Order by [name];

OPEN dbCursor

 FETCH NEXT FROM dbCursor INTO @dbName

WHILE @@FETCH_STATUS = 0

BEGIN

Set @sql = ‘dbcc updateusage ([‘ + @dbName + ‘]) WITH NO_INFOMSGS;’;

Exec (@sql);

Set @sql = ‘Use ‘ + QuoteName(@dbName) + ‘ Checkpoint;’;

Exec (@sql);

Use Master;

FETCH NEXT FROM dbCursor INTO @dbName;

END;

CLOSE dbCursor;

DEALLOCATE dbCursor;

 

Step 2: Integrity Check

USE Master;

DECLARE @dbName varchar(255);

DECLARE @SQL varchar(255);

DECLARE dbCursor CURSOR Local Fast_Forward FOR

select [name]

From master..sysdatabases

WHERE [Name] not in (‘master’,‘msdb’,‘tempdb’,‘model’,‘Distribution’,‘Resource’)

Order by [name];

OPEN dbCursor

FETCH NEXT FROM dbCursor INTO @dbName;

WHILE @@FETCH_STATUS = 0

BEGIN

Set @sql = ‘dbcc CHECKDB ([‘ + @dbName + ‘]) WITH NO_INFOMSGS;’;

Exec (@sql);

FETCH NEXT FROM dbCursor INTO @dbName;

END;

CLOSE dbCursor;

DEALLOCATE dbCursor;

 

Step 3: Update Stats

USE Master;

DECLARE @dbName varchar(255);

DECLARE dbCursor CURSOR Local Fast_Forward FOR

/* 1048 represents REadonly databases but it is no the only code that represents Readonly

you will need to check your dbs*/

select [name]

From master..sysdatabases

WHERE Name not in (‘master’,‘msdb’,‘tempdb’,‘model’,‘Distribution’,‘Resource’)

AND status <> 1048

Order by [name];

OPEN dbCursor

FETCH NEXT FROM dbCursor INTO @dbName

WHILE @@FETCH_STATUS = 0

BEGIN

Exec (‘Use [‘ + @dbName + ‘] EXEC sp_MSforeachtable ”UPDATE Statistics ? With Sample 50 Percent”’);

FETCH NEXT FROM dbCursor INTO @dbName;

END;

CLOSE dbCursor;

DEALLOCATE dbCursor;

 

Step 4; Backup databases

USE Master;

DECLARE @rootDir as Varchar(255);

SET @rootDir = ‘E:\Backup’; /*Enter path to user backups here*/

DECLARE @dbName varchar(255);

DECLARE dbCursor CURSOR Local Fast_Forward FOR

select [name] From master..sysdatabases

WHERE Name not in (‘master’,‘msdb’,‘tempdb’,‘model’,‘Distribution’,‘Resource’)

Order by [name];

OPEN dbCursor

FETCH NEXT FROM dbCursor INTO @dbName;

WHILE @@FETCH_STATUS = 0

BEGIN

DECLARE @BakCommand as varchar(1024)

SET @BakCommand = ‘master.dbo.xp_sqlmaint N”-S . -D ‘ + @dbName + ‘ -BkUpDB “‘+ @rootDir + ‘” -BkUpMedia DISK -CrBkSubDir -DelBkUps 3DAYS -BkExt “BAK””’;

–Print @BakCommand

EXEC (@BakCommand);

FETCH NEXT FROM dbCursor INTO @dbName;

END;

CLOSE dbCursor;

DEALLOCATE dbCursor;

http://lqqsql.blogspot.com/2009/09/sql-server-2000-maintenance-plan-non.html

This is really one reason why I don’t like the built in maintenance plans. Why can’t they simply ignore the databases which are in simple recovery mode? Why do they instead fill the error logs with message that they have tried to backup the transaction log of the master database??

I wanted to be able to choose which databases to exclude – and then only try to carry out a T log backup on a DB which has Full or Bulk-Logged recovery set.
Remember you can only start T Log backups after you have taken a full backup.

DECLARE @name VARCHAR(50); — database name  
DECLARE @fileName VARCHAR(250); — filename for backup
DECLARE @BakCommand Varchar(550);
DECLARE @rootDir Varchar(20);
DECLARE @fileDate Varchar(50);
/*@rootDir is the root directory where user db backups go*/
SET @rootDir = ‘F:\SQLBackups\user’;  

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)  
   + ‘_’  
   + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),’:’,”) ;

DECLARE db_cursor CURSOR Local Fast_Forward FOR  
SELECT [name]  
FROM master.dbo.sysdatabases  
WHERE [name] NOT IN (‘master’,’model’,’msdb’,’tempdb’)  
   AND DATABASEPROPERTYEX(name, ‘Recovery’) IN (‘FULL’,’BULK_LOGGED’) ;

OPEN db_cursor    
        FETCH NEXT FROM db_cursor INTO @name    
        WHILE @@FETCH_STATUS = 0    
        BEGIN
                /*A sub directory is created for each database if not already there
                Backups are kept for 3 days*/
                SET @BakCommand = ‘use [‘ + @name + ‘]; Checkpoint;’;
                EXEC (@BakCommand);
                use Master;
                SET @BakCommand = ‘master.dbo.xp_sqlmaint N”-S . -D ‘ + @name + ‘ -BkUpLog “‘+ @rootDir + ‘” -BkUpMedia DISK  -CrBkSubDir -DelBkUps 3DAYS -BkExt “TRN””’;
                –Print @BakCommand
                EXEC (@BakCommand);                
       
                FETCH NEXT FROM db_cursor INTO @name;    
        END;
CLOSE db_cursor;
DEALLOCATE db_cursor;