February 11, 2011 at 3:15 am
hi All,
I found this Error
The transaction log for database 'tempdb' is full. Msg 9002, Level 17, State 4, Line 2
To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
can any body please explain ,if we encounter the above error what are the steps to resolve this error
Thanks & Regards
Deepak.A
February 11, 2011 at 3:23 am
Is there any space left on the drive where the tempdb is hosted. I feel that you will most probably need to restart the SQL Server Services.
Cheers,
Satnam
February 11, 2011 at 3:33 am
As Satnam already mentioned, restarting the SQL Server will clear up the log space used by tempdb.
If this however is not an option, you can try the following:
* Issue a CHECKPOINT command to free up log space in the log file.
* Check the available log space with DBCC SQLPERF('logspace'). If only a small percentage of your log file is actually been used, you can try a DBCC SHRINKFILE command. This can however possibly introduce corruption in tempdb.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 11, 2011 at 4:05 am
If you have another drive with space available you can try to add a file there in order to get enough space to attempt to resolve the issue, whatever it might be.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 12, 2011 at 2:16 am
Koen Verbeeck (2/11/2011)
* Issue a CHECKPOINT command to free up log space in the log file.
.
Hi Koen verbeeck,
What is CHECKPOINT , how to issue the CHECKPONT , how it will free up the space in the tempDB ?
Can you please explain little biit more
Thanks & Regards
Deepak.A
February 12, 2011 at 6:52 am
http://msdn.microsoft.com/en-us/library/ms188748.aspx
http://sqlserverpedia.com/blog/sql-server-bloggers/the-lazy-writer-and-the-checkpoint/
http://www.sqlskills.com/BLOGS/PAUL/post/What-does-checkpoint-do-for-tempdb.aspx
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 9, 2013 at 1:31 am
This issue with the log file size.You can verify the below url to rectify easily.
The transaction log for database 'mydb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases......http://mytecharticle.com/?p=447
August 9, 2013 at 1:38 am
rupesh.rams (8/9/2013)
The transaction log for database 'mydb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases......http://mytecharticle.com/?p=447%5B/quote%5D
This thread is two years old, I hope his log issues are already fixed. 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 21, 2013 at 12:38 pm
Koen Verbeeck (2/11/2011)
As Satnam already mentioned, restarting the SQL Server will clear up the log space used by tempdb.
I too am getting:
Msg 9002, Level 17, State 4, Line 10
The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
and I just had our system admin restart server, but problem is same. Using SQL Server 2008 R2.
The log_reuse_wait value in sys.databases was 4 before and still is.
I am going to read about Checkpoint...with the links provided.
--Quote me
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply