June 29, 2010 at 2:01 pm
HI all,
I am facing an issue when i logged in a server and use a database in query analyzer
i see during that time my restore job is failing in log shipping.
I searched that it does not have exclusive access in the database.
To give exclusive access
Alter database sr2 set single_user with roleback immediate
But dont know where at this query will i run.
Will i run it creating a step in the same job ?
If i do not connect wit database in the secaondary instance th restore job always succeded .
Can soem one help ?
Mushfiq
June 29, 2010 at 2:51 pm
If you are connected to the database restore jobs will fail as they require exclusive access, so make sure you are not connected to the database when the restore jobs run.
In logshipping there is an option to kill any connections to the database as part of the restore process.
---------------------------------------------------------------------
June 29, 2010 at 3:48 pm
Below standby mode that terminate user in (recommended) is already selected in add destination server
in log shipping plan.
It is selected during the setup.
I do not want to close database or stop using standby database.
I want to be connected and same time log shipped to this database
that has been told to me.
But is that restore option is set to only for single user or soemthing?
PLz need help
June 29, 2010 at 3:50 pm
Beside that where is actually that kill connection in database while restore this option i will get?
Thank you
June 29, 2010 at 3:52 pm
you (or anyone else) cannot be connected to the database at the same time as a restore is happening.
you will have to amend the restore job run times to outside the time periods people want to use the database.
No way round that I am afraid.
---------------------------------------------------------------------
June 29, 2010 at 4:11 pm
http://msdn.microsoft.com/en-us/library/ms189572%28v=SQL.100%29.aspx
George is correct, users are disconnected from a STANDBY database when it is being restore. You can kill connections, or let them pile up until all users are gone and the restores will start.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 29, 2010 at 5:06 pm
As mentioned others, First kill all the connections to restoring database in the first step and second step start the restoration.
I have developed a script for my purpose, first create the following KillSpids in any of your database
then call the script in your first step of your job some thing like this
exec KillSpids 'Database Name'
Script
------
USE [dba]
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[KillSpids]
@pDbName varchar (100)=null, /*database where we will kill processes.
If ALL-we will attempt to kill processes in all DBs*/
@pUserName varchar (100)=NULL /*user in a GIVEN database or in all databases where such a user name exists,
whose processes we are going to kill. If NULL-kill all processes. */
/*Purpose: Kills all processes in a given database and/or belonging to a specified user.
If no parameters are supplied it will attempt to kill all user processes on the server.
Database: DBA
*/
AS
SET NOCOUNT ON
DECLARE @p_id smallint
DECLARE @dbid smallint
DECLARE @dbname varchar(100)
DECLARE @exec_str varchar (255)
DECLARE @error_str varchar (255)
DECLARE @loginame nchar(128)
select @pDbName = lower(IsNull(@pDbName,''))
if @pDbName = ''
begin
print ''
print '*** Error: @pDbName must be either valid database name or "all" to kill processes in all databases ***'
print ''
Return -1
end
IF NOT EXISTS (SELECT * FROM master.dbo.sysdatabases where name=ltrim(rtrim(@pDbName)) AND @pDbName <> 'all' )
BEGIN
Set @error_str='No database '+ltrim(rtrim(@pDbName)) +' found.'
Raiserror(@error_str, 16,1)
RETURN-1
END
Create Table ##DbUsers(dbid smallint,uid smallint)
If @pUserName is not null
BEGIN
--Search for a user in all databases or a given one
DECLARE curDbUsers CURSOR FOR
SELECT dbid,name FROM master.dbo.sysdatabases where name=ltrim(rtrim(@pDbName)) or @pDbName = 'all'
OPEN curDbUsers
FETCH NEXT FROM curDbUsers INTO @dbid,@dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @exec_str='Set quoted_identifier off
INSERT ##DbUsers SELECT '+cast(@dbid as char)+', uid FROM '+@dbname+'.dbo.sysusers
WHERE name="'+ltrim(rtrim(@pUserName))+'"'
EXEC (@exec_str)
FETCH NEXT FROM curDbUsers INTO @dbid,@dbname
END
CLOSE curDbUsers
DEALLOCATE curDbUsers
If not exists(Select * from ##DbUsers)
BEGIN
Set @error_str='No user '+ltrim(rtrim(@pUserName)) +' found.'
DROP TABLE ##DbUsers
Raiserror(@error_str, 16,1)
RETURN-1
END
END
ELSE --IF @pUserName is null
BEGIN
INSERT ##DbUsers SELECT ISNULL(db_id(ltrim(rtrim(@pDbName))),-911),-911
END
--select * from ##dbUsers
DECLARE curAllProc CURSOR FOR
SELECT sp.spid,sp.dbid,sp.loginame FROM master.dbo.sysprocesses sp
INNER JOIN ##DbUsers t ON (sp.dbid = t.dbid or t.dbid=-911) and (sp.uid=t.uid or t.uid=-911)
OPEN curAllProc
FETCH NEXT FROM curAllProc INTO @p_id, @dbid, @loginame
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @exec_str = 'KILL '+ Convert(varchar,@p_id)+ ' checkpoint'
SELECT @error_str = 'Attempting to kill process '+Convert(varchar,@p_id)+ ' (' + @loginame + ') in database '+db_name(@dbid)
RAISERROR (@error_str,10,1)with log
EXEC (@exec_str)
FETCH NEXT FROM curAllProc INTO @p_id, @dbid, @loginame
END
CLOSE curAllProc
DEALLOCATE curAllProc
DROP TABLE ##DbUsers
SET NOCOUNT OFF
print ''
print 'Done killing processes.......!'
print ''
Hope this will help.
Regards
Hema.,
Regards
Hema.,
June 29, 2010 at 5:20 pm
sakibd2k (6/29/2010)
Beside that where is actually that kill connection in database while restore this option i will get?Thank you
its a checkbox in the restore job tab of the logshipping wizard.
---------------------------------------------------------------------
June 30, 2010 at 10:04 am
Thanks Hema.
YOUR script actually succeeded me what i want .
It worked
Mushfiq
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply