0

my problem is really simply: shrink log file does not work. When I execute the maintenance plan (Database full backup and shrink database log) the execution terminate without problems or errors but the log file is used at the same percentage.

The Database is in AlwaysOn High Availability group. The group have 1 primary server (called BO1), 2 secondary servers (called BO2 e MI1) and I use SQL Server 2014.

After executed the backup I used the command 

DBCC SQLPERF(LOGSPACE);

I did a check my log database (Database called DM_AUT). This is the result: Log Size (MB) 10689.3 and Log Space Used (%) 90.90916.

These is the script that I used for the full backup:

DECLARE @preferredReplica int

SET @preferredReplica = (SELECT [master].sys.fn_hadr_backup_is_preferred_replica('DM_AUT'))

IF (@preferredReplica = 1)
  BEGIN
      BACKUP DATABASE [DM_AUT] TO  DISK = N'K:\xxx\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\DM_AUT.bak' WITH  COPY_ONLY, NOFORMAT, INIT,  NAME = N'DM_AUT_backup_2017_02_24_125045_4829954', SKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 10
 END
GO

declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'DM_AUT' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'DM_AUT' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''DM_AUT'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM  DISK = N'K:\xxx\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\DM_AUT.bak' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND

After the backup I executed the shrink command:

USE DM_AUT
BACKUP LOG BISYSTEM TO DISK='NUL:'
DBCC SHRINKFILE (DM_AUT_log,50)

At the end of these maintenance activities I checked the database log and the result is the same: Log Space Used (%) 90.90916.

I have other databases in this AlwaysOn group but the backup and shrink log file works fine (and are used the same scripts).

These are the database configurations: enter image description here

I would like to understand how to fix the shrink log task.

Thanks to all.

Michele
  • 23
  • 7
  • look out this link :http://stackoverflow.com/questions/7193445/dbcc-shrinkfile-on-log-file-not-reducing-size-even-after-backup-log-to-disk and this https://msdn.microsoft.com/en-us/library/ms189493.aspx – TheGameiswar Feb 24 '17 at 13:55
  • Hi TheGameiswar I am not sure that are similar to my problem. Shrink log file works fine if the database is not in Always On group but it does not work fine when I add the database at the Always on group. In these 2 examples the Always on group is not used. – Michele Feb 27 '17 at 11:20

1 Answers1

0

Shrink is not the solution, when log file has been grown abnormally. You should check the Factors that can delay log truncation.

For more information to understand the structre of a log file and when SQL Server free ups the log file, I suggest to read this article:

SQL Server Transaction Log Architecture and Management Guide

Meyssam Toluie
  • 1,061
  • 7
  • 21