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