May 15, 2009 at 11:46 am
What is tempdb set at for a size? I wonder if you can set it lower and restart SQL.
May 15, 2009 at 11:52 am
use [tempdb]
go
select * from sys.database_files
select * from sys.master_files where database_id = 2
go
Produces two different "sizes" in pages
select * from sys.database_files
gives me "size" on disk. (free +used)
select * from sys.master_files where database_id = 2
gives me "size" used
May 17, 2009 at 9:56 pm
Unless you are experiencing disk space issues on the disk where tempdb resides, I'd leave it alone. It grew to whatever size it is for a reason. If you shrink it, it will just have to grow again if more space is needed later. This is an expensive operation and can impact your systems response times.
May 18, 2009 at 5:01 am
I agree with Lynn. Unless you're experiencing serious space issues, leave it alone. I've had this problem several times myself and usually it's a valid process. Only on rare occasions was it a runaway process that wouldn't release the connections.
If you are having disk or runaway process issues, try killing all the users on the instance (or on the database which is actually using the space). This may take a while as there's rollback which needs to be done. Then try the shrink again.
Another option is to add another data file on another disk, make it the default, and let TempDB finish what it's trying to do.
And, if all else fails, restarting the services really is the only way to go.
May 18, 2009 at 6:15 am
In my case it was a one time query that caused the tempdb files to grow so I did want to reclaim the 19GB that they were holding. I restarted SQL server and they cleared as expected.
There should be a way to do it with restarting.
June 7, 2009 at 10:18 am
I'm having a similar issue.. tempdb.mdf grew to a horrendous size while running a seldom used process. Now I can't get the tempdb.mdf to shrink back down, ever after restarting the SQL Server.
June 7, 2009 at 10:38 am
jlab (6/7/2009)
I'm having a similar issue.. tempdb.mdf grew to a horrendous size while running a seldom used process. Now I can't get the tempdb.mdf to shrink back down, ever after restarting the SQL Server.
Was the restart a clean shutdown of SQL Server, or did the system have to recover the databases which may have caused your tempdb to grow again?
I'd also check the initial size of tempdb and see if it may have been changed.
June 8, 2009 at 4:31 am
And don't forget to check the size of Model too. TempDB is created off the settings on Model (all databases are) and if Model was changed, than all dbs will be created in the future with those settings.
Check your processes & run an sp_who2 just to be sure of what processes are active. Like Lynn said, it could be the database was still in the process of doing things when you restarted the services.
June 8, 2009 at 9:46 am
First.. I apologize. This is actually regarding SQL Server 2000, not 2005. But this thread was some of the best info I've found so far and everyone here seems to really know their stuff. If I should be posting elsewhere, please let me know.
The SQL server machine crashed after having a zero disk space error. After it was brought back up, the SQL Server was restarted cleanly and then the machine was rebooted again after some disk space was cleared up. I'm not positive how to check the initial size of tempdb (I've been combing websites for information). I did run sp_spaceused with these results:
This is tempdb:
tempdb database_size=136040.94 MB unallocated space=136031.98 MB
Reserved = 792 KB Data=288 KB Index_size=416 KB Unused=88 KB
This is model:
model database_size=2.63 MB unallocated space=0.11 MB
Reserved = 528 KB Data=144 KB Index_size=280 KB Unused=104 KB
The tempdb has grown to 139,297,536 KB on disk. This is FAR larger than it's ever grown before and is a huge issue. It does not shrink back down after a SQL Server restart or even a full server reboot. :crazy:
June 8, 2009 at 10:07 am
Sounds like SQL Server went down hard initially. This means it has to recover the databases to a consistant state. have the database recoveries completed, or are they still running when you attempt to restart SQL Server?
June 8, 2009 at 10:21 am
Everything is up and running now. The only obvious issue is that tempdb won't shrink back down. But otherwise, all operations appear to be normal. This happened early Saturday morning (2 days ago) and users have been using the database since it was brought back up and have had no problems.
June 8, 2009 at 12:26 pm
My thought on this issue is that the server still thinks something's wrong.
Announce to your users that you'll be taking the server down for maintenance on X date at Y time. Use any current maint. windows you have for this. Backup up all your databases (include Master, Model & MSDB in this, but NOT tempDB). Save the backups someplace other than the server in question.
Once you're sure your backups are safe, take the server down gracefully. Use all the proper protocols. Bring it back up. Check TempDB to verify the size has returned to a normal size.
If all else fails, and this is a LAST DITCH EFFORT suggestion, you can take the server down again (gracefully and properly) and delete the TempDB data file & log file before bringing the server backup.
I don't usually advocate deleting the TempDB except as a last resort. SQL Server re-creates this particular database each time the services restart anyway, so deleting is usually unnecessary. And if processes were interupted improperly, you could be screwed by even doing this. However, usually the ACID test prevents anything from being "caught" in the TempDB when services are stopped.
But again, and I can't stress this enough, don't delete the TempDB unless you have truly tried every other option at your disposal. Including powering down the server first for a few minutes if you have to.
June 8, 2009 at 12:35 pm
Thank you both for the advice. I truly, truly appreciate it. I'm keeping an eye on disk space and I think we will be okay until the next normally scheduled maintenance. Then I'll run through everything again before even considering manually deleting the tempdb.
I'll keep checking back here in case there is any more advice, and I'll post back with my results.
Thanks again!!!
June 8, 2009 at 12:45 pm
I haven't read each and every post, but have you tried issuing the alter to change the default size with a move as well? I had this same problem over the weekend and that was my resolution.
June 8, 2009 at 1:03 pm
I haven't tried changing the default size yet but that's one of the things I'm going to try. When I run sp_spaceused is shows a huge size for tempdb, but it looks like it's mostly just empty. Hopefully that will take care of it.. my fingers are crossed. We definitely do not need a 130GB+ tempdb! 😉
Viewing 15 posts - 16 through 30 (of 66 total)
You must be logged in to reply to this topic. Login to reply