3

SQL Server 2008 Enterprise (I know! #wink. I am working on a migration plan) production server, in FULL recovery mode. I have a database that is used as a source for SQL replication. Daily Full backups and 2-hours incrementals.

I have a log file that has grown a lot (123Go) due to a one-time operation, and that I can't seem to shrink back down to a more normal level.

  • log_reuse_wait_desc = 'NOTHING'
  • DBCC Loginfo show a single line with the status = 2
  • DBCC SQLPERF(LOGSPACE) shows 0.9% used space
  • sys.sysprocesses WHERE open_tran = 1 shows the SLEEPING reader agents
  • DBCC OPENTRAN shows the oldest non-distributed as (0:0:0)

When I try to run a small increment shrink, DBCC SHRINKFILE (N'X_log', 120360) I get:

Cannot shrink log file 2 (X_log) because requested size is larger than the start of the last logical log file.

What else can I try to get what transaction is blocked in the LOG? I will not switch to SIMPLE, I will not backup to NUL, or truncate the LOG, or break my backup chain unless there are clearly no other solution. All suggestions I found seem to somehow assume it is a viable method to break the chain or reboot the server.

What diagnostic command can I still try at this point? I would very much like to debug that one without resorting to Kill-the-spider-with-a-flamethrower actions.

Thanks

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Philippe
  • 131
  • 4
  • Maybe this could shed some light: [What does it mean when DBCC LOGINFO returns many rows with status=2 and DBCC OPENTRAN returns no active open transactions?](https://social.msdn.microsoft.com/Forums/sqlserver/en-US/1fb51bb5-a09f-4bd6-8880-4f0c15227db5/what-does-it-mean-when-dbcc-loginfo-returns-many-rows-with-status-2-and-dbcc-opentran-returns-no?forum=sqldatabaseengine) - Possible line of interest: "Only a LOG backup will free the VLF's for reuse under FULL recovery" – Peter B Nov 16 '18 at 16:24
  • But taking a transaction log backup truncates the log! If you *do not want* to truncate the log, do you perform transaction log backups? :) Post the results of `DBCC LogInfo;` here, please! It may help diagnosing the issue. – Andrey Nikolov Nov 17 '18 at 08:41
  • @AndreyNikolov I mean I do not want to do log backups or truncates outside of the regular backup schedule. We are using a 3rd party tool to handle backups (CommVault) so I don't want to risk breaking the backup chain. – Philippe Nov 19 '18 at 13:35

0 Answers0