Restoring MSDB causes Log Shipping Failures

My situation is as follows, I have created a new Staging Server; we want everything to be as close as possible to our Production OLAP server, including the Job System...how best to do this?

I tired playing around with creating a linked server and copying stuff from msdb from primary to staging, but eventually I ran into problems with ID's and just couldn't make it work, so I decided to simply copy the msdb database from my OLAP server and restore it on my Staging server. Sounds simple right? Well, there were a number of problems that I ran into, specifically with regards to Log Shipping; with some help from Google and Twitter, I was able to resolve the issues and wanted to post the fixes to help any of you that run into the same issues.

FYI, I'm running SQL Server 2008 R2

First Error: Log Shipping jobs show a status of "Suspended" and I can't get them to do anything.
Fix: Thanks to http://www.sqlservercentral.com/Forums/Topic8379-7-1.aspx and user TVR, I found the answer; I needed to run:

use msdb
go
delete from msdb.dbo.syssubsystems
exec msdb.dbo.sp_verify_subsystems 1
go

And Restart the service.

Next problem: Log Shipping Copy's work fine, but Restores fail, saying "Error: Could not find a log backup file that could be applied to secondary database". This time help came from Twitter, where user @AngryPets a.k.a Michael K. Campbell pointed me in the direction of the msdb..log_shipping_monitor_secondary table. It took quite a bit of playing with it and the associated tables to figure out what needed to be reset/changed, but I was finally able to get it to work by doing this:

/*******************************
** Created By: Eric Zierdt **
** ericzierdt@gmail.com **
** ericemployed.blogspot.com **
*******************************/
UPDATE msdb.dbo.log_shipping_monitor_secondary
SET secondary_server = '[Staging_Server_Name]'
 , last_restored_file = NULL -- Need to get rid of last restore data
WHERE secondary_server = '[Production_Server_Name]'

UPDATE msdb.dbo.log_shipping_secondary
SET  monitor_server = '[Staging_Server_Name]'
WHERE monitor_server = '[Production_Server_Name]'

Simple enough, but took me forever to figure out.

Hope this helps, if so, drop me a comment.

Enable/Disable jobs

This post will probably not have mass appeal, but it's something I wrote recently that I find of value. The situation I was working with is that on our OLAP server, we have a number of "Invoke" agent jobs that fire off ever 2 minutes which go out and look to see if we need to process run our ETL's and process our cube. When we are doing releases or work on ETL's we want to disable all the Invoke jobs so they don't start. I'm sure you can think of other situations where this might be necessary outside of cube processing.

So one way to do this (arguably the faster way) is to open your job manager and highlight the invoke jobs and right-click and choose disable. But what if you have a few invokes disabled for one reason or another? You'll have to either remember which ones were disabled, or make a list, and make sure you don't re-enable those when you are done. Or, what if you want to wait for your current Invoke to end before you do the disable (not necessary, but what if), or you want to do it at a specific time of day?

I came up with a script that I put into two agent jobs to facilitate this. These can be scheduled and work pretty well.

The first job, creates a storage table, stores the id's for the enabled jobs, then disables them. (It checks to see if the job exists first and if so, error's out)

/*******************************
** Created By: Eric Zierdt   **
** ericzierdt@gmail.com      **
** ericemployed.blogspot.com **
*******************************/

IF OBJECT_ID('msdb.dbo.ActiveInvokeJobs') IS NULL
BEGIN
 CREATE TABLE FRZ_ADMINDB.dbo.ActiveInvokeJobs (
    job_id UNIQUEIDENTIFIER
  , [Name] VARCHAR(255)
 )
 INSERT INTO msdb.dbo.ActiveInvokeJobs
 ( job_id, [Name])
 SELECT  job_id
     ,[Name]
 FROM    msdb.dbo.sysjobs S
 WHERE   enabled = 1
   AND name LIKE '%invoke%'

 UPDATE msdb.dbo.sysjobs
 SET  enabled = 0
 WHERE job_id IN (
    SELECT  job_id
    FROM msdb.dbo.ActiveInvokeJobs
   )
END
ELSE
BEGIN
 RAISERROR ('Storage Table Already Exists.',16,1);
END

The next agent job checks to see if the storage table exists, and if so enables the disabled jobs, then deletes the storage table.

/*******************************
** Created By: Eric Zierdt   **
** ericzierdt@gmail.com      **
** ericemployed.blogspot.com **
*******************************/

IF OBJECT_ID('msdb.dbo.ActiveInvokeJobs') IS NOT NULL
BEGIN
 UPDATE msdb.dbo.sysjobs
 SET  enabled = 1
 WHERE job_id IN (
    SELECT  job_id
    FROM msdb.dbo.ActiveInvokeJobs
   )

 DROP TABLE msdb.dbo.ActiveInvokeJobs

END
ELSE
BEGIN
 RAISERROR ('Storage Table Does Not Exist.',16,1);
END

Sync Config Settings between Servers

I'm setting up a new Staging Environment and am trying to find ways we can sync Staging with our production environment. My current task is to Sync the configurations and settings. Here is the script I came up with for the Config Settings. Hope it's of use.

/***************************************************
 ** Title:  Sync Config Settings between Servers  **
 ** Author: Eric Zierdt                           **
 ** Date:   03/10/2011                            **
 ***************************************************/

/**************************************
 ** STEP 1: Turn on Advanced Options **
 **************************************/
sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO


/*******************************************
 ** STEP 2: Populate the values from Prod **
 *******************************************/
-- SELECT * INTO #TMP_PROD_Configs FROM ProductionServer.msdb.sys.configurations C


/*****************************************
 ** STEP 3: Run Cursor and copy results **
 *****************************************/
DECLARE @SQL VARCHAR(MAX)
  ,@name VARCHAR(300)
  ,@value SQL_VARIANT

DECLARE SYNC_CURSOR CURSOR FOR
 SELECT TOC.name, TOC.value
 FROM sys.configurations C
 LEFT JOIN #TMP_PROD_Configs TOC ON C.configuration_id = TOC.configuration_id
 WHERE C.value <> TOC.VALUE
   AND C.configuration_id NOT IN (1543,1544)

OPEN SYNC_CURSOR
FETCH NEXT FROM SYNC_CURSOR 
INTO @name, @value
WHILE @@FETCH_STATUS = 0
BEGIN

 SET @SQL = '
sp_configure ''' + @Name + ''', ' + CAST(@Value AS VARCHAR(25)) + '
GO
RECONFIGURE
GO
'
PRINT @SQL

 FETCH NEXT FROM SYNC_CURSOR 
 INTO @name, @value
END
CLOSE SYNC_CURSOR 
DEALLOCATE SYNC_CURSOR


/*************************************************
 ** STEP 4: Paste results below, review and run **
 *************************************************/





/**********************************************
 ** STEP 5: Be good and drop your temp table **
 **********************************************/
DROP TABLE #TMP_PROD_Configs


/***************************************
 ** STEP 6: Turn off Advanced Options **
 ***************************************/
sp_configure 'show advanced options', 0
GO
RECONFIGURE
GO


/***************************************
 ** Query to compare values if needed **
 ***************************************/
SELECT C.configuration_id, C.name, C.value,C.value_in_use, C.description
  , TOC.configuration_id, TOC.name, TOC.value,TOC.value_in_use, TOC.description
FROM sys.configurations C
LEFT JOIN #TMP_PROD_Configs TOC ON C.configuration_id = TOC.configuration_id
WHERE C.value <> TOC.VALUE
  AND C.configuration_id NOT IN (1543,1544)

Getting Object Counts

Recently I was reporting a bug I noticed with a 3rd party tool, the support group asked me to send them "the number of instances, number of databases per instance, total number of tables and total number of indexes." Now I knew the number of instances I was monitoring, and I knew the approximate number of tables (perhaps a good DBA should know this number, not sure...do other DBA's out there with larger servers know the exact number? comment below, I'm interested), but I had no idea how many indexes. So I wrote a little script to give me this number and as I like to do, I'm making it available to the community, hope this helps you...if you find use of it (even years in the future) post below.

/***********************************
 ** Author: Eric Zierdt           **
 ** Date:   4/21/2011             **
 ** Email:  eric.zierdt@gmail.com **
 ***********************************/

DECLARE @CountAll AS TABLE (CntName VARCHAR(100), Cnt INT)

/*******************************************
 ** GET COUNT OF ALL NON-SYSTEM DATABASES **
 *******************************************/
INSERT INTO @CountAll
SELECT 'TotalDBs',COUNT(1) AS TotalDBs
FROM sys.databases D
WHERE owner_sid <> '0x01'


/******************************
 ** GET COUNT OF ALL TABLES  **
 ** FOR NON-SYSTEM DATABASES **
 ******************************/
DECLARE @CountTable AS TABLE (DB VARCHAR(100),CNT INT)
DECLARE @SQL VARCHAR(MAX)
DECLARE @DBName VARCHAR(128)
SET @DBName = ''
WHILE @DBName < (
 SELECT MIN(name) 
 FROM sys.databases
 WHERE name > @DBName 
   AND name NOT IN ('master','model','msdb','tempdb')
)
BEGIN
 SELECT @DBName = MIN(NAME) FROM sys.databases WHERE name > @DBName AND NAME NOT IN ('master','model','msdb','tempdb')
 SET @SQL = '
  USE [' + @DBName + ']
  SELECT ''' + @DBName + ''' AS DBName
    ,COUNT(1) AS Cnt
  FROM   sys.tables
 '
 INSERT INTO @CountTable
 EXEC (@SQL)
END

INSERT INTO @CountAll
SELECT 'TotalTables',SUM(CNT) AS TotalTables 
FROM @CountTable

/*******************************
 ** GET COUNT OF ALL INDEXES  **
 ** FOR NON-SYSTEM DATABASES  **
 *******************************/
DECLARE @IndexTable AS TABLE (DB VARCHAR(100),TableName VARCHAR(100), CNT INT)
SET @DBName = ''
WHILE @DBName < (
 SELECT MIN(name) 
 FROM sys.databases
 WHERE name > @DBName 
   AND name NOT IN ('master','model','msdb','tempdb')
)
BEGIN
 SELECT @DBName = MIN(NAME) FROM sys.databases WHERE name > @DBName AND NAME NOT IN ('master','model','msdb','tempdb')
 SET @SQL = '
  USE [' + @DBName + ']
  SELECT ''' + @DBName + ''' AS DBName
    ,OBJECT_NAME(I.object_id) AS TableName
    , COUNT(1) AS Cnt
  FROM sys.indexes I
  JOIN sys.tables T ON I.object_id = T.object_id
  WHERE I.name IS NOT NULL
  GROUP BY OBJECT_NAME(I.object_id)
 '
 --PRINT @SQL
 INSERT INTO @IndexTable
 EXEC (@SQL)
END

INSERT INTO @CountAll
SELECT 'TotalIndexes',SUM(CNT) AS TotalIndexes 
FROM @IndexTable

SELECT * 
FROM @CountAll

GUIDS mess up defragmenting

I'm sure most of you know already that the use of GUIDS or UniqueIdentifiers is messy for doing defragmenting; because they are not sequential, they tend to cause problems for indexes and while you can decrease your FillFactor, I found that I'd rather not defrag an index only to find it at 99% fragmented again within a few hours.

I wrote a little script to help me identify the indexes that have an included column which is of type UniqueIdentifier.

-- CHANGE THE DB TO THE ONE YOU WANT TO SEE INDEXES WITH GUIDS IN THEM

;WITH LIST AS (
 SELECT p.object_id
   ,OBJECT_NAME(p.object_id) AS TableName
   ,I.name AS IndexName
   ,C.Name AS ColName
   ,T.name AS DataType
 FROM sys.partitions P
 JOIN sysobjects O ON P.object_id = object_id(O.name)
 JOIN sys.indexes I ON I.object_id = P.object_id AND I.index_id = P.index_id
 JOIN sys.index_columns IC ON p.object_id = IC.object_id AND p.index_id = IC.index_id
 JOIN sys.columns C ON P.object_id = C.object_id AND IC.column_id = C.column_id
 JOIN sys.types T ON c.system_type_id = T.system_type_id
 WHERE T.name = 'uniqueidentifier'
   AND P.partition_number = 1
   AND O.xtype='U'
)
SELECT DISTINCT *
FROM LIST
ORDER BY TableName,ColName

Titles

I'm sure we've all seen them before, the silly title that some people get that sound so ridiculous, like at my last company the front desk person was the Director of First Impressions. I recently saw that one of our business people was on a YouTube video showing off our new product and her title was "Brand Ambassador". For some reason this struck me as very silly, so now we have brand managers and brand ambassadors...why do people find it so necessary to find these odd title for themselves. So I've come up with a list of possible titles for myself, I'll see if I can get my manager to allow me to use one of them. I've come up with a good list I think, but feel free to post a comment with some other good ones a DBA or BI Developer might consider.

Director of Fluff
Director of Shock and Awe
"Behind the Scenes Magic" Technician
Data Sorcerer
Data Mage
Data Defender
High Priest of the Data arts
His Royal Majesty
Knight of the Data Table
BI Crew Member
Developer of the BI Arts
The Keymaster
Data Doctor
Report Guru
Cube Guardian
Data Ambassador
Keeper of all things BI
Data Librarian
The Undersecretary of Data
The Sultan of SQL
The Duke of Data

Second to last Friday in Month

I was tasked with creating a SQL Agent Job that would run on the 2nd to last Friday every month; but how to do that? The 3rd Friday of every month isn't necessarily the 2nd to last Friday...The Job Scheduler has an option for "LAST" x of the month, but not 2nd to last, so after some thought I decided to write a function that would do it for me. Now, I'm happy to see any comments from people on a better way of doing this, but this is what I came up with, hope it helps you, or spawns some ideas for you.

Before you can implment the main function, you'll need to have a function that returns the first day of a given month, and the last day of a given month....Ok, you probably don't need them as functions, but I like them that way...

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =====================================================================
-- Author:  Eric Zierdt: ericzierdt@gmail.com
-- Create date: 01/12/2010
-- Description: Returns the Last day of the month
-- Usage: SELECT dbo.LastOfMonth(GetDate())
-- =====================================================================
CREATE FUNCTION [dbo].[LastOfMonth] 
(
 -- Add the parameters for the function here
 @DTC DATE -- DateToCheck
)
RETURNS DATE
AS
BEGIN
 -- Declare the return variable here
 DECLARE @Result DATE

 -- Add the T-SQL statements to compute the return value here
 SELECT @Result = CAST(DATEADD(d,-1,DATEADD(mm,DATEDIFF(m,0,@DTC)+1, 0)) AS DATE)

 -- Return the result of the function
 RETURN @Result

END




SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- =====================================================================
-- Author:  Eric Zierdt: ericzierdt@gmail.com
-- Create date: 01/12/2010
-- Description: Returns the first day of the month
-- Usage: SELECT dbo.FirstOfMonth(GetDate())
-- =====================================================================
CREATE FUNCTION [dbo].[FirstOfMonth] 
(
 -- Add the parameters for the function here
 @DTC DATE -- DateToCheck
)
RETURNS DATE
AS
BEGIN
 -- Declare the return variable here
 DECLARE @Result DATE

 -- Add the T-SQL statements to compute the return value here
 SELECT @Result = CAST(DATEADD(ms,0,DATEADD(mm,DATEDIFF(m,0,@DTC), 0)) AS DATE)

 -- Return the result of the function
 RETURN @Result

END

And now for the main attraction:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =====================================================================
-- Author:  Eric Zierdt: ericzierdt@gmail.com
-- Create date: 01/06/2011
-- Description: Returns the Nth last  of the given month
-- Usage: SELECT dbo.NthLastDoWOfMonth(GetDate(),6,1)
-- =====================================================================
CREATE FUNCTION [dbo].[NthLastDoWOfMonth] 
(
 -- Add the parameters for the function here
 @MonthToCheck DATE  -- MonthToCheck
 ,@DayOfWeek INT -- 1=Sunday...7=Saturday
 ,@WeeksFromLast INT-- 0=Last @DayOfWeek of the month, 1=2nd to last @DayOfWeek of the month, etc.
)
RETURNS DATE
AS
BEGIN
 DECLARE @DaysInMonth TABLE (DayOfMonth DATE, DayOfWeek INT)

 DECLARE @FirstOfMonth DATE
   ,@LastOfMonth DATE
   ,@ThisDay DATE
   ,@Result DATE

 SELECT @FirstOfMonth = dbo.FirstOfMonth(@MonthToCheck), @LastOfMonth = dbo.LastOfMonth(@MonthToCheck)
 SET @ThisDay = @FirstOfMonth
 WHILE @ThisDay <= @LastOfMonth
 BEGIN
  INSERT INTO @DaysInMonth
  SELECT @ThisDay, DATEPART(dw,@ThisDay)
  SET @ThisDay = DATEADD(d,1,@ThisDay)
 END

 ;WITH DaysCTE AS (
  SELECT DayOfMonth ,ROW_NUMBER() OVER(ORDER BY DayOfMonth) AS 'RowNumber'
  FROM @DaysInMonth DIM 
  WHERE DayOfWeek = @DayOfWeek 
 )
 , MaxRow AS (
  SELECT MAX(RowNumber) AS MaxRowNbr
  FROM DaysCTE
 )
 SELECT @Result = DaysCTE.DayOfMonth
 FROM DaysCTE, MaxRow
 WHERE DaysCTE.RowNumber = MaxRow.MaxRowNbr-@WeeksFromLast

 RETURN @Result

END

For some reason I can't figure out, there seems to be a /day tag at the end of the scirpt...it's not in my code, it appears to be in the exact spot as the code stating the end of my SQL...not sure, ignore it.