log file to be reduced

Hi All
I have a mdf file of around 4gb but log file increased to
20gb. how to reduce size of log file.
Any help would be highly appreciated.
Rgds
TriveniHave a look at these articles
INF: How to Shrink the SQL Server 7.0 Transaction Lo
http://support.microsoft.com/default.aspx?scid=kb;en-us;25665
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFIL
http://support.microsoft.com/default.aspx?scid=kb;en-us;27231
http://www.mssqlserver.com/faq/logs-shrinklog.as
Hope this help
John

log file to be reduced

Hi All
I have a mdf file of around 4gb but log file increased to
20gb. how to reduce size of log file.
Any help would be highly appreciated.
Rgds
TriveniHave a look at these articles.
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/defaul...kb;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/defaul...kb;en-us;272318
http://www.mssqlserver.com/faq/logs-shrinklog.asp
Hope this helps
John

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

Log file size gets reduced

Hi,
I increased the total log file size of a database from 30 MB to 300 MB from
Enterprise Manager. I check it in Enterprise manager and it shows the size a
s
300 MB. However, every time I do that it gets reduced to 30 MB after some
time. What am I doing wrong?Hi,
You should check your maintenance plan. Maybe you set "remove unused space"
from logfile.
Regards,
Janos
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:CFF80442-D600-40E8-BCEE-3AF516192E34@.microsoft.com...
> Hi,
> I increased the total log file size of a database from 30 MB to 300 MB
> from
> Enterprise Manager. I check it in Enterprise manager and it shows the size
> as
> 300 MB. However, every time I do that it gets reduced to 30 MB after some
> time. What am I doing wrong?|||Check if the autoshrink database option is set and also of you have any main
t job or similar that
perform shrink operations.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:CFF80442-D600-40E8-BCEE-3AF516192E34@.microsoft.com...
> Hi,
> I increased the total log file size of a database from 30 MB to 300 MB fro
m
> Enterprise Manager. I check it in Enterprise manager and it shows the size
as
> 300 MB. However, every time I do that it gets reduced to 30 MB after some
> time. What am I doing wrong?|||Hello,
If it is a production server and if the option Tibor mentioned is enabled
then it is recommended to uncheck the option. This will degrade the
performance if the
file gets auto shunk and then grow automatically.
Thanks
Hari
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e$Uxm4njHHA.1388@.TK2MSFTNGP05.phx.gbl...
> Check if the autoshrink database option is set and also of you have any
> maint job or similar that perform shrink operations.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:CFF80442-D600-40E8-BCEE-3AF516192E34@.microsoft.com...
>sql

Log file size gets reduced

Hi,
I increased the total log file size of a database from 30 MB to 300 MB from
Enterprise Manager. I check it in Enterprise manager and it shows the size as
300 MB. However, every time I do that it gets reduced to 30 MB after some
time. What am I doing wrong?Hi,
You should check your maintenance plan. Maybe you set "remove unused space"
from logfile.
Regards,
Janos
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:CFF80442-D600-40E8-BCEE-3AF516192E34@.microsoft.com...
> Hi,
> I increased the total log file size of a database from 30 MB to 300 MB
> from
> Enterprise Manager. I check it in Enterprise manager and it shows the size
> as
> 300 MB. However, every time I do that it gets reduced to 30 MB after some
> time. What am I doing wrong?|||Check if the autoshrink database option is set and also of you have any maint job or similar that
perform shrink operations.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:CFF80442-D600-40E8-BCEE-3AF516192E34@.microsoft.com...
> Hi,
> I increased the total log file size of a database from 30 MB to 300 MB from
> Enterprise Manager. I check it in Enterprise manager and it shows the size as
> 300 MB. However, every time I do that it gets reduced to 30 MB after some
> time. What am I doing wrong?|||Hello,
If it is a production server and if the option Tibor mentioned is enabled
then it is recommended to uncheck the option. This will degrade the
performance if the
file gets auto shunk and then grow automatically.
Thanks
Hari
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e$Uxm4njHHA.1388@.TK2MSFTNGP05.phx.gbl...
> Check if the autoshrink database option is set and also of you have any
> maint job or similar that perform shrink operations.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:CFF80442-D600-40E8-BCEE-3AF516192E34@.microsoft.com...
>> Hi,
>> I increased the total log file size of a database from 30 MB to 300 MB
>> from
>> Enterprise Manager. I check it in Enterprise manager and it shows the
>> size as
>> 300 MB. However, every time I do that it gets reduced to 30 MB after some
>> time. What am I doing wrong?
>

Log file size gets reduced

Hi,
I increased the total log file size of a database from 30 MB to 300 MB from
Enterprise Manager. I check it in Enterprise manager and it shows the size as
300 MB. However, every time I do that it gets reduced to 30 MB after some
time. What am I doing wrong?
Hi,
You should check your maintenance plan. Maybe you set "remove unused space"
from logfile.
Regards,
Janos
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:CFF80442-D600-40E8-BCEE-3AF516192E34@.microsoft.com...
> Hi,
> I increased the total log file size of a database from 30 MB to 300 MB
> from
> Enterprise Manager. I check it in Enterprise manager and it shows the size
> as
> 300 MB. However, every time I do that it gets reduced to 30 MB after some
> time. What am I doing wrong?
|||Check if the autoshrink database option is set and also of you have any maint job or similar that
perform shrink operations.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:CFF80442-D600-40E8-BCEE-3AF516192E34@.microsoft.com...
> Hi,
> I increased the total log file size of a database from 30 MB to 300 MB from
> Enterprise Manager. I check it in Enterprise manager and it shows the size as
> 300 MB. However, every time I do that it gets reduced to 30 MB after some
> time. What am I doing wrong?
|||Hello,
If it is a production server and if the option Tibor mentioned is enabled
then it is recommended to uncheck the option. This will degrade the
performance if the
file gets auto shunk and then grow automatically.
Thanks
Hari
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e$Uxm4njHHA.1388@.TK2MSFTNGP05.phx.gbl...
> Check if the autoshrink database option is set and also of you have any
> maint job or similar that perform shrink operations.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:CFF80442-D600-40E8-BCEE-3AF516192E34@.microsoft.com...
>

Log file size

What's the easiest way to truncate the log file size? After some test
operations it was increased to 420 mbytes, keeping in mind that I don't need
this log file and the real database size is only 20-30 mbytes it's nonsense.
Should I disable a full recovery mode or what?
Just D.How about just create a job that runs once a w or so that truncates the
log for you?
Have the job execute something like this:
BACKUP LOG [Your_Database_Name] WITH TRUNCATE_ONLY
DBCC SHRINKFILE ([Your_Database_Name]_log,50)
--Jason
"Just D." <no@.spam.please> wrote in message
news:CIC0e.420$k57.401@.fed1read07...
> What's the easiest way to truncate the log file size? After some test
> operations it was increased to 420 mbytes, keeping in mind that I don't
need
> this log file and the real database size is only 20-30 mbytes it's
nonsense.
> Should I disable a full recovery mode or what?
> Just D.
>|||http://msdn.microsoft.com/library/d... />
t_6rqr.asp
Describes the usage of recovery models in your database.
If you choose Simple Recovery, then your backup, restore, and recovery
models should be adjusted to accomodate the data you have.
Pick the recovery model that best enables you to maintain the level of
support and down time in case of emergency.
"Just D." wrote:

> What's the easiest way to truncate the log file size? After some test
> operations it was increased to 420 mbytes, keeping in mind that I don't ne
ed
> this log file and the real database size is only 20-30 mbytes it's nonsens
e.
> Should I disable a full recovery mode or what?
> Just D.
>
>|||Jason,
It's easy to do that, I actually created a scheduled job that works once per
day. But it doesn't help and this built-in shrink doesn't truncate the log
file. That's why I asked about any official method to truncate the log file.
One of the possible ideas was to disable the full database recovery, maybe
it stores all my steps one by one to roll back any time if I need that.
Just D.
"Jason Mauss gmail.com>" <jason.mauss@.<nospam> wrote in message
news:%23XCJFcJMFHA.3320@.TK2MSFTNGP15.phx.gbl...
> How about just create a job that runs once a w or so that truncates the
> log for you?
> Have the job execute something like this:
> BACKUP LOG [Your_Database_Name] WITH TRUNCATE_ONLY
> DBCC SHRINKFILE ([Your_Database_Name]_log,50)
> --Jason
> "Just D." <no@.spam.please> wrote in message
> news:CIC0e.420$k57.401@.fed1read07...
> need
> nonsense.
>|||Yes, the idea was correct - to switch from FULL recovery mode to Simple,
then shrink. I got a 1 mbyte file size only, that's all I wanted.

> "Jason Mauss gmail.com>" <jason.mauss@.<nospam> wrote in message
> news:%23XCJFcJMFHA.3320@.TK2MSFTNGP15.phx.gbl...
>|||Yeah, I was going to suggest going from FULL to SIMPLE for the recovery
model...sounds like you figured it out. I made my suggestion based on what
we do for our CRM database...which needs FULL recovery.
-Jason
"Just D." <no@.spam.please> wrote in message
news:fQG0e.517$k57.110@.fed1read07...
> Yes, the idea was correct - to switch from FULL recovery mode to Simple,
> then shrink. I got a 1 mbyte file size only, that's all I wanted.
>
don't
>