September 7, 2017 at 3:20 am
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.
September 7, 2017 at 3:31 am
Thom A - Thursday, September 7, 2017 3:23 AMWhat 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.
September 7, 2017 at 3:42 am
anjang2k - Thursday, September 7, 2017 3:31 AMWe 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.
Larnu.uk
September 7, 2017 at 4:14 am
Thom A - Thursday, September 7, 2017 3:42 AManjang2k - Thursday, September 7, 2017 3:31 AMWe 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?
September 7, 2017 at 4:25 am
anjang2k - Thursday, September 7, 2017 4:14 AMCan 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.
September 7, 2017 at 4:40 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 7, 2017 at 4:44 am
Grant Fritchey - Thursday, September 7, 2017 4:40 AMFunny 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
September 7, 2017 at 4:48 am
anthony.green - Thursday, September 7, 2017 4:44 AMGrant Fritchey - Thursday, September 7, 2017 4:40 AMFunny 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)
Thomas Rushton
blog: https://thelonedba.wordpress.com
September 7, 2017 at 5:53 am
Thom A - Thursday, September 7, 2017 3:23 AMWhat 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 ?
September 7, 2017 at 6:17 am
Arsh - Thursday, September 7, 2017 5:53 AMThom A - Thursday, September 7, 2017 3:23 AMWhat 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 ?
Sorry, I forgot that you were mirroring when I wrote that post.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply