Tempdb division

donovan Stoltz
donovan Stoltz used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
First, Increase the size of tempdb so that it doesn't need to grow dynamically.  If it needs 50 more GB, just give it 50 more GB ahead of time.  Trying to avoid the growth will take far more effort than just giving it additional space.

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.

Author

Commented:
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
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
Ouch.  If you've got a busy SQL Server and don't have 50GB in easy reserve, you have a problem right there really.
ValentinoVBI Consultant
Most Valuable Expert 2011

Commented:
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 :)
I will have to close this question as the developers are creating a complete new .vdi of application environment and testing this in the foreseeable future. Since I have posted the question there have been no spikes in the templog.

Thanks for the additional info though!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial