November 30, 2017 at 5:44 am
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.
November 30, 2017 at 6:21 am
what is your backup strategy? how often do you take transaction log backups?
November 30, 2017 at 11:08 pm
Emil B - Thursday, November 30, 2017 6:21 AMwhat 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.
December 1, 2017 at 1:05 am
anjang2k - Thursday, November 30, 2017 11:08 PMEmil B - Thursday, November 30, 2017 6:21 AMwhat 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/
December 1, 2017 at 1:37 am
Emil B - Friday, December 1, 2017 1:05 AManjang2k - Thursday, November 30, 2017 11:08 PMEmil B - Thursday, November 30, 2017 6:21 AMwhat 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?
December 1, 2017 at 1:44 am
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.
December 1, 2017 at 1:46 am
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;
February 19, 2018 at 11:21 am
anjang2k - Thursday, November 30, 2017 11:08 PMAt 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.
June 18, 2018 at 9:50 am
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.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply