Log file won't shrink

We're having a problem with one log file which refuses to be shrunk.
First off we have a problem with the fact that the log keeps growing way too
much, something that I have to look into, but besides that, I've tried to
shrink it down as it will end up eating up all the available disk space,
eventually.
I heard that DBCC SHRINKFILE will create dummy transactions in order to get
the log file to recycle, so that it will shrink, but it doesn't seem to want
to do that.
I've tried performing BACKUP LOG with various options and then trying to
shrink the log file, all to no avail.
Any hints or suggestions welcome as to how I can force this overgrown log
file back down to a more reasonable size.
Michael MacGregor
Database Architect
Are you using the syntax:
DBCC SHRINKFILE (fileName, TRUNCATEONLY)

>From BOL:
TRUNCATEONLY
Causes any unused space in the files to be released to the operating
system and shrinks the file to the last allocated extent, reducing the
file size without moving any data. No attempt is made to relocate rows
to unallocated pages. target_size is ignored when TRUNCATEONLY is used.
WITH
DBCC SHRINKFILE
(
{ 'file_name' | file_id }
{ [ , EMPTYFILE ]
| [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]
}
)
[ WITH NO_INFOMSGS ]
Michael MacGregor wrote:
> We're having a problem with one log file which refuses to be shrunk.
> First off we have a problem with the fact that the log keeps growing way too
> much, something that I have to look into, but besides that, I've tried to
> shrink it down as it will end up eating up all the available disk space,
> eventually.
> I heard that DBCC SHRINKFILE will create dummy transactions in order to get
> the log file to recycle, so that it will shrink, but it doesn't seem to want
> to do that.
> I've tried performing BACKUP LOG with various options and then trying to
> shrink the log file, all to no avail.
> Any hints or suggestions welcome as to how I can force this overgrown log
> file back down to a more reasonable size.
> Michael MacGregor
> Database Architect
|||Michael,
Is your log unrestricted? If it is then give it a maximum otherwise (under
SQL2005 and maybe SQL2000) it will keep on growing.
Chris
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:uNI12wd$GHA.4292@.TK2MSFTNGP02.phx.gbl...
> We're having a problem with one log file which refuses to be shrunk.
> First off we have a problem with the fact that the log keeps growing way
> too much, something that I have to look into, but besides that, I've tried
> to shrink it down as it will end up eating up all the available disk
> space, eventually.
> I heard that DBCC SHRINKFILE will create dummy transactions in order to
> get the log file to recycle, so that it will shrink, but it doesn't seem
> to want to do that.
> I've tried performing BACKUP LOG with various options and then trying to
> shrink the log file, all to no avail.
> Any hints or suggestions welcome as to how I can force this overgrown log
> file back down to a more reasonable size.
> Michael MacGregor
> Database Architect
>
|||Yes.
<tootsuite@.gmail.com> wrote in message
news:1162403350.700370.279630@.e64g2000cwd.googlegr oups.com...
> Are you using the syntax:
> DBCC SHRINKFILE (fileName, TRUNCATEONLY)
>
> TRUNCATEONLY
> Causes any unused space in the files to be released to the operating
> system and shrinks the file to the last allocated extent, reducing the
> file size without moving any data. No attempt is made to relocate rows
> to unallocated pages. target_size is ignored when TRUNCATEONLY is used.
> WITH
> DBCC SHRINKFILE
> (
> { 'file_name' | file_id }
> { [ , EMPTYFILE ]
> | [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]
> }
> )
> [ WITH NO_INFOMSGS ]
>
> Michael MacGregor wrote:
>
|||I'm actually not bothered by that, what is concerning me right now is that I
can't get it to shrink, the continual growth issue is not a concern right
now, as I believe I know what is causing that anyway and I have to deal with
that rather than restrict the log growth.
I have queries sys.databases and it is telling me that the
log_reuse_wait_desc is REPLICATION, but so far am going round in circles
trying to figure out why and what to do about it.
Michael MacGregor
Database Architect
|||Fixed it! There was a replication transaction still open.
Michael MacGregor
Database Architect
sql