Backup History Performance

I recently has cause to cleanup the backup history on a SQL 2000 box. Not an issue, standard DBA fare really. SQL 2000 never had history cleanup options in the maintainence plans. Therefore we have to create TSQL jobs to remove backup history.

Given there was several years of backup history I started to clear backup history using a nibble delete approach. This proved to be slower than I anticipated so I started investigating further.

The quickest option would have been to directly modify the system tables, truncating them. Whilst constraints prevent this, there are solutions out there that tell you in which order to clear down the underlying tables. Pradeep Adiga is one of many to blog on pruning backup history and the fact that only 4 indexes exist on the 8 backup tables in MSDB.

Of all the scripts for additional MSDB indexes, Geoff Hiten provides the most complete solution in his post MSDB Performance Tuning. A script for MSDB indexes is located there which greatly improves the time to perform housekeeping on the backup tables.

I should include it in my build scripts really, and roll the indexes out as standard.

TSQL : Updating jobs that originated from an MSX server

If you try to update a SQL Agent Job or Maintainence plan that was set up using an MSX server (Master/Target environment) you get this error.

Error 14274: Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server.

If an MSX server has been decommissioned, renamed or is unrecoverable you have a genuine need to override this message. Providing you have permissions on the local server, you can update the entries in msdb directly.

This query updates all jobs, making their 'owner' (originating_server). the local server.

UPDATE msdb.dbo.sysjobs
SET originating_server = CONVERT(nvarchar, SERVERPROPERTY('servername'))
WHERE originating_server <> CONVERT(nvarchar, SERVERPROPERTY('servername'))

Once run, you can update the jobs once more.

ref : MSX Error

DBCC UPDATEUSAGE

Had a DBCC Error on a live system today on a scheduled DBCC check.

(NB; Post content is anonymized)

Executing the query "DBCC CHECKDB WITH NO_INFOMSGS

" failed with the following error: "The In-row data RSVD page count for object "ContactDetail", index ID 0, partition ID 130617058000896, alloc unit ID 130617058000896 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE.

CHECKDB found 0 allocation errors and 1 consistency errors in table 'ContactDetail' (object ID 1993058136).

CHECKDB found 0 allocation errors and 1 consistency errors in database 'LiveData'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

The problem occurs when there are inconsistencies between the data and what is reported in sysindexes for a clustered index (sysindexes holds rows, used, reserved, and dpages columns).

The error message gave us names for the database and table, the parameters for DBCCUPDATEUSAGE...

DBCC UPDATEUSAGE('LiveData','ContactDetail')

DBCC UPDATEUSAGE: counts updated for table 'ContactDetail' (index 'ContactDetail', partition 1):

RSVD pages (In-row Data): changed from (-501) to (25) pages.

DBCC UPDATEUSAGE: counts updated for table 'ContactDetail' (index 'IDX_ContactDetail', partition 1):

RSVD pages (In-row Data): changed from (16) to (17) pages.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Problem solved!

Reference -
MSDN : DBCC UPDATEUSAGE

Update! You can run it on the whole database too! -  DBCC UPDATEUSAGE('DatabaseName')

Audit Scripts : When were SQL Server & Windows Installed?

What version of SQL Server are we running ?

SELECT @@version

Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) Mar 29 2009 10:11:52 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.0 (Build 6002: Service Pack 2) 
 
When was SQL Server installed ?

SELECT  createdate as InstallDate 
FROM    sys.syslogins 
where   sid = 0x010100000000000512000000 

When was Windows installed ?

Open a command prompt, and type -

systeminfo | find /i "install date"

Links :
How to check when SQL Server was installed
How to check when Windows was installed

CXPACKET delays & the MAXDOP query hint

Running a large aggregate query today , Activity Monitor filled with suspended sessions with a WAITTYPE of CXPACKET.

CXPACKET means parallelism issues i.e the load has been shared between processors/cores and they're waiting on each other to finish.

The query in question takes a results set of 500,000 rows and aggregates it by four grouping columns into a smaller set of 46,000 records.

To solve this I added the query hint OPTION (MAXDOP n) to the end of a query (where n is number of processors).
Trying the query on it's own generated the CXPACKET wait types, as did any value of MAXDOP over 1.
Specifiying MAXDOP 1 let the query finish in 3 minutes without interuption.

SELECT columnlist...
FROM table1
INNER JOIN table2 ON table1.fk = table2.pk
WHERE column1 = criteria1
AND column2 = criteria2
GROUP BY
[group by column list]
ORDER BY
[order by column list]
OPTION (MAXDOP 1)

NB : MAXDOP is NOT a magic setting to be applied everywhere. Most queries will happily generate a parallel execution plan. Only use this to reduce the number of cores used if you have a query that will not scale or deliberately want to prevent an operation taking resources on the server.

Links :
Server level Max Degree of Parallelism setting
MSDN : Query Hints

Find SQL Agent Job Steps that do / don't generate an output file

Find SQL Agent Job Steps that generate an output file for debugging -

select 
  @@servername
, name
, step_name
, output_file_name
from msdb.dbo.sysjobs j
inner join msdb.dbo.sysjobsteps s
on j.job_id = s.job_id
where output_file_name is not null

Find SQL Agent Job Steps without output files -

select 
  @@servername
, name
, step_name
, output_file_name
from msdb.dbo.sysjobs j
inner join msdb.dbo.sysjobsteps s
on j.job_id = s.job_id
where output_file_name is null

TSQL : Remove a user from all databases

Drop User from all databases on a server -

EXEC sp_Msforeachdb "USE [?]; IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'USERNAME') DROP USER [USERNAME]"
GO

Drop the login -

IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'USERNAME') DROP LOGIN [USERNAME]
GO

Manual Uninstall of SQL 2005

I managed to get a corrupt SQL 2005 which in turn prevented the install of SQL 2008...


A useful post, that helped me out today >

http://www.gilham.org/Blog/Lists/Posts/Post.aspx?List=aab85845-88d2-4091-8088-a6bbce0a4304&ID=190

Automatic USB Stick Backup

A little something i've been meaning to do for a while, automatically backing up my USB stick when i plug it in.

The obvious solution is to create an autorun.inf file on the stick to run a backup script from the device. Autorun is not always available however and I want control over where my data (for the record, encrypted by Truecrypt) is copied.

I have found 'USB Detect and Launch' by Matt Collinge which once installed allows you to run scripts when devices are plugged in, allowing different actions depending on the volume label.
The software is free for personal use or $40 for business use.

1 ) Download & Install tool from >

http://mattcollinge.wordpress.com/software/usb-detect-and-launch/

2 ) I created a directory for my backups >

D:\!USB_BACKUP\R5D4

3 ) I created D:\!\backup-r5d4.cmd (a 1 line backup command) as >

XCOPY %1\*.* D:\!USB_BACKUP\R5D4 /e /i /h /y /d

Usb Detect & Launch Configuration >

4 ) In 'Main Settings' I created one entry.
This runs my backup command whenever it sees a usb stick with a label of R5D4 >

label:R5D4 exec:d:\!\backup-r5d4.cmd %1

5) In 'Main Settings' I changed the poll interval to 60 seconds.


Results :
Bingo! Plugged in my USB drive and up popped the command window which proceeded to copy my files.

Database Loop - WITHOUT CURSOR

SET NOCOUNT ON

DECLARE  @CurrentRow INT
DECLARE  @TotalRows INT

DECLARE  @DatabaseName NVARCHAR(255)                   
SELECT @CurrentRow = 1
               
DECLARE  @Databases  TABLE(
 UNIQUEROWID  INT   IDENTITY ( 1,1 )   PRIMARY KEY   NOT NULL
,DATABASENAME NVARCHAR(255)
)
   
INSERT INTO @Databases (DATABASENAME)
SELECT NAME
FROM SYS.DATABASES
WHERE DATABASE_ID > 4
               
SELECT @TotalRows = COUNT(* )
FROM @Databases
 
WHILE @CurrentRow <= @TotalRows  
BEGIN    
 SELECT @DatabaseName = DATABASENAME      
 FROM @Databases     
 WHERE UNIQUEROWID = @CurrentRow         
 PRINT @DatabaseName         
 SELECT @CurrentRow = @CurrentRow + 1  
END 

Database Loop - Loop all user databases with a cursor

-- Cursors are bad kids! Don't use them ...
DECLARE @DatabaseName VARCHAR(200)
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM sys.databases where database_id > 4
OPEN DatabaseCursor
FETCH NEXT FROM DatabaseCursor INTO @DatabaseName

WHILE @@FETCH_STATUS = 0
BEGIN

PRINT @DatabaseName
-- Do other stuff to databases here,
-- e.g DBCC CHECKDB(@Variable)
FETCH NEXT FROM DatabaseCursor INTO @DatabaseName
END

CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor

Lose a bloaty Transaction Log file

Shrinking Log files

-- find logical name of log file
SELECT * FROM sys.sysfiles

-- Change recovery model to SIMPLE.
ALTER DATABASE DATABASE_NAME SET RECOVERY SIMPLE

-- Shrink truncated log file to 1 MB.
DBCC SHRINKFILE (data_log, 1);
GO

-- Change recovery model to FULL.
ALTER DATABASE DATABASE_NAME SET RECOVERY FULL

!!! Take FULL backup as your chain of Transaction log backups will now be broken by the RECOVERY mode change.

SQL Sysadmin : Recycle SQL Server Error Logs

Schedule this as appropriate (weekly?) so that SQL logs do not become too cumbersome (taking ages to load in management studio).

Database : master

Command :

EXEC sp_cycle_errorlog
go

SQL Sysadmin : Clear Backup History (nibble delete)

It is best practice to remove old backup history records from MSDB. Else they will eventually cause MSDB to bloat and Management Studio to become unresponsive when dealing with BACKUPs and RESTOREs.

Note : Only do this when required backups are safely archived away.

Last year I blogged about this and included a script to trim backup history back to a month's data.
SQL Solace : Removing Database Backup History

If housekeeping has not been performed on backup history then many thousands of backup records may exist. For example ;
Say 47 log backups are taken each day, in addition to 1 full backup.
This happens for 10 databases, every day for 5 years. The number of log records is therefore -
48 backup records * 10 databases * 365 days * 5 years = 876000 records

If you find yourself in this situation, the following script will help.
It uses the nibble delete principle and cleans up backup history 1 day at a time, starting with the oldest record.

USE MSDB
GO
DECLARE @OldestBackupDate DATETIME
DECLARE @DaysToLeave INT
DECLARE @DaysToDeleteAtOnce INT
DECLARE @DeleteDate DATETIME

DECLARE @Counter INT
DECLARE @CounterText VARCHAR(30)

SELECT  @OldestBackupDate = MIN(backup_start_date) FROM msdb..backupset  
SELECT  @OldestBackupDate
SET @DaysToLeave = 30
SET @DaysToDeleteAtOnce = 1

SELECT @Counter = DATEDIFF(DAY,@OldestBackupDate,GETDATE())

WHILE @Counter >= @DaysToLeave  
BEGIN   
 SET @CounterText = CONVERT(VARCHAR(30),DATEADD(DAY, -@Counter,GETDATE()),21) 
 SELECT @DeleteDate = CONVERT(VARCHAR(30),DATEADD(DAY, -@Counter,GETDATE()),21) 
 RAISERROR (@CounterText , 10, 1) WITH NOWAIT   
 EXEC sp_delete_backuphistory @DeleteDate ;
 SELECT @Counter = @Counter - @DaysToDeleteAtOnce  
END 

It reports progress to screen, like this -

2006-06-17 16:27:59.970

Backup history older than Jun 17 2006 4:27PM has been deleted.

2006-06-18 16:28:42.067

Backup history older than Jun 18 2006 4:28PM has been deleted.

2006-06-19 16:30:08.853

Backup history older than Jun 19 2006 4:30PM has been deleted.

2006-06-20 16:31:32.653

Bad Autogrowth

The default Autogrowth for a new database is 'By 1 MB, unrestricted growth'

Imagine this default is still set on a production server, and you import a 50MB file when the datafile is at capacity.
The result is that 50 occurences of 1MB autogrow operations occur.
This is costly in terms of performance.

Now imagine multiple databases, all growing at varying rates (now that sounds like EVERY server doesn't it?)
As each of these datafiles 'expand' they take more disk space, grabbing the next available area on the disk. In this way, fragmentation of database files occurs.
As time goes on, simple database operations will rely more heavily on the disk as the server fetches data from logical files that are physically fragmented i.e. split.

Morals of this story...

1) Manually set data file sizes, allowing for expansion.
2) Review Disk/File fragmentation and perform disk maintenance
3) Do 1) & 2) regularly (stick reminders in your outlook!)

Schedule Sql Server Restart

Job Step for Restarting Server >


(schedule as required)