Reducing the size of the Transaction Log.

We have a SQL Server 2000 SP3 database with recovery model set to full. I
set up 3 automatic backup jobs as follows:
Each day,Mon thru Fri at 9:00 am
1 step - full backup of database to file A with overwrite
Each day,Mon thru Fri every 30 minutes,between 9:30 am and 5:00 pm
1 step - transaction log backup of database to file A with append
Each day,Mon thru Fri at 5:05 pm
1st step - transaction log backup of database to file A with append
2nd step - execute BACKUP LOG WITH NO_LOG
3rd step - execute DBCC SHRINK (db_file_name,1)
First I tried to shrink the Log file without issuing the "BACKUP LOG WITH
NO_LOG" statement , but DBCC SHRINK doesn't shrink the file as I want, even
though I back up the Log.
When I use BACKUP LOG WITH NO_LOG everything seems to be working OK.
However, I get an error in the Event Viever(Application Log) stating
"Database Log truncated".
My question is:
Should I worry about this error ?
Is any data loss likely will this arrangement ?
After all, I truncate the Transaction Log on purpose, right after backing it
back in the previous step.
Regards,
SezginYou should not use the no_log command if you require point in time recovery,
and if you do not require point in time recovery, set your recovery mode to
simple. I would advise not to be shrinking your log or data file like you
are as if the db or log has to autogrow it costs you at the point it needs
to allocate more space and that is typically when your db is busy.
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Sezgin Rafet" <srafet@.hotmail.com> wrote in message
news:OUaTekvxDHA.2520@.TK2MSFTNGP10.phx.gbl...
> We have a SQL Server 2000 SP3 database with recovery model set to full. I
> set up 3 automatic backup jobs as follows:
> Each day,Mon thru Fri at 9:00 am
> 1 step - full backup of database to file A with overwrite
> Each day,Mon thru Fri every 30 minutes,between 9:30 am and 5:00 pm
> 1 step - transaction log backup of database to file A with append
> Each day,Mon thru Fri at 5:05 pm
> 1st step - transaction log backup of database to file A with append
> 2nd step - execute BACKUP LOG WITH NO_LOG
> 3rd step - execute DBCC SHRINK (db_file_name,1)
> First I tried to shrink the Log file without issuing the "BACKUP LOG WITH
> NO_LOG" statement , but DBCC SHRINK doesn't shrink the file as I want,
even
> though I back up the Log.
>
> When I use BACKUP LOG WITH NO_LOG everything seems to be working OK.
> However, I get an error in the Event Viever(Application Log) stating
> "Database Log truncated".
> My question is:
> Should I worry about this error ?
> Is any data loss likely will this arrangement ?
> After all, I truncate the Transaction Log on purpose, right after backing
it
> back in the previous step.
> Regards,
> Sezgin
>
>|||Thanks for your reply Ray,
The database has a high number of transactions every day. I have to make
sure any possible data loss is minimized. That is why I used full recovery
model. During tests, in about 5 weeks time, the Transaction Log grew up to
nearly 7 GB (the data file currently is just over 1 GB). I tried my best to
shrink the Transaction Log without using the "Backup Log with no_log"
command, but to no success. Could you offer any advice or alternative
solution.
Best regards,
Sezgin Rafet
"Ray Higdon" <rayhigdon@.higdonconsulting.com> wrote in message
news:%23L6$G5vxDHA.1740@.TK2MSFTNGP09.phx.gbl...
> You should not use the no_log command if you require point in time
recovery,
> and if you do not require point in time recovery, set your recovery mode
to
> simple. I would advise not to be shrinking your log or data file like you
> are as if the db or log has to autogrow it costs you at the point it needs
> to allocate more space and that is typically when your db is busy.
> HTH
> --
> Ray Higdon MCSE, MCDBA, CCNA
> --
> "Sezgin Rafet" <srafet@.hotmail.com> wrote in message
> news:OUaTekvxDHA.2520@.TK2MSFTNGP10.phx.gbl...
> > We have a SQL Server 2000 SP3 database with recovery model set to full.
I
> > set up 3 automatic backup jobs as follows:
> >
> > Each day,Mon thru Fri at 9:00 am
> > 1 step - full backup of database to file A with overwrite
> >
> > Each day,Mon thru Fri every 30 minutes,between 9:30 am and 5:00 pm
> > 1 step - transaction log backup of database to file A with append
> >
> > Each day,Mon thru Fri at 5:05 pm
> > 1st step - transaction log backup of database to file A with append
> > 2nd step - execute BACKUP LOG WITH NO_LOG
> > 3rd step - execute DBCC SHRINK (db_file_name,1)
> >
> > First I tried to shrink the Log file without issuing the "BACKUP LOG
WITH
> > NO_LOG" statement , but DBCC SHRINK doesn't shrink the file as I want,
> even
> > though I back up the Log.
> >
> >
> > When I use BACKUP LOG WITH NO_LOG everything seems to be working OK.
> > However, I get an error in the Event Viever(Application Log) stating
> > "Database Log truncated".
> >
> > My question is:
> > Should I worry about this error ?
> > Is any data loss likely will this arrangement ?
> >
> > After all, I truncate the Transaction Log on purpose, right after
backing
> it
> > back in the previous step.
> >
> > Regards,
> >
> > Sezgin
> >
> >
> >
> >
>|||THe log should eventually shrink, and you should NOT be using backup log
with no_log...
I noticed however that you said you were using dbcc shrink...
The proper command is dbcc shrinkfile, or dbcc shrikdatabase... Could that
be part of the problem?
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sezgin Rafet" <srafet@.hotmail.com> wrote in message
news:uvz5lIGyDHA.2308@.TK2MSFTNGP11.phx.gbl...
> Thanks for your reply Ray,
>
> The database has a high number of transactions every day. I have to make
> sure any possible data loss is minimized. That is why I used full recovery
> model. During tests, in about 5 weeks time, the Transaction Log grew up to
> nearly 7 GB (the data file currently is just over 1 GB). I tried my best
to
> shrink the Transaction Log without using the "Backup Log with no_log"
> command, but to no success. Could you offer any advice or alternative
> solution.
>
> Best regards,
>
> Sezgin Rafet
>
> "Ray Higdon" <rayhigdon@.higdonconsulting.com> wrote in message
> news:%23L6$G5vxDHA.1740@.TK2MSFTNGP09.phx.gbl...
> > You should not use the no_log command if you require point in time
> recovery,
> > and if you do not require point in time recovery, set your recovery mode
> to
> > simple. I would advise not to be shrinking your log or data file like
you
> > are as if the db or log has to autogrow it costs you at the point it
needs
> > to allocate more space and that is typically when your db is busy.
> >
> > HTH
> >
> > --
> > Ray Higdon MCSE, MCDBA, CCNA
> > --
> > "Sezgin Rafet" <srafet@.hotmail.com> wrote in message
> > news:OUaTekvxDHA.2520@.TK2MSFTNGP10.phx.gbl...
> > > We have a SQL Server 2000 SP3 database with recovery model set to
full.
> I
> > > set up 3 automatic backup jobs as follows:
> > >
> > > Each day,Mon thru Fri at 9:00 am
> > > 1 step - full backup of database to file A with overwrite
> > >
> > > Each day,Mon thru Fri every 30 minutes,between 9:30 am and 5:00 pm
> > > 1 step - transaction log backup of database to file A with append
> > >
> > > Each day,Mon thru Fri at 5:05 pm
> > > 1st step - transaction log backup of database to file A with append
> > > 2nd step - execute BACKUP LOG WITH NO_LOG
> > > 3rd step - execute DBCC SHRINK (db_file_name,1)
> > >
> > > First I tried to shrink the Log file without issuing the "BACKUP LOG
> WITH
> > > NO_LOG" statement , but DBCC SHRINK doesn't shrink the file as I want,
> > even
> > > though I back up the Log.
> > >
> > >
> > > When I use BACKUP LOG WITH NO_LOG everything seems to be working OK.
> > > However, I get an error in the Event Viever(Application Log) stating
> > > "Database Log truncated".
> > >
> > > My question is:
> > > Should I worry about this error ?
> > > Is any data loss likely will this arrangement ?
> > >
> > > After all, I truncate the Transaction Log on purpose, right after
> backing
> > it
> > > back in the previous step.
> > >
> > > Regards,
> > >
> > > Sezgin
> > >
> > >
> > >
> > >
> >
> >
>|||Thanks Wayne,
I used DBCC SHRINKFILE(db_log_file_name,1) , just have mistyped it in the
post.
Sezgin Rafet
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:%23G0pjgIyDHA.2440@.TK2MSFTNGP12.phx.gbl...
> THe log should eventually shrink, and you should NOT be using backup log
> with no_log...
> I noticed however that you said you were using dbcc shrink...
> The proper command is dbcc shrinkfile, or dbcc shrikdatabase... Could that
> be part of the problem?
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "Sezgin Rafet" <srafet@.hotmail.com> wrote in message
> news:uvz5lIGyDHA.2308@.TK2MSFTNGP11.phx.gbl...
> > Thanks for your reply Ray,
> >
> >
> >
> > The database has a high number of transactions every day. I have to make
> > sure any possible data loss is minimized. That is why I used full
recovery
> > model. During tests, in about 5 weeks time, the Transaction Log grew up
to
> > nearly 7 GB (the data file currently is just over 1 GB). I tried my best
> to
> > shrink the Transaction Log without using the "Backup Log with no_log"
> > command, but to no success. Could you offer any advice or alternative
> > solution.
> >
> >
> >
> > Best regards,
> >
> >
> >
> > Sezgin Rafet
> >
> >
> > "Ray Higdon" <rayhigdon@.higdonconsulting.com> wrote in message
> > news:%23L6$G5vxDHA.1740@.TK2MSFTNGP09.phx.gbl...
> > > You should not use the no_log command if you require point in time
> > recovery,
> > > and if you do not require point in time recovery, set your recovery
mode
> > to
> > > simple. I would advise not to be shrinking your log or data file like
> you
> > > are as if the db or log has to autogrow it costs you at the point it
> needs
> > > to allocate more space and that is typically when your db is busy.
> > >
> > > HTH
> > >
> > > --
> > > Ray Higdon MCSE, MCDBA, CCNA
> > > --
> > > "Sezgin Rafet" <srafet@.hotmail.com> wrote in message
> > > news:OUaTekvxDHA.2520@.TK2MSFTNGP10.phx.gbl...
> > > > We have a SQL Server 2000 SP3 database with recovery model set to
> full.
> > I
> > > > set up 3 automatic backup jobs as follows:
> > > >
> > > > Each day,Mon thru Fri at 9:00 am
> > > > 1 step - full backup of database to file A with overwrite
> > > >
> > > > Each day,Mon thru Fri every 30 minutes,between 9:30 am and 5:00 pm
> > > > 1 step - transaction log backup of database to file A with append
> > > >
> > > > Each day,Mon thru Fri at 5:05 pm
> > > > 1st step - transaction log backup of database to file A with append
> > > > 2nd step - execute BACKUP LOG WITH NO_LOG
> > > > 3rd step - execute DBCC SHRINK (db_file_name,1)
> > > >
> > > > First I tried to shrink the Log file without issuing the "BACKUP LOG
> > WITH
> > > > NO_LOG" statement , but DBCC SHRINK doesn't shrink the file as I
want,
> > > even
> > > > though I back up the Log.
> > > >
> > > >
> > > > When I use BACKUP LOG WITH NO_LOG everything seems to be working
OK.
> > > > However, I get an error in the Event Viever(Application Log) stating
> > > > "Database Log truncated".
> > > >
> > > > My question is:
> > > > Should I worry about this error ?
> > > > Is any data loss likely will this arrangement ?
> > > >
> > > > After all, I truncate the Transaction Log on purpose, right after
> > backing
> > > it
> > > > back in the previous step.
> > > >
> > > > Regards,
> > > >
> > > > Sezgin
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>