Truncate / Shrink Log for Mirrored Databse

Question

Post reply

Truncate / Shrink Log for Mirrored Databse

jpattard

SSC Veteran

Points: 215
More actions
November 13, 2013 at 2:53 am

#395810

Hello,
I have a production database which is mirrored with witness. Unfortunately the log is growing huge.
MDF file is nearly 15GB and the LDF file is nearly 75GB
The DB is in full recovery mode because of mirroring. There are no problems between the Principal and Mirror (always in sync)
Both file sizes are the same on live and mirror server.
I have a maintenance plan to rebuild and re-organise indexes every Sunday.
Also a Daily full backup (on tape) of the database is scheduled.
How can I safely shrink the huge LDF file as it's eating up ally the server storage?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

Are you running regular log backups? With that size of log file, I suspect not.

Please take a read through this: http://qa.sqlservercentral.com/articles/Administration/64582/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jpattard SSC Veteran Points: 215 More actions · Answer 2

I have did manual backup of the Log file (not regular) but still did not shrink.

Should it shrink if i setup daily T-Log backups even if in mirrored scenario like mine?

SELECT file_id, name, type_desc, physical_name, size, max_size

FROM sys.database_files ;

Migrate_logLOGC:\Program Files\...\DATA\Migrate_log.ldf9303672268435456

Actual size on disk is 75GB 🙁

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

Log backups don't shrink the log. They just mark it as reusable.

Please read the article I referenced.

Then, set up regular log backups and do a once-off (not regular, not scheduled) shrink of the log to a reasonable size (not 0).

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jpattard SSC Veteran Points: 215 More actions · Answer 4

Very Good article indeed!

I will do some testing on a TEST Mirror environment.

Will create a Maint Plan for Log Backup and then will shrink the file.

[highlight=#ffff11]DBCC SHRINKFILE (N'Migrate_log' , 1024)[/highlight]

This will be done in a mirror environment and will check the actual file size after the process is complete.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 5

Don't shrink to too small a size. Is 1GB enough for the regular operations on your database? If not, don't shrink that small.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jpattard SSC Veteran Points: 215 More actions · Answer 6

I did a T-Log Back and Shrink in my Test environment Here are the Details:

T-Log before Backup 17,943,808 KB

T-Log After Backup 17,943,808 KB

Backup File: 4,188,116 KB

After shrink using (DBCC SHRINKFILE (N'Migrate_log' , 1024))

The log file remained same size 🙁

jpattard SSC Veteran Points: 215 More actions · Answer 7

OK Manged to do it on the Test environment!

I had to run more than once the T-Log backup plan, before the shrink worked!

Thanks for all your help

SQLAli Ten Centuries Points: 1079 More actions · Answer 8

Gila,

If we shrink the log file of Priniscipal database, does the mirror log gets shrink?

Thanks,
I’m nobody but still I’m somebody to someone………….