Temp db auto growth propblem

Question

Temp db auto growth propblem

pat-261672

Hall of Fame

Points: 3325
More actions
February 22, 2010 at 1:13 pm

#215997

Hi,
I am keep getting message in the alert log as followeD:
Autogrow of file 'templog' in database 'tempdb' took 73500 milliseconds. Consider using ALTER DATABASE to set a smaller FILEGROWTH for this file.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Sometimes it gives me error on event viewer as followed:
The log for database 'tempdb' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
I have set the db to auto grow for 2074 MB and unrestricted growth.
So why i am geeting this type of problems!!
I have sql server 2--5 with SP2.
Thanks
Pat

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

d33m SSChasing Mays Points: 620 More actions · Answer 1

Do you have autoshrink on the tempdb log file or are you shrinking the log file on a scheduled or manual basis? How much free space is located in the tempdb log file? How often do you see the autogrow show up in the SQL Log for this database?

pat-261672 Hall of Fame Points: 3325 More actions · Answer 2

Hi I have schedule job to do shrink log and db which runs every 2 min. also the message for auto grow shows up every 3 to 4 mins a part. I have about 40 G free on the drive.

Thanks

Pat

d33m SSChasing Mays Points: 620 More actions · Answer 3

Each time the tempdb, or any database for that matter, has to grow the server takes a performance hit. So if you are growing the tempdb out 2 GB which is giving tempdb more free space then shrinking it just 2 minutes later you are going to not only see the message in your error log, but also see performance degradation on your sever.

If you have some way of monitoring the free space on the drive where you have tempdb located I would suggest leaving sufficient space in tempdb and only shrinking when you need some more room on the disk drive. Let's say you currently have your tempdb sized at 10 GB, but you are running queries on your database which make it grow to 20 GB, it has to grow to 20 GB in 2 GB interval, each time causing a performance hit. Filling up to 12 GB then to 14 GB and so on. Then you shrink it back down to 10 GB only to repeat the process all day long. But if you let it grow without shrinking it, it may grow out to 20 GB and stay there. When the queries finish it doesn't shrink back down to 10 GB, but stays at 20 GB with lots of free space in the file. So the next time it needs more room it doesn't have to grow unless it needs to grow past the 20 GB mark. Make sense?

In an optimal configuration you have tempdb located on a separate drive from anything else.

d33m SSChasing Mays Points: 620 More actions · Answer 4

One more thing. Each time you either restart the server or restart the instance, tempdb gets recreated. Make sure you size your initial configuration accordingly because if you size it small initially, it will have to grow back to the size it is normally whenever a server reboots or the instance is restarted.

pat-261672 Hall of Fame Points: 3325 More actions · Answer 5

Hi,

So the suggestin would be the make tempdb and log to lets say about 15 G and!!

if so how can i do that.. alter database command?

Please let me know.

thanks

Pat

d33m SSChasing Mays Points: 620 More actions · Answer 6

The first thing I would do is disable the job that is shrinking tempdb every two minutes. Just make sure you have some way of monitoring the disk space on the drive because tempdb can easily blow the drive out if you are not careful!!!

So, number one you can just do nothing and let it grow to the size it needs too. It will stay there until it needs more space since you have autogrow set to on and have an unrestricted file growth. Or you can run the alter database command and it will resize tempdb. Depending on how many data and log files you have for Tempdb you can run something like this:

alter database tempdb modify file (name = tempdev, size = 15360)

alter database tempdb modify file (name = templog, size = 2048)

You can find the names if you run:

select * from tempdb..sysfiles

I would suggest that you do not size the log out to 15 GB though, you probably can just grow out the data file and let the log at say 2 GB? Sizing them both to 15 GB may be overkill, plus if the data file needs to grow again you won't have any room left on the drive. I making some estimations since I do not know your system.