TempDB getting full often

[daglugub37]

Lately we have been running out of space for the tempdb log. I just do a quick fix truncate of the log but I can not identify one common transaction that is singularly causing the log file to take all remaining drive space.

I am wondering if maybe we have run into this scenario as a matter of growth.

I wonder does anyone know of a generic bench mark or standard on how much space should be reserved for the TempDB log per bytes of MDF on the server maybe?

I guess what I am thinking is if we have a server whose databases are about 1 TB, should we have a minimum amount of space for tempdb mdf and ldf.

Maybe there is too many factors for a useful benchmark, I just want to make sure there is not one that I should know about.

 

[mrdenny]

Basically you need enough space that your tempdb can hold all the data in the largest transaction that you are processing.

Also, make sure that no one put the tempdb into full recovery mode.

If you keep running out of space, you'll want to add more space. With a database size of 1TB you are probably using a SAN for storage, so adding space should be fairly easy.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Catadmin]

You might consider running Profiler during the time the TempDB grows. This will give you an idea of what processes/queries are causing the DB to "overgrow".

Also, if you have the space, consider moving TempDB to a separate physical or logical drive. Sometimes that helps.

Do you use UDFs or Procs or queries that use a lot of temp tables?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[daglugub37]

thanks, I think we will just put the ldf on a bigger logical drive, we have plenty of space but try not to waste it.

In terms of using temp tables, yes we have lots and lots of sps and udfs creating pound tables. Also we have tried to identify 1 or a few offenders, but it is always different times of day and different processes.

I don't think it would be iresponsible to just throw some more storage at this problem, thanks for your reinforcement

 

[markknowsley]

I have been having the same problem - we're using SQL Server 2005 on a server with 2 x 30GB drives. We're running a data matching program which keeps supersizing the tempdb on drive C - it got to 25GB this morning.

Someone here suggested splitting the tempdb across multiple drives - if I could split it onto drives C and D I would double my disk space. I've seen lots of stuff written on Microsoft websites and blogs that indicates this is possible, but couldn't find anything about actually how to make this work.

Can anyone talk me through it or point me to a tutorial (I know there's an answer on experts-exchange but my bosses won't fork out $9.95 per month.

Cheers,

Mark.

 

[Catadmin]

Use Books Online. You'll want to look up the topic Alter Database.

I believe you can create multiple files on different drives using the same filegroup for a database using the Alter Database command and then it will "stripe" (for lack of a better term) across those drives.

You can't use multiple filegroups this way because only one can be the primary, but multiple files in the same filegroup should work.

Let me know if it does or does not. I haven't actually used this technique before myself.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[mrdenny]

Multiple files in the PRIMARY file group is what needs to be done. This will stripe the data accross both drives as Caradmin said.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Catadmin]

Thanks for the correction, Denny. That's what I meant to say. My fingers just didn't want to type it correctly. @=)



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"