Alter DATABASE failed because a lock could not be placed on database

Active connections: Friends or foes?

When active connections fiercely guard database, they become a major roadblock for ALTER DATABASE operations.

Active connections and you: Spotting potential threats

Before charging in, let's identify the opponents:

• Run sp_who2 to unveil active sessions:

EXEC sp_who2;


• Filter results to display only your database's connections.

Taking on active foes: Disconnecting users wisely

Terminate sessions without sparking a revolt:

• Weigh the consequences of WITH ROLLBACK IMMEDIATE. It rolls back ongoing transactions, use it wisely!
• Go for a subtle approach with KILL <SPID> to terminate sessions individually.
• Don't forget to put on your sysadmin or dbcreator hat, as executing these commands requires elevated privileges!

Win wars before they start: Averting new connections

Once the battleground is clear, prevent reinforcement:

• Avoid engaging with the database until your operation is complete.
• Maintain the database in SINGLE_USER mode throughout the ALTER DATABASE operation.

Nailing down your victory: Conquering underlying issues

Recurring lock issues raise the flag for other systemic issues:

The Might of Authorization: Check User Privileges

Ensure your account is armed with necessary user privileges and roles.

The Unseen Foe: Transaction Log Growth

Unchecked transaction log growth can hold your database hostage. Regular log backups keep this enemy at bay.

The Slow and Steady: Long-running Queries

Long-running queries can lock your victory away by blocking ALTER DATABASE operations:

• Unleash the SQL Profiler to spot and analyze stalemate transactions.
• Trim the fat and speed up those queries: Optimize indexes or remodel queries.

Preemptive strategies and tricks up your sleeve

Pre-emptive Troubleshooting

Look before you leap:

• Review settings like lock timeout to avoid jumping into a trap.
• Schedule renovation projects during off-peak hours for minimal disruptions.

Always be Monitoring

Keep a close eye on your kingdom:

• Deploy monitoring tools to get an early heads-up about high lock contention or suspicious activities.
• Perform regular health checks to prevent unnecessary battles.

Emergency measures: Plan B

If peaceful negotiations fail:

• A SQL Server restart could be your last resort.
• Consider setting camp at a new site: Create and switch to a new database if the battle tends to linger at your current location.