Drop Database - Can it Fail ?

Question

Drop Database - Can it Fail ?

homebrew01

SSC Guru

Points: 55539
More actions
July 28, 2015 at 5:41 am

#302806

I need a foolproof way to run a job that restores a database from server A to server B
Every once in a while, my restore fails, because my attempt to kill connections before the restore doesn't work.
Will this make sure there are no connections, so I can restore :
ALTER DATABASE MyDatabase SET OFFLINE WITH ROLLBACK IMMEDIATE
Or is DROP DATABASE MyDatabase more foolproof ?? What can prevent the DROP DATABASE from working ?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Drop database will fail if there are connections. Plus if you drop the DB, the restore has to go and recreate the files, which may take some time if the log file is large.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

homebrew01 SSC Guru Points: 55539 More actions · Answer 2

I am using RedGate Backup/restore, and have included DISCONNECT_EXISTING, but it does not always work.

My primary concern is reliability of the restore process, with speed secondary.

Lowell SSC Guru Points: 323504 More actions · Answer 3

homebrew i do something very similar as far as restoring an existing database.

i have a SQL job with the steps to handle the takeover and restore as separate steps; this might help:

--step 1 drop the database: performed in db context of UserAnalysis so i keep the connection

IF EXISTS(SELECT * FROM master.sys.databases WHERE name='UserAnalysis')

BEGIN

ALTER DATABASE [UserAnalysis] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

EXEC sp_renamedb 'UserAnalysis','UserAnalysisDropping';

DROP DATABASE [UserAnalysisDropping]

END

--step 2

RESTORE DATABASE [UserAnalysis]

FROM DISK = 'W:\SQLBackup\CFSDWHD_MonthEnd.bak'

WITH RECOVERY,

MOVE 'CFSDWH' TO 'W:\SQLData\UserAnalysis.mdf',

MOVE 'CFSDWH_log' TO 'W:\SQLLogs\UserAnalysis_1.ldf'

--step 3 housekeeping for logical file names

ALTER DATABASE [UserAnalysis] SET RECOVERY SIMPLE WITH NO_WAIT

ALTER DATABASE [UserAnalysis] MODIFY FILE ( NAME = CFSDWH, NEWNAME = UserAnalysis_data ) ;

ALTER DATABASE [UserAnalysis] MODIFY FILE ( NAME = CFSDWH_log, NEWNAME = UserAnalysis_log ) ;

--step 4

DBCC SHRINKFILE (N'UserAnalysis_Data' , 0, TRUNCATEONLY)

DBCC SHRINKFILE (N'UserAnalysis_log' , 0, TRUNCATEONLY)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Lowell (7/28/2015)
DBCC SHRINKFILE (N'UserAnalysis_Data' , 0, TRUNCATEONLY)
DBCC SHRINKFILE (N'UserAnalysis_log' , 0, TRUNCATEONLY)

Wha????

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lowell SSC Guru Points: 323504 More actions · Answer 5

yeah, i know....

it has a purpose, really!

it comes from a big ol data warehouse db that gets copied once a month.; a previous job step deleted a ton of data, since this db, in my case, is supposed to be a static analysis db.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Just bear in mind that TRUNCATEONLY is ignored when shrinking a log file (log records can never be moved within the log), so that may shrink the log to 0.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

homebrew01 SSC Guru Points: 55539 More actions · Answer 7

I noticed this in my log file:

"Msg 6107, Sev 14, State 1, Line 1 : Only user processes can be killed."

So there's some system process every once in a while that I can't kill ??

Will this work if there's some system process connected ?

ALTER DATABASE [UserAnalysis] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

EXEC sp_renamedb 'UserAnalysis','UserAnalysisDropping';

DROP DATABASE [UserAnalysisDropping]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Probably the checkpoint process.

The ALTER might wait for the system process to finish, but it should work.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

homebrew01 SSC Guru Points: 55539 More actions · Answer 9

Any opinion on this method ?

ALTER DATABASE MyDatabase SET OFFLINE WITH ROLLBACK IMMEDIATE

John Perry Newbie Points: 9 More actions · Answer 10

homebrew01 (7/28/2015)
Any opinion on this method ?
ALTER DATABASE MyDatabase SET OFFLINE WITH ROLLBACK IMMEDIATE

Thanks! That worked for me and I was able to kill unkillable the SPIDs <50 and put it into single user mode in my own process.