SQL Server Shrinkfile Error Solution

Message
Executed as User:cn\hksqlpwv625sqlagent. Cannot shrink log file 2 (Dix_log) because the logical log file located at the end of the file are in use. [SQLSTATE 01000] (Message 9008)   DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528)   DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528)   cannot shrink log file 2 (Ltd_log) because the logical log file located at the end of the file are in Use. [SQLSTATE 01000] (Message 9008)   DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528)   Backup, file manipulation operations (such as ALTER DATABASE ADD file) and encryption changes on a DA Tabase must be serialized. Reissue the statement after the current backup or file manipulation operation is completed. [SQLSTATE 42000] (Error 3023) .  the step failed.

Use DIX

DBCC LOGINFO

--Create tables for storing loginfo information

CREATE TABLE Doxloginfo
(
ID int Identity,
Recoveryunitid int NULL,
Fileld int NULL,
FileSize int NULL,
Startoffset int NULL,
Status int NULL,
Parity int NULL,
CREATELSN int NULL,
CreateDate datetime default GETDATE ()
)

Insert into Dixloginfo (RECOVERYUNITID,FILELD,FILESIZE,STARTOFFSET,FSEQNO,STATUS,PARITY,CREATELSN)

EXEC (' DBCC loginfo ')

--Query the last state of the virtual log and decide to shrink 0 o'clock

DECLARE @status int Select @status = status from Dixloginfo where id = (select MAX (ID) from Dixloginfo)

if (@status =0)

Begin

DBCC Shrinkfile (dix_log,truncateonly)

--Delete loginfo outside of 7 days

DELETE from Dixloginfo where DateDiff (Day,createdate,getdate ()) >7

End

GO

--The following turn from the Internet author ominous

Each database has at least one log file, and SQL Server treats it as a contiguous file, regardless of the number of physical files that are defined for the transaction log. The transaction log file is actually managed by a series of virtual log files, VLF. The size of the virtual log file is determined by the size of the total log file for SQL Server. The physical structure diagram for the virtual log file is as follows:

When the log file shrinks, unused VLF at the end of the log file can be deleted.

In SQL server2000, the log file can only be shrunk from the tail of the log file, but Microsoft has corrected the previous problem in SQL Server 7.0, and when you back up or truncate the log, SQL Server automatically transfers the active portion of the log to the beginning of the file. Then you run the DBCC SHRINKFILE or DBCC SHRINKDATABASE command to free unused space.

If you want to determine how many virtual log files are in the log file, and which virtual log files are active, you can use the non-archived command DBCC command: DBCC LOGINFO, whose syntax is as follows:

DBCC Loginfo [(dbname)]

Let's take a sample to introduce the usage of DBCC LOGINFO and see how the log shrinkage and truncation work and implement.

First, create a test database with the following script:

Use MASTER;

Go

CREATE DATABASE Logtest

GO

ALTER DATABASE logtest SET Recovery full

GO

Use Logtest;

GO

DBCC Loginfo; GO

You can know that the status of the active virtual log file for the 2,logtest database has two virtual log files, currently only one virtual log file is active, now create a table, and then populate some rows to generate some logs to see the changes in the log.

SELECT TOP 10000 * into Bigorderheader

From AdventureWorks.Sales.SalesOrderHeader

GO

DBCC Loginfo GO

At this point you will see that there are 12 virtual log files in the log file, and they are all active (state 2), now, shrink the log and then look at what's changed?

DBCC SHRINKFILE (logtest_log) DBCC LOGINFO GO

Because the database is not backed up and there are still no active transactions, SQL Server will assume that you do not need to keep the inactive part of the log and delete it. Now make a backup of the database.

BACKUP DATABASE Logtest

to DISK = ' F:\logtest.bak ' GO has processed 440 pages for database ' logtest ', file ' Logtest ' (located on file 1).

2 pages have been processed for database ' logtest ', file ' Logtest_log ' (located on file 1).

BACKUP DATABASE successfully processed 442 pages and took 0.851 seconds (4.246 MB/s).

Now run some more log records and recheck the log changes:

SET ROWCOUNT 1000

GO

BEGIN TRAN

DELETE Bigorderheader

ROLLBACK TRAN

GO

SET ROWCOUNT 0

GO

DBCC Loginfo

GO

Notice that there are now 3 active transactions labeled 2, and then shrink the log:

DBCC Shrinkfile (Logtest_log)

GO

The log file 2 (logtest_log) cannot be shrunk because all the logical log files are in use.

(1 rows affected)

DBCC execution is complete. If DBCC outputs an error message, contact your system administrator.

From the output information, the last virtual log file of the file is still active, so failure occurs, SQL Server cannot shrink from the end of the file, and then we execute another transaction to keep the log growing:

SET ROWCOUNT go BEGIN TRAN DELETE bigorderheader ROLLBACK TRAN Go Set ROWCOUNT 0 go DBCC loginfo go

The log also cannot be shrunk at this point because the marked virtual log is used for the restore operation, and the space can be freed only if the log is backed up or truncated.

BACKUP LOG logtest with TRUNCATE_ONLY

DBCC Loginfo

GO

The virtual log that is now marked will no longer be required (logging is either truncated or has been backed up to disk) and the log file can be shrunk.

DBCC Shrinkfile (Logtest_log)

DBCC Loginfo

GO

(1 rows affected)

DBCC execution is complete. If DBCC outputs an error message, contact your system administrator.

(2 rows affected)

DBCC execution is complete. If DBCC outputs an error message, contact your system administrator.

SQL Server Shrinkfile Error Solution