No Exceptions should be raised by this code

Hit a fun error today throwing a Severity 020 error. Here is the error from the Error Log

SQL Server Assertion: File: <qxcntxt.cpp>, line=956 Failed Assertion = '!"No exceptions should be raised by
this code"'. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to
check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted.

The error was happening every hour at 34 minutes past the hour. The problem started at 4:34PM the day before and was consistently happening every hour. I figured I had a SQL Agent job throwing some error or something. However after some searching around this error seemed pretty rare.

The alert notification email had some more clues:

Process ID 67 attempted to unlock a resource it does not own: DATABASE: 30 . Retry the transaction,
because this error may be caused by a timing condition. If the problem persists, contact the database administrator.

I figured that the DATABASE: 30 was a reference to a database with the DB_ID of 30. However there was no database with the ID of 30 in the instance. The highest DB_ID was 28. I fired up a SQL Profiler trace to see if I could capture the session and the statement. The Process ID was showing up in the alert email so that helped me track down the statement.

SELECT database_id, object_id, index_id, partition_number, page_io_latch_wait_count, page_io_latch_wait_in_ms
FROM sys.dm_db_index_operational_stats(null,null,null,null)
WHERE page_io_latch_wait_count >0

We have a Diagnostics tool that was running the SQL Statement above. I ran the statement in SSMS and was able to reproduce the error. So now what. Well I noticed we had some databases that were set to Offline. We are in the process of decommissioning this server. I detached a couple of the databases and the statement completed with no errors.

Above the statement that was erroring out was a query to grab all the databases on the server and throw them in a table. My guess is that the statement used to grab all the databases grabbed one of the Offline Databases by mistake. Then the sys.dm_db_index_operational_stats procedure was executed against the offline database thus throwing this wild error.

One problem with that theory is that the offline database was not being returned in the database list query. So maybe the diganostics tool was running off a cached copy of the database list or something. Not sure. Anyway, after the databases were detached the problem went away.

CLR20r3 SSMS

Hit this wild problem today.

2014-01-15_10-45-48

Turns out the problem was caused by the Print Spooler Service. The Print Spooler service was disabled. I enabled it and started it and the problem went away.

Server Manager was also crashing with the same error.

SQL State 42000 Rebuild Index

So when using this script

USE [database name]
GO
exec sp_MSForEachtable @command1=”ALTER INDEX ALL ON ? REBUILD”
GO
exec sp_MSForEachtable @command1=”UPDATE STATISTICS ? WITH FULLSCAN”
GO

I started to receive this error message:

ALTER INDEX failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods. [SQLSTATE 42000]

When you try to rebuild a indexed view or a computed column you will run into this error. I quickly tried adding a SET QUOTED_IDENTIFIER ON to the top of my query but after some reading found out that this has no impact when using the sp_MSForEachTable.

For now I created a DB Maintenance Plan to rebuild the indexes and recompute the statistics. Another way around the issue would be to build a custom script/stored procedure to loop through all the tables in sys.objects (where type = ‘U’ and then using the SET statement before each ALTER INDEX statement.

Error 14274

So I was trying to modify some of my SQL Agent jobs and I got an Error: 14274. Wild.

Background:
We had to rename our production server after software upgrade this weekend. We did not want anyone hitting the old server. The problem started.

Solution:

1. Run a select @@SERVERNAME. Verify that you have the correct server name. The server name was incorrect due to the server rename so I had to run the following commands:
sp_dropserver ‘Old Server Name’

sp_addserver ‘New Server Name’

You then have to restart the SQL Service. Once you restart run the select @@SERVERNAME command again and verify the name is correct.

2. Select * from msdb..sysjobs. This will show you the Originating Server Column. In my case this was the original server name.

3. Run an update statement to fix the server name: update sysjobs set originating_server = ‘New Server Name’ where originating_server = ‘Old Server Name’

After that final update statement I was then able to update/edit/delete the jobs.

Changed database context to

It has been a while since my last post. Ill use the standard excuses of “i was busy” and “i’m on vacation”. Ran across an interesting error message today working on a VBScript.

That blackout portion is my database name. The script is a simple script to query a database and dump some data into a .csv. Well the error started driving me crazy. The code looked correct. I kept pouring over the code again and again looking for an answer.

Did some google searches and did not find a whole lot of information. Finally the answer hit me. While running the script I kept opening it up in a text editor. The resulting CSV is really wide and my header column data is longer than the data in the rows. So one time I opened the CSV in Excel to see the data lined up a little better.

Well I forgot to close the CSV in Excel before running the script again. The error message threw me off. Instead of saying something like access denied or permission denied, it gives me some wild context changed error.

Oh well, I closed down Excel and badda-bing.

X app is slow…fix it

So an application is reported as slow. What do you check first? Here is how I checked an application today that was reported as running slow.

  1. Check the SQL Server Logs
  2. Check the Current Activity/Process Info (SQL Server 2000)
  3. Check to make sure no SQL Jobs are running
  4. Check my sysProcess Tracker. A little database/web app I wrote to go out and collect the sysprocesses table every hour for all servers. Can help show spikes or trends, but only on the hourly level. Would like to get more granular in the future.
  5. DatabaseServerPerf. Another custom solution that will go out and look at CPU, RAM and Disk for all servers on an hourly basis
  6. Then I start again from Step 1 and repeat
  7. I also fire up a Perf Mon Counter log with my presets to start collecting performance data every minute. I do this from a remote server as to hopefully limit the load on the database server. I leave the counter running well after the issue is reported as fixed so I can a clear view of the perf stats during and after. This helps me ensure my performance baseline is set appropriately.

There is  probably a tool out there or single web app that could provide a one stop shop for all this, but what fun is that.

Epic quest: Cannot generate SSPI context

I think I made this way too difficult. The problem first started when one of our Windows 2003 servers crapped out. Total hardware failure. The fix was to replace the server and re-configure. After the new server was in place and working fine, the SQL backup job started failing. The SQL Server was a separate server using a share on the file server via UNC. For the article I will call the File Server FILESERVER1 and the SQL Server SQLSERVER1.

I noticed that the SQL Server Service was using LocalSystem to start the service. The odd thing is that the SQL Server backup job was working fine before the FILESERVER1 failed using the LocalSystem account. I thought this was odd because I have read several articles stating that if you want to backup to a UNC the SQL Server Service must be a domain user and have the appopriate permissions to the share. So I thought, no sweat. I will just change the SQL Server Service to use a domain user with privledges and away we go. I changed the service and got approved for a quick Service Restart. The Service started fine and I could browse the server fine locally using Enterprise Manager.

I then try to hit the server from my laptop using Windows Authentication from Enterprise Manager and received the following error: Cannot generate SSPI context error. I quickly just changed the service back to LocalSystem so I could do some testing on a test box, trying to reproduce the error.

Found this article from Microsoft: http://support.microsoft.com/kb/811889. This is a very good description of the problem and some possible solutions. The article links off to this article which contained the solution to the problem: http://msdn.microsoft.com/en-us/library/aa905162%28SQL.80%29.aspx

Using the setspn resource tool was an interesting adventure. First off I am not a Domain Admin so I had to have some assistance from our Server team. Had a Domain Admin run the following command

setspn -A MSSQLSvc/SQLSERVER1.domain.com sqlaccount

You will need to use the Fully Qualified Name here. You will need to run this for every server you would like to start using a Domain User account. The sqlaccount is the actual Domain User account you want to use. Refer to the Security Account Delegation Article for more details such as what to do if the domains do not match, etc. One gotcha is that it took a little while for the setspn command to make the change to the domain user. I assume it was some kind of Synchronization delay.

While browsing around for the error I sumbled on the ADSI Edit MSC. This is helpful to look at to review the setspn change. You can find the user in the domain and view the property. If you are a Domain Admin you can actually make the change from the ADSIEdit console. see below for screenshots from ADSIEdit:

So after the user was given the attribute for SQLSERVER1, I then hoped back on the server and restarted the SQL Service. Service Started fine, I was able to browse to it from Enterprise Manager on my laptop, and the UNC backup to FILESERVER1 started working again.

Here is another good Microsoft KB that shows how to setup Windows Services Accounts for SQL Server: http://msdn.microsoft.com/en-us/library/aa176564%28SQL.80%29.aspx