Log file size

27 Jan 2006 3:27 AM

krygim

I find that the following commands successfully decrease the size of the log
file:
- Backup Log MyDB With Truncate_Only
- DBCC SHRINKDATABASE (MyDB)

However the following commands (exec one after one) can't decrease the size
of the log file:
- DBCC SHRINKDATABASE (MyDb, TruncateOnly)
- DBCC SHRINKDATABASE (MyDB)

Can anybody explain why?

Thanks,
KM

27 Jan 2006 4:02 AM

Dan Guzman

You can use DBCC SHRINKFILE to shrink a log file. See the Books Online for
usage information.

--
Hope this helps.

Dan Guzman
SQL Server MVP

Show quote

"krygim" <kry***@hotmail.com> wrote in message
news:edS35FvIGHA.3056@TK2MSFTNGP09.phx.gbl...
>I find that the following commands successfully decrease the size of the
>log
> file:
> - Backup Log MyDB With Truncate_Only
> - DBCC SHRINKDATABASE (MyDB)
>
> However the following commands (exec one after one) can't decrease the
> size
> of the log file:
> - DBCC SHRINKDATABASE (MyDb, TruncateOnly)
> - DBCC SHRINKDATABASE (MyDB)
>
> Can anybody explain why?
>
> Thanks,
> KM
>
>

27 Jan 2006 4:06 AM

KenJ

the TLog is made up internally of virtual log files. truncating the
log frees up the virtual files so shrinkdatabase/shrinkfile can take
back the space. if you run shrinkdatabase/shrinkfile without first
freeing up the virtual files, the space those virtual files take up
cannot be reallocated to the OS.

BOL has a GREAT explanation at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_1uzr.asp