Wednesday, June 15, 2016

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 o.name, o.id, c.text, o.type
     FROM sysobjects o RIGHT JOIN syscomments c 
       ON o.id = c.id
     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
   /* PREVENT INSERT IF NO MATCHING KEY IN 'tblItems' */
   IF (SELECT COUNT(*) FROM inserted) !=
      (SELECT COUNT(*) FROM tblItems, inserted WHERE (tblItems.ItemID = inserted.ItemID))
       BEGIN
           RAISERROR(778441, 16, 1)
           ROLLBACK TRANSACTION
       END
   /* PREVENT INSERT IF NO MATCHING KEY IN 'tblOrders' */
   IF (SELECT COUNT(*) FROM inserted) !=
    (SELECT COUNT(*) FROM tblOrders, inserted WHERE (tblOrders.OrderID = inserted.OrderID))
       BEGIN
           RAISERROR(778450, 16, 1)
           ROLLBACK TRANSACTION
       END
   

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; 
     GO 
     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:

Prepare:
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.name, 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
Perform: 
1.         Connect to Principal, pause mirroring session:
ALTER DATABASE JTSdr SET PARTNER SUSPEND
2.         Backup JTSdr database on Principal instance, SERVERA
BACKUP DATABASE JTSdr TO DISK = 'E:\backups\JTSdr_20160601.bak'
WITH FORMAT;
3.         Connect to Principal, run CHECKDB on JTSdr, SERVERA
              USE JTSdr;
       DBCC CHECKDB
4.       Apply the patch to the Mirror, SERVERB
5.      Connect to Principal, resume mirroring session
    ALTER DATABASE JTSdr SET PARTNER RESUME
6.       Connect to Principal, perform manual fail-over so Mirror assumes Principal role
    USE master;
    ALTER DATABASE JTSdr SET PARTNER FAILOVER
7.       Connect to Principal, pause mirroring session
                  ALTER DATABASE JTSdr SET PARTNER SUSPEND
8.       Apply patch to the new Mirror, SERVERA
9.       Connect to Principal, resume mirroring session
    ALTER DATABASE JTSdr SET PARTNER RESUME
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
    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 : ) 

Query to list schedule details for SQL Server Agent Jobs

Kinda big, but this is a helpful query for listing scheduling details for SQL Server Agent jobs.  I'm doing a multi-table join between sysjobs, sysjobschedules and sysschedules, returning JobName, Enabled, Frequency, NextRunDate, RunTime and ScheduleDetails.

Here's a slice from my customer's data:



Let me know what you think.

  -- return agent job schedule details
  USE msdb;
  SELECT
    sj.Name [Job Name],
    CASE sj.enabled WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END [Enabled],
    CASE syssc.freq_type
      WHEN 1 THEN 'Once'
      WHEN 4 THEN 'Daily'
      WHEN 8 THEN 'Weekly'
      WHEN 16 THEN 'Monthly'
      WHEN 32 THEN 'Monthly relative'
      WHEN 64 THEN 'When SQLServer Agent starts' END [Frequency],
    CASE syssc.active_start_date WHEN 0 THEN NULL
      ELSE SUBSTRING(CONVERT(VARCHAR(15),syssc.active_start_date),1,4) + '/' +
       SUBSTRING(CONVERT(VARCHAR(15),syssc.active_start_date),5,2) + '/' +
       SUBSTRING(CONVERT(VARCHAR(15),syssc.active_start_date),7,2) END [NextRunDate],
    CASE LEN(syssc.active_start_time)
      WHEN 1 THEN CAST('00:00:0' + RIGHT(syssc.active_start_time,2) AS CHAR(8))
      WHEN 2 THEN CAST('00:00:' + RIGHT(syssc.active_start_time,2) AS CHAR(8))
      WHEN 3 THEN CAST('00:0' + LEFT(RIGHT(syssc.active_start_time,3),1) +':' +
 RIGHT(syssc.active_start_time,2) AS CHAR (8))
      WHEN 4 THEN CAST('00:' + LEFT(RIGHT(syssc.active_start_time,4),2) +':' +
 RIGHT(syssc.active_start_time,2) AS CHAR (8))
      WHEN 5 THEN CAST('0' + LEFT(RIGHT(syssc.active_start_time,5),1) +':' +
 LEFT(RIGHT(syssc.active_start_time,4),2) 
         +':' + RIGHT(syssc.active_start_time,2) AS CHAR (8))
      WHEN 6 THEN CAST(LEFT(RIGHT(syssc.active_start_time,6),2) +':' +
 LEFT(RIGHT(syssc.active_start_time,4),2) 
         +':' + RIGHT(syssc.active_start_time,2) AS CHAR (8)) END [RunTime],
    dbo.udf_schedule_description(syssc.freq_type,
         syssc.freq_interval, 
         syssc.freq_subday_type,
         syssc.freq_subday_interval,
         syssc.freq_relative_interval, 
         syssc.freq_recurrence_factor,
         syssc.active_start_date,
         syssc.active_end_date, 
         syssc.active_start_time,
         syssc.active_end_time) [ScheduleDetails]
  FROM
  dbo.sysjobs sj LEFT OUTER JOIN dbo.sysjobschedules sjs
           ON sj.job_id = sjs.job_id INNER JOIN dbo.sysschedules syssc
             ON sjs.schedule_id = syssc.schedule_id LEFT OUTER JOIN (
                 SELECT job_id, MAX(run_duration) [run_duration]
                 FROM dbo.sysjobhistory
                 GROUP BY job_id
                ) q1
                 ON sj.job_id = q1.job_id
  WHERE
       sjs.next_run_time = 0

UNION

  SELECT
     sj.Name [JobName],
     CASE sj.Enabled    WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END [Enabled],
     CASE syssc.freq_type
         WHEN 1 THEN 'Once'
         WHEN 4 THEN 'Daily'
         WHEN 8 THEN 'Weekly'
         WHEN 16 THEN 'Monthly'
         WHEN 32 THEN 'Monthly relative'
         WHEN 64 THEN 'When SQLServer Agent starts' END [Frequency],
     CASE sjs.next_run_date WHEN 0 THEN NULL
         ELSE SUBSTRING(CONVERT(VARCHAR(15),next_run_date),1,4) + '/' +
          SUBSTRING(CONVERT(VARCHAR(15),next_run_date),5,2) + '/' +
          SUBSTRING(CONVERT(VARCHAR(15),next_run_date),7,2) END [NextRundate],
     CASE LEN(sjs.next_run_time)
         WHEN 1 THEN CAST('00:00:0' + RIGHT(next_run_time,2) AS CHAR(8))
         WHEN 2 THEN CAST('00:00:' + RIGHT(next_run_time,2) AS CHAR(8))
         WHEN 3 THEN CAST('00:0' + LEFT(RIGHT(next_run_time,3),1) +':' +                   
            RIGHT(next_run_time,2) AS CHAR (8))
         WHEN 4 THEN CAST('00:' + LEFT(RIGHT(next_run_time,4),2)  +':' +         
            RIGHT(next_run_time,2) AS CHAR (8))
         WHEN 5 THEN CAST('0' + LEFT(RIGHT(next_run_time,5),1) +':' +
            LEFT(RIGHT(next_run_time,4),2) 
                +':' + RIGHT(next_run_time,2) AS CHAR (8))
         WHEN 6 THEN CAST(LEFT(RIGHT(next_run_time,6),2) +':' +
            LEFT(RIGHT(next_run_time,4),2) 
                +':' + RIGHT(next_run_time,2) AS CHAR (8)) END [RunTime],
         syssc.freq_interval, 
         syssc.freq_subday_type,
         syssc.freq_subday_interval,
         syssc.freq_relative_interval, 
         syssc.freq_recurrence_factor,
         syssc.active_start_date,
         syssc.active_end_date, 
         syssc.active_start_time,
         syssc.active_end_time) [ScheduleDetails]
   FROM
      dbo.sysjobs sj LEFT OUTER JOIN dbo.sysjobschedules sjs
        ON sj.job_id = sjs.job_id INNER JOIN dbo.sysschedules syssc
          ON sjs.schedule_id = syssc.schedule_id LEFT OUTER JOIN (
               SELECT job_id, MAX(run_duration) [run_duration]
               FROM dbo.sysjobhistory
               GROUP BY job_id
               ) q1
              ON sj.job_id = Q1.job_id
   WHERE
      sjs.next_run_time <> 0
   ORDER BY
      RunTime



Tuesday, May 10, 2016

How to find all SQL Server instances on your network?

How do you discover all of the different SQL Server instances on your network?  We'd like to think we know our inventory, and are managing it well, but... Different developers, different software installations, or just different days of the week.  :-)  There are often new instances popping up on the network.  I believe this is called 'SQL Server Sprawl', where large numbers of SQL Server instances are installed in the domain, many of which are not needed, not managed... and very often not licensed. But that's another story.

Anyway, how do you discover all of the different SQL Server instances on the network?  SQL Ping, the MSFT Assessment and Planning Toolkit, Powershell, OSQL, and many more.  I'm wiling to bet this post is one of the fastest and easiest ways to do the deed.

Open a CMD prompt, maneuver to whatever directory you choose, and type the following:

         SQLCMD -L >SQLservers.txt

         Press ENTER.

That's it.  Seconds later (depending on how big your network is), you've output all discovered SQL Server instances on your network to the targeted .txt file.  

Seriously. 











It's that easy.




Short and sweet... leaving you all kinds of time to cleanup that SQL Server Sprawl.

Take a look at this for more details on the sqlcmd utility:
    https://msdn.microsoft.com/en-us/library/ms162773.aspx