Transaction log shrinking... weird behavior?!?!?!?

[jeffreyv]

Hi!

I'm studying to have my MCSE 70-228 certification and I'm trying some
things with backing up transaction logs and shrinking it.

Here's what I do:

There is no activity in the database by the way.

I have a transaction log of 1792 kb...

I do the following command:

BACKUP LOG TestDB TO TestDBBackup
DBCC SHRINKFILE ('TestDB_Log',0)

The transaction log is now 1280 kb

I do the same command and finally my transaction log is now 1024kb...

Any idea why it didn't shrink it at 1024 kb the first time?

Thanks!

Jeff

[Erland Sommarskog]

(je******@globetrotter.net) writes:

I'm studying to have my MCSE 70-228 certification and I'm trying some
things with backing up transaction logs and shrinking it.

Here's what I do:

There is no activity in the database by the way.

I have a transaction log of 1792 kb...

I do the following command:

BACKUP LOG TestDB TO TestDBBackup
DBCC SHRINKFILE ('TestDB_Log',0)

The transaction log is now 1280 kb

I do the same command and finally my transaction log is now 1024kb...

Any idea why it didn't shrink it at 1024 kb the first time?

Dunno really. Books Online says that if you don't specify a
target size, the file shrinks to its default size. But I seem to
recall that this is not the true story.

In any case, I think it is a good idea to always specify a target size,
particularly with a transaction log. The log file may have swollen to,
say 1GB, because of some unusual operation. Shrinking it all the way
down to 1024 kB would be a bad idea, because the file grow again, and
the autogrows costs in performance. So shrinking to a reasonable target
size is a better idea.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp