• 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