SQLSERVER事务日志备份

一、场景：

事务日志备份计划失败日志文件：**
Microsoft(R) Server Maintenance Utility (Unicode) Version 10.50.1600
Report was generated on "10.2.2.112".
Maintenance Plan: LogBackup
Duration: 00:00:14
Status: Warning: One or more tasks failed.
Details:
Back Up Database (Transaction Log) (10.2.2.112)
Backup Database on Local server connection
Databases: AIS20140721160444
Type: Transaction Log
Append existing
Task start: 2018-11-26T18:00:02.
Task end: 2018-11-26T18:00:16.
Failed:(-1073548784) Executing the query "BACKUP LOG [AIS20140721160444] TO DISK = N'E:\\KD_..." failed with the following error: "Write on "E:\\KD_SQL_BAK\\LogBackup\\AIS20140721160444\\AIS20140721160444_backup_2018_11_26_180002_4974166.trn" failed: 112(failed to retrieve text for this error. Reason: 15105)
Write on "E:\\KD_SQL_BAK\\LogBackup\\AIS20140721160444\\AIS20140721160444_backup_2018_11_26_180002_4974166.trn" failed: 112(failed to retrieve text for this error. Reason: 15105)
BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Command:EXECUTE master.dbo.xp_create_subdir N''E:\KD_SQL_BAK\LogBackup\AIS20140721160444''

GO
BACKUP LOG [AIS20140721160444] TO DISK = N''E:\KD_SQL_BAK\LogBackup\AIS20140721160444\AIS20140721160444_backup_2018_11_26_180002_4974166.trn'' WITH RETAINDAYS = 5, NOFORMAT, NOINIT, NAME = N''AIS20140721160444_backup_2018_11_26_180002_4964165'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N''AIS20140721160444'' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N''AIS20140721160444'' )
if @backupSetId is null begin raiserror(N''Verify failed. Backup information for database ''''AIS20140721160444'''' not found.'', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N''E:\KD_SQL_BAK\LogBackup\AIS20140721160444\AIS20140721160444_backup_2018_11_26_180002_4974166.trn'' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND

GO
二、环境：WINDOWSERVER2008R2+SQLSERVER2008R2 英文版本
三、解决步骤
1.查看数据库实体的事务日志文件达到60G.
2.数据库服务器曾经发生断电故障，可能由此造成日志文件Dirty
3. 处理方法：
USE AIS20140721160444;
GO

-- 1）Truncate the log by changing the database recovery model to SIMPLE.

ALTER DATABASE AIS20140721160444 SET RECOVERY SIMPLE;

GO

--2） Shrink the trun cated log file to 1 MB.

DBCC SHRINKFILE (SCM102SP2_log, 10);
GO

-- 3）Reset the database recovery model.

ALTER DATABASE AIS20140721160444 SET RECOVERY FULL;
GO
4. 重新运行事务日志备份作业，结果成功

---恢复内容结束---

转载于:https://www.cnblogs.com/24Key/p/10057040.html