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