How To Use sp_cycle_errorlog

Before I do any testing I like to execute the sp_cycle_errorlog stored procedure. That way I can quickly see the specific SQL Server log entry without having to scroll / filter through tons of log.

It’s pretty simple to use. Just run this in the query window: sp_cycle_errorlog

Whenever you execute sp_cycle_errorlog the SQL Server log closes the “Current” log and cycles it like a server restart does. So your “current” log becomes #1, #2 becomes #3, and so on. The new “Current” log will only have a couple rows that include Microsoft copyright information, authentication mode, etc. (see screenshot below).

sp_cycle_errorlog

That should make testing and searching through the log MUCH easier!

How to Check SQL Server Database Encryption Algorithm

I have enabled TDE (Transparent Data Encryption) on almost all of my production SQL Server database servers. Yesterday I was approached by the IA (Information Assurance) team and they wanted to know what was the encryption level (key length and algorithm) of one of the database servers.

I already knew it was AES-256 but IA needed a screenshot as proof. So I wrote this little query to bring back the database name, id, encryption key length and algorithm. I ran it, took a screenshot and sent it to them.

--Bring back database name, id
--key algorithm and key length
select db.name,
e.database_id,
e.key_algorithm,
e.key_length
from sys.dm_database_encryption_keys e
join sys.databases db on db.database_id = e.database_id

Dirty Reads vs Phantom Reads in SQL Server

During a recent “interview” I was asked, “What two isolation levels in SQL Server will prevent phantom reads?”

I had never heard of “phantom reads” before but thought the person meant, “dirty reads.” So I replied, “READ COMMITTED and SNAPSHOT isolation levels.”

I was wrong…sort of. The interviewer said, “It’s actually Serializable and Snapshot.” As soon as the “interview” was over, I had to read up on phantom reads.

This is what I learned:

Phantom Reads: Occurs when a transaction is allowed to read data from a row that has been modified by another running transaction that has not yet committed.

Dirty Reads: Reading uncommitted modifications. When a transaction is allowed to read data from a row that has been modified by another running transaction that has not yet committed.

Below is a table that shows each isolation level and whether they allow dirty or phantom reads. (Photo taken from MSDN)

Isolation Levels Dirty Reads vs Phantom Reads

How to Rename a SQL Server Table

This morning at work I created a whole bunch of test tables with dummy data in them. After inserting all that data I realized I created the table with the wrong name. So instead of dropping and recreating everything, I used the “sp_rename” stored procedure. The syntax is as follows:

exec sp_rename ‘old Table name’, ‘new Table name’

It’s pretty simple and easy to execute.

One thing to note according to Microsoft (see image below)

sp_rename cautionIf you plan on using sp_rename make sure all your objects (stored procedures, tirggers, functions, etc.) reference the new name or they will break.

How to Read SQL Server Error Log Using sp_readerrorlog

I had an incident at work where I had to analyze the SQL Server error log. I was looking for a specific piece of information and so I thought I could just open the error log in notepad and find what I was looking for with the good old “CTRL+F.” That just took longer due to the confusing format. Below is a better and faster way.

The script below uses the sp_readerrorlog undocumented store procedure. You can replace the two parameters with whatever search string you’re looking for.

--Read error log stored procedure

--First Param: 0 = current, 1 = Archive #1, 2 = Archive #2, etc.
--Second Param: 1 or NULL = error log, 2 = SQL Agent log
--Third Param: String one you want to search for
--Fourth Param: String two you wan to search for to further refine results


--Below proc will search current error log for string 'recovery' and 'option'
exec sp_readerrorlog 0, 1, 'recovery', 'option'

How to Create a DISA STIG SQL Server Trace

If you work for the government and have to create a SQL Server Trace that monitors all the trace event IDs that come in the DISA STIGs then you have come to right place! (I feel like I’m hosting an infomercial)

I have implemented this script on numerous database servers during my professional career. It’s very straight-forward. There are a few places that you need to provide parameter information, for example the drive location you want SQL Server to save your trace files to. Also, this script is setup to override and shut down the server if for some reason SQL Server Trace has a problem writing to the trace file. (per STIG requirement)

You can run the script below and be assured that your SQL Server Trace suffices the STIG requirement. If you’d like to know how to stop and delete a SQL Server Trace, read about it here.

USE [master]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[sp_create_STIG_trace]
-- Create the trace
AS
    -- Declare local variables
  declare @rc int
  declare @on bit
  declare @scriptname nvarchar(50)
  declare @tracefile nvarchar(256)
  declare @maxfilesize bigint
  declare @filecount int
  declare @traceid int 

  set @maxfilesize =100
  set @filecount =100

  -- Trace file name
  set @scriptname = 'cc_STIG_trace_' + REPLACE(REPLACE(CONVERT( varchar(50), getdate(),126), ':', ''), '.','')

  set @tracefile = 'E:\Trace\' + @scriptname ---need to modify the directory path

  --- Create the trace
    	--- Second parameter 6 indicates: TRACE_FILE_ROLLOVER(2)+SHUTDOWN_ON_ERROR(4)
  
  exec @rc = sp_trace_create @traceid OUTPUT, 6, @tracefile, @maxfilesize, NULL , @filecount
  
  IF (@rc != 0) 
  begin
    GOTO PROC_ERROR
  end

  -- Add Trace Events
  set @on = 1
 
  -- Audit Login
  exec sp_trace_setevent @TraceID, 14, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 14, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 14, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 14, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 14, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 14, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 14, 31, @on  -- Error
  exec sp_trace_setevent @TraceID, 14, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 14, 64, @on  -- SessionLoginName
   
  -- Audit Logout
  exec sp_trace_setevent @TraceID, 15, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 15, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 15, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 15, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 15, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 15, 31, @on  -- Error
  exec sp_trace_setevent @TraceID, 15, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 15, 64, @on  -- SessionLoginName

  -- Audit Server Starts and Stops Event Class
  exec sp_trace_setevent @TraceID, 18, 10, @on  -- ApplicationName	
  exec sp_trace_setevent @TraceID, 18, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 18, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 18, 21, @on  -- EventSubClass 
  exec sp_trace_setevent @TraceID, 18, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 18, 64, @on  -- SessionLoginName

  -- Audit Login Failed
  exec sp_trace_setevent @TraceID, 20, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 20, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 20, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 20, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 20, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 20, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 20, 31, @on  -- Error
  exec sp_trace_setevent @TraceID, 20, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 20, 64, @on  -- SessionLoginName

  -- SP:Starting (Ex)
 	exec sp_trace_setevent @TraceID, 42, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 42, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 42, 12, @on  -- SPID
  exec sp_trace_setevent @TraceID, 42, 14, @on  -- StartTime
 	exec sp_trace_setevent @TraceID, 42, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 42, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 42, 64, @on  -- SessionLoginName

  -- SP:Completed (Ex)
  exec sp_trace_setevent @TraceID, 43, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 43, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 43, 12, @on  -- SPID
  exec sp_trace_setevent @TraceID, 43, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 43, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 43, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 43, 64, @on  -- SessionLoginName

  -- Audit Database Scope GDR Event
  exec sp_trace_setevent @TraceID, 102, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 102, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 102, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 102, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 102, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 102, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 102, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 102, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 102, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 102, 64, @on  -- SessionLoginName
   
  -- Audit Schema Object GDR Event
  exec sp_trace_setevent @TraceID, 103, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 103, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 103, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 103, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 103, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 103, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 103, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 103, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 103, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 103, 59, @on  -- ParentName
  exec sp_trace_setevent @TraceID, 103, 64, @on  -- SessionLoginName

  -- Audit AddLogin Event
  exec sp_trace_setevent @TraceID, 104, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 104, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 104, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 104, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 104, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 104, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 104, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 104, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 104, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 104, 64, @on  -- SessionLoginName

  -- Audit Login GDR Event
  exec sp_trace_setevent @TraceID, 105, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 105, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 105, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 105, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 105, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 105, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 105, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 105, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 105, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 105, 64, @on  -- SessionLoginName
   
  -- Audit Login Change Property Event
  exec sp_trace_setevent @TraceID, 106, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 106, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 106, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 106, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 106, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 106, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 106, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 106, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 106, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 106, 64, @on  -- SessionLoginName
   
  -- Audit Login Change Password Event
  exec sp_trace_setevent @TraceID, 107, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 107, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 107, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 107, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 107, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 107, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 107, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 107, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 107, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 107, 64, @on  -- SessionLoginName
   
  -- Audit Add Login to Server Role Event
  exec sp_trace_setevent @TraceID, 108, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 108, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 108, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 108, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 108, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 108, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 108, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 108, 38, @on  -- RoleName
  exec sp_trace_setevent @TraceID, 108, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 108, 64, @on  -- SessionLoginName

  -- Audit Add DB User Event
  exec sp_trace_setevent @TraceID, 109, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 109, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 109, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 109, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 109, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 109, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 109, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 109, 38, @on  -- RoleName
  exec sp_trace_setevent @TraceID, 109, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 109, 64, @on  -- SessionLoginName
   
  -- Audit Add Member to DB Role Event
  exec sp_trace_setevent @TraceID, 110, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 110, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 110, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 110, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 110, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 110, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 110, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 110, 38, @on  -- RoleName
  exec sp_trace_setevent @TraceID, 110, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 110, 64, @on  -- SessionLoginName

  -- Audit Add Role Event
  exec sp_trace_setevent @TraceID, 111, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 111, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 111, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 111, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 111, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 111, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 111, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 111, 38, @on  -- RoleName
  exec sp_trace_setevent @TraceID, 111, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 111, 64, @on  -- SessionLoginName
   
  -- Audit App Role Change Password Event
  exec sp_trace_setevent @TraceID, 112, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 112, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 112, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 112, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 112, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 112, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 112, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 112, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 112, 64, @on  -- SessionLoginName

  -- Audit Statement Permission Event
  exec sp_trace_setevent @TraceID, 113, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 113, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 113, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 113, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 113, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 113, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 113, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 113, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 113, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 113, 64, @on  -- SessionLoginName
   
  -- Audit Schema Object Access Event
  exec sp_trace_setevent @TraceID, 114, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 114, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 114, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 114, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 114, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 114, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 114, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 114, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 114, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 114, 59, @on  -- ParentName
  exec sp_trace_setevent @TraceID, 114, 64, @on  -- SessionLoginName
   
  -- Audit Backup/Restore Event
  exec sp_trace_setevent @TraceID, 115, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 115, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 115, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 115, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 115, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 115, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 115, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 115, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 115, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 115, 64, @on  -- SessionLoginName
   
  -- Audit DBCC Event
  exec sp_trace_setevent @TraceID, 116, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 116, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 116, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 116, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 116, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 116, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 116, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 116, 64, @on  -- SessionLoginName
   
  -- Audit Change Audit Event
  exec sp_trace_setevent @TraceID, 117, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 117, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 117, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 117, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 117, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 117, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 117, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 117, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 117, 64, @on  -- SessionLoginName

  -- Audit Object Derived Permission Event
  exec sp_trace_setevent @TraceID, 118, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 118, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 118, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 118, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 118, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 118, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 118, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 118, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 118, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 118, 64, @on  -- SessionLoginName

  -- Audit Database Management Event
  exec sp_trace_setevent @TraceID, 128, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 128, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 128, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 128, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 128, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 128, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 128, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 128, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 128, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 128, 64, @on  -- SessionLoginName
   
  -- Audit Database Object Management Event
  exec sp_trace_setevent @TraceID, 129, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 129, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 129, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 129, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 129, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 129, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 129, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 129, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 129, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 129, 64, @on  -- SessionLoginName
   
  -- Audit Database Principal Management Event
  exec sp_trace_setevent @TraceID, 130, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 130, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 130, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 130, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 130, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 130, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 130, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 130, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 130, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 130, 64, @on  -- SessionLoginName
   
  -- Audit Schema Object Management Event
  exec sp_trace_setevent @TraceID, 131, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 131, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 131, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 131, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 131, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 131, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 131, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 131, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 131, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 131, 59, @on  -- ParentName
  exec sp_trace_setevent @TraceID, 131, 64, @on  -- SessionLoginName
   
  -- Audit Server Principal Impersonation Event
  exec sp_trace_setevent @TraceID, 132, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 132, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 132, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 132, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 132, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 132, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 132, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 132, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 132, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 132, 64, @on  -- SessionLoginName
   
  -- Audit Database Principal Impersonation Event
  exec sp_trace_setevent @TraceID, 133, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 133, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 133, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 133, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 133, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 133, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 133, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 133, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 133, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 133, 64, @on  -- SessionLoginName
   
  -- Audit Server Object Take Ownership Event
  exec sp_trace_setevent @TraceID, 134, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 134, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 134, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 134, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 134, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 134, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 134, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 134, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 134, 64, @on  -- SessionLoginName
   
  -- Audit Database Object Take Ownership Event
  exec sp_trace_setevent @TraceID, 135, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 135, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 135, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 135, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 135, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 135, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 135, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 135, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 135, 64, @on  -- SessionLoginName

  -- Audit Change Database Owner
  exec sp_trace_setevent @TraceID, 152, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 152, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 152, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 152, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 152, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 152, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 152, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 152, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 152, 64, @on  -- SessionLoginName
   
  -- Audit Schema Object Take Ownership Event
  exec sp_trace_setevent @TraceID, 153, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 153, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 153, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 153, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 153, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 153, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 153, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 153, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 153, 59, @on  -- ParentName
  exec sp_trace_setevent @TraceID, 153, 64, @on  -- SessionLoginName
  
  -- Audit User Error Message Event (Ex)
  exec sp_trace_setevent @TraceID, 162, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 162, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 162, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 162, 12, @on  -- SPID
  exec sp_trace_setevent @TraceID, 162, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 162, 64, @on  -- SessionLoginName

  -- Audit Server Scope GDR Event
  exec sp_trace_setevent @TraceID, 170, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 170, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 170, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 170, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 170, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 170, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 170, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 170, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 170, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 170, 64, @on  -- SessionLoginName
   
  -- Audit Server Object GDR Event
  exec sp_trace_setevent @TraceID, 171, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 171, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 171, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 171, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 171, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 171, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 171, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 171, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 171, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 171, 64, @on  -- SessionLoginName
   
  -- Audit Database Object GDR Event
  exec sp_trace_setevent @TraceID, 172, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 172, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 172, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 172, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 172, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 172, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 172, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 172, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 172, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 172, 64, @on  -- SessionLoginName
   
  -- Audit Server Operation Event
  exec sp_trace_setevent @TraceID, 173, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 173, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 173, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 173, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 173, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 173, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 173, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 173, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 173, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 173, 64, @on  -- SessionLoginName
   
  -- Audit Server Alter Trace Event
  exec sp_trace_setevent @TraceID, 175, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 175, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 175, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 175, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 175, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 175, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 175, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 175, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 175, 64, @on  -- SessionLoginName
   
  -- Audit Server Object Management Event
  exec sp_trace_setevent @TraceID, 176, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 176, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 176, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 176, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 176, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 176, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 176, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 176, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 176, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 176, 64, @on  -- SessionLoginName
   
  -- Audit Server Principal Management Event
  exec sp_trace_setevent @TraceID, 177, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 177, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 177, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 177, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 177, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 177, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 177, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 177, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 177, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 177, 64, @on  -- SessionLoginName
   
  -- Audit Database Operation Event
  exec sp_trace_setevent @TraceID, 178, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 178, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 178, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 178, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 178, 21, @on  -- EventSubClass
  exec sp_trace_setevent @TraceID, 178, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 178, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 178, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 178, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 178, 64, @on  -- SessionLoginName
   
  -- Audit Database Object Access Event (Ex)
  exec sp_trace_setevent @TraceID, 180, 1, @on  -- TextData
  exec sp_trace_setevent @TraceID, 180, 10, @on  -- ApplicationName
  exec sp_trace_setevent @TraceID, 180, 11, @on  -- LoginName
  exec sp_trace_setevent @TraceID, 180, 14, @on  -- StartTime
  exec sp_trace_setevent @TraceID, 180, 23, @on  -- Success
  exec sp_trace_setevent @TraceID, 180, 34, @on  -- ObjectName
  exec sp_trace_setevent @TraceID, 180, 35, @on  -- DatabaseName
  exec sp_trace_setevent @TraceID, 180, 40, @on  -- DBUserName
  exec sp_trace_setevent @TraceID, 180, 64, @on  -- SessionLoginName


  -- Set the trace status to start

  exec sp_trace_setstatus @TraceID, 1
  
  PRINT 'INFO: Successfully created the trace with ID ' + CAST (@traceid AS varchar(10))
  RAISERROR ('SQL Server 2008 started, CC trace process started.', 1, 1) WITH LOG

  PRINT 'Passed'	
  return (0)

PROC_ERROR:
  PRINT 'Failed'	
  return(1)

How To Find All Tables, Columns, Data Types of SQL Server Database

Recently I had to find all the tables, columns, data types, etc. from a database. Below is a thorough script that brings back all the tables, attributes, data types, whether the column allows NULLS, whether it’s a Primary Key, or a Foreign Key (and if so, the referencing table). It’s extremely useful and easy to run.

Here is the script. Just make sure to un-comment the first line and replace it with USE yourDB!

--USE DATABASE_NAME
SELECT obj.name [Table],
col.name [Attribute],
typ.name [DataType],
col.isnullable [Allow Nulls?],
CASE WHEN d.name is null
    THEN 0
    ELSE 1
END [PKey?],
CASE WHEN e.parent_object_id is null
    THEN 0
    ELSE 1
END [FKey?],
CASE WHEN e.parent_object_id is null
    THEN '-'
    ELSE g.name
END [Ref Table],
CASE WHEN h.value is null
    THEN '-'
    ELSE h.value
END [Description]
FROM sysobjects AS obj
JOIN syscolumns AS col ON obj.id = col.id
JOIN systypes AS typ ON col.xtype = typ.xtype
LEFT JOIN (SELECT  so.id,sc.colid,sc.name
      FROM    syscolumns sc
      JOIN sysobjects so ON so.id = sc.id
      JOIN sysindexkeys si ON so.id = si.id
                    AND sc.colid = si.colid
      WHERE si.indid = 1) d on obj.id = d.id and col.colid = d.colid
LEFT JOIN sys.foreign_key_columns AS e
    ON obj.id = e.parent_object_id AND col.colid = e.parent_column_id   
LEFT JOIN sys.objects as g
    ON e.referenced_object_id = g.object_id 
LEFT JOIN sys.extended_properties AS h
    ON obj.id = h.major_id AND col.colid = h.minor_id
WHERE obj.type = 'U' ORDER BY obj.name

How to Find Last Login Date of a SQL Server Login?

There are many options to find the last login date for a a SQL Server login. Even though there are awesome scripts like Adam Machanic’s “Who is Active” (download link here), sometimes you might find yourself without internet access, or perhaps at a client site that doesn’t have “Who is Active” installed and you forgot your thumb drive at home. :)

You can easily query the sys.dm_exec_sessions dmv to get the last login time of SQL Server logins. Per MSDN, the sys.dm_exec_sessions DMV,

Returns one row per authenticated session on SQL Server….it’s a server-scope view that shows information about all active user connections and internal tasks

Here’s a little script to help you out!

SELECT MAX(login_time) AS [Last Login Time], login_name [Login]
FROM sys.dm_exec_sessions
GROUP BY login_name;

Find Rogue Transactions in SQL Server

One of the developers approached me today asking why their simple SELECT SQL query was taking forever. I walked over to their desk and noticed their SQL code had a BEGIN TRAN but no COMMIT or ROLLBACK. I ran a:

SELECT @@trancount

…but that didn’t bring back anything. So then I ran:

DBCC OPENTRAN

…and it returned an open transaction with its associated SPID.

How to find rogue transactions in SQL Server

I used the KILL command to kill SPID 57 (Kill 57) and the developer’s query returned instantly.

And just in case you were wondering, the cause of the rogue transaction was a BEGIN statement that the developer ran without a COMMIT or ROLLBACK and the developer tried to access that same table in another session window.

Setup SQL Email Alert for Disk Space Usage

I had an application go kaput on me all of a sudden and that wasn’t good. I had gotten back from lunch (always happens when I get back from lunch) and was immediately approached by the Sys Admin saying that a certain web application couldn’t connect to the SQL database. He wanted me to check out why and get back to him ASAP.

So I quickly log into the database server and see that SQL server is running fine, the SQL account that the web application uses is not “locked” and the password expiration is not checked (meaning that technically the password for that SQL account should never expire). I look at the disk drives by clicking on “My Computer” in Windows Explorer and quickly saw …

“E: Drive 0 GB free of 99 GB”

We have SQL Server configured to store Audit files on the “E” drive. I went into the folder and removed the oldest month worth of audit files. That freed up about 20 GB of space.

Roughly 5 minutes later the same Sys Admin ran in my office saying, “hey! I reset the SQL Account password and it works now.”

Hmm…

I know. What a coincidence right? :)

I believe the reason why the application couldn’t connect to the database is because SQL Server couldn’t write any more audit files to the disk drive. As a result, SQL Server refused outside connections.

I wrote a script that checks whether a specific disk drive space goes below a given threshold. If so, it will send the DBAs an email alert.

Here is the script. Feel free to use/modify it however you see fit.

--create temp table for results
create table #freespace
(drive char(1),mb_free int)

--insert drive data into temp table
insert into #freespace exec sys.xp_fixeddrives

declare @subject varchar(100)
declare @profile_name varchar(25)
declare @body varchar(200)
declare @gb_free int
declare @recipients varchar(50)

--you can specify whatever drive leter
select @gb_free = (mb_free / 1024) from #freespace where drive = 'E'

--you can specify whatever number you want. I put 20GB.
if (@gb_free < 20)
 BEGIN
  SET @profile_name = 'Profile Name goes here'
  SET @recipients = 'your@email.com'
  SET @subject = 'ALERT!! E: drive is BELOW 20 GB'
  SET @body = 'Please check the E: drive! It has fallen below 20 GB of free space. There is currently ' + CONVERT(varchar,@gb_free) + ' GB of free space left on the E drive.'
  
  exec msdb.dbo.sp_send_dbmail
  @profile_name = @profile_name,
  @body = @body,
  @subject = @subject,
  @recipients = @recipients
 END
--drop temp table
drop table #freespace;
go