Schedule Trucate and Shrink

Hello all,
I am very new to sql server as we have just replaced our enterprise
application (was access) Our Datafile is around 3GB and the log grows 3GB in
a day. I have auto job routines scheduled in EM and happening every night
for backups etc and they are then auto (scheduled) backed up to tape. The
log file is still 2.5GB in the am after the all the jobs run so I am
shrinking the DB every few days and the log drops to 768KB.
My question is I see all the DBCC shrinfile commands Truncate etc... my
question is (pardon my ignorance) where the heck to I put these, how do they
run and how can i schedule them?
Please be gentle with you reply.
MIf your log naturally grows 3gb a day you should (1) not shrink it and (2)
possibly schedule more frequent log backups. If you require point in time
recovery you should use the truncate log command, if you do not require
point in time recovery, life is easy, set the db to simple recovery mode and
you won't have to worry about the tran logs.
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Maureen" <nosend2me@.hotmail.com> wrote in message
news:u9GckFtuDHA.3140@.TK2MSFTNGP11.phx.gbl...
> Hello all,
> I am very new to sql server as we have just replaced our enterprise
> application (was access) Our Datafile is around 3GB and the log grows 3GB
in
> a day. I have auto job routines scheduled in EM and happening every night
> for backups etc and they are then auto (scheduled) backed up to tape. The
> log file is still 2.5GB in the am after the all the jobs run so I am
> shrinking the DB every few days and the log drops to 768KB.
> My question is I see all the DBCC shrinfile commands Truncate etc... my
> question is (pardon my ignorance) where the heck to I put these, how do
they
> run and how can i schedule them?
> Please be gentle with you reply.
> M
>|||Ray,
Thanks for the note.
I need to keep to possible "point in time" recovery. here is a normal day.
12.01 AM E.M.-Sql-Maintenence Plan begins and optimizes/backup data/backup
log report Scheduled daily
5:00 AM Complete Server Backup to Tape NTBACKUP Scheduled including backup
to tape of SqlBackup
8:00 AM Workday begins (Tape changed from [Day1,2,3,4,5 etc]-Nightly to
[Day1,2,3,4,5]-Daily
Regular Work Day....
5:00 PM SqlBackup to tape Complete
5:30 PM Workday end (Tape changed from [Day1,2,3,4,5 etc]-Daily to
[Day1,2,3,4,5]-Nightly
I can easily add a couple more backups to tape or disk during the day. The
server has hardware raid with 5 drives in a raid 5 (data is on these
spmdles) PLUS 2 drives raid 10 for the logs. I am well covered for potential
failure. What I would like to know is where do I issue the Truncate Log
Command AND how do I schedule it to run at 7:30AM every day?
M
"Ray Higdon" <rayhigdon@.higdonconsulting.com> wrote in message
news:e9wR7etuDHA.540@.tk2msftngp13.phx.gbl...
> If your log naturally grows 3gb a day you should (1) not shrink it and (2)
> possibly schedule more frequent log backups. If you require point in time
> recovery you should use the truncate log command, if you do not require
> point in time recovery, life is easy, set the db to simple recovery mode
and
> you won't have to worry about the tran logs.
> HTH
> --
> Ray Higdon MCSE, MCDBA, CCNA
> --
> "Maureen" <nosend2me@.hotmail.com> wrote in message
> news:u9GckFtuDHA.3140@.TK2MSFTNGP11.phx.gbl...
> > Hello all,
> >
> > I am very new to sql server as we have just replaced our enterprise
> > application (was access) Our Datafile is around 3GB and the log grows
3GB
> in
> > a day. I have auto job routines scheduled in EM and happening every
night
> > for backups etc and they are then auto (scheduled) backed up to tape.
The
> > log file is still 2.5GB in the am after the all the jobs run so I am
> > shrinking the DB every few days and the log drops to 768KB.
> >
> > My question is I see all the DBCC shrinfile commands Truncate etc... my
> > question is (pardon my ignorance) where the heck to I put these, how do
> they
> > run and how can i schedule them?
> >
> > Please be gentle with you reply.
> > M
> >
> >
>|||Ray,
I have read a little further and think I may have found the answer. Are
these commands issues as part of a SQL job? I have not even looked at this
section of EM as all I have in place currently is done as a maintenance
plan. If this is the case I'll have a look at this Friday as I do not have
access to the server from here.
M
"Maureen" <nosend2me@.hotmail.com> wrote in message
news:Obo8ODuuDHA.1196@.TK2MSFTNGP12.phx.gbl...
> Ray,
> Thanks for the note.
> I need to keep to possible "point in time" recovery. here is a normal
day.
> 12.01 AM E.M.-Sql-Maintenence Plan begins and optimizes/backup data/backup
> log report Scheduled daily
> 5:00 AM Complete Server Backup to Tape NTBACKUP Scheduled including backup
> to tape of SqlBackup
> 8:00 AM Workday begins (Tape changed from [Day1,2,3,4,5 etc]-Nightly to
> [Day1,2,3,4,5]-Daily
> Regular Work Day....
> 5:00 PM SqlBackup to tape Complete
> 5:30 PM Workday end (Tape changed from [Day1,2,3,4,5 etc]-Daily to
> [Day1,2,3,4,5]-Nightly
> I can easily add a couple more backups to tape or disk during the day. The
> server has hardware raid with 5 drives in a raid 5 (data is on these
> spmdles) PLUS 2 drives raid 10 for the logs. I am well covered for
potential
> failure. What I would like to know is where do I issue the Truncate Log
> Command AND how do I schedule it to run at 7:30AM every day?
> M
>
> "Ray Higdon" <rayhigdon@.higdonconsulting.com> wrote in message
> news:e9wR7etuDHA.540@.tk2msftngp13.phx.gbl...
> > If your log naturally grows 3gb a day you should (1) not shrink it and
(2)
> > possibly schedule more frequent log backups. If you require point in
time
> > recovery you should use the truncate log command, if you do not require
> > point in time recovery, life is easy, set the db to simple recovery mode
> and
> > you won't have to worry about the tran logs.
> >
> > HTH
> >
> > --
> > Ray Higdon MCSE, MCDBA, CCNA
> > --
> > "Maureen" <nosend2me@.hotmail.com> wrote in message
> > news:u9GckFtuDHA.3140@.TK2MSFTNGP11.phx.gbl...
> > > Hello all,
> > >
> > > I am very new to sql server as we have just replaced our enterprise
> > > application (was access) Our Datafile is around 3GB and the log grows
> 3GB
> > in
> > > a day. I have auto job routines scheduled in EM and happening every
> night
> > > for backups etc and they are then auto (scheduled) backed up to tape.
> The
> > > log file is still 2.5GB in the am after the all the jobs run so I am
> > > shrinking the DB every few days and the log drops to 768KB.
> > >
> > > My question is I see all the DBCC shrinfile commands Truncate etc...
my
> > > question is (pardon my ignorance) where the heck to I put these, how
do
> > they
> > > run and how can i schedule them?
> > >
> > > Please be gentle with you reply.
> > > M
> > >
> > >
> >
> >
>|||If you need point in time recovery, you need to write "backup log"
statements, not truncate log. See BOL for details
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Maureen" <nosend2me@.hotmail.com> wrote in message
news:e8$qHKuuDHA.640@.tk2msftngp13.phx.gbl...
> Ray,
> I have read a little further and think I may have found the answer. Are
> these commands issues as part of a SQL job? I have not even looked at this
> section of EM as all I have in place currently is done as a maintenance
> plan. If this is the case I'll have a look at this Friday as I do not have
> access to the server from here.
> M
>
> "Maureen" <nosend2me@.hotmail.com> wrote in message
> news:Obo8ODuuDHA.1196@.TK2MSFTNGP12.phx.gbl...
> > Ray,
> >
> > Thanks for the note.
> >
> > I need to keep to possible "point in time" recovery. here is a normal
> day.
> >
> > 12.01 AM E.M.-Sql-Maintenence Plan begins and optimizes/backup
data/backup
> > log report Scheduled daily
> > 5:00 AM Complete Server Backup to Tape NTBACKUP Scheduled including
backup
> > to tape of SqlBackup
> > 8:00 AM Workday begins (Tape changed from [Day1,2,3,4,5 etc]-Nightly to
> > [Day1,2,3,4,5]-Daily
> > Regular Work Day....
> > 5:00 PM SqlBackup to tape Complete
> > 5:30 PM Workday end (Tape changed from [Day1,2,3,4,5 etc]-Daily to
> > [Day1,2,3,4,5]-Nightly
> >
> > I can easily add a couple more backups to tape or disk during the day.
The
> > server has hardware raid with 5 drives in a raid 5 (data is on these
> > spmdles) PLUS 2 drives raid 10 for the logs. I am well covered for
> potential
> > failure. What I would like to know is where do I issue the Truncate Log
> > Command AND how do I schedule it to run at 7:30AM every day?
> >
> > M
> >
> >
> >
> > "Ray Higdon" <rayhigdon@.higdonconsulting.com> wrote in message
> > news:e9wR7etuDHA.540@.tk2msftngp13.phx.gbl...
> > > If your log naturally grows 3gb a day you should (1) not shrink it and
> (2)
> > > possibly schedule more frequent log backups. If you require point in
> time
> > > recovery you should use the truncate log command, if you do not
require
> > > point in time recovery, life is easy, set the db to simple recovery
mode
> > and
> > > you won't have to worry about the tran logs.
> > >
> > > HTH
> > >
> > > --
> > > Ray Higdon MCSE, MCDBA, CCNA
> > > --
> > > "Maureen" <nosend2me@.hotmail.com> wrote in message
> > > news:u9GckFtuDHA.3140@.TK2MSFTNGP11.phx.gbl...
> > > > Hello all,
> > > >
> > > > I am very new to sql server as we have just replaced our enterprise
> > > > application (was access) Our Datafile is around 3GB and the log
grows
> > 3GB
> > > in
> > > > a day. I have auto job routines scheduled in EM and happening every
> > night
> > > > for backups etc and they are then auto (scheduled) backed up to
tape.
> > The
> > > > log file is still 2.5GB in the am after the all the jobs run so I am
> > > > shrinking the DB every few days and the log drops to 768KB.
> > > >
> > > > My question is I see all the DBCC shrinfile commands Truncate etc...
> my
> > > > question is (pardon my ignorance) where the heck to I put these, how
> do
> > > they
> > > > run and how can i schedule them?
> > > >
> > > > Please be gentle with you reply.
> > > > M
> > > >
> > > >
> > >
> > >
> >
> >
>