locked
Mirroring - Transaction Log File Size Problem RRS feed

  • Question

  • Hi everyone ,

    first of all i'll introduce myself, this is my first post ever in this website, which was really helpfull for other topics .

    i am a system engineer who found himself in a "Forced" DBA position , without entering lot in details, i have this problem and i need some Help if possible

    I have a production SQl server environment , with Two Databases all mirrored 

    When i discovered the environment, i found that the Transaction Log File for one of the Databases have reached 103Gb , while its Database Size is 84Gb.

    I already found a  maintenance plan that backup the Tlog more frequently , i then tried to shrink the File but it give me just 300 Mb as a free space (0%), i did some digging and found that because o f a mirroring problem the Tlog was somehow locked , i looked in the secondary server(mirrored one) and found that the disk partition containing the mirrored Tlog was full .

    is it because of the replication/mirroring problem that the Tlog in the main server is getting bigger ?

    thank you in advance for your help , and sorry my english isn't my main/native language.

    Saturday, February 10, 2018 7:51 PM

All replies

  • Hi everyone ,

    first of all i'll introduce myself, this is my first post ever in this website, which was really helpfull for other topics .

    i am a system engineer who found himself in a "Forced" DBA position , without entering lot in details, i have this problem and i need some Help if possible

    I have a production SQl server environment , with Two Databases all mirrored 

    When i discovered the environment, i found that the Transaction Log File for one of the Databases have reached 103Gb , while its Database Size is 84Gb.

    I already found a  maintenance plan that backup the Tlog more frequently , i then tried to shrink the File but it give me just 300 Mb as a free space (0%), i did some digging and found that because o f a mirroring problem the Tlog was somehow locked , i looked in the secondary server(mirrored one) and found that the disk partition containing the mirrored Tlog was full .

    is it because of the replication/mirroring problem that the Tlog in the main server is getting bigger ?

    thank you in advance for your help , and sorry my english isn't my main/native language.

    Good day,

    >> first of all i'll introduce myself, this is my first post ever in this website

    Great to meet you and welcome to the forums :-)

    >> Now let's move to the end - from the official mirroring documentation

    This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use Always On availability groups instead.

    It might be a good time to think about re-design the system and get ride of the mirroring.

    >> i then tried to shrink the File

    Wow :-(

    this is probably a big mistake. In most cases there is no reason and it s not a good idea to manually shrink the log file. There is a reason it growth! At lease not before you found the reason that it growth.

    >> Log File for one of the Databases have reached 103Gb , while its Database Size is 84Gb

    Log file bigger then the database is not common but does not necessarily mean that there is an issue. It is something for alert.

    * First, determine whether the database requires a full or simple recovery model.

    >> i did some digging and found that because of a mirroring problem the Tlog was somehow locked 

    Mirroring is based on the transaction log. The log records remain active until the principal server receives notification from the mirror server that the record has been written on the mirror server. If the mirror server falls behind the principal server then the log file grows accordingly.

    There are several reasons SQL Server might report when it cannot truncate the transaction log, which results in a growing log file. Execute the below query and give us the report:

    select Log_reuse_wait_desc,name,* 
    from sys.databases

    in extreme case that the mirror server falls behind the principal server you can close the gap by stooping database mirroring, take a log backup that truncates the log, apply that log backup to the mirror database WITH NORECOVERY, and restart mirroring.

    >> i looked in the secondary server(mirrored one) and found that the disk partition containing the mirrored Tlog was full

    And this probably the source of your issue, which cause the mirror server falls behind the principal server, as I explained above :-)

    ** Of course this is all theoretical assumptions and guessing according to the theory and your description...


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]



    • Edited by pituachMVP Sunday, February 11, 2018 7:38 AM
    • Proposed as answer by Teige Gao Monday, February 26, 2018 2:46 AM
    Sunday, February 11, 2018 7:36 AM
  • If the database log file grown out of control, you can shrink log files with DBCC SHRINKFILE in the principal database . And then the command will be mirrored from the principal to the mirrored database

    USE yourdb

    go

    DBCC SHRINKFILE (2, 100);


    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

    • Proposed as answer by Teige Gao Monday, February 26, 2018 2:46 AM
    Sunday, February 11, 2018 7:49 AM