Unable to remove t-log file

  • khandelwal_arun

    SSC Eights!

    Points: 829

    Hi,

    I unable to remove t-log file from my db...

    Issue is my t-log file got full which having transaction replication. Now I want either shrink t-log or add new t-log file and remove existing t-log file.

    Shrink is getting failed so I am trying to add and remove exsting t-log file but getting error "Cann't remove primary data/log file"... whenever t-Log file have any file group.

    Can you please help me out to reslove the issue...?

    Thanks!!!

  • Devendra (Dev) Shirbad

    SSC-Forever

    Points: 42488

    You cannot remove a file from the database unless the file has no existing data or transaction log information; the file must be completely empty before it can be removed.

    For More: http://technet.microsoft.com/en-us/library/ms191433.aspx

  • khandelwal_arun

    SSC Eights!

    Points: 829

    SQL Server has been restarted and also issue the checkpoint... means all uncommited transaction should be go in mdf... than do you think data present in t-log file?

    Thanks,

  • Devendra (Dev) Shirbad

    SSC-Forever

    Points: 42488

    Not Necessarily.

    Please go through following articles. It will answer your current (as well as next) questions.

    Troubleshooting a Full Transaction Log

    http://msdn.microsoft.com/en-us/library/ms175495.aspx

    Recover from a full transaction log in a SQL Server database

    http://support.microsoft.com/kb/873235

  • Devendra (Dev) Shirbad

    SSC-Forever

    Points: 42488

    One snippet here, from BOL for your case (Replication & Transaction Log issue).

    Merge replication and snapshot replication do not affect transaction log size, but transactional replication can. If a database includes one or more transactional publications, the log is not truncated until all transactions relevant to the publications have been delivered to the distribution database. If the transaction log is growing too large, and the Log Reader Agent is running on a scheduled basis, consider shortening the interval between runs. Or, set it to run in continuous mode. If it is set to run in continuous mode (the default), ensure that it is running.

  • Gail Shaw

    SSC Guru

    Points: 1004424

    You can't remove the first log file that the database had. If you add other log files you can remove them, but the first one has to stay.

    Why do you want to shrink? What's the situation (in detail please)? What's the size of the log? What's the % used? What's the log_reuse_wait_desc? What's the output of DBCC OPENTRAN? What's the status of the log reader?

    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
  • khandelwal_arun

    SSC Eights!

    Points: 829

    I have 2 TB SAN Drive and now t-log size reach this much size there is no disk space.

    There are no open transaction....

    I stopped replication and other source which creates the transaction...

    Now My issue is there is not disk space available on log drive so I need to create space on this drive.

    Either shrink the t-log file or add and delete the existing t-log file....

    or any other way to create to space...

    But both suivation I didn't perform....

    Thanks,

  • Gail Shaw

    SSC Guru

    Points: 1004424

    GilaMonster (12/20/2011)


    What's the size of the log? What's the % used? What's the log_reuse_wait_desc? What's the output of DBCC OPENTRAN? What's the status of the log reader?

    Not asking for my own amusement...

    Please take a read through this: http://www.sqlservercentral.com/articles/Transaction+Log/72488/

    We can help you, but you're going to have to give us something to work with...

    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
  • khandelwal_arun

    SSC Eights!

    Points: 829

    I just figure out the idea where I can reslove the issue.

    log_reuse_wait_desc = REPLICATION

    Thanks,

  • khandelwal_arun

    SSC Eights!

    Points: 829

    Hello,

    Can you please help me out how I remove log_reuse_wait_desc = Replication to Active_Transcation.

    Thanks in advanace for help me out ...

    Regards,

  • Gail Shaw

    SSC Guru

    Points: 1004424

    Getting there slowly...

    GilaMonster (12/20/2011)


    What's the output of DBCC OPENTRAN? What's the status of the log reader?

    Please take a read through this if you haven't already (I know there are some details in there on replication waits): http://www.sqlservercentral.com/articles/Transaction+Log/72488/

    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
  • Perry Whittle

    SSC Guru

    Points: 233678

    khandelwal_arun (12/20/2011)


    Hello,

    Can you please help me out how I remove log_reuse_wait_desc = Replication to Active_Transcation.

    Thanks in advanace for help me out ...

    Regards,

    Are you taking regular backups of the publication\distribution databases?

    Is the log reader agent running or running successfully?

    Please return the results of the following

    Against the distributor

    select databasepropertyex('distribution', 'IsSyncWithBackup')

    Against the publication database

    select databasepropertyex('your pub db', 'IsSyncWithBackup')

    Have you checked Replication Monitor for furtehr details?

    -----------------------------------------------------------------------------------------------------------

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] 😉

  • khandelwal_arun

    SSC Eights!

    Points: 829

    The issue has been resloved thanks for your prompt help... 🙂

    I really appriciate your prompt and helpful response....

    Thanks,

  • Perry Whittle

    SSC Guru

    Points: 233678

    khandelwal_arun (12/20/2011)


    The issue has been resloved thanks for your prompt help... 🙂

    I really appriciate your prompt and helpful response....

    Thanks,

    Care to share the resolution, it may help others in future

    -----------------------------------------------------------------------------------------------------------

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] 😉

  • khandelwal_arun

    SSC Eights!

    Points: 829

    Add new drive in my system and create new log file on new added drive....

    remove replicaiton ....

    clean distributor log....

    ... shrink log file with empty....

    .... and get it done...

Viewing 15 posts - 1 through 15 (of 16 total)

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