SQL Administration Part 1

Here are some SQL Administrative queries:

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

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

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

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

will return this:

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

SQL: Get Database Recovery Model, Compatibility Mode and State

will return this:

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

Views – 1743