Attach Database with Missing NDF File

Posted on by John H15 Comments on Attach Database with Missing NDF File

I wonder if you’ve had the situation that I had before where you have to attach a database with one or few missing .ndf files. It seems pretty common since I’ve just seen the same scenario at one of my clients. The reasons behind it are quite similar – They created a new file group with files to host some temporary data for data fixing or testing. New files were not created under the folder as usual and they thought they would remove them right after tasks complete. But the latter part got forgotten afterwards.

Continue reading “Attach Database with Missing NDF File” →

Fix Page Checksum

Posted on by John H8 Comments on Fix Page Checksum

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 “Fix Page Checksum” →

SQL Server +0 and -0

Posted on by John H2 Comments on SQL Server +0 and -0

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, , http://support.microsoft.com/kb/923247. — 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 “SQL Server +0 and -0” →

SQL Saturday #198 Feb 16, 2013

Posted on by John HLeave a Comment on SQL Saturday #198 Feb 16, 2013

SQL Saturday 198, http://www.sqlsaturday.com/198/ ; is coming to Vancouver Feb 16 this year.  As of now, 56 sessions are collected from 30 speakers, http://www.sqlsaturday.com/198/schedule.aspx?sat=198. 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.

NULL Values Impact the Performance of MAX() and MIN()

Posted on by John H3 Comments on NULL Values Impact the Performance of MAX() and MIN()

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 “NULL Values Impact the Performance of MAX() and MIN()” →

Inserted and Deleted are One Table?

Posted on by John H5 Comments on Inserted and Deleted are One Table?

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 “Inserted and Deleted are One Table?” →

Have You Seen This Error?

Posted on by John H2 Comments on Have You Seen This Error? 
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 “Have You Seen This Error?” →