40gig templog - What to do
-
This thread is related to a problem I thought I was having with locks, but turned out not to be the case.
My real problem is something is writing to templog, as I can see it growing all last night, starting at about midnight and ending at about 8am with a failure of a few jobs I run every morning at 8am. Templog is full.
I've read some information about truncating the log, but the real problem is how do I find out what's causing the file to grow out of control?
-
- start a sqlprofiler trace to find out what's going on !
- to shrink tempdb, you'll have to stop/start sqlserver. It will cleanup the db and start with the original tempdb-size settings.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
In regards to shrinking the db, I did restart the service by stop then start. The growth happend after this point. What I've read is even if you do this stop start, but do not set a max file size or do some other file sizing commands, the tempdb and log will just grow back to what the previous size was.
-----
Please explain the process of starting a trace in more detail. I've never done this, and can't seem to find any more info than 'start a trace'.
Thanks. -
Ok I've got a trace running for starttime greater than 2007-08-28 00:00:00 less than 2007-08-28 09:00:00. That should show me what's going on with tembdb, correct?
-
Not neccessarily, make sure yo include DB and LOG growth. Remember TempDB is a scratch pad for large resultsets as they are bein joined or otherwise manipulated. Most times large growth like this is related to a query in a large database, so all your small db's (under a few hundred megs) most likely are not involved.
-
Yeah I often use temp tables when gathering data to update into a very large data table. The database I'm updating is only about 1g (but it's log file is 10g ...).
I tried to run BACKUP LOG tempdb WITH TRUNCATE_ONLY to cut the temb db log file, but after saying 'successful' the log file is still 40g. The actual size of tempdb is only 8 meg. I'm stumped.
First I'd like to cut down the log, so jobs will run, and then I can figure out the exact culprit.
-
More information: I just read that truncating the log file won't actually free up physical size only logical size. So I need to go in and run a shrinkfile on templog apparently. I'll have to do that in a few minutes, as I'm running some other reports at the moment.
-
I tried running a DBCC SHRINKFILE (templog, TRUNCATEONLY) and got these results:
DBID FILEID CURRENTSIZE MINSIZE USEDPAGES MINPAGES
2 2 546152 64 546152 64
So then I ran DBCC SHRINKFILE (templog, 2000) to try and shrink the file to approximately half its size. I got the same results as above. And:
Cannot shrink log file 2 (templog) because all logical log files are in use.
-
I usually do this
BACKUP LOG tempdb WITH TRUNCATE_ONLY
DBCC SHRINKFILE (templog,[value])
GO
which usually works.
-
Ok I just stopped and started the service again. Templog is now 2g. Still seems pretty large.
-
Yes that is pretty large but you know it can be a lot larger. I would give it maybe a month and see how big it is, if larger than 2 then consider if the file has to resize it takes cycles out of processing and 2 may be a god size or not, if the file isn't veen full then I would possibly consider loser to whatever it is. But you need to get feel for file growth before you assume anything is right.
-
Good advice. I'm going to set up a trace on file growth, and see what's going on. I stopped and started the service yesterday at approximately 4pm, so that would mean it would have had to grow from 2g to 40g overnight. That kind of growth just doesn't even seem possible, considering I've never had this problem before.
Running a trace right now to see if I can capture any growth.
-
Ok I found some log growth already. I also picked up on the user, and it's a coworker in Texas who is pushing data into my DB. I killed his spid when I saw the same ID growing the log file. I tried calling him, and he's not there.
So I need to kill his access to the DB if I don't here from him? Never had to go to such extremes, but his spid is growing log files like it's going out of style.
-
I know I'm coming in late to the party - but do you really need transaction logs on the tempDB? If it really is being used as it is - tempDB only incurs work based on stuff happening in other DB's, so why would you ever want to replay the trans logs in tempDB? and if you're not - why not turn off trans. logging (switch it to simply logging).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
Tempdb is already set to simple recovery, so everything in the templog is just waiting to commit...from what I've read. I'm no expert.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply