Transaction log on Mirrorring

Last Post 18 Apr 2013 09:46 AM by gunneyk. 10 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
sql-tips
New Member
New Member

--
30 Jan 2013 05:45 PM
How do I shrink transaction log without disabling the mirroring setup?

Can I use following? Right now I have only prod, no test system available to try it out.

USE DatabaseName
GO
DBCC SHRINKFILE(, 1)
BACKUP LOG WITH TRUNCATE_ONLY
DBCC SHRINKFILE(, 1)
GO

rm
New Member
New Member

--
31 Jan 2013 05:05 AM
You can't backup log with truncate, and can't cleanup logs that are not committed on partner yet.
gunneyk
New Member
New Member

--
01 Feb 2013 07:28 AM
Why do you wnat to shrink it in the first place? You can issue a shrinkfile on the log but don't shrink it all the way down or it will just grow again and cause perf issues.
sql-tips
New Member
New Member

--
09 Feb 2013 08:44 AM
The reason I want to shrink is the log growth went up to 200GB and having space issues. looking for best way to reduce the log size.
gunneyk
New Member
New Member

--
13 Feb 2013 06:31 AM
It probably grew due to either someone leaving a long running open transaction or you are in FULL recovery mode and not issuing reglar log backups. In any case use DBCC SHRINKFILE to shrink it down.
skyline212
New Member
New Member

--
16 Apr 2013 06:37 AM
you can't shrink a t-log if the database is mirrored unless you deactivate the mirror. If I'm wrong, please correct me, but I've found no solution that works!

Log shipping is the way to go if you only have two servers. Mirroring is almost pointless without a witness server, because the only way to fail over is from the principal... kinda defeats the purpose of having a mirror if you can't fail over when the principal crashes.
rm
New Member
New Member

--
16 Apr 2013 10:46 AM
Not true, you can shrink log even db is mirrored.
skyline212
New Member
New Member

--
16 Apr 2013 10:35 PM
Shrinking the log file only works when there's free space at the end of the log file. If your log file is growing, that means that it must be writing to the last logical file (the one at the end of the physical file).
Backing up the log file clears out all of the logical files but, the current logical file is still the one at the end of the physical file so, you can't shrink.
The short answer is, keep repeating the backup log and shrink until it works.
gunneyk
New Member
New Member

--
18 Apr 2013 05:09 AM
If there are no uncommitted trans and you have a very recent log backup then the active VLF can be moved to the beginning of the file and thus allow shrinking. This is done automatically with Shrinkfile since SQL2000. However the VLF's at the beginning of the log file must be able to be truncated. If you had a long running tran find it and kill it or commit it and this should be a non-issue.
skyline212
New Member
New Member

--
18 Apr 2013 07:08 AM
There is another option available to shrink your database try out
http://www.sqlservermanagement.net/
to shrink your SQL Server database via few clicks only, with the help of this tool you can perform many other operations as well like Partitioning, Defragment your database,
Capacity Management etc.
gunneyk
New Member
New Member

--
18 Apr 2013 09:46 AM
Those tools may make it a little more user friendly but they simply execute the same tsql commands you would do yourself. There is no other way to shrink an individual Data or Log file than DBCC SHRINKFILE.
You are not authorized to post a reply.

Acceptable Use Policy