Prev: SQL 2008 on SBS 2003
Next: Delete large table in chunks to minimise log file growth
From: John Brown on 10 Apr 2010 14:17
Hello All,

If you set the initial size of the log file to X when you create it, the
nyou can never shrink the log file to below X. Suppose you decide that X is
too large. Is there a way to reduce the minimum size of the log file?

One way I can think of is:

Detach the database, rename (or delete) the log file, and reattach the
database. A minimal log file will be created and you can size it however you
want.

I consider that a little drastic. Is there another way?

Regards,
Alias John Brown.
From: Tom Cooper on 10 Apr 2010 14:24
You can shrink the log file to be smaller than the original size of the
file. To do so, you must use DBCC SHRINKFILE, not DBCC SHRINKDATABASE.
DBCC SHRINKFILE can shrink log or data files to be smaller than the original
size. See the DBCC SHRINKFILE topic in BOL.

Tom

"John Brown" <JohnBrown(a)discussions.microsoft.com> wrote in message
news:AD4C0D5D-5E51-485C-8BED-BE2F0CBFE59C(a)microsoft.com...
> Hello All,
>
> If you set the initial size of the log file to X when you create it, the
> nyou can never shrink the log file to below X. Suppose you decide that X
> is
> too large. Is there a way to reduce the minimum size of the log file?
>
> One way I can think of is:
>
> Detach the database, rename (or delete) the log file, and reattach the
> database. A minimal log file will be created and you can size it however
> you
> want.
>
> I consider that a little drastic. Is there another way?
>
> Regards,
> Alias John Brown.

From: John Brown on 10 Apr 2010 20:04


"Tom Cooper" wrote:

> You can shrink the log file to be smaller than the original size of the
> file. To do so, you must use DBCC SHRINKFILE, not DBCC SHRINKDATABASE.
> DBCC SHRINKFILE can shrink log or data files to be smaller than the original
> size. See the DBCC SHRINKFILE topic in BOL.
>
> Tom

It works. My silly mistake. I was executing the following code that the
author says is guaranteed to reduce the log file to the smallest size
possible:

http://blog.sqlauthority.com/2006/12/30/sql-server-shrinking-truncate-log-file-log-full/

USE DatabaseName
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO

Reference : Pinal Dave (http://www.SQLAuthority.com), BOL

But it was not working because I used the database name (not the log file)
in the second "dbcc shrinkfile" command.

Thanks,
Alias John Brown.

 | 
Pages: 1
Prev: SQL 2008 on SBS 2003
Next: Delete large table in chunks to minimise log file growth