Rotating Header Image

Microsoft…. Y U NO FIX??



I would like…. if I may… it take you on a strange journey….

Consider this:

  • A SQL Server database with ALLOW_SNAPSHOT_ISOLATION set to true
  • A stored procedure that creates a #temp table in tempdb and subsequently drops it
  • Multiple calls of this stored procedure (running in Snapshot Isolation mode) happening at the same time

Please tell me how/why I would get this error:

Msg 3961, Level 16, State 1, Procedure CREATETABLE, Line 8
Snapshot isolation transaction failed in database ‘tempdb’ because the object accessed by the statement has been modified by a DDL statement in another concurrent transaction since the start of this transaction.  It is disallowed because the metadata is not versioned. A concurrent update to metadata can lead to inconsistency if mixed with snapshot isolation.


Riddle me that!!!! (and yes I saved you the horror of seeing me in skintight green spandex)

SETUP (credit: Loris Chiocca):

USE [master]
CREATE DATABASE [SnapshotIsolationDB]
( NAME = N'SnapshotIsolationDB', FILENAME = N'C:\TEMP\SnapshotIsolationDB.mdf' , SIZE = 102400KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
( NAME = N'SnapshotIsolationDB_log', FILENAME = N'C:\TEMP\SnapshotIsolationDB_log.ldf' , SIZE = 10240KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
USE SnapshotIsolationDB
      ID int NOT NULL primary key
     waitfor delay '00:00:01'
        exec ACCESSTABLE


set transaction isolation level snapshot
begin tran
commit tran

Keep in mind you’ll probably have to rush-click-execute a few times before being able to produce the error…

So this isn’t my code or my find in the first place.

Why am I talking about it then?

3 Reasons…

  1. To hopefully bring more light to this bug
  2. To hopefully help others who run into this problem and their google-fu wasn’t able to point them to the Microsoft Connect Item.
  3. Cause it’s a thorn in my ass right now

Check this out…

And the comment of particular concern:

“Thank you for submitting this feedback. After carefully evaluating all of the bugs in our pipeline, we are closing bugs that we will not fix in the current or future versions of SQL Server. The reasons for closing this bug is because the scenario reported in the bug are not common enough and due to the risk of implementing a fix it unfortunately does not meet the bar for the current version of the product.



If anyone is curious as to how I “fixed” my problem…. I stopped explicitly dropping the #temp tables in my stored procedure and allowed SQL Server to “clean up after itself” as the Snapshot Isolation errors always seemed to have an issue with the:



The question outstanding though is if #temp tables are created with the same name by different SPIDs and they exist in tempdb with the unique name that looks like : #temp_____00000090019545666 how can ANOTHER SPID be affected by the creation and dropping of said #temp table???



Enjoy!! (Follow me on Twitter: @ColinStasiuk)

Post to Twitter Post to Delicious Post to Digg Post to StumbleUpon

*tap tap tap* Hello World?

Let’s see if I can remember how all of this goes….

Start with a “hello”

Insert a picture that hopefully somewhat relates to my blog post


and now proceed to the guts of the post.


So hi everyone!! :)

I have to admit it’s been a VERY long time so I’m probably going to be out of practice so please be gentle.

Have you ever loved something so much that you immersed yourself so completely in it that it became something that you no longer loved.  That was me in respect to the SQL Community in 2014 and most of 2015.

Now don’t get me wrong… I ABSOLUTELY LOVE my #SQLFamily but I allowed blogging, presenting, conferences, MVP status, etc. to basically take over my life.

I decided to take some much needed time off from “it all” to allow myself to fall back in love with doing things like writing posts, presenting, geeking out with others about the awesomeness that is SQL Server.

I wanted a clean slate and a fresh start so I opted out of the MVP program (for now) and while it has been a fantastic 3 years with the award I wanted to ensure that my contributions were because I wanted to make them vs. feeling obligated to present x number of times or blog x number of times or do anything x number of times.

I’ve been working on some very cool projects over the last year or so and have lots of fun things to blog about… I’m not going to worry about ‘flexing’ in my posts and having crazy advanced technical content but it’s going to get back to the basics of where I started blogging which was…. “huh… that’s pretty cool…. I wonder if anyone else knows about this”

Anyways I’m back on Tweetdeck (assuming that’s still what you cool kids use… if not please update me)… so if you feel like it… say “hello” :)

I was going to try and use a new hashtag #SQLove but apparently it’s already in use and I don’t recommend clicking it with safe-search off hahaha


Enjoy!! (Follow me on Twitter: @ColinStasiuk)

Post to Twitter Post to Delicious Post to Digg Post to StumbleUpon

PASS Summit 2014 – I’m Speaking!!!

Good Morning!!

With all the huzzah of PASS announcing the speakers and topics recently I thought I would wait a bit before doing the standard “I’m Speaking” blog post :)

As always it’s an honor to be selected to speak anywhere but the PASS Summit has and always will have a special place in my heart.

I’ve said it before and I’ll say it again… it’s the most wonderful time of the year for me as I get to recharge my SQL battery and see all the amazing friends and #SQLFamily I’ve had the pleasure of knowing over the years.

Last year I had two sessions and felt VERY overwhelmed and nervous (as it was my first ever Summit as a speaker) this year I’m hoping to be a bit more “myself” and enjoy the experience that much more.

Have a great day and I’ll see you in November!!!


Enjoy!! (Follow me on Twitter: @ColinStasiuk)

Post to Twitter Post to Delicious Post to Digg Post to StumbleUpon

Waiting For A Pot To Boil… Restore Database Progress


Ever kicked off a restore and didn’t set the STATS parameter?

Ever had a separate application handle a restore and not give you any feedback at all on how far along a restore is?

Well if your answer is no than this post / script is NOT for you  :)


If your answer is YES then this script should tickle your fancy:

SELECT	A.start_time as 'RestoreStartTime',
		DATEDIFF(minute, A.start_time, getdate()) as 'CurrentRestoreDurationInMinutes',
		((DATEDIFF(minute, A.start_time, getdate()) * 100) / A.percent_complete) as 'EstimatedTotalDurationInMinutes',
		ROUND(((DATEDIFF(minute, A.start_time, getdate()) * 100) / A.percent_complete) - (DATEDIFF(minute, A.start_time, getdate())), 2) as 'EstimatedTimeRemainingInMinutes',
 FROM	sys.dm_exec_requests A CROSS APPLY 
		sys.dm_exec_sql_text(A.sql_handle) B
 WHERE	A.percent_complete != 0


Enjoy!! (Follow me on Twitter: @ColinStasiuk)

Post to Twitter Post to Delicious Post to Digg Post to StumbleUpon

List All Statistics For a Column – Update

Msg 5074, Level 16, State 1, Line 1
The statistics ‘StatName’ is dependent on column ‘ColumnName’.

Msg 4922, Level 16, State 9, Line 1

ALTER TABLE DROP COLUMN ColumnName failed because one or more objects access this column.

So what is a nice quick and easy way to list all the statistics that are dependant on that column?

My googling skills are obviously subpar this morning cause I couldn’t find anything to help me and it seems to me that this would be something that lots of people have run into. This is what I came up with:

DECLARE @SearchColumnName	nvarchar(100)
SELECT	@SearchColumnName = 'SearchColumn'
SELECT	OBJECT_NAME(A.object_id) as 'TableName',
		C.stats_column_id AS 'StatisticID', as 'StatisticName',
		COL_NAME(C.object_id, C.column_id) AS [Name]
		sys.stats B ON B.object_id = A.object_id INNER JOIN
		sys.stats_columns C ON C.stats_id = B.stats_id AND C.object_id = B.object_id
WHERE	COL_NAME(C.object_id, C.column_id) = @SearchColumnName

I dunno… seems like there should be a sp_ListAllStatisticsForAColumn system stored proc or something LOL.

If you have something easier/better please post it in a comment!!

Change your WHERE Clause to this:

WHERE	COL_NAME(C.object_id, C.column_id) = @SearchColumnName AND  NOT IN (Select ISNULL(name,'') from sys.indexes)

If you want to filter out the statistics that are created based on indexes (which can’t be dropped anyways)


Post to Twitter Post to Delicious Post to Digg Post to StumbleUpon

Twitter links powered by Tweet This v1.6.1, a WordPress plugin for Twitter.