/* ** ---------------------------- ** database audit script ** mar 2005 - Wolfgang Führer ** ---------------------------- */ ------------------------------------- -- make Audit folder for later use -- ------------------------------------- USE master exec xp_cmdshell 'c: & md "c:\program files\microsoft sql server\Audit"', no_output -------------------------------------------- -- drop audit database if exist -- -------------------------------------------- IF DB_ID('DB_Audit') IS NOT NULL BEGIN DROP database DB_Audit END go -------------------------- -- create main database -- -------------------------- CREATE DATABASE DB_Audit ON ( NAME = 'DB_Audit_Data', FILENAME = 'c:\program files\microsoft sql server\Audit\DB_Audit_data.mdf', SIZE = 10MB, FILEGROWTH = 10MB ) LOG ON ( NAME = 'DB_Audit_log', FILENAME = 'c:\program files\microsoft sql server\Audit\DB_Audit_log.ldf', SIZE = 5MB, FILEGROWTH = 5MB ) go ----------------------------------------- -- create audit table if not available -- ----------------------------------------- USE DB_Audit IF OBJECT_ID('tbl_DBsize') IS NULL BEGIN CREATE TABLE tbl_DBSize_Day ( DBname VARCHAR(128) NOT NULL DEFAULT ('???'), DATAmax_MB DECIMAL(15,2) NULL DEFAULT (0.0), DATAsize_MB DECIMAL(15,2) NOT NULL DEFAULT (0.0), DATAused_MB DECIMAL(15,2) NOT NULL DEFAULT (0.0), DATAfree_MB DECIMAL(15,2) NOT NULL DEFAULT (0.0), DATAdrivename CHAR(3) NULL DEFAULT ('?'), DATAdrivefree_MB DECIMAL(15,0) NULL DEFAULT (0.0), LOGmax_MB DECIMAL(15,2) NULL DEFAULT (0.0), LOGsize_MB DECIMAL(15,2) NOT NULL DEFAULT (0.0), LOGused_MB DECIMAL(15,2) NOT NULL DEFAULT (0.0), LOGfree_MB DECIMAL(15,2) NULL DEFAULT (0.0), LOGdrivename CHAR(3) NULL DEFAULT ('?'), Logdrivefree_MB DECIMAL(15,0) NULL DEFAULT (0.0), CacheHitRate_PRZ DECIMAL(15,2) NULL DEFAULT (0.0), AVG_WaitTime_MS int NULL DEFAULT (0), RecordDate smalldatetime NOT NULL DEFAULT (getdate()), RecordID int IDENTITY (1, 1) NOT NULL PRIMARY KEY ) ON [PRIMARY] END go ----------------------------------- -- drop audit procedure if exist -- ----------------------------------- IF OBJECT_ID('usp_Audit_DBSize_Day') IS NOT NULL BEGIN DROP PROCEDURE usp_Audit_DBSize_Day END go ---------------------------- -- create audit procedure -- ---------------------------- CREATE PROCEDURE usp_Audit_DBSize_DAY AS -- declare temp variables DECLARE @dbsize dec(15,2) -- size of complete database DECLARE @logsize dec(15,2) -- size of logs DECLARE @numberofdb int -- used as counter DECLARE @nameofdb VARCHAR(128) -- name of database by counter DECLARE @exec_stmt VARCHAR(600) -- string for dynamic query building DECLARE @datadrivename CHAR(1) -- name of data drive DECLARE @logdrivename CHAR(1) -- name of log drive DECLARE @low VARCHAR(11) -- size of page in bytes SELECT @low = CONVERT(VARCHAR(11),low) FROM master.dbo.spt_values WHERE TYPE = N'E' AND NUMBER = 1 -- create temp table for database names CREATE TABLE #tblNames ( DBID int IDENTITY (1, 1) NOT NULL, DBName nvarchar (128) NOT NULL ) ON [PRIMARY] -- insert database names in temp table INSERT INTO #tblNames SELECT [name] FROM master.dbo.sysdatabases -- create temp table for dynamic results CREATE TABLE #tblCalc ( datamax dec (15,2), datasize dec (15,2), datareserved dec (15,2), datadrivename CHAR (1), datadrivefree dec (15,2), logmax dec (15,2), logsize dec (15,2), logreserved dec (15,2), logused dec (15,2), logdrivename CHAR (1), logdrivefree dec (15,2), cachehits dec (15,2), cachehitbase dec (15,2), dbname nvarchar (128), waittime int ) ON [PRIMARY] -- fill table to be able to use 'update' for #fields INSERT INTO #tblCalc SELECT 0.0,0.0,0.0,'?',0.0,0.0,0.0,0.0,0.0,'?',0.0,0.0,0.0,'name',0 -- create temp table for drive infos CREATE TABLE #tblDrives( driveletter CHAR(1), freeMB int ) INSERT INTO #tblDrives exec master.dbo.xp_fixeddrives -- preparing steps are finished now ----------------------------------- -- find database entry with highest number SELECT @numberofdb = MAX(DBID) FROM #tblNames -- start auditing with higest number WHILE @numberofdb > 0 -- if there are more entries BEGIN -- fetch name of database SELECT @nameofdb = DBName FROM #tblNames WHERE DBID = @numberofdb -- print '==> ' + @nameofdb UPDATE #tblCalc SET dbname = @nameofdb -- compute complete database size SELECT @exec_stmt = 'update #tblCalc set datasize = ' + '(select convert(dec(15,2),sum(d.size))* ' + @low + '/ 1048576' + ' from ' + quotename(@nameofdb, N'[') + N'.dbo.sysfiles as d ' + ' where d.groupid <> 0)' -- print '==> ' + @exec_stmt EXECUTE (@exec_stmt) -- execute builded query -- compute complete log size SELECT @exec_stmt = 'update #tblCalc set logsize = ' + '(select convert(dec(15,2),sum(d.size))* ' + @low + '/ 1048576' + ' from ' + quotename(@nameofdb, N'[') + N'.dbo.sysfiles as d ' + ' where d.groupid = 0)' -- print '==> ' + @exec_stmt EXECUTE (@exec_stmt) -- execute builded query -- compute complete used data size SELECT @exec_stmt = 'update #tblCalc set datareserved = ' + '(select convert(dec(15,2),sum(d.reserved))* ' + @low + '/ 1048576' + ' from ' + quotename(@nameofdb, N'[') + N'.dbo.sysindexes as d ' + ' where d.indid in (0,1,255))' -- print '==> ' + @exec_stmt EXECUTE (@exec_stmt) -- execute builded query -- compute lowest max data size SELECT @exec_stmt = 'update #tblCalc set datamax = ' + '(select convert(dec(15,2),min(d.maxsize))* ' + @low + '/ 1048576' + ' from ' + quotename(@nameofdb, N'[') + N'.dbo.sysfiles as d ' + ' where d.groupid <> 0 and d.maxsize <> -1)' -- print '==> ' + @exec_stmt EXECUTE (@exec_stmt) -- execute builded query -- compute lowest max log size SELECT @exec_stmt = 'update #tblCalc set logmax = ' + '(select convert(dec(15,2),min(d.maxsize))* ' + @low + '/ 1048576' + ' from ' + quotename(@nameofdb, N'[') + N'.dbo.sysfiles as d ' + ' where d.groupid = 0 and d.maxsize <> -1)' -- print '==> ' + @exec_stmt EXECUTE (@exec_stmt) -- execute builded query -- compute log size use SELECT @exec_stmt = 'update #tblCalc set logused = (select convert(dec(15,2),(m.cntr_value))/1024' + ' from master.dbo.sysperfinfo as m' + + ' where m.instance_name = ''' + @nameofdb + ''' and ' + ' m.counter_name = ''Log File(s) Used Size (KB)'')' -- print '==> ' + @exec_stmt EXECUTE (@exec_stmt) -- execute builded query -- compute drive letter of first data file SELECT @exec_stmt = 'update #tblCalc set datadrivename = ' + '(select left(d.filename,1)' + ' from ' + quotename(@nameofdb, N'[') + N'.dbo.sysfiles as d ' + ' where d.groupid <> 0)' -- print 'drive letter data ==> ' + @exec_stmt EXECUTE (@exec_stmt) -- execute builded query SELECT @datadrivename = datadrivename FROM #tblCalc -- compute data drive free space SELECT @exec_stmt = 'update #tblCalc set datadrivefree = (select convert(dec(15,2),f.freeMB)' + ' from #tblDrives as f' + + ' where f.driveletter = ''' + @datadrivename + ''')' -- print 'drive free data ==> ' + @exec_stmt EXECUTE (@exec_stmt) -- execute builded query -- compute drive letter of first log file SELECT @exec_stmt = 'update #tblCalc set logdrivename = ' + '(select left(d.filename,1)' + ' from ' + quotename(@nameofdb, N'[') + N'.dbo.sysfiles as d ' + ' where d.groupid = 0)' -- print 'drive letter log ==> ' + @exec_stmt EXECUTE (@exec_stmt) -- execute builded query SELECT @logdrivename = logdrivename FROM #tblCalc -- compute log drive free space SELECT @exec_stmt = 'update #tblCalc set logdrivefree = (select convert(dec(15,2),f.freeMB)' + ' from #tblDrives as f' + + ' where f.driveletter = ''' + @logdrivename + ''')' -- print 'drive free log ==> ' + @exec_stmt EXECUTE (@exec_stmt) -- execute builded query -- compute cache hits SELECT @exec_stmt = 'update #tblCalc set cachehits = (select convert(dec(15,2),(m.cntr_value))/1024' + ' from master.dbo.sysperfinfo as m' + + ' where m.counter_name = ''Buffer cache hit ratio'')' -- print '==> ' + @exec_stmt EXECUTE (@exec_stmt) -- execute builded query -- compute cache hits base SELECT @exec_stmt = 'update #tblCalc set cachehitbase = (select convert(dec(15,2),(m.cntr_value))/1024' + ' from master.dbo.sysperfinfo as m' + + ' where m.counter_name = ''Buffer cache hit ratio base'')' -- print '==> ' + @exec_stmt EXECUTE (@exec_stmt) -- execute builded query -- compute cache hits base SELECT @exec_stmt = 'update #tblCalc set waittime = (select m.cntr_value' + ' from master.dbo.sysperfinfo as m' + + ' where m.counter_name = ''Average Wait Time (ms)'' and' + ' m.object_name = ''SQLServer:Locks'' and' + ' m.instance_name = ''_Total'')' -- print '==> ' + @exec_stmt EXECUTE (@exec_stmt) -- execute builded query -- transfer #tempdata to audit table INSERT INTO DB_Audit.dbo.tbl_DBSize_Day (DBname,DATAmax_MB,DATAsize_MB,DATAused_MB,DATAfree_MB,DATAdrivename,DATAdrivefree_MB,LOGmax_MB,LOGsize_MB,LOGused_MB,LOGfree_MB,LOGdrivename,LOGdrivefree_MB,CacheHitRate_PRZ,AVG_WaitTime_MS) SELECT dbname, datamax, datasize, datareserved, (datasize - datareserved), datadrivename + ':\',datadrivefree, logmax, logsize, logused, (logsize - logused) , logdrivename + ':\',logdrivefree, (cachehits/cachehitbase)*100, waittime from #tblCalc set @numberofdb = @numberofdb - 1 -- decrement for next table and repeat end go exec usp_Audit_DBSize_Day -------------------------------- -- summary view for databases -- -------------------------------- IF OBJECT_ID('v_DBSize_summary') IS not NULL drop view v_DBSize_summary go create view v_DBSize_summary as -- -- for sql 2005 -- select RecordDate, sum(DATAsize_MB)as DBsum_MB, sum(DATAdrivefree_MB) as DATAdrivefree,sum(LOGdrivefree_MB) as Logdrivefree_MB, avg(CacheHitRate_PRZ) as CacheHitRate_PRZ, avg(AVG_WaitTime_MS) as AVG_WaitTime_MS -- -- for sql 2000 select RecordDate, sum(DATAsize_MB)as DBsum_MB, sum(DATAdrivefree_MB) as DATAdrivefree,sum(LOGdrivefree_MB) as Logdrivefree_MB, sum(CacheHitRate_PRZ) as CacheHitRate_PRZ, sum(AVG_WaitTime_MS) as AVG_WaitTime_MS -- from DB_audit.dbo.tbl_DBSize_Day group by RecordDate go ------------------------------------- -- grouped view for database files -- ------------------------------------- IF OBJECT_ID('v_FileSizes') IS not NULL drop view v_FileSizes go create view v_FileSizes as -- -- for sql 2005 -- select RecordDate, DBname,sum(DATAmax_MB) as DATAmax_MB,sum(DATAsize_MB) as DATAsize_MB,sum(DATAused_MB) as DATAused_MB,SUM(DATAfree_MB) as DATAfree_MB,sum(DATAdrivefree_MB) as DATAdrivefree,sum(LOGmax_MB) as LOGmax_MB,sum(LOGsize_MB) as LOGsize_MB,sum(LOGused_MB) as LOGused_MB,sum(LOGfree_MB) as LOGfree_MB,sum(LOGdrivefree_MB) as Logdrivefree_MB, avg(CacheHitRate_PRZ) as CacheHitRate_PRZ, avg(AVG_WaitTime_MS) as AVG_WaitTime_MS -- -- for sql 2000 select RecordDate, DBname,sum(DATAmax_MB) as DATAmax_MB,sum(DATAsize_MB) as DATAsize_MB,sum(DATAused_MB) as DATAused_MB,SUM(DATAfree_MB) as DATAfree_MB,sum(DATAdrivefree_MB) as DATAdrivefree,sum(LOGmax_MB) as LOGmax_MB,sum(LOGsize_MB) as LOGsize_MB,sum(LOGused_MB) as LOGused_MB,sum(LOGfree_MB) as LOGfree_MB,sum(LOGdrivefree_MB) as Logdrivefree_MB, sum(CacheHitRate_PRZ) as CacheHitRate_PRZ, sum(AVG_WaitTime_MS) as AVG_WaitTime_MS -- from DB_audit.dbo.tbl_DBSize_Day group by RecordDate,DBName go ----------------------------------------- -- only last values for database files -- ----------------------------------------- IF OBJECT_ID('v_FileSizes_last') IS not NULL drop view v_FileSizes_last go create view v_FileSizes_last as select RecordDate, DBname,DATAmax_MB,DATAsize_MB,DATAused_MB,DATAfree_MB,DATAdrivename,DATAdrivefree_MB,LOGmax_MB,LOGsize_MB,LOGused_MB,LOGfree_MB,LOGdrivename,LOGdrivefree_MB,CacheHitRate_PRZ,AVG_WaitTime_MS from DB_audit.dbo.tbl_DBSize_Day as d where d.RecordDate = (select Max(f.RecordDate) from DB_audit.dbo.tbl_DBSize_Day as f ) go
DB_Audit.sql
Mo, 10/12/2007 - 13:21 – wolfgang
- Anmelden um Kommentare zu schreiben