Why you should not use undocumented commands in production server?

Today I worked on a interesting issue...

There was a Non-yielding scheduler dump generated and continuos Non-yielding scheduler error messages in SQL Server errorlog. This somehow caused Latch timeouts and Buffer Latch timeouts and SQL Server went unresponsive.

When we analyzed the dump, thread which is owning the latch was running the following query and query kept running without coming out of scheduler...

DECLARE cursor1 CURSOR FOR

SELECT name FROM master.dbo.sysdatabases WHERE status&512 = 0
OPEN cursor1
FETCH NEXT FROM cursor1 INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQLString = 'USE [' + @DBName + '] DBCC LOGINFO WITH NO_INFOMSGS'
/* Some business logic here */
FETCH NEXT FROM cursor1 INTO @DBName
END
DEALLOCATE cursor1
 
Nothing is wrong with this query but what went wrong?
 
Problem was this instance had a snapshot database (CREATE DATABASE AS SNAPSHOT OF) and as explained in this link http://connect.microsoft.com/SQLServer/feedback/details/292136/running-dbcc-loginfo-on-db-snapshot SPID kept spinning for a spinlock without knowing that there is no log file for the database snapshot which caused Non-yielding scheduler situation in SQL Server.
 
Issue has been fixed in SQL 2008 and solution for SQL 2005 is to exclude the snapshot database name which running DBCC LOGINFO....
 
So this is one more instance to instruct DBA's not to use Undocumented commands in production server which will cause undesired situations in the production server...
 
Also this is an example to quickly generate Non-yielding scheduler and dump in SQL Server :)

SQL Server Trace Flags Repository

I'm really glad to see almost update to date list of SQL Server trace flags repository at SQL Server Central.

Though we use certain trace flags, we intend to forget the explanation about each trace flag so you can go-ahead and refer this anytime:

http://www.sqlservercentral.com/articles/trace+flags/70131/

Also take a look at the KB article http://support.microsoft.com/kb/920093 which talks about lot of unknown trace flags to improve performance on a heavy SQL Servers.

Here is a list of undocumented trace flags, DBCC commands, inline functions etc presented by Paul Randal in a SQL Server connections session

http://devconnections.com/updates/LasVegas_Fall10/SQL/Randal-SQL-SDB407-Undocumented.pdf

Excerpts from 15 Easy Ways to Burn More Fat

I took this notes while reading about "15 Easy Ways to Burn More Fat". I'm saving it here for my reference and it will also help you to burn more fat :)

1. Stay Hydrated
2. Eat Watermelon
3. Drink Milk
4. Get More Vitamin D (tuna, fortified milk and cereal, and eggs)
5. Eat Iron-Rich Foods (Shellfish, lean meats, beans, fortified cereals, and spinach are excellent sources)
5. Fight Fat with Fiber (Oats)
6. Drink Coffee or Tea
7. Rev Up in the Morning (Don’t skip BF)
8. Eat the Heat (about 1 tablespoon of chopped peppers (red or green))
9. Drink Cold Water (drinking 6 cups of cold water a day)
10. Get Up, Stand Up (break up long periods by standing up—for example, while talking on the phone)
11. Go Organic When You Can (Avoid pesticides)
12. Eat More Protein (3 ounces of lean meat, 2 t.poons of nuts, or 8 ounces of low-fat yogurt, to every meal and snack.)
13. Go to Bed Earlier (Sleep for long time)
14. Don't Diet
15. I missed it...

Another Reason to Upgrade to SQL 2005 SP3

Microsoft Recently released SP3 for Production, Just today I came across one new vulnerablity like IE6. Let see some info about SP3 first and about the threat to SQL Server..

Some Info about SP3:
1. November 2008 update of SQL Server Books Online has updates of SP3.
2. The 32-bit version of SP3 can be used to update 32-bit instances that run in Windows-on-Windows 64 (WOW64) x86 emulation mode on a 64-bit system. The 32-bit version will not upgrade any components of a 64-bit instance of SQL Server 2005. To upgrade all components of a 64-bit instance of SQL Server 2005, use the 64-bit version of SP3.
3. 3 Type of Packages available for download as x86, IA64, x64.
4. SQL Server 2005 SP3 will upgrade all editions of SQL Server 2005 to SP3 except Express Edition.
5. As Many as 519 Bugs where fixed noted after SP2 are fixed in SP3.

Release Notes [Important]:
http://download.microsoft.com/download/5/7/1/5718A94A-3931-457B-8567-AA0995E34870/ReleaseNotesSQL2005SP3.htm

List of the bugs that are fixed in SQL Server 2005 Service Pack 3:
http://support.microsoft.com/?kbid=955706

Download Link:
http://go.microsoft.com/fwlink/?LinkId=130243

Now info about new threat for SQL Server:
This vulnerability could leave numerous versions of the database software vulnerable to cyberattack.

Microsoft (NSDQ: MSFT) has confirmed the existence of a new and potentially serious security threat to users of its SQL Server database software.

"Microsoft is aware that exploit code has been published on the Internet for the vulnerability addressed by this advisory," the company said in a bulletin published Monday.

The threat is essentially software code that hackers could use to access or alter corporate databases built with SQL Server. The malicious code could allow what's known in IT security as remote code execution, a process by which hackers could, for instance, alter figures in a bank account without ever setting foot on the bank's premises.

Microsoft said SQL Server 2000, SQL Server 2005, SQL Server 2005 Express Edition, SQL Server 2000 Desktop Engine, and Windows Internal Database (WYukon) are all potentially vulnerable to the threat. It added, however, that it's not aware of any attacks having actually been carried out.

The threat does not affect SQL Server 7.0 Service Pack 4, SQL Server 2005 Service Pack 3, or SQL Server 2008, Microsoft said.

"This vulnerability is not exposed anonymously. An attacker would need to either authenticate to exploit the vulnerability or take advantage of a SQL injection vulnerability in a Web application that is able to authenticate," Microsoft noted in its security bulletin.

Microsoft said it's continuing to investigate the problem and will issue a security patch if necessary -- either as a special download or as part of its regular monthly security update cycle.

In the meantime, Microsoft is urging customers who believe they've been targeted by hackers using the vulnerability to contact Microsoft customer service, as well as the Federal Bureau of Investigation and the Internet Crime Complaint Center.

(Source: InformationWeek)

So its better to upgrade to SP3 imdly. atleast for internet based databases.