John H

May 022013

A checksum value is tagged with every data page in SQL Server. This is used to detect issues on the storage system to ensure what has been read from the disk is what has been written to the disk. When the checksum is detected which is not the one it’s supposed to be, the page will not be read into the buffer pool. SQL Server will return error. If you are sure the data on the page are all correct or the checksum is broken purposely, you do have the option to fix it even you don’t know how to calculate it.

Continue reading »

Apr 302013

This is a very interesting error happened in one of my clients’ databases. Their database has been running for at least 12 years without a single problem. They run DBCC CheckDB regularly to ensure the database is in healthy state until few days ago their DBA ran DBCC CHECKTABLE with data_purity and get

Msg 2570, Level 16, State 3, Line 1
Page (1:118), slot 0 in object ID 245575913, index ID 0, partition ID 72057594039042048, alloc unit ID 72057594043432960 (type "In-row data"). Column "Value" value is out of range for data type "numeric". Update column to a legal value.

Finally, they found this article, , — Many things can cause this kind of error. In this blog post, I am going to demo how I repeat and fix this error — for fun.

Continue reading »

Jan 092013

SQL Saturday 198, ; is coming to Vancouver Feb 16 this year.  As of now, 56 sessions are collected from 30 speakers, Curiously last night, I pulled all speakers from the list and concluded that it will be a great event this time.

  • 19 SQL MVPs
  • 7 author/co-authors
  • 4 MCMs
  • 4 PASS User Group Leaders
  • 3 MS Employees (One of them is program manager of SQL Cloud infrastructure team)
  • 2 former MS employees (one of them was former MCM program manager)
  • One from NetApp
  • One PhD

Join us and enjoy the sessions.

Dec 262012

We know that we need to take special attention about NULL values while writing queries because they may lead us into writing incorrect query logics. NULLs may also affect the performance of aggregation function MAX() and MIN(). I have submitted this issue to Microsoft Connect. There are number of ways to get around it. I think the improvement can also be done at query engine level. Hope this can be fixed in next or future version of SQL Server. But for now, we need to know it and know how to get around it.

Continue reading »

Oct 172012

Inserted and deleted tables are 2 pseudo tables preseting the before and after images of modified rows. They can only be accessed from within triggers. Those two have been around for quite long time but it’s hard to find any articles to tell how they are populated. Some rumors said they are retrieved from transaction logs in SQL Server 2000. Recently, I was asked to evaluate the performance impact of using trigger for my customer’s project. I did some investigation and got some interesting results.

Continue reading »

Sep 102012
location:     AppendOnlyVerStoreMgr.cpp:776
Expression:   0
SPID:         64
Process ID:   4912
Description:  Cannot locate version record and page is not allocated. Status = 3
Msg 3624, Level 20, State 1, Line 1
A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support. 
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

This was the error a Sr. DBA at my client asked me. There error appeared after reading a table from decrypted database which has been encrypted by TDE on SQL Server 2008 R2.

Continue reading »

Sep 042012

SQL PASSCome and join SQL PASS Victoria User Group in Victoria, BC and let’s talk about SQL Server. This will be a great opportunity to improve your SQL skills by learning from others and build your professional network. Meetings will be held 3rd or 4th Thursday every month at campus of Camosun College in Victoria, BC. Membership and attendance of sessions are absolutely free. The schedule for each meeting will be
5:00PM – 6:00PM – Networking.
6:00PM – 8:30PM – Presentation(s)
8:30PM – 9:00PM – Door prizes and Question & Answer session
The first meeting will be on Oct 18, 2012. Visit for more details.


Get every new post on this blog delivered to your Inbox.

Join other followers: