February 20, 2013 at 2:09 pm
Hi Guys,
New here and new to the SQL administration.
I have a SQL server 2008 r2 with 3 data bases. I have maintenance plans for all of them to do backups but one DB listed below keeps on giving me this error. I have no idea how or what to do to fix this since I have already used up all resources (which I understand) from google.
thanks in advance
TIME OF EVENT: 2/19/2013 12:05:59 AM
EVENT LOG: Application
EVENT SOURCE: MSSQLSERVER
EVENT ID: 9002
SEVERITY: Error
DESCRIPTION: The transaction log for database 'Aquapak_Live_app' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
February 20, 2013 at 2:22 pm
So, what is the result of the following query?
select name, log_reuse_wait_desc from sys.databases where name = 'Aquapak_Live_app';
February 20, 2013 at 2:26 pm
Thanks for a quick reply
In result I have 2 tables
Name: Auapak_Live_app
log_reuse_wait_desc : Log_Backup
February 20, 2013 at 2:31 pm
Sounds like your database is using BULK_LOGGED or Full Recovery model and you aren't running any transaction log backups. Full backups and Differential backups do not truncate the transaction log. This only occurs when you run a transaction log backup.
February 20, 2013 at 2:32 pm
The last article I reference in my signature block talks about managing the transaction log, you should read it.
February 20, 2013 at 2:44 pm
Lynn Pettis (2/20/2013)
Sounds like your database is using BULK_LOGGED or Full Recovery model and you aren't running any transaction log backups. Full backups and Differential backups do not truncate the transaction log. This only occurs when you run a transaction log backup.
If so, then you should take regular transaction log backups, such as every 30 minutes, depending on how important the databse is.
February 21, 2013 at 10:20 am
ok so at the time I opened this thread the DB was in simple mode which is weird
so I changed it to FUll mode
Did a bckup with Transaction log back
This morning we weren't able to connect to the DB
So changed the mode back to the Simple mode everything started to work
I ran your query again
and now under Log reuse wait desc came out to "nothing" for that DB
Can you make any sense out of this? 🙁
thanks again guys I really appreciate this
February 21, 2013 at 10:41 am
Why did you change the DB to full recovery in the first place? Full recovery makes log management harder (you need scheduled log backups), not easier.
A DB in simple recovery will not show log backup as the log_reuse_wait_desc, because simple recovery does not need log backups.
Please read through this - Managing Transaction Logs[/url] and this - http://www.sqlservercentral.com/articles/Transaction+Log/72488/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 21, 2013 at 10:49 am
I was reading info about transaction logs and DB modes
Now if the DB is in Simple Mode why would it be giving messages such as "DB log file is full" when what I understand it should automatically clear that out after the backup?
So thinking maybe someone changed it by mistake to simple mode and now the transaction log backups are not running the file got full?
February 21, 2013 at 11:08 am
ausman (2/21/2013)
Now if the DB is in Simple Mode why would it be giving messages such as "DB log file is full" when what I understand it should automatically clear that out after the backup?
Simple recovery does not prevent the log from filling, just means you don't need to run log backups to mark the log as reusable. See the two articles I just referenced. btw, full backups have nothing to do with log reuse in any recovery model.
So thinking maybe someone changed it by mistake to simple mode and now the transaction log backups are not running the file got full?
A database in simple recovery does not need log backups to mark the log as reusable. See the two articles I just referenced.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 21, 2013 at 11:10 am
Regardless of your current problem, if 'Aquapak_Live_app' database is a critical database, then you most likely should have it in FULL recovery, and take regular trans log backups so you can recover to point in time.
February 21, 2013 at 1:05 pm
Ausman,
Just changing the recovery model to simple does not truncate the log....I think it has to wait for the next checkpoint to happen.
Also, I have noticed that if you haven't done log backup in a while and if the log is big, you have to do log backup couple of times and then shrink the log in order to free up space.
Please let u know how it went.
Thanks,
SueTons.
Regards,
SQLisAwe5oMe.
February 22, 2013 at 12:09 pm
You guys are right so i have contacted SAGE about this and they said that the DB should have been in Bulk logged mode but someone went in there before me and changed it to Simple for whatever reason.
I will sometime today backup the DB and change it to Bulk Logged
Turn on Transaction log backups for it and let them run every half hour
Thank you so much guys 🙂
February 22, 2013 at 1:41 pm
Bulk logged is not usually an isolation level that's used for long periods of time, because of the risks involved (can't do point in time recovery to a log interval that contains a bulk operation). Since the point of log backups is to allow point in time recovery, that's kinda counter-productive.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply