I am using SQL Server version 7 with Service Pack 4. We have a full database
backup job which is executed everyday at 12:00 noon. Now everyday manually
we goto to the Property Of that particular database to turn the Truncate Log
On Checkpoint Option TRUE.
Then, from the query analyzer we put CHECKPOINT. And then again we turn the
option FALSE. All this things are done before 12:00 noon, say around 11:55 a
m. Now suddenly we are noticing that the physical size of the Log File is ge
tting reduced. Say first d
ay it was 1024 MB, next day after the same operation it has become 512 mb...
The Auto Shrink option is not set, it is FALSE. Can anyone tell me why is th
is happening ? Actually I don't want the physical size of the log file to re
duce.
Any help in this regard is highly appreciated.
Dipankar.Dipankar Ganguly wrote:
> Hi Friends,
> I am using SQL Server version 7 with Service Pack 4. We have a full
> database backup job which is executed everyday at 12:00 noon. Now
> everyday manually we goto to the Property Of that particular database
> to turn the Truncate Log On Checkpoint Option TRUE. Then, from the
> query analyzer we put CHECKPOINT. And then again we turn the option
> FALSE. (...)
There are better ways to do this
Check out below KB 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
Log File Grows too big
http://www.support.microsoft.com/?id=317375
Log file filling up
http://www.support.microsoft.com/?id=110139
Considerations for Autogrow and AutoShrink
http://www.support.microsoft.com/?id=315512
http://www.mssqlserver.com/faq/logs-shrinklog.asp
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks for the links. But please tell me why is this size reducing incident
happening ? Is there any other setting which is changed by someone and hence
it is behaving in that manner?|||I have had a lot of fun with this exact same problem. This happens when
someone does a DBCC SHRINKFILE(2, 512) on the log file. Once this has been
issued, it will always try to shrink to that size. There is no way of
turning this value off or back to the default of not shrinking. Even if you
manually grow the log file it will still shrink. A pain I know.
So the next question is how to get around it. If you want the log file to
be 1Gb, grow the log file to say 1.5GB, then shrink it to 1GB. Now it will
always shrink to 1Gb.
HTH
Barry McAuslin
Look inside your SQL Server files with SQL File Explorer.
Go to http://www.sqlfe.com for more information.
"Dipankar Ganguly" <dipankarganguly@.hotmail.com> wrote in message
news:005FFB0F-7DA2-4C62-8D47-302601D7207A@.microsoft.com...
> Thanks for the links. But please tell me why is this size reducing
incident happening ? Is there any other setting which is changed by someone
and hence it is behaving in that manner?sql
