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 |
|
influent
Constraint Violating Yak Guru
367 Posts |
 Posted - 2012-01-26 : 15:55:20
|
| I have a drive used only for the TempDB (log file and data files). All the files have auto-growth disabled. A couple times per month SCOM tells me that the "DB Total Free Space (%)" has dropped below 20%. Does this mean the data files or log files? I find it confusing because isn't the TempDB constantly taking in new bits (data) and clearing it out once the query is done? According to SCOM the free space is at 100% for nearly the entire day, every day. Does this mean very few queries need TempDB, and only a couple times per month does a query need most of it? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2012-01-26 : 16:06:06
|
| Well you've likely got a large report/query that needs a huge amount of space in tempdb. I can't tell you what file SCOM is referring to because it could be either.I'm sure you have plenty of queries needing tempdb, it's just that they are small.Do you have monthly or weekly large queries that run that could be causing this? How about your index rebuilds? Sort in tempdb option?I really don't like auto-growth being disabled. I hate it actually. We let them autogrow and have alerts (pages when it's critical) fire when free space at the disk level is low. This prevents an outage when a database has filled up.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2012-01-26 : 16:17:52
|
| Thanks as usual Tara. The server is for Great Plains, and it always seems to be the same process (historical aging or something) that causes the free space percentage to majorly drop. I don't have any index maintenance set up. |
|
|
|
|
|
|
tempdb free space alerts