When I use a script to drop a database and reload the database with updated data, I often get an error message saying that the database is in use. I'm the only user. If I stop and restart the MSSQLServer service, the script works fine. I don't always receive the error message, but after I do, the message appears repeatedly until I reboot or stop and restart the service. How can I avoid this error message?

If you're using SQL Server 2000, try executing the following statement to remove all users from the database:

ALTER DATABASE SET ROLLBACK IMMEDIATE

Make sure you aren't logged in to the database and that you haven't issued a USE <db> command to the database you're trying to load.

End of Article


You must log on before posting a comment.

If you don't have a username & password, please register now.

Reader Comments

i could not get this to work. i was in master, and i included the dbname (not master) in the statement and got incorrect syntax near the keyword 'ROLLBACK', same for 'IMMEDIATE'

Doug Fala- October 08, 2001

from Master database I get error message - Server: Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'set'. Server: Msg 102, Level 15, State 1, Line 2 Incorrect syntax near 'immediate'.

Sue Royal- January 25, 2002