SQL Administration Part 1 – LincolnBlog.net

Here are some SQL Administrative queries:

SELECT SERVERPROPERTY('IsClustered')

1	SELECT SERVERPROPERTY('IsClustered')

sp_configure Use
Type the following to see the options the system sproc “sp_configure” has available to it:

EXEC sp_configure

1	EXEC sp_configure

A Simple Database Dashboard
Here is a query that will quickly give you the details of all databases on a server\instance:

SELECT
	sdb.name AS 'Database Name',
	databasepropertyex(sdb.name, 'Recovery') AS [Recovery Mode],
	databasepropertyex(sdb.name, 'IsAutoShrink') AS [Auto-shrink?],
	CASE
		WHEN primary_database IS NULL
			THEN 'no'
		ELSE 'yes'
	END AS 'Log-shipped?',
	databasepropertyex(sdb.name, 'IsInStandBy') AS [Stand-by?],
	d.compatibility_level AS [Compat Level],
	databasepropertyex(sdb.name, 'Status') AS [Status],
	CASE
		WHEN max(bs.backup_finish_date) IS NULL
			THEN 'No Backup'
		ELSE convert(VARCHAR(100), max(bs.backup_finish_date))
	END AS 'Last Backup Complete Date',
	CASE
		WHEN primary_database IS NULL
			THEN 'No Log-shipped Backup'
		ELSE convert(VARCHAR(100), max(lspd.last_backup_date))
	END AS 'Last Log-ship Date?',
	CASE
		WHEN secondary_database IS NULL
			THEN 'No Restore'
		ELSE convert(VARCHAR(100), max(lssd.last_restored_date))
	END AS 'Last Restored Date?',
	(SELECT replace(convert(VARCHAR(20), cast((smf.size * 8) / 1024 AS MONEY), 1), '.00', '')
	 FROM sys.master_files smf
	 WHERE d.database_id = smf.database_id
		 AND smf.type_desc = 'ROWS'
		 AND smf.file_id = 1) AS 'File Size in MB',
	(SELECT replace(convert(VARCHAR(20), cast((smf.size * 8) / 1024 AS MONEY), 1), '.00', '')
	 FROM sys.master_files smf
	 WHERE d.database_id = smf.database_id
		 AND smf.type_desc = 'LOG'
		 AND smf.file_id = 2) AS 'Log Size in MB'
        FROM master.dbo.sysdatabases sdb
	INNER JOIN sys.databases d ON d.database_id = sdb.dbid
	INNER JOIN sys.master_files smf ON smf.database_id = d.database_id
	LEFT OUTER JOIN msdb.dbo.backupset bs ON sdb.name = bs.database_name AND bs.type = 'D'
	LEFT OUTER JOIN msdb.dbo.log_shipping_primary_databases lspd ON lspd.primary_database = sdb.name
	LEFT OUTER JOIN msdb.dbo.log_shipping_secondary_databases lssd ON lssd.secondary_database = sdb.name
        WHERE smf.file_id = 1
        GROUP BY sdb.name, lspd.primary_database, d.compatibility_level, lspd.last_backup_date, lssd.last_restored_date, lssd.secondary_database, smf.size, d.database_id
        ORDER BY 1

SELECT

sdb.name AS 'Database Name',

databasepropertyex(sdb.name, 'Recovery') AS [Recovery Mode],

databasepropertyex(sdb.name, 'IsAutoShrink') AS [Auto-shrink?],

CASE

WHEN primary_database IS NULL

THEN 'no'

ELSE 'yes'

END AS 'Log-shipped?',

databasepropertyex(sdb.name, 'IsInStandBy') AS [Stand-by?],

d.compatibility_level AS [Compat Level],

databasepropertyex(sdb.name, 'Status') AS [Status],

CASE

WHEN max(bs.backup_finish_date) IS NULL

THEN 'No Backup'

ELSE convert(VARCHAR(100), max(bs.backup_finish_date))

END AS 'Last Backup Complete Date',

CASE

WHEN primary_database IS NULL

THEN 'No Log-shipped Backup'

ELSE convert(VARCHAR(100), max(lspd.last_backup_date))

END AS 'Last Log-ship Date?',

CASE

WHEN secondary_database IS NULL

THEN 'No Restore'

ELSE convert(VARCHAR(100), max(lssd.last_restored_date))

END AS 'Last Restored Date?',

(SELECT replace(convert(VARCHAR(20), cast((smf.size * 8) / 1024 AS MONEY), 1), '.00', '')

FROM sys.master_files smf

WHERE d.database_id = smf.database_id

AND smf.type_desc = 'ROWS'

AND smf.file_id = 1) AS 'File Size in MB',

(SELECT replace(convert(VARCHAR(20), cast((smf.size * 8) / 1024 AS MONEY), 1), '.00', '')

FROM sys.master_files smf

WHERE d.database_id = smf.database_id

AND smf.type_desc = 'LOG'

AND smf.file_id = 2) AS 'Log Size in MB'

FROM master.dbo.sysdatabases sdb

INNER JOIN sys.databases d ON d.database_id = sdb.dbid

INNER JOIN sys.master_files smf ON smf.database_id = d.database_id

LEFT OUTER JOIN msdb.dbo.backupset bs ON sdb.name = bs.database_name AND bs.type = 'D'

LEFT OUTER JOIN msdb.dbo.log_shipping_primary_databases lspd ON lspd.primary_database = sdb.name

LEFT OUTER JOIN msdb.dbo.log_shipping_secondary_databases lssd ON lssd.secondary_database = sdb.name

WHERE smf.file_id = 1

GROUP BY sdb.name, lspd.primary_database, d.compatibility_level, lspd.last_backup_date, lssd.last_restored_date, lssd.secondary_database, smf.size, d.database_id

ORDER BY 1

What are Dynamic Management Views (DMVs)
Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance.

SQL: Finding out which Database is Creating a lot of Checkpoints

DBCC TRACEON (3502, 3605, -1);

1	DBCC TRACEON (3502, 3605, -1);

Checkpoints and Log files
Here is a link explaining how to decipher what is written in the SQL Error Logs: [^http://sqlmag.com/blog/how-monitor-checkpoints|How to monitor checkpoints]

Here is another: [^http://msdn.microsoft.com/en-us/library/ms189573(v=sql.90).aspx|Checkpoints and the Active Portion of the Log]

SQL: Get the Last Backup Date for All Databases

SELECT db.name
     , CASE
         WHEN max(b.backup_finish_date) IS NULL THEN
           'No Backup'
         ELSE
           convert(VARCHAR(100), max(b.backup_finish_date))
       END AS last_backup_finish_date
FROM sys.databases db
  LEFT OUTER JOIN msdb.dbo.backupset b
    ON db.name = b.database_name AND b.type = 'D'
WHERE db.database_id NOT IN (2)
GROUP BY db.name
ORDER BY 2 DESC

SELECT db.name

, CASE

WHEN max(b.backup_finish_date) IS NULL THEN

'No Backup'

ELSE

convert(VARCHAR(100), max(b.backup_finish_date))

END AS last_backup_finish_date

FROM sys.databases db

LEFT OUTER JOIN msdb.dbo.backupset b

ON db.name = b.database_name AND b.type = 'D'

WHERE db.database_id NOT IN (2)

GROUP BY db.name

ORDER BY 2 DESC

will return this:

[imageleft|All Databases Backup Status|{UP(SQL-Server-Administration-Notes)}LastBackupDate.PNG]

SQL: Get Database Recovery Model, Compatibility Mode and State

SELECT name
, compatibility_level
, recovery_model_desc
, state_desc
FROM sys.databases

SELECT name

, compatibility_level

, recovery_model_desc

, state_desc

FROM sys.databases

will return this:

[imageleft|Comp Mode, Recovery Model and State Results|{UP(SQL-Server-2012-Security-Notes)}RecoveryModelQueryResults.PNG]

Views – 2280