Submit a ticketCall us

Have You Auto Renewed? If not, you're missing out.
The SolarWinds Renewal Program comes with a host of benefits including the most recent product updates, 24/7 technical support, virtual instructor-led training and more. Experience all of this with the convenience of Auto Renewal, and never worry about missing any of these great benefits. Learn More.

Home > Success Center > Network Performance Monitor (NPM) > Taking SQL DB offline takes a long time in SQL Management Studio

Taking SQL DB offline takes a long time in SQL Management Studio

Updated June 16, 2017

Overview

Taking a SQL Server database offline using the following query in SQL Management Studio takes a long time:

ALTER DATABASE <dbname> SET OFFLINE WITH ROLLBACK IMMEDIATE

The following error is displayed:

ALTER DATABASE failed because a lock could not be placed on database 'dbname' Try again later.

Environment

All NPM versions using MS-SQL as DB

Cause 

MSSQL is unable to close open connections.

 

Resolution

  1. Run the following:
    EXEC sp_who2
  2. Locate the database in the results, and locate the SPID for that connection.
  3. Run the following:
    KILL <SPID>
Disclaimer: Please note, any content posted herein is provided as a suggestion or recommendation to you for your internal use. This is not part of the SolarWinds software or documentation that you purchased from SolarWinds, and the information set forth herein may come from third parties. Your organization should internally review and assess to what extent, if any, such custom scripts or recommendations will be incorporated into your environment.  You elect to use third-party content at your own risk, and you will be solely responsible for the incorporation of the same, if any.

 

Last modified

Tags

Classifications

Public