Cause Analysis and Solution for SQL Server failure to contract log files, SQL Cause Analysis

Cause Analysis and Solution for SQL Server failure to contract log files, SQL Cause Analysis

An error is reported when the server executes a job that compresses the log file size recently.

One of my batch log shrinking scripts

USE [master] GO/****** Object: StoredProcedure [dbo]. [ShrinkUser_DATABASESLogFile] Script Date: 01/05/2016 09:52:39 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ongoalter proc [dbo]. [ShrinkUser_DATABASESLogFile] asbegin declare @ dbname nvarchar (MAX) DECLARE @ SQL NVARCHAR (MAX) -- temporary TABLE save data CREATE TABLE # DataBaseServerData (ID INT IDENTITY (1, 1 ), dbname nvarchar (MAX), Log_Total_MB DECIMAL (18, 1) not null, Log_FREE_SPACE_MB DECIMAL (18, 1) not null) -- cursor declare @ itemCur CURSORSET @ itemCur = cursor for select name from SYS. [databases] WHERE [name] not in ('master', 'model', 'tempdb', 'msdb', 'reportserver', 'reportservertempdb', 'Distribution ') and state = 0 OPEN @ itemCurFETCH next from @ itemCur INTO @ DBNAMEWHILE @ FETCH_STATUS = 0 begin set @ SQL = n' USE ['+ @ DBNAME +']; '+ CHAR (10) +' DECLARE @ TotalLogSpace DECIMAL (18, 1) DECLARE @ FreeLogSpace DECIMAL (18, 1) DECLARE @ filename NVARCHAR (MAX) DECLARE @ CanshrinkSize bigint declare @ SQL1 nvarchar (MAX) SELECT @ TotalLogSpace = (SUM (CONVERT (dec (17, 2), sysfiles. size)/128) FROM dbo. sysfiles AS sysfiles WHERE [groupid] = 0 SELECT @ FreeLogSpace = (SUM (size-FILEPROPERTY (name, ''spaceused '')/128.0 FROM sys. database_files WHERE [type] = 1 SELECT @ filename = name FROM sys. database_files WHERE [type] = 1 SET @ CanshrinkSize = CAST (@ TotalLogSpace-@ FreeLogSpace) as bigint) SET @ SQL1 = ''use ['+ @ DBNAME +'] ''set @ SQL1 = @ SQL1 + ''dbcc SHRINKFILE ([''+ @ filename +''], ''+ CAST (@ CanshrinkSize + 1 as nvarchar (MAX) +'') ''exec (@ SQL1) 'exec (@ SQL) fetch next from @ itemCur INTO @ dbname end close @ itemCurDEALLOCATE @ itemCurSELECT * FROM [# DataBaseServerData] drop table [# DataBaseServerData] END

Fortunately, the error message is still comprehensive. Locate the relevant database based on the error message and execute dbcc loginfo.

Dbcc loginfo (n'cdb ')

It is found that there are only two VLF files that cannot be shrunk any more. Because it is a batch script, when one of the databases fails, subsequent libraries will not be shrunk.

You only need to add the VLF quantity of the database to determine the number of databases.

If this article is not well written, I would like to ask you to give your valuable comments. If you have any good solutions, please share them and everyone will learn and make progress together.

Articles you may be interested in:

• Useful SQL statements (deleting duplicate records and shrinking logs)
• SQL statement for SQL Server 2000 and SQL Server 2005 log shrinking (Batch)
• SQL2005 log shrinking method
• 2000/2005/2008 log shrinking method and log clearing method
• Method for shrinking Database Log Files (only applicable to mssql2005)