locked
SQL Server 2012 RRS feed

  • Question

  • I am using SQl Server 2012 Database Mirroring with around 40 gb as mdf and 1 gb as ldf. Now my ldf size increased . How to reduce ldf size while mirror enabled with mirror server and witness server. Can shrink the ldf with mirror enables.
    Sunday, June 14, 2015 7:50 AM

Answers

All replies

  • How you configure Mirroring ?
    (1) High Performance (asynchronous).
    (2) High safety without automatic failover (synchronous).
    (3) High safety with automatic failover(synchronous) need withness server.

    Database files participating in mirroring can’t be shirnked by using truncate_only option with Backup log command.

    To shrink Log file of database  participating in mirroring, Backup the log file at any location (can be a local drive or network location)

     BackupLog <DatabaseName> to disk=‘D:\ff\dbname.trn’

    Lets check if all transaction is written to disk.

    DBCC LOGINFO(‘database name’)

    Transaction which are written on disk will have status=0

    In case if last transaction show status=2 then backup the database log once again.

    Now shrink the file

    DBCC SHRINKFILE(Logfileid)

    or

    DBCC SHRINKFILE(Filename,minsize)

     The file will shrink to its maximum extent.

    Refer


    Please Mark Answer if it solved your issue, Vote As Helpful if it helps to solve your issue


    • Edited by AV111 Sunday, June 14, 2015 12:14 PM
    Sunday, June 14, 2015 12:13 PM
  • >>>>How to reduce ldf size while mirror enabled with mirror server and witness server

    Perform backup log file (on regular basic) and then shrink the log file.


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, June 14, 2015 12:26 PM
  • As Uri said you have to perform regular log backup but dont shrink daily you can shrink ONCE to reclaim space.

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Sunday, June 14, 2015 2:00 PM