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

Source: Internet
Author: User

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)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.