2023年12月5日 星期二

SQL Server has encountered the following error: operating system returned error 19 or Could not allocate space for object in database because the PRIMARY filegroup is full

SQL Server has encountered the following error:  

1.Error operating system returned error 19 

2.Could not allocate space for object in database because the PRIMARY filegroup is full


Possible Cause:

外接SAN/iSCSI Storage磁碟機,指派的可用空間超過Storage實際可用空間,作業系統看到的空間並非Stoage實際可用空間,當Storage空間不足,SQL Server就會出現以下錯誤訊息,導致疑難排解困難,因為作業系統或SQL Server所看到可用空間是足夠的,但卻出現空間不足訊息

例如:  另一種可能是VMware 超額分配可用空間

Dynamic Storage Provisioning / vSphere Storage Thin Provisioning 


Error Message:

Error 1:

The operating system returned error 19(The media is write protected.) to SQL Server during a write at offset 0000000000000000 in file 'N:\SQLData\xxxx2.ndf'. Additional messages in the SQL Server error log and operating system error log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

Error 2:

Could not allocate space for object in database because the 'PRIMARY' filegroup is full.


Resolution:

1.從Storage端,增加磁碟擴充可用空間

2.從OS端,因為Storage端空間已經不足,即使OS端檔案總管看到還有可用空間,但仍然需要刪除一些檔案來增加更多可用空間。


SQL Server Database Mirroring 資料庫鏡像

SQL Server Database Mirroring 資料庫鏡像