2

I have a 225GB DB with a 390GB log file, which has only recently exploded in size. I am trying to shrink it - per these instructions: https://www.brentozar.com/blitz/transaction-log-larger-than-data-file/

I am not able to Shrink it on SSMS or by the DBCC SHRINKFILE command. I tried waiting until it was not in use, and also tried a Log backup then running the DBCC SHRINKFILE again, but to no avail. Any ideas on what I can do?

I saw in this article (https://www.mssqltips.com/sqlservertutorial/3311/how-to-shrink-the-transaction-log/) that it says I could try flipping the recovery model over to Simple, run the shrink, then flip it back to Full, but that sounds risky to me so I am cautious to try that.

Also worth noting is that my database is Mirroring and currently stuck in a Suspended state.

Wondering if you guys could lend any advice. We are your typical DBA-less shop, so I appreciate any novice-conscience pointers or suggestions! Thanks!

  • Changing the recovery model won’t be possible with mirroring. You would have to break the mirror and reestablish. You can try right after a full backup and then a transaction backup. Also make sure you aren’t trying to shrink it below what is still marked as in use as it won’t work – Aaron Aug 18 at 1:51
  • 4
    I'm assuming this is the mirror primary? You won't be able to shrink until the mirror issue is resolved or you break the mirror. The primary is holding onto the transaction logs until the secondary is available to receive new transactions again. – Jonathan Fite Aug 18 at 2:04
  • Thanks for your responses. That makes sense. So when you say "break the mirror" - is that done just by selecting "remove mirroring"? And is there any harm in doing this? – Brian B Aug 18 at 2:09
  • Brian, if you break the mirroring, then it will need to be re-established (assuming it's still required). You can try issuing a resume command and see if that resolves the issue. (from primary) ALTER DATABASE <name> SET PARTNER RESUME. Then use the database mirror monitoring tool to see if it's processing them. – Jonathan Fite Aug 19 at 1:28
  • @BrianB If your mirroring is not working now, the only risk to disabling it, is that you will need to get it working again, which you have to do anyway. So no real risk that you don't already have. – James Jenkins Aug 19 at 11:28
3

Your log_reuse_wait_desc is database_mirroring and you mentioned it currently stuck in a Suspended state

select name, log_reuse_wait_desc from sys.databases

You should find a cause your mirroring was suspended. Try to search messages that include Database mirroring has been suspended in ERRORLOG file on the principal. It could be like one below:

2019-08-13 11:40:48.98 spid64s Error: 1453, Severity: 16, State: 1.

2019-08-13 11:40:48.98 spid64s 'TCP://xxx.com:5022', the remote mirroring partner for database 'DB', encountered error 5149, status 3, severity 25. Database mirroring has been suspended. Resolve the error on the remote server and resume mirroring, or remove mirroring and re-establish the mirror server instance.

Using error number you are able to get its description from sys.messages:

select * from sys.messages where message_id = 5149

This case:

MODIFY FILE encountered operating system error %ls while attempting to expand the physical file '%ls'.

That means there is not enough physical space to expand a file on the mirror.

After solving any found errors you have to turn on your mirroring. Run on the principal:

ALTER DATABASE YourDBName SET PARTNER RESUME;

In case you won't be able to find any related errors in ERRORLOG, just try to resume your mirroring using a command above and then look after the database and check your ERRORLOG for errors once the mirrored database goes to the suspended state (if it does).

  • Thanks so much! This was the exact error I found and was able to add more space and all synced properly without needing to break the mirror, then sure enough the log file cleared out and was able to be shrunk after the next log backup. Thanks for all your help! – Brian B Aug 23 at 21:57
1

Right click the database select Reports -> Standard Reports -> Disk Usage to check transaction log file is empty or full. If it is full, run following command to check what it is waiting for.

select name,log_reuse_wait_desc from sys.databases
  • Thank you. It's showing 100% full and the wait reason is "database_mirroring". I've confirmed that the Mirror and Witness servers are both online and accessible, but the Mirror was not mirroring for a couple weeks. Is the best bet to "Remove Mirroring" then try to reestablish? Is there any risk in doing this? – Brian B Aug 19 at 0:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.