Removing lock via SQL script

Locked objects occur when a developer, consultant, or system process opens an object in the Object Designer, runs a compilation, or starts an operation that places a lock. In a production environment, this often happens unintentionally. During an upgrade, however, this becomes disastrous: the upgrade tools cannot modify, convert, or delete objects while they are locked. This blog post is about removing lock via SQL script.

When you perform an upgrade of a Dynamics NAV or Dynamics 365 Business Central database that still runs on C/AL objects, you expect the process to go smoothly. But anyone who works regularly with NAV databases knows that this is not always the case. One of the most frustrating causes of a failed upgrade is the presence of locked objects in the database. Fortunately, there is a SQL script that is about removing lock via SQL script.

That’s why experienced NAV specialists always use two simple but powerful SQL scripts to detect and unlock locked objects before starting the upgrade.

This blog explains:

• Why locked objects occur
• How they block an upgrade
• What the SELECT query does
• What the UPDATE query does
• Why these scripts are safe
• How to use them in an upgrade workflow
• Best practices for NAV/BC upgrades

What are locked objects in NAV/Business Central?

Dynamics NAV (and early versions of Business Central) use a classic C/AL object structure. Objects such as tables, pages, reports, and codeunits are stored in the dbo.Object table. When a developer opens an object in the Object Designer, NAV places a lock on that object.

That lock has a purpose: it prevents two people from editing the same object simultaneously. But during an upgrade, that lock becomes a problem. The upgrade tools need to:

• Convert objects
• Rewrite metadata
• Recompile objects
• Delete or replace objects

If even one object is locked, the entire upgrade can stop.

That’s why it’s crucial to check for locked objects beforehand.

The first script: Detecting locked objects

It starts with a SELECT statement that displays all locked objects. The filter [Locked] = 1 means the object is actively locked.

This shows the following:

• Type (Table, Page, Report, Codeunit, etc.)
• ID
• Name
• Locked (0 or 1)
• Locked By (the user who placed the lock)
• Date and time of the lock

This script is ideal for quickly identifying who or what caused the lock. Often, you’ll see that a developer left an object open, or that a background process placed a lock.

sql

USE [Your Database Name]
GO

SELECT
    [Type],
    [ID],
    [Name],
    [Locked],
    [Locked By],
    [Date],
    [Time]
FROM [dbo].[Object]
WHERE [Locked] = 1
ORDER BY [Type], [ID];

It’s an essential part of every upgrade checklist.

The second script: Removing lock via SQL script

The second script is an UPDATE statement that unlocks all locked objects by setting [Locked] = 0.

This script is safe because:

• It only changes the lock status
• It doesn’t delete or modify objects
• It doesn’t alter metadata
• It doesn’t trigger compilation

It simply performs what NAV itself would do when a user closes an object.

USE [Your Database Name]
GO

UPDATE [dbo].[Object]
SET
    [Locked] = 0
WHERE [Locked] = 1;

During an upgrade, this script is often necessary because NAV doesn’t provide an interface to remove locks when the Object Designer isn’t available.

Why removing lock via SQL script result in a successful upgrade

An upgrade of NAV or Business Central with C/AL objects involves several steps:

1. Copy the database
2. Convert objects
3. Upgrade metadata
4. Run compilation
5. Convert extensions (if applicable)
6. Migrate data

If even one object is locked, step 2 or 3 can fail completely. The upgrade then stops with errors such as:

• Object is locked by another user
• Cannot modify object during upgrade
• Metadata update failed due to locked object

By running the SELECT query first, you immediately see if there are problems. Then, by running the UPDATE query, you remove all locks at once.

This prevents hours of troubleshooting and failed upgrade runs.

How to use removing lock via SQL script in an upgrade workflow

Here’s a practical workflow used by NAV consultants worldwide:

1. Stop all NAV services Prevents background processes from placing new locks.
2. Run the SELECT query Check which objects are locked.
3. Analyze the cause Sometimes a developer is still logged in; sometimes a process like NAS placed a lock.
4. Run the UPDATE query Unlock all objects.
5. Run the SELECT query again Confirm that all locks are gone.
6. Start the upgrade Now the upgrade tools can freely modify all objects.

Best practices for NAV/BC upgrades using SQL lock removal

Proven recommendations:

• Always use a pre‑upgrade checklist
• Always check for locked objects
• Stop all services before starting the upgrade
• Run the scripts on a copy of the database
• Document who is responsible for object management
• Automate the SELECT query in your upgrade pipeline
• Combine this with an Upgrade strategy
• Consider migrating to AL objects to avoid locks entirely

Conclusion: Small scripts, big impact

In conclusion, these two SQL scripts may be small, but they prevent major problems. They ensure your upgrade doesn’t stall due to locked objects and give you full control over your NAV database.

Anyone who regularly performs upgrades knows that these checks make the difference between:

• An upgrade that succeeds on the first try
• An upgrade that fails for hours due to invisible locks

By including these scripts in your standard workflow, you’ll work faster, safer, and more professionally. Fortunately, there’s a SQL script that describes exactly how to remove a lock.

For more information about SQL Server, click the link. If you have questions about this blog post, contact me via the contact form.