/*
** ----------------------------
** 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