Troubleshooting problems with DBCC commands in SQL Server 2014
If you have problems with SQL Server 2014 DBCC commands, review this troubleshooting
checklist to find potential solutions.
1. Install the latest SQL Server 2014 service pack.
Because many SQL Server 2014 DBCC bugs were fixed in SQL Server service packs, you
should install the latest SQL Server service pack.
At the time this article was written the latest SQL Server 2014 service pack was service
pack 1. You can download the SQL Server 2014 service pack 1 at here:
https://www.microsoft.com/en-us/download/details.aspx?id=46694
2. The error 2570 occurs when you log on to SQL Server 2014 by using the SA
account and then run DBCC CHECKDB command against the master database.
This error indicates that data purity corruption in sys.sysbinobjs table in master
database occurs. This problem occurs when you enable the Common Criteria compliance
option on your SQL Server 2014 instance. This bug was first fixed in Cumulative
Update package 1 for SQL Server 2014. You can download the Cumulative Update
package 1 for SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/2931693
To work around this problem, you can enable the trace flag 2566 that disables the
DATA_PURITY check when you run the DBCC CHECKDB statement against the master database.
3. When you use DBCC SHRINKDATABASE or DBCC SHRINKFILE command, pages that belong
to the nonclustered columnstore index cannot be moved.
This is SQL Server 2014 bug. This bug was first fixed in Cumulative Update package 2
for SQL Server 2014. You can download the Cumulative Update package 2 for
SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/2967546
4. You can find out that DBCC CHECKDB and DBCC CHECKTABLE command may take
longer in SQL Server 2014.
This problem occurs when you have database or table that contains SQL CLR User-Defined
type (UDT) data. This bug was first fixed in Cumulative Update package 6 for
SQL Server 2014. You can download the Cumulative Update package 6 for SQL Server 2014
at here:
https://support.microsoft.com/en-us/kb/3031047
5. The operating system error 665 occurs when you execute DBCC CHECKDB command
for database that contains columnstore index.
This is the error message text: “The requested operation could not be completed
due to a file system limitation”. This is SQL Server 2014 bug. This bug was first
fixed in Cumulative Update package 6 for SQL Server 2014. You can download the
Cumulative Update package 6 for SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/3031047
6. When you run DBCC CHECKDB command against the database, a memory leak
occurs under the MEMORYCLERK_SQLQUERYEXEC clerk.
This problem occurs when the database has columnstore indexes. This bug was first
fixed in Cumulative Update package 6 for SQL Server 2014. You can download the
Cumulative Update package 6 for SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/3031047
7. Rollback recovery on a snapshot fails when you run DBCC CHECKDB command.
In this case, SQL Server 2014 shuts down unexpectedly. This bug was first fixed
in Cumulative Update package 7 for SQL Server 2014. You can download the Cumulative
Update package 7 for SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/3046038
8. You may experience a decrease in query performance after you run the
DBCC CHECKDB statement.
This problem occurs when a database has the AUTO_CLOSE database option set to ON.
In this case, the DBCC CHECKDB command clears the whole procedure cache. To work
around this problem, try to avoid turning on the AUTO_CLOSE database option.
9. Sometimes executing some DBCC commands (such as DBCC CHECKDB) may cause low
queries performance or lock user’s connections and queries.
In this case, run the DBCC commands during periods of low database access. Try to run
the DBCC statements when there are no other disk I/O operations, such as disk backups,
replication and so on. Because DBCC commands usually are very resource effective,
try to schedule them during CPU idle time and slow production periods.