Submit a ticketCall us

Get a crash course on Network Monitoring delivered right to your inbox
This free 7-day email course provides a primer to the philosophy, theory, and fundamental concepts involved in IT monitoring. Lessons will explain not only how to perform various monitoring tasks, but why and when you should use them. Sign up now.

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
17:44, 15 Jun 2017

Tags

Classifications

Public