Tdb log filling up
-
We have a poorly written application on a server here. it ran fine,until we upgraded to sql server 2005, and now, our nightly refresh jobs fail due to temp db being full all the time. i cant go rewriting the database code, as much as i would like to, so im wondering, out from giving more and more space to the tempdb, is there anything else i can do?
-
I will assume your TempDB is on the C drive under the SQL directory and it is filling up your drive space which is causing problems.
So, go ahead and move the TempDB to another drive with more space. Use script below, make sure to use the correct drive letter:
USE
master
go
ALTER
DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'E:\tempdb.mdf')
go
ALTER
DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'E:\templog.ldf')
go
-
After doing the above, restart SQL and then delete the original tempdb data and log files, as they will still exist in their original location.
-
no, that was the first thing i checked. we have spread the tempdb out over about 3 drives, and given it 6 files, each 2 gb in size. i could allocate more, but a tempdb shouldnt need anymore than 12gb i thought,and even that is overkill. it was never located on the drive with the OS and SQL installation.
-
Tempdb is used during query processing for sorts, joins, unions, etc. If you have enough poorly written code and a number of users, it is possible that tempdb is getting well used. If you have the size of the tempdb files fixed, unable to grow, this could be the issue. Also, if it is fixed, try changing it to autogrow, but fix the size it grow each time, don't allow it to grow by a percentage. I'd set it to grow 100 MB at a time since you have the foles currently set at 2 GB each.
-
Personally I would be doing whatever is necessary to get what I assume is very important off-hours jobs to complete successfully. So stop fooling around and simply make tempdb as big as possible on the given drives you have placed files on! If your night jobs are successful then you can start reducing the size down if necessary. If not, you have much bigger problems it would seem. 🙂
Have you checked to see how large they DO autogrow to if you go that route first? I would recommend setting the maximum on each file so that you never fill up a drive, and I would also pick a larger increment that 100MB to avoid fragmentation. You could do this and watch how big it gets and then fix the size at something larger than what was successful.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply