September 12, 2010 at 9:34 am
Hi,
I have a SQL Server 2005, whose tempdb grows to 19.6G (which is very large in my case). I tried to shrink it, but failed. Although no one is using this database at that time, I still can't do it. I've met this case on other database: the database can't be shrinked but no one is using it.. Can anybody tell me why?
Since the shrinking dosen't work, I restarted the instance to solve the problem. However, after that, I found the tempdb is still 19.6G. And reading from the errorlog, I found that the tempdb is been restored automatically!! Luckily, this time I can shrink the datebase. I believe the tempdb is been deleted and then created each time the instance is been restarted. So I'm wondering about it.
September 12, 2010 at 10:54 am
I have a SQL Server 2005, whose tempdb grows to 19.6G (which is very large in my case). I tried to shrink it, but failed. Although no one is using this database at that time, I still can't do it. I've met this case on other database: the database can't be shrinked but no one is using it.. Can anybody tell me why?
If there is an open transaction or a lock held on the database you cannot shrink it. I had discussed about it here[/url]
Since the shrinking dosen't work, I restarted the instance to solve the problem. However, after that, I found the tempdb is still 19.6G. And reading from the errorlog, I found that the tempdb is been restored automatically!! Luckily, this time I can shrink the datebase. I believe the tempdb is been deleted and then created each time the instance is been restarted. So I'm wondering about it.
Tempdb is recreated every time the SQL Server service is restarted. Not sure which entry in the error log indicated that it got restored?
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
September 12, 2010 at 9:09 pm
Adiga (9/12/2010)
I have a SQL Server 2005, whose tempdb grows to 19.6G (which is very large in my case). I tried to shrink it, but failed. Although no one is using this database at that time, I still can't do it. I've met this case on other database: the database can't be shrinked but no one is using it.. Can anybody tell me why?
If there is an open transaction or a lock held on the database you cannot shrink it. I had discussed about it here[/url]
Oh, I used to think that if no process (seen from sp_who) is on the database, the database is free.
Since the shrinking dosen't work, I restarted the instance to solve the problem. However, after that, I found the tempdb is still 19.6G. And reading from the errorlog, I found that the tempdb is been restored automatically!! Luckily, this time I can shrink the datebase. I believe the tempdb is been deleted and then created each time the instance is been restarted. So I'm wondering about it.
Tempdb is recreated every time the SQL Server service is restarted. Not sure which entry in the error log indicated that it got restored?
The log is shown as below:
2010-09-10 13:18:16.55 spid5 Clearing tempdb database.
...
2010-09-10 13:18:20.44 server SQL server listening on TCP, Shared Memory, Named Pipes, Rpc.
2010-09-10 13:18:20.44 server SQL Server is ready for client connections
2010-09-10 13:18:29.01 spid5 Starting up database 'tempdb'.0
2010-09-10 13:18:29.21 spid3 Recovery complete.
Seems that after all database is started, the tempdb is recovery.
September 12, 2010 at 10:24 pm
2010-09-10 13:18:29.01 spid5 Starting up database 'tempdb'. 0
2010-09-10 13:18:29.21 spid3 Recovery complete.
The first line states that the tempdb is starting.
The second line states that the Recovery is Complete. When SQL Server starts up, it tries to recover all the databases. This message means that SQL Server has completed recovering all the databases. It is not specific to tempdb.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
September 13, 2010 at 2:02 am
Great! Got it! Thank you very much for your help:-D
September 13, 2010 at 3:23 am
The first line states that the tempdb is starting.
The second line states that the Recovery is Complete. When SQL Server starts up, it tries to recover all the databases. This message means that SQL Server has completed recovering all the databases. It is not specific to tempdb.
I'm not sure whether tempdb will be recovery or not.
If not, since the tempdb is recreated, why is its size the same as the size before restarted?
September 13, 2010 at 3:42 am
Hi
I have come across a scenario where the temp db after restore or recovery defaults the file size of the mdf and ldf to very large values.
Also you could check if the filegroeth size is set to a very large percentage.
Ideally when u restart the sql server service , it is supposed to clear the tempdb and the default size to return to a few MB.
Also you shud try to find out the temp db usage by running a perfmon to see if the tempdb is growing dynamically or if the size is set by default.
September 14, 2010 at 8:45 am
I've had a similar issue where in SQL 2000 I could shrink the tempdb with no other action needed. However, after going to SQL 2005 I have had to do a couple more steps: Only do the following steps during scheduled maintenance time and when it is possible for you to do a db server reboot if necessary. First, I do a shrink db to tempdb. Then, I verify the data content has gone down although the file size remains high. I then exit out of SQL Enterprise and stop the SQL services. I then go into the DOS prompt on the DB server and enter sqlservr -m (after going to the C:\Program Files\Microsoft SQL Server\MSSQL\BINN or similar location for the BINN folder) this command will log you into SQL in single user mode - do not close the DOS screen!! When in single user mode you can set the tempdb mdf and ldf file sizes back to a smaller size. When done then go back to the DOS prompt and type Control-C to properly exit single user mode and go ahead and tell it to shut down the SQL server. You will need to restart the SQL services you stopped but should be done at this point.
September 15, 2010 at 12:36 pm
Have you found root cause of why the tempdb is so large in the first place? If you don't fix the problem, it's just going to grow back that size. Shrinking it will only cause significant performance issues as it extends.
Tempdb is used for a couple of things... sorts that don't fit in memory, and temporary objects (ie. temp tables)
A couple things to look at, if you haven't already:
1) SQL Tuning...
a. Look for nested-loop joins and SORT operators in you plans.
b. Look for the use of temporary objects. This is a common and acceptable practice. If your application(s) make lots of use of them, your growth might just be "nature of the beast".
2) Index tuning...
a. Organizing/re-ordering/creation of indexes causes sorts, and often on disk. Look for indexes that aren't being used. A pleasant side-effect will also be improved DML performance 🙂
b. Also consider a periodic maintenance plan that re-organizes indexes and updates statistics.
Also, remember, tempdb is instance level, not database level... so SQL and Indexes in ALL your databases in that instance need to be evaluated.
Once you've ruled out the causes, then consider shrinking tempdb. You can monitor page usage within the file to see what the new high-water marks are, and if there is any improvement. The "delete and restart" trick will provide you with an empty tempdb to start your testing and analysis with. Once you know the usage after any tuning, then I recommend shrinking to that size plus 10%, if you shrink at all.
Just blindly shrinking is never a good approach. It's that big for a reason, and will always grow to that size again no matter how many times you shrink it.
Good luck! Let me know if you need any help with the tuning.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply