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.