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


Huge volume of .ldf file causing drive full frequently.


Huge volume of .ldf file causing drive full frequently.

Author
Message
anjang2k
anjang2k
SSC Veteran
SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)

Group: General Forum Members
Points: 295 Visits: 35
I am having 2 servers( namely Server 1 and Server 2) based on Windows Server 2008 R2 where SQL server 2008 R2 Enterprise edition is installed.
I am having 2 SQL databases that are mirrored from Server 1 to 2. e.g. at a given point of time say, Server 1 database is Principal and Server 2 Database is mirrored. The mirroring is in automatic fail over mode with the help of a Witness Server.
These 2 databases are huge w.r.t to transaction and in a day, one database is having almost 4000 rows added in it.
I have facing the issue of huge volume of .ldf file which is causing the designated disk for SQL filled very frequently.

Frequently we need to stop mirroring, take backup and delete the .ldf file.This is a troublesome job.The volume of .ldf file is almost 230 GB and .mdf file volume is 28.7 GB.This causing great inconvenience to the database maintenance staff.

Is there any way to solve this issue so that,.ldf file volume can be less and require very less manual intervention for delete the same by stopping mirroring , take back up and delete the files.
Thom A
Thom A
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86694 Visits: 22261
What Recovery Model are using, and if (I assume correctly) you are using FULL, are you doing frequent log backups? How often is "frequent"?


Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
anjang2k
anjang2k
SSC Veteran
SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)

Group: General Forum Members
Points: 295 Visits: 35
Thom A - Thursday, September 7, 2017 3:23 AM
What Recovery Model are using, and if (I assume correctly) you are using FULL, are you doing frequent log backups? How often is "frequent"?

We are using FULL. No we are not doing frequent log backup.Taking backup of Log is manual activity for us.We do it in 3 months interval.
We intend to automate this process.

Thom A
Thom A
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86694 Visits: 22261
anjang2k - Thursday, September 7, 2017 3:31 AM

We are using FULL. No we are not doing frequent log backup.Taking backup of Log is manual activity for us.We do it in 3 months interval.
We intend to automate this process.

That's why it's so large. You need to do transactional back up on a much more frequent basis. And by frequent, I don't mean, Monthly or weekly, I'm talking hourly (at a minimum). Not doing a backup for 3 months is far from ideal.

At the moment, your ldf file is storing transactions up to the last point you did a transactional back up. If that was 2 month and 28 days ago, of course it's going to be huge, it's got almost 3 months worth of transaction details stored.

Edit: If you can't do frequent Transactional Backups, I'd suggest switching to the SIMPLE Recovery model, and then creating a backup of the database.



Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
anjang2k
anjang2k
SSC Veteran
SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)

Group: General Forum Members
Points: 295 Visits: 35
Thom A - Thursday, September 7, 2017 3:42 AM
anjang2k - Thursday, September 7, 2017 3:31 AM

We are using FULL. No we are not doing frequent log backup.Taking backup of Log is manual activity for us.We do it in 3 months interval.
We intend to automate this process.

That's why it's so large. You need to do transactional back up on a much more frequent basis. And by frequent, I don't mean, Monthly or weekly, I'm talking hourly (at a minimum). Not doing a backup for 3 months is far from ideal.

At the moment, your ldf file is storing transactions up to the last point you did a transactional back up. If that was 2 month and 28 days ago, of course it's going to be huge, it's got almost 3 months worth of transaction details stored.

Edit: If you can't do frequent Transactional Backups, I'd suggest switching to the SIMPLE Recovery model, and then creating a backup of the database.

Can you share the procedure to make a schedule job in order to backup of transaction log?

anthony.green
anthony.green
SSC Guru
SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)

Group: General Forum Members
Points: 103158 Visits: 8698
anjang2k - Thursday, September 7, 2017 4:14 AM

Can you share the procedure to make a schedule job in order to backup of transaction log?

Take a look at Ola Hallengren's maintenance script https://ola.hallengren.com/ or Minionware Backup http://minionware.net/products/backup/ if that's to much to take in, take a look at a creating a maintenance plan https://docs.microsoft.com/en-us/sql/relational-databases/maintenance-plans/options-in-the-back-up-database-task-for-maintenance-plan.




How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (361K reputation)SSC Guru (361K reputation)SSC Guru (361K reputation)SSC Guru (361K reputation)SSC Guru (361K reputation)SSC Guru (361K reputation)SSC Guru (361K reputation)SSC Guru (361K reputation)

Group: General Forum Members
Points: 361755 Visits: 34454
Funny how this blog post never goes out of date. Please read this about your log file.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
Product Evangelist for Red Gate Software
anthony.green
anthony.green
SSC Guru
SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)

Group: General Forum Members
Points: 103158 Visits: 8698
Grant Fritchey - Thursday, September 7, 2017 4:40 AM
Funny how this blog post never goes out of date. Please read this about your log file.


Or this book Troubleshooting SQL Server: A Guide for the Accidental DBA



How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


Thomas Rushton
Thomas Rushton
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: Moderators
Points: 19905 Visits: 10443
anthony.green - Thursday, September 7, 2017 4:44 AM
Grant Fritchey - Thursday, September 7, 2017 4:40 AM
Funny how this blog post never goes out of date. Please read this about your log file.


Or this book Troubleshooting SQL Server: A Guide for the Accidental DBA

Or this book: SQL Server Transaction Log Management
(also a free PDF)

Arsh
Arsh
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5367 Visits: 1001
Thom A - Thursday, September 7, 2017 3:23 AM
What Recovery Model are using, and if (I assume correctly) you are using FULL, are you doing frequent log backups? How often is "frequent"?

Just curious . Can mirroring be setup with other recovery models ? I believe a mirror set up would need at least one log backup and hence the need to have the DB's in 'Full' recovery model , or , am I missing something ?

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