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 Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 22
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-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45644 Visits: 15487
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 Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 22
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-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45644 Visits: 15487
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 Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 22
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 (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62430 Visits: 8587
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 (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)

Group: General Forum Members
Points: 220562 Visits: 33546
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
anthony.green
anthony.green
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62430 Visits: 8587
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
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: Moderators
Points: 11774 Visits: 6171
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
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2188 Visits: 822
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