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

Log file growing on principal server--log backups scheduled every 2 hrs. Expand / Collapse
Author
Message
Posted Thursday, January 31, 2013 5:20 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 6, 2013 10:41 AM
Points: 9, Visits: 192
Hi,

The transaction log file is growing(currently 99%) and it is close to zero out the disk space.
Tranasaction log backups are in place running every 2 hours.
Mirroring is synchronized - no problem with mirroring.

Here is what i tried:
Took a log backup and tried to shrink the log. Unable to shrink the log file.
Also, transaction log growth hits 100% when taking log backup.

log_reuse_wait_desc shows LOGBACKUP but still backing up log shrinking doesn't work.

Please advise.
Post #1414406
Posted Thursday, January 31, 2013 10:00 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Yesterday @ 11:10 AM
Points: 20,808, Visits: 32,746
Looks to me that you may need to run your log backups more frequently than every two hours.

You may also have a problem with many small VLF's. Not sure how to show this, but I'm sure someone else can help with that.



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 #1414438
Posted Friday, February 1, 2013 1:42 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: Yesterday @ 2:35 PM
Points: 40,438, Visits: 36,895
Increase the frequency of your log backups.

Why on earth would you try to shrink a full log? Full means no free space left in the log, surely that would suggest growing it, not shrinking it.



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 #1414493
Posted Friday, February 1, 2013 11:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 6, 2013 10:41 AM
Points: 9, Visits: 192
Thanks for your reply Lynn. I increased the frequency of log backups and seems like it is working.

Thanks.
Post #1414783
Posted Friday, February 1, 2013 11:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 6, 2013 10:41 AM
Points: 9, Visits: 192
Thanks Gail. I increased the frequency of logbackups.

I tried shrinking because, my log file size is 60 GB and a log backup releasing 90% of space.
that was my prod DB and my disk left with only 500 MB space left.
I thought of shrinking and release some gigs to OS to avoid any application interruption.

The problem now i am facing is, though i have 90% unused space in the log , i could not shrink it.Not sure why.

Please correct me if my approach is wrong.
Thanks for your time!
Post #1414790
Posted Saturday, February 2, 2013 6:16 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 1:05 PM
Points: 2,834, Visits: 8,544
Uncommitted replication transactions can also be a problem.


Post #1414975
Posted Sunday, February 3, 2013 11:43 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 5:34 PM
Points: 35,609, Visits: 32,200
HarryG24 (2/1/2013)
Thanks Gail. I increased the frequency of logbackups.

I tried shrinking because, my log file size is 60 GB and a log backup releasing 90% of space.
that was my prod DB and my disk left with only 500 MB space left.
I thought of shrinking and release some gigs to OS to avoid any application interruption.

The problem now i am facing is, though i have 90% unused space in the log , i could not shrink it.Not sure why.

Please correct me if my approach is wrong.
Thanks for your time!


How big do the log files (the TRN files in the backups) end up being now that you're backing them up once per hour?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1415040
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse