November 18, 2008 at 1:22 pm
I "was" having a very interesting issue with the tempdb.
The issue is that I have two data files for the tempdb in the primary filegroup, one of which has about 10gig of empty space and the other is close to full.
The first, on C:, was nearly full, while the second, on E: was virtually unused.
About two or three weeks after adding this second tempdb file, I starting getting the error message below (the std disk space is full).
Now, the fix was to stop and restart the mssqlsvr service. Which was a big deal as this db is a 24/7 externally facing system. (a side note: the db's are mirrored, but the developed code was not written to gracefully fail over, so no luck there - you can only get the developers to do so much.)
Has anyone had these issues, found a solution, or found the reason for this error?
The message:
DATE/TIME:11/18/2008 11:37:09 AM
DESCRIPTION:Could not allocate a new page for database 'tempdb' because of insufficient disk space in filegroup 'PRIMARY'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
COMMENT:(None)
JOB RUN:(None)
Now, while I have the short term fix (cycle service) I'd like to never have to see that again.
The more you are prepared, the less you need it.
November 18, 2008 at 3:00 pm
You must monitor tempdb growth. When all working tables are disposed and at off hours you can use ALTER DATABASE to get tempdb back to a normal size. You should try to find what is causing the large increase though.
* Noel
November 18, 2008 at 5:58 pm
Thanks, but just for the record, the load on the tempdb comes from snapshot isolation and nasty cursors used by the Java developers. It's a long road, but working on them to ditch the cursors, but that is another story.
Overall the tempdb use is under control, and it does spike so no real need to make is smaller, just so it can grow. I intentionally made the second tempdb file large so I would not have to have the engine expand it while under load. [disk space is cheap, expanding is expensive]
What I really want to know is if anyone has ever had this problem that I just had. Why did the engine refuse to use the second file all of a sudden. And I hate to have to cycle the service just so it will work.
The more you are prepared, the less you need it.
November 18, 2008 at 10:07 pm
November 20, 2008 at 7:44 am
I have seen this issue here a few times. Odd thing is the Drive has tons of space, TEMP DB is set to autogrow but does not grow to fill the drive so I don't understand it either. In researching it appears that this problem is a black hole and the only thing I have found is this:
backup transaction tempdb with truncate_only
go
checkpoint
go
Quite honestly it makes no sense to me though.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply