SQL 2005 : Database is in transition...Error 952

I detached a database in SQL 2005 today, doing so using sp_detach. 
The operation should have been pretty instant, but after a few minutes I realised something was up.


The database was stuck 'in transition', or so it appeared to Management Studio. I contemplated what to do. I wanted to avoid restarting the service and leaving the db in an inconsistent state, and hence googled the following...


Error 952 Database is in Transition


As it happens, the database isn't stuck at all, it's just Management Studio thinks it is.
The solution. RESTART MANAGEMENT STUDIO.   Argh!

SQL Sysadmin : Clear Job History (nibble delete)

This post is a minor change to my Clear Backup History (nibble delete) script to apply the same technique to Deleting Sql Agent Job history. If you find a server where no one has done this for a while (ever?) or where maintenance plans are missing, you'll need this script. Once again, nibble deleting history tables prevents large transactions that could cause server slowdowns. This works, 1 day at a time, starting with the oldest record. If you are wondering why I do this rather than looping sp_purge_jobhistory, that is because it only takes a date parameter on sql 2005+

-- Nibble Delete Job History

USE MSDB
GO

DECLARE @OldestJobHistoryDate DATETIME
DECLARE @DaysToLeave INT
DECLARE @DaysToDeleteAtOnce INT
DECLARE @DeleteDate DATETIME
DECLARE @Counter INT
DECLARE @CounterText VARCHAR(100)
DECLARE @datepart INT

SELECT @OldestJobHistoryDate = convert(datetime,rtrim(run_date)) 
FROM msdb..sysjobhistory
WHERE instance_id = (select MIN(instance_id) FROM msdb..sysjobhistory)

SELECT @OldestJobHistoryDate
SET @DaysToLeave = 30
SET @DaysToDeleteAtOnce = 1

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

WHILE @Counter >= @DaysToLeave  
BEGIN   
 SET @CounterText = CONVERT(VARCHAR(30),GETDATE(),21) + ' processing ' + CONVERT(VARCHAR(30),DATEADD(DAY, -@Counter,GETDATE()),21)
 SELECT @DeleteDate = CONVERT(VARCHAR(30),DATEADD(DAY, -@Counter,GETDATE()),21) 
 RAISERROR (@CounterText , 10, 1) WITH NOWAIT   
 SET @datepart = CONVERT(INT, CONVERT(VARCHAR, @DeleteDate, 112))
 DELETE FROM msdb.dbo.sysjobhistory WHERE (run_date < @datepart)
 SELECT @Counter = @Counter - @DaysToDeleteAtOnce  
END 
GO

Shrink Current Log file

Useful in lengthy deployment scripts in conjunction with CHECKPOINT commands

/*
Shrinks log file for current database
Useful for index deployment script
*/
DECLARE @logfilename NVARCHAR(50)
SELECT @logfilename = name FROM sysfiles WHERE fileid = 2
DBCC SHRINKFILE (@logfilename , 0, TRUNCATEONLY)

Windows 2000 : Support for disks over 128GB

Yes I know that Windows 2000 is ancient, rarely used nowadays etc but I've had cause to build a VM to perform some testing.
That's why I've come accross this long forgotten nugget, how to get Windows 2000 to support drives over 128GB in size!

It's a registry entry. Either enter it manually of paste the content below into a .reg file and dounle click the file.

REGEDIT4

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\atapi\Parameters]

"EnableBigLba"=dword:00000001

Reboot the box, and hey presto...

http://www.lazyllama.com/misc/bigdisk.html

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 : Correct Compatibility Levels

I'm always finding databases on sites that were not put into the correct compatibility mode when server migrations/upgrades occurred.
This script sorts them all out at once.

DECLARE @ServerVersion INT
SELECT @ServerVersion = 10 * (CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), CHARINDEX('.', CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), 1) - 1) AS INTEGER))

-- loop databases setting compatibility mode correctly
DECLARE GET_DATABASES CURSOR
READ_ONLY
FOR SELECT NAME FROM SYS.DATABASES WHERE COMPATIBILITY_LEVEL != CAST(@ServerVersion AS VARCHAR(10))
DECLARE @DATABASENAME NVARCHAR(255)
DECLARE @COUNTER INT
SET @COUNTER = 1
OPEN GET_DATABASES
FETCH NEXT FROM GET_DATABASES INTO @DATABASENAME
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
-- change database compatibility
EXECUTE sp_dbcmptlevel @DATABASENAME , @ServerVersion
PRINT  @DATABASENAME + ' changed'
SET @COUNTER = @COUNTER + 1
END
FETCH NEXT FROM GET_DATABASES INTO @DATABASENAME
END
CLOSE GET_DATABASES
DEALLOCATE GET_DATABASES

adapted from 'Database Compatibility Levels : How to change all at Once' to detect the server version

Using NOEXEC for conditional processing

NOEXEC allows you to do conditional processing even if you have GO statements splitting your script into batches.

DECLARE @runit INT
SET @runit =0

SET NOEXEC OFF

PRINT '1'

IF @runit = 0
 BEGIN
 PRINT  'Skipping Next Section'
 SET NOEXEC ON
 END
GO

PRINT '2'
GO
PRINT '3'
GO
PRINT '4'
GO

SET NOEXEC OFF
PRINT '5'
GO

Run the script twice, changing the value of @runit to 1 the second time. Cool huh?

MSDN : NOEXEC

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

TSQL : Development / UAT Server Prep

A simple script to loop all databases, setting them to SIMPLE recovery mode and SHRINKing any logs.

Very useful for restoring UAT / DEV databases from live environments.

sp_msforeachdb @command1 = '
 USE [?];
 IF DB_NAME() <> ''tempdb''
 BEGIN
  PRINT ''---''
  PRINT DB_NAME()
  PRINT ''---''
  DECLARE @databasename VARCHAR(1000)
  SET @databasename = DB_NAME()
  
  DECLARE @sqlcmd NVARCHAR(1000)
  SET @sqlcmd = ''ALTER DATABASE ['' + @databasename+ ''] SET RECOVERY SIMPLE ''
  EXECUTE (@sqlcmd)
  
  DECLARE @logfilename VARCHAR(1000)
  SELECT @logfilename = RTRIM(name) from sysfiles where fileid = 2
  SELECT @logfilename
  DBCC SHRINKFILE (@logfilename , 0, TRUNCATEONLY)
 END '

TSQL : SQL 2000 : List Primary Key Columns

SELECT
sysobjects.name AS TableName
,sysindexes.name AS PKName
,syscolumns.colid AS ColumnOrder
,index_col(object_name(sysindexes.id), sysindexes.indid,syscolumns.colid) AS ColumnName
FROM sysobjects 
INNER JOIN sysindexes
ON sysobjects.id = sysindexes.id 
INNER JOIN syscolumns
ON sysindexes.id = syscolumns.id
WHERE syscolumns.colid <= sysindexes.keycnt
AND sysindexes.indid = 1
--AND sysobjects.name = 'tablename'
ORDER BY sysobjects.name ,sysindexes.name