Francisco H Tapia
my.lists at verizon.net
Mon Feb 24 12:07:42 CST 2003
This is a cleaner route, thanks for posting it :D
-Francisco
http://rcm.netfirms.com
----- Original Message -----
From: "Djabarov, Robert" <Robert.Djabarov at usaa.com>
To: <dba-sqlserver at databaseadvisors.com>
Sent: Monday, February 24, 2003 8:34 AM
Subject: RE: [dba-SQLServer]Rolling back a transaction log?
: 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
:
:
:
: _______________________________________________
: dba-SQLServer mailing list
: dba-SQLServer at databaseadvisors.com
: http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
: http://www.databaseadvisors.com
: