Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Problems with large databases Expand / Collapse
Author
Message
Posted Sunday, September 12, 2010 9:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 27, 2014 2:49 AM
Points: 9, Visits: 108
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.
Post #984393
Posted Sunday, September 12, 2010 10:54 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 7:55 AM
Points: 1,618, Visits: 20,978
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


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
Post #984409
Posted Sunday, September 12, 2010 9:09 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 27, 2014 2:49 AM
Points: 9, Visits: 108
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

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.
Post #984496
Posted Sunday, September 12, 2010 10:24 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 7:55 AM
Points: 1,618, Visits: 20,978
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
Post #984516
Posted Monday, September 13, 2010 2:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 27, 2014 2:49 AM
Points: 9, Visits: 108
Great! Got it! Thank you very much for your help
Post #984570
Posted Monday, September 13, 2010 3:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 27, 2014 2:49 AM
Points: 9, Visits: 108
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?
Post #984612
Posted Monday, September 13, 2010 3:42 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 15, 2011 12:19 AM
Points: 125, Visits: 58
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.
Post #984621
Posted Tuesday, September 14, 2010 8:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 5, 2014 5:16 PM
Points: 5, Visits: 39
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.
Post #985582
Posted Wednesday, September 15, 2010 12:36 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 7, 2012 12:02 PM
Points: 7, Visits: 31
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.
Post #986566
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse