Sql-server – How to do a one-time log shrink on a database with transactional replication

log-shippingshrinksql serversql-server-2012transaction-log

I have inherited a 10 GB database with an 80 GB log [only 3% in use according to DBCC SQLPERF(logspace)]. It seems safe to assume the extreme log growth was due to problems from long before I was hired.

The primary has a log shipping backup job that runs every 15 minutes. The primary has copy and restore jobs that run every 15 minutes.

When I attempt to shrink the log, I get "Cannot shrink log file 2 (DatabaseName_log) because the logical log file located at the end of the file is in use." I have retried this several times 15 minutes apart and even days apart, but always get the same result.

DBCC LOGINFO shows 784 VLFs, with only the first 245 and the last on with a status of 2. p_WhoIsActive shows that the longest open transaction has been running for less than 2 hours (long transactions are not unusual here due to a third-party Microsoft Access app with ODBC driver issues).

How can I successfully shrink this log (without creating an outage for users)?

Thanks,
Mark

Best Answer

The easiest solution to this issue is to set the database to simple recovery, shrink the log, then set it back to full recovery. In T-SQL this would be:

ALTER DATABASE [database] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE([logfilename], 1)
ALTER DATABASE [database] SET RECOVERY FULL WITH NO_WAIT

Changing the database to simple recovery may cause transactions to stop being replicated, but if scheduled during off hours it should shrink the log and return to full recovery with no side effects.

If that is unacceptable, you can use the sp_repldone stored procedure to manually mark transactions as distributed and reset the replication status of invalid transactions:

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,    @time = 0, @reset = 1
Related Question