SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Transaction Logs Full


Transaction Logs Full

Author
Message
ausman
ausman
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 9
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
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)

Group: General Forum Members
Points: 92511 Visits: 38954
So, what is the result of the following query?

select name, log_reuse_wait_desc from sys.databases where name = 'Aquapak_Live_app';

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
ausman
ausman
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 9
Thanks for a quick reply

In result I have 2 tables
Name: Auapak_Live_app
log_reuse_wait_desc : Log_Backup
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)

Group: General Forum Members
Points: 92511 Visits: 38954
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)

Group: General Forum Members
Points: 92511 Visits: 38954
The last article I reference in my signature block talks about managing the transaction log, you should read it.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
homebrew01
homebrew01
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12000 Visits: 9222
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.



ausman
ausman
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 9
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? Sad

thanks again guys I really appreciate this
GilaMonster
GilaMonster
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218267 Visits: 46278
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 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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


ausman
ausman
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 9
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?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218267 Visits: 46278
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search