Showing posts with label log. Show all posts
Showing posts with label log. Show all posts

Wednesday, March 28, 2012

Regarding Active and Inactive

Can any one let me know the shortest possible like how I can inactive and
active log shipping.
The logshopping is working fine but want to inactive for two days and will
active after that according to business need so can anyone let me know how
is it possible.
Thanks
John.
> Can any one let me know the shortest possible like how I can inactive and
> active log shipping.
Disable the jobs temporally.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
|||Thanks
John
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in
message news:u6p00CTAFHA.2316@.TK2MSFTNGP15.phx.gbl...
> Disable the jobs temporally.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>

Reg. Log Shipping.

I have configured a log shipping on one of my Database.
Both the two jobs i.e. copy log and resotre log jobs run successfully
on the destination machines without any problem but I dont see any
logs copied on the destination machine.
1.Initially I had a job which used to take the transaction log
backup ,I thought this might be the reason as all the transaction are
getting backed up log shipping copy job is not getting any transaction
log to copy to the destination server.
2.But after disabling the transaction log backup job also samething is
happening i.e. log shipping copy and restore job is running
successfully but i dont see anything copied or resotred.
I must be doing something wrong but dont know whats that..
Please help.
Thanks
Sudhir
Hi
Are you sure that job was running? Have you looked at view job history?
"Sudhir" <sam904@.gmail.com> wrote in message
news:1182320095.674384.196630@.u2g2000hsc.googlegro ups.com...
>I have configured a log shipping on one of my Database.
> Both the two jobs i.e. copy log and resotre log jobs run successfully
> on the destination machines without any problem but I dont see any
> logs copied on the destination machine.
> 1.Initially I had a job which used to take the transaction log
> backup ,I thought this might be the reason as all the transaction are
> getting backed up log shipping copy job is not getting any transaction
> log to copy to the destination server.
> 2.But after disabling the transaction log backup job also samething is
> happening i.e. log shipping copy and restore job is running
> successfully but i dont see anything copied or resotred.
> I must be doing something wrong but dont know whats that..
> Please help.
> Thanks
> Sudhir
>

Reg. Log Shipping.

I have configured a log shipping on one of my Database.
Both the two jobs i.e. copy log and resotre log jobs run successfully
on the destination machines without any problem but I dont see any
logs copied on the destination machine.
1.Initially I had a job which used to take the transaction log
backup ,I thought this might be the reason as all the transaction are
getting backed up log shipping copy job is not getting any transaction
log to copy to the destination server.
2.But after disabling the transaction log backup job also samething is
happening i.e. log shipping copy and restore job is running
successfully but i dont see anything copied or resotred.
I must be doing something wrong but dont know whats that..
Please help.
Thanks
SudhirHi
Are you sure that job was running? Have you looked at view job history?
"Sudhir" <sam904@.gmail.com> wrote in message
news:1182320095.674384.196630@.u2g2000hsc.googlegroups.com...
>I have configured a log shipping on one of my Database.
> Both the two jobs i.e. copy log and resotre log jobs run successfully
> on the destination machines without any problem but I dont see any
> logs copied on the destination machine.
> 1.Initially I had a job which used to take the transaction log
> backup ,I thought this might be the reason as all the transaction are
> getting backed up log shipping copy job is not getting any transaction
> log to copy to the destination server.
> 2.But after disabling the transaction log backup job also samething is
> happening i.e. log shipping copy and restore job is running
> successfully but i dont see anything copied or resotred.
> I must be doing something wrong but dont know whats that..
> Please help.
> Thanks
> Sudhir
>

Reg. Log Shipping.

I have configured a log shipping on one of my Database.
Both the two jobs i.e. copy log and resotre log jobs run successfully
on the destination machines without any problem but I dont see any
logs copied on the destination machine.
1.Initially I had a job which used to take the transaction log
backup ,I thought this might be the reason as all the transaction are
getting backed up log shipping copy job is not getting any transaction
log to copy to the destination server.
2.But after disabling the transaction log backup job also samething is
happening i.e. log shipping copy and restore job is running
successfully but i dont see anything copied or resotred.
I must be doing something wrong but dont know whats that..
Please help.
Thanks
SudhirHi
Are you sure that job was running? Have you looked at view job history?
"Sudhir" <sam904@.gmail.com> wrote in message
news:1182320095.674384.196630@.u2g2000hsc.googlegroups.com...
>I have configured a log shipping on one of my Database.
> Both the two jobs i.e. copy log and resotre log jobs run successfully
> on the destination machines without any problem but I dont see any
> logs copied on the destination machine.
> 1.Initially I had a job which used to take the transaction log
> backup ,I thought this might be the reason as all the transaction are
> getting backed up log shipping copy job is not getting any transaction
> log to copy to the destination server.
> 2.But after disabling the transaction log backup job also samething is
> happening i.e. log shipping copy and restore job is running
> successfully but i dont see anything copied or resotred.
> I must be doing something wrong but dont know whats that..
> Please help.
> Thanks
> Sudhir
>

Wednesday, March 7, 2012

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
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

Reducing the size of the transaction log file

At this time I am only playing with the applications that generate the data and send it to the database. Without doing too much, and with deleting most data tables that were created, my transaction log file has grown over a gigabyte. I tried using the SQL server management studio (express) to shrink the database (tried shrinking files, too) but that did not make the file smaller. Right now there is hardly any data in the database (6 tables, a dozen columns and rows each) so it must be old transactions that are kept in the log. How do I get rid of the old data and make the file size smaller? Thanks.
KamenHi,

I had the same issue yesterday, i had to, Backup the log file then use shrink, backup one moore time and shrink.|||1. Check your database 'Recovery Model', set it according to your requirement.
2. Make a backup plan matching your data sensitivity & requirement as well as schedule it.
3. Plan Log backup along with Log Truncate option.

Refer Books OnLine from SQL Query Analyzer.|||Thank you very much!
Kamen

Saturday, February 25, 2012

reducing size of log file

Hi,
I have SQL 2000 running on W2000.
Some of my data has huge log files - eg 5Gb log for a DB of 50Mb.
Under properties, it reports that it is 95% space.
How can I reduce the size of these?
I have tried the shrink option bu it doesn't help.
Many thanks
NEIL
You need to backup log before shrinking it
BACKUP LOG database_name WITH TRUNCATE_ONLY
On Jan 25, 1:05 pm, "Neil Jarman" <n...@.tNOiSPAMvPLEASEy.co.uk> wrote:
> Hi,
> I have SQL 2000 running on W2000.
> Some of my data has huge log files - eg 5Gb log for a DB of 50Mb.
> Under properties, it reports that it is 95% space.
> How can I reduce the size of these?
> I have tried the shrink option bu it doesn't help.
> Many thanks
> NEIL
|||Neil
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
news:epa9v6$oo8$1$8300dec7@.news.demon.co.uk...
> Hi,
> I have SQL 2000 running on W2000.
> Some of my data has huge log files - eg 5Gb log for a DB of 50Mb.
> Under properties, it reports that it is 95% space.
> How can I reduce the size of these?
> I have tried the shrink option bu it doesn't help.
> Many thanks
> NEIL
>
|||Thanks for both your replies.
I have bvacked up log - using EM Backup Database... and this has reduced the
actual used size even miore, however it still occupies 5Gb of disk space.
I can't seem to use the DBCC commands described in the article supplied, can
you tell me how to do this? If I type DBCC followed by anything, I get dbcc
is not recognised ...
cheers,
NEIL
|||Hello,
Backup Log will clear up all the inactive postion of the log but will not
reduce the physical file. To reduce the physical
file you may need to SHRINK the LDF file using DBCC SHRINKFILE command. Take
a look into books online about
DBCC SHRINKFILE.
One more thing is schedule a frequent transaction log backup. This will
ensure that your LDF is not growing heavily as well as
make sure you have backup files to recover the database when required.
Thanks
Hari
"Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
news:epaec9$p5e$1$830fa7b3@.news.demon.co.uk...
> Thanks for both your replies.
> I have bvacked up log - using EM Backup Database... and this has reduced
> the actual used size even miore, however it still occupies 5Gb of disk
> space.
> I can't seem to use the DBCC commands described in the article supplied,
> can you tell me how to do this? If I type DBCC followed by anything, I get
> dbcc is not recognised ...
> cheers,
> NEIL
>
|||Hi Tibor,
I used a CMD screen to do this - I presumed it was a DOS type command.
NEIL
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eWgPBBKQHHA.2140@.TK2MSFTNGP03.phx.gbl...
> What application are you using to execute the DBCC command?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
> news:epaec9$p5e$1$830fa7b3@.news.demon.co.uk...
>
|||Hi Tibor,
Many thanks for the help - my log file is massively reduced now.
regards,
NEIL
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23VKhZhKQHHA.3944@.TK2MSFTNGP06.phx.gbl...
> Nope, it is a TSQL command. Use Query Analyzer/SQL Server Management
Studio.[vbcol=seagreen]
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
> news:epaph8$f6s$1$8300dec7@.news.demon.co.uk...
in message[vbcol=seagreen]
reduced the actual used size[vbcol=seagreen]
supplied, can you tell me how to[vbcol=seagreen]
recognised ...
>

reducing size of log file

Hi,
I have SQL 2000 running on W2000.
Some of my data has huge log files - eg 5Gb log for a DB of 50Mb.
Under properties, it reports that it is 95% space.
How can I reduce the size of these?
I have tried the shrink option bu it doesn't help.
Many thanks
NEILYou need to backup log before shrinking it
BACKUP LOG database_name WITH TRUNCATE_ONLY
On Jan 25, 1:05 pm, "Neil Jarman" <n...@.tNOiSPAMvPLEASEy.co.uk> wrote:
> Hi,
> I have SQL 2000 running on W2000.
> Some of my data has huge log files - eg 5Gb log for a DB of 50Mb.
> Under properties, it reports that it is 95% space.
> How can I reduce the size of these?
> I have tried the shrink option bu it doesn't help.
> Many thanks
> NEIL|||Neil
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
news:epa9v6$oo8$1$8300dec7@.news.demon.co.uk...
> Hi,
> I have SQL 2000 running on W2000.
> Some of my data has huge log files - eg 5Gb log for a DB of 50Mb.
> Under properties, it reports that it is 95% space.
> How can I reduce the size of these?
> I have tried the shrink option bu it doesn't help.
> Many thanks
> NEIL
>|||Thanks for both your replies.
I have bvacked up log - using EM Backup Database... and this has reduced the
actual used size even miore, however it still occupies 5Gb of disk space.
I can't seem to use the DBCC commands described in the article supplied, can
you tell me how to do this? If I type DBCC followed by anything, I get dbcc
is not recognised ...
cheers,
NEIL|||Hello,
Backup Log will clear up all the inactive postion of the log but will not
reduce the physical file. To reduce the physical
file you may need to SHRINK the LDF file using DBCC SHRINKFILE command. Take
a look into books online about
DBCC SHRINKFILE.
One more thing is schedule a frequent transaction log backup. This will
ensure that your LDF is not growing heavily as well as
make sure you have backup files to recover the database when required.
Thanks
Hari
"Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
news:epaec9$p5e$1$830fa7b3@.news.demon.co.uk...
> Thanks for both your replies.
> I have bvacked up log - using EM Backup Database... and this has reduced
> the actual used size even miore, however it still occupies 5Gb of disk
> space.
> I can't seem to use the DBCC commands described in the article supplied,
> can you tell me how to do this? If I type DBCC followed by anything, I get
> dbcc is not recognised ...
> cheers,
> NEIL
>|||> If I type DBCC followed by anything, I get dbcc is not recognised ...
What application are you using to execute the DBCC command?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
news:epaec9$p5e$1$830fa7b3@.news.demon.co.uk...
> Thanks for both your replies.
> I have bvacked up log - using EM Backup Database... and this has reduced t
he actual used size even
> miore, however it still occupies 5Gb of disk space.
> I can't seem to use the DBCC commands described in the article supplied, c
an you tell me how to do
> this? If I type DBCC followed by anything, I get dbcc is not recognised ..
.
> cheers,
> NEIL
>|||Hi Tibor,
I used a CMD screen to do this - I presumed it was a DOS type command.
NEIL
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eWgPBBKQHHA.2140@.TK2MSFTNGP03.phx.gbl...
> What application are you using to execute the DBCC command?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
> news:epaec9$p5e$1$830fa7b3@.news.demon.co.uk...
>|||Nope, it is a TSQL command. Use Query Analyzer/SQL Server Management Studio.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
news:epaph8$f6s$1$8300dec7@.news.demon.co.uk...
> Hi Tibor,
> I used a CMD screen to do this - I presumed it was a DOS type command.
> NEIL
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:eWgPBBKQHHA.2140@.TK2MSFTNGP03.phx.gbl...
>|||Lines: 68
NNTP-Posting-Host: nbtdance.demon.co.uk
X-Trace: news.demon.co.uk 1170082069 7290 80.177.3.126 (29 Jan 2007 14:47:49
GMT)
X-Complaints-To: abuse@.demon.net
NNTP-Posting-Date: Mon, 29 Jan 2007 14:47:49 +0000 (UTC)
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1807
X-Priority: 3
X-Newsreader: Microsoft Outlook Express 6.00.2800.1807
X-MSMail-Priority: Normal
Xref: leafnode.mcse.ms microsoft.public.sqlserver.server:25631
Hi Tibor,
Many thanks for the help - my log file is massively reduced now.
regards,
NEIL
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23VKhZhKQHHA.3944@.TK2MSFTNGP06.phx.gbl...
> Nope, it is a TSQL command. Use Query Analyzer/SQL Server Management
Studio.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
> news:epaph8$f6s$1$8300dec7@.news.demon.co.uk...
in message[vbcol=seagreen]
reduced the actual used size[vbcol=seagreen]
supplied, can you tell me how to[vbcol=seagreen]
recognised ...[vbcol=seagreen]
>

reducing size of log file

Hi,
I have SQL 2000 running on W2000.
Some of my data has huge log files - eg 5Gb log for a DB of 50Mb.
Under properties, it reports that it is 95% space.
How can I reduce the size of these?
I have tried the shrink option bu it doesn't help.
Many thanks
NEILYou need to backup log before shrinking it
BACKUP LOG database_name WITH TRUNCATE_ONLY
On Jan 25, 1:05 pm, "Neil Jarman" <n...@.tNOiSPAMvPLEASEy.co.uk> wrote:
> Hi,
> I have SQL 2000 running on W2000.
> Some of my data has huge log files - eg 5Gb log for a DB of 50Mb.
> Under properties, it reports that it is 95% space.
> How can I reduce the size of these?
> I have tried the shrink option bu it doesn't help.
> Many thanks
> NEIL|||Neil
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
news:epa9v6$oo8$1$8300dec7@.news.demon.co.uk...
> Hi,
> I have SQL 2000 running on W2000.
> Some of my data has huge log files - eg 5Gb log for a DB of 50Mb.
> Under properties, it reports that it is 95% space.
> How can I reduce the size of these?
> I have tried the shrink option bu it doesn't help.
> Many thanks
> NEIL
>|||Thanks for both your replies.
I have bvacked up log - using EM Backup Database... and this has reduced the
actual used size even miore, however it still occupies 5Gb of disk space.
I can't seem to use the DBCC commands described in the article supplied, can
you tell me how to do this? If I type DBCC followed by anything, I get dbcc
is not recognised ...
cheers,
NEIL|||Hello,
Backup Log will clear up all the inactive postion of the log but will not
reduce the physical file. To reduce the physical
file you may need to SHRINK the LDF file using DBCC SHRINKFILE command. Take
a look into books online about
DBCC SHRINKFILE.
One more thing is schedule a frequent transaction log backup. This will
ensure that your LDF is not growing heavily as well as
make sure you have backup files to recover the database when required.
Thanks
Hari
"Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
news:epaec9$p5e$1$830fa7b3@.news.demon.co.uk...
> Thanks for both your replies.
> I have bvacked up log - using EM Backup Database... and this has reduced
> the actual used size even miore, however it still occupies 5Gb of disk
> space.
> I can't seem to use the DBCC commands described in the article supplied,
> can you tell me how to do this? If I type DBCC followed by anything, I get
> dbcc is not recognised ...
> cheers,
> NEIL
>|||> If I type DBCC followed by anything, I get dbcc is not recognised ...
What application are you using to execute the DBCC command?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
news:epaec9$p5e$1$830fa7b3@.news.demon.co.uk...
> Thanks for both your replies.
> I have bvacked up log - using EM Backup Database... and this has reduced the actual used size even
> miore, however it still occupies 5Gb of disk space.
> I can't seem to use the DBCC commands described in the article supplied, can you tell me how to do
> this? If I type DBCC followed by anything, I get dbcc is not recognised ...
> cheers,
> NEIL
>|||Hi Tibor,
I used a CMD screen to do this - I presumed it was a DOS type command.
NEIL
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eWgPBBKQHHA.2140@.TK2MSFTNGP03.phx.gbl...
>> If I type DBCC followed by anything, I get dbcc is not recognised ...
> What application are you using to execute the DBCC command?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
> news:epaec9$p5e$1$830fa7b3@.news.demon.co.uk...
>> Thanks for both your replies.
>> I have bvacked up log - using EM Backup Database... and this has reduced
>> the actual used size even miore, however it still occupies 5Gb of disk
>> space.
>> I can't seem to use the DBCC commands described in the article supplied,
>> can you tell me how to do this? If I type DBCC followed by anything, I
>> get dbcc is not recognised ...
>> cheers,
>> NEIL
>|||Nope, it is a TSQL command. Use Query Analyzer/SQL Server Management Studio.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
news:epaph8$f6s$1$8300dec7@.news.demon.co.uk...
> Hi Tibor,
> I used a CMD screen to do this - I presumed it was a DOS type command.
> NEIL
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:eWgPBBKQHHA.2140@.TK2MSFTNGP03.phx.gbl...
>> If I type DBCC followed by anything, I get dbcc is not recognised ...
>> What application are you using to execute the DBCC command?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
>> news:epaec9$p5e$1$830fa7b3@.news.demon.co.uk...
>> Thanks for both your replies.
>> I have bvacked up log - using EM Backup Database... and this has reduced the actual used size
>> even miore, however it still occupies 5Gb of disk space.
>> I can't seem to use the DBCC commands described in the article supplied, can you tell me how to
>> do this? If I type DBCC followed by anything, I get dbcc is not recognised ...
>> cheers,
>> NEIL
>>
>|||Hi Tibor,
Many thanks for the help - my log file is massively reduced now.
regards,
NEIL
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23VKhZhKQHHA.3944@.TK2MSFTNGP06.phx.gbl...
> Nope, it is a TSQL command. Use Query Analyzer/SQL Server Management
Studio.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
> news:epaph8$f6s$1$8300dec7@.news.demon.co.uk...
> > Hi Tibor,
> >
> > I used a CMD screen to do this - I presumed it was a DOS type command.
> >
> > NEIL
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in message
> > news:eWgPBBKQHHA.2140@.TK2MSFTNGP03.phx.gbl...
> >> If I type DBCC followed by anything, I get dbcc is not recognised ...
> >>
> >> What application are you using to execute the DBCC command?
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
> >> news:epaec9$p5e$1$830fa7b3@.news.demon.co.uk...
> >> Thanks for both your replies.
> >>
> >> I have bvacked up log - using EM Backup Database... and this has
reduced the actual used size
> >> even miore, however it still occupies 5Gb of disk space.
> >>
> >> I can't seem to use the DBCC commands described in the article
supplied, can you tell me how to
> >> do this? If I type DBCC followed by anything, I get dbcc is not
recognised ...
> >>
> >> cheers,
> >>
> >> NEIL
> >>
> >>
> >
> >
>

Reducing physical transaction log file size.

I need some help to reduce the physical file size of a database transaction
log. I ran the shrink database option under the System Manager and was able
to shring the logical size of the log file, however, I would also like to
reduce the physical size of the file to free up disk space. I have tried to
run the DBCC Shrinkfile and DBCC shrinkdatabase commands but that does not
appear to be successfull. Can anybody help? I am running SQL 2000. Thanks
.http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Elbryyan" <Elbryyan@.discussions.microsoft.com> wrote in message
news:67FAF140-014E-4CB2-800E-740AF1B69212@.microsoft.com...[vbcol=seagreen]
>I need some help to reduce the physical file size of a database transaction
> log. I ran the shrink database option under the System Manager and was ab
le
> to shring the logical size of the log file, however, I would also like to
> reduce the physical size of the file to free up disk space. I have tried
to
> run the DBCC Shrinkfile and DBCC shrinkdatabase commands but that does not
> appear to be successfull. Can anybody help? I am running SQL 2000. Thanks.[/vbc
ol]

Reducing log (ldf) file size

I thought that the size of the log file went down when you did a full
database backup. How can I reduce the size of my log file? Do I need to
back it up separately? Thanks.
David
DBCC SHRINKFILE(databasename_log, 0)
This will reduce the log file size to 504 KB.
However, if you don't change your current recovery model and/or the behavior
of your apps, the log file is just going to grow again between backups. So
the above is only a temporary relief.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:uC#FtmiLFHA.1392@.TK2MSFTNGP10.phx.gbl...
> I thought that the size of the log file went down when you did a full
> database backup. How can I reduce the size of my log file? Do I need to
> back it up separately? Thanks.
> David
>
|||You are right. I thought I had set it to "Simple" but was set to
"Full". Thanks Aaron.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||and addtionally to Aarons reply, you need to backup the log seperately
(...look up BACKUP LOG xxxx...in Books On Line..) to keep the log in shape.
Setting the recovery to Simple will limit your possibilities to recover data
in case of something happens to the database.
Regards
Steen
David wrote:
> You are right. I thought I had set it to "Simple" but was set to
> "Full". Thanks Aaron.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Reducing log (ldf) file size

I thought that the size of the log file went down when you did a full
database backup. How can I reduce the size of my log file? Do I need to
back it up separately? Thanks.
DavidDBCC SHRINKFILE(databasename_log, 0)
This will reduce the log file size to 504 KB.
However, if you don't change your current recovery model and/or the behavior
of your apps, the log file is just going to grow again between backups. So
the above is only a temporary relief.
--
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:uC#FtmiLFHA.1392@.TK2MSFTNGP10.phx.gbl...
> I thought that the size of the log file went down when you did a full
> database backup. How can I reduce the size of my log file? Do I need to
> back it up separately? Thanks.
> David
>

Reducing log (ldf) file size

I thought that the size of the log file went down when you did a full
database backup. How can I reduce the size of my log file? Do I need to
back it up separately? Thanks.
DavidDBCC SHRINKFILE(databasename_log, 0)
This will reduce the log file size to 504 KB.
However, if you don't change your current recovery model and/or the behavior
of your apps, the log file is just going to grow again between backups. So
the above is only a temporary relief.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:uC#FtmiLFHA.1392@.TK2MSFTNGP10.phx.gbl...
> I thought that the size of the log file went down when you did a full
> database backup. How can I reduce the size of my log file? Do I need to
> back it up separately? Thanks.
> David
>|||You are right. I thought I had set it to "Simple" but was set to
"Full". Thanks Aaron.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||and addtionally to Aarons reply, you need to backup the log seperately
(...look up BACKUP LOG xxxx...in Books On Line..) to keep the log in shape.
Setting the recovery to Simple will limit your possibilities to recover data
in case of something happens to the database.
Regards
Steen
David wrote:
> You are right. I thought I had set it to "Simple" but was set to
> "Full". Thanks Aaron.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

reduce trans log size; help needed

Hello,

I am hoping you can help me with the following problem; I need to process
the following steps every couple of hours in order to keep our Sql 2000
database a small as possible (the transaction log is 5x bigger than the db).

1.back-up the entire database
2.truncate the log
3.shrink the log
4.back-up once again.

As you may have determined, I am relatively new to managing a sql server
database and while I have found multiple articles online about the topics I
need to accomplish, I cannot find any actual examples that explain where I
input the coded used to accomplish the above-mentioned steps. I do
understand the theory behind the steps I just do not know how to accomplish
them!

If you know of a well-documented tutorial (Aside from Books Online (F1)),
please point me in the right direction.

Regards.First, create a backup device with whatever name you want. ( I assume you
know how to do this).
Might have to check the syntax on each of these but here goes

1. backup database [your_database_name] to [your_backup_device] with stats,
init
2. backup tran [your_database_name] to [your_backup_device]
3. dbcc shrinkfile 2,truncateonly
4. backup database [your_database_name] to [your_backup_device]

Step 3 above assumes that file 2 is the logfile for your database. You
might have to check this by issuing a select * from sysfiles (in query
analyzer) to make sure that file 2 is the log file.

Hope this helps.

Oscar...

> 1.back-up the entire database
> 2.truncate the log
> 3.shrink the log
> 4.back-up once again.
> As you may have determined, I am relatively new to managing a sql server
> database and while I have found multiple articles online about the topics
I
> need to accomplish, I cannot find any actual examples that explain where I
> input the coded used to accomplish the above-mentioned steps. I do
> understand the theory behind the steps I just do not know how to
accomplish
> them!

reduce trans log size; help needed

Hello,
I am hoping you can help me with the following problem; I need to process
the following steps every couple of hours in order to keep our Sql 2000
database a small as possible (the transaction log is 5x bigger than the db).
1.back-up the entire database
2.truncate the log
3.shrink the log
4.back-up once again.
As you may have determined, I am relatively new to managing a sql server
database and while I have found multiple articles online about the topics I
need to accomplish, I cannot find any actual examples that explain where I
input the coded used to accomplish the above-mentioned steps. I do
understand the theory behind the steps I just do not know how to accomplish
them!
If you know of a well-documented tutorial (Aside from Books Online (F1)),
please point me in the right direction.
Regards.| Hello,
|
| I am hoping you can help me with the following problem; I need to process
| the following steps every couple of hours in order to keep our Sql 2000
| database a small as possible (the transaction log is 5x bigger than the
db).
|
| 1.back-up the entire database
| 2.truncate the log
| 3.shrink the log
| 4.back-up once again.
|
| As you may have determined, I am relatively new to managing a sql server
| database and while I have found multiple articles online about the topics
I
| need to accomplish, I cannot find any actual examples that explain where I
| input the coded used to accomplish the above-mentioned steps. I do
| understand the theory behind the steps I just do not know how to
accomplish
| them!
--
You can create a job that executes the above steps every couple of hours.
You can define each of the above steps as actual job steps with the job.
You can use Enterprise Manager to create this job.
Hope this helps,
--
Eric Cárdenas
SQL Server support

Monday, February 20, 2012

Reduce the Size of SQL Log File

We have MS SQL 2000 Enterprise Edition, running on Windows 2000 Server. Is
it a good idea to reduce the size of the SQL Log File? If it is, do I need
to make sure no one is using the database while I am reducing the size of
the log file? Is it possible to setup these commands in a batch file
instead of running them under Query Analyzer? Thanks.
BACKUP LOG Smart_DATA WITH TRUNCATE_ONLY
GO
DBCC SHRINKDATABASE (Smart_DATA, TRUNCATEONLY)
What recovery model is the database in? Also see
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Diane Walker" <ett9300@.yahoo.com> wrote in message news:u90cVi%23vFHA.1256@.TK2MSFTNGP09.phx.gbl...
> We have MS SQL 2000 Enterprise Edition, running on Windows 2000 Server. Is it a good idea to
> reduce the size of the SQL Log File? If it is, do I need to make sure no one is using the
> database while I am reducing the size of the log file? Is it possible to setup these commands in
> a batch file instead of running them under Query Analyzer? Thanks.
> BACKUP LOG Smart_DATA WITH TRUNCATE_ONLY
> GO
> DBCC SHRINKDATABASE (Smart_DATA, TRUNCATEONLY)
>
>
>
|||"while I am reducing the size of the log file?"
-No you neednt, even that would speed up the task. if you have any
maintainance window, do it within this.
"Is it possible to setup these commands in a batch file instead of
running them under Query Analyzer ?"
-Yes of course, put the statements in a job (step) and execute it here.
HTH, Jens Suessmeyer.
|||Thank you very much.
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1127458411.014481.253870@.g49g2000cwa.googlegr oups.com...
> "while I am reducing the size of the log file?"
> -No you neednt, even that would speed up the task. if you have any
> maintainance window, do it within this.
> "Is it possible to setup these commands in a batch file instead of
> running them under Query Analyzer ?"
> -Yes of course, put the statements in a job (step) and execute it here.
> HTH, Jens Suessmeyer.
>

Reduce the Size of SQL Log File

We have MS SQL 2000 Enterprise Edition, running on Windows 2000 Server. Is
it a good idea to reduce the size of the SQL Log File? If it is, do I need
to make sure no one is using the database while I am reducing the size of
the log file? Is it possible to setup these commands in a batch file
instead of running them under Query Analyzer? Thanks.
BACKUP LOG Smart_DATA WITH TRUNCATE_ONLY
GO
DBCC SHRINKDATABASE (Smart_DATA, TRUNCATEONLY)What recovery model is the database in? Also see
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Diane Walker" <ett9300@.yahoo.com> wrote in message news:u90cVi%23vFHA.1256@.TK2MSFTNGP09.phx.gbl...
> We have MS SQL 2000 Enterprise Edition, running on Windows 2000 Server. Is it a good idea to
> reduce the size of the SQL Log File? If it is, do I need to make sure no one is using the
> database while I am reducing the size of the log file? Is it possible to setup these commands in
> a batch file instead of running them under Query Analyzer? Thanks.
> BACKUP LOG Smart_DATA WITH TRUNCATE_ONLY
> GO
> DBCC SHRINKDATABASE (Smart_DATA, TRUNCATEONLY)
>
>
>|||"while I am reducing the size of the log file?"
-No you neednt, even that would speed up the task. if you have any
maintainance window, do it within this.
"Is it possible to setup these commands in a batch file instead of
running them under Query Analyzer ?"
-Yes of course, put the statements in a job (step) and execute it here.
HTH, Jens Suessmeyer.|||Thank you very much.
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1127458411.014481.253870@.g49g2000cwa.googlegroups.com...
> "while I am reducing the size of the log file?"
> -No you neednt, even that would speed up the task. if you have any
> maintainance window, do it within this.
> "Is it possible to setup these commands in a batch file instead of
> running them under Query Analyzer ?"
> -Yes of course, put the statements in a job (step) and execute it here.
> HTH, Jens Suessmeyer.
>

Reduce the Size of SQL Log File

We have MS SQL 2000 Enterprise Edition, running on Windows 2000 Server. Is
it a good idea to reduce the size of the SQL Log File? If it is, do I need
to make sure no one is using the database while I am reducing the size of
the log file? Is it possible to setup these commands in a batch file
instead of running them under Query Analyzer? Thanks.
BACKUP LOG Smart_DATA WITH TRUNCATE_ONLY
GO
DBCC SHRINKDATABASE (Smart_DATA, TRUNCATEONLY)What recovery model is the database in? Also see
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Diane Walker" <ett9300@.yahoo.com> wrote in message news:u90cVi%23vFHA.1256@.TK2MSFTNGP09.phx
.gbl...
> We have MS SQL 2000 Enterprise Edition, running on Windows 2000 Server. I
s it a good idea to
> reduce the size of the SQL Log File? If it is, do I need to make sure no
one is using the
> database while I am reducing the size of the log file? Is it possible to
setup these commands in
> a batch file instead of running them under Query Analyzer? Thanks.
> BACKUP LOG Smart_DATA WITH TRUNCATE_ONLY
> GO
> DBCC SHRINKDATABASE (Smart_DATA, TRUNCATEONLY)
>
>
>|||"while I am reducing the size of the log file?"
-No you neednt, even that would speed up the task. if you have any
maintainance window, do it within this.
"Is it possible to setup these commands in a batch file instead of
running them under Query Analyzer ?"
-Yes of course, put the statements in a job (step) and execute it here.
HTH, Jens Suessmeyer.|||Thank you very much.
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1127458411.014481.253870@.g49g2000cwa.googlegroups.com...
> "while I am reducing the size of the log file?"
> -No you neednt, even that would speed up the task. if you have any
> maintainance window, do it within this.
> "Is it possible to setup these commands in a batch file instead of
> running them under Query Analyzer ?"
> -Yes of course, put the statements in a job (step) and execute it here.
> HTH, Jens Suessmeyer.
>

Reduce the size of log file (SQL Server 2000)

Dear all,
I want to reduce the size of log file, what should I do ?
Thanks.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
http://www.nigelrivett.net/TransactionLogFileGrows_1.html Log File issues
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=272318 Shrinking Log in SQL Server
2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=873235 How to stop the log file from
growing
http://www.support.microsoft.com/?id=305635 Timeout while DB expanding
http://www.support.microsoft.com/?id=307487 Shrinking TempDB
Andrew J. Kelly SQL MVP
"Vensia" <vensia2000_nospam@.yahoo.com> wrote in message
news:%23BGLTKrOHHA.4244@.TK2MSFTNGP04.phx.gbl...
> Dear all,
> I want to reduce the size of log file, what should I do ?
> Thanks.
>
|||Vensia wrote:
> Dear all,
> I want to reduce the size of log file, what should I do ?
> Thanks.
>
First, determine why it's grown to the current size. Is it because
you're not doing transaction log backups, or does your normal day-to-day
activity require it to be that size?
Quite often, when someone asks this question, it's because they have the
database in Bulk-Logged or Full recovery mode, without understanding
what that means. The transaction log is a sort of "journal", recording
all of the changes that occur in your database. As these journal
entries accumulate, the log file must grow. You can minimize this
growth in 2 ways:
- put the database in Simple mode. Committed transactions are
automatically flushed from the log. You lose the ability to recover the
database to the point of failure, however.
- schedule periodic transaction log backups. A log backup flushes out
the committed transactions, AND gives you the ability to restore a
database to literally any point in time, by restoring the last full
backup, followed by all of the transaction log backups up to the point
of failure. Doing log backups every 15 minutes means you should never
lose more than 15 minutes of work if the database should crash.
It is important to understand that flushing out these transactions
(known as TRUNCATING the log) will NOT reduce the physical size of the
log file. Truncating simply removes the committed transactions, making
that space available for re-use by future transactions.
To reduce the physical size of the log file, you must perform a SHRINK,
using DBCC SHRINKFILE. Think carefully before doing this. Think about
WHY the log file is the size that it is - what made it this size?
Shrinking it makes no sense if it's just going to grow again, you'll end
up with file fragmentation, affecting performance.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Thanks for Tracy and Andrew.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45AF7638.9080009@.realsqlguy.com...
> Vensia wrote:
> First, determine why it's grown to the current size. Is it because
> you're not doing transaction log backups, or does your normal day-to-day
> activity require it to be that size?
> Quite often, when someone asks this question, it's because they have the
> database in Bulk-Logged or Full recovery mode, without understanding
> what that means. The transaction log is a sort of "journal", recording
> all of the changes that occur in your database. As these journal
> entries accumulate, the log file must grow. You can minimize this
> growth in 2 ways:
> - put the database in Simple mode. Committed transactions are
> automatically flushed from the log. You lose the ability to recover the
> database to the point of failure, however.
> - schedule periodic transaction log backups. A log backup flushes out
> the committed transactions, AND gives you the ability to restore a
> database to literally any point in time, by restoring the last full
> backup, followed by all of the transaction log backups up to the point
> of failure. Doing log backups every 15 minutes means you should never
> lose more than 15 minutes of work if the database should crash.
> It is important to understand that flushing out these transactions
> (known as TRUNCATING the log) will NOT reduce the physical size of the
> log file. Truncating simply removes the committed transactions, making
> that space available for re-use by future transactions.
> To reduce the physical size of the log file, you must perform a SHRINK,
> using DBCC SHRINKFILE. Think carefully before doing this. Think about
> WHY the log file is the size that it is - what made it this size?
> Shrinking it makes no sense if it's just going to grow again, you'll end
> up with file fragmentation, affecting performance.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Reduce the size of log file (SQL Server 2000)

Dear all,
I want to reduce the size of log file, what should I do ?
Thanks.http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
http://www.nigelrivett.net/Transact...ileGrows_1.html Log File issues
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=272318 Shrinking Log in SQL Server
2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=873235 How to stop the log file from
growing
http://www.support.microsoft.com/?id=305635 Timeout while DB expanding
http://www.support.microsoft.com/?id=307487 Shrinking TempDB
Andrew J. Kelly SQL MVP
"Vensia" <vensia2000_nospam@.yahoo.com> wrote in message
news:%23BGLTKrOHHA.4244@.TK2MSFTNGP04.phx.gbl...
> Dear all,
> I want to reduce the size of log file, what should I do ?
> Thanks.
>|||Vensia wrote:
> Dear all,
> I want to reduce the size of log file, what should I do ?
> Thanks.
>
First, determine why it's grown to the current size. Is it because
you're not doing transaction log backups, or does your normal day-to-day
activity require it to be that size?
Quite often, when someone asks this question, it's because they have the
database in Bulk-Logged or Full recovery mode, without understanding
what that means. The transaction log is a sort of "journal", recording
all of the changes that occur in your database. As these journal
entries accumulate, the log file must grow. You can minimize this
growth in 2 ways:
- put the database in Simple mode. Committed transactions are
automatically flushed from the log. You lose the ability to recover the
database to the point of failure, however.
- schedule periodic transaction log backups. A log backup flushes out
the committed transactions, AND gives you the ability to restore a
database to literally any point in time, by restoring the last full
backup, followed by all of the transaction log backups up to the point
of failure. Doing log backups every 15 minutes means you should never
lose more than 15 minutes of work if the database should crash.
It is important to understand that flushing out these transactions
(known as TRUNCATING the log) will NOT reduce the physical size of the
log file. Truncating simply removes the committed transactions, making
that space available for re-use by future transactions.
To reduce the physical size of the log file, you must perform a SHRINK,
using DBCC SHRINKFILE. Think carefully before doing this. Think about
WHY the log file is the size that it is - what made it this size?
Shrinking it makes no sense if it's just going to grow again, you'll end
up with file fragmentation, affecting performance.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks for Tracy and Andrew.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45AF7638.9080009@.realsqlguy.com...
> Vensia wrote:
> First, determine why it's grown to the current size. Is it because
> you're not doing transaction log backups, or does your normal day-to-day
> activity require it to be that size?
> Quite often, when someone asks this question, it's because they have the
> database in Bulk-Logged or Full recovery mode, without understanding
> what that means. The transaction log is a sort of "journal", recording
> all of the changes that occur in your database. As these journal
> entries accumulate, the log file must grow. You can minimize this
> growth in 2 ways:
> - put the database in Simple mode. Committed transactions are
> automatically flushed from the log. You lose the ability to recover the
> database to the point of failure, however.
> - schedule periodic transaction log backups. A log backup flushes out
> the committed transactions, AND gives you the ability to restore a
> database to literally any point in time, by restoring the last full
> backup, followed by all of the transaction log backups up to the point
> of failure. Doing log backups every 15 minutes means you should never
> lose more than 15 minutes of work if the database should crash.
> It is important to understand that flushing out these transactions
> (known as TRUNCATING the log) will NOT reduce the physical size of the
> log file. Truncating simply removes the committed transactions, making
> that space available for re-use by future transactions.
> To reduce the physical size of the log file, you must perform a SHRINK,
> using DBCC SHRINKFILE. Think carefully before doing this. Think about
> WHY the log file is the size that it is - what made it this size?
> Shrinking it makes no sense if it's just going to grow again, you'll end
> up with file fragmentation, affecting performance.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com