Autogrow of file 'templog' in database 'tempdb'

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Autogrow of file 'templog' in database 'tempdb'

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-12-11 : 08:59:08

I'm currently running the command below and getting this error message. I am getting it every few seconds for a couple hours now. Seems to have started when I started this SPROC below. I'm just over 2 hours into running.

Not sure what this means yet, any help is much appreciated. Desperately trying to resolve this urgent matter!

thanks once again!
mike123

"Autogrow of file 'templog' in database 'tempdb' was cancelled by user or timed out after 2813 milliseconds. Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size."


EXEC isp_ALTER_INDEX 
                            @dbName = 'db1', 
                            @statsMode = 'DETAILED', 
                            @defragType = 'REBUILD', 
                            @minFragPercent = 10,
                            @maxFragPercent = 100,
                            @minRowCount = 1000

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2007-12-11 : 09:16:02

I presume you have enought free space on your disk. also what is the parameter for the autogrow for tempdb.
I for once use the default files created on C drive as not to autogrow but stay stagnant. The files on other drives like D or E is what I set to autogrow infinitely.

Also try running the activity monitor to check what is going on on the system at the time.

Regards
Paresh Motiwala
Boston, USA

nr
SQLTeam MVY

12543 Posts

Posted - 2007-12-11 : 09:18:36

Is there any space on the disk for the tempdb log? has it reached it's max size? what is it's growth amount? how big is it?
It's presumably trying to create a new virtual log file and it's taking too long to grow.
If the log file is very big and set to 10% growth try setting it to 100MB or such.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-12-11 : 09:28:24

I actually just shrunk the tempDB yesterday, so its very small with the default settings.

I just rebooted the SQLserver and so far everything is ok. All errors have stopped occuring.. I can't explain what might have caused this but I will try to look into..

thanks for the support
mike123

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-12-11 : 11:32:16

don't shrink the tempdb if you want to avoid corruption issues.. it dynamically goes back to it's minimum size without you having to shrink it

anyways... you need to move tempdb files to a larger drive (if your system drive is too small to accomodate the growth)... remember that tempdb is shared with all your databases and it needs to grow whenever needed

I've handled large OLTPs and 50 GB usually suffices... If you're expecting large batches that will fill it up pretty fast, add more disk space

You need to monitor the growth in any case to determine how much you really need... visit my blog here at sqlteam... there's a simple setup for monitoring db sizes

--------------------
keeping it simple...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources