Wednesday 31 August 2011

Checking CHECKDB !

When reviewing some agent logs this morning it struck me that the maintenance plans had run a little quickly. The agent job hadn't failed, but the run duration seemed remarkably short (considering the database size). The plan concerned was one of those 'all maintenance in one step' jobs hence I couldn't quantify how the run duraton was comprised. Backups were present in their directory, so one of the other steps must have silently failed.

Running a server audit script picked up Integrity Checks as not having occurred. My audit script uses this piece of code to get the date.


CREATE TABLE #temp (        
       [ParentObject]    VARCHAR(255)
       , [Object]       VARCHAR(255)
       , Field          VARCHAR(255)
       , [Value]        VARCHAR(255) 
   ) 

INSERT INTO #temp EXECUTE ('DBCC DBINFO WITH TABLERESULTS')

SELECT DISTINCT Value AS DBCCDate FROM #temp WHERE Field = 'dbi_dbccLastKnownGood'

There are 2 caveats with this code
  1. It only works on SQL 2005+
  2. It needs DISTINCT in the select statement to run on SQL 2008 (where a bug means the date is reported twice).
So, having found that the DBCC checks had not occurred, i turned to google :/
It turns out the client were running SQL 2005 SP2, build 9.00.3042.
In this build there is a bug re; integrity checks failing in maintenance plans.

Top Tweets

Top 3 Tweets today! Linking these to read them later...

Jamie Thomson (@jamiet) tweets :
"Down with Primary Keys?" by David Portas. Always enjoy going back to this article.

Tony Rogerson (@tonyrogerson) tweets :
Database Design (Normalisation) rules poster

Joesph Sack (@josephsack) tweets :
Windows 8 to directly support ISO and VHD files

Monday 29 August 2011

Online index rebuilds : Unsupported data types

I was attempting to rebuild some indexes ONLINE (using SQL 2005 Enterprise) when I got this error.

Msg 2725, Level 16, State 2, Line 4 Online index operation cannot be performed for index 'PKCI_MyTable_SearchIndex' because the index contains column 'ReportData' of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max) or xml. For non-clustered index the column could be an include column of the index, for clustered index it could be any column of the table. In case of drop_existing the column could be part of new or old index. The operation must be performed offline.

The error tells us what the problem is, i.e. the inability to use certain data types when rebuilding indexes ONLINE. In my case, the offending column was a deprecated TEXT data type. Still, it reminded me to audit the database for such columns...

Finding deprecated data types-

SELECT
   sys_schemas.Name as [schema_name]
 , sys_tables.Name AS [table_name]
 , sys_columns.Name AS [column_name]
 , sys_types.Name AS [datatype_name]
FROM Sys.Tables sys_tables (nolock)
JOIN Sys.Schemas sys_schemas (nolock)
ON sys_schemas.Schema_Id = sys_tables.Schema_Id
JOIN Sys.Columns sys_columns (nolock)
ON sys_columns.Object_Id = sys_tables.Object_Id
JOIN Sys.Types sys_types (nolock)
ON sys_types.System_Type_Id = sys_columns.System_Type_Id
AND sys_types.Name IN ('text,'ntext','image')

See also Deprecated in SQL 2008 R2

Sunday 21 August 2011

Cleaning up MSDB

I've been working extensively with old SQL 2000 instances recently. The majority have been left to fend for themselves without DBA support. Whilst backups and index maintenance have for the most part been occuring, the msdb databases are bloated (and fragmented). This is due to some missing functionality in SQL 2000 , namely clearing down the history tables. This post summarises my previous ones on MSDB clearup.

1) Backup History

To determine the date of the oldest backup history record, run this -

SELECT TOP 1 backup_start_date FROM msdb.dbo.backupset WITH (NOLOCK) ORDER BY backup_set_id ASC

If there is significant history to remove, use the Ultimate Delete Backup History Script to remove it. To prevent it building up again in the future, regularly run (or schedule) -

DECLARE @BackupHistoryDeleteDate DATETIME
SET @BackupHistoryDeleteDate = DATEADD(m,-1,GETDATE())
EXEC msdb.dbo.sp_delete_backuphistory @BackupHistoryDeleteDate ;

2) Agent Job History

To determine the date of the oldest agent history record, use this -
DECLARE @agentstartdate DATETIME
SELECT @agentstartdate = CONVERT(datetime,CONVERT(VARCHAR(8),MIN(Run_date))) from msdb..sysjobhistory WITH (NOLOCK) OPTION (MAXDOP 1)

If there is significant history to remove, use the Clear Job History (Nibble Delete) Script to remove it.
To prevent it building up again in the future, regularly run (or schedule) -
For SQL 2000 -
DECLARE @JobHistoryDeleteDate DATETIME
SET @JobHistoryDeleteDate = DATEADD(m,-1,GETDATE())
DECLARE @datepart INT
SET @datepart = CONVERT(INT, CONVERT(VARCHAR, @JobHistoryDeleteDate, 112))
DELETE FROM msdb.dbo.sysjobhistory WHERE (run_date < @datepart)

For SQL 2005+ you can use sp_purge_jobhistory (it now takes a date parameter), hence regularly run -
DECLARE @JobHistoryDeleteDate DATETIME
SET @JobHistoryDeleteDate = DATEADD(m,-1,GETDATE())
EXEC sp_purge_jobhistory @oldest_date = @JobHistoryDeleteDate 

You can also set a row limit by using this registry entry.

3) Maint Plan History

To determine the date of the oldest maintenance plan record, use this -
DECLARE @mpstartdate DATETIME
SELECT @mpstartdate = MIN(end_time) FROM msdb.dbo.sysdbmaintplan_history WITH (NOLOCK) OPTION (MAXDOP 1)

If there is significant history to remove, use the Clear Maintenance Plan History (Nibble Delete) Script to remove it. To prevent it building up again in the future, regularly run (or schedule) -
DECLARE @MaintPlanHistoryDeleteDate DATETIME
SET @MaintPlanHistoryDeleteDate = DATEADD(m,-1,GETDATE())
DELETE FROM msdb.dbo.sysdbmaintplan_history WHERE (end_time < @MaintPlanHistoryDeleteDate 

To keep all 3 tables under control, here is a script I schedule on my SQL 2000 instances, to keep MSDB in shape
-- Keep MSDB Tidy
-- This script keeps 3 tables that have a tendency to bloat down to 2 weeks data
-- It also Cycles error logs when it is executed

DECLARE @HistoryDeleteDate DATETIME
SET @HistoryDeleteDate = DATEADD(week,-2,GETDATE())

-- 1) Backup History
EXEC msdb.dbo.sp_delete_backuphistory @HistoryDeleteDate ;

-- 2) Agent Job History
DECLARE @datepart INT
SET @datepart = CONVERT(INT, CONVERT(VARCHAR, @HistoryDeleteDate, 112))
DELETE FROM msdb.dbo.sysjobhistory WHERE (run_date &amp;lt; @datepart)

-- 3) Maint Plan History
DELETE FROM msdb.dbo.sysdbmaintplan_history WHERE (end_time < @HistoryDeleteDate) 

-- 4) Cycle SQL Server Error logs
EXEC master.dbo.sp_cycle_errorlog;

Clear Maintenance Plan History (nibble delete)

Similar to Clear Backup History (nibble delete) and Clear Job History (nibble delete), this third script applies the same technique to the maintenance plan history table.
-- Maintenance Plan History

USE MSDB
GO

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

SELECT @OldestJobHistoryDate = end_time 
FROM msdb.dbo.sysdbmaintplan_history 
WHERE sequence_id = (select MIN(sequence_id) FROM msdb.dbo.sysdbmaintplan_history )

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   
 DELETE FROM msdb.dbo.sysdbmaintplan_history WHERE end_time < @DeleteDate 
 SELECT @Counter = @Counter - @DaysToDeleteAtOnce  
END 
GO

Tuesday 16 August 2011

Maintenance Plan will not SAVE

Came across this issue, where a Maintenance Plan would not save...



Solution :

To resolve this, either apply the latest SQL Service pack

or

Open a command prompt, and re-register 2 DLLs -

regsvr32 msxml3.dll
regsvr32 msxml6.dll

Monday 15 August 2011

Loop : Remove Auto Shrink from all databases!

SQL 2000 Version :
DECLARE @databasename varchar(100)
DECLARE @sqlAlterStatement varchar(500)

DECLARE NastyCursorThing CURSOR READ_ONLY FOR
SELECT Name FROM sysdatabases
WHERE DBID > 4 AND DATABASEPROPERTYEX(name, 'IsAutoShrink') = 1

OPEN NastyCursorThing
FETCH NEXT FROM NastyCursorThing INTO @databasename
WHILE @@FETCH_STATUS = 0
	BEGIN
	SET @sqlAlterStatement ='ALTER DATABASE [' + @databasename + '] SET AUTO_SHRINK OFF WITH NO_WAIT' + CHAR(10)
	print @sqlAlterStatement
	EXEC(@sqlAlterStatement)
	FETCH NEXT FROM NastyCursorThing INTO @databasename
	END

CLOSE NastyCursorThing
DEALLOCATE NastyCursorThing
SQL 2005/2008 Version :
DECLARE @databasename varchar(100)
DECLARE @sqlAlterStatement varchar(500)

DECLARE NastyCursorThing CURSOR READ_ONLY FOR
SELECT name FROM sys.databases
WHERE database_id >  4 AND DATABASEPROPERTYEX(name, 'IsAutoShrink') = 1

OPEN NastyCursorThing
FETCH NEXT FROM NastyCursorThing INTO @databasename
WHILE @@FETCH_STATUS = 0
	BEGIN
	SET @sqlAlterStatement ='ALTER DATABASE [' + @databasename + '] SET AUTO_SHRINK OFF WITH NO_WAIT' + CHAR(10)
	print @sqlAlterStatement
	EXEC(@sqlAlterStatement)
	FETCH NEXT FROM NastyCursorThing INTO @databasename
	END

CLOSE NastyCursorThing
DEALLOCATE NastyCursorThing

Thursday 4 August 2011

SQL Server Support Dates

As an exercise to see how up to date clients are with patching, I produced the following list.
So, (as of August 2011) -

SQL 2000 (yes some organisations still have it)
  • SP4 Released May 2005. Support ended April 2008
  • SP3 Released Jan 2003. Support ended July 2007
  • SP2 Released Nov 2001. Support ended Apr 3003
  • SP1 Released Jun 2001. Support ended Feb 2002
  • RTM Released Nov 2000. Support ended Dec 2001

SQL 2005
  • SP4 + Updates (Build > 9.0.00.5254). Current.
  • SP4 Released Dec 2010. Support ended April 2011.
  • SP3 Released Nov 2008. Support ended Apr 2011.
  • SP2 Released Jan 2007. Support ended Jan 2010.
  • SP1 Released Apr 2006. Support ended Apr 2008.
  • RTM Released Jan 2006. Support ended July 2007.

SQL 2008
  • SP2 Released Sep 2010. Current.
  • SP1 Released Apr 2009. Current. Support ends Oct 2011
  • RTM Released Aug 2008. Support ended Apr 2010

SQL 2008 R2
  • RTM Released Apr 2010. Current. Support ends Jan 2014


Links