July 6, 2010 at 4:17 am
Hi,
I have a scenario. i have a database of size 200 GB. yesterday, i got space issue on OLTP side and we (DBA ) team decide to shrink the log file for that first we remove the mirroring. then did the log shrink. after that we needed to start miroring again, folowing is the time interval for some task.
(On Staging Server)
backup taken on OLTP node : 2 hrs.
restoration : 1 hr
mirroring setup : 1 minute
Total downtime : 3 hrs
now i need to do log shrinking for another DB whose size is around 500+ GB. so again downtime would be around 6-7 hrs .
Is there any way that i can shrink the log with less downtime.
If i "PAUSE" mirroring can it help me ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
July 7, 2010 at 4:17 am
I still stuck with his problem , please help me
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
July 8, 2010 at 6:44 am
-- sql 2000
USE DatabaseName
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO
--- end
--SQL 2008
USE [master]
GO
ALTER DATABASE [TestDb] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(TestDbLog, 1)
ALTER DATABASE [TestDb] SET RECOVERY FULL WITH NO_WAIT
GO
July 8, 2010 at 6:54 am
Why do you feel that you need to remove mirroring to do this?
I shrink log files all the time for databases that are participating in mirroring.
The Redneck DBA
July 8, 2010 at 11:01 am
Have you determined the root cause of the transaction log growth? What is prohibiting the SQL Server from automatically truncating the log and thereby freeing disk space? Are database backup events preventing log truncation? Are long-running transactions holding up truncation and causing the log to grow? With database mirroring implemented, is the secondary server instance falling behind the primary server instance and thereby causing the primary server's transaction log to grow?
With database mirroring it is required to stop mirroring, take a log backup that truncates the log, apply the log backup to the mirror (using WITH NORECOVERY) and restart mirroring.
July 8, 2010 at 11:25 am
I would be wondering if you are taking periodic t-log backups on the primary database as well.
July 8, 2010 at 12:34 pm
OK, what am I missing here? Why are you thinking you need to stop mirroring? Just take a log backup and truncate the log if it is too big for your liking.
A better solution is probably to take more frequent log backups to start with so it's size doesn't get out of hand to start with.
The Redneck DBA
July 8, 2010 at 9:55 pm
The reason to stop mirroring is : if i rebuild the indexs of larger tables, log will grow tramendously.and same size (log) will be replicated to report node also. so to avoid this i am thinknig to stop it.
Anyways by reading all your comments, i have decided to
take log backup
do shrinking.
and appliying log backup to mirrored side
Is this OK ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
July 9, 2010 at 5:43 am
Jason Shadonix (7/8/2010)
OK, what am I missing here? Why are you thinking you need to stop mirroring? Just take a log backup and truncate the log if it is too big for your liking.A better solution is probably to take more frequent log backups to start with so it's size doesn't get out of hand to start with.
U sure that u dont have any issue after taking log backup of database in mirroring??
I wonder as this taking log backup will break the chain..
July 9, 2010 at 6:17 am
Bhuvnesh (7/8/2010)
The reason to stop mirroring is : if i rebuild the indexs of larger tables, log will grow tramendously.and same size (log) will be replicated to report node also. so to avoid this i am thinknig to stop it.Anyways by reading all your comments, i have decided to
take log backup
do shrinking.
and appliying log backup to mirrored side
Is this OK ?
Ah. I have had a little trouble with index rebuilds causing issues when I was mirroring across a WAN before. I dealt with it by using a modified version of the script in example D on this Books Online page: http://msdn.microsoft.com/en-us/library/ms188917.aspx to only rebuild indexes that are fragmented over a certain percent. I also set the MAXDOP option to 1 to "slow it down" a little bit to help mirroring keep up.
Another option you might play with is to temporarily switch your mirroring to asynchronous mode if you can tolerate a little possibility of data loss (don't think that will work if you have a witness server configured though).
I've never tried applying a log backup to a mirror database after mirroring was up and running, so I can't say if your plan will be OK. I notice you are calling your mirror server "report node". Are you taking snapshots of the mirrored database for reporting purposes or something? If so, that might affect your ability to do restores on the mirrored database. It also might account for some of your file growth on the mirror side...I would think a snapshot would grow quite a bit during a large index rebuild. I'd just create a small database and test it before you try it for real.
The Redneck DBA
July 9, 2010 at 6:19 am
Ratheesh.K.Nair (7/9/2010)
Jason Shadonix (7/8/2010)
OK, what am I missing here? Why are you thinking you need to stop mirroring? Just take a log backup and truncate the log if it is too big for your liking.A better solution is probably to take more frequent log backups to start with so it's size doesn't get out of hand to start with.
U sure that u dont have any issue after taking log backup of database in mirroring??
I wonder as this taking log backup will break the chain..
There are no issues with making a transaction log backups of your database when it is mirroring. In fact it's pretty much required to do some sort of backup / truncation / shrink as the log will grow forever if you don't do anything to it.
The Redneck DBA
July 9, 2010 at 6:25 am
I keep my log file size in check by running this every hour on my mirrored servers. (You'll have to modify the backup command to not use compression if you are running this on SQL 2005).
If the database in question is highly critical, you'd probably also want to modify this to actually retain these log backups (and move them to a different server) instead of just deleting them as an extra precaution.
But there is no problem with taking a log backup while mirroring is running.
-----------------------------------------------------------------------
DECLARE @sql VARCHAR(512) -- Dynamic SQL
DECLARE @CMD VARCHAR(512) -- Dynamic CMDSHELL
DECLARE @CURRENTDB VARCHAR(255)
DECLARE DB_CURSOR CURSOR FOR
SELECT
[NAME]
FROM
sys.databases db
INNER JOIN sys.database_mirroring mirror
ON db.database_id = mirror.database_id
WHERE
db.recovery_model_desc = 'FULL'
AND mirror.mirroring_role_desc = 'PRINCIPAL'
OPEN DB_CURSOR
FETCH NEXT FROM DB_CURSOR INTO @CURRENTDB
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'BACKUP LOG ' + @CURRENTDB +
' TO DISK = ''D:\' + @CURRENTDB + '_LOG.bak''
WITH INIT, COMPRESSION, STATS = 1'
EXECUTE (@SQL)
SET @sql = 'USE ' + @CURRENTDB + '
'
SET @sql = @sql + 'DBCC SHRINKFILE (''' + @CURRENTDB + '_LOG'', 0, TRUNCATEONLY)'
EXECUTE (@SQL)
SET @CMD = 'DEL D:\' + @CURRENTDB + '_LOG.bak'
EXECUTE master.dbo.xp_cmdshell @CMD
FETCH NEXT FROM DB_CURSOR INTO @CURRENTDB
END
CLOSE DB_CURSOR
DEALLOCATE DB_CURSOR
The Redneck DBA
July 9, 2010 at 7:09 am
Jason Shadonix (7/9/2010)
I also set the MAXDOP option to 1 to "slow it down" a little bit to help mirroring keep up.
Whats the benefit you got with it ?
Jason Shadonix (7/9/2010)
I notice you are calling your mirror server "report node". Are you taking snapshots of the mirrored database for reporting purposes or something? If so, that might affect your ability to do restores on the mirrored database. It also might account for some of your file growth on the mirror side...I would think a snapshot would grow quite a bit during a large index rebuild.
yes it is being used for reporting purposes, so how would i go for indexe rebuild ?
Can i shrink log with mirroring running state on principal node ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
July 9, 2010 at 7:40 am
Bhuvnesh (7/9/2010)
Jason Shadonix (7/9/2010)
I also set the MAXDOP option to 1 to "slow it down" a little bit to help mirroring keep up.
Whats the benefit you got with it ?
Jason Shadonix (7/9/2010)
I notice you are calling your mirror server "report node". Are you taking snapshots of the mirrored database for reporting purposes or something? If so, that might affect your ability to do restores on the mirrored database. It also might account for some of your file growth on the mirror side...I would think a snapshot would grow quite a bit during a large index rebuild.
yes it is being used for reporting purposes, so how would i go for indexe rebuild ?
Can i shrink log with mirroring running state on principal node ?
The benfit I got with setting the MAXDOP to 1 was the index rebuild took longer to run (because it was only using one thread), which allowed mirroring to keep up. In my case the bottleneck was network bandwidth because it was mirroring across a WAN (a couple thousand miles). If you are just mirroring across a LAN it may be more of a disk I/O bottleneck, but slowing down the rate of change may still help.
I'll have to defer to someone who uses snapshots on your snapshot question. But my guess is the snapshot files will grow as much as the log file does (more or less).
YES, you CAN run log backups and shrinks while a database is mirroring. SQL is smart enough not to truncate / shrink away any data that is needed for mirroring.
The Redneck DBA
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply