<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
SQL Server 2008 Administration
»
Transaction Logs Full
14 posts, Page 1 of 2
1
2
»»
Transaction Logs Full
Rate Topic
Display Mode
Topic Options
Author
Message
ausman
ausman
Posted Wednesday, February 20, 2013 2:09 PM
Forum 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
Lynn Pettis
Lynn Pettis
Posted Wednesday, February 20, 2013 2:22 PM
SSC-Insane
Group: General Forum Members
Last Login: Today @ 3:18 AM
Points: 21,588,
Visits: 27,375
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
ausman
ausman
Posted Wednesday, February 20, 2013 2:26 PM
Forum 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
Lynn Pettis
Lynn Pettis
Posted Wednesday, February 20, 2013 2:31 PM
SSC-Insane
Group: General Forum Members
Last Login: Today @ 3:18 AM
Points: 21,588,
Visits: 27,375
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
Lynn Pettis
Lynn Pettis
Posted Wednesday, February 20, 2013 2:32 PM
SSC-Insane
Group: General Forum Members
Last Login: Today @ 3:18 AM
Points: 21,588,
Visits: 27,375
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
homebrew01
homebrew01
Posted Wednesday, February 20, 2013 2:44 PM
SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 11:37 AM
Points: 2,551,
Visits: 7,201
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
ausman
ausman
Posted Thursday, February 21, 2013 10:20 AM
Forum 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
GilaMonster
GilaMonster
Posted Thursday, February 21, 2013 10:41 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 2:48 AM
Points: 37,635,
Visits: 29,886
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
ausman
ausman
Posted Thursday, February 21, 2013 10:49 AM
Forum 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
GilaMonster
GilaMonster
Posted Thursday, February 21, 2013 11:08 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 2:48 AM
Points: 37,635,
Visits: 29,886
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 »
14 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
