SQL Server Performance Forum – Threads Archive
95% done with log shipping, help get me to 100%!
Ok I am sooo close I can feel it! I have been trying to troubleshoot this myself for days now.. and I need help.. im a bit of an sql noob so pardon my ignorance at times. Background: sql standard 2000 db. Boss wants it redundant. No money to pay for enterprise. I then look for cheap alternative. I come across this site a few weeks ago, woohooo a cheap solution! ive bought a secondary box, got a windows 2k license and a MSSQL 2k license. I then use this guide to get me started:http://www.sql-server-performance.com/sql_server_log_shipping.asp great log shipping guide, simple to follow etc (so thanks whoever wrote it) – but unfortunatly I cant get it all working correctly! Ok my problem is this: Ive setup the stored procedures and their corresponding jobs exactly as the guide says. I fire up the restore DB job, it truncates, backups up, copies to standby server then tries to restore. Error: RESTORE DATABASE successfully processed 97065 pages in 130.917 seconds (6.073 MB/sec). [SQLSTATE 01000] (Message 3014) Database options single user and dbo use only cannot be set at the same time. [SQLSTATE 42000] (Error 5066). The step failed. After looking at the restore database stored procedure it has these two lines in it:
WITH DBO_ONLY,
and
EXEC sp_dboption ‘mydatabase’, ‘single user’, true dbo_only: sets db to ‘dbo use only’ after it is restore, to help keep people out of database after restore
exec: puts the restored db in ‘single user mode’ to prevent problems when restoring again as users might be connected to db (the above is the explanation from the guide) Im a little confused, as these 2 lines appear to be doing the same thing, ie preventing users from connecting to my standby database. Plus, my error message seems to think there is a conflict as well. does anyone have an idea whats going on here? Shall I be removing either the dbo_only entry, or alternatively removing the sp_dboption line? any help is GREATLY appreciatted cheers
dave
ok ive found someone else on this forum with the same problem: http://www.sql-server-performance.c…d dbo use only cannot be set at the same time
i’ll try the changes and see how I go!
doh, the link to the script to kill all users in a db is dead <img src=’/community/emoticons/emotion-6.gif’ alt=’
‘ /><br /><br /<a target="_blank" href=http://www.admin911.com/downloads/usp_KillUsers.sql>http://www.admin911.com/downloads/usp_KillUsers.sql</a><br /><br />anyone else able to give me a script or let me know how to do it?<br /><br />
ok found another site with a script: <br /><br /><br /><br />CREATE proc rp_kill_db_processes<br />(@dbname varchar(20))<br />as<br /><br />Declare @dbid int,<br /> @spid int,<br /> @str nvarchar(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /><br />select @dbid = dbid from master..sysdatabases<br /> where name = @dbname<br />declare spidcurs cursor for<br /> select spid from master..sysprocesses where dbid = @dbid<br />open spidcurs<br />fetch next from spidcurs into @spid<br />While @@fetch_status = 0<br /> Begin<br /> Select @str = ‘Kill ‘+convert(nvarchar(30),@spid)<br /> exec(@str)<br /> –print @str<br /> fetch next from spidcurs into @spid<br />End<br />Deallocate spidcurs<br />GO<br /><br />Usage:<br /><br />exec rp_kill_db_processes test<br /><br />**where test is the name of your database.
a bit less sophisticated way to kick all users out of the database is: ALTER DATABASE dbname SET READ_ONLY WITH ROLLBACK IMMEDIATE
]]>