Resetting TempDB data files

I'm Back!

Have you ever missed a personal deadline for something (like a making a doctor's or dentist appointment) and the further you get past it, the harder it it is to do it? That happened it me and I found that I can go a LONG time before I finally break down and do it, 2 years and 8 months, apparently. Well in that time I have learned quite a bit about SQL and hope to be able to post something informative about every two weeks that isn't just a rehash of what you can find on other peoples blogs. Anyway, on with the post..

TempDB Configuration

Lately I have been doing some research and testing into the configuration of Tempdb. I won't rehash all the details of how or why you should change the default settings since there are people who I would just be paraphrasing:

• Demystify Tempdb Performance and Management - Robert Davis
• TempDB Performance and Configuration - Kendra Little
• Building Perfect SQL Servers, Every Time - Joey D'Antoni
• Correctly Adding data Files to TempDB - Paul Randall

What I am going to do is tell you about an error I kept getting when I was testing out some different configurations and how I resolved it.

Almost all of the information that you find on the internet about TempDB configuration tells you that you need to have more than one data file for TempDB so you don't suffer performance issues related to page allocation contention. There are some varying opinions on how many you need to have. Some say 1 file per core up to 8, some say 1 file for every 2 cores. There are also opinions on how to properly size the data files and the log file. You can read about them in the links above or with a google search. One important thing to note is that most everyone says you need to right size your data files and turn off auto-growth. This allows TempDB to allocate space in a round robin fashion. If one file is much bigger and has more free space, it will get the writes until the free space is reduced to the size of the other files.

My current task at work is to come up with a script that can automatically create the TempDB files and size them. In our current build doc we just create 8 TempDB files (even if we only have 4 cores) and size them at 1GB with auto grow at 1GB. My plan was to mess around with some different configurations and try out different numbers of data files. I the first test, I just took our existing 8 file standard and sized them to 80% of the disk that they are on.

--- Set the number of files
SET @number_of_files = 8
--- Get the Drive size for TempDB
SELECT @drive_size_mb=total_bytes/1024/1024 FROM sys.dm_os_volume_stats (2,1)
--- Calculate 80% of the total drive size and divide that by the number of files to get individual file size
SELECT @individ_file_size = (@drive_size_mb*.8)/@number_of_files

Once, I was finished with that script I wanted to reset TempDB so that it only had one file so I could try something different. I used DBCC SHRINKFILE to empty out each TempDB file and then REMOVE FILE to remove them.

USE TempDB
GO
DBCC SHRINKFILE (tempdev8,EMPTYFILE);
GO

ALTER DATABASE TempDb
REMOVE FILE tempdev8;
GO

The problem is that when I tried to do that I got an error message:

DBCC SHRINKFILE: Page 9:32 could not be moved because it is a work table page.
Msg 2555, Level 16, State 1, Line 4
Cannot move all contents of file "tempdev8" 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 6
The file 'tempdev8' cannot be removed because it is not empty.

It looks like something is using my TempDB files so I can't get rid of them. (This is a test server but it does have some other non-prod stuff running on it). I tried restarting SQL service and running the script as soon as it started but I still got the error on most of the files. Something started using the TempDB data files right when SQL started and I couldn't get them deleted. I started thinking about how I could force everything to use just one of the TempDB files so I could delete the other ones. Can you figure out what I did based on something I have already said in this article?

The solution that worked was I increased the size of the main TempDB file so that it was much larger than the other files. Then when I restarted SQL Service all the TempDB writes went to that files since it had more free space then the other ones. Right after restart I ran my delete script and was left with one TempDB datafile so I could start working with another configuration.

Sum Up

I know this isn't a groundbreaking post (gimme a break, it's my first one back) but I love it when you can take some information that you are learning or just read about and then you apply it in a real life situation like this. I'll be back in two weeks, no idea of a topic yet but I'll think of something.

Hasta Luego,
RutherfordTX

Professional Development Planning

I went to a Cactuss meeting last night (the local Austin SQL users group) and heard a talk by A.J Mendo (twitter|website) about Professional Development planning and it put a name to what I am trying to do in my career right now so I thought I would share what I took away from it.

1. Own Your Career

Nobody is going to walk up to you and give you advancement opportunities (unless your last name is the same as the CEO's). You need to make yourself valuable and decide exactly what you want your career to look like. Do you want to be a DBA? A Manager? A VP? Think about what you want to gain out of your career and work towards it. That brings us to the next point

2. Set Goals

Take the time to sit down and map out some goals for yourself. Think about what you can do today. Think about what you can do in five years. Think about what you can do in 10 years. Goals can be as simple as joining Linkedin or Twitter or more complex like getting certified. Don't beat yourself up if you don't meet your goals. If you wanted to get certified by April but weren't ready, Don't Sweat It, reevaluate your goals and give yourself a new deadline. The important part is to keep evaluating where you are and where you want to be.

WRITE IT ALL DOWN. Get a journal and make a plan in it. Then go back and re-read it and revise it as needed.

3. Join the community

The best way to learn is to find people who do what you do only better and talk to them. There is a User Group in almost every major city for almost anything. You have your SUGs and your LUGs and your MUGs and your Pugs. Join one, go to the meetings and get involved. Raise your hand and ask questions. Answer questions. Introduce yourself. Stay late and meet people. Exchange Business Cards. Network. You never know what can come out of a chance encounter. Just by going to the CACTUSS meeting last night I came away with two job opportunities for a friend of mine who is currently unemployed.

Join online groups. There are tons of forums on the internet. Find one and join it and spend some time there. Many of us in the IT industry, especially those of us at small to mid-size businesses, don't have people to talk to about what we do and what we are doing wrong and how we can do it better. Forums are a great place to find that needed camaraderie.

4. Learn and Make Time to Learn

This one should be a no-brainer for anyone in the tech industry. We need to learn constantly just to keep up with our jobs. I buy books and subscribe to magazines and read blogs and try to listen when I can. It goes beyond just learning how to do what you do. Learn about different or better ways to do it. Learn about things you didn't even know you could do. I wish I had researched SSRS a year ago, I have been doing things with third party apps like Cognos and Crystal I could have been doing straight from SQL.

Make time to learn. Find a time of day you can stop working and read something. I use the netvibes website to group rss feeds of the blogs I read into one page so when i get to work in the morning I can get all the new posts from one place. Here is the public page to my SQL tab in netvibes: http://www.netvibes.com/rutherford#SQL .

Don't be afraid to learn at work. I have never met a boss who would get angry at an employee trying to do his job better (as long as productivity doesn't suffer).

5. Build a brand.

I read an excellent article by Andy Warren (website|twitter) which can be found here about building your brand. That article actually inspired me to join twitter, start this blog and join my local user group. I won't try to repeat what he says, I'll just ask you to read it and hopefully it will change your career life like it did mine.

If you are a clock watcher who thinks of his/her job as a way to pay the bills, this post probably isn't for you. But if you love your career life (not necessarily your job) as much as you love your home life, I hope you give this some thought.

Hasta Luego,

RutherfordTX

First Post!

Howdy. I have been in IT for over 15 years but have just recently developed an interest with MS SQL server. I am in the process of studying to get certified and have been reading SQL books like crazy to help me learn. I want to use this blog to help me in my learning process. I remember from school that I always learned a subject better if I had to write a paper at the end of it. I also will post some of the real world problems I encounter as I try to manage, write code and create reports for the two production SQL Databases that we run at my office.

I thought for this first post (besides introducing myself) I would list some of the resources that I have used in the last year as I have begun to familiarize myself with MS SQL server and T-SQL.

Books:

• Microsoft SQL Server 2008 T-SQL Fundamentals by Itzik Ben-Gan
• Transact SQL Cookbook by Jonathan Gennick
• SQL Server 2005 Implementation and Maintenance - Microsoft Press

Websites:

• SQL Skills
• SQL Blog
• SQL Server Magazine
• SQL Server Central
• Kendal Van Dyke's Blog

Twitter - I joined twitter and started to follow some SQL pros that I had heard of:

• Paul Randall
• Kimberly L. Tripp
• Brent Ozar
• Andrew Kelley

Training:

• The Training Consortium
• New Horizons

This list is tiny compared to the resources available out there for the budding SQL star. Hit Google and see what you can find. Let me know of you favorite sites.

Hasta Luego,

RutherfordTX