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 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 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
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3161 Visits: 1989
what is your backup strategy? how often do you take transaction log backups?
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
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
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3161 Visits: 1989
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 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
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 (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63328 Visits: 8598
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
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3161 Visits: 1989
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;

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