SQL Server Assertion: File: , line=2763 Failed Assertion = ‘pvb->FInUse ()’

Oopps! Upgrade your browser pretty please.Oopps! Upgrade your browser pretty please.

One of my clients got ”SQL Server Assertion: File: <sosmemobj.cpp>, line=2763 Failed Assertion = ‘pvb->FInUse ()'” errors. The SQL server dumped the memory to file and failed over to the secondary availabilty group. The error was triggered by a query run from a monitoring tool called Orion. I opened a support case with Microsoft and after some investigation, they confirm that this is bug in SQL Server 2012 and will be fixed in the next service pack.

The code that triggered the error was :

DECLARE @db_id SMALLINT;
SET @db_id = DB_ID(N'master');
BEGIN
If exists (
SELECT IPS.avg_fragmentation_in_percent as [Fragmentation (%)], 
   object_name(IPS.object_id) AS [TableName with fragmentation],
   SI.name AS [IndexName], 
   IPS.Index_type_desc, 
   IPS.avg_fragment_size_in_pages, 
   IPS.avg_page_space_used_in_percent, 
   IPS.record_count, 
   IPS.ghost_record_count,
   IPS.fragment_count
FROM sys.dm_db_index_physical_stats(@db_id, NULL, NULL, NULL , 'DETAILED') IPS
   JOIN sys.tables ST WITH (nolock) ON IPS.object_id = ST.object_id
   JOIN sys.indexes SI WITH (nolock) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id
WHERE IPS.avg_fragmentation_in_percent > 0
)
begin
SELECT IPS.avg_fragmentation_in_percent as [Fragmentation (%)], 
   object_name(IPS.object_id) AS [TableName with fragmentation],
   SI.name AS [IndexName], 
   IPS.Index_type_desc, 
   IPS.avg_fragment_size_in_pages, 
   IPS.avg_page_space_used_in_percent, 
   IPS.record_count, 
   IPS.ghost_record_count,
   IPS.fragment_count
FROM sys.dm_db_index_physical_stats(@db_id, NULL, NULL, NULL , 'DETAILED') IPS
   JOIN sys.tables ST WITH (nolock) ON IPS.object_id = ST.object_id
   JOIN sys.indexes SI WITH (nolock) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id
WHERE IPS.avg_fragmentation_in_percent > 0
ORDER BY 1 desc
end
else
select '0'
end

The code it self have some issues, like trying to query the sys.dm_db_index_physical_stats in the exist clause and if any record is returned the view is queried a second time. sys.dm_db_physical_stats can be very time consuming and shouldn’t be called twice.

Microsoft has handled this supportcase very well, thank you Jen! 

There is a connect item that is similar to this defect:
https://connect.microsoft.com/SQLServer/feedback/details/796382/sql-2012-sys-dm-db-index-physical-stats-produce-errors-in-exists-clause

/Håkan Winther
Twitter: @h_winther