Friday, September 2, 2016

Check the SQL Server Database Compatibility Level

Just a quick script to check your database compatibility level... and change it, if you need to.  This statement will bring back the compatibility levels for each database in the system:

     /* query the db compatibility level */
          name [database],
          compatibility_level ,
          [version] =
          CASE compatibility_level
             WHEN 65  THEN 'SQL Server 6.5'
             WHEN 70  THEN 'SQL Server 7.0'
             WHEN 80  THEN 'SQL Server 2000'
             WHEN 90  THEN 'SQL Server 2005'
             WHEN 100 THEN 'SQL Server 2008/R2'
             WHEN 110 THEN 'SQL Server 2012'
             WHEN 120 THEN 'SQL Server 2014'
             WHEN 130 THEN 'SQL Server 2016' END
And when you need to change it:

     /* change db compatibility level */
     ALTER DATABASE YourDatbaseName

Be wary, though, about changing the compatibility level.  Often times by doing so, you will be unable to take advantage of certain features. Take a look at this MSDN reference for more details on the differences between higher/lower compatibility levels:     

Friday, July 29, 2016

Using a SQL Server Logon Trigger

One of my customers has an instance where many people log in as sa. We're planning to change the password and rename the sa login, but we first need to know who is using it, and for what. To do this, I've put together an sa Logon Trigger. While I typically avoid triggers, I believe this one is very manageable because it is specific to only one login, and it is only collecting details from each login attempt.

First I create a LogonAudit table to collect the logon details.

     USE DBA;
     CREATE TABLE LogonAudit
      LogonTime datetime,
      HostName varchar(50),
      ProgramName varchar(500),
      LoginName varchar(50),
      OriginalLoginName varchar(50),
      ClientHost varchar(50)
     ) ON [DBA_Data]

Now for the trigger.

CREATE TRIGGER tr_LogonTrigger
Used with 'sa logon audit', to determine who is logging in with sa, when and from where.
Auth:  Me
Date:  7/28/2016  
@LogonTriggerData xml,
       @EventTime datetime,
       @LoginName varchar(50),
       @ClientHost varchar(50),
       @LoginType varchar(50),
       @HostName varchar(50),
       @AppName varchar(500)

  SET @LogonTriggerData = eventdata()
  SET @EventTime = @LogonTriggerData.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime')
  SET @LoginName = @LogonTriggerData.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(50)')
  SET @ClientHost = @LogonTriggerData.value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(50)')
  SET @HostName = HOST_NAME()
  SET @AppName = APP_NAME()

  --- Add condition to log only succesful sa logins
  IF((ORIGINAL_LOGIN() = 'sa'))
  INSERT INTO DBA..LogonAudit (
       LogonTime,HostName,ProgramName,LoginName,OriginalLoginName,ClientHost )


How to test it?  Easy. Launch SSMS and open a query window.  Look at the lower right, you should see you're logged with your domain login.  Right click the query screen, choose the 'Connection' option, and then 'Change Connection...'.  Here you will change the authentication to 'SQL Server Authentication', then input the sa login and password and hit 'Connect'.  That's it!  That single action was a logon attempt by the sa login, and it should have triggered an insert into your LogonAudit table.  

This is what I captured in my local instance:

But wait.  We logged in one time, and we have more than one entry recorded in our LogonAudit table.  Why?  This happens because there are multiple SQL Server Services running in parallel.  If you were to go in and stop all SQL Server Services except for SQL Server (MSSQLSERVER), then you would only capture one entry in the table for each login attempt.

Maybe you want to disable it for some reason?  Rather than deleting it, you can just DISABLE/ENABLE with these statements:


See more details here regarding the SQL Server Logon Trigger:

Friday, June 24, 2016


I was putting together a procedure earlier for a fairly common ETL need -- remove records from one table that no longer exist in another table.  A simple WHERE NOT EXISTS, right?   Well, I caught myself in a syntax error, so I thought I would post it here for all of you.

In this example, we want to delete from CustomerSales where CustomerID no longer exists in the Customers table:

             DELETE dbo.CustomerSales a
             WHERE NOT EXISTS(
                    SELECT 1 FROM dbo.Customers b
                    WHERE a.CustomerID = b.CustomerID)
Pretty close, but no cigar.  Try that, and you'll receive this error:

                   Msg 102, Level 15, State 1, Line 3
                       Incorrect syntax near 'a'.

This is the correct syntax:

             DELETE a
             FROM dbo.CustomerSales a
             WHERE NOT EXISTS(
                    SELECT * FROM dbo.Customers b
                    WHERE a.CustomerID = b.CustomerID)

Hopefully useful for somebody!

Sunday, June 19, 2016

Msg 1422, The mirror server instance is not caught up to the recent changes to database....

I ran into this error a couple weeks ago when I was applying a service pack to a couple of mirrored SQL Servers. I had just patched the Mirror and resumed the mirroring session, but when I tried to perform a manual failover of the Mirror to the Principal, I received this message:

We'll see this if we try to failover the mirroring session when the databases are not in a SYNCHRONIZED state.  Makes sense.. if you take a look at this post, you'll see the step before the manual failover was to resume the Mirror session.  It just wasn't completely synchronized yet.

To confirm how far behind the mirror was, I ran this query on both the Principal and the Mirror.  

         DB_NAME(database_id) [Database],

The 'mirroring_failover_lsn' is the log sequence number that the Mirror partners use as the point of reconciliation. I waited a couple minutes and tried again.  The failover completed successfully because the LSN was now the same on both the Principal and Mirror instance.

See this for more detail on sys.database_mirroring and the LSN:

And this for instructions on performing the manual failover via tSQL:

Wednesday, June 15, 2016

Add DEFAULT constraint, or add new column with DEFAULT

On many occasions over the years, I've enabled a DATETIME attribute on a table, but allowed SQL to populate it for me, with a GETDATE() DEFAULT constraint.  This can be very helpful for monitoring application data flow, and for troubleshooting or debugging.  To know exactly when a record was written, is a very good thing, I can assure you.

You may already have a date field, but there is no constraint.  Just use this to add the constraint to your table:  

   ALTER TABLE [dbo].[YourTable]
   ADD CONSTRAINT [df_YourTable_DateField]
   DEFAULT (GETDATE()) FOR [DateField];

Or, this one can be used to both add the new field, and enable the constraint:

   ALTER TABLE [dbo].[YourTable]
   CONSTRAINT [df_YourTable_InsertTime] DEFAULT {GETDATE());

I usually use 'InsertTime' or 'DateCreated' to name attributes like this, because I think it very intuitively suggests what the value is used for. 

Error # was raised, but no message with that error number was found in sys.messages

I received this alert from one of my customer's servers today:

   Msg 18054, Level 16, State 1, Line 1
   SQL Server Alert System: 'Severity 016' occurred on \\SQLSERVER11
   DESCRIPTION: Error 778441, severity 16, state 1 was raised, but no message
   with that error number was found in sys.messages. If error is larger than 50000,
   make sure the user-defined message is added using sp_addmessage.

Odd.  I didn't know who or what is trying to raise an error with a msg_id that doesn't exist yet, so I ran this to search all database objects for any reference to 778441:

     SELECT,, c.text, o.type
     FROM sysobjects o RIGHT JOIN syscomments
       ON =
     WHERE c.text LIKE '%778441%'

It returned to me this trigger:

If we look at the trigger definition, you can see where the unknown msg_id's are being called:

   CREATE TRIGGER [dbo].[tblOrderItems_ITrig] ON [dbo].[tblOrderItems] FOR INSERT AS
   IF (SELECT COUNT(*) FROM inserted) !=
      (SELECT COUNT(*) FROM tblItems, inserted WHERE (tblItems.ItemID = inserted.ItemID))
           RAISERROR(778441, 16, 1)
   IF (SELECT COUNT(*) FROM inserted) !=
    (SELECT COUNT(*) FROM tblOrders, inserted WHERE (tblOrders.OrderID = inserted.OrderID))
           RAISERROR(778450, 16, 1)

Take a look in sys.messages to confirm that they don't exist:
     SELECT * FROM sys.messages WHERE message_id IN (778441,778450)

What is the fix?  I just run this to create two new user defined error messages using the numeric identifiers in the trigger:

     USE master; 
     EXEC sp_addmessage
        @msgnum = 778441, @severity = 16, @msgtext = 'There is no matching key in
        tblItems, the insert may not be performed.'@lang = 'us_english', 
        @with_log = 'TRUE';

     EXEC sp_addmessage
        @msgnum = 778450, @severity = 16, @msgtext = 'There is no matching key in
        tblOrders, the insert may not be performed.'@lang = 'us_english', 
        @with_log = 'TRUE';

With these new error messages, the msg_id numbers are now known, and the sev 16 alert goes away.  

See this for more details on sp_addmessage:

Thursday, June 9, 2016

How to apply a service pack to Mirrored SQL Servers

I patched a set of Mirrored servers last week, and I wanted to post the sequence of events here. Hope to help somebody else who needs to do the same.  To minimize downtime, I used a 'rolling' upgrade.  Very simply, this is the upgrade of the Mirror instance, followed by a manual fail-over, and then the upgrade of the new mirror server, formerly the Principal instance.

Say the servers are SERVERA and SERVERB and the mirrored database is JTSdr.  These are my steps to prepare for, and then to apply SQL Server 2008 sp4:

1.       Script dependencies to file from both instances
a)      Configuration settings
     SELECT * FROM sys.configurations ORDER BY name;
b)      Startup parameters
     SELECT registry_key,value_name,value_data
     FROM sys.dm_server_registry
     WHERE registry_key LIKE '%MSSQLServer\Parameters';
c)       Linked Server definition, if exists
     SELECT a.* FROM sys.servers a LEFT OUTER JOIN sys.linked_logins b
       ON b.server_id = a.server_id LEFT OUTER JOIN sys.server_principals c
         ON c.principal_id = b.local_principal_id
d)      SQL Server Agent Job definition
      SQL Server Agent\Jobs\ right click each job\Script Job as CREATE To File
e)      ALL SQL Server logins, with corresponding database users
           ...back soon on this one
f)       Mirror Session definition 
  SELECT, d.database_id, m.mirroring_role_desc,
  m.mirroring_state_desc, m.mirroring_safety_level_desc,
  m.mirroring_partner_name, m.mirroring_partner_instance,
  m.mirroring_witness_name, m.mirroring_witness_state_desc
  FROM sys.database_mirroring m JOIN sys.databases d
    ON m.database_id = d.database_id
  WHERE mirroring_state_desc IS NOT NULL
1.         Connect to Principal, pause mirroring session:
2.         Backup JTSdr database on Principal instance, SERVERA
BACKUP DATABASE JTSdr TO DISK = 'E:\backups\JTSdr_20160601.bak'
3.         Connect to Principal, run CHECKDB on JTSdr, SERVERA
              USE JTSdr;
4.       Apply the patch to the Mirror, SERVERB
5.      Connect to Principal, resume mirroring session
6.       Connect to Principal, perform manual fail-over so Mirror assumes Principal role
    USE master;
7.       Connect to Principal, pause mirroring session
8.       Apply patch to the new Mirror, SERVERA
9.       Connect to Principal, resume mirroring session
10.   Analyze logs, validate patch application success
Review SQL Server Error Log, Windows Application Event Log, and a Summary.txt and Detail.txt file at this location:        
      C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log

Assuming success, the Final result in the Summary.txt file be one of these:
    Passed but reboot required, see logs for details

Of course, SELECT @@VERSION  will now return this:

Microsoft SQL Server 2008 (SP4) - 10.0.6000.29 (Intel X86)   Sep  2 2014 22:55:13 Copyright (c) 1988-2008 Microsoft Corporation  Standard Edition on Windows NT 5.2 <X86> (Build : )