Hi,

I ran log backup couple of times. but the log file size did not shrink.
Anyone knows why?

Thanks

Backing up the transaction log does not decrease the size of
the transaction log. It will truncate the log but people
often get confused by this term. Truncating the log refers
to marking active portions of the log that are no longer
needed for recovery as inactive. When portions of the log
are inactive, they can be reused for new transactions.
If you need to decrease the size of the log, you should use
dbcc shrinkfile. Refer to the following:
INF: Shrinking the Transaction Log in SQL Server 2000 with
DBCC SHRINKFILE
http://support.microsoft.com/?id=272318

-Sue

On Thu, 19 Jan 2006 16:22:51 -0500, "mecn"
<mecn2***@yahoo.com> wrote:

Show quoteHide quote

If log file is quite big, run a full back up(mdf and ldf). Detach your
database and attach only mdf file. SQL will create a small log file.

Is this correct to do? Someone pls suggest.

biswa.

No, just shrink the log file (if necessary) with DBCC SHRINKFILE.

If the log file doesn't shrink down small enough for you, you may need
to do a log backup, wait a while for the transactions to cycle around to
the front of the physical file(s) and then do another log backup to make
sure the none of the active portion of the log is at the end of the
physical file (see Transaction Log Physical Architecture
<http://msdn.microsoft.com/library/en-us/architec/8_ar_da2_3t2d.asp> and
other related topics in BOL).  Then you should be able to shrink the log
file to pretty much whatever size you want.  And all these operations
are ONLINE operations, meaning no users are interrupted while all this
is going on (except for a possible degradation in server performance
while you're messing around with disk I/O in the backup & shrinkfile ops).

But just because you can that doesn't mean you should... (see Tibor's
article on why you shouldn't shrink
<http://www.karaszi.com/SQLServer/info_dont_shrink.asp>).

Show quoteHide quote

Difference between 2000 and 2005. Maybe a bug?
Sql Server 2005 - SMS "Modify Table option" times out when trying to 'alter' very large table
SQLExpress
Rename Cluster Instance - New Hardware - Keep Name
Moving the SQL 2005 System Tables
linked server to Excel
SQL Server CPU Spikes
Object search query???
Database refresh
server feedback