Dec 162013

Implementation of SoundEx function is changed in SQL Server 2012, described here. For an example, the result of SOUNDEX(‘Csomorova’) in SQL Server 2012 is C561 whereas the result of it from SQL Server 2008 is C256.

It does not seem very significant to our life. But it gives you surprise when this function is used for a calculated column where the value of it is persisted in the index. The indexes containing this column will be disabled automatically after restoring an SQL Server 2008 database to SQL Server 2012. Here is the result

RESTORE DATABASE successfully processed 0 pages in 2.094 seconds (0.000 MB/sec).
Warning: The index "PK_blablaView" on "blaSchema"."blablaView" was disabled because the implementation of "soundex" have changed.
Warning: Clustered index 'PK_blaView' on view 'blaView' referencing table 'blaTable' was disabled as a result of disabling the index 'PK__blaTable'.
Warning: The index "PK__blaTable" on "blaSchema"."blaTable" was disabled because the implementation of "soundex" have changed.

The solution is to rebuild indexes after restore.

Nov 072013

It seems like I am going to write another none SQL stuff again. No. This is a real life scenario. I am writing an ETL for my customer to load data from a Sybase server running on Unix system. The database I am accessing is a standby log-shipped database which is restored 5 minutes. The user account the ETL is using has very limited right on Sybase Server. The Sybase DBA does not have SQL Server driver on the Unix system. This means that I can’t put log restore as part of my ETL and Sybase DBA does not have connectivity to SQL Server side. We need to coordinate those 2 processes that when one is running the other must not run.
Continue reading »

Aug 312013

It’s so sad to say that there will be no more Microsoft Certified SQL Server Master, Architect, and Solution Master any more after October 1, 2013. We all received this email from Microsoft. If you are still keen in getting the highest technology certification, September this year will be your last chance. It’s also your last chance to stay in this exclusive and highly technical community. Otherwise, waiting for evolved certification in the future and/or joining millions of MCSEs will be your choices. Although it will be retired very soon, I still believe all certified masters possess their expert level knowledge on SQL Server. They have been spent tremendous time on learning SQL Server, practicing, and resolving real life problems. Great experience attending 3 week training session at Microsoft Redmond. Thanks for all my instructors.

May 142013

I blogged DBCC WritePage a year ago here It’s an extremely dangerous command especially with the last parameter “directORbufferpool” of this command turned on. I also showed you how to fix the page checksum here To protect yourself, please ensure you are operating on the database that you want, not system databases. Taking a full backup before playing this command is important. Never run it in your production. Beside that, there is a simple way to rollback changes made by DBCC WritePage…

Continue reading »

May 092013

In my last post, I demonstrated how to mount a database with missing NDF files. In the end, we still have issues removing tables created over missing files, the files are taken offline. Be cautious of taking files offline since there is no (official) way to bring it back online. In either situation, you will need to modify/correct SQL Server metadata. This is allowed in SQL Server 2000 with “Allow ad hoc update” option turned on. However, it’s deprecated in SQL Server 2005 and removed in SQL Server 2008 and upper versions.

Continue reading »

May 072013

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 »

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 »


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

Join other followers: