•  
  • Archives for Database Maintenance (19)

Is your LOG backed up?

by Jason Brimhall
Categories: Corner, News, Professional, SQLServerPedia Syndication, SSC
Tags: , ,
Comments: No Comments
Published on: February 11, 2013

You have been doing a fantastic job of late.  You have all of your databases being backed up on a regular schedule.  Now you get an alert that your transaction log just keeps growing.  Why is that?

You decide to do a little investigation and find that you have your recovery model set to full and you are performing full backups.  That should cover it right?  No, that is not right!

What is this?  Now you have to do something more?  Yes, that is correct.  Performing full backups is not always enough to recover your critical data.  You need to know the recovery requirements for the database / server in question.  But since you have the recovery model set to full, let’s just talk about what else you should be doing.

The first step should be to run a quick script to determine what databases you have in full recovery and which of those databases do not have a LOG backup.  You see, when a database is in full recovery, you should also backup your transaction log on a regular schedule too.  That schedule is to be determined as a part of the second step (and I will only talk about the first two and only briefly about the second step).

In that first step, you can query your msdb database to help generate a report of which databases have had a transaction log backup.  That should be easy enough to do.  Despite the ease, it should not lessen the importance by any degree.  Here is the script that I wrote recently to help determine which databases were in need of a log backup.

SELECT T1.Name AS DatabaseName
		,ISNULL(t2.database_name, 'No Backup Taken') AS LogBackupAvail
		,T1.recovery_model_desc
		,'Log' AS BackupType 
		,ISNULL(CONVERT(VARCHAR(23), CONVERT(DATETIME, MAX(T2.backup_finish_date), 131))
				, CASE WHEN T1.recovery_model_desc = 'Simple'
						THEN 'N/A'
						ELSE 'Backup Not Taken'
						END
		) AS LastBackUpTaken
	FROM sys.databases T1
		LEFT OUTER JOIN msdb.dbo.backupset T2
			ON t1.name = t2.database_name
			AND t2.type = 'L'
	GROUP BY T1.Name,T2.database_name,T1.recovery_model_desc

You will likely notice that I am querying both sys.databases out of the master database as well as dbo.backupset out of msdb.  Look more closely and you will see the employ of a Left Outer Join with two conditions on the Join.  In this case, both conditions are required to produce the Outer Join effect that I was seeking.  Had I used a script like the following:

SELECT T1.Name AS DatabaseName
		,ISNULL(t2.database_name, 'No Backup Taken') AS LogBackupAvail
		,T1.recovery_model_desc
		,'Log' AS BackupType 
		,ISNULL(CONVERT(VARCHAR(23), CONVERT(DATETIME, MAX(T2.backup_finish_date), 131))
				, CASE WHEN T1.recovery_model_desc = 'Simple'
						THEN 'N/A'
						ELSE 'Backup Not Taken'
						END
		) AS LastBackUpTaken
	FROM sys.databases T1
		LEFT OUTER JOIN msdb.dbo.backupset T2
			ON t1.name = t2.database_name
		WHERE t2.type = 'L'
	GROUP BY T1.Name,T2.database_name,T1.recovery_model_desc

You would see a considerably different result set.  The reason for this different result set is tied to the predicate used and the Join conditions.  And when one examines the execution plan, the difference becomes a little more evident.

That WHERE predicate converted our Left Outer Join to an Inner Join.  Now, if I had only wanted to return results for databases that had log backups, that might be fine.  I want to report on all databases and find not only the last log backup for a database, but I also want to find if a log backup is missing.  Therefore, I need to ensure that both conditions are declared as part of my Left Outer Join.

Running a query such as this will now provide us with some knowledge as to the database recovery models as well as which databases have had a log backup, have not had a log backup, and which do not need a log backup (based on recovery model).

Armed with this information, it is imperative to do a bit more digging now.  Knowing what the business deems as an acceptable data loss is important.  Find that out and then create transaction log backups accordingly so the business can recover appropriately in the event of a disaster.

Audit Configuration Changes

by Jason Brimhall
Categories: Corner, News, Professional, Scripts, SQLServerPedia Syndication, SSC
Tags: , , ,
Comments: 2 Comments
Published on: January 24, 2013

Do you know the last time a Server Property was changed on your instances of SQL Server?

Are you wondering when the setting for max degree of parallelism was changed?

Do you know who changed the setting?

In some environments there are a lot of hands trying to help mix the pot.  Sometimes more hands can make light work.  This is not always the case though.  More hands in the mix can be a troublesome thing.  Especially when things start changing and the finger pointing starts but nobody really knows for sure who made the change or when the change was made.

I know, that is why there is this concept called change management.  If you make a change to a setting, it should be documented, approved and communicated.  Unfortunately the process does not always dictate the work flow and it may be circumvented.  This is why auditing is a tool that is in place and should be in place – like it or not.

Auditing can be a very good tool.  Like many things – too much of a good thing is not a good thing.  Too much auditing can be more of a hindrance than help.  You don’t want to cause interference by auditing too many things.  You also don’t want too much data that the facts get blurred.  I hope that this script strikes more of a balance with just the right amount of data being captured to be of use.

The basic principle to auditing server configs is to find what values changes, when they were changed and by whom.  There are ancillary details that can be helpful in tracking the source of the change such as the hostname of the source computer making the change.  These are all things that we should capture.  But if a setting hasn’t changed – then we need not necessarily report that the setting was unchanged (it should go without saying).

So for this, I created a couple of tables and then a script that I can add to a job to run on a regular basis.  I can put the script in a stored procedure should I desire.  I’ll leave that to you as an exercise to perform.

USE AdminDB;
GO
SET NOCOUNT ON;
 
DECLARE @ConfigLastUpdateDate	DATETIME
		,@PreviousPollDate		DATETIME
		,@MaxPollDate			DATETIME
		,@PATH					NVARCHAR(260);
 
SELECT @PATH = REVERSE(SUBSTRING(REVERSE([PATH]), 
						CHARINDEX('\', REVERSE([path])), 260)) + N'LOG.trc'
	FROM sys.traces 
	WHERE is_default = 1;
 
IF OBJECT_ID('tempdb..#DBCCConfig') IS NOT NULL DROP TABLE #DBCCConfig;
	CREATE TABLE #DBCCConfig(
		[ParentObject] VARCHAR (100),
		[Object]       VARCHAR (100),
		[Field]        VARCHAR (100),
		[Value]        VARCHAR (100)); 
 
INSERT INTO #DBCCConfig (ParentObject, Object, Field, Value)
	EXECUTE ('DBCC CONFIG WITH TABLERESULTS');
 
WITH cte AS
(
SELECT cfgupddate = MAX(CASE WHEN t1.Field = 'cfgupddate' THEN t1.Value ELSE NULL END),
        cfgupdtime = MAX(CASE WHEN t1.Field = 'cfgupdtime' THEN t1.Value ELSE NULL END)
FROM #DBCCConfig t1
WHERE   Field IN ('cfgupddate', 'cfgupdtime')
)
SELECT  @ConfigLastUpdateDate = CONVERT(DATETIME,t3.configure_upd_dt)
FROM    cte t1
        CROSS APPLY (SELECT cfgupddate = DATEADD(DAY, CONVERT(INT, t1.cfgupddate), '1900-01-01')) t2
        CROSS APPLY (SELECT configure_upd_dt = DATEADD(ms, CONVERT(INT, t1.cfgupdtime)*3.3, t2.cfgupddate)) t3;
 
IF NOT EXISTS (SELECT Name 
					FROM sys.objects 
					WHERE name = 'SysConfigAudit')
	CREATE TABLE SysConfigAudit (
		configuration_id	int
		,name	NVARCHAR(256)
		,value	sql_variant
		,minimum	sql_variant
		,maximum	sql_variant
		,value_in_use	sql_variant
		,description	NVARCHAR(MAX)
		,is_dynamic	bit
		,is_advanced	BIT
		,PollDate		DATE
		,LastConfigUpdtDate	DATETIME)
 
IF NOT EXISTS (SELECT Name 
					FROM sys.objects 
					WHERE name = 'SysConfigChangeLog')
	CREATE TABLE SysConfigChangeLog (
		configuration_id	int
		,name	NVARCHAR(256)
		,CurrValue	SQL_VARIANT
		,PrevValue	SQL_VARIANT
		,description	NVARCHAR(MAX)
		,PollDate		DATE
		,LastConfigUpdtDate	DATETIME
		,PrevConfigUpdtDate	DATETIME
		,ChangeDate	DATETIME
		,ChangeBy	NVARCHAR(256)
		,HostName	NVARCHAR(256)
		,ChangeSPID	SQL_VARIANT
		,Changedata	NVARCHAR(2000)
		,ApplicationName NVARCHAR(256)
		,Severity INT
		,ERROR SQL_VARIANT
		,ChangeBySessionLogin	NVARCHAR(256))
IF NOT EXISTS (SELECT Name 
					FROM sys.key_constraints 
					WHERE name ='PK_SysConfigChangeLog' 
						AND OBJECT_NAME(parent_object_id) = 'SysConfigChangeLog')
BEGIN
ALTER TABLE dbo.SysConfigChangeLog ADD CONSTRAINT
	PK_SysConfigChangeLog PRIMARY KEY CLUSTERED 
	(
	configuration_id,
	ChangeDate DESC
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
		ON Admin_Data
 
END
 
SET @MaxPollDate = ISNULL((SELECT TOP 1 PollDate
								FROM dbo.SysConfigAudit 
								ORDER BY PollDate DESC),'1/1/1900')
 
/* Write the configurations out to audit table 
*/
IF CONVERT(DATE,GETDATE()) <> @MaxPollDate
BEGIN
	INSERT INTO SysConfigAudit
			( configuration_id ,
			  name ,
			  value ,
			  minimum ,
			  maximum ,
			  value_in_use ,
			  description ,
			  is_dynamic ,
			  is_advanced,
			  PollDate,
			  LastConfigUpdtDate
			)
	SELECT configuration_id,name
			,value
			,minimum,maximum
			,value_in_use
			,description
			,is_dynamic,is_advanced
			,GETDATE(),@ConfigLastUpdateDate
		FROM master.sys.configurations;
END
 
/* Recast MaxPollDate */
SET @MaxPollDate = ISNULL((SELECT TOP 1 PollDate
								FROM dbo.SysConfigAudit 
								ORDER BY PollDate DESC),'1/1/1900')
 
SET @PreviousPollDate = ISNULL((SELECT TOP 1 PollDate 
									FROM dbo.SysConfigAudit 
									WHERE Polldate <> @MaxPollDate 
									ORDER BY PollDate DESC),'1/1/1900');
 
/*	A configuration has changed and a reboot has occurred 
	causing the updtdate to be written to the config block of the page 10
	The configuration may not be written to the page, but we will write it to
	the table anyway and then compare to the default trace file in all cases.
	If there are any values changed, then write those to the change log with
	the login of the person who changed the value
*/
 
BEGIN
 
WITH presel AS (
SELECT df.LoginName,df.TextData,df.StartTime,df.HostName,df.Severity,df.DatabaseName,df.SPID,df.ERROR
		,df.SessionLoginName,df.ApplicationName
		,SUBSTRING(df.TextData
					,CHARINDEX('''',df.TextData)+1
					,CHARINDEX('''',df.TextData
								,CHARINDEX('''',df.TextData)+1)-CHARINDEX('''',df.TextData)-1)
				AS ConfigName
		,ROW_NUMBER() OVER (PARTITION BY SUBSTRING(df.TextData
													,CHARINDEX('''',df.TextData)+1
													,CHARINDEX('''',df.TextData
															,CHARINDEX('''',df.TextData)+1)-CHARINDEX('''',df.TextData)-1) 
								ORDER BY df.StartTime DESC) AS RowNum
	FROM ::fn_trace_gettable( @path, DEFAULT )  df
	WHERE 1=1
		AND df.TextData LIKE '%Configuration option%'
		AND df.ApplicationName NOT IN ('SQLAgent - Initial Boot Probe','SQLAgent - Enabling/disabling Agent XPs')
		AND df.Severity IS NOT NULL
)
 
INSERT INTO SysConfigChangeLog (configuration_id,name,CurrValue,PrevValue,description,PollDate	
							,LastConfigUpdtDate	,PrevConfigUpdtDate	,ChangeDate,ChangeBy
							,HostName,ChangeSPID,Changedata,ApplicationName,Severity
							,ERROR,ChangeBySessionLogin)
SELECT CUR.configuration_id,cur.NAME,cur.VALUE AS CurrValue,Prev.VALUE AS PrevValue,cur.description
		,cur.polldate, cur.LastConfigUpdtDate,prev.LastConfigUpdtDate AS PrevConfigUpdtDate
		,df.StartTime AS ChangeDate,df.LoginName AS ChangeBy,df.HostName,df.SPID AS ChangeSPID
		,df.TextData AS ChangeData,df.ApplicationName,df.Severity,df.ERROR
		,df.SessionLoginName AS ChangeBySessionLogin
	FROM AdminDB.dbo.SysConfigAudit CUR
		INNER JOIN AdminDB.dbo.SysConfigAudit Prev
			ON CUR.configuration_id = Prev.configuration_id
			AND CUR.PollDate = @MaxPollDate
			AND Prev.PollDate = @PreviousPollDate
		CROSS APPLY presel  df
	WHERE df.RowNum = 1
		AND df.ConfigName = CUR.NAME
		AND CUR.VALUE <> Prev.VALUE	
		AND NOT EXISTS (SELECT configuration_id
							FROM SysConfigChangeLog
							WHERE Name = Cur.NAME
								AND CurrValue = CUR.VALUE
								AND PrevValue = Prev.VALUE
								AND ChangeDate = df.StartTime
								AND ChangeBy = df.LoginName)
		;
END
 
SELECT *
	FROM dbo.SysConfigChangeLog
	ORDER BY ChangeDate DESC;

Here I am trapping the config settings on a daily basis (as the script is written for now). I then cross reference the current settings against the previous settings.  Then I check the default trace to see if any of those settings exist in the trace file.

The default trace captures the data related to configuration changes.  On busy systems, it is still possible for these settings to roll out of the trace files.  For those systems, we may need to make some alterations to the script (such as running more frequently and changing the comparisons to account for smaller time intervals than a day break).

To ensure proper comparison between the audit tables and the trace file, note the substring function employed.  I can capture the configuration name and then join to the audit tables on configuration name.

This has proven useful to me so far in tracking who did what to which setting and when they did it.

I hope you will find good use for it as well.

A Firm Foundation

by Jason Brimhall
Categories: Corner, News, Professional, SQLServerPedia Syndication, SSC, TSQL Tuesday
Tags: , ,
Comments: 4 Comments
Published on: January 7, 2013

Last week I sent out an invite for the monthly TSQL Tuesday party.

The theme for the party is a take on the words resolve or resolution.  I was hoping the theme would encourage some reflection and sharing of real life experiences that have led to a difference being made.

I have resolved on two stories to share.  Both are rather short and simple in nature.

 

This arch (in Arches National Park, Ut.) has stood RESOLUTE for milennia

Story the First

Near the end of the year in 2012, I inherited a database that had not had a consistency check done on it – ever!  In checking the page_verify setting, I found that it was set to none as well.  Both of these should be alarming to any DBA – unless you are completely unconcerned by corrupt data and the potential for corrupt data.  Never-mind the potential business repercussions of having corrupt or lost data.

To find what level of page verification you have enabled, it is a matter of a quick script like the following.

SELECT name, page_verify_option_desc
	FROM sys.databases;

You can have any one of three settings for your page_verify.  The recommended option is to have CHECKSUM enabled.  If you see NONE or TORN_PAGE_DETECTION, you really need to consider changing that.  Keep in mind if you are still running SQL 2000, CHECKSUM is not an option and the query provided will fail.

Changing the verify option is very simple as well.  It only requires an Alter Database to be run such as the following.

ALTER DATABASE [msdb]
	SET PAGE_VERIFY CHECKSUM;

You will probably notice that I am using the msdb in my sample script.  There is a reason for this that will be shown later.  Just keep in mind that msdb should not need to be changed because it should already be using the CHECKSUM option.

What if you have numerous databases that are not using the CHECKSUM method?  It can become rather tedious to change each of those manually.  That is why we might come up with a cursor such as the following.

DECLARE
	@DBName SYSNAME,
	@SQL    VARCHAR(512);
 
DECLARE dbchecksum CURSOR
	LOCAL STATIC FORWARD_ONLY READ_ONLY
	FOR SELECT name
		FROM sys.databases
		WHERE name not in ('tempdb')
			AND state_desc = 'online'
			AND page_verify_option_desc <> 'Checksum';
 
OPEN dbchecksum;
FETCH NEXT FROM dbchecksum INTO @DBName;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'ALTER DATABASE [' + @DBName +'];' +CHAR(10)+CHAR(13)
SET @SQL = @SQL + 'SET PAGE_VERIFY CHECKSUM;' +CHAR(10)+CHAR(13)
 
EXECUTE (@SQL);
SET @SQL = ''
 
FETCH NEXT FROM dbchecksum INTO @DBName;
END
CLOSE dbchecksum;
DEALLOCATE dbchecksum;

This script is only checking for databases that are not using CHECKSUM.  Then it loops through and changes the setting to use CHECKSUM.

I strongly caution about running this in production without an outage window!  I make that recommendation for very simple reasons.  First, the change is to a production system.  Second, the change can have a temporary adverse effect.  Now before you get too excited about it, I have a short demonstration.

Here is a script broken out into three sections.

SELECT TOP(10) [type] AS [Memory Clerk Type], SUM(single_pages_kb) AS [SPA Mem, Kb] 
FROM sys.dm_os_memory_clerks WITH (NOLOCK)
GROUP BY [type]  
ORDER BY SUM(single_pages_kb) DESC OPTION (RECOMPILE);
SELECT DB_NAME(database_id) AS [DATABASE Name],
COUNT(*) * 8/1024.0 AS [Cached SIZE (MB)]
FROM sys.dm_os_buffer_descriptors
WHERE database_id <> 32767 -- ResourceDB
--AND database_id > 4 -- system databases
GROUP BY DB_NAME(database_id)
ORDER BY [Cached SIZE (MB)] DESC OPTION (RECOMPILE);
 
SELECT DB_NAME(dbid) AS DbName,dbid,SUM(size_in_bytes)/1024/1024 AS TotalPlanCacheSize_in_MB
FROM sys.dm_exec_cached_plans cp WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(plan_handle) st
GROUP BY dbid
ALTER DATABASE [msdb]
		SET PAGE_VERIFY CHECKSUM;
SELECT TOP(10) [type] AS [Memory Clerk Type], SUM(single_pages_kb) AS [SPA Mem, Kb] 
FROM sys.dm_os_memory_clerks WITH (NOLOCK)
GROUP BY [type]  
ORDER BY SUM(single_pages_kb) DESC OPTION (RECOMPILE);
SELECT DB_NAME(database_id) AS [DATABASE Name],
COUNT(*) * 8/1024.0 AS [Cached SIZE (MB)]
FROM sys.dm_os_buffer_descriptors
WHERE database_id <> 32767 -- ResourceDB
--AND database_id > 4 -- system databases
GROUP BY DB_NAME(database_id)
ORDER BY [Cached SIZE (MB)] DESC OPTION (RECOMPILE);
 
SELECT DB_NAME(dbid) AS DbName,dbid,SUM(size_in_bytes)/1024/1024 AS TotalPlanCacheSize_in_MB
FROM sys.dm_exec_cached_plans cp WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(plan_handle) st
GROUP BY dbid

Sections one and three are the same.  This script is used to measure various memory components within SQL Server.  The second section is the change we will make to the msdb database.  The queries in the first and third section perform the following: retrieve memory clerk usage (aggregated to memory clerk type), retrieve total data pages stored in cache (aggregated by database), and retrieve the plan cache use (aggregated by database).

Now on to some pre and post change results.  First with what my results were prior to the change.

Memory Clerk Usage
Memory Clerk Type SPA Mem, Kb
CACHESTORE_SQLCP 156184
CACHESTORE_PHDR 45904
CACHESTORE_OBJCP 20664
USERSTORE_DBMETADATA 8472
USERSTORE_SCHEMAMGR 6376

 

Pages in Cache
Database Name Cached Size (MB)
msdb 12.265625

 

Plan Cache
DbName dbid TotalPlanCacheSize_in_MB
NULL 32767 42
NULL NULL 150
msdb 4 13
ReportServer$ADMIN 5 0
MDW 28 8
AdminDB 14 0

And the following are the post change results.

Memory Clerk Usage
Memory Clerk Type SPA Mem, Kb
CACHESTORE_SQLCP 109160
CACHESTORE_PHDR 36744
CACHESTORE_OBJCP 9152
USERSTORE_DBMETADATA 8472
USERSTORE_SCHEMAMGR 6296

 

Data Pages in Cache
Database Name Cached Size (MB)
msdb 12.265625

 

Plan Cache
DbName dbid TotalPlanCacheSize_in_MB
NULL 32767 36
NULL NULL 104
ReportServer$ADMIN 5 0
MDW 28 8
AdminDB 14 0

First observation I want to point out is with the second result for both the pre and post run.  Making this change will not affect the pages in cache.  This goes along with what we have been taught by Paul Randal – that a CHECKSUM is not performed immediately (I paraphrased).  You can read more about the CHECKSUM and some misconceptions about it here.

If we now turn our attention to the first and third result sets, we will see that there are changes in the memory clerks used and the plan cache.  Starting with the the third result set (both pre and post) we see that the ResourceDB decreased in total plan cache size.  The NULL item (adhoc queries not associated to a specific database) also decreased.  After that, the only change in size is the msdb database – disappeared from the results due to no plan cache in use associated to this database.  (Starting to see why I chose the msdb database for this demo?)

If you now look closer at the results for the first query on both sides of the change, you will see correlating changes to the plan cache.  Notice that CACHESTORE_SQLCP dropped by about 46MB (correlates to the null entry from query 3).  But of those clerks listed, you will see that only USERSTORE_DBMETADATA did not change in size.

Looking at these results should demonstrate why this change should be performed during a maintenance window.  There will be an effect on performance and I would rather you let the business know what is coming down the pipe.  This change is akin to running DBCC FLUSHPROCINDB(<db_id>);.  There are other database settings that will have the same effect.  You can read a little about that from Kalen Delaney – here.

Story the Second

This story is far less interesting and a whole lot shorter.  This falls into the category of professional development and fine tuning my skills.  I took the MCM lab exam during the PASS Summit.  I failed, not unlike many who have attempted it.  That is all fine and well. I learned some things about myself and I learned some areas that may need some resolution (sharpened focus).

So as more of a resolution upon which I have greater resolve than a New Years resolution, I will be retaking the Lab exam.  And I will be getting my MCM in the near future.  Just sayin’!

SSRS Subscriptions Report

by Jason Brimhall
Categories: News, Professional, SQLServerPedia Syndication, SSC
Tags: , , , , ,
Comments: No Comments
Published on: January 7, 2013

As a part of my series leading up to Christmas 2012, I shared a script to Report on SSRS Subscriptions.  It was soon found to have a bug with SQL Server 2008 R2 SP2.  IN the comments on that post, I promised to post an updated script.  Here is that update – without the bug.

DECLARE @ReportName VARCHAR(100)
SET @ReportName = NULL;
 
CREATE TABLE #morepower (MonthDate BIGINT,N BIGINT,PowerN BIGINT PRIMARY KEY CLUSTERED
							,NameofMonth VARCHAR(25),WkDay VARCHAR(25))
;
 
WITH powers(powerN, n) AS (
	SELECT POWER(2,number), number 
		FROM master.dbo.spt_values 
		WHERE type = 'P' AND number < 31)
 
INSERT INTO #morepower ( MonthDate ,N,PowerN ,NameofMonth ,WkDay)
	SELECT ROW_NUMBER() OVER (ORDER BY N) AS MonthDate,N,PowerN
			,CASE WHEN N BETWEEN 0 AND 11 
				THEN DATENAME(MONTH,DATEADD(MONTH,N+1,0)-1)
				ELSE NULL
				END AS NameofMonth
			,CASE WHEN N BETWEEN 0 AND 6
				THEN DATENAME(weekday,DATEADD(DAY,n+1,0)-2)
				ELSE NULL
				END AS WkDay
		FROM powers
 
SELECT DISTINCT s.ScheduleID,Ca.PATH AS ReportManagerPath,Ca.Name AS ReportName
		, U.UserName AS SubscriptionCreator
		,Su.Description AS SubscriptionDescription,S.StartDate,Su.LastRunTime
		,CASE 
				WHEN s.RecurrenceType = 1 THEN 'One Off'
				WHEN s.RecurrenceType = 2 THEN 'Hour'
				WHEN s.RecurrenceType = 4 THEN 'Daily'
				WHEN s.RecurrenceType = 5 THEN 'Monthly' 
				WHEN s.RecurrenceType = 6 THEN 'Week of Month' 
			END AS RecurrenceType
		,s.EventType
		,ISNULL(REPLACE(REPLACE(STUFF(
					(SELECT ', ['+CONVERT(VARCHAR(20),MonthDate)+']' AS [TEXT()] 
						FROM #morepower m1 
						WHERE m1.powerN < s.DaysofMonth+1 
							AND s.DaysofMonth & m1.powerN <>0 
						ORDER BY N FOR XML PATH(''), TYPE).VALUE('.','VARCHAR(MAX)')
			   , 1, 2, ''),'[',''),']','')
			,'N/A') AS DaysofMonth
		,ISNULL(c1.NameOfMonth,'N/A') AS MonthString
		,ISNULL(c2.WkDays,'N/A') AS DaysofWeek
		,CASE MonthlyWeek
				WHEN 1 THEN 'First'
				WHEN 2 THEN 'Second'
				WHEN 3 THEN 'Third'
				WHEN 4 THEN 'Fourth'
				WHEN 5 THEN 'Last'
				ELSE 'N/A'
			END AS MonthlyWeek
		,ISNULL(CONVERT(VARCHAR(10),s.DaysInterval),'N/A') AS DaysInterval
		,ISNULL(CONVERT(VARCHAR(10),s.MinutesInterval),'N/A') AS MinutesInterval
		,ISNULL(CONVERT(VARCHAR(10),s.WeeksInterval),'N/A') AS WeeksInterval
	FROM #morepower mp, dbo.Schedule s
		INNER JOIN ReportSchedule RS
			ON S.ScheduleID = RS.ScheduleID
		INNER JOIN CATALOG Ca
			ON Ca.ItemID = RS.ReportID
		INNER JOIN Subscriptions Su
			ON Su.SubscriptionID = RS.SubscriptionID
		INNER JOIN Users U
			ON U.UserID = S.CreatedById
			OR U.UserID = Su.OwnerID
	CROSS APPLY (SELECT s.ScheduleID,REPLACE(REPLACE(STUFF(
							(SELECT ', ['+ NameofMonth + ']' AS [TEXT()] 
								FROM #morepower m1 ,dbo.Schedule s1
								WHERE m1.NameofMonth IS NOT NULL 
									AND m1.powerN & s1.MONTH <>0 
									AND s1.ScheduleID = s.ScheduleID
								ORDER BY N FOR XML PATH(''), TYPE).VALUE('.','VARCHAR(MAX)')
							, 1, 2, ''),'[',''),']','') AS NameOfMonth)c1
	CROSS APPLY (SELECT s.ScheduleID,REPLACE(REPLACE(STUFF(
							(SELECT ', [' + WkDay + ']' AS [TEXT()] 
								FROM #morepower m1 ,dbo.Schedule s2
								WHERE m1.WkDay IS NOT NULL 
									AND DaysOfWeek & m1.powerN <>0
									AND  s2.ScheduleID = s.ScheduleID
								ORDER BY N FOR XML PATH(''), TYPE).VALUE('.','VARCHAR(MAX)')
							, 1, 2, ''),'[',''),']','') AS WkDays) c2
	WHERE Ca.Name = ISNULL(@ReportName,Ca.Name);
 
DROP TABLE #morepower;

The inline code seen above likes to reformat and and will throw an error due to capitalization of the function value and text().  Download the script here: SSRS_SubscriptionsV1_5

On the Ninth Day…

by Jason Brimhall
Categories: Corner, News, Professional, SQLServerPedia Syndication, SSC, SSSOLV
Tags: , , , , , , , ,
Comments: 2 Comments
Published on: December 21, 2012

It’s the end of the world as we know it.  And as the song goes…I feel fine!  But hey, we didn’t start the fire.

Those are a couple of songs that pop into my head every time somebody starts talking doomsday and doomsday prophecies.

If you are reading this, I dare say that the world is still turning.  And that is a good thing because that gives us a chance to talk about the 12 days of pre-Christmas.

Today we will be talking about a tool that can be at the DBAs disposal to help in tracking performance as well as problems.

First there are a couple of items of housekeeping.  First item is that I only realized with this post that the post on the first day was wrong.  I had miscalculated my twelve days to end on Christmas day.  That is wrong!  Counting down from that first post on the 13th means the 12th day will end up on December 24th.  Maybe I will throw in a bonus 13th day post, or delay a post for a day, or just go with it.  It will be a mystery!

Second item is naturally the daily recap of the 12 days to date.

On the Ninth Day of pre-Christmas…

My DBA gave to me a Data Collection!

If only my DBA had told me that I would need to have Management Data Warehouse (MDW) preconfigured.  Well, that is not a problem.  We can handle that too.  For a tutorial on how to setup MDW, I am going to provide a link to one that has been very well written by Kalen Delaney.

The article written by Kalen covers the topic of MDW very well all the way from setting up the MDW, to setting up the Data Collectors, and all the way down to the default reports you can run for MDW.  The MDW and canned Data Collectors can provide some really useful information for your environment.

What I want to share though is a means to add custom data collections to your MDW.  To create a custom collection, we need to rely on two stored procedures provided to us by Microsoft.  Those stored procedures are: sp_syscollector_create_collection_item and sp_syscollector_create_collection_set.  Both of these stored procedures are found in the, you guessed it, msdb database.

Each of these stored procedures has a number of parameters to help in the creation of an appropriate data collection.  When creating a data collection, you will first create the collection set, and then you add collection items to that set.

There are a few notable parameters for each stored procedure that I will cover.  Otherwise, you can refer back to the links for each of the stored procedures to see more information about the parameters.

Starting with the sp_syscollector_create_collection_set stored procedure, I want to point out the @schedule_name, @name, and @collection_mode parameters.  The name is pretty obvious – make sure you have a distinguishable name that is descriptive (my opinion) or at least have good documentation.  The collection mode has two values.  As noted in the documentation, you will want to choose one value over the other depending on the intended use of this data collector.  If running continuously, just remember to run in cached mode.  And lastly is the schedule name.  This will help determine how frequently the job runs.

Unfortunately, the schedule names are not found in the documentation, but rather you are directed to query the sysschedules tables.  To help you find those schedules, here is a quick query.

SELECT schedule_id,name
	FROM dbo.sysschedules
	WHERE name LIKE '%collector%';

Now on to the sp_syscollector_create_collection_item stored procedure.  There are three parameters that I want to lightly touch on.  For the rest, you can refer back to the documentation.  The parameters of interest here are @parameters, @frequency and @collector_type_uid.  Starting with the frequency parameter, this tells the collector how often to upload the data to the MDW if running in cached mode.  Be careful here to select an appropriate interval.  Next is the parameters parameter which is really the workhorse of the collection item.  In the case of the custom data collector that I will show in a bit, this is where the tsql query will go.

Last parameter to discuss is the collector type uid.  Like the schedule for the previous proc, the documentation for this one essentially refers you to a system view - syscollector_collector_types.  Here is a quick query to see the different collector types.

SELECT name
	FROM syscollector_collector_types;

The collector type that I will be using for this example is Generic T-SQL Query Collector Type.  A discussion on the four types of collectors can be reserved for another time.

Let’s move on to the example now.  This custom data collector is designed to help troubleshoot deadlock problems.  The means I want to accomplish this is by querying the system_health extended event session.

I can query for deadlock information direct to the system_health session using a query like the following.

SELECT CAST(
                  REPLACE(
                        REPLACE(XEventData.XEvent.VALUE('(data/value)[1]', 'varchar(max)'), 
                        '', ''),
                  '','')
            AS VARCHAR(4000)) AS DeadlockGraph
FROM
(SELECT CAST(target_data AS XML) AS TargetData
FROM sys.dm_xe_session_targets st
join sys.dm_xe_sessions s ON s.address = st.event_session_address
WHERE name = 'system_health') AS DATA
CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
WHERE XEventData.XEvent.VALUE('@name', 'varchar(4000)') = 'xml_deadlock_report';

You may notice that I have converted to varchar(4000) from XML.  This is in large part to make sure the results will play nicely with the data collector.  Now to convert that to a query that can be used in the @parameters parameter, we get the following.

<ns:TSQLQueryCollector xmlns:ns="DataCollectorType"><Query><Value>
 
SELECT CAST(
                  REPLACE(
                        REPLACE(XEventData.XEvent.VALUE(''(DATA/VALUE)[1]'', ''VARCHAR(MAX)''), 
                        '''', ''''),
                  '''','''')
            AS VARCHAR(4000)) AS DeadlockGraph
FROM
(SELECT CAST(target_data AS XML) AS TargetData
FROM sys.dm_xe_session_targets st
join sys.dm_xe_sessions s ON s.address = st.event_session_address
WHERE name = ''system_health'') AS DATA
CROSS APPLY TargetData.nodes (''//RingBufferTarget/event'') AS XEventData (XEvent)
WHERE XEventData.XEvent.VALUE(''@name'', ''VARCHAR(4000)'') = ''xml_deadlock_report'' 
 
</Value><OutputTable>systemhealthdeadlock</OutputTable></Query></ns:TSQLQueryCollector>

With this query, we are loading the necessary schema nodes that correlate to the Data Collector Type that we chose.  Since this parameter is XML, the schema must match or you will get an error.  We are now ready to generate a script that can create a deadlock data collector.

BEGIN TRANSACTION
BEGIN Try
DECLARE @collection_set_id_1 INT
DECLARE @collection_set_uid_2 UNIQUEIDENTIFIER
EXEC [msdb].[dbo].[sp_syscollector_create_collection_set]
	  @name=N'systemhealthdeadlock'
	, @collection_mode=1
	, @description=N'systemhealthdeadlock'
	, @logging_level=1
	, @days_until_expiration=14
	, @schedule_name=N'CollectorSchedule_Every_15min'
	, @collection_set_id=@collection_set_id_1 OUTPUT
	, @collection_set_uid=@collection_set_uid_2 OUTPUT
 
SELECT @collection_set_id_1, @collection_set_uid_2
 
DECLARE @collector_type_uid_3 UNIQUEIDENTIFIER
SELECT @collector_type_uid_3 = collector_type_uid 
	FROM [msdb].[dbo].[syscollector_collector_types] 
	WHERE name = N'Generic T-SQL Query Collector Type';
 
DECLARE @collection_item_id_4 INT
EXEC [msdb].[dbo].[sp_syscollector_create_collection_item]
	  @name=N'systemhealthdeadlock'
	, @PARAMETERS=N'<ns:TSQLQueryCollector xmlns:ns="DataCollectorType"><Query><Value>
 
SELECT CAST(
                  REPLACE(
                        REPLACE(XEventData.XEvent.value(''(data/value)[1]'', ''varchar(max)''), 
                        '''', ''''),
                  '''','''')
            AS varchar(4000)) AS DeadlockGraph
FROM
(SELECT CAST(target_data AS XML) AS TargetData
from sys.dm_xe_session_targets st
join sys.dm_xe_sessions s on s.address = st.event_session_address
where name = ''system_health'') AS Data
CROSS APPLY TargetData.nodes (''//RingBufferTarget/event'') AS XEventData (XEvent)
where XEventData.XEvent.value(''@name'', ''varchar(4000)'') = ''xml_deadlock_report'' 
 
</Value><OutputTable>systemhealthdeadlock</OutputTable></Query></ns:TSQLQueryCollector>'
	, @collection_item_id=@collection_item_id_4 OUTPUT
	, @frequency=30
	, @collection_set_id=@collection_set_id_1
	, @collector_type_uid=@collector_type_uid_3
 
SELECT @collection_item_id_4
 
COMMIT TRANSACTION;
END Try
BEGIN Catch
ROLLBACK TRANSACTION;
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
DECLARE @ErrorNumber INT;
DECLARE @ErrorLine INT;
DECLARE @ErrorProcedure NVARCHAR(200);
SELECT @ErrorLine = ERROR_LINE(),
       @ErrorSeverity = ERROR_SEVERITY(),
       @ErrorState = ERROR_STATE(),
       @ErrorNumber = ERROR_NUMBER(),
       @ErrorMessage = ERROR_MESSAGE(),
       @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');
RAISERROR (14684, @ErrorSeverity, 1 , @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine, @ErrorMessage);
 
END Catch;
 
GO

Upon creation, this will create a SQL Agent job with the defined schedule.  Since this is a non-cached data collector set, the Agent job will adhere to the schedule specified and upload data on that interval.

Now all we need to do is generate a deadlock to see if it is working.  It is also a good idea to introduce you to the table that will be created due to this data collector.  Once we create this collector set, a new table will be created in the MDW database.  In the case of this collector set, we have a table with the schema and name of custom_snapshots.systemhealthdeadlock.

This new table will have three columns.  One column represents the DeadlockGraph as we retrieved from the query we provided to the @parameters parameter.  The remaining columns are data collector columns for the collection date and the snapshot id.

Now that we have covered all of that, your favorite deadlock query has had enough time to finally fall victim to a deadlock.  We should also have some information recorded in the custom_snapshots.systemhealthdeadlock table relevant to the deadlock information (if not, it will be there once the agent job runs, or you can run a snapshot from SSMS of the data collector).  With a quick query, we can start looking into the deadlock problem.

SELECT collection_time,CAST(DeadlockGraph AS XML) AS DeadlockGraph, snapshot_id
	FROM mdw.custom_snapshots.systemhealthdeadlock;

This query will give me a few entries (since I went overkill and created a bunch of deadlocks).  If I click the DeadlockGraph cell in the result sets, I can then view the XML of the DeadlockGraph, as in the following.

<deadlock>
  <victim-list>
    <victimProcess id="process5a4ebc8" />
  </victim-list>
  <process-list>
    <process id="process5a4ebc8" taskpriority="0" logused="0" waitresource="KEY: 26:72057594048020480 (b4903b2250cc)" waittime="609" ownerId="2803145" transactionname="user_transaction" lasttranstarted="2012-12-20T22:32:09.987" XDES="0x8008d950" lockMode="X" schedulerid="8" kpid="13656" status="suspended" spid="87" sbid="0" ecid="0" priority="0" trancount="4" lastbatchstarted="2012-12-20T22:38:50.020" lastbatchcompleted="2012-12-20T22:38:50.020" lastattention="2012-12-20T22:38:24.217" clientapp="Microsoft SQL Server Management Studio - Query" hostname="SomeServer" hostpid="7604" loginname="SomeLogin" isolationlevel="serializable (4)" xactid="2803145" currentdb="26" lockTimeout="4294967295" clientoption1="673187936" clientoption2="390200">
      <executionStack>
        <frame procname="" line="10" stmtstart="40" sqlhandle="0x02000000eac1af36f412db4e21d9dcc86feb261fa6bcd230" />
        <frame procname="" line="10" stmtstart="356" stmtend="518" sqlhandle="0x0200000095b4ee32a25e9724dd73fd6894c60748af6c136b" />
      </executionStack>
      <inputbuf>
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
WHILE 1=1 
BEGIN 
BEGIN TRAN
UPDATE Purchasing.Vendor
SET CreditRating = 2
WHERE BusinessEntityID = 1492;
 
UPDATE Purchasing.Vendor
SET CreditRating = 1
WHERE BusinessEntityID = 1494;
 
COMMIT TRAN 
END
   </inputbuf>
    </process>
    <process id="process5a44bc8" taskpriority="0" logused="0" waitresource="KEY: 26:72057594048020480 (ade87e3a717c)" waittime="609" ownerId="2878446" transactionname="user_transaction" lasttranstarted="2012-12-20T22:38:50.020" XDES="0xa9abd950" lockMode="X" schedulerid="7" kpid="15008" status="suspended" spid="86" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-12-20T22:38:47.887" lastbatchcompleted="2012-12-20T22:38:47.887" lastattention="2012-12-20T22:36:21.247" clientapp="Microsoft SQL Server Management Studio - Query" hostname="SomeServer" hostpid="7604" loginname="SomeLogin" isolationlevel="serializable (4)" xactid="2878446" currentdb="26" lockTimeout="4294967295" clientoption1="673187936" clientoption2="390200">
      <executionStack>
        <frame procname="" line="9" stmtstart="40" sqlhandle="0x02000000eac1af36f412db4e21d9dcc86feb261fa6bcd230" />
        <frame procname="" line="9" stmtstart="352" stmtend="510" sqlhandle="0x020000000c4b9412577ec884cbd51882e5310dd340216739" />
      </executionStack>
      <inputbuf>
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
WHILE 1=1 
BEGIN 
BEGIN TRAN
UPDATE Purchasing.Vendor
SET CreditRating = 1
WHERE BusinessEntityID = 1494;
UPDATE Purchasing.Vendor
SET CreditRating = 2
WHERE BusinessEntityID = 1492;
COMMIT TRAN 
END
   </inputbuf>
    </process>
  </process-list>
  <resource-list>
    <keylock hobtid="72057594048020480" dbid="26" objectname="" indexname="" id="lock5aeec80" mode="X" associatedObjectId="72057594048020480">
      <owner-list>
        <owner id="process5a44bc8" mode="X" />
      </owner-list>
      <waiter-list>
        <waiter id="process5a4ebc8" mode="X" requestType="wait" />
      </waiter-list>
    </keylock>
    <keylock hobtid="72057594048020480" dbid="26" objectname="" indexname="" id="lock6164d80" mode="X" associatedObjectId="72057594048020480">
      <owner-list>
        <owner id="process5a4ebc8" mode="X" />
      </owner-list>
      <waiter-list>
        <waiter id="process5a44bc8" mode="X" requestType="wait" />
      </waiter-list>
    </keylock>
  </resource-list>
</deadlock>

Code to generate deadlock courtesy of “SQL Server 2012 T-SQL Recipes: A Problem-Solution Approach” by Jason Brimhall, Wayne Sheffield et al (Chapter 12, pages 267-268).  If you examine the deadlock graph you will see the code that generated the deadlock.

Since this is being pulled from the RingBuffer target of the system_health, it can prove useful to store that data into a table such as I have done.  The reason being that the Ringbuffer can be overwritten, and with good timing on the data collector, we can preserve this information for later retrieval and troubleshooting.  Deadlocks don’t always happen at the most opportune time and even less likely to occur when we are staring at the screen waiting for them to happen.

As you read more about the stored procedures used to create a data collector, you will see that there is a retention parameter.  This helps prevent the table from getting too large on us.  We can also ensure that an appropriate retention is stored for these custom collectors.

Conclusion

Creating a custom data collector can be very handy for a DBA.  Especially in times of troubleshooting.  These collectors are also quite useful for trending and analysis.  Consider this a tool in the chest for the poor man. ;)

Enjoy and stay tuned!

All scripts and references were for SQL 2008 R2.  The Deadlock script was pulled from the 2012 book, but the script runs the same for the 2008 version of the AdventureWorks database.

On the Eighth Day…

by Jason Brimhall
Categories: Corner, News, Professional, SQLServerPedia Syndication, SSC
Tags: , , , , ,
Comments: 5 Comments
Published on: December 20, 2012

Today’s post is merely an illusion.  The illusion being that we have finally stopped talking about the msdb database.  I’ll explain about that later in this post.

This should be a good addition to the script toolbox for those Mere Mortal DBAs out there supporting their corporate SSRS environment.  Everybody could use a script now and then that helps them better support their environment and perform their DBA duties, right?

No reading ahead now.  We’ll get to the script soon enough.  First, we have a bit of business to cover just as we normally do.

We need to quickly recap the first seven days thus far (after all, the song does a recap with each day).

 

  1. Runaway Jobs – 7th Day
  2. Maintenance Plan Gravage - 6th Day
  3. Table Compression - 5th Day
  4. Exercise for msdb - 4th Day
  5. Backup, Job and Mail History Cleanup - 3rd Day
  6. Service Broker Out of Control - 2nd Day
  7. Maint Plan Logs - 1st Day

On the Eighth Day of pre-Christmas…

My DBA gave to me a means to see Report Subscriptions and their schedules.

One of the intriguing points that we find with having a reporting environment is that we also need to report on that reporting environment.  And one of the nuisances of dealing with a Reporting Services Environment is that data like report subscription schedules is not very human friendly.

Part of the issue is that you need to be fluent with math.  Another part of the issue is that you need to be a little familiar with bitwise operations in SQL Server.  That said, it is possible to get by without understanding both very well.  And as a last resort, there is always the manual method of using Report Manager to check the subscriptions for each of the reports that have been deployed to that server.  Though, I think you will find this to be a bit tedious if you have a large number of reports.

I have seen more than one script that provides the schedule information for the subscriptions without using math and just relying on the bitwise operations.  This tends to produce a lot of repetitive code.  The method works, I’m just not that interested in the repetitive nature employed.

Within SQL Server you should notice that in several tables, views, and processes employ the powers of 2 or base 2 or binary number system.  This is natural since this is so integral within computer science in general.  Powers of 2 translates to binary fairly easily and then integrates so well with bitwise operations.

The following table demonstrates the powers of 2 and conversion to binary.

power of 2 value binary
0 1 1
1 2 10
2 4 100
3 8 1000
4 16 10000
5 32 100000
6 64 1000000
7 128 10000000
8 256 100000000

To get numbers and values between the binary results or the value results listed above is a matter of addition.  We add the value from a power of 2 to another power of 2.  So if I need a value of 7, then I need 2^0 + 2^1 + 2^2.  This results in a binary value of 0111.  Now this is where the need for bit comparisons comes into play so we will use some bitwise operations (read more here) to figure out quickly which values are used to reach an end value of 7 (so I don’t need to really know a lot of math there ;) ).

How does this Apply to Schedules?

This background has everything to do with scheduling in SSRS.  Within the ReportServer database, there is a table called Schedule in the dbo schema.  This table has multiple columns that store pieces of the Subscription Schedule.  The three key columns are DaysofWeek, DaysofMonth and Month.  The values stored in these columns are all sums of the powers of 2 necessary to represent multiple days or months.

For instance, you may see the following values

DaysOfWeek DaysOfMonth Month
62 135283073 2575

These values are not friendly to normal every day mere mortal DBAs.  The values from the preceding table are shown below with the human friendly data they represent.

DaysOfWeek DaysOfMonth Month
62 135283073 2575
Monday,Tuesday,Wednesday,Thursday,Friday 1,8,9,15,21,28 January,February,March,April,October,December

That is better for us to understand, but not necessarily better to store in the database.  So, I hope you can see the value of storing it in a numeric representation that is easily deciphered through math and TSQL.

Without further adieu, we have a script to report on these schedules without too much repetition.

DECLARE @ReportName VARCHAR(100)
SET @ReportName = NULL;
 
CREATE TABLE #morepower (MonthDate BIGINT,N BIGINT,PowerN BIGINT PRIMARY KEY CLUSTERED
							,NameofMonth VARCHAR(25),WkDay VARCHAR(25))
;
WITH E1(N) AS ( --=== Create Ten 1's
                 SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 --10
               ),
      E2(N) AS (SELECT 1 FROM E1 a, E1 b),   --100
cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT N)) FROM E2  UNION ALL SELECT 0
),powers(powerN,n) AS (SELECT POWER(2,N),N FROM cteTally WHERE N < 31)
 
INSERT INTO #morepower ( MonthDate ,N,PowerN ,NameofMonth ,WkDay)
	SELECT ROW_NUMBER() OVER (ORDER BY N) AS MonthDate,N,PowerN
			,CASE WHEN N BETWEEN 0 AND 11 
				THEN DATENAME(MONTH,DATEADD(MONTH,N+1,0)-1)
				ELSE NULL
				END AS NameofMonth
			,CASE WHEN N BETWEEN 0 AND 6
				THEN DATENAME(weekday,DATEADD(DAY,n+1,0)-2)
				ELSE NULL
				END AS WkDay
		FROM powers
 
SELECT DISTINCT s.ScheduleID,Ca.PATH AS ReportManagerPath,Ca.Name AS ReportName
		, U.UserName AS SubscriptionCreator
		,Su.Description AS SubscriptionDescription,S.StartDate,Su.LastRunTime
		,CASE 
			WHEN s.RecurrenceType = 1 THEN 'One Off'
			WHEN s.RecurrenceType = 2 THEN 'Hour'
			WHEN s.RecurrenceType = 4 THEN 'Daily'
			WHEN s.RecurrenceType = 5 THEN 'Monthly' 
			WHEN s.RecurrenceType = 6 THEN 'Week of Month' 
		END AS RecurrenceType
		,s.EventType
		,ISNULL(STUFF(
				(SELECT ','+CONVERT(VARCHAR(50),MonthDate) AS [TEXT()] 
					FROM #morepower m1 
					WHERE m1.powerN < s.DaysofMonth+1 
						AND s.DaysofMonth & m1.powerN <>0 
					ORDER BY N FOR XML PATH('')),1,1,''),'N/A') AS DaysofMonth
		,ISNULL(c1.NameOfMonth,'N/A') AS MonthString
		,ISNULL(c2.WkDays,'N/A') AS DaysofWeek
		,CASE MonthlyWeek
				WHEN 1 THEN 'First'
				WHEN 2 THEN 'Second'
				WHEN 3 THEN 'Third'
				WHEN 4 THEN 'Fourth'
				WHEN 5 THEN 'Last'
				ELSE 'N/A'
			END AS MonthlyWeek
		,ISNULL(CONVERT(VARCHAR(10),s.DaysInterval),'N/A') AS DaysInterval
		,ISNULL(CONVERT(VARCHAR(10),s.MinutesInterval),'N/A') AS MinutesInterval
		,ISNULL(CONVERT(VARCHAR(10),s.WeeksInterval),'N/A') AS WeeksInterval
	FROM #morepower mp, dbo.Schedule s
		INNER JOIN ReportSchedule RS
			ON S.ScheduleID = RS.ScheduleID
		INNER JOIN CATALOG Ca
			ON Ca.ItemID = RS.ReportID
		INNER JOIN Subscriptions Su
			ON Su.SubscriptionID = RS.SubscriptionID
		INNER JOIN Users U
			ON U.UserID = S.CreatedById
			OR U.UserID = Su.OwnerID
	CROSS APPLY (SELECT s.ScheduleID,STUFF(
							(SELECT ','+NameofMonth AS [TEXT()] 
								FROM #morepower m1 ,dbo.Schedule s1
								WHERE m1.NameofMonth IS NOT NULL 
									AND m1.powerN & s1.MONTH <>0 
									AND s1.ScheduleID = s.ScheduleID
								ORDER BY N FOR XML PATH('')),1,1,'') AS NameOfMonth)c1
	CROSS APPLY (SELECT s.ScheduleID,STUFF(
							(SELECT ','+WkDay AS [TEXT()] 
								FROM #morepower m1 ,dbo.Schedule s2
								WHERE m1.WkDay IS NOT NULL 
									AND DaysOfWeek & m1.powerN <>0
									AND  s2.ScheduleID = s.ScheduleID
								ORDER BY N FOR XML PATH('')),1,1,'') AS WkDays) c2
	WHERE Ca.Name = ISNULL(@ReportName,Ca.Name);
 
DROP TABLE #morepower;

Consider this as V1 of the script with expected changes coming forth.

I have set this up so a specific report name can be provided or not.  If not provided, the query will return all scheduling information for all reports.

Through the use of a numbers table (done through the CTE), I have been able to create a map table for each of the necessary values to be parsed from the schedule later in the script.  In the creation of that map table, note the use of the power function.  This map table was the critical piece in my opinion to create a script that could quickly decipher the values in the schedule and provide something readable to the DBA.

Conclusion

I did this script because I feel it important to know what reports are running and when they are running.  Add that management also likes to know that information, so there is value to it.  But, I found scripts on the web that used the bitwise operation piece but a lot of repetitive code to determine each Weekday and month.

An alternative would be to perform a query against the msdb database since Scheduled reports are done via a SQL Agent job.  I hope you find this report useful and that you can put it to use.

On the Seventh Day…

by Jason Brimhall
Categories: Corner, News, Professional, SQLServerPedia Syndication, SSC
Tags: , , , ,
Comments: 1 Comment
Published on: December 19, 2012

So far this series has been a bit of fun.  We have discussed a fair amount of useful things to date.  And here we are only on the seventh day.

Today we will be reverting our attention back to the msdb database.  This time not as a means of maintaining the under-carriage but rather to help us try and become better DBAs.

Sometimes to be a better DBA, we have to be a bit proactive and less reactive.

We’ll get to that shortly.  As we have done just about every day so far though, we need to recap real quick what we have to date in the 12 Days of pre-Christmas.

 

  1. Maintenance Plan Gravage – Day 6
  2. Table Compression - Day 5
  3. Exercise for msdb - Day 4
  4. Backup, Job and Mail History Cleanup - 3rd Day
  5. Service Broker Out of Control - 2nd Day
  6. Maint Plan Logs - 1st Day

On the Seventh Day of pre-Christmas…

My DBA gave to me an early Nuclear Fallout detection and warning system.  Ok, maybe not quite that extensive – but it could sure feel like it if something were to slip through the crevasses and the business management started breathing down your neck.

Have you ever had a SQL Job run longer than it should have?  Has the job run long enough that it ran past the next scheduled start time?  These are both symptoms that you might want to monitor for and track in your environment.  If you are watching for it – you should be able to preemptively strike with warning flares sent out to the interested parties.  Then you would be more of the hero than the scapegoat.  And we all want to be on the winning side of that conversation without the confrontation from time to time ;) .

Today, I am going to share a quick script that can be used to help monitor for jobs that have waltzed well beyond the next scheduled start time.  First though, there is a script available out there for the jobs that have run beyond normal run times.  You can check out this article by Thomas LaRock to get a good script to check and log long running jobs.

Though the criteria are similar – we do have two different needs that need to be reported on and monitored.  This is why we have this second script.  If you have a job that should run every 15 minutes but you find that the job has been running non-stop for 45 minutes, that is quite a problem.  And many times we should alert on something like that.

So here is the quick script.

WITH preagg AS (   
SELECT sj.name,MAX(sa.last_executed_step_date) AS last_executed_step_date
		,MAX(sa.start_execution_date) AS start_execution_date
		,MAX(sa.stop_execution_date) AS stop_execution_date
		,MAX(sa.next_scheduled_run_date) AS next_scheduled_run_date
		,MAX(sh.run_status) AS run_status
		,MAX(ca.next_run_date_time) AS Next_Scheduled_RunDate
		,MAX(CAST(STUFF(STUFF(REPLACE(STR(sh.run_duration, 6), ' ', '0')
							, 3, 0, ':')
						, 6, 0, ':')
				AS VARCHAR)) AS Run_Duration
	FROM msdb.dbo.sysjobs sj
		INNER JOIN msdb.dbo.sysjobactivity sa
			ON sj.job_id = sa.job_id
		LEFT OUTER JOIN msdb.dbo.sysjobhistory sh
			ON sa.job_history_id = sh.instance_id
		INNER JOIN msdb.dbo.sysjobschedules sjs 
			ON sj.job_id = sjs.job_id
        INNER JOIN msdb.dbo.sysschedules ss 
			ON sjs.schedule_id = ss.schedule_id
        CROSS APPLY (SELECT CONVERT(DATETIME, 
                    CASE WHEN sjs.next_run_date = 0 THEN NULL
                    ELSE RIGHT('00000000' + CONVERT(VARCHAR(8), sjs.next_run_date),8) 
						+ ' ' + 
						STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(6), sjs.next_run_time),6)
									, 3, 0, ':')
							, 6, 0, ':')
                    END)
            ) ca(next_run_date_time)
	WHERE (sa.start_execution_date > 0
		AND sa.stop_execution_date IS NULL)
		And (ISNULL(sa.next_scheduled_run_date,GETDATE()) <> ca.next_run_date_time
			OR ISNULL(sh.run_status,4) in (1,4))
	/*
		Status of the job execution:
		0 = Failed
		1 = Succeeded
		2 = Retry
		3 = Canceled
		4 = In progress
	*/
	GROUP BY sj.name
)
SELECT *
	FROM preagg
	WHERE next_scheduled_run_date IS NULL
	ORDER BY Next_Scheduled_RunDate

From here, it is a matter of creating a SQL job to run this script.  The schedule is dependent on job frequency and your expected needs to monitor.  It can very easily be set to run every five minutes.

Once the job is created then it is a matter of setting up a mechanism to alert.  There are any number of ways to do that as well.  I leave that to any who use the script to determine how best to implement for their own environment.

Conclusion

This script will catch jobs that are running and have exceeded the next scheduled start time.  This is accomplished through the filters in the where clause.  With a couple of date comparisons as well as filtering on job_status we can get the desired results.

 

On the Sixth Day…

by Jason Brimhall
Categories: Corner, News, Professional, SQLServerPedia Syndication, SSC
Tags: , , , ,
Comments: 3 Comments
Published on: December 18, 2012

What better way to kick off the sixth day of pre-Christmas than with six slices of foie-gras?  No animals have been hurt in the making of this post!

This could be a pretty festive dish thanks in part to those geese.  I enjoy a little foie gras every now and again.

No, this was not just a clever ploy to bring fat into another post.  Rather, I feel that foie gras is more of a delicacy than eggs.

Today, we will be talking of a delicacy in the database world.  It is a feature that should help every DBA sleep better at night.  Sadly, this delicacy can be tainted by another feature that may just give you hear burn instead.

First though, let’s recap.

  1. Table Compression – Day 5
  2. Exercise for msdb - Day 4
  3. Backup, Job and Mail History Cleanup - 3rd Day
  4. Service Broker Out of Control - 2nd Day
  5. Maint Plan Logs - 1st Day

On the Sixth Day of pre-Christmas…

Picture Courtesy of AllCreatures.org

My DBA gave to me a way to ensure my databases are not corrupt.

Sadly, the method chosen is to force feed (gravage) the checkdb through an SSIS style maintenance plan.  Have you ever tried to run checkdb through a maintenance plan?  It doesn’t always work so well.

Many times, when running checkdb, you may run into this pretty little error message.

Msg 5808, Level 16, State 1, Line 2
Ad hoc update to system catalogs is not supported.

Well, that is just downright wrong.  If I am provided a tool to create maintenance plans, then the tool should not generate an error such as that when I want to run consistency checks against the database.  This is akin to force feeding us something that isn’t all that good for our health.  There was even a connect item filed for this behavior, here.

So, what is it that causes this behavior?

Well, there are a couple of things that contribute to this behavior.  This can be reproduced from tsql as well.  To find what is causing this behavior, I used a tool that is a bit more reliable.  To recreate the failure, I created a small test database and then created a maintenance plan to run consistency checks for that database.  Then the reliable tool I used is Profiler.

Next up is to run the profiler with a filter for the test database, then to start the maintenance plan.  It shouldn’t take too long to have the maintenance plan complete.  When it completes, it is time to investigate the TSQL generated by Profiler and it should become apparent pretty quick what TSQL is being run during a maintenance plan that causes the checkdb to fail with the above mentioned error.

Are you ready?  This code is pretty complex and perplexing.  You better sit down so you don’t fall from the surprise and shock.

EXEC sys.SP_CONFIGURE N'user options', 0 
RECONFIGURE

Why would a maintenance plan need to run that snippet every time that a checkdb is performed?

Now, there is another piece to this puzzle.  This error is thrown when another configuration setting is present.  If we change that setting, the error no longer happens.  Soooo, a quick fix would be to change that setting.  The setting in question is “Allow Updates”.  It has a value of 1 and must be changed to a value of 0.  Since SQL 2005, we want it to be a 0 anyway.

EXEC SP_CONFIGURE 'allow updates' ,0
RECONFIGURE WITH override

Now, another option for this issue would be that the maintenance plan not run the sp_configure at all.  Or, if it is deemed necessary, that it be changed to the following.

EXEC sys.SP_CONFIGURE N'user options', 0 
RECONFIGURE WITH override

The best option in my opinion is that a maintenance plan not be used for consistency checks.  I’d prefer to see a custom built routine, a routine using sp_msforeachdb, or the maintenance plan routines developed by Ola Hallengren that can be found here.  All of these methods require that a little more thought be put into the consistency checking of your database, but that means you will get better sleep through the night.

On the Fifth Day…

by Jason Brimhall
Categories: Corner, News, Professional, SQLServerPedia Syndication, SSC, SSSOLV
Tags: , , ,
Comments: 1 Comment
Published on: December 17, 2012

Today we will have a quick change of pace.  This will be less about maintenance and more about internals.

Today’s topic is one of particular interest to me as well.  The topic is a small section of a presentation I like to give at User Groups and at SQL Saturdays.

Before we take this diversion from the under-carriage to something more related to the engine, let’s have a brief recap of the previous four days of pre-Christmas.

So far the gifts of pre-Christmas have included the following articles.

  1. Exercise for msdb – Day 4
  2. Backup, Job and Mail History Cleanup - 3rd Day
  3. Service Broker Out of Control - 2nd Day
  4. Maint Plan Logs - 1st Day

On the Fifth Day of pre-Christmas…

My DBA gave to me a much more compact database.  Surprisingly it can be as much as much as 20% of the original size.

I know, I know.  I’ve heard it before but this is not the compression used by doublespace and drivespace that we were given many years ago.  This really is much better.

And yes I have heard about the performance factor too.  That is a topic for another discussion.  As an aside, when it comes to performance, I always tell people that they must test for themselves because mileage will vary.

No, what I want to talk about is much different.  I want to talk about the CD Array(at the page level) and the new data types specific to compression that you may encounter within the CD Array.

CD Array Data Types

SQL Server introduces us to 13 data types that are used within the CD Array when Compression has been enabled.  Twelve of these data types can be seen when Row Compression has been enabled on an object.  The thirteenth data type is only applicable when page compression has been implemented.

There is no guarantee that any or all of these data types will be present on a page related to an object that has been compressed using either Row Compression or Page Compression.  If you want to find these data types on a compressed page, you may have to do a little hunting.

To demonstrate that these data types exist and that they can be found, I have a sample script.

USE CompressTest
GO
 
IF EXISTS(SELECT NAME FROM sys.tables WHERE name=N'CDTypes')
BEGIN
	DROP TABLE dbo.CDTypes
END
 
IF EXISTS(SELECT NAME FROM sys.tables WHERE name=N'CDTypes2')
BEGIN
	DROP TABLE dbo.CDTypes2
END
 
CREATE TABLE dbo.CDTypes( 
	SomeNull INT
	,SomeBit BIT
	,Some1Byte INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
	,Some2Byte CHAR(2)
	,Some3Byte CHAR(3)
	,Some4Byte CHAR(4)
	,Some5Byte CHAR(5)
	,Some6Byte CHAR(6)
	,Some7Byte CHAR(7)
	,Some8Byte CHAR(8)
	,SomeLong CHAR(9)
	,SomeBit2 BIT
) WITH (DATA_COMPRESSION=ROW) ;	
 
 SELECT TOP 20000
        SomeID       = IDENTITY(INT,1,1),
        SomeInt      = ABS(CHECKSUM(NEWID()))%50000+1,
        SomeLetters2 = REPLICATE(CHAR(ABS(CHECKSUM(NEWID()))%26+65)
                     + CHAR(ABS(CHECKSUM(NEWID()))%26+65),10),
        SomeMoney    = CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS MONEY),
        SomeDate     = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME),
        SomeHex12    = RIGHT(NEWID(),12)
   INTO dbo.CDTypes2
   FROM Master.dbo.SysColumns t1,
        Master.dbo.SysColumns t2;
GO
 
/*
Let's create a Clustered Index
*/
CREATE CLUSTERED INDEX CompressSTable ON  dbo.CDTypes2(SomeID) WITH (DATA_COMPRESSION = PAGE);
GO
 
/* Insert Data */
INSERT INTO dbo.CDTypes
        ( SomeNull, SomeBit,  Some2Byte, Some3Byte,Some4byte, Some5byte
        , Some6byte, Some7byte, Some8byte, SomeLong, Somebit2)
VALUES  (NULL, 0,  '12', '123', '1234', '12345', '123456', '1234567', '12345678', '123456789', 1)
Go
 
DBCC IND(CompressTest, 'CDTypes', 1)
go
 
/* Make sure CDTypes2 is page compressed */
ALTER INDEX CompressSTable ON CDTypes2 REBUILD WITH (DATA_COMPRESSION = PAGE);
 
DBCC IND(CompressTest, 'CDTypes2', 1)
go
 
DBCC TRACEON(3604)
go
/*
Set Trace 3604 To Get Output to SSMS
Then take a look at the Page
 
--or--
Use the tableresults method
Both methods shown here
 
*/
 
DBCC PAGE('CompressTest', 1, 20392, 3) WITH tableresults
go 
DBCC PAGE('CompressTest', 1, 24889, 3) --in this exercise the second page of type 1 is usually page compressed
go

Now let’s take a look at the different data types, starting with the 12 available with Row Compression.

0×00 NULL SomeNull
0×01 EMPTY SomeBit
0×02 ONE_BYTE_SHORT Some1Byte
0×03 TWO_BYTE_SHORT Some2Byte
0×04 THREE_BYTE_SHORT Some3Byte
0×05 FOUR_BYTE_SHORT Some4Byte
0×06 FIVE_BYTE_SHORT Some5Byte
0×07 SIX_BYTE_SHORT Some6Byte
0×08 SEVEN_BYTE_SHORT Some7Byte
0×09 EIGHT_BYTE_SHORT Some8Byte
0x0a LONG SomeLong
0x0b BIT_COLUMN SomeBit2

If we look at page 20392 (yours will likely be different), we will find all of these data types present.  We will also note that this page should show (COMPRESSED) PRIMARY_RECORD – which indicates that the page is Row Compressed.  When hunting for these data types, it is a good idea to make sure the page is compressed first.  In the supplied table, you can see what data type matches to which column in the table we created via the script.  The table also provides a short description of what that data type represents (as you would see in the CD Array).

If we now want to explore and find the 13th data type, we need to look at the second table we created in the attached script – CDTypes2.  Notice that this table has been page compressed.  I even did that twice.  I did this to make sure the data was page compressed (occasionally when testing I could not easily find a page compressed page unless I page compressed a second time).

Much the same as was done with Row Compression, we need to verify that a page is Page Compressed before searching up and down trying to find this 13th type.  To find a Page Compressed page, we need to look in the CompressionInfo section of the page for CI_HAS_DICTIONARY.  If this notation is present, the page is Page Compressed.

Here is a page snippet with the pertinent data type.

The CD array entry for Column 4 is what we are interested in here.  In this particular record, we see the thirteenth data type of 0x0c (ONE_BYTE_PAGE_SYMBOL).  This data type can appear for any of the columns in the CD array and can be different for each record on the page.  This is due to how Page Compression works (which is also a different discussion).

For this last example, I want to backtrack a bit and point out that I used page 24889 to query for the Symbol data type in the CD Array.  This page was different every time I re-ran the test script.  One thing that was consistent with how I setup the exercise is that the page compressed page that had this data type was always the second page of type 1 (data page) in the results from DBCC Ind.

Conclusion

I hope this helped demystify compression in SQL Server – a little.  This is only one piece of compression but an interesting one in my opinion.  Use the provided script and play with it a little bit to get some familiarity.

Stay tuned for the 6th day of pre-Christmas.

On the Fourth Day…

by Jason Brimhall
Categories: Corner, News, Professional, SQLServerPedia Syndication, SSC
Tags: , , ,
Comments: 1 Comment
Published on: December 16, 2012

We have come to the fourth day in this mini-series.  So far I have laid down some under-carriage maintenance for one of our favorite databases.  That database being the msdb database.  The maintenance discussed so far has covered a few different tables and processes to try and keep things tidy (at a base level).

Today I want to discuss some under-carriage work for the database of id 4.  Yep, that’s right, we will talk some more about the msdb database today.  What we discuss today, though, may be applicable beyond just the msdb database.  First, let’s recap the previous three days.

  1. Backup, Job and Mail History Cleanup – 3rd Day
  2. Service Broker Out of Control – 2nd Day
  3. Maint Plan Logs – 1st Day

On the Fourth Day of pre-Christmas…

My DBA gave to me an exercise program to help me trim the fat!!

Somehow, that seems like it would be more appealing on bacon.  But the amount of fat shown here is just nasty.  And that is what I think a table using multiple gigabytes in a database is when it holds 0 records.

Just deleting the records we have spoken of over the past three days is not the end of the road with the maintenance of the msdb database.  Often times the pages allocated to these tables don’t deallocate right away.  Sometimes, the pages don’t deallocate for quite some time.

When these pages are cleaned up depends in large part on the ghost cleanup process. Of course, the ghost cleanup is not going to do anything on the tables where records were removed unless it knows that there are some ghost records (records that have been deleted) in the first place.  This doesn’t happen until a scan operation occurs.  You can read a much more detailed explanation on the ghost cleanup process from Paul Randal, here.

Because of this, you can try running a query against the table to help move the process along.  But what if you can’t get a scan operation to occur against the table?

Could you try updating statistics?  Maybe try running updateusage?  How about forcing a rebuild of the Clustered Index?  Or even running Index Rebuilds with LOB_Compaction on?  Or even creating a Clustered Index where one doesn’t exist?  Maybe you could try DBCC CLEANTABLE and see if that works.  Or you could try running DBCC CheckDb on the msdb database and try to get all of the pages read.

While working to reduce the table size for the tables in the following image, I tried several different things to try and force a scan of the pages so I could reduce the table sizes (0 records really should not consume several GB of space).

 

Even letting things simmer for a few days to see if backup operations could help, I still see the following.

This is better than it was, but still seems a bit over the top.  Something that both of these tables have in common is that they have BLOB columns.  To find that for the sysxmitqueue table, you would need to connect to the server using a DAC connection.  If you were to use a DAC connection, you would find that the msgbody column is a varbinary(max).

What I found to work for this particular case was to use DBCC Shrinkfile.  As a one time (hopefully)  maintenance task, it should be ok.  I would never recommend this as a regular maintenance task.  The used space for these tables does not decrease until DBCC Shrinkfile reaches the DbccFilesCompact stage.  This is the second phase of ShrinkFile and may take some time to complete.  You can read more on Shrinkfile here.

Conclusion

Is it necessary to go to these lengths to reclaim space in your msdb database from tables that are excessively large with very few records?  That is up to you and your environment.  In many cases, I would say it is a nice to have but not a necessity.

If you do go to these lengths to reduce space in msdb, then you need to understand that there are costs associated with it.  There will be additional IO as well as index fragmentation that you should be prepared to handle as a consequence of shrinking.

Stay tuned for the next installment.  We will be taking a look into some internals related to Compression.

page 1 of 2»
Calendar
May 2013
M T W T F S S
« Apr    
 12345
6789101112
13141516171819
20212223242526
2728293031  
Content
Now Reading

Now Reading

Planned books:

Current books:

  • SQL Server 2012 T-SQL Recipes: A Problem-Solution Approach

    SQL Server 2012 T-SQL Recipes: A Problem-Solution Approach by Jason Brimhall

Recent books:

View full Library

Popular Posts

Popular Posts

Blogroll

Blogroll

Categories

Categories

SQLHelp

SQLHelp


Recent Posts
  • T-SQLTuesday #42! The Long … by Jason Brimhall

          The Long and Winding Road It is time for another installment in the monthly blog party for SQL Server professionals known as TSQL Tuesday. This month we have the pleasure of being hosted by Wendy Pastrick (blog | twitter).  The topic for the month requires a bit of introspection (almost like the[...]

  • May 2013 Las Vegas UG by Jason Brimhall

    Spring is in the air, I think. With that scent in the air, we have a nice juicy topic coming up this month for any and all that are interested. Chad Crawford will be presenting to the group on the tastiness that is Service Broker. Service Broker in Action SQL Server Service Broker is a[...]

  • Lost that Single-User Connection? by Jason Brimhall

    You have changed your database to single_user mode to do a task.  As you go about your business, you lose track of which connection was the single_user connection.  You start closing connections and mistakenly close the session that was your single_user connection.  Now you are unable to start a new single_user session.  It would seem[...]

  • MCM Road Less Traveled by Jason Brimhall

    I began this post back in October of 2012 after learning that I had passed the SQL Server 2008 MCM Knowledge exam.  I had set it aside in hopes of polishing it off after my first lab attempt at Summit 2012.  Notice I said first attempt?  I failed that first attempt.  This is a bit[...]

  • Las Vegas April 2013 UG Meeting by Jason Brimhall

    AlwaysOn – Finally, A usable ‘mirror’! In the past, High Availability and Disaster Recovery (HADR) had many limitations. Clustering and Mirroring are great, but the mirror/secondary database is not very usable since it is not online. Finally, AlwaysOn solves this limitation by merging both multi-node Clustering and mirroring. AlwaysOn also allows the secondary database to[...]

  • T-SQL Tuesday #040: File an… by Jason Brimhall

    Backstory Each month the SQL community comes together for an important party.  This is the blog party that was the brain child of Adam Machanic (Twitter) known as T-SQL Tuesday. The party is a very good collaboration among data professionals on a pre-determined topic.  This month, for TSQL Tuesday #40, the topic is on Files and[...]

  • March 2013 LV UG Meeting by Jason Brimhall

    Another month and we have another installment (meeting) for the Data Professionals of the Las Vegas area. This month we will be getting back on schedule with the usual meeting time and place.  We had a slight divergence last month due to that heartsy holiday. We are excited about some of the upcoming speakers and[...]

  • Las Vegas February 2013 UG by Jason Brimhall

    I know it is right in the middle of TSQL2SDAY when this post is to go live.  If you don’t know what that is, you should go check out this months edition here.  The topic this month is good and should drive up a lot of participation. It also happens that this week we have[...]

  • Is your LOG backed up? by Jason Brimhall

    You have been doing a fantastic job of late.  You have all of your databases being backed up on a regular schedule.  Now you get an alert that your transaction log just keeps growing.  Why is that? You decide to do a little investigation and find that you have your recovery model set to full[...]

  • Audit Configuration Changes by Jason Brimhall

    Do you know the last time a Server Property was changed on your instances of SQL Server? Are you wondering when the setting for max degree of parallelism was changed? Do you know who changed the setting? In some environments there are a lot of hands trying to help mix the pot.  Sometimes more hands[...]

Categories
Recent Comments
Welcome , today is Saturday, May 18, 2013