Не удалось выполнить инструкцию alter database

EXEC sp_who2

KILL <SPID>

5

1

0

I know this is an old post but I recently ran into a very similar problem. Unfortunately I wasn’t able to use any of the alter database commands because an exclusive lock couldn’t be placed. But I was never able to find an open connection to the db. I eventually had to forcefully delete the health state of the database to force it into a restoring state instead of in recovery.

In rare cases (e.g., after a heavy transaction is commited) a running CHECKPOINT system process holding a FILE lock on the database file prevents transition to MULTI_USER mode.

• Remove From My Forums

Answers

• it works, the problem was the in the authentification mode, when i use SA account it works nicely.

  thx

  • Marked as answer by
    Nissanna
    Wednesday, January 13, 2016 10:28 AM

All replies

• Nissanna,

  This error occurs because a lock could not be placed in database.

  Try to use the statement below to disconnect all users from the database before:

  ALTER DATABASE [DBName] SET SINGLE_USER WITH ROLLBACK IMMEDIATTE

  ---

  Felipe Lauffer MCSA: SQL Server | MCP

• Can you show us the query ?

  What are you trying to do with alter database command ?

  From where are you running this command , from SSMS or from something else

  ---

  Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it
  

  My Wiki Articles
  

  MVP

• Nissanna,

  This error occurs because a lock could not be placed in database.

  Try to use the statement below to disconnect all users from the database before:

  ALTER DATABASE [DBName] SET SINGLE_USER WITH ROLLBACK IMMEDIATTE

  What are you suggesting would you do the same on your production database which is running . Let us first see what OP’s problem is Alter database can be used to do lot of things .

  The above command could be very dangerous if applied out of context

  ---

  Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it
  

  My Wiki Articles
  

  MVP

• Same problem, i get the same error in this statement 

• I’m running this script: 

  USE [master]
  GO    
  declare @Path nvarchar(512)
  SELECT @Path=SUBSTRING(physical_name, 1, CHARINDEX(N’master.mdf’, LOWER(physical_name)) — 1) 
         FROM master.sys.master_files
         WHERE database_id = 1 AND file_id = 1

   
  declare @dbname varchar(100)
  set @dbname = ‘dbname’

  DECLARE @Query VARCHAR(MAX)=»
  SET @Query = @Query + ‘CREATE DATABASE ‘+@dbName +’ ON  PRIMARY ‘
  SET @Query = @Query + ‘( NAME = »’+@dbName +»’, FILENAME = N»’+@path+@dbName +’.mdf» , SIZE = 6144KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) ‘
  SET @Query = @Query + ‘ LOG ON ‘
  SET @Query = @Query + ‘( NAME = »’+@dbName +’_log», FILENAME = N»’+@path+@dbName +’_log.ldf» , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)’

   
  exec (@Query)

  ALTER DATABASE [dbname] SET COMPATIBILITY_LEVEL = 100
  GO  —>here the pb appears
  IF (1 = FULLTEXTSERVICEPROPERTY(‘IsFullTextInstalled’))
  begin
  EXEC [dbname].[dbo].[sp_fulltext_database] @action = ‘enable’
  end
  GO
  ALTER DATABASE [dbname] SET ANSI_NULL_DEFAULT OFF 
  GO
  ALTER DATABASE [dbname] SET ANSI_NULLS OFF 
  GO
  ALTER DATABASE [dbname] SET ANSI_PADDING OFF 
  GO
  ALTER DATABASE [dbname] SET ANSI_WARNINGS OFF 
  GO
  ALTER DATABASE [dbname] SET ARITHABORT OFF 
  GO
  ALTER DATABASE [dbname] SET AUTO_CLOSE OFF 
  GO
  ALTER DATABASE [dbname] SET AUTO_CREATE_STATISTICS ON 
  GO
  ALTER DATABASE [dbname] SET AUTO_SHRINK OFF 
  GO
  ALTER DATABASE [dbname] SET AUTO_UPDATE_STATISTICS ON 
  GO
  ALTER DATABASE [dbname] SET CURSOR_CLOSE_ON_COMMIT OFF 
  GO
  ALTER DATABASE [dbname] SET CURSOR_DEFAULT  GLOBAL 
  GO
  ALTER DATABASE [dbname] SET CONCAT_NULL_YIELDS_NULL OFF 
  GO
  ALTER DATABASE [dbname] SET NUMERIC_ROUNDABORT OFF 
  GO
  ALTER DATABASE [dbname] SET QUOTED_IDENTIFIER OFF 
  GO
  ALTER DATABASE [dbname] SET RECURSIVE_TRIGGERS OFF 
  GO
  ALTER DATABASE [dbname] SET  DISABLE_BROKER 
  GO
  ALTER DATABASE [dbname] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
  GO
  ALTER DATABASE [dbname] SET DATE_CORRELATION_OPTIMIZATION OFF 
  GO
  ALTER DATABASE [dbname] SET TRUSTWORTHY OFF 
  GO
  ALTER DATABASE [dbname] SET ALLOW_SNAPSHOT_ISOLATION OFF 
  GO
  ALTER DATABASE [dbname] SET PARAMETERIZATION SIMPLE 
  GO

• The script doesn’t contain errors.

  Are you sure you have SqlServer 2008 or higher?

  Maybe you’re running your script on SqlServer 2005.

  Hope this help

  ---

  Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

• now i’m using sql server express 2008 sp1, i think it’s a problem of user permission 

• Of course requires ALTER permission on the database.

  Do you have it?

  ---

  Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

• i know, of course, but i’m still getting the pb 

• i know, of course, but i’m still getting the pb 

  Can you just run below part of query and see if database is created

  USE [master]
   GO    
   declare @Path nvarchar(512)
   SELECT @Path=SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1) 
          FROM master.sys.master_files
          WHERE database_id = 1 AND file_id = 1
    
   declare @dbname varchar(100)
   set @dbname = 'dbname'
   
  DECLARE @Query VARCHAR(MAX)=''
   SET @Query = @Query + 'CREATE DATABASE '+@dbName +' ON  PRIMARY '
   SET @Query = @Query + '( NAME = '''+@dbName +''', FILENAME = N'''+@path+@dbName +'.mdf'' , SIZE = 6144KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) '
   SET @Query = @Query + ' LOG ON '
   SET @Query = @Query + '( NAME = '''+@dbName +'_log'', FILENAME = N'''+@path+@dbName +'_log.ldf'' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)'
    
   exec (@Query)
   

  After this select new query in database and then run rest of the script

  ---

  Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it
  

  My Wiki Articles
  

  MVP

  • Proposed as answer by
    Ice Fan
    Wednesday, January 13, 2016 10:19 AM

• But the database
  is successfully created?

  ---

  Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

• YEs, the database is successfully created with this part 

  USE [master]
   GO    
   declare @Path nvarchar(512)
   SELECT @Path=SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1) 
          FROM master.sys.master_files
          WHERE database_id = 1 AND file_id = 1
    
   declare @dbname varchar(100)
   set @dbname = 'dbname'
   
  DECLARE @Query VARCHAR(MAX)=''
   SET @Query = @Query + 'CREATE DATABASE '+@dbName +' ON  PRIMARY '
   SET @Query = @Query + '( NAME = '''+@dbName +''', FILENAME = N'''+@path+@dbName +'.mdf'' , SIZE = 6144KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) '
   SET @Query = @Query + ' LOG ON '
   SET @Query = @Query + '( NAME = '''+@dbName +'_log'', FILENAME = N'''+@path+@dbName +'_log.ldf'' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)'
    
   exec (@Query)
   

• i don’t why, but it works for me with cmd line. i didn’t add any thing.

  thx 

• Did you tried my approach and did it worked ?

  ---

  Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it
  

  My Wiki Articles
  

  MVP

• in fact, i’m using this script in installshield 2010, it doesn’t create the database, thought when i run just the script on the cmd line or in SSMS it works nicely, i think that installshield doesn’t execute the EXEC statement. 

• You can put your code in a file (sqlscript.sql) and after that
  Try this with the command prompt (CMD.EXE):
  osql.exe /U username /P password /S server /d database /i sqlscript.sql /o sqlscript.output
  

  ---

  Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

• it works, the problem was the in the authentification mode, when i use SA account it works nicely.

  thx

  • Marked as answer by
    Nissanna
    Wednesday, January 13, 2016 10:28 AM

• Hi Nissanna,

  Glad to hear that the issue is resolved. Thanks for your sharing, other community members could benefit from your solution.

  Thanks,
  Ice fan

  ---

  Ice Fan
  TechNet Community Support