sql solace

2011

2012-01-03T22:40:00.000Z

In common with a lot of technical bloggers I have been publicly setting and reviewing goals over the past few years. I find that having long term goals outside of the workplace keeps me focused and increases the breadth of my skill set.

That is all very well and has worked in the past for me. In 2011 however I fell short of achieving all of the tasks I set myself. Anyway, here is my autopsy of my goal list from last year.

A new role

I achieved this goal and started in April 2011. The role has been challenging in terms of the volume of work and has forced me to up my game re; communication skills. Technically however, the technologies are old and simple issues are repeated over geographically separate client sites. In terms of skills I have still been able to utilize a mixture of administration and development.

Blogging

My output to this site has been less than half that of previous years and has occurred in bursts rather than the usual trickle. I found myself constantly returning to old posts and scripts last year so my efforts have paid dividends. I need to index the site better however so that will be on this years list! Rather bizarrely a lot of content has been focused on SQL 2000 which I thought I had long seen the back of! The client is always right (or skint) however...

Community

I did manage to publish more scripts although only on my site (no further SSC contributions this year). As for publishing articles (to other sites) I totally failed on this goal.
In terms of SQL Server events I managed to attend 3 in 2011. In April there was SQLBits 8 in Brighton and September saw me attend (and help out at) SQLBits 9 in Liverpool.
The 3rd event I attended was Gavin Payne's October SQL Server in the Evening event where I made my speaking debut. In line with recent experiences I presented my approach to auditing SQL Server systems. Public speaking wasn't in the plan but I am grateful for the opportunity to conquer a demon.

Learning

I am a little disappointed not to have had the chance for a major project that requires SSAS, SSIS or .NET development so these will remain on my list. In terms of reading, my book backlog is not being helped by the volume of quality SQL content the community is producing. Time has not been on my side of late and my reading list is being joined by a viewing list of awesome free training videos!

TSQL : TOP N PER GROUP using RANK()

2011-11-16T23:27:00.001Z

An Adventureworks quick script to demonstrate top items in a group. It isn't the query I wrote today, but it is one I can publish here!

-- Sales Order ID, Name and Price for the 3 most expensive items in each order
-- We join to a derived table utilising the RANK function to order the items in each order by expense.
-- The WHERE clause then restricts the results according to the output of the RANK function
SELECT 
  S.SalesOrderID
 ,P.Name
 ,D.UnitPrice
FROM Sales.SalesOrderHeader S
INNER JOIN
 (SELECT 
  RANK() OVER (PARTITION BY SalesOrderID ORDER BY UnitPrice DESC) ItemCount
  ,* 
  FROM Sales.SalesOrderDetail)  D
ON S.SalesOrderID = D.SalesOrderID
INNER JOIN Production.Product P
ON P.ProductId = D.ProductId
WHERE ItemCount <= 3
ORDER BY SalesOrderID, Name, UnitPrice

Solace : Tsql : ROW_NUMBER, RANK, DENSE_RANK and PARTITION BY

Luke Hayler : Rediscovering RANK

Management Studio Error : mscorlib.tlb could not be loaded

2011-11-03T14:22:00.000Z

For testing purposes, I've got a VM with SQL 2000, 2005 & 2008 all as named instances. Yes I know it sucks performance wise but it is only for testing deployment scripts!

Anyway, after installing it and applying the various service packs, I got this error :

mscorlib.tlb could not be loaded

The 'Microsoft Fix it 50701' hotfix downloadble here did not work for me, but scroll down that page and you'll find instructions to modify the registry. It points Management Studio at the correct version of the .NET framework. These did work, the only problem being I'm reportedly going to have to do this each time I patch SQL :(

Could not access Maintenance Plans in Management Studio

2011-10-28T19:13:00.000+01:00

Error :
Method not found: 'Void Microsoft.SqlServer.Management.DatabaseMaintenance.TaskUIUtils..ctor()'. (Microsoft.SqlServer.MaintenancePlanTasksUI)

Investigation :
SQL Server 2005 Management Studio is unpatched (9.00.1399)
Running SELECT @@Version shows the database engine is to be at Cumulative Update 9 (9.00.4294).

Solution :
Apply Service Pack to Management Studio

Ref : http://mbsturk.blogspot.com/2010/03/method-not-found-void.html

Renaming SQL Server Instances

2011-10-18T19:55:00.000+01:00

Working with virtual machines frequently now I often duplicate entire systems for testing purposes and rename the newly created machine. When SQL Server is included in these images, the new copy is left not knowing what it's own name is.

To tell SQL it has been renamed, you need to run the following.

sp_helpserver
select @@servername
go
 
sp_dropserver 'OLDSNAME'
go
sp_addserver 'NEWNAME','local'
go
 
sp_helpserver
select @@servername
go

Remember you will need to restart the SQL Server service after executing this. You could even do it automatically using this Rename SQL Installation script from back in the day. It will detect the names automatically.

SQL People Interview

2011-10-04T00:00:00.002+01:00

My SQLPeople Interview was published today. You can read it here -

http://sqlpeople.net/admin/2011/10/03/richard-doering/

SQLPeople is the brainchild of SSIS Guru Andy Leonard (who I met when he flew over to the UK last week to present at SQLBits 9). It is a community project running events in the US (as well as the website). I found the site recently and enjoyed reading the DBA interviews. More proof if it were needed of the wide range of roles the term 'DBA' can encompass.

r

SQL Server in the Evening (4th Event)

2011-10-03T23:48:00.000+01:00

This evening was a first for me. Fresh (knackered) from helping out at SQLBits at the weekend I faced one of my demons and made my debut at public speaking.

The opportunity was provided to me by Gavin Payne (@GavinPayneUK) and it was tonight at his 'SQL Server in the evening' event that i took the beginners slot. The event is a SQL Server user group that runs in West Surrey. Tonight's venue was the Ramada Hotel on the Hog's Back in Farnham.

My session was first (no pressure) and was entitled CSI SQL : Auditing SQL Server. I felt I did ok but was initially rather nervous. My content addressed the way I have recently approached a large number of audits of SQL Server installations. It had quite a wide scope and was more of a memory jogger than a technical indepth talk. I finished by presenting the spreadsheets and scripts I developed to quickly audit systems. On a side note I loved the usb powerpoint clicker and have since put one on my birthday wish list (should I get the whim to present again!). I enjoyed doing it and found the positive feedback extremely encouraging.

After an delicious but delayed buffet (my fault for overrunning my time slot) we were treated to 2 further presentations. The first was an inspiring deep dive into PowerShell by Pete Rossi (@RossiPete). Powershell is high on my 'must play with' list for automating administration so I found this quite an eye opening session. Finally AlwaysOn functionality in Denali was the topic for Microsoft Certified Master Christian Bolton. Christian spent an hour explaining the how High Availability in SQL Server 2012 has improved.

A great evening overall and a large weight lifted for me by public speaking.

r

Windows 2003 : Reviewing Cluster Logs

2011-10-03T13:11:00.000+01:00

Spent some time corresponding SQL events with the cluster event log today. A cluster failing over seemingly without good cause. Look for - Source - Clussvc Category - Failover Manager

The cause will be another blog post!

SQLBits 9

2011-10-02T20:28:00.000+01:00

The 9th SQLBits was held in Liverpool this weekend. Despite it being 226 miles away from home that was no deterant for a few days of quality SQL content, company and beer.

This was my 5th SQLBits and I volunteered to help. This meant room monitoring (helping spearkers, tidying up etc) , helping attendees (mainly find rooms) and other light duties. For me volunteering gave me a purpose between sessions. Knowing the distributed nature of my work meant I was not able to furfil purchases from the exhibiting vendors I prefered not to add myself (once again) to their mailing lists. There is also a limit to how many conference freebies even my kids would appreciate!

The volunteering meant some of my sessions were allocated for me. This was fine as volunteers had given session preferences so they wouldnt miss content they needed. Ensuring everything went smoothly was expertly organised by Annette Allen (@Mrs_Fatherjack). I'm already looking forward to SQLBits 10 and would encourage SQL Professionals to make this a regular date. As a learning and social experience, SQLBits (and the SQLBits community) rocks!

The sessions I attended this time round, were -

Performance tuning from the field
Simon Sabin

Lightning Talks
Various Speakers

Myths and Monsters of Flash
Fusion-io

Advanced SQL Server 2008 Troubleshooting
Klaus Aschenbrenner

The Art of War-Fast Track Data Warehouse & Fragmentation
James Rowland-Jones

Designing an SSIS Framework
Andy Leonard

Building a SSMS Add-in; The Agony and Ecstasy
Mark Pryce-Maher

Understanding SQL Server Execution Plans
Klaus Aschenbrenner

Through the virtual looking glass – monitoring virtualised SQL
Gavin Payne

Whats new in Denali-TSQL
Dave Ballantyne

Other SqlBits 9 Coverage :

Update, November 2011 :
The conference videos have been uploaded in record time and are now available at sqlbits.com

Are my databases being used?

2011-09-17T20:55:00.000+01:00

I came across a SQL 2005 server recently where it's administrator had forgotten which databases were in use (among other things). With the goal of wanting to perform a tidy up on the server, I wrote this script to prevent a lenghty, painful manual process.

The script fetches data about when the databases were last used and returns the following

DatabaseName (Obvious really)
LastReadOperation - aggregated from index information in sys.dm_db_index_usage_stats
LastWriteOperation - aggregated from index information in sys.dm_db_index_usage_stats
DataFileDateStamp - file system date stamp
LogFileDateStamp - file system date stamp

It is based on 'When were my databases last accessed ?' and fetches the file date information from the file system by using dbo.get_file_date. To fetch the file system information, it does use xp_cmdshell , which may be a security concern depending on your environment. Becauase it uses the file system, you need to run it in Management Studio on the server you are auditing.

USE master
GO

exec sp_configure 'show advanced options', 1;
reconfigure;
exec sp_configure 'xp_cmdshell',1;
reconfigure;
GO
IF OBJECT_ID('tempdb..##Results') IS NOT NULL
 BEGIN
 DROP TABLE ##Results
 END

CREATE TABLE ##Results ([DatabaseName] sysname NULL, [FileName] sysname NULL, [PhysicalName] NVARCHAR(260) NULL,  
    [FileType] VARCHAR(4) NULL, [DateFileSystem] DATETIME ) 
DECLARE @SQL VARCHAR(MAX)
 SELECT @SQL =  
 'USE [?] INSERT INTO ##Results([DatabaseName], [FileName], [PhysicalName],  
 [FileType])  
 SELECT DB_NAME(), 
 [name] AS [FileName],  
 physical_name AS [PhysicalName],  
 [FileType] =  
 CASE type 
 WHEN 0 THEN ''Data'''  
 +  'WHEN 1 THEN ''Log''' 
 +  'END
 FROM sys.database_files  (NOLOCK)
 ORDER BY [FileType], [file_id]' 
 
EXEC sp_MSforeachdb @SQL 
 
DECLARE @file_date_op datetime 

DECLARE  @db SYSNAME
DECLARE @filename NVARCHAR(260)
DECLARE tablecursor CURSOR FORWARD_ONLY FOR 
SELECT databasename, physicalname 
FROM   ##results

OPEN tablecursor 

WHILE (1 = 1) 
BEGIN 
FETCH NEXT FROM tablecursor 
INTO @db,@filename 

IF @@FETCH_STATUS <> 0 
BREAK; 
SET @file_date_op = null
print @filename
exec master.dbo.get_file_date 
  @file_name = @filename
 ,@file_date = @file_date_op OUTPUT
UPDATE ##Results 
SET  DateFileSystem = @file_date_op
WHERE   physicalname = @filename 
END 
CLOSE tablecursor 
DEALLOCATE tablecursor 

SELECT
 Name AS DatabaseName
   ,REPLACE(CONVERT(VARCHAR(30),MAX(CASE 
  WHEN ISNULL(last_user_seek,0) >= ISNULL(last_user_scan,0) AND ISNULL(last_user_seek,0) >= ISNULL(last_user_lookup,0) THEN ISNULL(last_user_seek,0)
  WHEN ISNULL(last_user_scan,0) >= ISNULL(last_user_lookup,0) THEN ISNULL(last_user_scan,0)
  ELSE ISNULL(last_user_lookup,0)
  END),121),'1900-01-01 00:00:00.000','') AS LastReadOperation
   ,REPLACE(CONVERT(VARCHAR(30),ISNULL(MAX(last_user_update),0),121),'1900-01-01 00:00:00.000','')  AS LastWriteOperation
   ,DataFile.DateFileSystem AS DataFileDateStamp
   ,LogFile.DateFileSystem AS LogFileDateStamp
FROM sys.databases d
LEFT JOIN sys.dm_db_index_usage_stats s
ON s.database_id = d.database_id 
LEFT JOIN ##Results DataFile
ON DataFile.DatabaseName = DB_NAME(d.database_id)
LEFT JOIN ##Results LogFile
ON LogFile.DatabaseName = DB_NAME(d.database_id)
WHERE DataFile.FileType = 'Data'
  AND LogFile.FileType = 'Log'
GROUP BY 
 d.name
   ,DataFile.DateFileSystem
   ,LogFile.DateFileSystem

GO

When were my databases last accessed?

2011-09-14T20:49:00.000+01:00

This script uses sys.dm_db_index_usage_stats to get timestamps for the last read and write operations for a database.

SELECT
 DB_NAME(database_id) AS DatabaseName
   ,MAX(CASE 
  WHEN ISNULL(last_user_seek,'1900-01-01') >= ISNULL(last_user_scan,'1900-01-01') AND ISNULL(last_user_seek,'1900-01-01') >= ISNULL(last_user_lookup,'1900-01-01') THEN ISNULL(last_user_seek,'1900-01-01')
  WHEN ISNULL(last_user_scan,'1900-01-01') >= ISNULL(last_user_lookup,'1900-01-01') THEN ISNULL(last_user_scan,'1900-01-01')
  ELSE ISNULL(last_user_lookup,'1900-01-01')
  END) AS LastReadOperation
   ,MAX(last_user_update) AS LastWriteOperation
FROM sys.dm_db_index_usage_stats
GROUP BY 
 DB_NAME(database_id)

dbo.get_file_date

2011-09-09T20:38:00.000+01:00

This procedure gets the date a file was updated, as reported by the file system.

exec sp_configure 'show advanced options', 1;
reconfigure;
exec sp_configure 'xp_cmdshell',1;
reconfigure;
go

use master
go

create procedure [dbo].[get_file_date](
@file_name varchar(max)
,@file_date datetime output
) AS 
BEGIN 
set dateformat dmy
declare @dir table(id int identity primary key, dl varchar(2555))
declare @cmd_name varchar(8000),@fdate datetime,@fsize bigint, @fn varchar(255)
set @fn=right(@file_name,charindex('\',reverse(@file_name))-1)
set @cmd_name='dir /-C "'+@file_name+'"'

insert @dir
exec master..xp_cmdshell @cmd_name

select @file_date=convert(datetime,ltrim(left(dl,charindex('   ',dl))),103) 
from @dir where dl like '%'+@fn+'%'

end
go

usage -

declare @file_date_op datetime 

exec master.dbo.get_file_date 
  @file_name = 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\MSDBData.mdf'
 ,@file_date = @file_date_op OUTPUT

SELECT @file_date_op

SQL 2005 : Maintenance Plan won't update, Agent job won't delete

2011-09-02T00:05:00.000+01:00

I'm sure this is a documented bug somewhere, but I came across a Maintenance Plan / Agent Job issue today. The client was running SQL 2005 SP2 (Build 9.00.3042.00)

I created a Maintenance Plan (Transaction Log backups), scheduled it (for every 30 minutes) and it ran fine for a few hours. I came back to it today to find it no longer running. On examining the Maintenance Plan it would not let me view the schedule, returning to SQL Agent jobs I was unable to delete the job either.

The message I got was -

Trying the same from TSQL,

delete from sysmaintplan_subplans
where subplan_description = 'TL backups'

Produced the same -

Msg 547, Level 16, State 0, Line 2 The DELETE statement conflicted with the REFERENCE constraint "FK_sysmaintplan_log_subplan_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_log", column 'subplan_id'. The statement has been terminated.

This left me we with 3 options.

Disabling constraints in a system database in order to delete the record (not recommended)
Working out the order to delete maintenance plan records from system tables in order to obey referential integrity
Finding someone who has done it before.

Fortunately option 3 was just a few keystrokes away. Clay McDonald has already written a procedure and has published it in this awesome workaround : Can’t Delete Jobs (Microsoft SQL Server, Error: 547)

Checking CHECKDB !

2011-08-31T23:24:00.002+01:00

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

It only works on SQL 2005+
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

2011-08-31T21:50:00.000+01:00

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

Online index rebuilds : Unsupported data types

2011-08-29T11:27:00.000+01:00

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')

Cleaning up MSDB

2011-08-21T20:55:00.000+01:00

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)

2011-08-21T19:45:00.001+01:00

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

Maintenance Plan will not SAVE

2011-08-16T12:53:00.005+01:00

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

Loop : Remove Auto Shrink from all databases!

2011-08-15T11:45:00.001+01:00

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

Bookmark : How can you tell if an index is REALLY a duplicate?

2011-08-12T10:21:00.001+01:00

Another excellent post from Kimberley Tripp...
sqlskills : How can you tell if an index is REALLY a duplicate?

SQL Server Support Dates

2011-08-04T10:16:00.000+01:00

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

SQL Management Studio : Start Clean!

2011-07-29T09:08:00.000+01:00

When managing multiple servers I find this to be an essential tip.

From the menu bar select 'Tools > Options '.
Then from the 'General' pane (shown below), select 'Open empty environment' in the 'At Startup' drop down menu.

This means you will no longer be prompted to connect to the last server you were using when you start Management Studio.

Use this together with the other SSMS speedup tips listed here to speed up your Management Studio experience.

File Fragmentation : Contig.exe

2011-07-26T23:26:00.000+01:00

Contig is a free command line defragmentation tool.
Now on Microsoft Technet it was formerly part of the sysinternals project.
It can be used to analyze fragmentation without performing any defragmentation with the -a switch.
The advantage of the tool is that you can specify individual files to analyse. You do not have to wait on results for an entire drive to be analysed.
Running Contig without parameters tells you how to use it -

D:\MSSQL\Data>contig

Contig v1.6 - Makes files contiguous
Copyright (C) 1998-2010 Mark Russinovich
Sysinternals - www.sysinternals.com

Contig is a utility that defragments a specified file or files.
Use it to optimize execution of your frequently used files.

Usage:
contig [-a] [-s] [-q] [-v] [existing file]
or contig [-f] [-q] [-v] [drive:]
or contig [-v] -n [new file] [new file length]

-a: Analyze fragmentation
-f: Analyze free space fragmentation
-q: Quiet mode
-s: Recurse subdirectories
-v: Verbose

Contig can also analyze and defragment the following NTFS metadata files:
$Mft
$LogFile
$Volume
$AttrDef
$Bitmap
$Boot
$BadClus
$Secure
$UpCase
$Extend

To view fragmentation, use the -a switch like this...

D:\MSSQL\Data>contig -a *.mdf

Contig v1.6 - Makes files contiguous
Copyright (C) 1998-2010 Mark Russinovich
Sysinternals - www.sysinternals.com

D:\MSSQL\Data\Accounting.MDF is defragmented
D:\MSSQL\Data\Accounting_UAT.MDF is in 14 fragments
D:\MSSQL\Data\AuditPC.mdf is in 7 fragments
D:\MSSQL\Data\DataStore.mdf is in 34 fragments
D:\MSSQL\Data\FakeDb.MDF is in 5 fragments
D:\MSSQL\Data\Personel.mdf is in 4 fragments
D:\MSSQL\Data\master.mdf is in 3 fragments
D:\MSSQL\Data\model.mdf is in 2 fragments
D:\MSSQL\Data\msdbdata.mdf is in 5182 fragments
D:\MSSQL\Data\northwnd.mdf is in 2 fragments
D:\MSSQL\Data\pubs.mdf is in 2 fragments
D:\MSSQL\Data\Software.mdf is in 4 fragments
D:\MSSQL\Data\Software_UAT.mdf is in 9 fragments
D:\MSSQL\Data\Telecoms.mdf is in 17 fragments
D:\MSSQL\Data\tempdb.mdf is in 42 fragments
D:\MSSQL\Data\tools.mdf is in 5 fragments
D:\MSSQL\Data\Weblogs.MDF is in 89 fragments

Summary:
Number of files processed : 17
Average fragmentation : 176.03 frags/file

Download Link : http://technet.microsoft.com/en-us/sysinternals/bb897428

SQL Server : Auditing disk configuration

2011-07-19T23:38:00.000+01:00

Disks or SAN volumnes presented for SQL Server are best formatted and aligned for optimal use.
Auditing an existing SQL server however, you will want to check whether this is the case.

Checking Cluster Size (File Allocation unit)
Use FSUTIL, like this -

C:\>fsutil fsinfo ntfsinfo s:
NTFS Volume Serial Number :       0x385ed1bb5ed171dc
Version :                         3.1
Number Sectors :                  0x00000000207fcb54
Total Clusters :                  0x00000000040ff96a
Free Clusters :                  0x00000000017b10dc
Total Reserved :                  0x0000000000000000
Bytes Per Sector :               512
Bytes Per Cluster :               4096
Bytes Per FileRecord Segment    : 1024
Clusters Per FileRecord Segment : 0
Mft Valid Data Length :           0x000000022cfc0000
Mft Start Lcn :                  0x00000000000c0000
Mft2 Start Lcn :                  0x000000000207fcb5
Mft Zone Start :                  0x00000000002a9720
Mft Zone End   :                  0x00000000008dff40

Bytes Per Cluster is the figure we are after. In the example above it is 4096 bytes (4K). This is NTFS default and is less than optimal for SQL Server (64K reads & writes).

Checking Partition Alignment
There are 2 methods of doing this. The first is WMIC -

C:\>wmic partition get BlockSize, StartingOffset, Name, Index
BlockSize Index Name                   StartingOffset
512        0      Disk #0, Partition #0 16384
512        0      Disk #1, Partition #0 32256
512        0      Disk #2, Partition #0 32256
512        0      Disk #3, Partition #0 32256
512        0      Disk #4, Partition #0 32256
512        0      Disk #5, Partition #0 32256

If WMIC is not available, DISKPART commands can reveal how partition alignment is configured.

C:\> DISKPART

DISKPART> select volume s:

Volume 5 is the selected volume.

DISKPART> list partition

Partition ### Type Size Offset

------------- ---------------- ------- -------

* Partition 1 Primary 37 GB 32 KB

DISKPART> exit

In the examples above the majority of partitions are aligned to 32K (32256 bytes), again less than ideal.

See Disk Formatting & Partition Alignment for SQL Server