首页 > 代码库 > Sql Server ShrinkFile Error 解决方案

Sql Server ShrinkFile Error 解决方案

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 is 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 is 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 database 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

--创建表用于存储loginfo信息

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‘)

--查询虚拟日志最后一条状态 并判断为0时进行收缩

declare @status int select @status = Status from DixLoginfo where id = (select MAX(id) from DixLoginfo)   

if (@status =0)      

begin        

DBCC SHRINKFILE(DIX_log,TRUNCATEONLY)     

--删除7天以外的loginfo        

DELETE FROM DixLoginfo where datediff(day,createdate,getdate())>7      

end 

GO

 

--以下转自互联网 作者不祥

每一个数据库至少有一个日志文件,无论为事务日志定义多个少物理文件,SQL Server均视为一个连续的文件。该事务日志文件实际上由一系列的虚拟日志文件VLF来管理。虚拟日志文件的大小由SQL Server的总日志文件的大小决定。虚拟日志文件的物理结构图如下所示:

技术分享

当该日志文件收缩时,日志文件末端的未使用的VLF可以被删除。

在SQL server2000中,日志文件仅可以从日志文件的尾部收缩,但是微软已经纠正先前在SQL server 7.0中的问题,当你备份或截断日志时,SQL Server会自动将日志的活动部分转移到文件的始端,然后你运行DBCC SHRINKFILE或DBCC SHRINKDATABASE命令来释放未使用的空间。

如果要判断日志文件中有多少个虚拟日志文件,并且哪些虚拟日志文件是活动的,可以使用未归档命令DBCC命令:DBCC LOGINFO,其语法如下:

DBCC LOGINFO [ ( dbname ) ]

下面我们来通过一个示例来介绍DBCC LOGINFO的用法,同时查看日志收缩与截断的工作原理与实现机制。

首先,创建一个测试数据库,脚本如下:

USE MASTER;

Go

CREATE DATABASE logtest

GO

ALTER DATABASE logtest SET recovery FULL

GO

USE logtest;

GO

DBCC loginfo; GO

技术分享

从图中可以知道,活动的虚拟日志文件的状态(status)为2,logtest数据库有两个虚拟日志文件,当前仅有一个虚拟日志文件是活动的,现在创建一个表,然后填充一些行,以产生一些日志再查看日志的变化情况。

SELECT TOP 10000 * INTO bigOrderHeader

FROM AdventureWorks.Sales.SalesOrderHeader

GO

DBCC loginfo GO

技术分享

此时你将看到日志文件中有12个虚拟日志文件,并且它们都是活动的(状态都为2),现在,收缩日志然后再查看有什么变化?

DBCC SHRINKFILE (logtest_log) DBCC LOGINFO GO

技术分享

由于未对数据库进行备份,仍没有活动事务,SQL Server将认为你不需要保留日志的不活动部分,就将其删除。现在对数据库进行备份。

BACKUP DATABASE logtest

TO DISK = ‘f:\logtest.bak‘ GO 已为数据库‘logtest‘,文件‘logtest‘ (位于文件1 上)处理了440 页。

已为数据库‘logtest‘,文件‘logtest_log‘ (位于文件1 上)处理了2 页。

BACKUP DATABASE 成功处理了442 页,花费0.851 秒(4.246 MB/秒)。

现在再运行一些日志记录,重新检查日志的变化情况:

SET ROWCOUNT 1000

GO

BEGIN TRAN

DELETE bigOrderHeader

ROLLBACK TRAN

GO

SET ROWCOUNT 0

GO

DBCC loginfo

GO

技术分享

从上图注意到,现在有3个标记为2的活动事务,然后收缩该日志:

DBCC shrinkfile ( logtest_log)

GO

技术分享

无法收缩日志文件2 (logtest_log),因为所有的逻辑日志文件都在使用中。

(1 行受影响)

DBCC 执行完毕。如果DBCC 输出了错误信息,请与系统管理员联系。

从输出信息知道,该文件的上一个虚拟日志文件仍旧是活动的,因此发生了失败,SQL Server不能从文件的末端进行收缩,接着我们执行另一个事务,让日志继续增长:

SET ROWCOUNT 5000 GO BEGIN TRAN DELETE bigOrderHeader ROLLBACK TRAN GO SET ROWCOUNT 0 GO DBCC loginfo GO

技术分享

此时的日志也不能进行收缩,原因在于标记的虚拟日志用于还原操作,只有该日志做了备份或截断,其空间才可以被释放。

BACKUP LOG logtest WITH TRUNCATE_only

DBCC loginfo

GO

技术分享

现在作了标记的虚拟日志将不再需要(日志记录要么是截断的要么是已经备份至磁盘),日志文件可以进行收缩。

DBCC shrinkfile (logtest_log)

DBCC loginfo

GO

技术分享

(1 行受影响)

DBCC 执行完毕。如果DBCC 输出了错误信息,请与系统管理员联系。

(2 行受影响)

DBCC 执行完毕。如果DBCC 输出了错误信息,请与系统管理员联系。

Sql Server ShrinkFile Error 解决方案