[dba-SQLServer]Rolling back a transaction log?

Djabarov, Robert Robert.Djabarov at usaa.com
Mon Feb 24 10:34:43 CST 2003 

Here's my version of the same idea that I use whenever I need to get rid of connections from a database:

   use master
   go
   if object_id('dbo.sp_KillUsers') is not null
      drop procedure dbo.sp_KillUsers
   go
   create procedure dbo.sp_KillUsers (
      @dbname   varchar(128) )
   as
      declare @spid varchar(10), @print varchar(8000)
      set nocount on
      if @dbname is null begin
         set @print = 'Database name must be specified!' + char(13)+char(10) + 'Operation aborted.'
         raiserror (@print, 15, 1)
         return (1)
      end
   
      if @dbname = db_name() begin
         set @print = 'Executing this procedure from the same database ' +
                      'as the one that you are currently in is not allowed! ' + char(13)+char(10) +
                      'Operation aborted.'
         raiserror (@print, 15, 1)
         return (1)
      end
   
      select @spid = cast(min(spid) as varchar(10)) from sysprocesses (nolock) where dbid = db_id(@dbname)
      while @spid is not null begin
         exec ('kill ' + @spid)
         select @spid = cast(min(spid) as varchar(10))
            from sysprocesses (nolock) 
            where dbid = db_id(@dbname) and spid > cast(@spid as int)
      end
   go
   -- Example: kill all users in pubs
   exec dbo.sp_KillUsers pubs

This way you're not using something as archaic as a  C U R S O R, especially off of a temporary table!!!
______________________________________________________
Robert Djabarov
Certified MS SQL Server DBA
Certified MS SQL Server Programmer
Certified MS VB Programmer
? (210) 913-3148 - phone
( (210) 753-3148 - pager

 -----Original Message-----
From: 	Francisco H Tapia [mailto:my.lists at verizon.net] 
Sent:	Monday, February 24, 2003 9:51 AM
To:	dba-sqlserver at databaseadvisors.com
Subject:	Re: [dba-SQLServer]Rolling back a transaction log?

Found out some GREAT NEWS!, Hope you all had a good weekend... it TURNS out
that when you don't auto-shrink your log files, meaning you set a fixed
lenght and then auto-backup whenever your log file hits 60% there are some
outstanding benefits.... One of which you NEVER loose any data... When you
go and choose to restore your database and you have your full backup
selected (hopefully from last night)  and your logs from during the day,
simply plug in a point in time to restore to and viola!, 15 minutes before I
was alerted was the time I chose and it restored the entire db up until that
point, I kept playing with it pushing less and less time until I found the
point (minute) in time that it was killed.. I did this of course NOT on the
production server as it has already been restored loosing only 2 hrs worth
of data, but instead I did this on my TEST database that I run on my Box.  I
hope this helps you guys out... as an added bonus, I found this great script
at www.SQLSERVERCENTRAL.com, I can't say enough good things about that site,
it is a kick all users out of my DB script.. just drop it into QA or make a
sproc out of it, and run it, it will kick out all  your database users,
which is required in order to restore it.. here it is....(BTW, if you've
never visited the site and you're dealing more and more with SQL you really
ought to stop by, there are articles written here with many times real life
situations taken into consideration... )

DECLARE @dbname varchar(50)

SET @dbname = 'YourDBNAME HERE'


DECLARE @strSQL varchar(255)
PRINT 'Killing Users'
PRINT '-----------------'

CREATE table #tmpUsers(
spid int,
eid int,
status varchar(30),
loginname varchar(50),
hostname varchar(50),
blk int,
dbname varchar(50),
cmd varchar(30))

INSERT INTO #tmpUsers EXEC SP_WHO


DECLARE LoginCursor CURSOR
READ_ONLY
FOR SELECT spid, dbname FROM #tmpUsers WHERE dbname = @dbname

DECLARE @spid varchar(10)
DECLARE @dbname2 varchar(40)
OPEN LoginCursor

FETCH NEXT FROM LoginCursor INTO @spid, @dbname2
WHILE (@@fetch_status <> -1)
BEGIN
 IF (@@fetch_status <> -2)
 BEGIN
 PRINT 'Killing ' + @spid
 SET @strSQL = 'KILL ' + @spid
 EXEC (@strSQL)
 END
 FETCH NEXT FROM LoginCursor INTO  @spid, @dbname2
END

CLOSE LoginCursor
DEALLOCATE LoginCursor

DROP table #tmpUsers
go


-Francisco
http://rcm.netfirms.com
----- Original Message -----
From: "Stuart McLachlan" <stuart at lexacorp.com.pg>
To: <dba-sqlserver at databaseadvisors.com>; <CWortz at tea.state.tx.us>
Sent: Saturday, February 22, 2003 5:12 PM
Subject: RE: [dba-SQLServer]Rolling back a transaction log?


: If he deleted a database, he would have deleted the mirrored version
: as well. The only thing mirroring protects you from is physical failure of
: a drive.
:
: > Francisco,
: >
: > How frequently you backup is dependent on how valuable is the lost data.
: > If you can afford to lose a day's worth of data, or if you can easily
: > recreate the day's worth of data, then you backup daily.  If you can
: > afford to lose an hour's worth of data, or if you can easily recreate
: > the hour's worth of data, then you backup hourly.  If you cannot afford
: > to lose any data, then you mirror your database.
: >
: > Charles Wortz
: > Software Development Division
: > Texas Education Agency
: > 1701 N. Congress Ave
: > Austin, TX 78701-1494
: > 512-463-9493
: > CWortz at tea.state.tx.us
: > (SELECT * FROM users WHERE clue > 0)
: >
: >
: > -----Original Message-----
: > From: Francisco H Tapia [mailto:my.lists at verizon.net]
: > Sent: Friday 2003 Feb 21 13:19
: > To: dba-SQLServer at databaseadvisors.com; sswug-sql2k at topica.com
: > Subject: [dba-SQLServer]Rolling back a transaction log?
: >
: >
: > Well it finally happened... We have a Complaint database that runs in
: > conjunction with a goldmine database.  The Goldmine database tracks
: > outgoing calls made by our company to customers in order to track sales
: > leads and now complaints.  Since 4/1/2002 we have not had a database
: > error or hiccup until now.  The company's official DBA, while working on
: > a development database on the production server (yes, that's right)
: > inadvertently wiped out my database instead of his test one this
: > morning, The users of the Complaint db suddenly began to complaining
: > that there were no records, and upon checking I found this to be the
: > case.  I backup every night, and I have the log file backup when it
: > reaches 60%.  BUT.  I did not have *ANY* protection for the moments
: > before the wipe out.  Initially I panicked about not being able to kick
: > the users out quickly enough... I didn't bother to *remember* that I had
: > a Kill All Users In Db script.  So about 10 minutes later (after kicking
: > all the users out) I restored the database back to last log backup, but
: > that was not good as it had the transactions that wiped out the
: > database.  SO I had to restore to last nights copy officially killing
: > all entries from 10am and prior. :(
: >
: > I've secured my script for killing Active Users in the DB.  And My boss
: > knows *who* wiped out the database, in fact I made sure he knew as soon
: > as it happened ... maybe that's not a good political move, but I'm in
: > charge of the db.  Now the question is... Since the log file is only
: > 1meg long and on average it doesn't backup the log for perhaps every 2
: > to 3 days... (I do make a full backup every night).  I suppose I could
: > manage the backups to include incremental changes every hr, so that as
: > little data is lost?  What do you guys suggest?
: >
: >
: > -Francisco
: > http://rcm.netfirms.com
: > _______________________________________________
: > dba-SQLServer mailing list
: > dba-SQLServer at databaseadvisors.com
: > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
: > http://www.databaseadvisors.com
: >
:
:
: --
: Lexacorp Ltd
: http://www.lexacorp.com.pg
: Information Technology Consultancy, Software Development,System Support.
:
:
:
: _______________________________________________
: dba-SQLServer mailing list
: dba-SQLServer at databaseadvisors.com
: http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
: http://www.databaseadvisors.com
:


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

More information about the dba-SQLServer mailing list