Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rsn1966
Starting Member
9 Posts |
 Posted - 2010-09-24 : 15:10:57
|
| I am running SQL Server 2008 Enterprise Edition (64-bit). I noticed that my hard disk space is getting low so I checked the size of the databases on my server. The largest database that I have is about 70 Gb. When I checked the system databases, I noticed that tempdb is 360 Gb. I attempted to shrink the size of tempdb using Microsoft SQL Server Management Studio but it didn't work. Can anyone recommend a method to shrink the file size of tempdb.mdf? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-09-24 : 15:15:11
|
| You should never shrink tempdb as it could cause data corruption. To resize tempdb, restart your SQL Server service to set it back down to its initial size. Make sure the initial size is appropriate though so that it does not need to grow back out.The fact that you've got a tempdb of that size indicates a problem. Sizing tempdb is a complex topic, but this will get you started: http://msdn.microsoft.com/en-us/library/ms345368.aspxDo you have a maintenance plan in place to rebuild indexes? If so, then that's likely a problem as the maintenance plan does not do a good job of it. It doesn't explain 360GB though if your largest database is 70GB. Do you have inefficient code? Do you make heavy use of temp tables?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
rsn1966
Starting Member
9 Posts |
Posted - 2010-09-24 : 15:28:32
|
| I did have a maintenance plan which rebuilds indexes. I will remove that maintenance plan. As for usage, the database has several large tables. The database users view the data through custom views that have been setup. All data tables are read only and will not allow users to write to any table. I will try to restart SQL Server to see if that works. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
rsn1966
Starting Member
9 Posts |
Posted - 2010-09-24 : 15:49:28
|
| After restarting SQL Server, the file size of Tempdb.mdf has not gone down. As of a few weeks ago, Tempdb.mdf was not as large as it is today. As for queries, many of the users run queries using the custom views that I have setup. Is it possible that the custom views that could be the issue that is causing the growth of the Tempdb.mdf? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-25 : 03:39:52
|
| We see a number of questions here where a restart of SQL Service does not set TEMPDB back to it configure initial size. I don't know what the cause is.You could manually start SQL Service in SINGLE USER MODE, and then shrink TEMPDB. Be very careful to follow the instructions on Microsoft website carefully. There must be no other activity that is accessing other databases when TEMPDB is shrunk - otherwise corruption of those databases is possible.Question: Can one just rename the TEMPDB files when SQL Service is stopped so that SQL Server creates new ones on start up? (and if not just rename the files back again) |
|
|
|
|
|
|
|
|
Shinking Tempdb