Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Transaction Logs Full Expand / Collapse
Author
Message
Posted Wednesday, February 20, 2013 2:09 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 22, 2013 1:42 PM
Points: 5, 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
Post #1422296
Posted Wednesday, February 20, 2013 2:22 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 9:04 AM
Points: 23,397, Visits: 32,242
So, what is the result of the following query?

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





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)
Post #1422304
Posted Wednesday, February 20, 2013 2:26 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 22, 2013 1:42 PM
Points: 5, Visits: 9
Thanks for a quick reply

In result I have 2 tables
Name: Auapak_Live_app
log_reuse_wait_desc : Log_Backup

Post #1422306
Posted Wednesday, February 20, 2013 2:31 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 9:04 AM
Points: 23,397, Visits: 32,242
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.




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)
Post #1422307
Posted Wednesday, February 20, 2013 2:32 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 9:04 AM
Points: 23,397, Visits: 32,242
The last article I reference in my signature block talks about managing the transaction log, you should read it.



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)
Post #1422309
Posted Wednesday, February 20, 2013 2:44 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 1:06 PM
Points: 2,829, Visits: 8,479
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.



Post #1422313
Posted Thursday, February 21, 2013 10:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 22, 2013 1:42 PM
Points: 5, 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?

thanks again guys I really appreciate this
Post #1422703
Posted Thursday, February 21, 2013 10:41 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:33 AM
Points: 43,026, Visits: 36,190
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 2008, MVP
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

Post #1422715
Posted Thursday, February 21, 2013 10:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 22, 2013 1:42 PM
Points: 5, 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?
Post #1422720
Posted Thursday, February 21, 2013 11:08 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:33 AM
Points: 43,026, Visits: 36,190
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 2008, MVP
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

Post #1422724
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse