Solved

Sql Server TempDb data file too big

Posted on 2011-02-17
17
1,265 Views
Last Modified: 2012-05-11
On our production database (Sql Server 2008 R2 64 bit) our temdb is too big for the drive it is on.  When we run exec sp_spaceused it says it is using 3 MB of the 40 GB allocated.  When we run "dbcc shrinkdatabase(tempdb,100,truncateonly)" it says that there isnt enough unallocated space to shrink the file.  When we run "dbcc shrinkfile (tempdev, 100)" it does shrink it by about 1 GB (if it has been a couple days since we last shrank it).  

On our dev database (same software) we had the same problem with the same behavior but after we restarted the service, "dbcc shrinkfile (tempdev, 100)" did successfully shrink the file down to 100 MB.

My question is, how can I shrink that file without restarting the service or putting the database in single user mode (since it is our production database we cannot stop it without consequences).  If the answer is simply "it isnt possible" then please provide links to documentation of that fact.

Thank you.
0
Comment
Question by:dave4dl
  • 5
  • 3
  • 3
  • +3
17 Comments
 
LVL 9

Expert Comment

by:damerval
Comment Utility
Hello Dave,
My reading of the following article indicates that what you are trying to do is not possible in SQL server:
http://support.microsoft.com/kb/307487
Let me know if you agree or not. I will keep doing research in the meantime.

Philippe
0
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
Is it possible to move the tempDB from one location to another location?

http://blog.sqlauthority.com/2007/04/01/sql-server-tempdb-is-full-move-tempdb-from-one-drive-to-another-drive/
0
 
LVL 9

Expert Comment

by:damerval
Comment Utility
The indications at the end of the article you point to say the changes do not occur until the server has been restarted.
0
 
LVL 15

Author Comment

by:dave4dl
Comment Utility
damerval,
That is the same article that we were reading as well.  Methods 2 and 3 do not say that restarting is required (and, in-fact, we did not have to restart after trying these methods on our dev platform, but those commands would not work right until after we rebooted).  So the question is, how do we make it work without restarting first.

Sharath,
We did try that and discovered the same thing that the article you posted says: "However, no changes are made to TempDB till SQL Server restarts".

0
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
Do you have any issues restarting the SQL Server service? I know that it impacts who are all working on same server. But schedule a downtime for restart.
0
 
LVL 15

Author Comment

by:dave4dl
Comment Utility

Sharath_123,

Well, that is what my question is.  How do I do shrink the file *without* restarting the service (or putting it in single user mode).  Like I say in my original post, this is our production server so scheduling downtime for a restart is hard.  In addition, I would like to find a way to prevent this from continually happening.  If I have to restart the service every time, I cannot put it in a script that runs automatically periodically (or more accurately, it would be dangerous if I did).
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>> In addition, I would like to find a way to prevent this from continually happening.  <<
You need to find more disk space somewhere else and add another file to tempdb there.

>>I cannot put it in a script that runs automatically periodically (or more accurately, it would be dangerous if I did). <<
That is a very bad idea.  You should not have to change the size of tempdb.
0
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
I overlooked your question. As aceprkins said, add additional files in another derive where you have more space.
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 15

Author Comment

by:dave4dl
Comment Utility
Acperkins,
Thanks for jumping in to help (and thanks to Sharath and damerval for your help too).  I know that I can solve this problem right now by restarting and that we can use a different drive for the data file (which we will do eventually).  The problem remains even if we do this though.   The file continues to grow despite the fact that it doesn't need to (it is using 40 GB but only needs 3 MB right now and even with that scenario the file continues to grow).  So if we move it to a 500 GB drive then it will eventually use up that whole drive and we will have to shrink the file (and thus we are faced with the same problem).

So I just want to know how to shrink that file (since nearly 100% of the file is not used) without taking down the server (and thus all our other systems).  If no one here can help me with this then I will probably just set sql server to use another drive, and set a hard limit on the filesize and hope nothing bad happens.
0
 
LVL 8

Expert Comment

by:vinurajr
Comment Utility
you are looking some thing like this: http://support.microsoft.com/kb/307487
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 50 total points
Comment Utility
>>So if we move it to a 500 GB drive then it will eventually use up that whole drive and we will have to shrink the file (and thus we are faced with the same problem).<<
IMHO you are asking the wrong question.  Your question should be why does tempdb keep growing?  There is something desperately wrong there.  There is no earthly reason that the tempdb should keep on increasing in a well balanced SQL Server.  It sounds like some very bad query or queries that are causing havoc.  I agree that if this is the case setting a hard limit is a good idea.  If nothing else you will very quickly find out the culprit or culprits that are the root cause of this problem.

0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>I know that I can solve this problem right now by restarting and that we can use a different drive for the data file (which we will do eventually).<<
In other words, this does not solve your problem, it merely postpones the inevitable.  Get yourself a tool like sp_whoisactive and start doing some serious monitoring.
0
 
LVL 15

Author Comment

by:dave4dl
Comment Utility
ACPerkins, I totally agree with you that the most ideal solution is to figure out why the tempdb data file is growing when it seems to have no need to but that sounded like a harder problem to track down so I was just trying to find the temporary fix with this question (shrinking the file down since it is almost all unused).

We have redgate monitoring tools (as well as some standard monitoring queries) and they all show that there is very little activity on the database while it is growing (just a few simple queries from our websites) in addition to the fact that it only 3 MB of the total 40 GB of that file are actually allocated.

If you (or anyone) can answer either of those questions (how to shrink without restart or how to prevent unnecessary growth), it would solve our immediate problem (and would let me to close this question).

vinurajr,

That is the same link damerval posted yesterday as the very first response to this question (which I responded to above).
0
 
LVL 9

Accepted Solution

by:
damerval earned 450 total points
Comment Utility
Dave,
Regarding options 2 and 3 on the link I provided which you and your team were already looking at, I disagree that they do not require a server restart within the confines of your situation. Indeed, they will not work unless the TempDB database is not currently in use, which means that in a production environment you need to restart the server in single user mode, apply the commands, and restart the server in normal mode.
So again, within the parameters you imposed on the question, I would say there is no way to shrink the tempDB database: it is always in use, and all the documentation I find tells me there is no way around it but to stop the server, start in single user mode, shrink the database, and restart the server again.
If you are using the enterprise version of SQL server, you can kick out all users and kill processes on each database without restarting the server, however in my book that is the same thing as restarting the server - same inconvenience, same outage.
Like acperkins, I think your one direction of investigation needs to be finding out why your log file keeps growing. TempDB stores three types of things:
- Explicitly created temporary tables, cursors, procedures and other db objects
- Intermediary result sets created during query execution, for sorting or other.
- Materialized static cursors, or row versions created by DML in various operations and circumstances
These last for as long as a any connection lasts on any database on the server.

It is complex to determine the maximum size your tempdb needs to be. Its size depends not upon the simplicity or complexity of the queries and stored procedures you run, or even on how many of them run per unit of time, but on the amount of data wielded by them. One thing you can do to help is to close connections from the business layer as soon as they are no longer needed. There are many other things to try, outlined in the article offered here:
http://msdn.microsoft.com/en-us/library/ms345368.aspx
On my setup here, each client opens their own individual connection which goes away once they close their browser or let their session time out. No connections are ever maintained for more than a couple of hours, and connections are closed explicitly (which I think is important). TempDB is stored on its own individual drive which is 2/3 the size of the data drive, based on the assumption that no operation will ever apply on more than about half the available data - all sorting, subquerying and other similar operations requiring TempDB use being carefully applied so that it happens after filtering either through where or having clause components or joins/foreign key constraints. Consequently, I find as I expect that the TempDB database is growing, but always as a fraction of the size of the databases on my server - about 35%, give or take. I monitor this ratio but it tends to stay the same, all other parameters being equal. If it should change, I have the other third of the database size to react and fix whatever problem it is on my business layer - timed out connections, excessive querying etc - that is causing the issue.

In conclusion, I would go with ACperkins and say that:
1) It is impossible to shrink the tempDB database without causing an outage of the production databases, either through server restart or through active connection termination
2) TempDB size should be managed from the business layer.

I hope this helps

Philippe


0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
Comment Utility
<<If you (or anyone) can answer either of those questions (how to shrink without restart or how to prevent unnecessary growth), it would solve our immediate problem (and would let me to close this question).>>
I am afraid you will have to restart the service to do what you are asking.  
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
Comment Utility
<<TempDB stores three types of things:>>
Add to that READ_COMITTED_SNAPSHOT version stores. (these hurt *bad*)
0
 
LVL 15

Author Comment

by:dave4dl
Comment Utility
Thank you so much for you help, I really appreciate it (and sorry it took me so long to close this question)
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL Backup skipping a few tables 7 24
MS SQL Backup 24 67
Help with SQL Query 23 38
Retention Policy for Backups 1 12
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now