0

we have a stored proc where we rebuild indexes and also take Backup of filegroups.Following is part of the code. But this job is failing since few weeks for error message : Error : Executing the query "EXEC DoBackupsRebuildIndexes 'AttDW..." failed with the following error: "Database 'AttDW' is already open and can only have one user at a time.

**I tried to kill users connected but still getting same message. I am clueless. *****Any inputs appreciated to get DB to single user mode and not run in to errors.*******

Error : Executing the query "EXEC DoBackupsRebuildIndexes 'AttDW..." failed with the following error: "Database 'AttDW' is already open and can only have one user at a time.

Part of code:

IF @testmode=0 
BEGIN

Exec [dbo].[usp_KillAllUsers]  @dbname='AttDW' 
If Exists (SELECT 1 FROM #FGToBackup WHERE rowid > 1)

-- rowID 1 is not set RO so no need for single user if we are only backing up the most recent FG
ALTER DATABASE AttDW SET SINGLE_USER WITH ROLLBACK IMMEDIATE
END

DECLARE @SQL  varchar(200)
DECLARE @rowNum INT

DECLARE FG_cursor CURSOR FOR
SELECT ROW_NUMBER() OVER (ORDER BY PartitionNumber DESC) AS ROWID,FileGroupName FROM maintenance.FGToBackup
OPEN FG_cursor
FETCH NEXT FROM FG_cursor INTO @rowNum,@FGName
WHILE (@@FETCH_STATUS <> -1)
BEGIN
   IF (@@FETCH_STATUS <> -2)
   BEGIN 
     INSERT RptsStgLive.dbo.SSIS_LOG VALUES ('DailyLoad','Backup',getdate(),'Processing FileGroup: ' + @FGName)
--print @FGName
-- Database must be in Single User mode to set Filegroups READ_ONLY

IF @rowNum <> 1
BEGIN
Exec [dbo].[usp_KillAllUsers]  @dbname='AttDW'  

SET @SQL='alter database AttDW modify filegroup ' + @FGName + ' read_only'
PRINT @SQL
IF @testmode=0 EXEC (@SQL)
-- NOW DO FINAL BACKUP
SET @fullBackupPath = @BackupPath + '\' + @DBName + '\' + @FGName + '.bak'
IF @testmode=0 BACKUP DATABASE AttDW FILEGROUP = @FGName TO DISK = @fullBackupPath WITH CHECKSUM,INIT
IF @testmode=1 PRINT 'BACKUP DATABASE AttDW FILEGROUP = ' + @FGName + ' TO DISK = ' + @fullBackupPath + ' WITH CHECKSUM,INIT'
END
  • I'm not sure the usp_KillAllUsers would wait until the connections were killed to proceed to the next step or if it would issue the kill commands and return leaving the rollbacks executing. If the latter happens, it could explain why you get the error of another connection open when the script runs: it didn't have time to properly kill the connections. Why don't you add two steps in your job: 1st save in a table all the connections that were open before the job step of rebuild and backup; 2nd do the same again and you'll be able to find out what connection remained open depite the kill. – Ronaldo Oct 16 at 19:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.