Hi everyone, I was hoping someone could help me.

I am having problems with my transaction log not shrinking after a log
backup has taken place. The log backups are usually of size 400 Mb at
7:00am and generate further 6 MB (approx.) log backups each half hour.
The log file is usually of size 300 MB after each log backup has
occured. For some reason, the log backup for 7:00am today was at 26.8
GB and the log file has not shrunk - remaining at 28.3 BG.

I have set up a database backup to take place at 2am and log backups
from 7am to 11pm every half hour:


-- Job 1: Full backup scheduled on Monday at 2:00 AM
DECLARE @str varchar(200)
SET @str =
'BACKUP DATABASE ZestLive TO DISK=''R:\BACKUP\ZestFull\ZestLive'
+ '_'
+ RTRIM(CONVERT(varchar, GETDATE(), 112))
+ '_'
+ STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varchar, GETDATE(), 113)), 12),
1, 5), 3, 1, '')
+ '.BAK'' '
EXEC (@str)
-----------------------------------------------------


and


-- Job 2: Transactional backup scheduled every hour between 7AM and
11PM inclusive
DECLARE @str varchar(200)
SET @str =
'BACKUP LOG ZestLive TO DISK=''R:\BACKUP\ZestTLog\ZestLive'
+ '_'
+ RTRIM(CONVERT(varchar, GETDATE(), 112))
+ '_'
+ STUFF(SUBSTRING(RIGHT(RTRIM(CONVERT(varchar, GETDATE(), 113)), 12),
1, 5), 3, 1, '')
+ '.TRN'' '
EXEC (@str)
-----------------------------------------------------


In addition, the Transaction log space within SQL identifies:


----------------------
Total: 27669.55 MB
Used: 166.15 MB
Free: 27503.4 MB
----------------------


I have also run the DBCC OPENTRAN command to identify any open
transactions that might be preventing the log file from shrinking:


------------------------------
DBCC OPENTRAN

Transaction information for database 'ZestLive'.
Replicated Transaction Information:
Oldest distributed LSN : (54590:259717:1)
Oldest non-distributed LSN : (0:0:0)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
------------------------------


Shouldn't a log backup shrink the log file down and get rid of all that
free space i.e. 27503.4 MB?

I havent changed anything interms of the backup schedule. Why is this
happening now?

Many thanks!

cheers
peter

When you Truncate the Log File it does not shrink the physical file -
only the logical file.

Lookup DBCC ShrinkFile in Books OnLine for further information.

HTH

Barry