Windows 2003 : Reviewing Cluster Logs

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

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 :

• Richard Douglas
• Victoria Holt
• Mark Broadbent

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

Are my databases being used?

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?

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

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 2008 : Schema Gotcha!

A colleague and I wasted a good deal of time trying to debug a problem today.
Why did the application work, but running the same SQL in Management Studio did not!

It turned out that the application logged in as 'appuser' and had a default schema 'schema1'.
When running inside Management Studio he was logged in as sa (it's a developer's local machine).
sa being a sysadmin, it has the default schema dbo. The old code (ported from sql 2000, no schema given in the query) therefore didn't know to look in 'schema1' for the objects and failed.

Therefore, always prefix object names with the owner (sql 2000) or the schema (2005+)

It will also save SQL trying to find a schema.

http://serverfault.com/questions/203551/sql-server-2008-default-schema-not-being-respected

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

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.

1. Disabling constraints in a system database in order to delete the record (not recommended) 
2. Working out the order to delete maintenance plan records from system tables in order to obey referential integrity 
3. 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)