茫茫網海中的冷日
         
茫茫網海中的冷日
發生過的事,不可能遺忘,只是想不起來而已!
 恭喜您是本站第 1664613 位訪客!  登入  | 註冊
主選單

Google 自訂搜尋

Goole 廣告

隨機相片
PIMG_00187.jpg

授權條款

使用者登入
使用者名稱:

密碼:


忘了密碼?

現在就註冊!

DB研討會 : [轉貼] 交易記錄檔已滿、爆掉;截斷交易記錄檔

發表者 討論內容
冷日
(冷日)
Webmaster
  • 註冊日: 2008/2/19
  • 來自:
  • 發表數: 15766
[轉貼] 交易記錄檔已滿、爆掉;截斷交易記錄檔

交易記錄檔已滿、爆掉;截斷交易記錄檔。(The transaction log for database 'DB1' is full. ;Transaction Log Truncation)

本文僅討論當發生交易記錄檔已滿、爆掉時的災難處理方式,並未討論如何避免發生交易記錄檔已滿的作法,請參考「管理交易記錄」等相關主題。
http://technet.microsoft.com/zh-tw/library/ms345382.aspx
如何避免 SQL Server 資料庫的交易記錄檔超出預期大小
http://support.microsoft.com/kb/873235/zh-tw

若資料庫因故造成交易記錄檔已滿、爆掉,將導致無法執行新增、刪除等作業。

遇到的錯誤訊息:




訊息 9002,層級 17,狀態 2,行 2
資料庫 'db1' 的交易記錄已滿。如果要了解為何無法重複使用記錄中的空間,請參閱 sys.databases 中的 log_reuse_wait_desc 資料行。
Msg 9002, Level 17, State 2, Line 2
The transaction log for database 'DB1' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases




請參考下圖所示:

01_在查詢編輯器遇到的錯誤,Error9002









若使用的版本是 SQL Server 2000、2005、2008
可以使用 SSMS 圖型介面管理工具,直接截斷、清空交易記錄檔的內容。流程如下:
將資料庫切換為「簡單復原模式」,便會自動截斷、清空交易記錄。
若決定要備份交易記錄檔(*.ldf),請再切換回「完整復原模式」。


步驟如下:
步驟01. 執行SSMS,在「物件總管」,展開「資料庫」,選擇指定的資料庫,滑鼠右鍵,選擇:「屬性」。
步驟02. 在「資料庫屬性」視窗,在左邊「選取頁面」頁籤,點選:「選項」 。
步驟03. 在右邊的「復原模式」方塊,將原來設定的「完整」,改選取為:「簡單」。 請參考下圖所示:

04_切換為「簡單復原模式」

步驟04. 點選「確定」,完成動截斷、清空交易記錄。
步驟05. 重複執行步驟01~04,但將此資料庫調整為「完整復原模式」。

或是利用以下的 Transact-SQL 陳述式:




/*
當資料庫使用簡單復原模式時,便會自動截斷交易記錄。
如果您必須從資料庫中移除記錄備份鏈結,請切換到簡單復原模式。
*/
--01 將資料庫 Northwind 切換為「簡單復原模式」,便會自動截斷交易記錄。
ALTER DATABASE Northwind
SET RECOVERY SIMPLE
GO
--02 若決定要備份交易記錄檔(*.ldf),再將資料庫 Northwind 切換回「完整復原模式」
ALTER DATABASE Northwind
SET RECOVERY FULL
GO




造成無法截斷的原因可能很多,例如:仍有交易在執行中等。所以若您發覺無法截斷交易記錄檔,請再度執行上述的動作或是Transact-SQL 陳述式,或許就可以截斷交易記錄檔。

若執行數次後都無法截斷交易記錄,建議您可以查詢系統檢視 sys.databases 中的 log_reuse_wait_desc 資料行的描述說明,找出為何無法截斷的可能因素。



若使用的版本是 SQL Server 2000、2005,可以任選以下Transact-SQL 陳述式,來截斷、清空交易記錄檔的內容,但是 SQL Server 2008 已經不再支援以下的寫法:




--01 使用 BACKUP LOG ... WITH NO_LOG
BACKUP LOG Northwind
WITH NO_LOG
GO
--02 或是,使用 BACKUP LOG ... WITH TRUNCATE_ONLY
BACKUP LOG Northwind
WITH TRUNCATE_ONLY
GO



BACKUP LOG 陳述式的 NO_LOG 和 TRUNCATE_ONLY 選項會中斷記錄鏈,
因為它們會在沒有建立備份副本情況下,移除記錄的非使用中部分。
在下次進行完整或差異資料庫備份之前,無法保護資料庫免於媒體失敗。

下一版的 Microsoft SQL Server 將不再提供此功能。
請勿在新的開發工作中使用此功能,並且儘速修改使用此功能的應用程式。

以下是使用 BACKUP LOG ... WITH NO_LOG 或 BACKUP LOG ... WITH TRUNCATE_ONLY 在 SQL Server 2008 上執行所產生的錯誤訊息:



--01 在SQL Server 2008上,使用 BACKUP LOG ... WITH NO_LOG,遇到的錯誤訊息:
BACKUP LOG Northwind
WITH NO_LOG
/*
訊息 3032,層級 16,狀態 2,行 1
這個陳述式不支援一或多個選項 (no_log)。請查閱文件集,了解支援的選項。
*/
--01 在SQL Server 2008上,使用 BACKUP LOG ... WITH TRUNCATE_ONLY,遇到的錯誤訊息:
BACKUP LOG Northwind
WITH TRUNCATE_ONLY
GO
/*
訊息 155,層級 15,狀態 1,行 3
'TRUNCATE_ONLY' 不是可辨識的 BACKUP 選項。
*/


參考資料:
管理交易記錄
http://technet.microsoft.com/zh-tw/library/ms345382.aspx

SQL Server 2008 中已停止的 Database Engine 功能
http://msdn.microsoft.com/zh-tw/library/ms144262.aspx

簡單復原模式下的備份
http://technet.microsoft.com/zh-tw/library/ms191164.aspx

交易記錄截斷
http://technet.microsoft.com/zh-tw/library/ms189085.aspx


如何避免 SQL Server 資料庫的交易記錄檔超出預期大小
http://support.microsoft.com/kb/873235/zh-tw

原文出處:德瑞克:SQL Server 學習筆記: 交易記錄檔已滿、爆掉;截斷交易記錄檔。(The transaction log for database 'DB1' is full. ;Transaction Log Truncation)
冷日
(冷日)
Webmaster
  • 註冊日: 2008/2/19
  • 來自:
  • 發表數: 15766
[轉貼] 查詢資料庫使用空間與壓縮資料庫的 Log 資料

SQL 語法:查詢資料庫使用空間與壓縮資料庫的Log資料

為了最近測試資料的成長,所以順手將這些資料作紀錄,畢竟資料庫的管理,常使用到這些查詢。

以後就可以直接引用。

查詢的過程中,出現以下的錯誤訊息!

訊息 3032 ,層級 16 ,狀態 2 ,行 1

這個陳述式不支援一或多個選項 (no_log) 。請查閱文件集,了解支援的選項。

意外發現原來SQL 2008 取消了部分的查詢語法與功能,如果如MSDN文件所說的,也就是以後要截斷交易記錄,必須要利用其他方式,或是利用正規的語法備份。雖然不方便,但是依照正常的邏輯推論,的確是應該這樣沒錯!

-- 查詢資料庫使用空間

USE [DB_NAME]

SET NOCOUNT
ON

DECLARE @tDBSize TABLE

( DBName SYSNAME DEFAULT ( [DB_NAME] ()),

Fileid INT , FileGroup INT , TotalExtents INT ,


UsedExtents INT , Name SYSNAME , FileName NVARCHAR ( 4000 ))

DECLARE @tLogSize TABLE

( DBName sysname , logsize float , used
float , status int )

INSERT @tDBSize ( Fileid , FileGroup , TotalExtents , UsedExtents , Name , FileName )

EXEC ( 'DBCC showfilestats' )

INSERT INTO @tLogSize

EXECUTE ( 'DBCC SQLPERF(LOGSPACE)' )

SELECT D . DBName N' 資料庫 ' , D . Name N' 資料檔案 ' ,

TotalExtents * 64.0 / 1024 N' 資料檔案使用硬碟空間 (MB)' ,

UsedExtents * 64.0 / 1024 N' 資料實際使用空間 (MB)' ,

logsize N' 交易記錄檔整體使用的硬碟空間 (MB)' ,

( logsize * used / 100 ) N' 交易記錄檔整體的實際使用 (MB)'

FROM
@tLogSize L INNER JOIN @tDBSize D

ON L . DBName = D . DBName

-- 截斷交易記錄檔

/*

當資料庫使用簡單復原模式時,便會自動截斷交易記錄。

如果您必須從資料庫中移除記錄備份鏈結,請切換到簡單復原模式。

*/

--01 將資料庫 [DB_NAME]
切換為「簡單復原模式」,便會自動截斷交易記錄。

ALTER DATABASE [DB_NAME]

SET RECOVERY SIMPLE

GO

--02 若決定要備份交易記錄檔 (*.ldf) ,再將資料庫 [DB_NAME] 切換回「完整復原模式」

ALTER DATABASE [DB_NAME]

SET RECOVERY FULL

GO

-- 使用 BACKUP LOG ... WITH NO_LOG(SQL 2008 不支援)

BACKUP LOG [DB_NAME]

WITH NO_LOG

-- 或是,使用 BACKUP LOG ... WITH TRUNCATE_ONLY(SQL 2008 不支援)

BACKUP LOG [DB_NAME]

WITH TRUNCATE_ONLY

-- 顯示資料庫檔案,找出交易記錄檔的邏輯檔名

EXEC sp_helpdb '[DB_NAME]' --[DB_NAME] :資料庫名稱

--Medical_Data (DB NAME)


--Medical_Log (DB Log NAME)

-- ()壓縮交易記錄檔

DBCC SHRINKFILE ( Medical_Log , 1024 ) --Medical_Log ldf 檔的邏輯檔名,用 EXEC sp_helpdb 可以找出

參考資料:

德瑞克: SQL Server 學習筆記
交易記錄檔已滿、爆掉;截斷交易記錄檔。(
The transaction log for database 'DB1' is full. Transaction Log Truncation

管理交易記錄
http://technet.microsoft.com/zh-tw/library/ms345382.aspx

SQL Server 2008 中已停止的 Database Engine功能
http://msdn.microsoft.com/zh-tw/library/ms144262.aspx

SQL Server 2008 R2 中已被取代的 Database Engine功能

http://msdn.microsoft.com/zh-tw/library/ms143729.aspx

簡單復原模式下的備份
http://technet.microsoft.com/zh-tw/library/ms191164.aspx

交易記錄截斷
http://technet.microsoft.com/zh-tw/library/ms189085.aspx

如何避免 SQL Server資料庫的交易記錄檔超出預期大小

http://support.microsoft.com/kb/873235/zh-tw


原文出處:SQL 語法:查詢資料庫使用空間與壓縮資料庫的Log資料 @ 混亂....逐漸擴散 :: 痞客邦 PIXNET ::
冷日
(冷日)
Webmaster
  • 註冊日: 2008/2/19
  • 來自:
  • 發表數: 15766
[轉貼] SQL SERVER 2008 刪除資料與交易記錄檔

SQL SERVER 2008刪除資料與交易記錄檔

因為沒做好交易紀錄檔的設定,導致SQL的交易紀錄檔無限制的膨脹...

直到整個硬碟空間都被塞爆了...

以下是小弟自己的解決方式,不見得是最佳做法:

1.確定你的SQL帳戶具有sysadmin權限

2.在資料庫屬性的選項將復原模式從"完整"更改為"簡單"

3.設定交易紀錄傳送(但是小弟只有一台SQL2008,所以事件檢視器都會出錯錯誤的Log,只好先暫停啟用事件傳送...)

4.開啟SQL Query:輸入下列字串,直接將LOG檔清除~

語法一:
====================================
BACKUP LOG DBNAME WITH TRUNCATE_ONLY
DBCC SHRINKFILE( DBNAME_log,2)
====================================

語法二:
DBCC SHRINKFILE ( ' DBNAME_log' ,0,TRUNCATEONLY)

語法一....小弟執行失敗...

語法二就成功刪掉那該死又大又肥的Log檔!!

PS: DBNAME是資料庫名稱

 

參考文章:


http://sharedderrick.blogspot.com/2009/03/transaction-log-for-database-is-full.html

http://tw.myblog.yahoo.com/jw!9skfwiSAHxQ7c8D8luphjfve/article?mid=-2&prev=145&l=f&fid=23

http://support.microsoft.com/kb/873235/zh-tw

http://delphi.ktop.com.tw/board.php?cid=32&fid=107&tid=40210

http://ithelp.ithome.com.tw/event/ironmanarticle2/id/20002493/page/1



原文出處:SQL SERVER 2008刪除資料與交易記錄檔 @ 轉速的世界 :: 痞客邦 PIXNET ::
冷日
(冷日)
Webmaster
  • 註冊日: 2008/2/19
  • 來自:
  • 發表數: 15766
[轉貼] SQL SERVER 管理交易記錄

管理交易記錄

這個主題討論交易記錄空間的例行管理,以防止交易記錄太滿。

記錄截斷 (在簡單復原模式下自動執行) 是避免記錄填滿不可或缺的動作。截斷處理序會將未保存任何邏輯記錄部分的虛擬記錄檔標示成非使用中,藉以縮減邏輯記錄檔的大小。不過,在某些情況下,實體壓縮或擴展實體記錄檔會很有用。

附註

記錄檔的自動成長量小也會降低效能。記錄檔的檔案成長量應夠大,才不用經常進行擴充。通常適當的預設成長量為 10%。如需有關對記錄檔變更檔案成長屬性的詳細資訊,請參閱< ALTER DATABASE (Transact-SQL)>。

交易記錄截斷

包含記錄截斷的相關資訊。

管理交易記錄檔的大小

包含監視交易記錄大小、壓縮交易記錄、加入或移除交易記錄檔及最佳化 tempdb 交易記錄成長速率的相關資訊。


可能會延遲記錄截斷的因素

描述可能會延遲記錄截斷的因素。


原文出處:Microsoft Technet 管理交易記錄



交易記錄截斷

若永遠不將記錄從交易記錄中刪除,交易記錄就會持續成長,直到填滿實體記錄檔可用的所有磁碟空間為止。記錄截斷會自動釋出邏輯記錄中的空間,以供交易記錄重複使用。

除了因為某個原因而造成延遲以外,記錄截斷會在下列情況下自動發生:

  • 在簡單復原模式下,發生在檢查點之後。

  • 在完整復原模式或大量記錄復原模式下,上一次備份以來發生檢查點時的記錄備份之後。如需詳細資訊,請參閱本主題稍後的「在完整或大量記錄復原模式下的記錄截斷」。

雖然記錄截斷是自動發生的,但是可能會因為各種因素而延遲。如需有關延遲記錄截斷之因素的詳細資訊,請參閱< 可能會延遲記錄截斷的因素>。

重要事項

如果在記錄截斷中發生長時間的延遲,交易記錄可能會填滿。如需有關如何處理完整交易記錄的詳細資訊,請參閱< 寫滿交易記錄疑難排解 (錯誤 9002)>。

如需有關記錄截斷的架構資訊,請參閱本主題稍後的「記錄截斷的運作方式」。

在完整復原模式或大量記錄復原模式下,記錄的非使用中部分要等到其所有記錄檔記錄都已經在記錄備份中擷取之後,才會被截斷。這是維護 「記錄鏈結」 (具有未中斷記錄序號 (LSN) 的一連串記錄檔記錄) 所需的項目。假設下列條件均成立,當您備份交易記錄後,就會截斷記錄:

  • 自從上次備份記錄之後發生檢查點。在完整復原模式或大量記錄復原模式下,截斷記錄一定需要檢查點,但是只有檢查點還是不夠。在檢查點之後,記錄會維持完整,至少到下一次交易記錄備份為止。

    如需詳細資訊,請參閱< 檢查點與記錄檔的使用中部份>。

  • 沒有其他因素妨礙記錄截斷。

    一般情況下,使用定期備份時,記錄空間將定期釋放以供日後使用。但是,其他因素 (例如,長時間執行的交易) 可能會暫時導致記錄無法截斷。如需詳細資訊,請參閱< 可能會延遲記錄截斷的因素>。

  • BACKUP LOG 陳述式未指定 WITH COPY_ONLY。

備份交易記錄

附註

截斷作業不會讓實體記錄檔變小。縮減記錄檔的實體大小需要壓縮檔案。如需有關壓縮實體記錄檔大小的詳細資訊,請參閱< 壓縮交易記錄檔>。

交易記錄是循環使用的檔案。建立資料庫時,邏輯記錄檔會從實體記錄檔開頭處開始。新的記錄會加在邏輯記錄檔的尾端,並朝向實體記錄檔的尾端擴充。資料庫中的交易記錄會對應到一或多個實體檔。SQL Server Database Engine 會在內部將每個實體記錄檔分成數個虛擬記錄檔。記錄截斷會釋出邏輯記錄檔中的空間,其方式是從邏輯記錄檔的開頭刪除非使用中的虛擬記錄檔。如需有關交易記錄架構的深入資訊,請參閱<
交易記錄邏輯架構
>和< 交易記錄檔實體架構>。

虛擬記錄檔是可重複使用的空間單位。只有僅包含非使用中記錄檔記錄的虛擬記錄檔才可以被截斷。交易記錄的使用中部分 ( 「使用中記錄」) 無法截斷,因為需要使用中記錄來還原資料庫。最近的檢查點會定義使用中記錄。記錄最多可以截斷至該記錄點。

附註

如需虛擬記錄檔功能的詳細資訊,請參閱< 交易記錄檔實體架構>。

執行檢查點時,交易記錄的非使用中部分會標示成可重複使用。之後,記錄截斷就可以釋出非使用中的部分。截斷會釋出非使用中的虛擬記錄檔以便重複使用。最後,當新記錄寫入釋出的虛擬記錄檔後,該虛擬記錄檔就會再次成為使用中。

記錄於檢查點的某項資訊是必須存在之第一筆記錄檔記錄的記錄序號 (LSN),才能成功回復整個資料庫。此 LSN 就稱為 「最小復原 LSN」 ( MinLSN)。記錄使用中部分的開頭就是含有 MinLSN 的虛擬記錄。截斷交易記錄後,只有此虛擬記錄檔前面的記錄檔記錄才能釋出以便重複使用。


下圖將顯示截斷前後的交易記錄。第一張圖是顯示從未進行截斷的交易記錄。目前,邏輯記錄正使用四個虛擬記錄檔。虛擬記錄是從第一個虛擬記錄檔的前面開始,並於虛擬記錄 4 結束。MinLSN 記錄位於虛擬記錄 3 中。虛擬記錄 1 和虛擬記錄 2 僅包含非使用中的記錄檔記錄。這些記錄都可以截斷。虛擬記錄 5 仍未使用而且不屬於目前邏輯記錄的一部分。

第二張圖是顯示記錄截斷之後的內容。虛擬記錄 1 和虛擬記錄 2 已經釋出以便重複使用。邏輯記錄檔現在會從虛擬記錄檔 3 的開頭開始。虛擬記錄檔 5 仍未使用,而且不屬於目前邏輯記錄檔的一部分。



管理交易記錄檔的大小 Manage the Size of the Transaction Log File

本主題涵蓋如何監視 SQL Server SQL Server 交易記錄大小、壓縮交易記錄、加入或加大交易記錄檔、最佳化 tempdb 交易記錄成長率,以及控制交易記錄檔的成長。 This topic covers how to monitor SQL Server SQL Server transaction log size, shrink the transaction log, add to or enlarge a transaction log file, optimize the tempdb transaction log growth rate, and control the growth of a transaction log file.

監視記錄空間的使用 Monitor log space use

使用 DBCC SQLPERF (LOGSPACE) 來監視記錄空間的使用。
Monitor log space use by using DBCC SQLPERF (LOGSPACE). 這個命令會傳回目前使用之記錄空間量的相關資訊,並指出交易記錄需要截斷的時機。 This command returns information about the amount of log space currently used, and indicates when the transaction log needs truncation. 如需詳細資訊,請參閱 DBCC SQLPERF Transact-SQL For more information, see DBCC SQLPERF Transact-SQL.
如需目前的記錄檔大小、大小上限及檔案的自動成長選項等詳細資訊,您也可以在 sys.database_files 中使用該記錄檔的 sizemax_sizegrowth 資料行。 For information about the current log file size, its maximum size, and the autogrow option for the file, you can also use the size, max_size, and growth columns for that log file in sys.database_files. 如需詳細資訊,請參閱 sys.database_files (Transact-SQL) For more information, see sys.database_files (Transact-SQL).


重要! Important! 請避免讓記錄磁碟多載! Avoid overloading the log disk!

壓縮記錄檔大小 Shrink log file size

若要減少實體記錄檔的實體大小,則必須壓縮記錄檔。 To reduce the physical size of a physical log file, you must shrink the log file. 如果您知道交易記錄檔包含未使用的空間,則這十分有用。 This is useful when you know that a transaction log file contains unused space. 只有當資料庫已上線,而且至少有一個虛擬記錄檔可用時,您才能壓縮記錄檔。 You can shrink a log file only while the database is online, and at least one virtual log file is free. 在某些情況下,壓縮記錄可能要等到下一個記錄截斷之後才能進行。
In some cases, shrinking the log may not be possible until after the next log truncation.

注意

像長時間執行的交易之類的因素,使虛擬記錄檔保持作用中一段很長的時間,可能限制記錄檔壓縮,甚至完全阻止記錄檔壓縮。 Factors, such as a long-running transaction, that keep virtual log files active for an extended period can restrict log shrinkage or even prevent the log from shrinking at all. 如需延遲記錄截斷可能因素的相關資訊,請參閱 交易記錄 (SQL Server) For information about factors that can delay log truncation, see The Transaction Log (SQL Server).

壓縮記錄檔會移除一或多個不保留任何邏輯記錄的虛擬記錄檔 (即「非使用中虛擬記錄檔」)。
Shrinking a log file removes one or more virtual log files that hold no part of the logical log (that is, inactive virtual log files).
當交易記錄檔壓縮之後,就會從記錄檔的結尾移除非使用中的虛擬記錄檔,將記錄縮減至大約目標大小。 When a transaction log file is shrunk, inactive virtual log files are removed from the end of the log file to reduce the log to approximately the target size.

壓縮記錄檔 (但不壓縮資料庫檔案) Shrink a log file (without shrinking database files)

注意

您可以將記錄檔設定為自動壓縮。 You can set log files to shrink automatically. 不過,我們建議您不要進行自動壓縮,而且 autoshrink 資料庫屬性預設為 FALSE。 However, we recommend against automatic shrinking, and the autoshrink database property is set to FALSE by default. 如果 autoshrink 設定為 TRUE,只有當超過 25% 的空間未使用時,自動壓縮才會減少檔案的大小。 If autoshrink is set to TRUE, automatic shrinking reduces the size of a file only when more than 25 percent of its space is unused. 此時,檔案會壓縮成只有 25% 的檔案是未使用空間的大小,或檔案的原始大小,以較大者為準。 The file is shrunk either to the size at which only 25 percent of the file is unused space or to the original size of the file, whichever is larger. 如需變更 autoshrink 屬性設定的相關資訊,請參閱
檢視或變更資料庫的屬性—使用 [選項] 頁面的 Auto Shrink 屬性—或 ALTER DATABASE SET 選項 (Transact-SQL)—使用 AUTO_SHRINK 選項。
For information about changing the setting of the autoshrink property, see View or Change the Properties of a Database—use the Auto Shrink property on the Options page—or
ALTER DATABASE SET Options (Transact-SQL)—use the AUTO_SHRINK option.

加入或加大記錄檔 Add or enlarge a log file

您可以加大現有的記錄檔 (如果磁碟空間允許的話),或是將記錄檔加入資料庫 (通常是在不同的磁碟上),來取得空間。 You can gain space by enlarging the existing log file (if disk space permits) or by adding a log file to the database, typically on a different disk.

  • 若要對資料庫新增一個記錄檔,請使用 ALTER DATABASE 陳述式的 ADD LOG FILE 子句。 To add a log file to the database, use the ADD LOG FILE clause of the ALTER DATABASE statement. 新增記錄檔可讓記錄檔增大。 Adding a log file allows the log to grow.


  • 若要加大記錄檔,可以使用 ALTER DATABASE 陳述式的 MODIFY FILE 子句,並指定 SIZE 與 MAXSIZE 語法。 To enlarge the log file, use the MODIFY FILE clause of the ALTER DATABASE statement, specifying the SIZE and MAXSIZE syntax. 如需詳細資訊,請參閱 ALTER DATABASE (Transact-SQL) For more information, see ALTER DATABASE (Transact-SQL).

最佳化 tempdb 交易記錄的大小 Optimize tempdb transaction log size

重新啟動伺服器執行個體時,就會將 tempdb 資料庫的交易記錄大小重新調整為自動成長之前的原始大小。
Restarting a server instance resizes the transaction log of the tempdb database to its original, pre-autogrow size.
這樣會降低 tempdb 交易記錄的效能。 This can reduce the performance of the tempdb transaction log. 您可以在啟動或重新啟動伺服器執行個體後,增加 tempdb 交易記錄的大小,藉以避免這項負擔。 You can avoid this overhead by increasing the size of the tempdb transaction log after starting or restarting the server instance. 如需詳細資訊,請參閱 tempdb Database For more information, see tempdb Database.

控制交易記錄檔的成長 Control transaction log file growth

請使用 ALTER DATABASE (Transact-SQL) 陳述式來管理交易記錄檔的成長。 Use the ALTER DATABASE (Transact-SQL) statement to manage the growth of a transaction log file. 請注意下列事項: Note the following:

  • 若要變更目前的檔案大小 (單位為 KB、MB、GB 和 TB),請使用 SIZE 選項。 To change the current file size in KB, MB, GB, and TB units, use the SIZE option.
    • 若要變更成長的增量,請使用 FILEGROWTH 選項。
      To change the growth increment, use the FILEGROWTH option.
      0 的值表示將自動成長設為關閉,而且不允許任何其他空間。 A value of 0 indicates that automatic growth is set to off and no additional space is permitted. 記錄檔的少量自動成長增量可能會降低效能。 A small autogrowth increment on a log file can reduce performance. 記錄檔的檔案成長量應夠大,才不用經常進行擴充。 The file growth increment on a log file should be sufficiently large to avoid frequent expansion. 通常適當的預設成長量為 10%。 The default growth increment of 10 percent is generally suitable.

如需對記錄檔變更檔案成長屬性的相關資訊,請參閱 ALTER DATABASE (Transact-SQL)
For information on changing the file-growth property on a log file, see ALTER DATABASE (Transact-SQL).

  • 若要控制記錄檔大小的最大值 (單位為 KB、MB、GB 和 TB) 或是將成長設定為 UNLIMITED,請使用 MAXSIZE 選項。 To control the maximum the size of a log file in KB, MB, GB, and TB units or to set growth to UNLIMITED, use the MAXSIZE option.

另請參閱 See also

BACKUP (Transact-SQL) BACKUP (Transact-SQL)

寫滿交易記錄疑難排解 (SQL Server 錯誤 9002) Troubleshoot a Full Transaction Log (SQL Server Error 9002)



可能會延遲記錄截斷的因素

記錄截斷會釋出記錄檔中的空間,以供交易記錄重複使用。由於記錄的使用中部分無法被截斷或是以壓縮的方式加以移除,因此當記錄檔記錄長時間保持使用中狀態時,截斷可能就會延遲。

附註

如需有關記錄截斷如何運作的詳細資訊,請參閱< 交易記錄截斷>。

記錄檔記錄可能會在各種情況下維持使用中狀態,本主題將會加以說明。若要探索是否有任何原因導致記錄截斷無法進行,請使用 sys.databases 目錄檢視的 log_reuse_waitlog_reuse_wait_desc 資料行。

附註

這些因素中有一部分 (例如,長時間執行的交易或已暫停的資料庫鏡像工作階段) 會導致交易記錄填滿的結果。如需有關如何回應完整交易記錄的詳細資訊,請參閱<
寫滿交易記錄疑難排解 (錯誤 9002)
>。

下表簡短描述 sys.database 目錄檢視的 log_reuse_waitlog_reuse_wait_desc 資料行的值。

log_reuse_wait 值

log_reuse_wait_desc 值

描述

0

NOTHING

目前有一個或多個可重複使用的虛擬記錄檔。

1

CHECKPOINT

自從上次記錄截斷後尚未出現任何檢查點,或是記錄標頭尚未移到虛擬記錄檔的範圍之外 (所有復原模式)。

這是延遲記錄截斷的一般原因。如需詳細資訊,請參閱< 檢查點與記錄檔的使用中部份>。

2

LOG_BACKUP

必須要有記錄備份,才能將記錄的標頭往前移 (僅限完整或大量記錄復原模式)。

附註
記錄備份不會防止截斷。

當記錄備份完成後,記錄的標頭會往前移,而某些記錄空間可能就可以重複使用。

3

ACTIVE_BACKUP_OR_RESTORE

正在進行資料備份或還原 (所有復原模式)。

資料備份的運作方式與使用中交易類似,而且執行中的備份將導致記錄無法截斷。如需詳細資訊,請參閱本主題稍後的「資料備份作業與還原作業」。

4

ACTIVE_TRANSACTION

交易在使用中 (所有復原模式)。

  • 長時間執行的交易可能存在於記錄備份的開頭。在此情況下,釋出空間可能需要另一個記錄備份。如需詳細資訊,請參閱本主題稍後的「長時間執行的使用中交易」。

  • 交易已延遲 (僅限 SQL Server 2005 Enterprise Edition 及更新的版本)。 「延遲交易」
    實際上是回復遭到封鎖的使用中交易 (因為某些無法使用的資源所造成)。如需有關延遲交易的原因以及如何將延遲交易移出延遲狀態的詳細資訊,請參閱< 延遲交易>。

5

DATABASE_MIRRORING

資料庫鏡像已暫停,或者在高效能模式下,鏡像資料庫已大幅落後主體資料庫 (僅限完整復原模式)。

如需詳細資訊,請參閱本主題稍後的「資料庫鏡像與交易記錄」。

6

REPLICATION

進行交易式複寫期間,與發行集相關的交易仍然未傳遞至散發資料庫 (僅限完整復原模式)。

如需詳細資訊,請參閱本主題稍後的「交易式複寫與交易記錄」。

7

DATABASE_SNAPSHOT_CREATION

正在建立資料庫快照集 (所有復原模式)。

這是延遲記錄截斷的一般原因 (通常也是暫時的原因)。

8

LOG_SCAN

正在進行記錄掃描 (所有復原模式)。

這是延遲記錄截斷的一般原因 (通常也是暫時的原因)。

9

OTHER_TRANSIENT

這個值目前尚未使用。

任何備份或還原作業期間都不會發生記錄截斷的狀況。在 SQL Server 2005 及更新的版本中,記錄備份可以在資料備份期間進行。不過,您不能在這種記錄備份期間截斷記錄,因為所有的交易記錄都必須保留給資料備份作業使用。如果資料備份阻礙截斷記錄,取消該備份可能有助於化解眼前的問題。

如需有關記錄截斷的詳細資訊,請參閱< 交易記錄截斷>。


使用中交易會要求包含交易開頭的記錄檔記錄保持使用中狀態。例如,如果交易的開始和結束是由使用者控制,長時間執行之交易的常見原因就是使用者開始進行交易,然後在交易等候使用者回應時離開。在這種情況下,雖然等候交易本身產生的記錄很少,但是它會阻擋記錄截斷動作,而導致記錄檔變大。

附註

如需有關如何避免長時間執行之交易的詳細資訊,請參閱< 撰寫有效率的交易>。

進行資料庫鏡像時,每一筆記錄都必須維持使用中狀態,直到主體伺服器執行個體收到鏡像伺服器執行個體傳來的通知,表示記錄已寫入鏡像伺服器的磁碟為止。如果鏡像伺服器執行個體落在主體伺服器執行個體之後,使用中的記錄空間量也會隨之成長。在這種情況下,您可能必須停止資料庫鏡像、建立截斷記錄的記錄備份、將該記錄備份套用至鏡像資料庫 (使用 WITH NORECOVERY),並重新啟動鏡像。


重要事項

此外,如果在必要的記錄備份之後建立任何額外的記錄備份,您也必須手動套用每一份額外的記錄備份 (一律使用 WITH NORECOVERY),然後才能啟動鏡像。套用最新的記錄備份之後,您就可以啟動鏡像。

如需詳細資訊,請參閱< 移除資料庫鏡像>和< 設定資料庫鏡像>。

合併式複寫和快照式複寫不會影響交易記錄大小,但是交易式複寫會影響。如果資料庫包含一個或多個交易式發行集,則除非已將所有與發行集相關的交易傳遞至散發資料庫,否則不會截斷記錄。如果交易記錄變得很大,並且「記錄讀取器代理程式」依排程執行,請考慮縮短兩次執行間的間隔或設定其以連續模式執行。如果已將其設定為在連續模式下執行 (預設值),則請確定它正在執行。如需有關如何檢查「記錄讀取器代理程式」狀態的詳細資訊,請參閱<
如何:檢視與發行集相關聯之代理程式的資訊並執行工作 (複寫監視器)
>。

此外,如果您已在發行集資料庫或散發資料庫中設定選項 'sync with backup',則除非已備份所有交易,否則交易記錄不會被截斷。如果交易記錄變得很大,並且您已設定此選項,則請考慮縮短兩次交易記錄備份之間的間隔。如需有關如何備份和還原涉及交易式複寫之資料庫的詳細資訊,請參閱< 備份與還原快照式和交易式複寫的策略>。

管理複寫

監視複寫

冷日
(冷日)
Webmaster
  • 註冊日: 2008/2/19
  • 來自:
  • 發表數: 15766
[轉貼] SQL SERVER 簡單復原模式下的備份

簡單復原模式下的備份

重要事項

對於無法接受損失最新變更的實際執行系統而言,簡單復原模式不是適當的選擇。在這些情況下,建議您使用完整復原模式。如需詳細資訊,請參閱< 在完整復原模式下備份>。

簡單復原模式提供最簡單的備份和還原形式。這個復原模式可支援資料庫備份和檔案備份,但是不支援記錄備份。交易記錄資料僅能與相關聯的使用者資料一起備份。如果沒有記錄備份,則會簡化備份與還原的管理。. 不過,資料庫只能還原到最近一次備份的結尾。

下圖顯示簡單復原模式下最簡單的備份和還原策略。這個策略只使用完整資料庫備份,其中包括資料庫內的所有資料。其中有五個完整資料庫備份存在,但只有在時間 t5 進行的最新備份才需要還原。還原這個備份會將資料庫回復到 t5 這個時間點。所有後面的更新 (以 t6 方塊表示) 都會遺失。


附註

在簡單復原模式下,會自動截斷交易記錄以移除任何非使用中的虛擬記錄檔。截斷通常會在每個檢查點之後發生,但是也可能在某些情況下延遲。如需詳細資訊,請參閱< 交易記錄截斷>。

在簡單復原模式下,工作損失風險會隨時間經過而增加,直到進行下一個完整備份或差異備份為止。相較於完整備份,差異備份只包含上一次完整備份之後所做的變更。因此,建議您排定充足的備份頻率,以避免遺失大量資料,但是不要讓備份變得難以管理。

下圖顯示只使用資料庫備份之備份計畫的工作損失風險。這個策略只適合可頻繁備份的小型資料庫。

下圖顯示使用差異資料庫備份補充資料庫備份來減輕工作損失風險的備份策略。在第一次資料庫備份之後,連續建立了三次差異備份。第三個差異備份已經大到下一次備份時必須進行資料庫備份。這樣會建立新的差異基底。


如需有關使用這些備份類型的詳細資訊,請參閱< 完整資料庫備份>和< 使用差異備份>。

完整備份和差異備份包含剛好足夠的記錄資料,可讓您復原資料庫。還原資料庫時需要一連串的還原作業 ( 「還原順序」)。還原順序會從還原完整備份開始,然後選擇性地進行對應的差異備份。在某些情況下 (例如,還原檔案時),可能需要還原多個成對的完整及差異備份。在還原相關備份之後,您必須復原資料庫。如需還原實例的簡介,請參閱< 還原和復原概觀 (SQL Server)>。

如需有關還原簡單復原模式備份之限制的詳細資訊,請參閱< 簡單復原模式下的還原限制



原文出處: Microsoft Technet 簡單復原模式下的備份
冷日
(冷日)
Webmaster
  • 註冊日: 2008/2/19
  • 來自:
  • 發表數: 15766
[轉貼]資料庫交易記錄已滿的可能解決方式

本文將介紹資料庫交易記錄已滿的可能解決方式。

問題描述

當您嘗試存取SQL Server 時發生【資料庫'db name'的交易記錄已滿。如果要了解為何無法重複使用記錄中的空間,請參閱sys.databases中的log_reuse_wait_desc資料行】,從 SQL Server 的記錄檔檢視器,您會看到類似下圖的記錄。

實際檢查交易記錄檔所使用的磁碟機似乎還有足夠的空間可供使用,為什麼會發生這樣的問題呢? KB 317375 指出可能的原因如下:

實作步驟

若您想要了解是什麼原因造成交易記錄無法重複使用,必須強制截斷或清空,您可以依照錯誤訊息的提示來查詢 sys.databases 系統檢視的 log_reuse_wait_desc 資料行,該資料行會告訴您目前交易記錄檔的空間正等待進行什麼樣的作業。


NOTHING CHECKPOINT LOG_BACKUP ACTIVE_BACKUP_OR_RESTORE
ACTIVE_TRANSACTION DATABASE_MIRRORING REPLICATION DATABASE_SNAPSHOT_CREATION
LOG_SCAN AVAILABILITY_REPLICA OLDEST_PAGE OTHER_TRANSIENT

但是要解決這個問題,或許您可以直接以下列兩種方式之一來截斷或清空交易記錄。

方法一、使用 T-SQL:您可以利用設定資料庫復原模式為簡單復原模式,來截斷或清空交易記錄。



SET RECOVERY SIMPLE
GO

方法二、使用 SSMS:您也可以在 SSMS 的物件總管中,在發生上述錯誤訊息的資料庫上按滑鼠右鍵選擇【屬性】。

於資料庫屬性視窗左側的選取頁面區塊中點選【選項】,接著設定復原模式為【簡單】,最後按確定,即可截斷或清空記錄檔。

建議除了開發或測試的資料庫使用簡單復原模式以外,正式環境還是在截斷交易記錄之後,還是把復原模式改回來完整,以確保可以使用更多樣的資料庫策略,進而降低資料遺失的機率。

參考資料

執行 SQL Server 之電腦上的交易記錄檔意外地擴充或滿溢


sys.databases (Transact-SQL)

The transaction log for database 'mydatabase' is full. To find out why space in the log cannot be reused, see the log_reuse_wait

交易記錄檔已滿、爆掉;截斷交易記錄檔。


原文出處: 資料庫交易記錄已滿的可能解決方式 | 積沙成塔 - 點部落
前一個主題 | 下一個主題 | 頁首 | | |



Powered by XOOPS 2.0 © 2001-2008 The XOOPS Project|