Hi,
My DB file (.ldf) is only 3 Mb, while the log file's size is almost 30 Mb,
how can I shrink or clear this log file to make it small?
Thanks!30mb is not really big, to adjust size do the following.
Open up Enterprise Manager scroll down to the database in question.
Right click db - click on properties - choose the option tab.
In the recovery panel make sure it's set to Simple rather than Full.
Apply changes and exit db properties.
Then you can now manually shrink the database via the All Tasks menu.
Have fun|||Thank u very much, it works as I expected!
"Greentko" <greentko@.hotmail.com> wrote in message
news:eFK2h19sDHA.3236@.tk2msftngp13.phx.gbl...
> 30mb is not really big, to adjust size do the following.
> Open up Enterprise Manager scroll down to the database in question.
> Right click db - click on properties - choose the option tab.
> In the recovery panel make sure it's set to Simple rather than Full.
> Apply changes and exit db properties.
> Then you can now manually shrink the database via the All Tasks menu.
> Have fun
>|||As Greenko say, 30 MB is nothing. It might just grow back the same size next time you do some batch
operation. However:
Check out below KB articles:
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/default.aspx?scid=kb;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=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
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"VVIPI" <vvipi@.hotmail.com> wrote in message news:uUwMbW8sDHA.628@.tk2msftngp13.phx.gbl...
> Hi,
> My DB file (.ldf) is only 3 Mb, while the log file's size is almost 30 Mb,
> how can I shrink or clear this log file to make it small?
> Thanks!
>
>
Showing posts with label clear. Show all posts
Showing posts with label clear. Show all posts
Wednesday, March 21, 2012
Monday, March 12, 2012
Log File (LDF) ?? (HELP)
Is there any way to clear out the LDF file or reduce in size?
thank you in advance fro any help,
Michael KintnerHi
DBCC SHRINKFILE. For more details please see BOL
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
"Michael Kintner" <nospam@.nospam.com> wrote in message
news:13dvsktmk6afhb3@.corp.supernews.com...
> Is there any way to clear out the LDF file or reduce in size?
> thank you in advance fro any help,
> Michael Kintner
>|||On Thu, 6 Sep 2007 08:29:16 -0400, "Michael Kintner"
<nospam@.nospam.com> wrote:
>Is there any way to clear out the LDF file or reduce in size?
>thank you in advance fro any help,
>Michael Kintner
First, what recovery model (Simple, Full, Bulk-Logged) is the database
using? If Simple, it will clear out by itself. For the others it
will only be cleared with the log is backed up. If no log backups are
running then it simply grows, and grows, and grows...
As for reducing the size, DBCC SHRINKFILE will do the job. However
shrinking the log file should be a VERY rare event. Shrinking the
file, only to have it grow again, is bad for performance. Allocating
more space is a significant delay for the transaction that causes it,
and physical file fragmentation is a common side effect. To over
simplify things a bit, set the log size large enough and backup the
log regularly to keep it from getting full.
Roy Harvey
Beacon Falls, CT|||In article <hn50e39j2sjnksnka0m5jem0qq12483ep0@.4ax.com>,
roy_harvey@.snet.net says...
> On Thu, 6 Sep 2007 08:29:16 -0400, "Michael Kintner"
> <nospam@.nospam.com> wrote:
> >Is there any way to clear out the LDF file or reduce in size?
> >
> >thank you in advance fro any help,
> >Michael Kintner
> First, what recovery model (Simple, Full, Bulk-Logged) is the database
> using? If Simple, it will clear out by itself. For the others it
> will only be cleared with the log is backed up. If no log backups are
> running then it simply grows, and grows, and grows...
> As for reducing the size, DBCC SHRINKFILE will do the job. However
> shrinking the log file should be a VERY rare event. Shrinking the
> file, only to have it grow again, is bad for performance. Allocating
> more space is a significant delay for the transaction that causes it,
> and physical file fragmentation is a common side effect. To over
> simplify things a bit, set the log size large enough and backup the
> log regularly to keep it from getting full.
> Roy Harvey
> Beacon Falls, CT
>
Just to add a bit -- SHRINKFILE can also be thwarted by open long-
running transactions since the shrink is done in units of VLF's and
won't occur if there is an open active transaction within the virtual
log. In order to get back space recently -- a 5G database had an 8G
logfile -- I had to backup the log, truncate the logfile and then shrink
to a more reasonable target. I figured 25% was a good guesstimate and of
that only 14M was actually used. Why so out of whack? -- try running an
app w/ no BACKUP LOG for over 2 years. The vendor never bothered to tell
my predecessor the basics of configuring sql and it just grew unbounded.
--
Graham (Pete) Berry
PeteBerry@.Caltech.edu
--
Graham (Pete) Berry
PeteBerry@.Caltech.edu
thank you in advance fro any help,
Michael KintnerHi
DBCC SHRINKFILE. For more details please see BOL
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
"Michael Kintner" <nospam@.nospam.com> wrote in message
news:13dvsktmk6afhb3@.corp.supernews.com...
> Is there any way to clear out the LDF file or reduce in size?
> thank you in advance fro any help,
> Michael Kintner
>|||On Thu, 6 Sep 2007 08:29:16 -0400, "Michael Kintner"
<nospam@.nospam.com> wrote:
>Is there any way to clear out the LDF file or reduce in size?
>thank you in advance fro any help,
>Michael Kintner
First, what recovery model (Simple, Full, Bulk-Logged) is the database
using? If Simple, it will clear out by itself. For the others it
will only be cleared with the log is backed up. If no log backups are
running then it simply grows, and grows, and grows...
As for reducing the size, DBCC SHRINKFILE will do the job. However
shrinking the log file should be a VERY rare event. Shrinking the
file, only to have it grow again, is bad for performance. Allocating
more space is a significant delay for the transaction that causes it,
and physical file fragmentation is a common side effect. To over
simplify things a bit, set the log size large enough and backup the
log regularly to keep it from getting full.
Roy Harvey
Beacon Falls, CT|||In article <hn50e39j2sjnksnka0m5jem0qq12483ep0@.4ax.com>,
roy_harvey@.snet.net says...
> On Thu, 6 Sep 2007 08:29:16 -0400, "Michael Kintner"
> <nospam@.nospam.com> wrote:
> >Is there any way to clear out the LDF file or reduce in size?
> >
> >thank you in advance fro any help,
> >Michael Kintner
> First, what recovery model (Simple, Full, Bulk-Logged) is the database
> using? If Simple, it will clear out by itself. For the others it
> will only be cleared with the log is backed up. If no log backups are
> running then it simply grows, and grows, and grows...
> As for reducing the size, DBCC SHRINKFILE will do the job. However
> shrinking the log file should be a VERY rare event. Shrinking the
> file, only to have it grow again, is bad for performance. Allocating
> more space is a significant delay for the transaction that causes it,
> and physical file fragmentation is a common side effect. To over
> simplify things a bit, set the log size large enough and backup the
> log regularly to keep it from getting full.
> Roy Harvey
> Beacon Falls, CT
>
Just to add a bit -- SHRINKFILE can also be thwarted by open long-
running transactions since the shrink is done in units of VLF's and
won't occur if there is an open active transaction within the virtual
log. In order to get back space recently -- a 5G database had an 8G
logfile -- I had to backup the log, truncate the logfile and then shrink
to a more reasonable target. I figured 25% was a good guesstimate and of
that only 14M was actually used. Why so out of whack? -- try running an
app w/ no BACKUP LOG for over 2 years. The vendor never bothered to tell
my predecessor the basics of configuring sql and it just grew unbounded.
--
Graham (Pete) Berry
PeteBerry@.Caltech.edu
--
Graham (Pete) Berry
PeteBerry@.Caltech.edu
Log File (LDF) ?? (HELP)
Is there any way to clear out the LDF file or reduce in size?
<nospam@.nospam.comwrote:
First, what recovery model (Simple, Full, Bulk-Logged) is the database
using? If Simple, it will clear out by itself. For the others it
will only be cleared with the log is backed up. If no log backups are
running then it simply grows, and grows, and grows...
thank you in advance fro any help,
Michael KintnerHi
DBCC SHRINKFILE. For more details please see BOL
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
"Michael Kintner" <nospam@.nospam.comwrote in message
news:13dvsktmk6afhb3@.corp.supernews.com...
Quote:
Originally Posted by
Is there any way to clear out the LDF file or reduce in size?
>
thank you in advance fro any help,
Michael Kintner
>
<nospam@.nospam.comwrote:
Quote:
Originally Posted by
>Is there any way to clear out the LDF file or reduce in size?
>
>thank you in advance fro any help,
>Michael Kintner
First, what recovery model (Simple, Full, Bulk-Logged) is the database
using? If Simple, it will clear out by itself. For the others it
will only be cleared with the log is backed up. If no log backups are
running then it simply grows, and grows, and grows...
As for reducing the size, DBCC SHRINKFILE will do the job. However
shrinking the log file should be a VERY rare event. Shrinking the
file, only to have it grow again, is bad for performance. Allocating
more space is a significant delay for the transaction that causes it,
and physical file fragmentation is a common side effect. To over
simplify things a bit, set the log size large enough and backup the
log regularly to keep it from getting full.
Roy Harvey
Beacon Falls, CT
Subscribe to:
Posts (Atom)