今天查看Job的History，發(fā)現(xiàn)Job 運(yùn)行失敗，錯(cuò)誤信息是：“The transaction log for database 'xxxx' is full due to 'ACTIVE_TRANSACTION'.”

錯(cuò)誤消息表明：數(shù)據(jù)庫(kù)的事務(wù)日志文件空間耗盡，log 文件不能再存儲(chǔ)新的transaction log。

SQL Server將事務(wù)日志文件在邏輯上劃分為多個(gè)VLF（Virtual Log Files），將這些VLF組成一個(gè)的環(huán)形結(jié)構(gòu)，以VLF為重用單元。如果一個(gè)VLF 中存在Active Transaction，那么該VLF就不能被截?cái)嗪椭赜谩Ｈ绻聞?wù)日志文件沒(méi)有可用的VLF，那么SQL Server就不能處理新增的事務(wù)，并拋出事務(wù)日志文件耗盡的錯(cuò)誤消息。

那為什么Active Transaction 會(huì)導(dǎo)致事務(wù)日志文件耗盡？

1，如果數(shù)據(jù)庫(kù)的事務(wù)日志文件太大，將整個(gè)Disk Space耗盡，那么就要考慮是什么原因造成事務(wù)日志文件大量增長(zhǎng)，定期做事務(wù)日志備份能夠截?cái)嗍聞?wù)日志文件。

2，如果數(shù)據(jù)庫(kù)的事務(wù)日志文件本身不是很大，可能的原因是SQL Server 無(wú)法為事務(wù)日志文件分配Disk Space。

3，查看數(shù)據(jù)庫(kù)中活動(dòng)的事務(wù)，如果是由于一個(gè)事務(wù)運(yùn)行時(shí)間太長(zhǎng)，沒(méi)有關(guān)閉，導(dǎo)致事務(wù)日志的VLF不能重用，那么必須修改應(yīng)用程序。

如果數(shù)據(jù)庫(kù)中某一個(gè) Transaction 運(yùn)行的時(shí)間太長(zhǎng)，導(dǎo)致其他transaction雖然被commint，但是其占用的VLF仍然被標(biāo)記為Active，不能被truncate和reuse，當(dāng)log文件中沒(méi)有可用的VLF，而SQL Server又要處理新增的Transaction時(shí)，SQL Server就會(huì)報(bào)錯(cuò)。

step1，查看事務(wù)日志文件的大小

查看日志文件的 size_gb 和?max_size_gb 字段，發(fā)現(xiàn)該事務(wù)日志文件的大小沒(méi)有達(dá)到最大值，并且事務(wù)日志文件占用的Disk Space并不是很大，我猜想，很可能是日志文件所在的Disk Space 被使用殆盡，沒(méi)有剩余的free space。

select db.name as database_name,db.is_auto_shrink_on,db.recovery_model_desc,mf.file_id,mf.type_desc,mf.name as logic_file_name,mf.size*8/1024/1024 as size_gb,mf.physical_name,iif(mf.max_size=-1,-1,mf.max_size*8/1024/1024) as max_size_gb,mf.growth,mf.is_percent_growth,mf.state_desc from sys.databases db inner join sys.master_files mf on db.database_id=mf.database_id where mf.size*8/1024/1024>1 -- GBand db.name='database name'and mf.type=0 order by size_gb desc

step2，查看Disk的Free Space

查詢(xún)結(jié)果顯示，D盤(pán)空間僅僅剩下9MB，正是事務(wù)日志文件所在的Disk。

exec sys.xp_fixeddrives

step3，Disk Space 用盡，必須想辦法將大的數(shù)據(jù)文件壓縮，或者將事務(wù)日志文件截?cái)唷?/p>

由于數(shù)據(jù)庫(kù)的恢復(fù)模式是simple，會(huì)自動(dòng)截?cái)嗍聞?wù)日志文件，因此，最大的可能是disk space耗盡。

1，查看數(shù)據(jù)庫(kù)空間的使用情況

exec sys.sp_spaceused

unallocated space 空閑很大，必須壓縮數(shù)據(jù)庫(kù)，以釋放disk space

2，收縮（shrink）數(shù)據(jù)庫(kù)文件

use target_database_name goselect file_id,type,type_desc,data_space_id,name,size*8/1024/1024 as size_gb,growth,is_percent_growth,physical_name,max_size from sys.database_filesdbcc shrinkfile('file logcial name',0,notruncate) dbcc shrinkfile('file logcial name',target_size_mb,truncateonly)

3，對(duì)數(shù)據(jù)庫(kù)中的 table 和 index 壓縮存儲(chǔ)
3.1， 查看數(shù)據(jù)庫(kù)中，占用存儲(chǔ)空間非常大的table；

use target_database_name goselect t.name,sum(case when ps.index_id<2 then ps.row_count else 0 end) as row_count,sum(ps.reserved_page_count)*8/1024/1024 as reserved_gb,sum(ps.used_page_count)*8/1024/1024 as used_gb,sum( case when ps.index_id<2then ps.in_row_data_page_count+ps.lob_used_page_count+ps.row_overflow_used_page_countelse 0 end)*8/1024/1024 as data_used_gb,sum(case when ps.index_id>=2 then ps.in_row_data_page_count+ps.lob_used_page_count+ps.row_overflow_used_page_countelse 0 end)*8/1024/1024 as index_used_gb from sys.dm_db_partition_stats ps inner join sys.tables ton ps.object_id=t.object_id group by t.object_id, t.name order by used_gb desc

3.2， 查看table及其Index是否被壓縮過(guò)

select p.partition_id,object_name(p.object_id) as ObjectName,p.index_id,p.rows,p.data_compression,p.data_compression_desc,au.Type,au.Type_desc,au.total_pages,au.used_pages,au.data_pages from sys.partitions p inner join sys.allocation_units auon p.partition_id=au.container_id where p.object_id=object_id('[dbo].[table_name]',N'U')

3.3，估計(jì)壓縮能夠節(jié)省的存儲(chǔ)空間

exec sys.sp_estimate_data_compression_savings @schema_name='dbo',@object_name='table_name',@index_id=1,@partition_number=null,@data_compression ='page'

3.4， 對(duì)table及其index進(jìn)行數(shù)據(jù)壓縮
對(duì)table 及其index 進(jìn)行 rebuild，SQL Server將重新分配存儲(chǔ)空間，慎重：rebuild 反而會(huì)增加數(shù)據(jù)庫(kù)占用的存儲(chǔ)空間。在數(shù)據(jù)壓縮存儲(chǔ)之后，必須shrink 數(shù)據(jù)庫(kù)文件，才能釋放數(shù)據(jù)庫(kù)所占用的存儲(chǔ)空間，增加Disk的Free Space。

alter table [dbo].table_name rebuild with(data_compression=page)alter index index_name on [dbo].table_name rebuild with(data_compression=page)

4，增加事務(wù)日志文件

參考：《The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'》

?

Appendix：《Log Reuse Waits Explained: ACTIVE_TRANSACTION》

SQL Server will return a log_reuse_wait_desc value of ACTIVE_ TRANSACTION if it runs out of virtual log files because of an open transaction. Open transactions prevent virtual log file reuse, because the information in the log records for that transaction might be required to execute a rollback operation.

To prevent this log reuse wait type, make sure you design you transactions to be as short lived as possible and never require end user interaction while a transaction is open.

To resolve this wait, you have to commit or rollback all transactions. The safest strategy is to just wait until the transactions finish themselves. Well-designed transactions are usually short lived, but there are many reasons that can turn a normal transaction into a log running one. If you cannot afford to wait for an extra-long running transaction to finish, you might have to kill its session. However, that will cause that transaction to be rolled back. Keep this in mind when designing your application and try to keep all transactions as short as possible.

One common design mistake that can lead to very long running transactions is to require user interaction while the transaction is open. If the person that started the transaction went to lunch while the system is waiting for a response, this transaction can turn into a very-long-running transaction. During this time other transactions, if they are not blocked by this one, will eventually fill up the log and cause the log file to grow.

作者：悅光陰 出處：http://www.cnblogs.com/ljhdo/ 本文版權(quán)歸作者和博客園所有，歡迎轉(zhuǎn)載，但未經(jīng)作者同意，必須保留此段聲明，且在文章頁(yè)面醒目位置顯示原文連接，否則保留追究法律責(zé)任的權(quán)利。 分類(lèi): TroubleShooting





本文轉(zhuǎn)自悅光陰博客園博客，原文鏈接：http://www.cnblogs.com/ljhdo/p/5535750.html，如需轉(zhuǎn)載請(qǐng)自行聯(lián)系原作者

總結(jié)

以上是生活随笔為你收集整理的The transaction log for database 'xxxx' is full due to 'ACTIVE_TRANSACTION'的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。