log backup not getting rid of 'free' space, why?

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
peterLog backups do NOT shrink the file. They only allow the space to be reused
if the transactions are committed. You need to commit that old transaction
so you can free up the space in the log for more transactions. Then you can
use DBCC SHRINKFILE to shrink it later.
--
Andrew J. Kelly SQL MVP
"peter" <peter@.nospam.com> wrote in message
news:O57Buj6OGHA.3576@.TK2MSFTNGP15.phx.gbl...
> 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
>
>|||Andrew J. Kelly wrote:
> Log backups do NOT shrink the file. They only allow the space to be reused
> if the transactions are committed. You need to commit that old transaction
> so you can free up the space in the log for more transactions. Then you can
> use DBCC SHRINKFILE to shrink it later.
>
And in addition to Andrews response, you should find out why the logfile
suddenly has grown so much. If it's because of a maintenance job that
runs e.g. once a week, then you should leave the logfile as it is -
otherwise it will just grow again next week and that's waste of
ressources. If the grow is due to a "one time" operation (e.g. deletion
/update of a huge amount of data) then it might be ok to shrink the
logfile since it not very likely that this much space will be needed for
normal operation.
Regards
Steen|||Poor application side coding is creating such issue... As you said monitor
oldest transaction using DBCC OPENTRAN, use DBCC INPUTBUFFER(OLDESTSPID)
to get the Query which was executed and by whom from which Host address. Try
to log that and rewrite the application code.
To shrink file,
Run => DBCC sqlperf(logspace), list the % of logspace used
Run => backup transaction DBNAME with no_log
BOL =>NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and
truncates the log. This option frees space. Specifying a backup device is
unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY are
synonyms.
Run => DBCC sqlperf(logspace)
sp_helpdb 'dbname' => get logical log name of file.
DBCC SHRINKFILE ('DBNAME_Log',0) => this will release dsik space...
Thanks,
Sree
"Steen Persson (DK)" wrote:
> Andrew J. Kelly wrote:
> > Log backups do NOT shrink the file. They only allow the space to be reused
> > if the transactions are committed. You need to commit that old transaction
> > so you can free up the space in the log for more transactions. Then you can
> > use DBCC SHRINKFILE to shrink it later.
> >
> And in addition to Andrews response, you should find out why the logfile
> suddenly has grown so much. If it's because of a maintenance job that
> runs e.g. once a week, then you should leave the logfile as it is -
> otherwise it will just grow again next week and that's waste of
> ressources. If the grow is due to a "one time" operation (e.g. deletion
> /update of a huge amount of data) then it might be ok to shrink the
> logfile since it not very likely that this much space will be needed for
> normal operation.
> Regards
> Steen
>