Presenting – The Odyssey Continues

As I’ve mentioned before, I set a goal at the beginning of the year to average a presentation a month in 2014. My presentation, Designing a Recovery Strategy, at the Wisconsin SQL Server User Group this month put me halfway there.

In June, I am presenting Designing a Recovery Strategy again at SQL Saturday #307 in Iowa City on the 7th. And then, for the first time ever, I will be presenting twice on the same day at SQL Saturday #286 in Louisville on the 21st. I’ll be doing Designing a Recovery Strategy and Interrogating the Transaction Log (now with 2014!) If you are close to either event, I highly recommend attending. It is a great opportunity to get a free day of training and meet other members of the SQL community.

I close out the month by presenting Interrogating the Transaction Log at MADPASS June 25th. It has been a wild ride, but I’ve enjoyed every minute of it. Presenting is a great way to give back to the SQL community and I learn more than I thought possible every time I put a presentation together.

sp_AutomateDBRestore – Now With Table-Valued Parameters!

After several weeks of procrastination, I have finished the latest version of sp_AutomateDBRestore. The previous versions of the procedure built the restore statements using sys.database_files for the restored database, msdb.dbp.backupset, and msdb.dbo.backupmediafamily. This process works great if the instance is up and running, but if the instance is unavailable it won’t work.

To handle this circumstance, I have added a table-valued parameter to the procedure allowing a list of backup files and backup file types to be passed in. This way, you can generate a restore statement as long as you have the backup files available. The proc uses RESTORE FILELISTONLY and RESTORE HEADERONLY statements to retrieve the physical and logical file names. Additionally, I’ve added parameters @backupfilepath and @logfilepath to allow a restore of the database to a different physical file than the source database. Here is the code to create a user-defined table type, followed by a call using the new parameters:

USE [master]

CREATE TYPE [dbo].[backupfiletype] AS TABLE(
	[backupfilename] [varchar](255) NULL,
	[backupfiletype] [varchar](30) NULL
DECLARE @backuptvp AS backupfiletype

INSERT INTO @backuptvp

DECLARE	@return_value int

EXEC	@return_value = [dbo].[sp_automateDBRestores]
		@sourcedbname = multifile,
		@restoredbname = multifile,
		@droprestoredb = 0,
		@noexec = 1,
		@datafilepath = 'C:\testrestore\',
		@logfilepath = 'C:\testrestore\',
		@TVP = @backuptvp

SELECT	'Return Value' = @return_value


You can find the code for the updated procedure at my Presentation Slides and Scripts page under Wisconsin SQL Server User Group. As usual, feel free to leave any questions or comments in the comments section for the post.

Categories: PASS, SQL Server, T-SQL Tags: , ,

SQL Saturday #291

Speaker dinner at the Schnitzel Platz (MarkV is an instigator) -Thanks to Dave Mattingly for the photo

Speaker dinner at the Schnitzel Platz (MarkV is an instigator) -Thanks to Dave Mattingly for the photo

SQL Saturday #291 took place this past Saturday at DeVry University in Addison, Illinois. It was my eighth SQL Saturday, and it was the first one I helped run. I want to thank Bill Lescher (t), Wendy Pastrick (b|t), Bob Pusateri (b|t), Jamie Samsel, and Andy Yun (b|t) for all of their hard work. I had a great time and have heard positive feedback from all of the attendees I’ve spoken with.

The hard work these folks put in wouldn’t mean anything without the speakers who volunteer their time and pay their own way to attend these events. I especially want to thank Jim Dorame (b|t), Merrill Aldrich (b|t), Eric Boyd (b|t), and Grant Fritchey (b|t). With only 15 minutes’ notice, Jim stepped in to replace a speaker who did not arrive, moving from his scheduled noon slot to 9:15. Then Jim, Merrill, Eric, and Grant held a PowerShell panel in Jim’s original slot. Their willingness to take on extra responsibility epitomizes the SQL community for me, and went a long way to making the event such a success.

Finally, I want to thank everyone who attended SQL Saturday #291. Without you, the SQL community would not exist. I spoke to a number of first-time attendees, and I hope they all come back again. The SQL community has given me more than I could ever give back. I’ve made great friends, learned from world-renowned experts, and grown personally and professionally. I met a current co-worker at SQL Saturday Chicago last year and my work in the community had a lot to do with my getting that job.

If you attended SQL Saturday and got something out of it, consider giving back. Presenting, blogging, volunteering, and answering questions on forums are all great ways to give back. If you haven’t already, register for your local PASS user group. If you don’t have a local user group, consider starting one. I’ve run the Chicago SQL Server User Group since July of 2012 and have had a blast. Feel free to contact me if you have any questions.

Track VLF Usage with usp_VLFTracker

April 24, 2014 Leave a comment

When SQL Server allocates or grows a transaction log file, it creates a number of virtual log files (VLFs) within that allocation. When a transaction log record is written to a VLF, its status changes from free to active. In SIMPLE recovery model, the VLF will remain active until the next checkpoint is run and no log records are part of active transactions. In FULL recovery model, the VLF will remain active until all transaction log records in the VLF are no longer needed. Log records are needed if they are part of an open transaction, have not been backed up by a transaction log backup, or are needed for mirroring or log replication. You can read more about transaction log architecture here.

DBCC LOGINFO() returns information about each of the VLFs in a database’s transaction log, including the status. DBCC LOGINFO() will return a status of 2 for active VLFs and a status of 0 for free VLFs. By tracking VLFs rate of status change, I can get an idea of how much activity is being written to the transaction log.

I needed to determine the rate databases on an instance were generating log records. To do this, I wrote the stored procedure below to track the status of virtual log files in each database log file using DBCC LOGINFO(). I have a job scheduled every 15 minutes to write the results to a table for analysis.

The result set of DBCC LOGINFO() added the recoveryunitid column, making separate temp tables necessary for SQL Server 2008 and SQL Server 2012. I initially tried using conditional logic to create the version-specific schema but found SQL Servedr will not let you run two CREATE TABLE statements for the same object in a script. Thanks to Keith Buck for the suggestion of creating both temp tables and using conditional logic to determine which table to use.

This version uses Aaron Bertrand’s (b|t) sp_foreachdb, which is on my short list of the coolest things on the Internet. I highly recommend you use it. If you cannot, the procedure is easily modified to use sp_msforeachdb. Though I really recommend you use it (here are some reasons you should).

Created By - Frank Gill
Created On - 2014-04-23


Procedure dumps the contents of DBCC LOGINFO() to a temp table
for each database on the instance. The output of DBCC LOGINFO()
changed with SQL Server 2012, making two different temp tables
necessary. After the results of DBCC LOGINFO() are gathered for 
each database, they are written to a permanent table for 

USE yourdbname

-- Drop stored procedure if it already exists
     AND SPECIFIC_NAME = N'usp_VLFTracker' 
   DROP PROCEDURE dbo.usp_VLFTracker



	CREATE TABLE #vlf2008
	(fileid INT
	,filesize BIGINT
	,startoffset BIGINT
	,fseqno INT
	,vlfstatus INT
	,parity INT
	,createlsn NUMERIC(25,0))
	CREATE TABLE #vlf2012
	(recoveryunitid INT
	,fileid INT
	,filesize BIGINT
	,startoffset BIGINT
	,fseqno INT
	,vlfstatus INT
	,parity INT
	,createlsn NUMERIC(25,0))

	(dbname SYSNAME
	,currentts DATETIME
	,fileid INT
	,filesize BIGINT
	,vlfstatus INT)
	DECLARE @version VARCHAR(20)

	IF SUBSTRING(@version,1,2) = '10'
		EXEC sp_foreachdb @command = N' 
		USE ?
		INSERT INTO #vlf2008
		INSERT INTO #vlfdb
		SELECT DB_NAME(), CURRENT_TIMESTAMP, fileid, filesize, vlfstatus FROM #vlf2008

		DELETE FROM #vlf2008
	ELSE IF SUBSTRING(@version,1,2) = '11'
		EXEC sp_foreachdb @command = N' 
		USE ?
		INSERT INTO #vlf2012
		INSERT INTO #vlfdb
		SELECT DB_NAME(), CURRENT_TIMESTAMP, fileid, filesize, vlfstatus FROM #vlf2012

		DELETE FROM #vlf2012
	INSERT INTO youdatabasename..yourtablename
	SELECT dbname, currentts, vlfstatus, COUNT(*) AS vlfcount FROM #vlfdb
	GROUP BY dbname, currentts, vlfstatus
	ORDER BY dbname, vlfstatus

	DROP TABLE #vlf2008
	DROP TABLE #vlf2012
	DROP TABLE #vlfdb


SQL Milestones

April 13, 2014 Leave a comment

The past few months have been full of firsts for me.

  • I started working at Apparatus February 17th. I wanted opportunities to learn new things, and I have not been disappointed.
  • Last night, I gave my fourth presentation of the year. With user groups lined up from now through August, I am on pace to meet my goal of averaging one presentation per month this year.
  • March marked the second anniversary of this blog. In that time, I’ve written 61 posts. While this is well short of my initial goal of a post a week, I’m hoping the constant learning opportunities at Apparatus will help me pick up the pace.
  • I hit 1,000 views on this blog for the first time in a month. I am really excited about this, but at the same time, it shows me how much room I have to grow.

It is just about four years since I attended my first SQL Saturday. Thanks to Jes Borland (b|t) for inspiring me to make my voice heard. And it’s just about three years since I attended SQL Skills’ Interals Immersion Event. Thanks to Paul Randal (b|t) and Kimberly Tripp (b|t) for giving me my first opportunity to present on SQL Server outside of work. Finally, I want to thank the members of the SQL community who have taught, helped, and inspired me. There are too many of you to mention, but you’ve made the past four years the most enjoyable and fulfilling of my life.


Categories: Career, Presenting Tags: , ,

Upcoming Presentations

March 24, 2014 Leave a comment

I'm a presenting fool!

I’m a presenting fool!

I am continuing to work toward my goal of averaging a presentation a month this year. So far I’ve presented Designing a Restore Strategy at I-380 PASS, QCPASS, and IndyPASS. I’m presenting it at the Chicago SQL Server User Group and SQL Saturday 291 – Chicago in April. The presentation centers around my stored procedure sp_AutomateDBRestores, which I hope to have documented fully this week. You can download the code here. If you are interested in attending the user group, you can RSVP here. If you haven’t registered for SQL Saturday Chicago, I recommend it highly. It is a wonderful opportunity to get a day’s worth of free training and network with your peers.

If you are looking for additional training, take a look at the pre-conference sessions being offered by Adam Machanic (b|t) and David Klee (b|t). Adam is the author of sp_whoisactive, which I can’t recommend highly enough, and is presenting Better Performance Through Parallelism. David is an expert in virtualizing SQL Server, so it is fitting that he is presenting Virtualization for SQL Server DBAs.


March 17, 2014 Leave a comment

I’ve posted a procedure that I wrote some time ago to make Adam Machanic’s sp_whoisactive easier to execute. It dumps the results of sp_whoisactive to a temporary table and selects the contents of that table. I’ve set EXEC sp_exec_whoisactive as one of my keyboard shortcuts in Management Studio. This makes it really easy to execute the proc repeatedly. If you aren’t using sp_whoisactive, I would advise stopping what you are doing and downloading it now. It is the most useful piece of code I’ve ever come across.

USE [master]

--Drop procedure if it exists
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_exec_whoisactive]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].sp_exec_whoisactive

-- =============================================
-- Author:		Frank Gill
-- Create date: 2014-03-17
-- Description:	This procedure executes Adam Machanic's sp_whoisactive, dumps the results to a temp table, and then selects the contents
--				'EXEC sp_whoisactive' can be set up as a keyboard shortcut to allow easy execution of the proc 
-- =============================================
CREATE PROCEDURE sp_exec_whoisactive

--Drop temp table if it exists
IF OBJECT_ID('tempdb..#WhoIsActive') IS NOT NULL 
	SELECT 'Dropping'
	DROP TABLE #WhoIsActive

--Create temp table to hold the results of sp_whoisactive
([dd hh:mm:ss.mss] VARCHAR(20)
,[dd hh:mm:ss.mss (avg)] VARCHAR(20)
,[session_id] SMALLINT
,[sql_text] XML
,[sql_command] XML
,[login_name] SYSNAME
,[wait_info] NVARCHAR(4000)
,[tran_log_writes] NVARCHAR(4000)
,[tempdb_allocations] VARCHAR(30)
,[tempdb_current] VARCHAR(30)
,[blocking_session_id] SMALLINT
,[blocked_session_count] VARCHAR(30)
,[reads] VARCHAR(30)
,[writes] VARCHAR(30)
,[physical_reads] VARCHAR(30)
,[query_plan] XML
,[used_memory] VARCHAR(30)
,[status] VARCHAR(30)
,[tran_start_time] DATETIME
,[open_tran_count] VARCHAR(30)
,[percent_complete] VARCHAR(30)
,[host_name] SYSNAME
,[database_name] SYSNAME
,[program_name] SYSNAME
,[start_time] DATETIME
,[login_time] DATETIME
,[request_id] INT
,[collection_time] DATETIME)

--Execute sp_whoisactive with #WhoIsActive set as the @destination_table parameter	
EXEC master.dbo.sp_WhoIsActive
@get_plans = 2, 
@get_outer_command = 1, 
@get_transaction_info = 1, 
@get_avg_time = 1, 
@find_block_leaders = 1,
@destination_table = #WhoIsActive

--Select the contents of #WhoIsActive, ordered by the blocked_session_count
--The ORDER BY clause can be removed or modified as necessary
SELECT * FROM #WhoIsActive
ORDER BY blocked_session_count DESC


Adam is teaching an all-day pre-conference seminar entitled Better Performance Through Parallelism on Friday, April 25th. It is the day before SQL Saturday #291 Chicago. This is a great opportunity to get a day’s worth of training from the author of sp_whoisactive for a very reasonable price. You can get more information and register here.

Categories: Uncategorized

Get every new post delivered to your Inbox.