Database Administration

Changed SoundEx Disables Index While Upgrading to SQL 2012

by

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 … Read more

Bring Offline File Online — Modify SQL Server Metadata

by

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.

Read more

Have You Seen This Error?

by 
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.

Read more

Modification Count of Statistics

by

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.

Read more

Produce CLR_MONITOR Wait Type

by

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.

Read more

Are Jobs Currently Running?

by

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)

Read more

New Way to Relocate Resource Database

by

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.

Read more

SQL Mail Removed in SQL Server 2012

by

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 … Read more

SQL Server Error Log

by

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.

Read more

Setup Procedure to Run Automatically When SQL Server Starts

by

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.

Read more