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?
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" <venkatara...@gmail.com> wrote in message
> 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?
"Uri Dimant" <u...@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" <venkatara...@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?
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?
> 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" <venkatara...@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?
"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" <u...@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" <venkatara...@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?
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
"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" <u...@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" <venkatara...@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?
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" <bneva...@no.spam.please.sunamerica.com> wrote in message
> 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" <u...@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" <venkatara...@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?
"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)
> > 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" <u...@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" <venkatara...@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?
> 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).
"Venkat" <venkatara...@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
> 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. :-)
"Venkat" <venkatara...@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)
>> 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" <u...@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" <venkatara...@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?
|
