Problem removing files from TempDB

I recently ran into an interesting problem while attempting to remove approximately half of the TempDB data files configured in a testing environment. As you might expect, there are various SQL Server tasks that are performed infrequently by a DBA, and this is a good example of one of them. Most environments have usually been misconfigured with too few TempDB data files or wrongly sized usually resulting in the classic allocation page contention problems (which is explained by this excellent SQLSkills article “The Accidental DBA (Day 27 of 30): Troubleshooting: Tempdb Contention“), but this particular environment had been temporarily over provisioned with disks and TempDB data files (for testing purposes).

Bonus fact: SQL Server 2016 attempts to automatically address the tempdb datafile allocation page contention problem by defaulting to 8 TempDB data files (or less if the number of cores is smaller). This can be overridden on install through the GUI or by using the /SQLTEMPDBFILECOUNT switch if performing a command line installation. Further optimisations have been implemented such as the adoption of uniform extent allocations by default and auto-growing all files simultaneously -both behaviours would formally have required turning on Traceflags 1117 and 1118. Several other improvements have been made to the performance of TempDB database which you can read about yourself in this (currently preview) documentation.

 

So the plan was to remove each TempDB file one by one, restart the instance (if required) and decommission those spare disks so that they can be returned and reallocated elsewhere in the environment. In order to remove each data file we would:

  1. Empty each file at a time (using DBCC SHRINKFILE).
  2. Remove each file upon empty.

Now there was a time (prior to SQL Server 2005) when we were told to tread very very carefully when shrinking TempDB files, and doing so could result in corruption. This perception remains with some of the longer serving SQL Server professionals (I question myself frequently) but if we take a look at KB307487 article “How to shrink the tempdb database in SQL Server” we can see that it is now safe to do so -although (as the Knowledge Base article states) certain scenarios can cause the shrink operation to fail.

So I ran the following code for each TempDB data file:

DBCC SHRINKFILE (tempdb_15, EMPTYFILE)
go
ALTER DATABASE [tempdb] REMOVE FILE [tempdb_15]
GO

The code succeeded for the first few files, right up to TempDB data file 12, where I hit the following error:

DBCC SHRINKFILE: Page 14:56 could not be moved
because it is a work table page.
Msg 2555, Level 16, State 1, Line 1
Cannot move all contents of file "tempdb_12" to
other places to complete the emptyfile operation.
DBCC execution completed. If DBCC printed error
messages, contact your system administrator.
Msg 5042, Level 16, State 1, Line 1
The file 'tempdb_12' cannot be removed
because it is not empty.

As odd as this error was (especially since the SQL instance was not currently “in use”) I decided to bounce it but after restarting the instance was again greeted with the same error message! After some very quick Google-Fu I came across an old MSDN Forum question “DBCC SHRINKFILE: Page 4:11283400 could not be moved because it is a work table page.” I ruled out some of the responses but came across the response by Mike Rose:

“I realize this is an old thread but I have found that in most cases work tables are related to Query Plans.

Try issuing the following commands and the shrinking the tempdb:

DBCC FREESYSTEMCACHE (‘ALL’)

DBCC FREEPROCCACHE

there will be some performance hit for this as SQL will have to recreate its Query Plans, but it should allow you to shrink you TEMPDB.

Hope this helps,

M Rose”

 
Now my initial thoughts were that this response was clearly nonsense since I had just restarted the SQL Service for the instance (thereby flushing the SQL caches), but hey what the hell, why not give it a whirl….

…and success! It worked.

Apart from the flushing of the caches, the outcome was even more strange to me for another reason. Upon reboot TempDB is “recreated” upon startup*1 and I would have expected that fact alone to have fixed the problem but from the behaviour I had experienced, something had persisted across instance restart.
*1 you may want to read this interesting post by Jonathan Kehayias “Does tempdb Get Recreated From model at Startup?

Also of interest to me was whether running FREEPROCCACHE and FREESYSTEMCACHE was overkill so when the opportunity arose I attempted first to try only clearing the systemcache specific to TempDB through:

DBCC FREESYSTEMCACHE ('tempdb')

…and then tried clearing temporary tables and table variables through:

DBCC FREESYSTEMCACHE ('Temporary Tables & Table Variables')

…and then by using both together. Sadly these did not seem to work.

On my final opportunity I tried clearing the procedure cache (DBCC FREEPROCCACHE) and after running this a few times it appeared to solve the problem and I was able to remove the TempDB file without error.

 


Clearing system caches on a Production SQL Server instance is never a good thing and should be avoided, but at least the next time I run into this problem I have (what appears) to be a work around to the problem I ran into and at least the requirement to remove TempDB data files should be a rare occurrence for most of us!

I confess that I am left with a few unanswered questions and clearly what I experienced does make me doubt the results somewhat (and my sanity) and there were further tests that I would like to perform at some point. So if you run into this problem yourself I encourage you to try this (disclaimer: your fault if you destroy your SQL Server😉 ) and I would be delighted if you leave feedback of what works for you (and what doesn’t)!

Posted in SQL, SQLServerPedia Syndication, Storage | Tagged , , | 3 Comments

Have you submitted to SQL Nexus yet?

sqlnexus

Nexus noun – “That which unites or binds”

If you have not already heard, SQL Nexus (a next generation SQL Server focused event) will be held on May 2nd – 4th 2016 in Copenhagen, Denmark.

The “idea” for SQL Nexus (w|t) was first conceived in 2012 more as a future pipe dream than anything else and it was something that I had in mind when we delivered the second SQLSaturday Cambridge in 2012 in which we included a dedicated SharePoint track.

At that time, the idea was for a SQL Server centric conference that embraced the whole of the MS Data Platform but SQLSaturday did not feel like the ideal platform for something of the scale required. This vision was discussed with Régis in early 2013 and lay dormant while we were both very busy with alternative commitments. But every great idea needs great people, the right time and the right place and it is through the efforts of Régis Baccaro (b|t) and Kenneth M. Nielsen (b|t) over the last few months that has helped drive this idea forward into reality to what will be a sensational event.

Thanks also must go to the following companies who have been pivotal in making this event possible:


The conference will be held on May 2nd to 4th at Cinemaxx in Copenhagen with one day of pre-conference sessions and 2 full days of breakout sessions. The program for the pre-conference is ready and will feature leading Data Platform professionals including:

  • Allan Hirt (b|t)
  • Itzik Ben-Gan (b|t)
  • Tim Chapman (t)

…and many others!

If you have not yet submitted a regular session then you have until the 15th February 2016, but why delay? You can submit your sessions through the Call for Speakers page.

 
If you would just like to attend then visit the website for more information and register!

Useful links :
Call for speakers
Registration
Main Website : sqlnexus.com
Follow us on Twitter @sqlnexus and use #sqlnexus in your tweets.
And if you have any questions then Email Us.

Stay tuned for more info about the event…and look forward to seeing you soon in Copenhagen!

Posted in Community, Events, SQL, SQLServerPedia Syndication | Tagged | Leave a comment

MVP today

homerToday I became a Microsoft Data Platform MVP for the very first time. Whilst I am delighted to receive the award and immensely grateful to Microsoft for awarding it to me, I won’t pretend that this journey has been easy or expected (which I wrote about here). Some of you will also (by now) be aware of personal problems that my family and I have been dealing with for the last 2 and a half years which I also wrote about in Dear #sqlfamily, so you will appreciate that this news today makes the award extra special to me. It is/was something that I never believed would happen.

My biggest hope with my new found status is that it will help facilitate and encourage my community efforts both public and private so that I can give even more of myself back but also hope that I can exponentially improve my technical skills in any areas that I may be lacking in this ever changing Data Platform landscape. I am flattered to join this community of fellow Professionals who I not only look up to, but which I also consider many to be personal friends.

This post would not be complete without thanking a few very special people.

 
First and foremost I would like to say a massive thank you to Niko Neugebauer (w|t) a man who I first met in 2012 and someone who has been a personal inspiration to me. Technically brilliant but also a Community champion and if I ever needed a role model (which I do), then Niko is it. Niko has not only believed in me from the beginning but has supported me whenever I have asked for his help or advice. Through his support, it is credit to him above all others that today I can call myself MVP.

Another Community devotee who I have also found inspirational and deserves my thanks has been my good friend and fellow MCM Edwin Sarmiento (b|t), who I first met in 2010 and has been encouraging me to succeed ever since.

I would also like to thank everyone who nominated me for the MVP award over the years. This time around I managed to complete 11 submissions, each taking me approximately 1 hour to fill in, and unfortunately ran out of time on the last 2. I even had 4 nominations that I had missed and had expired. So to those people who nominated me, regardless of whether I managed to fill in your nomination or not I would like to thank you all from the bottom of my heart for believing that I was worthy of the award. Without you this could not have happened.

Thanks also go to SQLPASS and its staff, directors and members who have enabled me to grow both technically and socially over many years.

Finally I would like to say a big thanks to my family who have endured my Community trips for years and continue to do so. This award is as much for you as it is for me.

Posted in Community, Personal Development, SQLServerPedia Syndication | 6 Comments