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


SQL server 2008 R2 frequent transaction log full and my database is a mirrored one.


SQL server 2008 R2 frequent transaction log full and my database is a mirrored one.

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

Group: General Forum Members
Points: 217 Visits: 35
I have 1 pair of identical server with SQL server 2008 R2 is installed.I have 2 databases in it which are mirrored.
Frequently I am facing disk full issue as the transaction logs gets full frequently. (with in 5 days it becomes 260 GB for 1 database).
The following activity we do when the disk space gets full.
1) Break the mirroring.
2) Shrink the transaction log.
3) Take full backup of the database and its transaction log.
4) Restore the same in standby machine with NO RECOVERY. (first full and then transaction log).
5) Then start the mirroring in the primary machine that was originally before mirroring.
This request a manual intervention. Can we automate the same through some maintenance plan or some scripts.
It will be nice if some body guide me in this regard.
Emil B
Emil B
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4518 Visits: 2016
what is your backup strategy? how often do you take transaction log backups?
anjang2k
anjang2k
SSC Veteran
SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)

Group: General Forum Members
Points: 217 Visits: 35
Emil B - Thursday, November 30, 2017 6:21 AM
what is your backup strategy? how often do you take transaction log backups?

At present we normally do the above mentioned steps once in 5 days.We backup transaction log only when we see that the disk is going to get full.
We can take backup in every 5 days.And keep the backup in separate location.

Emil B
Emil B
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4518 Visits: 2016
anjang2k - Thursday, November 30, 2017 11:08 PM
Emil B - Thursday, November 30, 2017 6:21 AM
what is your backup strategy? how often do you take transaction log backups?

At present we normally do the above mentioned steps once in 5 days.We backup transaction log only when we see that the disk is going to get full.
We can take backup in every 5 days.And keep the backup in separate location.

Your log will be growing until you take regular log backups, like let's say every 1 hour for start - this will allow you later to do a point in time restores. You have to remember that mirroring does not protect you from all disaster scenarios (like delete data). Your database is already in FULL recovery model so might as well use it to it's full potential in terms of protecting your data.

This Article should help you understand how transaction log works an why it's growing:
http://www.sqlservercentral.com/articles/Administration/64582/

anjang2k
anjang2k
SSC Veteran
SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)

Group: General Forum Members
Points: 217 Visits: 35
Emil B - Friday, December 1, 2017 1:05 AM
anjang2k - Thursday, November 30, 2017 11:08 PM
Emil B - Thursday, November 30, 2017 6:21 AM
what is your backup strategy? how often do you take transaction log backups?

At present we normally do the above mentioned steps once in 5 days.We backup transaction log only when we see that the disk is going to get full.
We can take backup in every 5 days.And keep the backup in separate location.

Your log will be growing until you take regular log backups, like let's say every 1 hour for start - this will allow you later to do a point in time restores. You have to remember that mirroring does not protect you from all disaster scenarios (like delete data). Your database is already in FULL recovery model so might as well use it to it's full potential in terms of protecting your data.

But how to take the backup of principal and mirror server by some maintenance schedule?

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

Group: General Forum Members
Points: 96820 Visits: 8690
You need to setup a maintenance schedule.

Lots of ways to do it, write your own, use maintenance plans, Ola's maintenance solution, Minion Ware, Red-Gate tools, Quest tools, Idera tools, ApexSQL tools.

Few links below for you to read up on transaction log management, backup & restore, maintenance plans, and my favorite Accidental DBA book helped me out so many times.

http://www.sqlservercentral.com/articles/books/94938/
http://www.sqlservercentral.com/articles/books/89519/
http://www.sqlservercentral.com/articles/books/68380/

https://ola.hallengren.com/
http://www.minionware.net/
http://www.sqlservercentral.com/articles/books/76296/

If it was me I'd go with Ola, but his scripts can be a little daunting, so read the maintenance plan book and go with that for a first pass.



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


Emil B
Emil B
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4518 Visits: 2016
you should backup principal, you can use either Olas scripts, maintenance plans or write your own.

Set up a backup solution of your choice on both servers and make sure it will only perform a log backup when the mirroring role is 'PRINCIPAL'
I usually use something like this in my sql agent jobs:

DECLARE @Mirror TINYINT;

SELECT @Mirror = mirroring_role
FROM sys.database_mirroring
WHERE DB_NAME(database_id) = N'DBName'

IF (@Mirror = 1 OR @Mirror IS NULL)
BEGIN
PRINT 'Your Code';
END;

Arsh
Arsh
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4605 Visits: 917
anjang2k - Thursday, November 30, 2017 11:08 PM

At present we normally do the above mentioned steps once in 5 days.We backup transaction log only when we see that the disk is going to get full.
We can take backup in every 5 days.And keep the backup in separate location.

Not taking a transactional log backup for a database in Full Recovery model will make the log grow until the disk becomes full. You MUST have log backup schedule for the same at frequent intervals. Maintenance plans or agent jobs are good enough to do it. I didn't see any problem setting them up for a mirrored database. Just curious.

shekhar.mca
shekhar.mca
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 60
Schedule(On principal server) a transaction log backup for the database in every 30 min interval and keep a tab on storage (Backup location).
Ensure backup retention plan is in place to avoid high storage.
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