| Author |
Topic |
|
poratips
Posting Yak Master
105 Posts |
 Posted - 2014-03-28 : 15:49:27
|
| Hello,We have Sql 2008 and TEMPDB is FULL, I have restarted the server but it's still showing full.Could you please explain me the correct steps to follow?I have TEMP DB files (.mdf and .log) on separate drive and one of the .mdf files is shows on server as almost full means I have 5 GB and it shows 4792 MB.Thanks and Appreciate it! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-03-28 : 17:22:02
|
| What do you mean it's full? Do you mean the file size is 4792MB or there's 4792MB in use INSIDE the mdf file? There's a difference. Show us the file properties of tempdb (in SSMS, need to see it's starting size).Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-03-28 : 17:22:42
|
| And 5GB is tiny. We've got one instance that needs 300GB of tempdb data space. Depends on the system, queries and data.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
poratips
Posting Yak Master
105 Posts |
Posted - 2014-03-28 : 17:46:42
|
| Thanks Tara.I mean 4792 MB means it shows on drive space when you check that on server drive properties and also on G:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data - size.From SSMS - 4,793 MB is initial size and auto growth By 10 percent, unrestricted growth. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-03-28 : 18:12:18
|
| The initial size is the culprit. That's the size it'll start out as when you restart SQL. Shrink it down and restart.I am very against the 10% autogrowth. I typically will use 1024 or 512MB for data files and 256 or 512MB for log files, depends on the size expectation though. 10% can be very bad as it grows.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
poratips
Posting Yak Master
105 Posts |
Posted - 2014-03-29 : 16:50:20
|
| Thanks Tara.I am agreed with you sI have set up the same way for my previous client, Initial size I have posted that was after restart. |
|
|
|
poratips
Posting Yak Master
105 Posts |
Posted - 2014-03-29 : 18:53:33
|
| When I was looking on server drive after restarting, it is showing on folder - Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data is 4.97 GB and when I right click on drive it shows out of 5 GB, only 270 MB free.Is it something I need to shrink or restart the server again? |
|
|
|
poratips
Posting Yak Master
105 Posts |
Posted - 2014-03-31 : 11:47:52
|
| I have concern as I am almost close to full capacity and somehow it's not still clearing the space and I couldn't see any open active transaction.Could you please guide any other steps I should follow or I can try again any step by steps?Thanks |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-03-31 : 13:12:23
|
| poratips, the issue is with the initial size setting. You need to shrink it down to get the initial size lower.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
poratips
Posting Yak Master
105 Posts |
Posted - 2014-03-31 : 16:16:30
|
| Thanks Tara.Can you suggest what I should set the initial size? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-03-31 : 17:14:26
|
| I can't as it depends on your environment. Most of my servers have 8 tempdb data files that are each set to 1GB, so our initial size is 8GB and the mount point has another 60GB of free space so that it can grow.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
poratips
Posting Yak Master
105 Posts |
Posted - 2014-03-31 : 17:18:03
|
| Do you think that if I change Initial size to 1 GB and Auto growth 100 MB should work? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-03-31 : 17:22:28
|
| Well it'll fix the disk space issue, but I can't say whether or not it'll need to grow out to the bigger size.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
poratips
Posting Yak Master
105 Posts |
Posted - 2014-04-07 : 20:26:35
|
| Thanks Tara. I have also created 4 TEMP DB files (based on CPU) for my another client during initial set up and make it 1 GB on different drive. |
|
|
|
|
TEMPDB is FULL - SQL 2008