SQL Saturday 149 Announcement

I found out that my submission for SQL Saturday 149 was accepted and I will again be presenting at the MN SQL Saturday this year. If you haven't signed up yet and you will be in the Twin Cities MN area on Saturday September 29th, 2012, you are missing out on one of the best SQL Server conferences around. This year, the event will be held at the University of Minnesota - Keller Hall, 200 Union Street SE, Minneapolis, MN 55455. Admittance to this event is free, but they do charge a lunch fee of 10.00 so that they can provide a lunch - not pizza! Please register soon as seating is limited, and let friends and colleages know about the event. You can find out more about this (and register!!) at the event homepage: http://www.sqlsaturday.com/149/eventhome.aspx

Extended Events with a view

So last time I wrote, I talked about my interest in Extended Events; I have been playing with them a bit since then and I came across something that made them so much easier to work with. This is not my own idea, but I wanted to share it as it blew my mind away with how simple it is, yet how I never thought of it. The idea is to take your ugly XQuery code and put it in a view, and to make it even better, create a Schema just for your Extended Events. I created a Schema called "XE" then when I want to get data from an Extended Event, I just have to type SELECT * FROM [XE]. and I let IntelliSense or SQLPrompt fill out the available options. This essentially makes querying Extended Events as easy as using a DMV. So in my last post, we created a Extended Even called "XE_Log_Shrink", If I wanted to implement this idea for this XE, I would do the following: Create the Schema (if I haven't previously done this):

USE [master]
GO
CREATE SCHEMA [XE] AUTHORIZATION [dbo]
GO

Create the View (using the XQuery from my previous post:

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/************************************************************
 * AUTHOR: Eric Zierdt          *
 * CREATED: 05/08/2012          *
 * USAGE: SELECT * FROM [XE].[vw_Log_Shrink]  *
 ************************************************************/
CREATE VIEW [XE].[vw_Log_Shrink]
AS
 
WITH Data
AS (
 SELECT CAST(target_data AS XML) AS TargetData
 FROM sys.dm_xe_session_targets dt
 JOIN  sys.dm_xe_sessions ds ON ds.address = dt.event_session_address
 JOIN  sys.server_event_sessions ss ON ds.Name = ss.Name
 WHERE dt.target_name = 'ring_buffer'
  AND ds.Name = 'XE_Log_Shrink'
)
SELECT  DATEADD(hour,-5,XEventData.XEvent.value('(@timestamp)[1]', 'datetime'))  AS event_timestamp
   ,XEventData.XEvent.value('@name', 'varchar(4000)')       AS event_name
   ,DB_NAME(XEventData.XEvent.value('(data/value)[2]', 'VARCHAR(100)'))  AS DatabaseName
   ,XEventData.XEvent.value('(action/value)[2]', 'VARCHAR(512)')    AS client_hostname
   ,XEventData.XEvent.value('(action/value)[3]', 'VARCHAR(512)')    AS nt_username
   ,XEventData.XEvent.value('(action/value)[6]', 'VARCHAR(512)')    AS username
   ,XEventData.XEvent.value('(action/value)[5]', 'VARCHAR(512)')    AS sql_text
   ,XEventData.XEvent.value('(action/value)[4]', 'VARCHAR(512)')    AS session_id
   ,XEventData.XEvent.value('(action/value)[1]', 'VARCHAR(512)')    AS client_app_name
FROM Data d
CROSS APPLY TargetData.nodes('//RingBufferTarget/event') AS XEventData ( XEvent )

GO

Thats all you have to do, now you can query this like a DMV, just write:

SELECT *
FROM [XE].[vw_Log_Shrink]
WHERE [event_timestamp] > '5/8/2012'

Let me know your thoughts.

SSRS Queries

I've recently been working with SSRS and needed to write some queries to get more knowledge about what was happening on the server.

One of the first things I wanted to know was what subscriptions had run that did not succeed. This query should be run on the server that hosts your ReportServer Database. I'm running these on SQL Server 2008R2

USE [ReportServer]
--GET THE SERVER NAME
DECLARE @ServerURL VARCHAR(MAX)
SELECT TOP 1 @ServerURL =[MachineName]
FROM [dbo].Keys
WHERE [MachineName] IS NOT NULL

IF CHARINDEX('\',@ServerURL) > 0
SET @ServerURL = LEFT(@ServerURL,CHARINDEX('\',@ServerURL)-1)

SET @ServerURL = 'http://' + @ServerURL + '/Reports/Pages'

--FIND THE NON-SUCCESSFUL SUBSCRIPTIONS
;WITH AgentJobsCTE AS (
  SELECT [SJ].job_id
     ,[SJ].name AS AgentJobName
     ,[SJS].[command]
     ,CAST(REPLACE(REPLACE(REPLACE([SJS].[command],'exec [ReportServer].dbo.AddEvent @EventType=''TimedSubscription'', @EventData=''',''),'exec ReportServer.dbo.AddEvent @EventType=''TimedSubscription'', @EventData=''',''), '''', '') AS SYSNAME) AS SubscriptionID
  FROM  msdb.[dbo].[sysjobs] AS SJ
  JOIN   msdb.[dbo].[sysjobsteps] AS SJS ON [SJ].[job_id] = [SJS].[job_id]
  WHERE  SJS.[command] LIKE '%TimedSubscription%'
)
SELECT O.[UserName] AS OwnerName,M.[UserName] AS ModifiedBy,C.[Name] AS ReportName,[AJ].AgentJobName,S.[SubscriptionID],S.[LastStatus],S.[LastRunTime],S.[ModifiedDate],S.[Report_OID]
   ,@ServerURL + '/SubscriptionProperties.aspx?ItemPath=' + REPLACE(REPLACE(C.Path,'/','%2f'),' ','+') + '&IsDataDriven=False&SubscriptionID=' + CAST(S.[SubscriptionID] AS VARCHAR(MAX)) AS SubscriptionURL
   ,@ServerURL + '/Report.aspx?ItemPath=' + REPLACE(REPLACE(C.Path,'/','%2f'),' ','+') + '&SelectedTabId=PropertiesTab&SelectedSubTabId=SubscriptionsTab&SortBy=LastExecuted&IsAscending=false' AS ReportURL
FROM [ReportServer].[dbo].[Subscriptions] AS S
JOIN  [ReportServer].[dbo].[Users] AS O ON [S].[OwnerID] = [O].[UserID]
JOIN  [ReportServer].[dbo].[Users] AS M ON S.[ModifiedByID] = [M].[UserID]
JOIN  [ReportServer].[dbo].[Catalog] AS C ON [S].[Report_OID] = [C].[ItemID]
LEFT JOIN [AgentJobsCTE] AJ ON CAST(S.[SubscriptionID] AS SYSNAME) = AJ.[SubscriptionID]
WHERE [LastRunTime] >= '04/02/2012 15:00' --NOT NEEDED, BUT MAKES YOUR LIST SHORTER
 AND [LastStatus] NOT LIKE 'Mail sent to%'
 AND [LastStatus] NOT LIKE 'The file "%'
           --AND [LastStatus] NOT LIKE 'Pending%' --Pending are currently running.
ORDER BY [LastRunTime] DESC

I noticed that I was seeing a number of subscriptions currently running for the same report, many run at the same time. I wrote a query to tell me which subscriptions had the same "report parameter values". This doesn't look at the subscription start time or days of the week, just the parameters...so you'll need to do some investigation (the next query will help with that)

USE [ReportServer]
DECLARE @ServerURL VARCHAR(MAX)
SELECT TOP 1 @ServerURL =[MachineName]
FROM [dbo].Keys
WHERE [MachineName] IS NOT NULL

IF CHARINDEX('\',@ServerURL) > 0
SET @ServerURL = LEFT(@ServerURL,CHARINDEX('\',@ServerURL)-1)

SET @ServerURL = 'http://' + @ServerURL + '/Reports/Pages'

;WITH DuplicateSubscriptions AS (
SELECT C.Name AS ReportName
    ,CAST(Parameters AS VARCHAR(MAX)) AS Parameters
    ,[Report_OID]
    ,C.Path
    ,COUNT(1) AS [Count]
FROM [ReportServer].[dbo].[Subscriptions] S
JOIN  [ReportServer].[dbo].[Catalog] AS C ON [S].[Report_OID] = [C].[ItemID]
LEFT JOIN [dbo].[ReportSchedule] AS RS ON [S].[SubscriptionID] = [RS].[SubscriptionID]
LEFT JOIN [dbo].[Schedule] AS SC ON RS.[ScheduleID] = SC.[ScheduleID]
WHERE (SC.EndDate IS NULL OR (SC.EndDate IS NULL AND SC.[RecurrenceType] <> 1))

GROUP BY CAST(Parameters AS VARCHAR(MAX)),[Report_OID],C.Name,C.Path
HAVING COUNT(1) > 1
)
SELECT *
   ,@ServerURL + '/Report.aspx?ItemPath=' + REPLACE(REPLACE(Path,'/','%2f'),' ','+') + '&SelectedTabId=PropertiesTab&SelectedSubTabId=SubscriptionsTab&SortBy=LastExecuted&IsAscending=false' AS URL
FROM [DuplicateSubscriptions]
--ORDER BY [ReportName], [Count] DESC
ORDER BY [Count] DESC, [ReportName]

So when you find a possible duplicate subscription in the previous list, get the Report_OID and Parameters field and past them into the variables in this query and execute to see if it needs more investigation. I usually look at the "LastRun" field and see if there are any duplicates in that list. If you need to dig in more, the query provides links to both the subscription webpage and the report management page listing all subscriptions

USE [ReportServer]
-- VIEW ALL (NON EXPIRED) SUBSCRIPTIONS FOR A SPECIFIC REPORT WITH SPECIFIC PARAMETERS
DECLARE @ServerSubscriptionURL VARCHAR(MAX)
SELECT TOP 1 @ServerSubscriptionURL =[MachineName]
FROM [dbo].Keys
WHERE [MachineName] IS NOT NULL

IF CHARINDEX('\',@ServerSubscriptionURL) > 0
SET @ServerSubscriptionURL = LEFT(@ServerSubscriptionURL,CHARINDEX('\',@ServerSubscriptionURL)-1)

SET @ServerSubscriptionURL = 'http://' + @ServerSubscriptionURL + '/Reports/Pages'

DECLARE @ReportID sysname = 'F9999999-888G-7H77-I9I9-000000J00000' -- ReportID from previous query
 ,@ParameterList VARCHAR(MAX) = '[parameters]'-- Parameters from previous query

SELECT C.Name AS ReportName
   ,S.[SubscriptionID]
   ,S.[Description] Descr
   ,S.[LastRunTime] LastRun
   ,S.[LastStatus]
   --,SC.[EndDate]
   --,SC.[RecurrenceType]
   ,U.[UserName] AS Owner
   ,@ServerSubscriptionURL + '/SubscriptionProperties.aspx?ItemPath=' + REPLACE(REPLACE(C.Path,'/','%2f'),' ','+') + '&IsDataDriven=False&SubscriptionID=' + CAST(S.[SubscriptionID] AS VARCHAR(MAX)) AS SubscriptionURL
   ,@ServerSubscriptionURL + '/Report.aspx?ItemPath=' + REPLACE(REPLACE(C.Path,'/','%2f'),' ','+') + '&SelectedTabId=PropertiesTab&SelectedSubTabId=SubscriptionsTab&SortBy=LastExecuted&IsAscending=false' AS ReportURL
FROM [ReportServer].[dbo].[Subscriptions] S
JOIN [ReportServer].[dbo].[Users] AS U ON [S].[OwnerID] = [U].[UserID]
JOIN  [ReportServer].[dbo].[Catalog] AS C ON [S].[Report_OID] = [C].[ItemID]
LEFT JOIN [dbo].[ReportSchedule] AS RS ON [S].[SubscriptionID] = [RS].[SubscriptionID]
LEFT JOIN [dbo].[Schedule] AS SC ON RS.[ScheduleID] = SC.[ScheduleID]
WHERE Report_OID = @ReportID
 AND CAST(S.Parameters AS VARCHAR(MAX)) = @ParameterList
 AND (SC.EndDate IS NULL OR (SC.EndDate IS NULL AND SC.[RecurrenceType] <> 1))
ORDER BY S.Description

But wait, thats not all, some additional interesting queries:
See all currently running reports

SELECT [JobID],[StartDate],[ComputerName],[RequestName],[RequestPath]
   ,[Description],[Timeout] AS Timeout, DATEDIFF(ss, startdate, GETDATE()) AS SecondsSinceRun
   , DATEDIFF(mi, startdate, GETDATE()) AS MinutesSinceRun
   ,[JobAction],[JobType],[JobStatus]
 ,'http://[reporturl]/Reports/Pages/Report.aspx?ItemPath=' + REPLACE(REPLACE(RJ.RequestPath,'/','%2f'),' ','+') + '&SelectedTabId=PropertiesTab&SelectedSubTabId=SubscriptionsTab&SortBy=LastExecuted&IsAscending=false' AS ReportURL
FROM [ReportServer].[dbo].[RunningJobs] AS RJ

See the run information for a specific report (comment out the RequestType if you want to see things other than subscriptions)

SELECT *
FROM [ReportServer].[dbo].[ExecutionLog2] AS EL
WHERE [ReportPath] LIKE '%[report name=""]' AND [RequestType] = 'Subscription'
ORDER BY [TimeStart] DESC

View a schedule for a specific Subscription

-- VIEW SCHDULE DATA FOR A SPEICIFC SUBSCRIPTION
SELECT *
FROM [dbo].[Schedule] AS S
JOIN [dbo].[ReportSchedule] AS RS ON [S].[ScheduleID] = [RS].[ScheduleID]
WHERE RS.[SubscriptionID] = '[subscriptionid]'

Ok, one last one, this lets you see all expired (past the end date) and one time subscriptions (useful if you want to clean them up)

-- EXPIRED AND ONE TIME RUN SUBSCRIPTIONS

DECLARE @ServerURL VARCHAR(MAX)
SELECT TOP 1 @ServerURL =[MachineName] 
FROM [dbo].Keys
WHERE [MachineName] IS NOT NULL

IF CHARINDEX('\',@ServerURL) > 0
 SET @ServerURL = LEFT(@ServerURL,CHARINDEX('\',@ServerURL)-1)

SET @ServerURL = 'http://' + @ServerURL + '/Reports/Pages'

;WITH ExpiredSubscriptions AS 
(
 SELECT RS.[SubscriptionID], S.[EndDate] 
 FROM [dbo].[Schedule] AS S 
 JOIN [dbo].[ReportSchedule] AS RS ON [S].[ScheduleID] = [RS].[ScheduleID]
 WHERE S.[EndDate] < GETDATE() OR S.[RecurrenceType] = 1
)
SELECT S.[SubscriptionID]
    ,C.Name AS ReportName
    ,S.[Description] Descr
    ,S.[LastRunTime] LastRun
    ,ES.[EndDate]
    ,S.[LastStatus]
    ,U.[UserName] AS Owner
    ,@ServerURL + '/SubscriptionProperties.aspx?ItemPath=' + REPLACE(REPLACE(Path,'/','%2f'),' ','+') + '&IsDataDriven=False&SubscriptionID=' + CAST(S.[SubscriptionID] AS VARCHAR(MAX)) AS SubscriptionURL 
    ,@ServerURL + '/Report.aspx?ItemPath=' + REPLACE(REPLACE(Path,'/','%2f'),' ','+') + '&SelectedTabId=PropertiesTab&SelectedSubTabId=SubscriptionsTab&SortBy=LastExecuted&IsAscending=false' AS ReportURL
FROM Subscriptions S
JOIN [dbo].[Users] AS U ON [S].[OwnerID] = [U].[UserID]
JOIN  [ReportServer].[dbo].[Catalog] AS C ON [S].[Report_OID] = [C].[ItemID]
JOIN [ExpiredSubscriptions] ES ON S.[SubscriptionID] = ES.[SubscriptionID]
ORDER BY C.[Name], ES.[EndDate] DESC

If you have any other good queries, please drop them in a comment below

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.

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.

Storing Login Errors

In an effor to post more often, I'm posting something very simple. I record (mainly for auditing reasons) all login failures. Nightly (at 11:59) I store the days errors in a table. This is how I do that:

-- =================================================================
-- Author:  Eric Zierdt
-- Create date: 09/24/2009
-- Description: Records daily Login Errors
-- exec DBA_INSERT_LOGIN_AUDIT
-- =================================================================

CREATE PROCEDURE [dbo].[DBA_INSERT_LOGIN_AUDIT]

AS
BEGIN
 
 CREATE TABLE #temp_login_trace
 (LogDate DATETIME,
  ProcessInfo VARCHAR(100),
  [Text] VARCHAR(MAX))

 INSERT #temp_login_trace
 EXEC xp_readerrorlog 0,1,'Login' 

 INSERT INTO dbo.DBA_LOGIN_AUDIT
 SELECT LogDate
   ,@@Servername
   ,ProcessInfo
   ,[Text]
 FROM #temp_login_trace
 WHERE LogDate > CAST(GETDATE() AS Date)

 DROP TABLE #temp_login_trace

END

Feature Add...Please

How many times has it happened to you that you are running some long running query and you need to close your connection (shut down your computer)? I wish there was a way to keep the request alive even if the connection is broken...

If anyone out there knows of a way to do this, let me know!

Kill All SPIDs for given Databases

So as promised in my last post, I'm going to include a script that will loop over a list of databases and kill all SPIDS connected to them. If you want to use this, you'll need to install the SP_WHO3 proc and the StringToTable function that I posted in the past two posts.

DECLARE @DBsToKill VARCHAR(MAX) = 'DB1,DB2,DB3' --List of DBs here, comma delimited
  ,@KillTest VARCHAR(20) = 'TEST' --TEST=print SPIDs; KILL=Kill SPIDs
  ,@SQL VARCHAR(MAX)

SET @SQL = '
SET NOCOUNT ON
CREATE TABLE #SpidsToKill (
 SPID VARCHAR(5)
 ,[Status] VARCHAR(max)
 ,[login] VARCHAR(max)
 ,[HostName] VARCHAR(max)
 ,BlkBy VARCHAR(max)
 ,DBName VARCHAR(100)
 ,Command VARCHAR(max)
 ,CPUTime VARCHAR(max)
 ,DiskIO VARCHAR(max)
 ,LastBatch VARCHAR(MAX)
 ,ProgramName VARCHAR(MAX)
 ,SPID2 VARCHAR(max)
 ,RequestID VARCHAR(max)
)
'
DECLARE @DBName VARCHAR(100)

DECLARE Z_CURSOR CURSOR FOR
 SELECT TableValue
 FROM dbo.StringToTable(@DBsToKill,',')
 
OPEN Z_CURSOR
FETCH NEXT FROM Z_CURSOR 
INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
 
 SET @SQL += '
 INSERT INTO #SpidsToKill

        ( SPID
        ,Status
        ,login
        ,HostName
        ,BlkBy
        ,DBName
        ,Command
        ,CPUTime
        ,DiskIO
        ,LastBatch
        ,ProgramName
        ,SPID2
        ,RequestID
        )
EXEC sp_who3 ''' + @DBName + '''
'

 FETCH NEXT FROM Z_CURSOR 
 INTO @DBName
END
CLOSE Z_CURSOR 
DEALLOCATE Z_CURSOR

IF (@KillTest = 'Kill')
BEGIN
 SET @SQL += '
 DECLARE @SPID VARCHAR(4)
  ,@InternalSQL VARCHAR(MAX) = ''''

 DECLARE Z_CURSOR CURSOR FOR
  SELECT SPID 
  FROM #SpidsToKill
  
 OPEN Z_CURSOR
 FETCH NEXT FROM Z_CURSOR 
 INTO @SPID
 WHILE @@FETCH_STATUS = 0
 BEGIN
  SET @InternalSQL += ''
KILL '' + @SPID

  FETCH NEXT FROM Z_CURSOR 
  INTO @SPID
 END
 CLOSE Z_CURSOR 
 DEALLOCATE Z_CURSOR

 exec(@InternalSQL)
 --PRINT @InternalSQL
 '
END
ELSE
BEGIN
 SET @SQL += 'SELECT * FROM #SpidsToKill ORDER BY DBName, SPID'

END 
SET @SQL += '
DROP TABLE #SpidsToKill'

--PRINT @SQL

EXEC(@SQL)

Returning SPIDS for a given Database

I took the SP_WHO2 proc and altered it so that it could take in a parameter containing a database name and return only SPIDs that are active in that database. I pretty much just use this when I'm going to be restoring backups and need to have the database without any connections to it. However in my next post, we'll make use of this proc and the string to table proc I discussed in yesterdays post and use them both to create a script to kill all spids in a list of databases.

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_who3]    Script Date: 04/16/2010 15:23:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



ALTER procedure [dbo].[sp_who3]  --- 1995/11/03 10:16
    @dbname     sysname = NULL
as

set nocount on

declare
    @retcode         int

declare
    @sidlow         varbinary(85)
   ,@sidhigh        varbinary(85)
   ,@sid1           varbinary(85)
   ,@dbid   INT
   ,@spidlow         int
   ,@spidhigh        int

declare
    @charMaxLenLoginName      varchar(6)
   ,@charMaxLenDBName         varchar(6)
   ,@charMaxLenCPUTime        varchar(10)
   ,@charMaxLenDiskIO         varchar(10)
   ,@charMaxLenHostName       varchar(10)
   ,@charMaxLenProgramName    varchar(10)
   ,@charMaxLenLastBatch      varchar(10)
   ,@charMaxLenCommand        varchar(10)

declare
    @charsidlow              varchar(85)
   ,@charsidhigh             varchar(85)
   ,@charspidlow              varchar(11)
   ,@charspidhigh             varchar(11)

-- defaults

select @retcode         = 0      -- 0=good ,1=bad.
select @sidlow = convert(varbinary(85), (replicate(char(0), 85)))
select @sidhigh = convert(varbinary(85), (replicate(char(1), 85)))

select
    @spidlow         = 0
   ,@spidhigh        = 32767

--------------------------------------------------------------
IF (@dbname IS     NULL)  --Simple default to all LoginNames.
      GOTO LABEL_17PARM1EDITED

-- select @sid1 = suser_sid(@dbname)
select @dbid = null
if exists(select * from sys.databases WHERE name = @dbname)
 select @dbid = database_id from sys.databases WHERE name = @dbname

IF (@dbid IS NOT NULL)  --Parm is a recognized login name.
   begin
   GOTO LABEL_17PARM1EDITED
   end

--------

raiserror(15007,-1,-1,@dbname)
select @retcode = 1
GOTO LABEL_86RETURN


LABEL_17PARM1EDITED:


--------------------  Capture consistent sysprocesses.  -------------------

select

  spid
 ,status
 ,sid
 ,hostname
 ,program_name
 ,cmd
 ,cpu
 ,physical_io
 ,blocked
 ,dbid
 ,convert(sysname, rtrim(loginame))
        as loginname
 ,spid as 'spid_sort'

 ,  substring( convert(varchar,last_batch,111) ,6  ,5 ) + ' '
  + substring( convert(varchar,last_batch,113) ,13 ,8 )
       as 'last_batch_char'
 ,request_id

      into    #tb1_sysprocesses
      from sys.sysprocesses with (nolock)

if @@error <> 0
 begin
  select @retcode = @@error
  GOTO LABEL_86RETURN
 end


--------Prepare to dynamically optimize column widths.


select
    @charsidlow     = convert(varchar(85),@sidlow)
   ,@charsidhigh    = convert(varchar(85),@sidhigh)
   ,@charspidlow     = convert(varchar,@spidlow)
   ,@charspidhigh    = convert(varchar,@spidhigh)



select
             @charMaxLenLoginName =
                  convert( varchar
                          ,isnull( max( datalength(loginname)) ,5)
                         )

            ,@charMaxLenDBName    =
                  convert( varchar
                          ,isnull( max( datalength( rtrim(convert(varchar(128),db_name(dbid))))) ,6)
                         )

            ,@charMaxLenCPUTime   =
                  convert( varchar
                          ,isnull( max( datalength( rtrim(convert(varchar(128),cpu)))) ,7)
                         )

            ,@charMaxLenDiskIO    =
                  convert( varchar
                          ,isnull( max( datalength( rtrim(convert(varchar(128),physical_io)))) ,6)
                         )

            ,@charMaxLenCommand  =
                  convert( varchar
                          ,isnull( max( datalength( rtrim(convert(varchar(128),cmd)))) ,7)
                         )

            ,@charMaxLenHostName  =
                  convert( varchar
                          ,isnull( max( datalength( rtrim(convert(varchar(128),hostname)))) ,8)
                         )

            ,@charMaxLenProgramName =
                  convert( varchar
                          ,isnull( max( datalength( rtrim(convert(varchar(128),program_name)))) ,11)
                         )

            ,@charMaxLenLastBatch =
                  convert( varchar
                          ,isnull( max( datalength( rtrim(convert(varchar(128),last_batch_char)))) ,9)
                         )
      from
             #tb1_sysprocesses
      where
             spid >= @spidlow
      and    spid <= @spidhigh



--------Output the report.


EXEC(
'
SET nocount off

SELECT
             SPID          = convert(char(5),spid)

            ,Status        =
                  CASE lower(status)
                     When ''sleeping'' Then lower(status)
                     Else                   upper(status)
                  END

            ,Login         = substring(loginname,1,' + @charMaxLenLoginName + ')

            ,HostName      =
                  CASE hostname
                     When Null  Then ''  .''
                     When '' '' Then ''  .''
                     Else    substring(hostname,1,' + @charMaxLenHostName + ')
                  END

            ,BlkBy         =
                  CASE               isnull(convert(char(5),blocked),''0'')
                     When ''0'' Then ''  .''
                     Else            isnull(convert(char(5),blocked),''0'')
                  END

            ,DBName        = substring(case when dbid = 0 then null when dbid <> 0 then db_name(dbid) end,1,' + @charMaxLenDBName + ')
            ,Command       = substring(cmd,1,' + @charMaxLenCommand + ')

            ,CPUTime       = substring(convert(varchar,cpu),1,' + @charMaxLenCPUTime + ')
            ,DiskIO        = substring(convert(varchar,physical_io),1,' + @charMaxLenDiskIO + ')

            ,LastBatch     = substring(last_batch_char,1,' + @charMaxLenLastBatch + ')

            ,ProgramName   = substring(program_name,1,' + @charMaxLenProgramName + ')
            ,SPID          = convert(char(5),spid)  --Handy extra for right-scrolling users.
            ,REQUESTID       = convert(char(5),request_id)
      from
             #tb1_sysprocesses  --Usually DB qualification is needed in exec().
      where
             dbid = ' + @dbid + '

      -- (Seems always auto sorted.)   order by spid_sort

SET nocount on
'
)


LABEL_86RETURN:


if (object_id('tempdb..#tb1_sysprocesses') is not null)
            drop table #tb1_sysprocesses

return @retcode -- sp_who2

Converting a delimited string into a table

This isn't anything new, but we will be building upon this in the next post. At times you want to take a delimited string and have it returned in a table format so you can loop over it or do stuff with it; many people have done this before and I'm sure that I'm drawing on stuff I've seen before. This function works well, you call it like this:

SELECT  *
FROM    dbo.StringToTable('a,b,c,d',',')

Where the first parameter is the string to be converted and the second is the delimiter

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--/***********************************************************************
--***
--*** Function:    [StringToTable]
--*** Purpose:      Takes a delimited string and returns a table
--***   
--***   
--*** Author:    Eric Zierdt
--*** Date Created:   2010-04-15
--*** 
--*** Revision History
--*** Date   Author   Description
--*** 2010-04-15 ez    Created
--*** select * from dbo.StringToTable('a,b,c,d',',')
--***********************************************************************/

CREATE FUNCTION [dbo].[StringToTable]
 (
  @String VARCHAR(MAX),
  @Delimiter CHAR(1) = ','
 )
RETURNS @ReturnList TABLE
 (
  ID INT,
  TableValue VARCHAR(MAX)
 )
AS BEGIN
 DECLARE @ArrayList TABLE
 (
  ID INT, TableValue VARCHAR(MAX)
 )
 DECLARE @Value VARCHAR(MAX)
 DECLARE @NextDelimiterLocation INT

  ---------------------------- 
 -- REMOVE LEADING DELIMITER --
  ---------------------------- 
 SET @String = LTRIM(RTRIM(@String)) + 
 CASE WHEN RIGHT(@String, 1) = @Delimiter THEN ''
 ELSE @Delimiter
 END
 
  ------------------------------------------- 
 -- SET THE LOCATION OF THE FIRST DELIMITER --
  ------------------------------------------- 
 SET @NextDelimiterLocation = ISNULL(CHARINDEX(@Delimiter, @String, 1), 0)  

  ---------------------------------------- 
 -- IF NO DELIMITER THEN SET THE WHOLE   --
 -- STRING TO THE FIRST ROW OF THE TABLE --
  ---------------------------------------- 
 IF @NextDelimiterLocation = 0
 BEGIN
  INSERT INTO @ArrayList
  ( 
   ID
   ,TableValue 
  )
  SELECT  1, @String
 END
 ELSE
 BEGIN
  DECLARE @Counter INT
  SET @Counter = 0
  WHILE @NextDelimiterLocation > 0
  BEGIN
    -----------------------------------------------------
   -- SET THE VALUE PARAM TO DATA BEFORE THE DELIMITER  --
   -- THEN LOOP TO DO THE SAME FOR THE REST OF THE LIST --
    -----------------------------------------------------
   SET @Value = LTRIM(RTRIM(LEFT(@String, @NextDelimiterLocation - 1)))
   IF LEN(@String) > 0 AND @NextDelimiterLocation <= LEN(@String)
   BEGIN
    SET @Counter = @Counter + 1
    INSERT  INTO @ArrayList ( ID, TableValue )
    SELECT  @Counter, @Value
   END
   SET @String = SUBSTRING(@String, @NextDelimiterLocation + LEN(@Delimiter), LEN(@String))
   SET @NextDelimiterLocation = CHARINDEX(@Delimiter, @String, 1)
  END
 END
 INSERT  @ReturnList 
 ( 
  ID, TableValue 
 )
 SELECT  ID, TableValue
 FROM    @ArrayList
 
    RETURN
END

Delay run until restore is done.

I had a problem the a while back where I noticed a job kept dieing because it was pulling data from a log shipped database and it was trying to pull that data while the database was restoring it's log file. So I came up with this little sp to fire right before I execute my code. What this does is, if the datase is restoring (State = 1) it performs a loop and every 5 seconds checks back to see if it is still restoring and does this until it is no longer restoring. Since I'm executing this before I do a EXEC (@SQL) it will delay the run of my code until the database is ready.



This can be edited to work for other states, or all non ONLINE states by simply changing the while loop to from @IsRestoring = 1 to @IsRestoring <> 0

I wish there was a better way of formatting this, my code doesn't look this crappy

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

/**********************************
**  Run_After_Restore_Completes  **
**  Created by Eric Zierdt       **
**********************************/

ALTER PROCEDURE [dbo].[Run_After_Restore_Completes] (@DBName VARCHAR(155))
AS

DECLARE @IsRestoring BIT

SET NOCOUNT ON

-- RUN IF THE DATABASE EXISTS
IF EXISTS (SELECT name FROM sys.databases WHERE name = @DBName)
BEGIN
 -- CHECK THE STATE OF THE DATABASE
 SELECT @IsRestoring = State 
 FROM sys.databases 
 WHERE NAME = @DBName

 -- LOOP UNTIL DATABASE IS NO LONGER RESTORING
 WHILE @IsRestoring = 1
 BEGIN
  -- SET A DELAY SO IT ISN'T RUNNING CONSTANTLY
  WAITFOR DELAY '00:00:05'
  SELECT @IsRestoring = State FROM sys.databases WHERE NAME = @DBName
 END
END

@@IDENTITY vs. SCOPE_IDENTITY()

So, I'm not doing a great job of posting, sorry about that.





This isn't new, but I wanted a proof of concept so I created a quick query to show how using @@IDENTITY won't work if you are inserting into a table that has a trigger on it which inserts into a second table. Instead you want to use SCOPE_IDENTITY()






BEGIN TRAN
CREATE TABLE TempA (ID INT IDENTITY (1, 1), TxtData VARCHAR(55))
CREATE TABLE TempB (ID INT IDENTITY (1000, 1), TAID INT, TextData VARCHAR(55))
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[TempA_T01]
ON [dbo].[TempA]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
INSERT INTO TempB
(
TAID
, TextData
)
SELECT
ID
,'System'
FROM INSERTED
END

INSERT INTO TempA
( TxtData )
VALUES ( 'Test' -- TxtData - varchar(55)
)
SELECT @@IDENTITY
SELECT SCOPE_IDENTITY()
SELECT *
FROM TempA
SELECT *
FROM TempB

ROLLBACK

Update USE

I think one thing I'd like to see in a future release of SQL is the ability to use variables with the USE command. I should be able to do this:



DECLARE @DBName VARCHAR(10) = "MyDB"



USE @DBName





but instead I have to write some dynamic SQL, and it's just a bigger pain the rear end than it needs to be.

File Type changes on a very large table

I had a situation recently where in looking at some tables we realized that they were originally created with a number of columns set to BigInt. As we know, the BigInt type can hold values upto 9,223,372,036,854,775,807, and has a cost of 8 bytes. We don't think that we will ever have values in these specific fields bigger than the max size of the int data type (2,147,483,647) and int uses half the size (4 bytes). This was the case for around 15-20 tables on several rows in each table and each table was around 20 gb. In many of the tables every field was a BigInt, so changing the datatype to Int would save us half the storage space needed for the table. Without thinking it through one of the DBAs tried to change the data type of one of the tables (after hours) and the Tranlog went through the roof, to the point that we actually got down to almost no free space left on our tranlog drive....not good, so how to solve the problem?

These tables were setup on a partition, where each month gets its own file, this will come in handy later on.

/***********************************************
First: create staging table:
***********************************************/
1) This I do with the GUI...Right click on the table, choose Script Table As: Create To: New Query Editor Window
2) Do the same thing for indexs and triggers, etc (but here I copy to clipboard and paste into the new table window.
3) Do a replace all on the tableName to add a _Staging to the end. Note, this depending on your naming, this should add _Staging to the indexes and triggers as well.



/***********************************************
Next: determine the min date of the data:
***********************************************/

--We use a date/time dimension to store date values, so dates/times are stored as int values in all tables
SELECT MIN(TDT.DateTimeField) FROM TableName TN WITH(NOLOCK) JOIN TimeDimTable TDT WITH(NOLOCK) ON TN.TimeKeyID = TDT.TimeKeyID


/***********************************************
Next: Next we BCP out all the data for each
month and import it into our staging table.
I do this in yearly batches so that I can check
numbers to make sure everything matches as I go.
Also, stop before you get to the current month.
***********************************************/
SET NOCOUNT ON

DECLARE @sql VARCHAR(8000)
DECLARE @TblName VARCHAR (255)
DECLARE @query VARCHAR(8000)
DECLARE @dbname VARCHAR (255)
DECLARE @StartDate VARCHAR(20)
DECLARE @ThisStartDate DATETIME
DECLARE @EndDate VARCHAR(20)
DECLARE @ThisEndDate DATETIME
DECLARE @path VARCHAR(255)

SET @TblName = 'TableName'
SET @dbname = 'DBName'
SET @ThisStartdate = '01/01/2008'
SET @ThisEndDate = '02/01/2008'
SET @path = 'Z:\BCP\'

--The date you enter below needs to be the first of the month, 2 months after the last month you want to process. So 2/1/2009 means that we will process upto 12/31/2008.
WHILE @ThisEndDate < '11/01/2009'
BEGIN

PRINT 'Start Time: ' + CAST(GETDATE() AS VARCHAR(30))
PRINT 'Running for Date: ' + CAST(@ThisStartDate AS VARCHAR(20))

SELECT @startdate = MIN(TimeKeyId), @enddate = MAX(TimeKeyID)-1 FROM TimeDimTable WITH(NOLOCK) WHERE DATETIME BETWEEN @ThisStartDate AND @ThisEndDate
SET @query = 'SELECT COUNT(*) FROM ' + @dbname + '.dbo.'+ @tblname +' WITH(NOLOCK) WHERE TimeKeyID BETWEEN ' + @startdate + ' AND ' + @enddate
PRINT @query
EXEC(@query)
SELECT @startdate = MIN(TimeKeyId), @enddate = MAX(TimeKeyID)-1 FROM TimeDimTable WITH(NOLOCK) WHERE DATETIME BETWEEN @ThisStartDate AND @ThisEndDate

-- Writing Format File
PRINT 'Writing Format File: ' + CAST(GETDATE() AS VARCHAR(30))
SELECT @sql = 'bcp ' + @dbname + '.dbo.' + @tblname + ' format nul -f ' + @path + @tblname + '.fmt -t, -r \n -c -T -S' + @@ServerName
EXEC master..xp_cmdshell @sql
--PRINT @sql

-- Writing Data File
PRINT 'Writing Data File: ' + CAST(GETDATE() AS VARCHAR(30))
SELECT @sql = 'bcp ' + @query + ' queryout ' + @path + @tblname + '.csv -t, -r \n -c -T -S' + @@ServerName
EXEC master..xp_cmdshell @sql
--PRINT @sql

-- Import Data
PRINT 'Starting Import: ' + CAST(GETDATE() AS VARCHAR(30))
set @sql = 'BULK INSERT @dbname.dbo.' + @tblname + '_staging FROM ''' + @path + @tblname + '.csv''
WITH
(
ORDER (TimeKeyID ASC)
,FORMATFILE = ''' + @path + @tblname + '.fmt''
,ROWS_PER_BATCH = 33000000
,TABLOCK

)'

--PRINT @sql
EXEC (@sql)

SET @ThisStartDate = DATEADD(m,1,@ThisStartDate)
SET @ThisEndDate = DATEADD(m,1,@ThisEndDate)
PRINT 'Month Complete: ' + CAST(GETDATE() AS VARCHAR(30)) + '

'
END

PRINT 'Entire Run Complete: ' + CAST(GETDATE() AS VARCHAR(30)) + '

'


/***************************************************** Next check that the row counts match up. Here is where having the data in a partition is nice I pull the rowcount for each partition for the tables. I pase this into Excel with some code to alert me if the values dont match: =IF(B3,IF(D3<>E3,"BAD",""),"")
The data I paste into excel is:
IndexID,PartitionNumber,FileGroupName,RowCt,NewRowCt

Here is the query to get this data:
*****************************************************/
DECLARE @FileGroupName varchar(50)
,@TableName varchar(50)
SET @FileGroupName = 'Months_Partition_Im_Working_With'
SET @TableName = 'TableName'-- do this twice, adding _Staging the second time

SELECT OBJECT_NAME(p.object_id) AS TableName,
-- i.name AS IndexName,
p.index_id AS IndexID,
--NULL AS PartitionScheme,
p.partition_number AS PartitionNumber,
fg.name AS FileGroupName,
NULL AS LowerBoundaryValue,
NULL AS UpperBoundaryValue,
-- NULL AS Boundary,
p.rows
FROM sys.partitions AS p
JOIN sys.indexes AS i ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN sys.data_spaces AS ds ON ds.data_space_id = i.data_space_id
JOIN sys.filegroups AS fg ON fg.data_space_id = i.data_space_id
WHERE OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0
and object_name(p.object_id) LIKE @TableName
AND fg.NAME LIKE @FileGroupName
UNION ALL
--get info for partitioned table/indexes
SELECT OBJECT_NAME(p.object_id) AS TableName,
-- i.name AS IndexName,
p.index_id AS IndexID,
--ds.name AS PartitionScheme,
p.partition_number AS PartitionNumber,
fg.name AS FileGroupName,
prv_left.value AS LowerBoundaryValue,
prv_right.value AS UpperBoundaryValue,
-- CASE pf.boundary_value_on_right WHEN 1 THEN 'RIGHT' ELSE 'LEFT' END AS Boundary,
p.rows AS Rows
FROM sys.partitions AS p
JOIN sys.indexes AS i ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN sys.data_spaces AS ds ON ds.data_space_id = i.data_space_id
JOIN sys.partition_schemes AS ps ON ps.data_space_id = ds.data_space_id
JOIN sys.partition_functions AS pf ON pf.function_id = ps.function_id
JOIN sys.destination_data_spaces AS dds2 ON dds2.partition_scheme_id = ps.data_space_id AND dds2.destination_id = p.partition_number
JOIN sys.filegroups AS fg ON fg.data_space_id = dds2.data_space_id
LEFT JOIN sys.partition_range_values AS prv_left ON ps.function_id = prv_left.function_id AND prv_left.boundary_id = p.partition_number - 1
LEFT JOIN sys.partition_range_values AS prv_right ON ps.function_id = prv_right.function_id AND prv_right.boundary_id = p.partition_number
WHERE OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0
and object_name(p.object_id) LIKE @TableName
and fg.name LIKE @FileGroupName
-- and p.rows > 0
-- and p.index_id IN (0,1)
-- and p.partition_number = 2
ORDER BY TableName, IndexID, PartitionNumber


/*****************************************************
Next: Repeat for all months until you get to current.
We were lucky that this was for a DataWarehouse and
the data was imported hourly, so we didn't have to be
concerned about getting the current months data and
having live data coming in at the same time, so we
just did the same thing for time span
first of current month - today+1 then as soon as we
verified it, we did the swap below.

If we had to worry about live data, I would have done
a date span of first of current month to today-1 bcp
then done an after hours insert to get todays data,
within a transaction and then done the swap below:
*****************************************************/
USE [DBName]
EXEC sp_rename 'TableName.IndexName','ZZ_IndexName', 'INDEX'
--Repeat for all origional table indexes, triggers, etc.

EXEC sp_rename 'TableName_Staging.IndexName_Staging', 'IndexName', 'INDEX'
--Repeat for all _Staging table indexes, triggers, etc.


EXEC sp_rename 'TableName', 'ZZ_TableName'
EXEC sp_rename 'TableName_Staging', 'TableName'

--Now all our tables and their indexes, triggers, etc should be swapped

Fixing a partition problem

So last time I wrote about using a partition and mentioned it can be used for archiving data; I came across an issue where because of an error Septembers partition didn't start on the first of the month, like it should, so Augusts partition contains data for September. This is a problem because we want to archive off the data for August, but if we stop and drop the August partition then we will also get rid of the few days of September as well and we don't want to do that.


We have a few options for resolving this, most aren't good.
1) we can re-configure the partition.
-- This is bad because of blocking and because of the log file size

2) We can keep the August partition and delete the august data.
-- This is bad, again because of the file size.

3) We can keep the August partition and find a different way of getting rid of the data.
-- This has promise.

What I'm going to end up doing is this:

First I am going to do my normal BCP out of the August data.

Next, make an exact schema copy of the tables in question.

Next switch the August partition to the new tables using this code for each table:
USE DatabaseName ALTER TABLE TableName SWITCH PARTITION 2 TO TableName_Staging PARTITION 2

Now that the data is in a "_Staging" table I can work with it without causing blocking on the main table.

I do the following to get rid of the August data:


/*************************************************
1) Get row count of Sept data
*************************************************/
SELECT COUNT(*) FROM TableName_Staging WHERE CreatedTime >= '9/1/2009' -- 2857208

/*************************************************
2) Copy Sept Data into a Temp Table
-- Confirm row count
*************************************************/
SELECT * INTO #Table_Staging FROM TableName_Staging WHERE CreatedTime >= '9/1/2009'

/*************************************************
3) Truncate Staging Table
4) Copy data from temp back into staging
-- Confirm row count before issuing Commit
*************************************************/
BEGIN TRAN
TRUNCATE TABLE TableName_Staging
INSERT INTO dbo.TableName_Staging
( ColumA ,
ColumnB,
ColumnC ,
ColumnD
)
SELECT * FROM #TableName_Staging

--ROLLBACK
--COMMIT

/*************************************************
2) Drop Temp table
*************************************************/
DROP TABLE #TableName_Staging


So now I should have removed the August data from the _Staging table and rater than deleting 50 million rows, I only had to copy 2.8 million rows; which is a much smaller hit on my tran log file and much, much faster.

Now I switch the partition back from the staging table to the live table:

USE ALTER TABLE TableName_Staging SWITCH PARTITION 2 TO TableName PARTITION 2

And Bobs your uncle, as a co-worker likes to say.

Filegroup Woes

Today I spent far too much time trying to figure out a problem that had a very simple solution.

Some background: the databases I administer utilize partitioning for the purposes of archiving old data. I created a job that at the end of the month creates a new file group and file for the next month set to the size of the file for the previous month. Then the job adds the new filegroup to the partition, so when midnight rolls around on the first of the month, all new data gets put into the new file. This is all very standard stuff; however we recently decided that some of our files were getting a bit big, so we decided to add a second file each month (both in the same filegroup). This created some additional coding to my job to allow for the fact that if we told it to create a "b" file, it shouldn't try to create the filegroup as well.

Long story short, something went wrong last night, and the files weren't created and the job died on the Database with two files. While trying to figure it out I tried manually running the code to create the whole shebang:

USE dbname
ALTER DATABASE dbname ADD FILEGROUP [Filegroupname_200910]
ALTER DATABASE Leadman ADD FILE (NAME = 'filename_200910a'
,FILENAME = 'location' ,SIZE = 22438 ,FILEGROWTH = 500) TO FILEGROUP [Filegroupname_200910]

ALTER PARTITION SCHEME PartitionSchemeName NEXT USED FileGroupName_200910
ALTER PARTITION FUNCTION PartitionFunction_F01() SPLIT RANGE ('10/01/2009')



Except something went wrong, I got a message that the filegroup already existed
So I ran the last two lines again...no file error. I decided to kill the file and kick the job off again, let it do it's thing (plus it'd get rid of the error icon next to the job name). So I deleted the file, no problem, then I tried to delete the filegroup, but got this error:
The filegroup 'XXX' cannot be removed because it is not empty ...

I've seen this before, usually it means the file is on the partion still, or there is a file associated with it. Checked; nope not on the partition; no files associated with it, hmm this is weird.

While doing this, I was working with our new DBA, training him in our what jobs run, and how our system runs; so we both started looking for other objects that might be connected to the filegroup that we weren't thinking about. He found an article that suggested running this query:
SELECT *
FROM sys.destination_data_spaces DDS
JOIN sys.partition_schemes ps ON DDS.partition_scheme_id = ps.data_space_id
JOIN sys.partitions p ON DDS.destination_id = p.partition_number
JOIN sys.data_spaces AS ds ON ds.data_space_id = DDS.data_space_id
WHERE DDS.data_space_id = 7 -- I verified that the data space id was 7

This showed us that filegroup was associated with with a table and had two indexes associated with it, each with 18 million rows in them. This just didn't sound right as we couldn't figure out how that table could get on this filegroup, when there is no file associated with the filegroup...it served to be a major distraction that kept our attention for around an hour.

We broke down what the query was doing, looking at the individual parts and learned that the DMV was probably looking across the entire server, not just that one database. We learned to use syspartitions instead of sys.partitions...but that just confirmed what we already knew, no files associated with it. Argh, this was getting frustrating, what could be holding on to the filegroup.

After another hour of searching and messing around with different things, we came up with a thought, what if it was associated with the partition? It clicked for us, when I ran:
ALTER PARTITION SCHEME PartitionSchemeName NEXT USED FileGroupName_200910
I connected the partition to the filegroup. So I ran the query again, but changed the date to 200909 and then was able to remove the file group.

My job is setup to run again tonight and with luck, we should have our new filegroup/file by tomorrow.