TempDB Error
-
I constantly am receiving the following error message:
Error: 9002, Severity: 17, State: 2
The log file for database tempdb ls is full.
When I am running a stored procedure that I use.
Now obviously this would mean I should allow tempdb to grow larger than I do already, YET the tempdb is already 640 MB (already changed it awhile back to give it a large starting point) and is set for 10% unrestricted growth on the drive.
This drive also still has 71Gigabytes free, so obviously I'm not running out of physical space.
The thing that is happening is a large update query inside of a stored procedure and I believe something is causing it to time-out the process and report back what would basically be a false failure.
Tempdb as it stands is not set to auto-shrink either.
The tables involved in the update are Cold2 (90Million Records clustered index on Phone) and Import (however many records are selected with a clustered index on Phone) using an inner join on the 2 Phone fields.
Any ideas of where to look to solve this problem?
-
Which columns are being updated ?
Are any of the updated columns part of the clustered index ? Are they part of other indexes ?
-
I would also look at the execution plan on the stored procedure to see if a different index could be utilized. In general I size tempdb at 2gig and allow growth upto 4gig. I've never had any problems and you don't have to wait for the os to format space for tempdb.
Tom
-
You posted the error as : The log file for database tempdb ls is full.
That means, you should check whether the log file is on a disk where it has enough space to grow. It is not necessarily the same disk on which TEMPDB data file resides. Look into Properties/Transaction log of the database in EM to find out what the location is.
Our tempdb data file is 15GB and log file around 3GB... so your tempdb size is rather low compared to this and you shouldn't be afraid to increase it (or let it grow as it needs). On the other hand, I agree that you should check performance of and index usage in your procedure, as soon as you make it work.
HTH, Vladan
-
When you get an error that a file is full...that doesn't mean there's not enough room to grow. It means that at THAT MOMENT THE FILE RAN OUT OF SPACE. It might still be growing, but it didn't grow fast enough or large enough.
Let's say you set your database to autogrow by 1 MB. Then you do a DBCC REINDEX. That takes up lots of space FAST. So, your log files won't grow fast enough for the transaction. Or you insert a million rows, that's more than 1 MB of transactions, so the log will run out of space FOR THE TRANSACTION.
I never set a file to autogrow by percentage. Mine are always set for MB (my TEMPDB files are set to grow by 2000MB - yes that's 2 GB and I do need them set that high).
-SQLBill
-
also remember to schedule a job to shrink the DB...
Moe C -
Hello Moe,
did you mean it as a joke, or serious advice? Not sure, with that smiley... anyway, to avoid confusion, when I said let the tempdb grow, I meant let it grow and do not shrink it. Disk space shouldn't be an issue, so there is no reason why tempdb couldn't be 15GB all the time, without any shrinking, even if that size is necessary only for optimization job that runs 1-2 times per month and most of the time the file is mainly empty space. As Bill said, some events need lots of space quickly... it is easier if the file is big and does not need to expand suddenly.
-
lol... its a joke.. no one has a sense of humor in this place...
...
Moe C
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply