Log just keeps growing!

> Hi,
>
> My 600Mb database continually has a 4Gb log file with it, which is a bit
of
> a pain as we have a very small number of inserts during the day so there's
> no need for the log to be this big.
>
> We do have a load process that loads approx 150,000 - 200,000 rows
running
> once a week, but, even still I can't see why the log grows as it does.
Every
> night we have a full backup running.
>
> So, I've got a couple of questions:
>
> 1) - What backup mode do I need to have set on the DB to ensure once
> it's backed up the log is then truncated correctly? I'm guessing the log
> itself is being truncated but the actual file isn't being shrunk?
>
> 2) - I keep detaching the DB and then reattaching it without a log
to
> get rid of the huge log file. Is this the best way to keep this huge log
> under control or am I risking data corruption?
>
> 3) - Are there any jobs I can run during the day that can keep this
> log under control?
>
> Any advice appreciated.
>
> Thanks
>
>You can use simple recovery mode. For more information check these out:
http://www.support.microsoft.com/?id=110139
http://www.support.microsoft.com/?id=272318
http://www.support.microsoft.com/?id=317375
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"London Developer" <dev@.nowhere.com> wrote in message
news:%23GGCbQngDHA.1760@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > My 600Mb database continually has a 4Gb log file with it, which is a bit
> of
> > a pain as we have a very small number of inserts during the day so
there's
> > no need for the log to be this big.
> >
> > We do have a load process that loads approx 150,000 - 200,000 rows
> running
> > once a week, but, even still I can't see why the log grows as it does.
> Every
> > night we have a full backup running.
> >
> > So, I've got a couple of questions:
> >
> > 1) - What backup mode do I need to have set on the DB to ensure
once
> > it's backed up the log is then truncated correctly? I'm guessing the log
> > itself is being truncated but the actual file isn't being shrunk?
> >
> > 2) - I keep detaching the DB and then reattaching it without a log
> to
> > get rid of the huge log file. Is this the best way to keep this huge log
> > under control or am I risking data corruption?
> >
> > 3) - Are there any jobs I can run during the day that can keep
this
> > log under control?
> >
> > Any advice appreciated.
> >
> > Thanks
> >
> >
>|||Bear in mind, the transaction log supports point-in-time database recovery.
If you need this functionality, truncating the log frequently during the day
will cause a problem.
What you should do is schedule frequent transaction log backups (to disk if
you plan point-in-time recovery, then make sure to back those up to tape
quickly, so you can clear off disk space). To recover the newly freed
space, schedule a job to run fairly frequently in the database that does
dbcc shrinkfile (<database_name>_log, 0) (in SQL 7, do not put the ,0 in).
Anyhow, here is your detail on backup modes:
Full recovery model (select into/bulk copy disabled, trunc. log on chkpt.
disabled) -- This is the default. It enables you to do point-in-time
restores, but the transaction log will grow unless you back it up frequently
to disk, and then shrink the log file afterwards.
Bulk logged recovery model (select into/bulk copy enabled, trunc. log on
chkpt. disabled) -- This improves the performance of bulk data loads and
other data processes (eg create index) other than simple INSERTs, UPDATEs
and DELETEs by only minimally logging the event. If you plan to backup the
transaction log so you can restore from it later (point-in-time restore),
you need to a full or differential database backup AFTER EVERY database
operation other than a INSERT, UPDATE or DELETE. You still need to backup
the log frequently, and schedule dbcc shrinkfile to return the inactive
space to the OS
Simple Recovery model (trunc. log on chkpt and select/into bulk copy
enabled). Not only are major database operations not logged, the
transaction log is automatically emptied out at regular intervals
(checkpoints). The only backups you can restore from here are full database
backups or differential backups. You may need to still run dbcc shrinkfile
to keep the log file under control.
If you have more questions, you can try emailing me, I'm not sure if my
account is still getting spammed out of control or not.
*******************************************************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andy_mcdba@.yahoo.com
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
Andy_mcdba@.yahoo.com gets filled up to the account
limit with spam every couple of hours now so replies may
not be possible. I will remove this disclaimer once every
ISP involved with relaying the spam can help me out.
*******************************************************************
"London Developer" <dev@.nowhere.com> wrote in message
news:%23GGCbQngDHA.1760@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > My 600Mb database continually has a 4Gb log file with it, which is a bit
> of
> > a pain as we have a very small number of inserts during the day so
there's
> > no need for the log to be this big.
> >
> > We do have a load process that loads approx 150,000 - 200,000 rows
> running
> > once a week, but, even still I can't see why the log grows as it does.
> Every
> > night we have a full backup running.
> >
> > So, I've got a couple of questions:
> >
> > 1) - What backup mode do I need to have set on the DB to ensure
once
> > it's backed up the log is then truncated correctly? I'm guessing the log
> > itself is being truncated but the actual file isn't being shrunk?
> >
> > 2) - I keep detaching the DB and then reattaching it without a log
> to
> > get rid of the huge log file. Is this the best way to keep this huge log
> > under control or am I risking data corruption?
> >
> > 3) - Are there any jobs I can run during the day that can keep
this
> > log under control?
> >
> > Any advice appreciated.
> >
> > Thanks
> >
> >
>

Log file size growing rapidly.. increased by 1 GB every week

Hi,
My sql server database log file size growing rapidly, 1 GB by every week. I
have a service running every 2 hours which deletes and inserts some data,
say around 5000 records for every two hours.
I know the source of the problem is deleting and inserting records for every
2 hours. I want to go for a densive mechansim for this, that is: i don't
want to change the service code (for example using TRUNCATE statement
instead of DELETE statement) for any reason.
So I am thinking to write sql job whose job is:
Backup transaction log and shrink log file.
This job runs every day at 4:00 AM.
I alreday have a full backup job which runs at 3:00 AM daily. Probably I
will add the above commnads (backup transaction log and then shrink log
file) to the full backup job schedule as another step.
Does any one experainced this? is it write thing to shrink file every day?
Thanks,
VenkatVenkat
What is recovery model of your db? Its possible it has FULL recovery mode.
If so, please start performing BACKUP LOG to move an inactive transactions
to be moved on the disk. I'd suggest you read about recovery models in the
BOL to understand their impact on the database...
"Venkat" <venkatarao.v@.gmail.com> wrote in message
news:uWnh13jZIHA.4712@.TK2MSFTNGP05.phx.gbl...
> Hi,
> My sql server database log file size growing rapidly, 1 GB by every week.
> I have a service running every 2 hours which deletes and inserts some
> data, say around 5000 records for every two hours.
> I know the source of the problem is deleting and inserting records for
> every 2 hours. I want to go for a densive mechansim for this, that is: i
> don't want to change the service code (for example using TRUNCATE
> statement instead of DELETE statement) for any reason.
> So I am thinking to write sql job whose job is:
> Backup transaction log and shrink log file.
> This job runs every day at 4:00 AM.
> I alreday have a full backup job which runs at 3:00 AM daily. Probably I
> will add the above commnads (backup transaction log and then shrink log
> file) to the full backup job schedule as another step.
> Does any one experainced this? is it write thing to shrink file every day?
> Thanks,
> Venkat
>|||... and to understand the implications of doing a regular shrink, check out:
http://sqlblog.com/blogs/tibor_karaszi/archive/2007/02/25/leaking-roof-and-file-shrinking.aspx
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:eVZW1IkZIHA.4332@.TK2MSFTNGP04.phx.gbl...
> Venkat
> What is recovery model of your db? Its possible it has FULL recovery mode.
> If so, please start performing BACKUP LOG to move an inactive transactions
> to be moved on the disk. I'd suggest you read about recovery models in the
> BOL to understand their impact on the database...
>
> "Venkat" <venkatarao.v@.gmail.com> wrote in message
> news:uWnh13jZIHA.4712@.TK2MSFTNGP05.phx.gbl...
>> Hi,
>> My sql server database log file size growing rapidly, 1 GB by every week.
>> I have a service running every 2 hours which deletes and inserts some
>> data, say around 5000 records for every two hours.
>> I know the source of the problem is deleting and inserting records for
>> every 2 hours. I want to go for a densive mechansim for this, that is: i
>> don't want to change the service code (for example using TRUNCATE
>> statement instead of DELETE statement) for any reason.
>> So I am thinking to write sql job whose job is:
>> Backup transaction log and shrink log file.
>> This job runs every day at 4:00 AM.
>> I alreday have a full backup job which runs at 3:00 AM daily. Probably I
>> will add the above commnads (backup transaction log and then shrink log
>> file) to the full backup job schedule as another step.
>> Does any one experainced this? is it write thing to shrink file every day?
>> Thanks,
>> Venkat
>|||Example with "Leaking roof and bucket" is great, easy to understand :-)
--
Regards,
anxcomp|||Thanks :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"anxcomp" <anxcomp@.discussions.microsoft.com> wrote in message
news:1663555F-8A8E-4772-9C74-D4DA6F5FB6A6@.microsoft.com...
> Example with "Leaking roof and bucket" is great, easy to understand :-)
> --
> Regards,
> anxcomp|||Hi Uri Dimant, Thsnaks for the reply.
Yes the recovery model is FULL
As I mentioned in my post, i have maintennace plan, which will backup the
database every day at 3:00 AM
So now if I include the BACKUP LOG in maintenance plan (and NO Shrinking
log file) then this will address the log file size issue?
Or
I should include DBCC SHRINKFILE(N'MyDb_log' , 0) also along with the BACKUP
LOG?
Thanks,
Venkat
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eVZW1IkZIHA.4332@.TK2MSFTNGP04.phx.gbl...
> Venkat
> What is recovery model of your db? Its possible it has FULL recovery mode.
> If so, please start performing BACKUP LOG to move an inactive
> transactions to be moved on the disk. I'd suggest you read about recovery
> models in the BOL to understand their impact on the database...
>
> "Venkat" <venkatarao.v@.gmail.com> wrote in message
> news:uWnh13jZIHA.4712@.TK2MSFTNGP05.phx.gbl...
>> Hi,
>> My sql server database log file size growing rapidly, 1 GB by every week.
>> I have a service running every 2 hours which deletes and inserts some
>> data, say around 5000 records for every two hours.
>> I know the source of the problem is deleting and inserting records for
>> every 2 hours. I want to go for a densive mechansim for this, that is: i
>> don't want to change the service code (for example using TRUNCATE
>> statement instead of DELETE statement) for any reason.
>> So I am thinking to write sql job whose job is:
>> Backup transaction log and shrink log file.
>> This job runs every day at 4:00 AM.
>> I alreday have a full backup job which runs at 3:00 AM daily. Probably I
>> will add the above commnads (backup transaction log and then shrink log
>> file) to the full backup job schedule as another step.
>> Does any one experainced this? is it write thing to shrink file every
>> day?
>> Thanks,
>> Venkat
>|||Venkat,
Just schedule the transaction log backup job. Do not schedule any shrinkfile
operation.
Once you have performed a transaction log backup you can do a shrink file
operation manually, but only once.
Hope this helps,
Ben Nevarez
"Venkat" wrote:
> Hi Uri Dimant, Thsnaks for the reply.
> Yes the recovery model is FULL
> As I mentioned in my post, i have maintennace plan, which will backup the
> database every day at 3:00 AM
> So now if I include the BACKUP LOG in maintenance plan (and NO Shrinking
> log file) then this will address the log file size issue?
> Or
> I should include DBCC SHRINKFILE(N'MyDb_log' , 0) also along with the BACKUP
> LOG?
> Thanks,
> Venkat
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eVZW1IkZIHA.4332@.TK2MSFTNGP04.phx.gbl...
> > Venkat
> > What is recovery model of your db? Its possible it has FULL recovery mode.
> > If so, please start performing BACKUP LOG to move an inactive
> > transactions to be moved on the disk. I'd suggest you read about recovery
> > models in the BOL to understand their impact on the database...
> >
> >
> >
> > "Venkat" <venkatarao.v@.gmail.com> wrote in message
> > news:uWnh13jZIHA.4712@.TK2MSFTNGP05.phx.gbl...
> >> Hi,
> >> My sql server database log file size growing rapidly, 1 GB by every week.
> >> I have a service running every 2 hours which deletes and inserts some
> >> data, say around 5000 records for every two hours.
> >>
> >> I know the source of the problem is deleting and inserting records for
> >> every 2 hours. I want to go for a densive mechansim for this, that is: i
> >> don't want to change the service code (for example using TRUNCATE
> >> statement instead of DELETE statement) for any reason.
> >>
> >> So I am thinking to write sql job whose job is:
> >> Backup transaction log and shrink log file.
> >>
> >> This job runs every day at 4:00 AM.
> >>
> >> I alreday have a full backup job which runs at 3:00 AM daily. Probably I
> >> will add the above commnads (backup transaction log and then shrink log
> >> file) to the full backup job schedule as another step.
> >>
> >> Does any one experainced this? is it write thing to shrink file every
> >> day?
> >>
> >> Thanks,
> >> Venkat
> >>
> >
> >
>
>|||Hi Tibor,
Thanks for the reply. Yes I read your post before posting the request here.
Your explanation is good on this topic. But I have one question:
Even if you allocate enough space to log file, say 20 GB to log file, after
some time some one has to go and clean it up before it overshoots the
allocated size.
So is the reason for the automated job to freeup the log file space.
From customers angle: Instead of realying upon DBA or some one to moitor
this log file and clean it up, they would like the system to take care this
automatically. So this is the reason why people want to go for regular
shrink of the log file to freeup the space
Please reply with your comments.
Thanks,
Venkat
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:5281B2E6-A3DC-43E6-BF11-EAF3F24BD2DC@.microsoft.com...
> Thanks :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "anxcomp" <anxcomp@.discussions.microsoft.com> wrote in message
> news:1663555F-8A8E-4772-9C74-D4DA6F5FB6A6@.microsoft.com...
>> Example with "Leaking roof and bucket" is great, easy to understand :-)
>> --
>> Regards,
>> anxcomp
>|||Venkat,
Just schedule the transaction log backup job. Do not schedule any shrinkfile
operation.
Once you have performed a transaction log backup you can do a shrink file
operation manually, but only once.
Hope this helps,
Ben Nevarez
"Venkat" wrote:
> Hi Uri Dimant, Thsnaks for the reply.
> Yes the recovery model is FULL
> As I mentioned in my post, i have maintennace plan, which will backup the
> database every day at 3:00 AM
> So now if I include the BACKUP LOG in maintenance plan (and NO Shrinking
> log file) then this will address the log file size issue?
> Or
> I should include DBCC SHRINKFILE(N'MyDb_log' , 0) also along with the BACKUP
> LOG?
> Thanks,
> Venkat
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eVZW1IkZIHA.4332@.TK2MSFTNGP04.phx.gbl...
> > Venkat
> > What is recovery model of your db? Its possible it has FULL recovery mode.
> > If so, please start performing BACKUP LOG to move an inactive
> > transactions to be moved on the disk. I'd suggest you read about recovery
> > models in the BOL to understand their impact on the database...
> >
> >
> >
> > "Venkat" <venkatarao.v@.gmail.com> wrote in message
> > news:uWnh13jZIHA.4712@.TK2MSFTNGP05.phx.gbl...
> >> Hi,
> >> My sql server database log file size growing rapidly, 1 GB by every week.
> >> I have a service running every 2 hours which deletes and inserts some
> >> data, say around 5000 records for every two hours.
> >>
> >> I know the source of the problem is deleting and inserting records for
> >> every 2 hours. I want to go for a densive mechansim for this, that is: i
> >> don't want to change the service code (for example using TRUNCATE
> >> statement instead of DELETE statement) for any reason.
> >>
> >> So I am thinking to write sql job whose job is:
> >> Backup transaction log and shrink log file.
> >>
> >> This job runs every day at 4:00 AM.
> >>
> >> I alreday have a full backup job which runs at 3:00 AM daily. Probably I
> >> will add the above commnads (backup transaction log and then shrink log
> >> file) to the full backup job schedule as another step.
> >>
> >> Does any one experainced this? is it write thing to shrink file every
> >> day?
> >>
> >> Thanks,
> >> Venkat
> >>
> >
> >
>
>|||Hi Ben Nevarez,
So backup database command does not automatically backup log file?
What is the difference between shrinking log file manually and automatically
(when I say automatically, I might shrink log file once in a Week by
scheduling a monthly job)
Thanks,
venkat
"Ben Nevarez" <bnevarez@.no.spam.please.sunamerica.com> wrote in message
news:8A0140FD-A4D4-47F1-AA53-329A6F57BE3A@.microsoft.com...
> Venkat,
> Just schedule the transaction log backup job. Do not schedule any
> shrinkfile
> operation.
> Once you have performed a transaction log backup you can do a shrink file
> operation manually, but only once.
> Hope this helps,
> Ben Nevarez
>
>
> "Venkat" wrote:
>> Hi Uri Dimant, Thsnaks for the reply.
>> Yes the recovery model is FULL
>> As I mentioned in my post, i have maintennace plan, which will backup the
>> database every day at 3:00 AM
>> So now if I include the BACKUP LOG in maintenance plan (and NO Shrinking
>> log file) then this will address the log file size issue?
>> Or
>> I should include DBCC SHRINKFILE(N'MyDb_log' , 0) also along with the
>> BACKUP
>> LOG?
>> Thanks,
>> Venkat
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:eVZW1IkZIHA.4332@.TK2MSFTNGP04.phx.gbl...
>> > Venkat
>> > What is recovery model of your db? Its possible it has FULL recovery
>> > mode.
>> > If so, please start performing BACKUP LOG to move an inactive
>> > transactions to be moved on the disk. I'd suggest you read about
>> > recovery
>> > models in the BOL to understand their impact on the database...
>> >
>> >
>> >
>> > "Venkat" <venkatarao.v@.gmail.com> wrote in message
>> > news:uWnh13jZIHA.4712@.TK2MSFTNGP05.phx.gbl...
>> >> Hi,
>> >> My sql server database log file size growing rapidly, 1 GB by every
>> >> week.
>> >> I have a service running every 2 hours which deletes and inserts some
>> >> data, say around 5000 records for every two hours.
>> >>
>> >> I know the source of the problem is deleting and inserting records for
>> >> every 2 hours. I want to go for a densive mechansim for this, that is:
>> >> i
>> >> don't want to change the service code (for example using TRUNCATE
>> >> statement instead of DELETE statement) for any reason.
>> >>
>> >> So I am thinking to write sql job whose job is:
>> >> Backup transaction log and shrink log file.
>> >>
>> >> This job runs every day at 4:00 AM.
>> >>
>> >> I alreday have a full backup job which runs at 3:00 AM daily. Probably
>> >> I
>> >> will add the above commnads (backup transaction log and then shrink
>> >> log
>> >> file) to the full backup job schedule as another step.
>> >>
>> >> Does any one experainced this? is it write thing to shrink file every
>> >> day?
>> >>
>> >> Thanks,
>> >> Venkat
>> >>
>> >
>> >
>>|||If you have a Full Backup that does not backup the transaction log. You need
to specify a Transaction Log Backup.
What I meant by manually is that you will need to shrink the file only once.
Do not schedule any job.
Hope this helps,
Ben Nevarez
"Venkat" wrote:
> Hi Ben Nevarez,
> So backup database command does not automatically backup log file?
> What is the difference between shrinking log file manually and automatically
> (when I say automatically, I might shrink log file once in a Week by
> scheduling a monthly job)
> Thanks,
> venkat
> "Ben Nevarez" <bnevarez@.no.spam.please.sunamerica.com> wrote in message
> news:8A0140FD-A4D4-47F1-AA53-329A6F57BE3A@.microsoft.com...
> >
> > Venkat,
> >
> > Just schedule the transaction log backup job. Do not schedule any
> > shrinkfile
> > operation.
> >
> > Once you have performed a transaction log backup you can do a shrink file
> > operation manually, but only once.
> >
> > Hope this helps,
> >
> > Ben Nevarez
> >
> >
> >
> >
> > "Venkat" wrote:
> >
> >> Hi Uri Dimant, Thsnaks for the reply.
> >>
> >> Yes the recovery model is FULL
> >>
> >> As I mentioned in my post, i have maintennace plan, which will backup the
> >> database every day at 3:00 AM
> >> So now if I include the BACKUP LOG in maintenance plan (and NO Shrinking
> >> log file) then this will address the log file size issue?
> >>
> >> Or
> >> I should include DBCC SHRINKFILE(N'MyDb_log' , 0) also along with the
> >> BACKUP
> >> LOG?
> >>
> >> Thanks,
> >> Venkat
> >>
> >> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> >> news:eVZW1IkZIHA.4332@.TK2MSFTNGP04.phx.gbl...
> >> > Venkat
> >> > What is recovery model of your db? Its possible it has FULL recovery
> >> > mode.
> >> > If so, please start performing BACKUP LOG to move an inactive
> >> > transactions to be moved on the disk. I'd suggest you read about
> >> > recovery
> >> > models in the BOL to understand their impact on the database...
> >> >
> >> >
> >> >
> >> > "Venkat" <venkatarao.v@.gmail.com> wrote in message
> >> > news:uWnh13jZIHA.4712@.TK2MSFTNGP05.phx.gbl...
> >> >> Hi,
> >> >> My sql server database log file size growing rapidly, 1 GB by every
> >> >> week.
> >> >> I have a service running every 2 hours which deletes and inserts some
> >> >> data, say around 5000 records for every two hours.
> >> >>
> >> >> I know the source of the problem is deleting and inserting records for
> >> >> every 2 hours. I want to go for a densive mechansim for this, that is:
> >> >> i
> >> >> don't want to change the service code (for example using TRUNCATE
> >> >> statement instead of DELETE statement) for any reason.
> >> >>
> >> >> So I am thinking to write sql job whose job is:
> >> >> Backup transaction log and shrink log file.
> >> >>
> >> >> This job runs every day at 4:00 AM.
> >> >>
> >> >> I alreday have a full backup job which runs at 3:00 AM daily. Probably
> >> >> I
> >> >> will add the above commnads (backup transaction log and then shrink
> >> >> log
> >> >> file) to the full backup job schedule as another step.
> >> >>
> >> >> Does any one experainced this? is it write thing to shrink file every
> >> >> day?
> >> >>
> >> >> Thanks,
> >> >> Venkat
> >> >>
> >> >
> >> >
> >>
> >>
> >>
>
>|||> Even if you allocate enough space to log file, say 20 GB to log file, after some time some one has
> to go and clean it up before it overshoots the allocated size.
Yes, this is what the BACKUP LOG command does, it empties the log. And, of course, this is something
we schedule.
If you don't want to do backup log, then set the database in simple recovery model. Then SQL Server
will truncate/empty the log every time a checkpoint occurs (something that happens pretty regularly
in the database).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Venkat" <venkatarao.v@.gmail.com> wrote in message news:uIutBC8ZIHA.4160@.TK2MSFTNGP03.phx.gbl...
> Hi Tibor,
> Thanks for the reply. Yes I read your post before posting the request here. Your explanation is
> good on this topic. But I have one question:
> Even if you allocate enough space to log file, say 20 GB to log file, after some time some one has
> to go and clean it up before it overshoots the allocated size.
> So is the reason for the automated job to freeup the log file space.
> From customers angle: Instead of realying upon DBA or some one to moitor this log file and clean
> it up, they would like the system to take care this automatically. So this is the reason why
> people want to go for regular shrink of the log file to freeup the space
> Please reply with your comments.
> Thanks,
> Venkat
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:5281B2E6-A3DC-43E6-BF11-EAF3F24BD2DC@.microsoft.com...
>> Thanks :-)
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "anxcomp" <anxcomp@.discussions.microsoft.com> wrote in message
>> news:1663555F-8A8E-4772-9C74-D4DA6F5FB6A6@.microsoft.com...
>> Example with "Leaking roof and bucket" is great, easy to understand :-)
>> --
>> Regards,
>> anxcomp
>|||> So backup database command does not automatically backup log file?
Backup database do include some log records. That that isn't the important question. The important
question is whether backup database empties the log. And the answer is: "It doesn't".
> What is the difference between shrinking log file manually and automatically (when I say
> automatically, I might shrink log file once in a Week by scheduling a monthly job)
That is what I tried to explain in my "leaking roof" analogy. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Venkat" <venkatarao.v@.gmail.com> wrote in message news:e4g41F8ZIHA.4208@.TK2MSFTNGP04.phx.gbl...
> Hi Ben Nevarez,
> So backup database command does not automatically backup log file?
> What is the difference between shrinking log file manually and automatically (when I say
> automatically, I might shrink log file once in a Week by scheduling a monthly job)
> Thanks,
> venkat
> "Ben Nevarez" <bnevarez@.no.spam.please.sunamerica.com> wrote in message
> news:8A0140FD-A4D4-47F1-AA53-329A6F57BE3A@.microsoft.com...
>> Venkat,
>> Just schedule the transaction log backup job. Do not schedule any shrinkfile
>> operation.
>> Once you have performed a transaction log backup you can do a shrink file
>> operation manually, but only once.
>> Hope this helps,
>> Ben Nevarez
>>
>>
>> "Venkat" wrote:
>> Hi Uri Dimant, Thsnaks for the reply.
>> Yes the recovery model is FULL
>> As I mentioned in my post, i have maintennace plan, which will backup the
>> database every day at 3:00 AM
>> So now if I include the BACKUP LOG in maintenance plan (and NO Shrinking
>> log file) then this will address the log file size issue?
>> Or
>> I should include DBCC SHRINKFILE(N'MyDb_log' , 0) also along with the BACKUP
>> LOG?
>> Thanks,
>> Venkat
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:eVZW1IkZIHA.4332@.TK2MSFTNGP04.phx.gbl...
>> > Venkat
>> > What is recovery model of your db? Its possible it has FULL recovery mode.
>> > If so, please start performing BACKUP LOG to move an inactive
>> > transactions to be moved on the disk. I'd suggest you read about recovery
>> > models in the BOL to understand their impact on the database...
>> >
>> >
>> >
>> > "Venkat" <venkatarao.v@.gmail.com> wrote in message
>> > news:uWnh13jZIHA.4712@.TK2MSFTNGP05.phx.gbl...
>> >> Hi,
>> >> My sql server database log file size growing rapidly, 1 GB by every week.
>> >> I have a service running every 2 hours which deletes and inserts some
>> >> data, say around 5000 records for every two hours.
>> >>
>> >> I know the source of the problem is deleting and inserting records for
>> >> every 2 hours. I want to go for a densive mechansim for this, that is: i
>> >> don't want to change the service code (for example using TRUNCATE
>> >> statement instead of DELETE statement) for any reason.
>> >>
>> >> So I am thinking to write sql job whose job is:
>> >> Backup transaction log and shrink log file.
>> >>
>> >> This job runs every day at 4:00 AM.
>> >>
>> >> I alreday have a full backup job which runs at 3:00 AM daily. Probably I
>> >> will add the above commnads (backup transaction log and then shrink log
>> >> file) to the full backup job schedule as another step.
>> >>
>> >> Does any one experainced this? is it write thing to shrink file every
>> >> day?
>> >>
>> >> Thanks,
>> >> Venkat
>> >>
>> >
>> >
>>
>