DBCC Archives - Rent a DBA

Aug 282015 Posted in Community, Corruption, DBCC, Scripts — 0 Responses

Data Recovery and Fixing Database Corruptions @DBA Lounge

I had recently presented to local DBA and Developers community in Iasi (Romania), a session on database corruptions and data recovery techniques in SQL Server.
It was interesting to discuss pros and cons of the approaches used in the demo, root causes for the corruptions, finding differences and correspondences Microsoft SQL Server vs. Oracle.
This was a touching base with Microsoft SQL Server storage internals and in the same time a “deep dive” into what can be done in order to protect/recover data.

The DBA Lounge group started few years ago on Linkedin, as we wanted to know each other better, share knowledge and have fun. The community is no technology specific. Anyone interested in database systems is welcomed.

Database Corruption Challenge

Last week I came across an interesting DBA challenge. Steve Stedman launched a 10 weeks “Database Corruption Challenge“.

The interesting part, so far, is that data can be recover 100%. To me, this is the actual challenge: how to be 100% sure that all data was recovered?!

From recovering data using indexes, to fixing page headers using DBCC WRITEPAGE or Hex editors, this is an excellent opportunity to practice recovery skills.

I will discuss about my solutions to each of the challenges, in future blog posts.

Apr 102015 Posted in DBCC — 0 Responses

DBCC CHECKCONSTRAINS & Error 446

On a customer database, on an empty table, DBCC CHECKCONSTRAINTS failed with the below message:

Msg 446, Level 16, State 11, Line 1
Cannot resolve collation conflict between “Latin1_General_BIN” and “Latin1_General_CI_AI” in CASE operator for DISTINCT operation.
Msg 2509, Level 16, State 2, Line 1
DBCC CHECKCONSTRAINTS failed due to an internal query error. Please run DBCC CHECKDATABASE to ensure your data consistency.

According to MSDN, indeed a temp table is created on tempdb. So I suspected (and still am) a coding bug: when database collation is different from the server one, in case of a join on char types columns, the collation should be specified.