SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Problems with large databases


Problems with large databases

Author
Message
Sephiroth_Jin
Sephiroth_Jin
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 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.
Adiga
Adiga
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2656 Visits: 21012
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
Sephiroth_Jin
Sephiroth_Jin
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 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.
Adiga
Adiga
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2656 Visits: 21012
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
Sephiroth_Jin
Sephiroth_Jin
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 108
Great! Got it! Thank you very much for your help:-D
Sephiroth_Jin
Sephiroth_Jin
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 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?
JAYANTH KURUP
JAYANTH KURUP
SSC-Enthusiastic
SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)

Group: General Forum Members
Points: 141 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.
tom.lemmer
tom.lemmer
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 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.
jpenniman
jpenniman
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 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 Smile
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search