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?
donovan StoltzAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
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.
donovan StoltzAuthor 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 DBACommented:
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 ConsultantCommented:
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 :)
donovan StoltzAuthor Commented:
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!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.