1

I'm using AdventureWorks database in Simple Recovery Model on SQL Server 2016 Ent, and I'm examining the active VLFs.

Running select * from sys.databases shows ACTIVE_TRANSACTION under log_reuse_wait_desc

but when I run dbcc opentran to figure out which session, i get "No Active Open Transactions"

There does seem to be a background task running, which prevents me from taking the database offline

  Status           DBName           Command  
BACKGROUND    AdventureWorks     XTP_OFFLINE_CKPT

There is also a full-text catalog (which gets crawled regularly I guess?)

How do I find out what transaction is holding up my log VLFs ?

  • Make sure you are running DBCC OPENTRAN against the AdventureWorks database and not accidentally against master or another database. The default for DBCC OPENTRAN is the current database context. – Scott Hodgin Feb 6 '18 at 11:46
2

Because this is AdventureWorks and obviously not a production server, you're probably not going to get great answers here. I'd just restart the server. (Is that the right answer? No, but we're obviously talking about a development playground box here, and I wanna get you across the finish line quickly.)

If you really wanted to troubleshoot it, try sp_WhoIsActive:

sp_WhoIsActive @show_sleeping_spids = 1, @show_system_spids = 1, @show_own_spid = 1

Just because if you're getting started with transaction troubleshooting, I'd rather give you something easier to use than DBCC commands.

1

Because this question is "only" a year and a half old I'll share my experience, which contradicts Brent's. I tried several things to clear this including restarting the server and restoring the database. Nothing works. Right now as we speak I've decided to leave it alone and see if it will finish processing by itself. If after a reasonable period of time I'll try detaching it and deleting the mdf and ldf, THEN restoring from backup. But the transaction log's pointers are probably still going to indicate some transactions that need to be rolled back and go right back to XTP_OFFLINE_CKPT. I'm curious if the OP found a solution.

  • nope.. but i'm no longer a DBA, so :) – d-_-b Sep 21 at 6:47

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.