SQL Server crash related to tempdb collation

SQL Server crash related to tempdb collation by Robert Heinig II

Closed

as Not Reproducible Help for as Not Reproducible

Type:	Bug
ID:	776579
Opened:	1/15/2013 2:54:03 AM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

In SQL 10.50.4000 a session crashes in a context involving differing collations on tempdb and the current db. Adding COLLATE DATABASE_DEFAULT to the temp table fields prevents the crash.

I've always thought that COLLATE DATABASE_DEFAULT should be the implicit default for all char fields in temp tables, which would probably also fix that years-old bug with SSMS' Securable pages...

Details (expand)

English

SQL Server 2008 R2 SP2 CTP

SQL Engine

Windows Server 2008 R2 Standard (SP1)

English

Get an instance running 2008R2Sp2 (10.50.4000), no CU's. Instance collation Latin1_General_CI_AS. (in my case in a 2cpu Hyper-V 2008 R2 VM). Create a DB with collation SQL_Latin1_General_CP1_CI_AS.

Get Alternate Solution 3 by Chris Morris from
http://www.sqlservercentral.com/articles/String+Manipulation/94365/
and put that function into the test DB.

Run the test bed as follows (present somewhere in that article or the discussions):
-- TEST
-- Crashes in build 10.50.4000 without the collate clauses!
CREATE TABLE #t1 (MyString VARCHAR(8000) COLLATE DATABASE_DEFAULT, Pattern VARCHAR(50) COLLATE DATABASE_DEFAULT)

INSERT INTO #t1
-- Split out the non-numeric components of a string
SELECT 'cbv736456XYZ543534534545XLS', '%[^0-9]%'
-- Retrieve [Tuples] regardless of bracketing characters
UNION ALL
SELECT '[001][0021][00043]', '%[0-9]%'
-- Retrieve only the alphabetic [Tuples]
UNION ALL
SELECT '<001><AAA><00043>', '%[A-Z]%'
-- Retrieve binary strings
UNION ALL
SELECT '01010101A011001B01900C', '%[0-1]%'
-- Retrieve hexadecimal strings
UNION ALL
SELECT '01ABXX02AYY0005ABCZZ', '%[0-9A-F]%'
-- Retrieve words and remove spaces & punctuation between them (from A. Machanic blog)
UNION ALL
SELECT 'The quick-brown fox jumped! over the lazy dog.', '%[a-zA-Z]%'
-- Retrieve masked credit card numbers
UNION ALL
SELECT 'Dwain532500010044****Charley432800245431****', '%[0-9*]%'

SELECT MyString, ItemNumber, Item
FROM #t1
CROSS APPLY dbo.[PatternSplitCM](MyString, Pattern)
WHERE [Matched] = 1

DROP TABLE #t1

Now remove the COLLATE DATABASE_DEFAULT clauses and re-run.

The session (spid) crashes with an access violation exception (C0000005) at address sqlservr+4A698, the SSMS query window gets disconnected after logging
"((7 row(s) affected)
Msg 0, Level 11, State 0, Line 0
Für den aktuellen Befehl ist ein schwerwiegender Fehler aufgetreten. Löschen Sie eventuelle Ergebnisse.
Msg 0, Level 20, State 0, Line 0
Für den aktuellen Befehl ist ein schwerwiegender Fehler aufgetreten. Löschen Sie eventuelle Ergebnisse."
(The SQL Server, its OS *and* the SSMS used are English, the localization must be due to the OS under the SSMS being german - IMHO also a bad design decision. I am forced to use a localized base OS, but I install orginal Dev environments and tools to have a chance to understand the documentation and error messages... Well in this case, that's at least gramatically correct though I don't see the results I'm being instructed to delete)

A cannot resolve collation conflict message I guess?

X64

Hyper-V (On-Premise)

File Name	Submitted By	Submitted On	File Size
Log.zip (restricted)	Robert Heinig II	1/18/2013	-

Posted by Microsoft on 2/14/2013 at 12:26 PM

Hello Robert,
I apologize that the bug was closed as no repro. Typically, we investigate issues (unless it is serious enough) in the latest version of SQL Server and then go from there. In this case, our developer tested the repro in SQL Server 2012 to see if the problem exists there & close the issue as no repro since it was fixed. He did try the repro in SQL Server 2008 R2 also to verify the problem but as I said earlier we decided not to pursue a fix there. Sorry, if this caused any confusion.

--
Umachandar, SQL Programmability Team

Posted by Robert Heinig II on 2/13/2013 at 11:47 PM

That's very good. Only - please do not mark a case as not reproducible first and explain several hours later. I already put in bandwidth and hours to build a demo VM.

Posted by Microsoft on 2/13/2013 at 2:36 PM

Hello Robert,
We looked at this and the problem has been fixed in SQL Server 2012. If you require a fix for older versions then you need to contact CSS.

Thanks
Umachandar

Posted by Microsoft on 1/18/2013 at 11:51 AM

Thanks for providing us the min-dump files. We will see if we can investigate the problem and identity the root cause using that.

--
Umachandar

Posted by Robert Heinig II on 1/18/2013 at 2:30 AM

Zip attached, hope you don't mind I flagged them MS-Only.

Posted by Microsoft on 1/17/2013 at 12:51 PM

Can you please attach the mini-dump files when you hit the exception? These files will be the SQL Server LOG directory.

--
Umachandar, SQL Programmability Team

Posted by Robert Heinig II on 1/16/2013 at 8:07 AM

No need to call CSS - workaround included :)
Also, sorry, for erroneous wording: It's not the service that crashes, it's just the one spid. I had unexpected recovery messages in my log, but those were unrelated, forgot to check the exact timestamps. Will fix above for future readers.

Posted by Microsoft on 1/15/2013 at 2:23 PM

Hello Robert,
Thanks for reporting the issue. Did you call CSS regarding the problem to troubleshoot this further? We will take a look at your repro & investigate the issue.

--
Umachandar, SQL Programmability Team