Changed SoundEx Disables Index While Upgrading to SQL 2012

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.

Bring Offline File Online — Modify SQL Server Metadata

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 “Bring Offline File Online — Modify SQL Server Metadata”

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?”

Modification Count of Statistics

When the size of a database is large or number of rows in a table become more, the built-in auto stats update usually can’t keep the statistics up-to-date. Out dated statistics can’t serve the query optimizer to generate better query planes. Consequentially, performance gets impacted negatively. Maintaining statistcs in a database is a one of the biggests challenge for DBAs. Because they need to know when the statistics updated last time and when they should perform stats update again. However, that’s not sufficient. They also need to know how many accumulated changes have not been reflected in the stats. It’s a simple task back to SQL Server 2000. They can just query column rowmodctr in sys.sysindexes. Unfortunately, this column is broken after SQL Server version 2000. It’s no longer accurate. Even worse, SQL Server 2005 and 2008 do not expose such information at all even though they have it internally.

Continue reading “Modification Count of Statistics”

Produce CLR_MONITOR Wait Type

CLR_MONITOR is one of the wait types in SQL Server 2008. It occurs when a task is currently performing CLR execution and is waiting to obtain a lock on the monitor according to BOL. When this shows up in sys.dm_exec_requests, it means the session is running a CLR which is waiting for lock being granted to an object by using Monitor class.

Continue reading “Produce CLR_MONITOR Wait Type”

Are Jobs Currently Running?

Are jobs currently running? This question is asked very frequently by DBAs. The simplest answer is to use SSMS, navigate to SQL Server Agent, double click on Job Activity Monitor under job folder, then check Status column. It is ok if you have one or few servers. Under the situation where you have dozens, hundred, or thousands of SQL Server instances, the simple task described above would become to an extremely challenging work for human beings. The best way is to check them utilizing machine. Here I provide 2 ways to get job execution status programmatically and also tell you how to get the session id of a running job (if it’s running T-SQL Task)

Continue reading “Are Jobs Currently Running?”

New Way to Relocate Resource Database

For many reasons, you may run into a situation that resource database of SQL Server must be moved to a different location. There are many articles you can find through Google and in BOL such as http://msdn.microsoft.com/en-us/library/ms345408.aspx telling you how to do it. After going through steps indicated in the article, you may sucessfully move your resource database, however, you may fail to do so. This happened to one of my customers. I am going to provide a new way to relocate resource database as a last resort and you can use this way when you are experiencing the same.
Continue reading “New Way to Relocate Resource Database”

SQL Mail Removed in SQL Server 2012

SQL Mail is removed from SQL Server 2012 as Microsoft promised in SQL 2008’s BOL. Configuration “SQL Mail XPs” is removed from SQL 2012, check sp_configure in SQL Server 2012. Supporting procedures are removed as well. Those procedures are removed from master database xp_startmail, xp_stopmail, xp_findnextmsg, xp_readmail, xp_deletemail, xp_sendmail, and sp_processmail. Ensure your SQL Server applications do not use them anymore. If do, have those contract procedures handy before upgrading SQL Servers to 2012.

SQL Server Error Log

SQL Server Error Log records information about SQL Server during runtime. It’s created under SQLServerRootFolder\MSSQL\Log folder for each start of SQL Server. Information in the file is varied. Some of them are information only; no action needed to be taken. Some of them are really the information you want to look at. The simplest way accessing it is to use SSMS->Management->SQL Server Logs. Bur when the log is huge, Log File Viewer will retrieve all logs back until hit the end of the log or all your local memory is exhaused. Here is a simple way you can recyle them.

Continue reading “SQL Server Error Log”

Setup Procedure to Run Automatically When SQL Server Starts

That’s simple, setup a job and run the procedure and schedule it runs when SQL Server agent started. See the problem here? My title is “when SQL Server Start”, not “When SQL Server Agent”. SQL Server can start without agent. Procedure in SQL Server can be started automatically when SQL Server starts. In order to do that, you have to set “scan for startup procs” through sp_configure function, and then use sys.sp_procoption to set a procedure to be launched as server started. See example below.

Continue reading “Setup Procedure to Run Automatically When SQL Server Starts”