backup tempdb

[ynnepztem]

I was trying to change an index and I received an error "The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space."

I tryed (novice) to backup the log by using
USE tempdb
GO
BACKUP log tempdb

but that resulted in the message "Backup and restore operations are not allowed on database tempdb."

What do I do?

 

[SQLBill]

Check to see if you allow the tempdb to grow and how large you allow it to grow. I see this error when a large query is being run (large meaning it returns a lot of data) or several large queries are being run. THe tempdb gets full and can't expand fast enough for the data so it returns that error. But it has always ended up expanding enough.

It's kinda like this:
tempdb is 5 MB
one or more queries are run and tempdb needs 7 MB of space
tempdb uses up the 5MB and returns the error that there's not enough space
tempdb expands by the amount you allow
tempdb uses 7MB of space and the queries finish

The hard part with tempdb is estimating how much it really will need to expand by....

if you set it to expand by 1 MB and you get a query that needs 6 MB, it expands no problem. But if the query needs 8 MB, tempdb expands once, doesn't have enough, sends error, expands again, etc. until it has enough space.

(At least that's what it looks like it's doing).

-SQLBill

 

[ynnepztem]

Thanks. It worked like a charm.