SQL Server Error 5030

Error number

5030

Severity

16 (user-correctable error)

State

5 (ALTER DATABASE ... COLLATE, reproduced); 2 seen once with MODIFY NAME

Product

Microsoft SQL Server Database Engine

Versions

All supported SQL Server versions (2016 to 2025), Azure SQL Managed Instance

Category

Database State & Recovery

Last reviewed

24 October 2026

What it means

Error 5030 means an ALTER DATABASE change that needs the database to itself, most often a collation change, could not get exclusive access because other sessions are still connected to the database.

Changing the default collation of a database rewrites metadata that every connected session depends on, so ALTER DATABASE ... COLLATE asks for an exclusive lock on the database. Every session whose current database it is holds a shared lock, even when idle. In our lab on SQL Server 2025 the ALTER waited about 20 seconds for that lock, then failed with 5030 state 5 and added 5072, which names the collation it could not set. With no other sessions connected, the same statement succeeded at once.

The message names the database but not the sessions in the way. They are usually ordinary connections: an SSMS query window or Object Explorer, an application pool, a job. Your own session counts too if its current database is the one you are changing, so run the change from master. Renames are less strict: in our lab MODIFY NAME worked while another session sat idle in the database, and gave 5030 (state 2) only once, while that session was still connecting.

Common causes

• Other sessions are connected to the database: query windows, Object Explorer, applications with connection pools, reports or jobs.
• Your own session is still in the database (the query window was opened on it, or the script ran USE AppDb earlier).
• A query running in another database uses this one through a three-part name and holds a lock on it.
• Tools that reconnect quickly (monitoring, IntelliSense in another window) take a connection again between attempts.

Diagnose it

Read-only These queries only read metadata and dynamic management views. They do not change data, permissions or configuration, so they are safe to run first.

SELECT session_id, login_name, host_name, program_name, status, last_request_end_time
FROM sys.dm_exec_sessions
WHERE database_id = DB_ID(N'AppDb');

SELECT request_session_id, request_mode, request_status
FROM sys.dm_tran_locks
WHERE resource_type = 'DATABASE'
  AND resource_database_id = DB_ID(N'AppDb');

SELECT name, user_access_desc, state_desc, collation_name
FROM sys.databases
WHERE name = N'AppDb';

How to fix it

Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.

USE master;
ALTER DATABASE AppDb COLLATE Latin1_General_CS_AS;

USE master;
ALTER DATABASE AppDb SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE AppDb COLLATE Latin1_General_CS_AS;
ALTER DATABASE AppDb SET MULTI_USER;

Examples

Changing the collation while another session is connected

Two sessions at once. The ALTER waited about 20 seconds for session 1 before it failed.

-- Session 1 is connected to AppDb (for example an open query window).
-- Session 2:
USE master;
ALTER DATABASE AppDb COLLATE Latin1_General_CS_AS;

Msg 5030, Level 16, State 5, Line 4
The database AppDb could not be exclusively locked to perform the operation.
Msg 5072, Level 16, State 1, Line 4
ALTER DATABASE failed. The default collation of database 'AppDb' cannot be set to Latin1_General_CS_AS.

How to prevent it

Plan collation changes and other database-wide ALTERs for a maintenance window with applications and jobs stopped, and run them from a session connected to master. Check sys.dm_exec_sessions just before.

Choose the collation when the database is created, which avoids a later change that needs exclusive access and can affect every text comparison.

Common questions

Learn more on CodeWithSQL

• Transactions, locks and deadlocks

Official documentation

• Set or change the database collation
• Rename a database
• Set a database to single-user mode
• Database engine errors 5000 to 5999

Last technically reviewed 24 October 2026; examples and diagnostics run on SQL Server 2025 (17.0.1145.4). Message text checked against the engine's sys.messages catalogue.