Thursday, March 22, 2012

Databases Info


Useful information about all the databases
- Name
- ID
- Size
- Files Disk and Path
- Files Sizes
- Free disk space
- Files Percentage of usage
- Files Auto growth Settings
- Recovery model
- Compatibility Level
- Last Backup (Full backup and transaction log backup)
- Last Restore
- Database Mirroring State and Status



-- MAIN SCRIPT

SET NOCOUNT ON
IF (object_id( 'tempdb..#TMPFIXEDDRIVES' ) IS NOT NULL) DROP TABLE #TMPFIXEDDRIVES
IF (object_id( 'tempdb..#TMPSPACEUSED' ) IS NOT NULL) DROP TABLE #TMPSPACEUSED
IF (object_id( 'tempdb..#HDB' ) IS NOT NULL) DROP TABLE #HDB

CREATE TABLE #TMPFIXEDDRIVES (
DRIVE CHAR(1),
MBFREE INT)

INSERT INTO #TMPFIXEDDRIVES
EXEC xp_FIXEDDRIVES

CREATE TABLE #TMPSPACEUSED (
DBNAME VARCHAR(255),
FILEID INT,
FILENME VARCHAR(255),
SPACEUSED FLOAT)

--// SP_HELPDB


CREATE TABLE #HDB (
name sysname not null,
db_size varchar(25) not null,
owner varchar(40) not null,
dbid int not null,
created smalldatetime not null,
status varchar(500) not null,
compatibility_level int not null
)

INSERT INTO #HDB
exec sp_helpdb;

INSERT INTO #TMPSPACEUSED
EXEC( 'sp_msforeachdb''use [?]; Select ''''?'''' DBName,fileid, Name FileNme, fileproperty(Name,''''SpaceUsed'''') SpaceUsed from sysfiles''')

SELECT
      @@servername as servername,
      A.Database_id as DBid,
      A.NAME AS DATABASENAME
      ,CASE D.FILEID
      WHEN 1 THEN ltrim(XX.db_size)
      ELSE NULL
      END as db_size
      ,CASE D.FILEID
      WHEN 1 THEN XX.owner
      ELSE NULL
      END as owner
      ,CASE D.FILEID
      WHEN 1 THEN XX.created
      ELSE NULL
      END as created
      ,C.DRIVE,
      C.MBFREE AS DISKFREE_MB,
      D.FILEID as FileId,
      B.NAME AS FILENAME,
      CASE B.TYPE
      WHEN 0 THEN 'DATA'
      ELSE TYPE_DESC
      END AS FILETYPE,
      (B.SIZE * 8 / 1024)AS FILESIZE_MB,
      ROUND((B.SIZE * 8 / 1024) - (D.SPACEUSED / 128),2) as SPACEFREE_MB,
      ROUND(100-((((B.SIZE * 8 / 1024) - (D.SPACEUSED / 128))*100)/
      CASE(B.SIZE * 8 / 1024)
      WHEN 0 THEN 1
      ELSE (B.SIZE * 8 / 1024)
      END
      ),2) as [%USED],
      b.size,
      b.max_size,
      b.growth,
      b.is_percent_growth,
      --CASE (B.SIZE * 8 / 1024)
      --WHEN 0 THEN 1 ELSE 0 END as VALID,
      B.PHYSICAL_NAME,
      CASE B.TYPE
      WHEN 0 THEN A.recovery_model_desc
      ELSE NULL
      END AS [Recovery_Model],
      CASE B.TYPE
      WHEN 0 THEN A.compatibility_level
      ELSE NULL
      END AS [Compatibility_Level]
      ,CASE D.FILEID
      WHEN 1 THEN BR.last_backup_finish_date
      ELSE NULL
      END as [Backup]
      ,CASE D.FILEID
      WHEN 1 THEN BR.last_TRLog_backup_finish_date
      ELSE NULL
      END as TRBackup
      ,CASE D.FILEID
      WHEN 1 THEN BR.last_restore_date
      ELSE NULL
      END as [Restore],
      DM.mirroring_role_desc+'('+DM.mirroring_state_desc+')' as DBMirror_Info
FROM SYS.DATABASES A
INNER JOIN SYS.MASTER_FILES B ON A.DATABASE_ID = B.DATABASE_ID
INNER JOIN #TMPFIXEDDRIVES C ON LEFT(B.PHYSICAL_NAME,1) = C.DRIVE
INNER JOIN #TMPSPACEUSED D ON A.NAME = D.DBNAME AND B.NAME = D.FILENME
INNER JOIN #HDB XX on XX.dbid= A.Database_id
INNER JOIN
(
SELECT D.database_id,B.last_backup_finish_date,TR.last_TRLog_backup_finish_date,R.last_restore_date
FROM sys.databases D
LEFT JOIN (
      SELECT
      BS.database_name
      ,max(BS.backup_finish_date) as last_backup_finish_date
      FROM
      msdb.dbo.backupset BS (NOLOCK)
      INNER JOIN
      msdb.dbo.backupmediafamily MF(NOLOCK) ON BS.media_set_id = MF.media_set_id
      WHERE
      BS.backup_start_date >= CAST(CONVERT(varchar(10),dateadd(mm,-3,getdate()),120) AS datetime)
      AND BS.server_name = @@servername and BS.type='D'
      GROUP BY
      BS.database_name
      ) B on D.name=B.database_name
LEFT JOIN (
      SELECT
      BS.database_name
      ,max(BS.backup_finish_date) as last_TRLog_backup_finish_date
      FROM
      msdb.dbo.backupset BS (NOLOCK)
      INNER JOIN
      msdb.dbo.backupmediafamily MF(NOLOCK) ON BS.media_set_id = MF.media_set_id
      WHERE
      BS.backup_start_date >= CAST(CONVERT(varchar(10),dateadd(mm,-1,getdate()),120) AS datetime)
      AND BS.server_name = @@servername and BS.type='L'
      GROUP BY
      BS.database_name
      ) TR on D.name=TR.database_name
LEFT JOIN (
      SELECT
      rh.destination_database_name,
      max(rh.restore_date) as last_restore_date
      FROM
      msdb.dbo.restorehistory rh (NOLOCK)
      INNER JOIN msdb.dbo.backupset BS (NOLOCK) ON rh.backup_set_id=BS.backup_set_id
      WHERE
      BS.type= 'D' AND
      RH.restore_date >=CAST(CONVERT(varchar(10),dateadd(mm,-3,getdate()),120) AS datetime)
      GROUP BY
      rh.destination_database_name
      ) R on D.name=R.destination_database_name
      ) BR on A.Database_id=BR.database_id
LEFT JOIN
            msdb.sys.database_mirroring dm (nolock) on A.database_id=dm.database_id
--WHERE A.NAME <> 'model'
ORDER BY DATABASENAME

IF (object_id( 'tempdb..#TMPFIXEDDRIVES' ) IS NOT NULL) DROP TABLE #TMPFIXEDDRIVES
IF (object_id( 'tempdb..#TMPSPACEUSED' ) IS NOT NULL) DROP TABLE #TMPSPACEUSED
IF (object_id( 'tempdb..#HDB' ) IS NOT NULL) DROP TABLE #HDB