April 27, 2009 at 5:31 am
Hi -
I need to import same data from some tables to other tables (in the same database).
This is an OLTP database. I need that during the process (import) nobody is connected to the database besides my app that is importaing this data to the tables.
I have seen this procedure below, that kills all the connections (but not my connection).
And i can then put the database in single user mode:
declare @execSql varchar(1000), @databaseName varchar(100)
-- Set the database name for which to kill the connections
set @databaseName = 'myDatabase'
set @execSql = ''
select @execSql = @execSql + 'kill ' + convert(char(10), spid) + ' '
from master.dbo.sysprocesses
where db_name(dbid) = @databaseName
and
DBID <> 0
and
spid <> @@spid
exec(@execSql)
BUT some people say that i can do in other way, like this:
alter database dbName set single_user with rollback immediate
and then to put the database in it's normal state again, i only need to do the following:
alter database dbName set multi_user with rollback immediate
Can you please explain masters, what does this commands do ??? (this last two)?
tks,
Pedro
April 27, 2009 at 7:10 am
the 'with rollback immediate ' is tellin the database engine to alter the state of the database despite people being connected to it (without this clause it would fail if people were connected).
What it does is pretty much what kill statement you also had does, it terminates any connections to the database and rolls back any open transactions, so the database is left in a consistent state.
---------------------------------------------------------------------
April 27, 2009 at 7:38 am
Thank you very much for the reply. I undestood.
What about the stored procedure? while it is killing the processes, it will do the rollback of any begin transaction? as the "with roollback" do?
Other question that i have is:
How can i lock tables using t-sql? I what to lock some table of the database, but not the entire database to other users (single user mode).
How can i do this?
tks,
Pedro
April 27, 2009 at 9:03 am
Thank you very much for the reply. I undestood.
What about the stored procedure? while it is killing the processes, it will do the rollback of any begin transaction? as the "with roollback" do?
Other question that i have is:
How can i lock tables using t-sql? I what to lock some table of the database, but not the entire database to other users (single user mode).
How can i do this?
tks,
Pedro
April 27, 2009 at 9:13 am
yes, kill will rollback the transaction. Proof if that is Use kill with the statusonly clause will tell you how rollback is progressing if it takes a while.
as for locking the tables, investigate the with tablock hint.
---------------------------------------------------------------------
April 27, 2009 at 9:19 am
sorry, didn't undestood very well the:
" yes, kill will rollback the transaction. Proof if that is Use kill with the statusonly clause will tell you how rollback is progressing if it takes a while.
"
April 27, 2009 at 9:20 am
not sure why you'd want to lock a table up, but I would do it with a transaction.
one of the HINTs you can use is to exclusively lock a table:
From Query Window #1:
begin transaction
--noone can even select from this table until you commit or rollback your transaction, unless they use the hint NOLOCK
select * from YOURTABLE with (TABLOCKX)
--do more work....
From a Second Query Window:
--waits for the transaction to complete:
select * from YOURTABLE
From a Third Query Window:
--able to read the info fromt he table due to the hint
select * from YOURTABLE with (NOLOCK)
don't forget to rollback or commit your transaction in window one!!!!
Lowell
April 27, 2009 at 12:52 pm
george sibbald (4/27/2009)
yes, kill will rollback the transaction. Proof if that is Use kill with the statusonly clause will tell you how rollback is progressing if it takes a while.as for locking the tables, investigate the with tablock hint.
kill command does rollback transactions. The connection can take a while to rollback if it was a large transaction. You can check on its progress with the command kill spid with statusonly.
Be aware what you are doing is quite harsh and the app will return an error to the users.
---------------------------------------------------------------------
April 28, 2009 at 3:44 am
Ok , thank you very much.
Other question that i have is this:
I'm selecting like below, to know wich APPs are connected to my database. BUT i am having dificulties.
Can you help please?
I am doing like this:
select * from master.dbo.sysprocesses
where db_name(dbid) ='My_Database_Name' and dbid 0
and spid@@spid
But this query does not return the machines that are connected to my database.....
can you please help? thank you very much once again.
April 28, 2009 at 4:10 am
so long as you enter the correct database name that code should work fine.
---------------------------------------------------------------------
April 29, 2009 at 4:01 am
thanks. You where right.
September 11, 2014 at 3:24 am
If you know the database id ,Instead of giving dbid<>0 just give your database ID then check it will display all the details.....
Regards
Chowdary...
Regards
Chowdary...
September 11, 2014 at 3:24 am
If you know the database id ,Instead of giving dbid<>0 just give your database ID then check it will display all the details.....
Regards
Chowdary...
Regards
Chowdary...
September 11, 2014 at 3:28 am
Please note: 5 year old thread.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply