Why me?

· Uncategorized
Authors

Why does it always happen to a nice guy like me? Once you arrive at a new customer, you get dragged from one issue into another.

First thing on the agenda was to validate a migration process. I can do this on my slippers; at least I thought I could.
All existing databases on SQL2005 were copied using SAN snapshots and attached into SQL2008R2, so running a DBCC CHECKDB was certainly justified.

You might predict what happened! We found corruption in one of the user databases:

Msg 8905, Level 16, State 1, Line 1
Extent (3:1430120) in database ID 8 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
CHECKDB found 1 allocation errors and 0 consistency errors not associated with any single object.
CHECKDB found 1 allocation errors and 0 consistency errors in database ‘DB1’.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (DB1).

 Just a bit of corruption in the GAM, nothing to worry about. So I fully confident ran a DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS. 
Subsequent DBCC CHECKDB gave no errors

Problem solved you might think?

–>WRONG

Inspecting the SQL errorlog, frequent Stack Dumps were generated each time an SSIS package was executed.

2011-06-08 09:01:15.32 spid121     Stack Signature for the dump is 0x00000000F9DBBFD0
2011-06-08 09:01:15.92 spid121     External dump process return code 0x20000001.
External dump process returned no errors.2011-06-08 09:01:15.92 Server      Error: 17310, Severity: 20, State: 1.
2011-06-08 09:01:15.92 Server      A user request from the session with SPID 121 generated a fatal exception. SQL Server is terminating this session. Contact Product Support Services with the dump produced in the log directory.
2011-06-08 09:01:15.93 spid120     Using ‘dbghelp.dll’ version ‘4.0.5’
2011-06-08 09:01:15.94 spid120     ***Stack Dump being sent to E:\SQL2008R2\MSSQL10_50.MSSQLSERVER\MSSQL\LOG\SQLDump0022.txt
2011-06-08 09:01:15.94 spid120     SqlDumpExceptionHandler: Process 120 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
2011-06-08 09:01:15.94 spid120     * *******************************************************************************
2011-06-08 09:01:15.94 spid120     *
2011-06-08 09:01:15.94 spid120     * BEGIN STACK DUMP:
2011-06-08 09:01:15.94 spid120     *   06/08/11 09:01:15 spid 120
2011-06-08 09:01:15.94 spid120     *
2011-06-08 09:01:15.94 spid120     *
2011-06-08 09:01:15.94 spid120     *   Exception Address = 0000000002019BB2 Module(sqlservr+00000000012D9BB2)
2011-06-08 09:01:15.94 spid120     *   Exception Code    = c0000005 EXCEPTION_ACCESS_VIOLATION
2011-06-08 09:01:15.94 spid120     *   Access Violation occurred reading address 0000000000000048
2011-06-08 09:01:15.94 spid120     * Input Buffer 122 bytes –
2011-06-08 09:01:15.94 spid120     *              set fmtonly on exec sp_execute 18 set fmtonly off

 Related to the “REPAIR_ALLOW_DATA_LOSS” you might think?

–>WRONG

Inspecting the SQL error log, these stack dumps occurred also weeks before this intervention. More, one and the same SSIS package failed each time reporting a deadlock issue. So I activated trace flags 1204 and 1222.

Guess what? When running the package, deadlocks were logged on the sys.sysschobjs system table immediately followed by our well known stack dumps.

2011-06-08 09:01:04.45 spid24s       resource-list
2011-06-08 09:01:04.45 spid24s        keylock hobtid=281474978938880 dbid=11 objectname=ODT.sys.sysschobjs indexname=clst id=lock51eef8f00 mode=X associatedObjectId=281474978938880
2011-06-08 09:01:04.45 spid24s         owner-list
2011-06-08 09:01:04.45 spid24s          owner id=process34f9d8088 mode=X
2011-06-08 09:01:04.45 spid24s         waiter-list
2011-06-08 09:01:04.45 spid24s          waiter id=processd3f948 mode=S requestType=wait
2011-06-08 09:01:04.45 spid24s        keylock hobtid=281474978938880 dbid=11 objectname=ODT.sys.sysschobjs indexname=clst id=lock51eef8f00 mode=X associatedObjectId=281474978938880
2011-06-08 09:01:04.45 spid24s         owner-list
2011-06-08 09:01:04.45 spid24s         waiter-list
2011-06-08 09:01:04.45 spid24s          waiter id=process3bc269948 mode=S requestType=wait
2011-06-08 09:01:04.45 spid24s        keylock hobtid=281474978938880 dbid=11 objectname=ODT.sys.sysschobjs indexname=clst id=lock5021c5180 mode=X associatedObjectId=281474978938880
2011-06-08 09:01:04.45 spid24s         owner-list
2011-06-08 09:01:04.45 spid24s          owner id=process3bc269948 mode=X
2011-06-08 09:01:04.45 spid24s         waiter-list
2011-06-08 09:01:04.45 spid24s          waiter id=process34f9d8088 mode=S requestType=wait
2011-06-08 09:01:04.75 spid9s      Deadlock encountered …. Printing deadlock information

 So there must be some kind of relation between the deadlocks and the stack dumps I hear you think?

–>WRONG

To get rid of the deadlocks, I set the database in READ_COMMITTED_SNAPSHOT:

ALTER DATABASE DB1 SET READ_COMMITTED_SNAPSHOT ON;

Guess what? Deadlocks were gone, but Stack dumps were still there.

–>BUMMER

So no relation to repairing with data loss; no relation to deadlocks. Why does this single package fail, and not the 300 others running on the same server?

However, there was one thing this package uses and not the others; synonyms!

After the help of a MS engineer we could reproduce our issue on two lines:

SET NO_BROWSETABLE ON
go
select top 10 * from DB1.dbo.MySynonymn

This is a known bug in SQL2008R2 that was fixed in CU6. So we had 2 options:

  1. Install CU6
  2. Replace synonyms with views or get rid of the NO_BROWSETABLE setting.

We finally choose the second option; as the NO_BROWSETABLE setting was impossible to adapt within the SSIS packages(?), replacing the synonyms was my last hope.

AND IT WORKS!!!!

A new week coming up with new challenges.

2 Comments

Comments RSS
  1. Jacinthe

    We worked on that cases for hours until we found your blog. We had EXACTLY the same problem as you described and couldn’t find a way to resolve it until we tried your suggestion — replace synonym with views. And it WORKED thank you SO MUCH !!!!! You saved our lives !!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: