August 27, 2003 at 9:28 am
Hi there,
I get the follewing error on sqlserver 2000 the log file for database TempDB is full. Backup the transaction log. I wondered because i can do that. Can somebody help whit this problemen
Ronald
August 27, 2003 at 10:58 am
backup database tempdb with truncate_only
August 27, 2003 at 11:46 am
Allen_Cui provided you the simple way to clear the Tempdb log. But you really need to find out WHY it was full.
1. Did you run out of disk space?
2. Go to Enterprise Manager, drill down to the databases, right click on TEMPDB and select Properties. Go to Data Files tab and see if it is set to allow AUTO GROW. Do the same thing on the LOG tab. Both should be set to AUTO GROW. You will also need to figure out (guesstimate) how the data and log files should grow (percentage or set MB size). I choose to set the amount of MB it will grow.
3. Make sure you allow it to grow in large enough size so that it doesn't fail because it's waiting for more room.
-SQLBill
August 28, 2003 at 2:09 am
thanks for you answer
The settings with the server are correct except that i have the grow on 10 percent. So this i have now on 50 percent. The diskspace is fine. Another proleem we have an i think it is because of the tempdb that we can copy the backup file.
Ronald
quote:
Allen_Cui provided you the simple way to clear the Tempdb log. But you really need to find out WHY it was full.1. Did you run out of disk space?
2. Go to Enterprise Manager, drill down to the databases, right click on TEMPDB and select Properties. Go to Data Files tab and see if it is set to allow AUTO GROW. Do the same thing on the LOG tab. Both should be set to AUTO GROW. You will also need to figure out (guesstimate) how the data and log files should grow (percentage or set MB size). I choose to set the amount of MB it will grow.
3. Make sure you allow it to grow in large enough size so that it doesn't fail because it's waiting for more room.
-SQLBill
August 28, 2003 at 3:19 am
The last problem we have solved (copy of the backup file>> there was an sequerity problem on the server an windows give an encrypton description the file can be copy because he is in use). Can you tel me how i can track the growth of the database.
Ronald
quote:
Allen_Cui provided you the simple way to clear the Tempdb log. But you really need to find out WHY it was full.1. Did you run out of disk space?
2. Go to Enterprise Manager, drill down to the databases, right click on TEMPDB and select Properties. Go to Data Files tab and see if it is set to allow AUTO GROW. Do the same thing on the LOG tab. Both should be set to AUTO GROW. You will also need to figure out (guesstimate) how the data and log files should grow (percentage or set MB size). I choose to set the amount of MB it will grow.
3. Make sure you allow it to grow in large enough size so that it doesn't fail because it's waiting for more room.
-SQLBill
August 28, 2003 at 8:13 am
I'm not sure I follow all your comments, but if I understood you correctly you are backing up the TEMPDB. I would never waste time doing that. TEMPDB is just that - a temporary database.
If you need space just BACKUP LOG Tempdb WITH TRUNCATE_ONLY.
I use sp_spaceused to get the information on the growth.
Refer to the Books OnLine for more information on sp_spaceused. Without any parameter, it uses the current database. You can add a table as a parameter and it will give you the growth of the table.
If you are REALLY good with TSQL, you can copy the sp, rename it, and change the query to only provide the data you want. For example, I changed the sp to only provide the Database Name, database size, unallocated space, and amount of data.
-SQLBill
September 2, 2003 at 8:02 am
Hi Bill
We receive the error 'log file for database TempDB is full. Backup the transaction log' in the event log of sql server 2000. the growth is set correctly and there is anought space on the disk. I have read many message with the same problem but nobody has an good anser for solving the problem
Ronald
September 2, 2003 at 10:31 am
What is happening when you get that error? Are you doing a large transaction? I've only seen that error when my TEMPDB isn't set to grow fast enough for the transaction that is occuring. Let's say you set it to grow by 100 MB. Then you have a transaction that requires 1000MB of TEMPDB. That means TEMPDB has to grow 10 times before the transaction finishes. It might not be able to do that fast enough for the transaction (especially BULK INSERTS).
-SQLBill
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply