March 19, 2016 at 5:06 am
Good morning Experts,
Transaction log file is full. I cannot even issue backup log command as there is no space at all. Please advise the best solution
March 19, 2016 at 6:00 am
coolchaitu (3/19/2016)
Good morning Experts,Transaction log file is full. I cannot even issue backup log command as there is no space at all. Please advise the best solution
Temporarily add a file to tempdb on another drive while you resolve the issue.
😎
March 19, 2016 at 6:14 am
What is the resolution for the issue
March 19, 2016 at 6:37 am
Tempdb is always in simple recovery so backup is futile, find out what is filling the log or even better, add the new file and see if what ever operation which is causing this finishes.
😎
March 19, 2016 at 7:06 am
It is not tempdb. It is an user database
March 19, 2016 at 1:37 pm
What else is going on with the database? Replication? Log Shipping? AlwaysOn?
The sledgehammer approach is to try changing to SIMPLE recovery but that could create a ton of work for you as well as some risk running with no DR-safety-net. If you do it, you can then shrink the log file and start anew by switching back to FULL and rebuilding whatever you need to rebuild if you were using some of the features I mentioned.
As Eirikur mentions though, if the log grew once chances are it will grow again. You need to get to the bottom of the why question.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 20, 2016 at 5:23 am
coolchaitu (3/19/2016)
It is not tempdb. It is an user database
Not sure how I got tempdb mixed up in this, guess I just blame my bad eyesight reading off the mobile:-)
Did you add a log file to the database?
😎
March 20, 2016 at 7:49 am
There is no space on other drive to add file.Please advise
March 20, 2016 at 8:15 am
coolchaitu (3/20/2016)
There is no space on other drive to add file.Please advise
Quick questions:
1. What is the disk setup on the server (Volumes,Sizes etc.)?
2. What are the recovery models on the user databases?
3. What Operating System (Win Version)?
4. Is this a production server?
5. Do you have physical access to the server?
6. What are the file growth settings on the Log files?
7. What are the file growth settings on the Data files?
8. Can you free up space on the drive by moving or deleting redundant / replaceable files (set-up files, temp files etc.)?
😎
This is starting to sound like a real mess, suggest you get some assistance from a local experienced SQL Server DBA.
March 20, 2016 at 11:42 am
Eirikur Eiriksson (3/20/2016)
This is starting to sound like a real mess, suggest you get some assistance from a local experienced SQL Server DBA.
+1
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 29, 2016 at 11:38 am
Have you ever backed up a transaction log for this database?. Sometimes the database is set in 'Full' recovery model by mistake and never backed up the transaction log. If that's the case, as suggested put the database in Simple recovery model.
If recovery model is intentional and transaction log file is getting filled up, check if any index or any job is running. Its also get complicated if you have replication, mirroring or any other set up.
March 29, 2016 at 9:37 pm
There is no replication or mirroring set up. How about issuing a checkpoint?
March 29, 2016 at 10:05 pm
coolchaitu (3/29/2016)
There is no replication or mirroring set up. How about issuing a checkpoint?
checkpoints do not cause log truncation when in full (or bulk logged) recovery mode and a log chain has been established (at least one full backup was taken).
a lot of time has passed since you first posted... is this database still running in this tlog is full state? if so it must not be critical.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 29, 2016 at 11:09 pm
Quick fix would be take a Full backup,Put the database in single_user mode(some downtime would be needed),change recovery to SIMPLE,truncate the log,change recovery to FULL,put db in MULTI_USER,take a DIFF backup and schedule a regular tlog backup to keep the future log growths in check.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply