September 5, 2008 at 8:50 am
May I have some help with an error, please?
This error was found in the logs, both SQL and application events:
"Error: 9002, Severity: 17, State: 6
The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space."
templog.ldf shows .59mb used, .4mb free
Following some other posts in this forum I checked free disk space and there is 24GB free.
I get 2-6 occurances in a few minute's span, every 2-8 days apart. No jobs or applications are failing that can be tied to these events.
The user databases have light, but steady activity. I don't know of any jobs that are allocating big temp tables.
Can I have some advice on what I should be looking for to find the cause of tempdb's log full message?
Thanks much,
Ken
No good deed goes unpunished.
September 5, 2008 at 8:55 am
Any maintenance jobs running (index rebuilds, checkdb)? Large adhoc or runaway queries? Is the tempdb set to autogrow? The log file seems awful small for tempdb (.59 mb??) with 24Gb free space. tempdb is the workhorse for SQL so I'd increase the size. This will eliminate the warnings and likely improve performance. If increasing the size doesn't resolve the issue or you cannot find any of the other causes I've listed, try using profiler to trap what exactly is running.
-- You can't be late until you show up.
September 5, 2008 at 9:32 am
Thanks Terry,
The log is set to auto grow by 10%.
All the maintenance plans ran 12 hours earlier than the last occurence.
I've reviewed all the scheduled jobs, none explicitly create any temp tables, but I guess SQL could be creating temp tables to complete query execution plans, right?
I've checked the security log for connected users that might know enough to write ad-hoc queries, but no one looked suspicious there.
I guess I'll bite the bullet and take your advice and fire up profiler to see if I can trap anything. Any suggestions for objects or events to filter on?
Thanks,
Ken
No good deed goes unpunished.
September 5, 2008 at 4:10 pm
Don't forget to track Data File Auto Grow and Log File Auto Grow Events (especially for tempdb database) under Database Event Class.
MJ
September 6, 2008 at 12:36 pm
what version of SQL are you on? I see this error occasionally but can never find any evidence there was a problem or the drive was out of space. I suspect a bug.
if tempdb log did fill up Sql server would grind to a halt for most processes and someone would complain, but no-one ever does.
---------------------------------------------------------------------
September 6, 2008 at 8:09 pm
Look for something open in tempdb as well. The database should be in simple mode, so it should clear the log at each checkpoint.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 7, 2008 at 3:20 am
If you have heavy disk activity, it may be your extending tempdb-process that gets a timeout, resulting in the errormessage you've seen.
As already stated, tempdb is one of the working horses of your sqlserver instance.
Don't leave it small, best is to monitor it and configuring it of reasonable size .
I prefer to have all my databases grow in MB not in percentages, because you can imagine autogrowing 10% of a 10MB database, growing to 1GB, how many extends will it need .... and you'll see it allocates larger and larger extends every time. (making it unpredictable to if your disk will be able to cope with e.g. a 100MB extend within the timeout periode)
Indeed, many small extends give you physical file fragmentation, so that may need some attention too.
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
September 7, 2008 at 10:40 am
The default settings for TempDB are to start at 1MB with 10% growth. If you do the math, it'll take 73 growths to get to 1GB... 73 growths also potentially means 73 fragments both in the database and on the operating system drives.
You know that TempDB is going to grow and that when it does grow, it basically paralizes the system during the growth. My recommendation would be to let it grow for a month (especially to catch month end runs) to see what size it actually needs to be, set it to start at that amount +20% with autogrowth set to 250MB on the MDF and 100MB on the ldf. Then, bounce the service so that TempDB will start out as one-nice-big-properly-sized-fragment-free-not-likely-to-grow-when-least-expected database.
Database growth should never take you by surprise... it should be a planned event.
--Jeff Moden
September 7, 2008 at 10:49 am
p.s.
We have some fairly large systems... in my last job, we booted TempDB at 9GB and in my current job, we boot it at 6GB. TempDB is an important resource for all code even if you don't explicity use temp tables or table variables. Don't make your server gasp for air... make sure it has enough TempDB to not have to grow during normal usage or month end crunches. I think it's so important that I will usually give it it's own physical drive or spindle (even on small systems) and I'll, many times, do the split balanced file thing based on the number of CPU's.
--Jeff Moden
September 8, 2008 at 8:21 am
Thanks, everybody! I just learned more than in the last 3 classes I took!
We're at SQL version 2000, installed with an industrial SCADA package called Wonderware. No one complains and no jobs are failing (yet); so far only errors in the log.
In the bliss of our ignorance, everything was set up with defaults. We do occasionally have to defrag drives, so our sysadmin insisted on installing Diskeeper against warnings not too. I'm going to act on all this advice on setting and monitoring tempdb file sizes. Wonderware constantly saves process data from hundreds of tags to flat history files, the DBs are hit lightly but when it does it always competes with WonderWare for I/O.
We have 16 servers configured more or less the same, so far there is only one showing these symptoms. But, they don't really allow me much time to monitor logs and such: it's not value-added work:crazy:
I'm a newbie DBA, but I'm learning.
Thanks all,
Ken
No good deed goes unpunished.
September 8, 2008 at 7:01 pm
I haven't used DiskKeeper in 10 years, but the folks at work swear by it. Your SysAdmin probably did a great job insisting that it be installed.
--Jeff Moden
September 9, 2008 at 1:18 am
Ken Stelter (9/8/2008)
... But, they don't really allow me much time to monitor logs and such: it's not value-added work:crazy: ...
Oh yes, SCADA stuff, not that much sqlserver performance needed, but they cannot waste a second, hmm :hehe:
We also use sqlserver for data store at the production line locally ("mirrored avant la lettre"), every hour, data is being pulled over by a consolidation server, which is being queried for history data.
IFix is the software they use to read out the PLC data.
btw: tell them they will lose data if your sqlserver generates this kind of errors ! because transactions will be broken. (and determining a decent unit of work (UOW) many times is not being taken into account by "industrial" teams)
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
September 9, 2008 at 3:20 am
we use diskkeeper too with no problems
---------------------------------------------------------------------
September 9, 2008 at 2:27 pm
My tempdb database property shows size as 16.6 GB and space available 99%. when I look at shrinkfile window datafile show 16.6 Gb size but in shrinkfile size to option I find size as -4.
No matter how, I tried all options to shrink this file, it won't and size remains the same. I ran a profiler also to see if anything is running on tempdb but nothing. Is there a way to force SQL server to shrink file without restarting MSSQLSERVICE. I know starting services will recreate tempdb form 2 mb.
Any suggestions?
SQL DBA.
September 10, 2008 at 3:03 am
SanjayAttray (9/9/2008)
My tempdb database property shows size as 16.6 GB and space available 99%. when I look at shrinkfile window datafile show 16.6 Gb size but in shrinkfile size to option I find size as -4.No matter how, I tried all options to shrink this file, it won't and size remains the same. I ran a profiler also to see if anything is running on tempdb but nothing. Is there a way to force SQL server to shrink file without restarting MSSQLSERVICE. I know starting services will recreate tempdb form 2 mb.
Any suggestions?
first of all, don't shrink it unless you have to because space needed on the drive by something else. Tempdb has grown to this size because it needed to at some point. so unless there was a one off or runaway job that caused this, this is the size (roughly) you should set tempdb to (as discussed by others above).
At a quite time you can shrink the file via query analyzer:
dbcc shrinkfile(tempdev,size in MB)
shrink it to slightly smaller than you really want then use alter database command to set final size, and it will be this size on reboot.
---------------------------------------------------------------------
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply