SQL Healthcheck

A common task when carrying out a health check on a client’s SharePoint farm is to ensure that all databases are being backed up and that the database files have been sized correctly.  Most of the time, a DBA will be tasked with carrying out backups and confirming that they are functional but in some cases, there is no DBA and there is no-one who can answer then questions around SQL Backups.

In this scenario it is useful to have a SQL script to capture and display this information.

Last Backups

The following code will present the dates of the backups, along with the collation, recovery model and whether auto shrink is on.

   1:  SELECT DISTINCT
   2:      d.Name AS [Database Name]
   3:  ,    d.recovery_model_desc AS [Recover yModel]
   4:  ,    COALESCE((
   5:              SELECT
   6:                  CONVERT(VARCHAR(12), MAX(bus_f.backup_finish_date), 101)
   7:              FROM msdb.dbo.backupset bus_f
   8:              WHERE
   9:                  bus_f.database_name = d.name
  10:              AND
  11:                  bus_f.type = 'd'
  12:              AND
  13:                  bus_f.is_copy_only = '0'
  14:      ), 'No Full') AS [Last Full Backup]
  15:  ,    COALESCE((
  16:              SELECT
  17:                  CONVERT(VARCHAR(12), MAX(bus_d.backup_finish_date), 101)
  18:              FROM msdb.dbo.backupset bus_d
  19:              WHERE
  20:                  bus_d.database_name = d.name
  21:              AND
  22:                  bus_d.type = 'i'
  23:              AND
  24:                  bus_d.is_copy_only = '0'
  25:      ), 'No Diff') AS [Last Differential Backup]
  26:  ,    COALESCE((
  27:              SELECT
  28:                  CONVERT(VARCHAR(20), MAX(bus_t.backup_finish_date), 120)
  29:              FROM msdb.dbo.backupset bus_t
  30:              WHERE
  31:                  bus_t.database_name = d.name
  32:              AND
  33:                  bus_t.type = 'l'
  34:      ), 'No Log') AS [Last Log Backup]
  35:  ,    d.collation_name AS [Collation]
  36:  ,    d.recovery_model_desc AS [Recovery Mode]
  37:  ,    CASE(d.is_auto_shrink_on)
  38:          WHEN 1 THEN 'Yes'
  39:          WHEN 0 THEN 'No'
  40:          ELSE '??'
  41:      END AS [AutoShrink Is On]
  42:  FROM sys.databases d
  43:  WHERE
  44:      d.name != 'tempdb'
  45:  AND
  46:      d.state_desc = 'online'
  47:  ORDER BY
  48:      d.name
  49:  ;

File Information

The following script will present details of all of the files associated with the databases, including the autogrowth settings and the maximum size that the files will grow to.

   1:  SELECT
   2:      DB_NAME(mf.database_id) AS [Database Name]
   3:  ,    mf.name AS [Logical Name]
   4:  ,    CONVERT (DECIMAL (20,2) , (CONVERT(DECIMAL, size)/128)) [Current File Size (MB)]
   5:  ,    CASE mf.is_percent_growth
   6:          WHEN 1 THEN 'Yes'
   7:          ELSE 'No'
   8:      END AS [Is Percent Growth]
   9:  ,    CASE mf.is_percent_growth
  10:          WHEN 1 THEN CONVERT(VARCHAR, mf.growth) + '%'
  11:          WHEN 0 THEN CONVERT(VARCHAR, mf.growth/128) + ' MB'
  12:      END AS [Growth Increment]
  13:  ,    CASE mf.is_percent_growth
  14:          WHEN 1 THEN CONVERT(DECIMAL(20,2), (((CONVERT(DECIMAL, size)*growth)/100)*8)/1024)
  15:          WHEN 0 THEN CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL, growth)/128))
  16:      END AS [Next Autogrowth Size (MB)]
  17:  ,    CASE mf.max_size
  18:          WHEN 0 THEN 'No growth is allowed'
  19:          WHEN -1 THEN 'File will grow until the disk is full'
  20:          ELSE CONVERT(VARCHAR, mf.max_size/128)
  21:      END AS [Max Size (MB)]
  22:  ,    mf.physical_name AS [Physical File Name]
  23:  FROM sys.master_files mf
  24:  ORDER BY
  25:      DB_NAME(mf.database_id)

The SQL script containing both of these scripts can be downloaded from here

2 thoughts on “SQL Healthcheck

  1. In the scenario where there is no DBA, surely backups and file settings are only really a small part of the possible issues, a much better piece of advice would be to use something like sql_blitz which highlights many other issues which could be overlooked using the approach above.

    Brent Ozar’s sql_blitz can be found here:

    http://www.brentozar.com/blitz/

Leave a Reply

Your email address will not be published.