Re: How to PURGE a transaction log?
From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 03/04/04
- Next message: Tibor Karaszi: "Re: Setting up an Alert to monitor DB Logs"
- Previous message: Tibor Karaszi: "Re: Install SQL on Linux"
- In reply to: Square Head: "Re: How to PURGE a transaction log?"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 4 Mar 2004 09:19:25 +0100
To me, purge sounds like "clean out" or "empty". This is what happens when
you do DUMP TRANSACTION. This doesn't shrink the actual file size.
Shrinking the file size is no simple matter in the old architecture. I don't
know of any how-to documents for this. In short:
Make sure you understand backup and restore architecture for 6.5 *really*
well. You don't want to end up with a situation where you can't restore the
database because you missed some point in how backup and restore work.
Backup and restore were not simple and "clean" in the old architecture.
Also, make sure you understand the database architecture regarding database
fragments; the way a database allocate fragments from database devices, and
an allocation can be for data or for log. To be certain, you want to read up
on segments as well, just in case you happen to have user defined segments.
The basic steps are:
DBCC SHRINKDB
Above might deallocate database allocations (un allocation units) from your
database devices. It can only to this from the end of the database, though
(similar to that a file in the new architecture can only shrink from the end
towards the beginning). If you're unlucky, there might be data pages or log
pages at the end. If there are log pages, you might be able to fix this by
doing dummy transactions and DUMP TRANSACTION until the log have moved
elsewhere and then do DBCC SHRINKDB again.
Now, run sp_help_revdatabase. Save the result. You need this info in order
to be able to restore.
Backup the database.
DROP DATABASE
Remove the devices (sp_dropdevice , DELFILE)
Create the devices, as suggested you need in the output from
sp_help_revdatabase above.
Create the database, as generated in the output from sp_help_revdatabase
above.
LOAD DATABASE from the backup.
You need to work a bit with Books Online to find out the concepts and the
name for certain commands/stored procedures. Most of us here haven't worked
with 6.5 for a log time (I haven't since about -99), so we don't remember
every name of commands and stored procedures. And again, if you feel
uncertain about any step above and fell that you are taking a risk with
this, weigh this risk with what you gain with the shrink. Or you might want
to hire someone who have worked a lot with 6.5 and know these steps and have
done this before.
Finally, I think that I got above outline correct, but you never know
(again, it has been a while). Make sure that you verify my thinking...
-- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp "Square Head" <anonymous@discussions.microsoft.com> wrote in message news:65d701c4015d$35cbee00$a401280a@phx.gbl... > Tibor, > > Here is the syntac I used to backup the database and Tlog > and master. > > In Enterprise\Schedule Task--I configured the following to > run a 5am in the morning to backup the database and log: > > DUMP DATABASE MASTER TO MSTRBKUP VOLUME = 'S1005' WITH > NOUNLOAD, STATS = 10, NOINIT, NOSKIP > > DUMP TRANSACTION MYDB TO MYDBBKUP VOLUME = 'S0001', MYLOG > VOLUME = 'S0002' WITH NOUNLOAD, STATS = 10, NOINIT, NOSKIP > > However is this what you consider a PURGE or Backup?? > Would you please walk me throught the steps or direct me > to some HOW TO technical notes. Thanks > > > YES. I would like to clear the transaction log (does that > mean the same as a PURGE?) I did see some notes from > other people on clearing the transaction log but they were > in reference to SQL 7.X. Do you have instructions for SQL > 6.5? If I first backup the databse and T-logs before I > clear (shrink) the log would I still need to be a SQL guru? > > Sorry for some many questions. But I do need help. > > Thanks greatly! > > > > >-----Original Message----- > >Define "purge", please. :-) > > > >You empty the log essentially the same way as in the new > architecture > >(although the command is DUMP TRANSACTION instead of > BACKUP LOG). > >Shrinking the physical file size is another matter, and I > do not recommend > >even try the steps needed to do that unless you are an > expert in the 6.5 > >architecture. > > > >-- > >Tibor Karaszi, SQL Server MVP > >Archive at: > >http://groups.google.com/groups? > oi=djq&as_ugroup=microsoft.public.sqlserver > > > > > >"Square Head" <anonymous@discussions.microsoft.com> wrote > in message > >news:493301c3ff9c$f5b8b5d0$a001280a@phx.gbl... > >> Hello, > >> > >> I have little to nothing experience with MS SQL 6.5. > >> However, I have to learn how to purge the transaction > >> log. I would give anyone a bazillion points if some > could > >> help me with this. > >> > >> Much appreciate! > > > > > >. > >
- Next message: Tibor Karaszi: "Re: Setting up an Alert to monitor DB Logs"
- Previous message: Tibor Karaszi: "Re: Install SQL on Linux"
- In reply to: Square Head: "Re: How to PURGE a transaction log?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
