今天群里面的東輝兄跟我說備份生產數據庫的時候報錯

環境：

32位的SQLSERVER2008

機器有16G內存

數據庫大小差不多60G

數據庫已經打開了AWE功能

因為是生產庫，暫時不能升級到64位SQL2008，那么還有沒有其他辦法可以解決這個問題呢？？

上網搜索了一下就找到了這篇文章：

Incorrect BufferCount data transfer option can lead to OOM condition

http://blogs.msdn.com/b/sqlserverfaq/archive/2010/05/06/incorrect-buffercount-data-transfer-option-can-lead-to-oom-condition.aspx

譯文：

今天介紹兩個DBA在做數據庫備份的時候一般都不會去指定的備份參數。一般備份數據庫大家會用下面的方法：

1、使用SSMS或者TSQL腳本來備份數據庫

2、使用第三方的備份軟件，這些第三方軟件利用SQLVDI.DLL暴露出來的編程接口或者使用快照備份

當你寫好備份命令并發送到服務器端的時候，你可以在你的備份命令里添加如下兩個參數：

MAXTRANSFERSIZE

BUFFERCOUNT

聯機叢書對于這兩個參數的解釋是：

http://technet.microsoft.com/zh-cn/library/ms186865(SQL.105).aspx

數據傳輸選項BUFFERCOUNT = { buffercount | @buffercount_variable }指定用于備份操作的 I/O 緩沖區總數?？梢灾付ㄈ魏握麛?；但是，較大的緩沖區數可能導致由于 Sqlservr.exe 進程中的虛擬地址空間（VAS）不足而發生“內存不足”錯誤。緩沖區使用的總空間由下面的公式確定：buffercount * maxtransfersize。注意有關使用 BUFFERCOUNT 選項的重要信息，請參閱不正確的 BufferCount 數據傳輸選項可以導致 OOM 情況博客。

MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable }指定要在 SQL Server 和備份介質之間使用的最大傳輸單元（字節）。

可能的值是 65536 字節 (64 KB) 的倍數，最多可到 4194304 字節 (4 MB)。

根據你是否提供這兩個參數值，SQLSERVER會指定用于備份的連續的緩沖區大小。

這是對于32位系統中大量的連續的內存分配的 non-Buffer pool區域的虛擬地址空間是至關重要的。這會令備份失?。?strong>言下之意，備份使用的是non buffer pool）。

當使用SQLVDI，重要的是在VDI配置已經完成之后，我們不能改變MAXTRANSFERSIZE 。大量的連續的內存分配決定于備份設備的數量和數據庫文件存放的磁盤的數量

當你進行備份或者還原數據庫的時候可以通過跟蹤標志3213來查看你的備份/還原 配置參數。

具體可以參考我這篇文章：《SQLSERVER數據庫備份操作和還原操作做了什么》

我會給你們演示指定不正確的BUFFERCOUNT 參數或者不指定這個參數出現的致命錯誤

Trace Flag 3213

我使用下面的SQL命令來備份SQLSERVER數據庫

backup database dbBackupto disk = 'C:/dbBackup1.bak',disk = 'C:/dbBackup2.bak',disk = 'C:/dbBackup3.bak',disk = 'C:/dbBackup4.bak',disk = 'C:/dbBackup5.bak',disk = 'C:/dbBackup6.bak'with stats = 1, format, buffercount = 10, maxtransfersize = 4194304

我特意指定了buffercount參數的值為10 和maxtransfersize為4MB

跟蹤標志3213的輸出顯示了下面的信息

最后使用了 40MB buffer 空間去完成備份

Backup/Restore buffer configuration parametersMemory limit: 3954MBBuffer count:               10Max transfer size:          4096 KBMin MaxTransfer size:       64 KBTotal buffer space:         40 MB

上面的內容在SQLSERVER聯機叢書上已經提到過。

我們現在需要知道buffer count不指定的情況下會發生什么。如果我們不指定buffer count，那么buffer count 會比之前指定的時候大

Backup/Restore buffer configuration parametersMemory limit: 3954MBBuffer count:               26Max transfer size:          4096 KBMin MaxTransfer size:       64 KBTotal buffer space:         104 MB

如果你看到上面的輸出會發現不指定的情況下buffer space已經飆升到104MB，這是因為buffer count已經增長到26

默認的數據庫備份的Buffer Count的計算

SQL Server 2005 或以上

(NumberofBackupDevices * GetSuggestedIoDepth) + NumberofBackupDevices + (2*DatabaseDeviceCount)

SQL Server 2000

(NumberofBackupDevices * GetSuggestedIoDepth) + NumberofBackupDevices + (DatabaseDeviceCount)  

注意：如果使用的是磁盤，那么備份時候GetSuggestedIoDepth的默認值是3

上面的公式運算完畢之后會返回一個返回值給SQLSERVER,根據備份介質的不同，例如磁盤、tape磁帶 、VDI返回不同的值

根據上面的公式，第一個例子里面我的備份設備是6，磁盤數是1，因此：在我的例子里buffer count是[(6*3) + 6 + (2*1)]=26

我將上面的SQL運行在64位SQLSERVER實例上。當我把上面的SQL運行在32位SQLSERVER實例上，

這將占近三分之一的可用的 non-Buffer Pool內存 SQL Server(MemToLeave)區域。

當我使用下面的SQL，從原來的6個備份設備增加到12個，SQLSERVER就會報錯

backup database dbBackupto disk = 'C:/dbBackup1.bak',disk = 'C:/dbBackup2.bak',disk = 'C:/dbBackup3.bak',disk = 'C:/dbBackup4.bak',disk = 'C:/dbBackup5.bak',disk = 'C:/dbBackup6.bak'disk = 'C:/dbBackup7.bak'disk = 'C:/dbBackup8.bak'disk = 'C:/dbBackup9.bak'disk = 'C:/dbBackup10.bak'disk = 'C:/dbBackup11.bak'disk = 'C:/dbBackup12.bak'with stats = 1, format, buffercount = 10, maxtransfersize = 4194304

報錯信息

Msg 3013, Level 16, State 1, Line 1BACKUP DATABASE is terminating abnormally.Msg 701, Level 17, State 17, Line 1There is insufficient system memory to run this query.Backup/Restore buffer configuration parameters

Backup/Restore buffer configuration parameters Memory limit: 4029MBBuffer count:               50Max transfer size:          4096 KB Min MaxTransfer size:       64 KBTotal buffer space:         200 MB

我的32位SQLSERVER 的最大的可用連續塊在NON BUFFER POOL(MemToLeave)里面只有157MB,因此， 就會報OOM（out of memory）錯誤

大多數第三方備份軟件會使用一個Max Transfer Size值但是不會指定Buffer Count 的大小。如果你在做備份的時候，遇到OOM錯誤，你應該下意識的檢查一下

Total Buffer Space的使用量在你備份的時候。

在這種情況下，你應該減少備份設備的數目或者減少buffer count通過指定buffer count的值在書寫備份命令的時候

下面的表格指出了默認的buffer count值對于不同的備份/還原操作

如果你在數據庫完整備份到磁盤的時候想大概估計Buffer memory的用量，下面的SQL腳本能夠計算出來

DECLARE @MaxTransferSize FLOAT ,    @BufferCount BIGINT ,    @DBName VARCHAR(255) ,    @BackupDevices BIGINT-- Default value is zero. Value to be PRovided in MB.SET @MaxTransferSize = 0-- Default value is zeroSET @BufferCount = 0-- Provide the name of the database to be backed upSET @DBName = 'dbBackup'-- Number of disk devices that you are writing the backup toSET @BackupDevices = 1DECLARE @DatabaseDeviceCount INTSELECT  @DatabaseDeviceCount = COUNT(DISTINCT ( SUBSTRING(physical_name, 1,                                                          CHARINDEX(physical_name,                                                              ':') + 1) ))FROM    sys.master_filesWHERE   database_id = DB_ID(@DBName)        AND type_desc <> 'LOG' IF @BufferCount = 0    SET @BufferCount = ( @BackupDevices * 3 ) + @BackupDevices + ( 2                                                              * @DatabaseDeviceCount )IF @MaxTransferSize = 0    SET @MaxTransferSize = 1SELECT  'Total buffer space (MB): '        + CAST (( @Buffercount * @MaxTransferSize ) AS VARCHAR(10))

至于更詳細的參數可以參考我這篇文章：《SQLSERVER數據庫備份操作和還原操作做了什么》

實驗

我在電腦上安裝了一個SQL2005 express版本 32位

使用下面的SQL語句進行備份就會報OOM錯誤

BACKUP DATABASE [test] TO DISK ='E:/test1FULLBACKUP.BAK' ,disk = 'E:/test2FULLBACKUP.bak',disk = 'E:/test3FULLBACKUP.bak',disk = 'E:/test4FULLBACKUP.bak',disk = 'E:/test5FULLBACKUP.bak',disk = 'E:/test6FULLBACKUP.bak'WITH BUFFERCOUNT=999999999,FORMAT

消息 3013，級別 16，狀態 1，第 1 行BACKUP DATABASE 正在異常終止。消息 701，級別 17，狀態 123，第 1 行沒有足夠的系統內存來運行此查詢。

但是指定BUFFERCOUNT=1就不會

BACKUP DATABASE [test] TO DISK ='E:/test1FULLBACKUP.BAK' WITH BUFFERCOUNT=1

東輝兄的情況

下午的時候我叫他指定 BUFFERCOUNT=1 試試

結果沒有報OOM錯誤

使用了17分鐘備份完差不多60G的數據庫

雖然指定BUFFERCOUNT=1 備份速度會慢一點，但是non buffer pool的內存占用不會那么多

感謝東輝兄提供這么鮮明的例子o(∩_∩)o

如有不對的地方，歡迎大家拍磚o(∩_∩)o