donovan Stoltz
asked on
Tempdb division
I have a SQL Server 2008 box that is randomly giving issues with the tempdb file.
At random occurrences the templog file fills up with different transactions executing but then at other times these transactions cause no fill-up.
The only way to bring the templog down when big growth occurs is to manually clear/ kill all open connections / sessions older than two hours that use our front end application that connects to a backend db within SQL Server 2008
tempdb config:
on one drive
tempdb.....mdf file
tempdb1....ndf file
tempdb2...ndf file
templog:
On another drive
templog....ldf file.
Any recommendations?
At random occurrences the templog file fills up with different transactions executing but then at other times these transactions cause no fill-up.
The only way to bring the templog down when big growth occurs is to manually clear/ kill all open connections / sessions older than two hours that use our front end application that connects to a backend db within SQL Server 2008
tempdb config:
on one drive
tempdb.....mdf file
tempdb1....ndf file
tempdb2...ndf file
templog:
On another drive
templog....ldf file.
Any recommendations?
ASKER
Thanks, Scott.
Unfortunately, the storage guys couldn't(probably wouldn't) allocate me an extra 50 Gig. This I think is a temporary solution in any case as it is probably something to do with the application not correctly closing the "old" sessions.
Will investigate further
Unfortunately, the storage guys couldn't(probably wouldn't) allocate me an extra 50 Gig. This I think is a temporary solution in any case as it is probably something to do with the application not correctly closing the "old" sessions.
Will investigate further
Ouch. If you've got a busy SQL Server and don't have 50GB in easy reserve, you have a problem right there really.
In addition to what Scott said I'd recommend you to take a closer look at what those open transactions are actually doing. Check out sp_whoisactive* and it's various parameters, very useful sproc!
* this is not a standard SQL Server SP thus needs to get installed first, you won't regret doing that though :)
* this is not a standard SQL Server SP thus needs to get installed first, you won't regret doing that though :)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Then investigate the issue. Make sure the apps properly close their connections (although with connection pooling some allocations will still exist in SQL).
Note that you can shrink tempdb while SQL is up, although it's not generally preferred.