Query To Find SQL Audit Details

I've been deploying a SQL Server auditing solution for our new SQL 2014 estate. I know, we're a few years behind. But if you saw some of the legacy systems I work with you'd understand that migrating to SQL 2014 is quite a coup!

Anyway, I found a really great audit solution that I have used as a base on Colleen Morrow's blog. I will detail what I have added to her solution in a later post. But, for now, I would like to share a query that displays some basic information about existing audits, both at the server and database level.

I put this query together for the purposes of documentation. Our 2014 estate is getting large fast. And with so many installations, we need to keep track of what is installed and configured on all our servers.

Tracking Query Plan Changes

I was reading a blog post from Brent Ozar (SQL Server Query Store), which detailed Microsoft's announced future feature in SQL Server that aims to store a history of cached execution plans. The main purpose, it seems, of such a feature is to aid in performance troubleshooting and tuning. If something has changed recently and a query or store procedure begins behaving badly, there will be another tool in the DBA toolbelt to aid in finding what happened. Beyond that I'll let others explain it further.

Full List of SQL Server 2014 DMVs

I'm not sure how useful this will be to others, but I keep searching for a single, comprehensive list of all the SQL Server DMVs with short descriptions. I am looking to complete a certification and this should prove handy for studying. This is certainly no replacement for the full descriptions and examples on MSDN, but I thought it worth putting together.

Get table and row size data

It often happens that you need to know how big a table is. There are many reasons for this: finding which tables have the most rows; understanding which tables tend to grow the fastest; find which table is using the most space.

I found a nice query on stackexchange.com that listed the tables, row counts and data sizes in a database:

Last SQL Server Instance Start Date

I was interested in finding when a new SQL Server instance I started working on was last restarted and came across this blog post.

Although the information was very good I felt the queries could be tweaked slightly and broken out into the variations needed for the different SQL Server editions.

I find that it is helpful to know when the database server was started due to the various Dynamic Management Views and Functions (DMV) that I use. Many of these collate cumulative statistics beginning at a SQL Server start. Which means that they are cleared down when the instance is restarted, so it's helpful to know the uptime of the server in order to know how much data has been collected and whether it is representative of how the server is being used: i.e. one day's worth of statistics vs. one month's.

As the blog post mentioned above outlines, there are several ways of getting to this data and some of them work only in certain versions of SQL Server. Here they are in my modified form:
NB: I am ignoring SQL Server 2000

Method 1 - Check TempDB create date
SQL Server 2005+ 

SELECT [crdate] AS [LastStartupDate],

DATEDIFF(dd,[crdate],GETDATE()) AS [Days since server start]

FROM   [dbo].[sysdatabases]

WHERE  [name] = 'tempdb'

AND [crdate] < GETDATE() - 0 --filter results to instances restarted greater than X days

Method 2 - Check the system DMV
SQL Server 2008+

SELECT [sqlserver_start_time] AS [LastStartupDate],

DATEDIFF(dd, [sqlserver_start_time],GETDATE()) AS [Days since server start]

FROM   [sys].[dm_os_sys_info]

Method 3 - Interrogate the SQL Server error log
SQL Server 2005 - 2008

DECLARE @LogNum TINYINT

SET @LogNum = 0 --0=current 1=.1 etc

DECLARE @LogType TINYINT

SET @LogType = 1 --1=SQL 2=Agent

DECLARE @ProcessType VARCHAR(64)

SET @ProcessType = 'Server'

DECLARE @TextSearch VARCHAR(20)

SET @TextSearch = 'Server process ID is'

DECLARE @ErrLog AS TABLE([LogDate] DATETIME, [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX))

INSERT INTO @ErrLog

EXEC sys.xp_readerrorlog @LogNum, @LogType , @ProcessType, @TextSearch

--grab the first occurrence and report back the timestamp as the last startup

SELECT MIN(LogDate) AS [LastStartupDate],

DATEDIFF(dd, MIN(LogDate),GETDATE()) AS [Days since server start]

FROM @ErrLog

WHERE LogDate < GETDATE() - 0 --filter results to instances restarted greater than X days

SQL Server 2008 - 2012
In SQL Server 2012 the extended stored procedure accepts only two parameters 

DECLARE @LogNum TINYINT = 0 --0=current 1=.1 etc

DECLARE @LogType TINYINT = 1 --1=SQL 2=Agent

DECLARE @ProcessType VARCHAR(64) = 'Server'

DECLARE @TextSearch VARCHAR(20) = 'Server process ID is'

DECLARE @ErrLog AS TABLE([LogDate] DATETIME, [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX))

INSERT INTO @ErrLog

EXEC sys.xp_readerrorlog @LogNum, @LogType --, @ProcessType, @TextSearch

--grab the first occurrence and report back the timestamp as the last startup

SELECT MIN(LogDate) AS [LastStartupDate],

DATEDIFF(dd, MIN(LogDate),GETDATE()) AS [Days since server start]

FROM @ErrLog

WHERE [ProcessInfo] = @ProcessType AND [TEXT] LIKE '%' + @TextSearch + '%'

AND LogDate < GETDATE() - 0 --filter results to instances restarted greater than X days

DMV Starter Pack

A while back I discovered the DMV Starter Pack, a really useful set of queries exploiting the very powerful Dynamic Management Views and Functions in SQL Server 2005 and 2008.

What I've done is add a few bits to the queries I use most often. Mostly the index queries. In the missing and bad indexes queries I added CREATE and DROP statement columns. Obviously, the CREATE statements conform to the naming conventions of my company [IX_tablename_column1name_column2name...], but this is quite easily modified. I also use a fillfactor of 90 by default. I hope this is helpful.