Saturday, September 17, 2011

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

Wednesday, September 14, 2011

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)


Friday, September 9, 2011

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

Thursday, September 8, 2011

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

Friday, September 2, 2011

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)