0

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?

  • Could the problem be isolated to a specific database? Do you know if DBCC CHECKDB is being run on these databases regularly? – Josh Darnell Jun 23 at 16:51
  • CHECKDB is not regularly run, and it seems to affect all databases, though GSMPRD is by far the largest. – RonJohn Jun 23 at 17:41
  • Side note: you probably already know this, but getting DBCC CHECKDB scheduled should be a pretty high priority – Josh Darnell Jun 23 at 18:43
0

It's possible there is corruption in those system tables. Try running:

DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;

On all the databases on this instance to check for corruption.

It's also possible you're encountering a SQL Server bug. Try checking the SQL Server error log for any error messages around the time you get that "a severe error occurred" error message. You might get more details about the error, or maybe even stack dumps that are happening at that time.

| improve this answer | |
  • Nothing in the error log, and CHECKDB is clean, too. – RonJohn Jun 29 at 0:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.