Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Thursday, June 20, 2019 12:36 PM
I attempted to shrink and reorganize a bunch of databases on my test system. Many came up with:
Cannot shrink log file 2 (Database_log) because the logical log file located at the end of the file is in use.
Research showed to check:
SELECT Name, Database_id, recovery_model_desc, log_reuse_wait_desc
from sys.databases
That comes out with:
Database, 16, SIMPLE, ACTIVE_TRANSACTION
That says the database has active transactions. OK, not sure who's using it, check Activity Monitor. Database not open by any process.
OK, more research. DBCC OPENTRAN will show open transactions. The answer is none.
I did find a few more items to check, such as:
SELECT *
FROM sys.dm_tran_database_transactions WHERE database_id = db_id('DATABASE')
Still no transactions.
So, somehow, sys.databases appears to have the log_reuse_wait_desc locked. Now, these are restores of live databases that had transactions when backed up. It appears that flag has stayed locked, but I haven't found the magic search terms to find how to clear the lock.
Thanks in advance for your help,
Terry
Thursday, June 20, 2019 12:44 PM
Are you running b restores?
Are the databases participating in Replication?
Can you run CHECKPOINT command and try shrink the log again?
Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/
MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence
Thursday, June 20, 2019 1:38 PM
Not sure what your question about b restores is... Over time, field databases get backed up and restored. (All database should be SIMPLE in the field.) On the system we restore, 10 of the 25 databases are listed as ACTIVE_TRANSACTION. Backups are generally just maintenance plan backups (usually with compression) of active databases. Restore is just going through, selecting Restore Database, selecting the file under Device, and pressing OK.
Interesting. With the CHECKPOINT 30 added, it successfully shrunk every database and log. log_reuse_wait_desc is still listed as ACTIVE_TRANSACTION!
Looks like the log_reuse_wait_desc is just stalled at ACTIVE_TRANSACTION even if there isn't an active transaction, although with the checkpoint command, shrinks can happen.
Not sure if this is a "fix" or not. It is a fix to allow the database to be shrunk but I'm not sure about a fix on a database that's "stuck" in ACTIVE_TRANSACTION.
Terry
Thursday, June 20, 2019 1:44 PM
<<<<Now, these are restores of live databases that had transactions when backed up
<<<Not sure what your question about b restores is..
<<<log_reuse_wait_desc is still listed as ACTIVE_TRANSACTION!
Try running again and it will disappear , otherwise there is another active transaction
Run dbcc loginfo(dbid). Do you see ta the bottom status column =2? If you do not then probably there is no active transaction and all committed data is flush=shed to the disk
Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/
MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence