By Daniel Sauer
Periodic database maintenance is simply a must to ensure that business systems are tuned for efficiency and readily available during both user-peak times and those seemingly quiet times. To augment normal maintenance plans based on database and system needs, we may want to perform manual tasks…
problem: need to shrink a database and/or files and within the operation you get an error describing a locked file(s)
fix: run the following command to give some further info into what sessions are active
So, after ensuring a client’s development database was configured to run in ‘single-user’ mode, the A-Team began performing a series of required maintenance tasks. When running a command to shrink the database and reorganize the files before releasing unused space, an error occurred as shown.
Not knowing what else could be “using” the database since we had it designated to run in ‘single-user’ mode, the A-Team was quick to run a simple query to determine the who and what…
select log_reuse_wait_desc from sys.databases where name <> ''
To isolate the issue further instead of reporting back the entire database environment, simply change the where condition to a target database name. This will help you determine what has a session open with your database and make sense out of the reported error.
In our case today, it was because of replication. Wait!? The A-Team generally does not have replication enabled on single test/development environments so the question immediately was asked on how and why was this database attached to a replication configuration? Well, anytime you restore a database to a new environment, if replication was configured in the original system, then the database restore process ensures that it is available in the new environment. So in this scenario, the database had been restored in an on-premise, localized development environment from a backup made in an environment where replication was enabled.
So, to proceed with the original plan and knowing that this particular test environment is not requiring replication, simply running the following sql command with a target database name helps to remove the configuration and will allow us to continue on the real tasks at hand: optimizing the database and preparing for that next big thing.
exec sp_removedbreplication 'db_name'