This is a snippet from some code I run on a
2012 (SP3-CU9) (KB4016762) - 11.0.6598.0 (X64) system that I just inherited and have not yet had a chance to fully patch. AlwaysOn AG are not enabled, and there's plenty of space in the LDF.
declare @sqlstr nvarchar(2048); declare @dbname sysname = 'GSMPRD' declare @qdbname sysname; set @qdbname = QUOTENAME(@dbname); set @sqlstr = 'use ' + @qdbname + '; ' + 'select ''' + @dbname + ''', '+ 'schema_name(tbl_schid) as sch_name, tbl_name, ndx_name, cast(ips.avg_fragmentation_in_percent as decimal(5,2)) as frag_pct FROM (select i.object_id, i.index_id, i.name as ndx_name, t.name as tbl_name, t.schema_id as tbl_schid from ' + @qdbname + '.sys.tables as t, ' + @qdbname + '.sys.indexes as i where i.object_id = t.object_id ) as ndx, sys.dm_db_index_physical_stats(' + cast(@dbid as char(4)) + ', NULL, NULL, NULL, NULL) as ips WHERE ndx.object_id = ips.object_id AND ndx.index_id = ips.index_id and ips.index_type_desc <> ''HEAP'' and ips.avg_fragmentation_in_percent >= 5.0 and ips.fragment_count > 25 ORDER BY tbl_name, ndx_name;' exec(@sqlstr) Msg 596, Level 21, State 1, Line 0 Cannot continue the execution because the session is in the kill state. Msg 0, Level 20, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded.
Sometimes it runs, and sometimes it doesn't. Also, I frequently see
A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) when rerunning SQL that had previously run.
sqlsvr.exe is using less than it's max allowed RAM, and the Task Manager says that it's barely using half of available RAM.
What can be causing this?