SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 TEMPDB is FULL - SQL 2008
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

poratips
Posting Yak Master

104 Posts

Posted - 03/28/2014 :  15:49:27  Show Profile  Reply with Quote
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

USA
36984 Posts

Posted - 03/28/2014 :  17:22:02  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36984 Posts

Posted - 03/28/2014 :  17:22:42  Show Profile  Visit tkizer's Homepage  Reply with Quote
And 5GB is tiny. We've got one instance that needs 300GB of tempdb data space. Depends on the system, queries and data.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

poratips
Posting Yak Master

104 Posts

Posted - 03/28/2014 :  17:46:42  Show Profile  Reply with Quote
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.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36984 Posts

Posted - 03/28/2014 :  18:12:18  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

poratips
Posting Yak Master

104 Posts

Posted - 03/29/2014 :  16:50:20  Show Profile  Reply with Quote
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.
Go to Top of Page

poratips
Posting Yak Master

104 Posts

Posted - 03/29/2014 :  18:53:33  Show Profile  Reply with Quote
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?
Go to Top of Page

poratips
Posting Yak Master

104 Posts

Posted - 03/31/2014 :  11:47:52  Show Profile  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36984 Posts

Posted - 03/31/2014 :  13:12:23  Show Profile  Visit tkizer's Homepage  Reply with Quote
poratips, the issue is with the initial size setting. You need to shrink it down to get the initial size lower.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

poratips
Posting Yak Master

104 Posts

Posted - 03/31/2014 :  16:16:30  Show Profile  Reply with Quote
Thanks Tara.
Can you suggest what I should set the initial size?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36984 Posts

Posted - 03/31/2014 :  17:14:26  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

poratips
Posting Yak Master

104 Posts

Posted - 03/31/2014 :  17:18:03  Show Profile  Reply with Quote
Do you think that if I change Initial size to 1 GB and Auto growth 100 MB should work?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36984 Posts

Posted - 03/31/2014 :  17:22:28  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

poratips
Posting Yak Master

104 Posts

Posted - 04/07/2014 :  20:26:35  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000