SQL Server Performance

95% done with log shipping, help get me to 100%!

Discussion in 'SQL Server Log Shipping' started by dhammond, May 30, 2005.

  1. dhammond New Member

    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
  2. dhammond New Member

  3. dhammond New Member

    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 />
  4. dhammond New Member

    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.
  5. Dimonytch New Member

    a bit less sophisticated way to kick all users out of the database is:

    ALTER DATABASE dbname SET READ_ONLY WITH ROLLBACK IMMEDIATE

Share This Page