SQL Server Performance Forum – Threads Archive

Setting up shipping fails.

hi, my first post here… i read through the well written articlehttp://www.sql-server-performance.com/sql_server_log_shipping.asp a couple of times, and decided to implement it. the trans_log truncate, backup, and copy steps are working. It seems the restoring part it self is OK, but the command EXEC sp_dboption â€˜database#%92, â€˜single user#%92, true is failing, with the error : â€œ…RESTORE DATABASE successfully processed… Database options single user and dbo use only cannot be set at the same time…â€. The full error log is: Executed as user: DOMAINusername. Processed 1 pages for database â€˜database#%92, file â€˜database_Log#%92 on file 1. [SQLSTATE 01000] (Message 4035) Processed 23368 pages for database â€˜database#%92, file â€˜database_Data#%92 on file 1. [SQLSTATE 01000] (Message 4035) RESTORE DATABASE successfully processed 23369 pages in 12.312 seconds (15.548 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. Then I tried to remove the â€˜read only#%92 value of the db, but it fails with error â€œdata base is a warm standby, a warm standby is read-only, alter database failed#%92. when i view the DB in EM, it is marked as "DBO use only/Read-Only". But another point, why keep the DB in single user mode? Why not giving access to all, and when running the restore command, first kill all the connections to it, and do the restore? If this script puts it in single user, will it not block developers from querying the database? any help will do ! thanks
Hugh

In SQL Server 2000, a database cannot be in single-user mode with dbo use only. Instead, the following alternative options are available by using the ALTER DATABASE command:
ALTER DATABASE database SET SINGLE_USER.
This command restricts access to the database to only one user at a time. During the restore, the specified database must not be in use. Any data in the specified database is replaced by the restored data. Refer to this Q&A linkhttp://www.sql-server-performance.com/q&a37.asp to kill all users while restoreing the logs, and during the log restore you must refrain users querying against the database. Normally restore of log will be completed very fast and may not take longer than expected. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

hi satya, i’ve dropped the ‘dbo only’ part of the restore, and allow connections to the db. before a data or log restore, i kill all connections to db, and all is running fine. thanks a lot! im just concerned about something else – when doing the data backup, the first step is : BACKUP LOG databaseName WITH TRUNCATE_ONLY WAITFOR DELAY ’00:00:05′ before running this step, my .ldf file is 200mb. after error-free completion, it is still on 200mb. should it not truncate this file? thanks a lot hugh

BACKUP LOG to backup transaction log will backup the active portion of transaction log and truncates for next turn of transactions. This truncation does not reduce the size of physical log file, it reduces the size of logical file. To shrink the physical file you must use DBCC SHRINKFILE and books online is best resource to know more about it. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>