SQLServerPedia

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 that somebody or something has taken your connection.

Today, I am going to discuss some things you may do to get around this problem.

The first thing that may come to mind when you encounter this is “Oh crap!”  Well, no need to get too terribly worried (not unless you really hosed something up and you are trying to fix it real quick before the boss notices).

The next thing you may think of trying is how to circumvent the single_user mode.  And during that thought process you may be thinking that single_user does not really mean single_user so you might try something like start a DAC session.  Well, let’s go through that and see what would happen in a DAC session if your single_user session is stolen.

I am going to skip the part of setting a database into single_user mode because we are presuming that the condition already exists.  To start a DAC session, I am going to point you to a previous article I did on the topic – here.

To ensure I am using a DAC session, I am going to issue the following query.  This will ensure I am in the right session and that DAC is in use.

Code block

SELECT s.group_id,e.name, CAST(g.name AS NVARCHAR(20)) AS ResourceGroup, s.session_id 
		, s.login_time, CAST(s.HOST_NAME AS NVARCHAR(20)) AS HostName 
		, CAST(s.program_name AS NVARCHAR(20)) AS ProgramName 
		,s.original_login_name 
		,s.is_user_process 
		,s.STATUS 
	FROM sys.dm_exec_sessions s 
		INNER JOIN sys.dm_resource_governor_workload_groups g 
			ON g.group_id = s.group_id 
		INNER JOIN sys.dm_exec_connections ec 
			ON s.session_id = ec.session_id 
		LEFT OUTER JOIN sys.endpoints e  
			ON ec.[endpoint_id]=e.[endpoint_id] 
WHERE s.session_id = @@SPID 
ORDER BY g.NAME; 
GO

In my case, this results in an endpoint with the name “Dedicated Admin Connection” and a spid of 84.  Good, I am in the correct session for the rest of this test.  Next, I will issue a Use database statement.  I have created a test database called ClinicDB.  So I will issue the following.

Code block

USE ClinicDB;
GO

I get the following result.

Code block

Msg 924, LEVEL 14, STATE 1, Line 1
DATABASE 'ClinicDB' IS already OPEN and can ONLY have one USER AT a TIME.

So, that blows that idea right out of the water.  It shouldn’t really have been a consideration in the first place because single_user really means just that – single_user.

Now What?

Well, what do you think we could do now to circumvent this little problem and get that single_user session back?

That requires a little investigative work.  It is time to find out who has taken the single_user session and politely ask them to give it up.  To make that task a little easier, we could modify the previous query to find out who has that single_user session (thus limiting how many people we have to ask).  I have modified the following query to use sys.sysprocesses so I could limit the results to the ClinicDB.  This is a limitation of SQL 2008 R2 and older versions.  Getting the database reliably means using sysprocesses.  Despite the database_id being available in other related DMVs, it’s just not that easy.  One would think you could use sys.dm_exec_requests.  But if a request is not active, an entry won’t exist for that session.  This problem is fixed in SQL 2012 since the sys.dm_exec_connections DMV now has the database_id field.  Enough of that birdwalk and on to the query.

Code block

SELECT s.group_id,e.name, CAST(g.name AS NVARCHAR(20)) AS ResourceGroup, s.session_id ,DB_NAME(r.dbid) AS DBName
		, s.login_time, CAST(s.HOST_NAME AS NVARCHAR(20)) AS HostName
		, CAST(s.program_name AS NVARCHAR(20)) AS ProgramName 
		,s.original_login_name 
		,s.is_user_process 
		,s.STATUS 
	FROM sys.dm_exec_sessions s 
		INNER JOIN sys.dm_resource_governor_workload_groups g 
			ON g.group_id = s.group_id 
		INNER JOIN sys.dm_exec_connections ec 
			ON s.session_id = ec.session_id 
		INNER JOIN sys.sysprocesses r
			ON r.spid = s.session_id
		LEFT OUTER JOIN sys.endpoints e  
			ON ec.[endpoint_id]=e.[endpoint_id]
	WHERE DB_NAME(r.dbid) = 'ClinicDB'
ORDER BY g.NAME; 
GO

I chose not to do an entirely new query to simply demonstrate that it was possible with a very small tweak to what has been already used.

Now that you know (in my case I can see that I have a session open with ID = 80 that is connected to that single_user database), I can walk over to the person (knowing his/her login id and computer name) and politely ask them to disconnect.

In the end, this is really an easy thing to resolve.  Sure it may take some people skills – but that doesn’t make the task too terribly difficult.  Next time this happens to you, just remember you can run a quick query to find who has sniped that single_user session.

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 of a story about the journey through the exams and the results.

The first attempt was a little crushing.  A mix of emotions concerning the lab came over me because I felt I knew the technology.  While the results were not desirable – the end effect was desirable and I am glad I did not succeed on that first attempt.

When I took the knowledge exam, I took my time going through the questions and examined the questions.  I think that was a good method for that exam – I was trying to ensure I understood the question and didn’t miss anything that was stated.  Though I took it slowly, that is not an indictment to difficulty for the exam.  I was well prepared and did not want to make any invalid assumptions.

Fast forward a little bit to the first attempt at the lab exam.  I had a plan going into the exam.  I felt confident in my skills.  I felt relaxed with the technology.  Then the exam started and my plan went out the window.  I started making assumptions about the exam that I really should not have done.  I rushed a few scenarios that I should not have rushed because I could have done them better.  In the end, I was my own worst enemy during the exam.  This is not a characteristic of an MCM.  Even when the pressure is on, doing what you know and being methodical is a wonderful asset.

Having failed the first time, I wondered if I was ready to be an MCM.  In retrospect, I was not.  Leading up to the exam, I did feel that I was ready for the exam.  I had read several articles such as the following to try and figure out if I was ready.  In the end, it’s more of a gut check and a leap of faith for some.

Gavin Payne http://gavinpayneuk.com/2012/05/01/knowing-when-youre-ready-to-attempt-to-become-an-mcm-of-sql-server/

Joe Sack http://blogs.msdn.com/b/joesack/archive/2010/11/21/how-do-you-know-if-you-re-ready-for-sql-mcm.aspx

I even perused some of the resource type of articles such as the following.

Nick Haslam http://nhaslam.com/blog/2011/11/19/mcmprep-88-970-sql-mcm-knowledge-exam-sqlmcm/

MCM Blog http://blogs.technet.com/b/themasterblog/archive/tags/sql+server+mcm/

Robert Davis Amazon MCM Reading List http://www.amazon.com/Official-Server-Certified-Master-Readiness/lm/R3RB13PQ7D8TKB

Brent Ozar MCM Articles http://www.brentozar.com/sql-mcm/

In the end, this post by Rob Farley sums it up nicely for me (http://sqlblog.com/blogs/rob_farley/archive/2012/12/23/the-mcm-lab-exam-two-days-later.aspx).  Rob references an article by Tom LaRock and some of what Tom did and didn’t do.  As well as some theory on how to approach the exam at certain stages.  Of all those things, I think the best advice I read as well as I could recommend is to get a study buddy.

It’s not sooo much to have somebody to bounce ideas off of, as it is to have somebody to try and teach.  I worked with Wayne Sheffield as we prepped for our retakes.  The biggest benefit was, as I said, in that I could pick a topic and try to teach Wayne.  He did the same to me.  And then, we could question each other on what-if type questions about our selected topics.  Another benefit is to have a heat-check so to speak.  Having a study buddy can help you from straying too far off into tangents or maybe keeping you from wasting too much time on a topic they might feel you understand exceptionally well.

If you are reading this far, you probably have figured that I have taken the Lab a second time.  I was debating whether to do it this soon due to life and family.  My wife continued to push me to do it (much as Nic Cain experienced here - http://sirsql.net/blog/2012/3/26/achievement-unlockedmcm-sql-server-2008.html).  Without Krista pushing me, I might have delayed even longer.  It was also helpful to have a co-worker and friend pushing me along too (I’m a little competitive).  Thanks again Wayne!  You can read his experiences here.

I received my notification email while driving home from Vegas last week.  Seeing that email pop up from boB Taylor gets your heart racing a little bit – especially after you have failed the lab previously.  I glanced at the email and only need to go to the first word – “CONGRATULATIONS!”

Oh yeah!  Vindication, satisfaction, elation, joy and general happiness set in quickly.  Memories of that old “Wide World of Sports” show from a long time ago spilled in with the words “Agony of Defeat, Thrill of Victory.”

I am glad I took the time to pursue this certification.  This adventure has helped me to learn and grow as a DBA.  I am also grateful to those that helped or pushed in some way or another – whether they knew it or not at the time.  Last but not least, I am thankful for my employer Ntirety.  They understood the importance of this for me and were very supportive in this endeavor.

As one final take away, I was recently shown this link with some interesting questions to help you decide if you feel you are ready to take the exams.

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.

Code block

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:

Code block

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.

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.

Code block

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.

This is the wrap up for TSQL Tuesday #38.  We had a good turnout this month with some new faces and with some regulars.  In total, we had 22 entries.  All of the entries this month were very good.

The topic was on “Standing Firm” and can be found by clicking the image to the left.

Stay tuned to twitter for future TSQL Tuesday announcements and news via the #tsql2sday hash tag.

Rob Farley (blog | twitter) - Running Goals: In his opening paragraph Rob says “Peer pressure can be useful at times, but I also find that it can make me even more stubborn.”  WOW!  I can really relate to that.  Then he proceeds to recount a story from his past that would be hard for a lot of people to overcome.  I know I would have a hard time with not being able to lift my children.  Now Rob is running and plans to keep going and improving.  Excellent story!

Jim McLeod (blog | twitter) - Environment: Jim also decided to write about fitness and exercise, specifically cycling.  Jim ties planning into his goal to cycle more and stick with it.  Then he ties that into SQL Server.  ”Put together an environment that supports and encourages you to stick to your resolution.”  Plan ahead whether it be with certification or cycling, and not just for the happy times but for the rough times as well.

Thomas Stringer (blog | twitter) - Lucky 13:  Like many cross country runners, Thomas wants to hit it hard.  Then he wants to maintain the pace.  The principle is to set out to do something and build up a routine.  Routine becomes habit – and eventually becomes easier.  Thomas is setting out to learn something new every day.  That is a good goal.

Robert Davis (blog | twitter) - Disaster Recovery Resolutions:  This month Robert happens to be providing a month long series on Disaster Recovery.  In keeping with that theme, he has offered up several resolutions every DBA should make.

Koen Verbeeck (blog | twitter) - Resolving an SSIS Performance Problem:  Koen reached Defcon 2 (not really – but you should read it) when presented an opportunity to troubleshoot performance problems in an SSIS package.  The problem seems pretty typical – package works and then starts slowing down over time.  Part of the reason for that is due to four bullet points that Koen discusses.

John Sansom (blog | twitter) - Take More Ownership:  John hits on a big button in his contribution this month.  There are problems within our database environments.  Often times these problems are allowed to coexist.  While they cost time and money and raise frustration levels, how many times do we step up and offer a solution to the real problem?  It’s a good story – check it out.

Matt Velic (blog | twitter) - Stuck:  Matt is a thinker and he was stuck in a rut of late.  Matt enjoys thinking about decisions – a lot.  This was contributing to him being stuck.  He reveals some quick tips about how to get unstuck and to enjoy life just a bit more.

Steve Jones (blog | twitter) - Resolute:  Many people have a hard time standing up for what they want or believe.  They have a hard time saying “no.”  We sometimes joke about DBAs being a stop in the flow in work because we say “no” too much.  I think we probably do not say “no” enough.  And Steve points out plenty of ways that we need to learn to say no more often.  It is all about life balance.  How many times do you say “no” to a new project when you already have a full plate?

Julie Koesmarno (blog | twitter) - Tribute to Mum:  Julie, like her mother, is a WIT/Engineering.  We learn   about some of the personal life of Julie as she gives credit to her mother for being a role model as she grew up.  Now, Julie wants to help be an inspiration to others as her mother was for her.

Chris Fradenburg (blog | twitter) - Avoiding the Repetitive Mess of a Disaster:  Chris is the first of the first timers that participated this month (we had a few – woohoo).  Besides the bottle of gel soap that must be used every time he washes his hands, he is trying to improve his environment by reducing the manual repetitive tasks.  This is a good story about a disaster encountered on the first day on the new job.

Wayne Sheffield (blog | twitter) - Learning:  I did a fair bit of arm twisting to get Wayne to participate this month.  He was having a bit of writers block.  Then a topic fell into his lap and it should provide a month of writing for him.  Wayne discusses how is looking to learn and also provide a series of articles on PoSH to help others learn.  It should be out soon!

Alan Dykes (blog | twitter) - Solid Skills:  Alan is pretty much a self taught SQL Developer.  He has recently resolved to sharpen his skillset.  He learned from reading another post about how performance can vary by using a different tsql solution (e.g. NOT EXISTS versus LEFT OUTER JOINS).

Robert Pearl (aka Bobby Tables) (blog | twitter) - HealthySQL:  Some more arm twisting on this one.  Bobby has an excellent idea and it is something we should resolve to do every year as Data professionals.  Too often we get into the break/fix mode rather than the preemptive strike mode.  Regular maintenance (like you should do with your car) keeps things running longer and with less overall cost.

Hemanth.D (blog | twitter) – Firmness of Purpose:  Hemanth is the second of our first timers to participate this month.  Hemanth goes back and explores the past a little and how community ties in with the #SQLFamily.  Hemanth wants to be more resolute in the upcoming year when faced with an issue and ensuring the issue is resolved.

Mickey Stuewe (blog | twitter) – Spoons:  Mickey introduces us first to Neo and his effort at bending a spoon.  Then she correlates that to her writing ability.  For her to write is like trying to bend a spoon with her mind.  So she uses other tools to accomplish the goal.  She shared a couple of tools of which I think I will try the mind mapping tool.  That might mean some bizarre maps though.

Ed Watson (blog | twitter) – Speaking of Resolve:  Another first timer to the party (iirc), Ed shares his resolution to start speaking more at the local level.  Ed was more prone to flight than fight when it came to public speaking.  He talks about his experience and what he did in 2012 to start speaking publicly.

Jeffrey Verheul (blog | twitter) – Standing Firm:  Jeff had already planned out his goals by the time TSQL Tuesday rolled around.  After he had written some goals, he ran into a rough patch.  Based on that week, he has chosen to stand more firm and improve all that much more.

Ana Mihalj (blog | twitter) – Getting Involved:  Another warm welcome to another first timer.  Ana is from Bosnia and Herzegovina and has been working on blogging and getting involved in the community for the past year and a half.  Now she wants to step up her efforts.

Ken Watson (blog | twitter) – Not an Average Accidental DBA:  Ken shares a story on how he transitioned from a jack of all trades to being a DBA.  Ken is not enamored with being an average DBA and is working at something about which he is passionate.  He chased a dream and it is working out for him.

Tim Ford (blog | twitter) – Bring out your Dead:  You’ll get it once you read his post.  Tim is bringing back #Learn365.  If there were two themes this month it was exercise and learning.  Learn365 is right up that alley with learning.  Go check it out.

Chris Yates (blog | twitter) – Hoops and Databases:  We have a baller among us.  Chris talks about the resolve he had as a High School athlete to be a better ball player and make it to the Collegiate game.  That same resolve is applicable to being an Exceptional DBA.  Practice your craft and stand firm in your determination to be an Exceptional DBA.

My entry (twitter) – A Firm Foundation:  I shared a story that relates to disaster recovery and database consistency.  In that article, I also explored the immediate impact of changing the page verify for your database (plan cache).

I hope you are ready for some free SQL learning.  Coming up on Thursday January 10th at 6:30 PM Pacific (GMT – 8 ) we will have a stellar opportunity to do just that – Learn!

Edwin Sarmiento will be presenting for us this at the upcoming monthly Las Vegas User Group meeting.  Edwin has chosen (well he left it up to me to decide) to present on Powershell.

Title: Windows PowerShell for the SQL Server DBA

Abstract

Windows PowerShell is becoming the scripting language of choice for managing Microsoft servers and workstations. And while T-SQL is still the scripting language for DBAs to administer and manage SQL Server, there are tons of stuff that can be easily done with Windows PowerShell. In this session, learn Windows PowerShell from the ground up and how you can use it with SQL Server Management Objects (SMO) to administer and manage a SQL Server instance.

Here is the bonus behind all of this – we will be broadcasting the presentation to the virtual sphere.Here are the details for joining the meeting via livemeeting.

Copy this address and paste it into your web browser:

• https://www.livemeeting.com/cc/UserGroups/join

Copy and paste the required information:

• Meeting ID: JRW8JQ
• Location: https://www.livemeeting.com/cc/UserGroups

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.

Code block

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.

Code block

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.

Code block

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.

Code block

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

Code block

ALTER DATABASE [msdb]
		SET PAGE_VERIFY CHECKSUM;

Code block

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.

And the following are the post change results.

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’!

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.

Code block

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

Introduction

Welcome back for the 38th installment in the wildly popular blog party for the SQL Server community.  This is the party that happens on the second Tuesday of each month.  The party was started by Adam Machanic (B|T) just over three years ago.

Each month a new host selects a theme and announces it about a week in advance.  And this month I will be hosting.

Theme

To kick off the new year (2013), we must first adhere to a little tradition.  This is not a T-SQL Tuesday tradition.  It is more of an annual tradition for all to welcome the new year.  Feel free to click the link and sing along!!

Now for the theme.  A common thing for many people to do this time of year is to do a little self reflection.  Some set meaningful goals for themselves.  Fewer actually accomplish those goals or even follow-up after initially setting the goal.

We are not going to set goals as a part of this T-SQL Tuesday – unless you want to.  I want to take a little different spin on the New Year’s “resolution” tradition.  So the theme this month is “Standing Firm.”

The idea for this theme is to start with a little self reflection.  Then to come up with a story relating to one of these words: resolve, resolution, or resolute.  Here are some examples of how these stories may be portrayed.

• Resolve:  A system outage occurred and you “resolved” it.
• Resolute:  You made an executive decision and did not waver from it.
• Resolution:  You discovered a bug and documented a work-a-round resolution for it.
• Resolution:  You have discovered certain T-SQL skills are fuzzy and want to sharpen your ability in that area.
• Resolute:  You are determined to improve performance in your application.

All of these words are very closely related.  It is up to you to determine how you would like to apply them to your T-SQL world.  Your experiences and stories can be loosely or tightly coupled to T-SQL, it is up to you.

And since the theme requires a little bit of self-reflection first, bonus kudos to those that can tie a past experience to a future plan.

The Rules

I know, parties are not supposed to have rules.  Sadly, all parties have some rules – you just may not know them.  These rules are very simple.

1. Your post must go live between 00:00:00 GMT on Tuesday the 8^th of January 2013 and 00:00:00 GMT on Wednesday the 9^th.
2. Your post has to link back to the hosting blog, and the link must be anchored from the T-SQL Tuesday LOGO (found above) which must also appear at the top of the post.
3. Trackbacks should work. However, all comments and trackbacks are moderated, so give me a few minutes. If you think you waited long enough and still don’t see yours, leave a comment below.
4. Tweet your post to the #TSQL2sDay hash tag.
5. Have Fun writing and participating.

Follow these rules, and your post will be included in the roundup that will be posted on the 15^th or 16^th of January.

Bacon wrapped frog legs (twelve of them) for Christmas.  No more drumming for these guys!!

What could be better than bacon wrapped frog legs?  Oh yeah, more Virtual lab setup.

We will delve into setting up a SQL Cluster today.  We will also cover some high level tips for dealing with virtual box.  This will be good information and the type of stuff I would have like to have known heading into setting up a Virtual Lab.

Season Cleaning First.

• Domain Setup – 11^th Day
• Virtual Storage – 10^th Day
• Death and Destruction, err Deadlocks - 9^th Day
• SSRS Schedules – 8^th Day
• Runaway Jobs – 7^th Day
• Maintenance Plan Gravage – 6^th Day
• Table Compression – 5^th Day
• Exercise for msdb – 4^th Day
• Backup, Job and Mail History Cleanup – 3^rd Day
• Service Broker Out of Control – 2^nd Day
• Maint Plan Logs – 1^st Day

On the Twelfth Day of pre-Christmas…

My DBA brought to me a Virtual SQL Cluster.  And with that cluster, we have a a few tidbits for Using VirtualBox.

The first tidbit is an administration aid.  Occasionally it is good to have similar machines grouped together.  At the same time, it is also necessary to start multiple virtual machines at the same time.  This is done through groups in VirtualBox.

Here you can see some of the groups that I have created.  If I right-click on a machine name, I will be presented a menu that has the Group option.

Once I have a group created, I can get a few different options if I were to highlight the group name I would get different options as shown in the following image.

The notable options here are to “Ungroup”, “Rename Group”, and “Add Machine.”  Another option is “Start.”  Though this option is present for the machine menu, the behavior is different.  This option allows you to start the entire group.  This can be a handy tool when dealing with a cluster for instance.

The next handy tidbit is the snapshot.  A snapshot allows point in time image of the VM to be taken so different configurations can be tested – and then quickly reverted i necessary.  Here is what I have for one of my VMs in the snapshot manager.

From this very same screen you can also see one of the many methods available to create a clone of a virtual machine.  The clone icon is the little button above the right hand pane that looks like a sheep.  Cloning a VM is a quick way to create several machines for various purposes.  As you will hear from many people – you should build a base image first, then run sysprep against it.  Sysprep is necessary in order to help prevent problems down the road.

The next tidbit for today is in regards to the file locations for virtual machines and virtual disks.  I recommend changing the default path for the VM files.  This can be done through the preferences option on the file menu.  Shown in the attachment is what it may look like if you have not changed it.  Notice that the default path goes to your user profile directory.

Ignore the red text on this page for now.  We will not be discussing the Proxy.

The last tip is in the network settings within the preferences that we already have open.  In the network settings, we can find an option to configure DHCP settings for the Host-Only Ethernet Adapter.  These are settings you may want to configure to ensure you have more control over the environment.  It is also helpful when looking to configure those IP settings for the FreeNAS that we have already discussed.

As I wrap up these tidbits, I have decided that this is a lot of information to soak in at this point.  So in the spirit of Christmas, I have decided to finish off the clustering information in a 13th day post.  This final post may or may not be available on Christmas day.  Worst case it will be available on the 26th.

Part of that reason is I want to rebuild my lab following the instructions I will be posting and I need time to test it.  I want the instructions to be of great use.

Please stay tuned as we conclude this series very soon.