database backup not clearing the transaction log...

Hi
I have a 150Gb 'time series' type database running in SQL 2000. every sunday
@. 0230 it re-indexes using 'dbcc dbreindex'. this takes several hours and
creates a big fat transaction log.
At 0030 Monday morning the database is backed up using
BACKUP DATABASE [timeseries] TO [Backup_timeseries] WITH INIT , NOUNLOAD ,
NAME = N'timeseries backup - init BAK file', NOSKIP , STATS = 10,
NOFORMAT
which DOES NOT CLEAR THE SIZE OF THE TRANSACTION LOG. The trans log is only
cleared at 0800 Monday morning when the trans log is backed up (and is
continually backed up every 10 minutes throughout the working day all week
until Saturday) using the following syntax:
surely the database backup at 0030 should clear down the size of the trans
log?

> which DOES NOT CLEAR THE SIZE OF THE TRANSACTION LOG. The trans log is
> only
> cleared at 0800 Monday morning when the trans log is backed up (and is
> continually backed up every 10 minutes throughout the working day all week
> until Saturday) using the following syntax:
Sure , it does not. You need to BACKUP LOG file in order to truncate
inactive portions in the LOG
"Methodology" <Methodology@.discussions.microsoft.com> wrote in message
news:11993BB5-D220-4AFF-99D1-F0C7FAE045F6@.microsoft.com...
> Hi
> I have a 150Gb 'time series' type database running in SQL 2000. every
> sunday
> @. 0230 it re-indexes using 'dbcc dbreindex'. this takes several hours and
> creates a big fat transaction log.
> At 0030 Monday morning the database is backed up using
> BACKUP DATABASE [timeseries] TO [Backup_timeseries] WITH INIT , NOUNLOAD
> ,
> NAME = N'timeseries backup - init BAK file', NOSKIP , STATS = 10,
> NOFORMAT
> which DOES NOT CLEAR THE SIZE OF THE TRANSACTION LOG. The trans log is
> only
> cleared at 0800 Monday morning when the trans log is backed up (and is
> continually backed up every 10 minutes throughout the working day all week
> until Saturday) using the following syntax:
> surely the database backup at 0030 should clear down the size of the trans
> log?
>
|||Methodology wrote:
> Hi
> I have a 150Gb 'time series' type database running in SQL 2000. every sunday
> @. 0230 it re-indexes using 'dbcc dbreindex'. this takes several hours and
> creates a big fat transaction log.
> At 0030 Monday morning the database is backed up using
> BACKUP DATABASE [timeseries] TO [Backup_timeseries] WITH INIT , NOUNLOAD ,
> NAME = N'timeseries backup - init BAK file', NOSKIP , STATS = 10,
> NOFORMAT
> which DOES NOT CLEAR THE SIZE OF THE TRANSACTION LOG. The trans log is only
> cleared at 0800 Monday morning when the trans log is backed up (and is
> continually backed up every 10 minutes throughout the working day all week
> until Saturday) using the following syntax:
> surely the database backup at 0030 should clear down the size of the trans
> log?
>
A database backup DOES NOT, repeat, DOES NOT truncate (flush out) the
transaction log, only a log backup will do that (assuming Full or
Bulk-Logged modes). Also, truncation != SHRINKING. The physical size
of the log file will not change when truncating. Shrinking is a
separate process, and there are many reasons why you don't want to do it.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Here is an idea. I did this once at one job I had. I had a similarlarly
large sized databases whose transaction logs "blew out" on a reindex
(weekly). I had a lot of ugly clustered indexes that had to be rebuilt
and took a lot of space. SInce the database was not heavily used during
that period, I did an alter database and put the db on simple mode.
Then it won't blow out the log. I did the re-index on all necessary
tables and then did a full backup. Note, this is dangerous as you break
the transaction log sequence at that time, so you must do an immediate
full backup. In my case, this was ok as it was a mainly Mon-Sat db.
KR
Tracy McKibben wrote:
> Methodology wrote:
> A database backup DOES NOT, repeat, DOES NOT truncate (flush out) the
> transaction log, only a log backup will do that (assuming Full or
> Bulk-Logged modes). Also, truncation != SHRINKING. The physical size
> of the log file will not change when truncating. Shrinking is a
> separate process, and there are many reasons why you don't want to do it.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
sql