August 20, 2011 at 8:36 am
Paul Randal (8/19/2011)
You've hit bugs in rollback - contact PSS and they'll work to get a workaround and fix. Nothing much else to do without being able to step through the code as it's rolling back the operation, which I can't do any more. And neither can Kimberly - from your email to her.
Just hung up with PSS. FYI 695$ for a week-end high<est> priority call! Good thing it won't get charged at the end of the process.
They've been able to duplicate the issue. It's been filed as a bug with the dev team.
I'll keep you posted as I get more info.
@P Randal.
I just had to send that note to your dear wife. The subject line was too funny to not send :-D.
Now it's just too bad she didn't get this while doing immersion class :w00t:. That could have been the high note of the event :kiss:
On another Note, WayneS has offered to run this on 6.5, 7, and 2000. We'll see if the issue exists there as well.
August 20, 2011 at 8:46 am
Workaround :
Comment this out
ALTER TABLE dbo.RPT_Historique_Couts
ALTER COLUMN DateHistorique_fin DATETIME NULL
then use this to insert instead :
SELECT
hist.NoArticle
, CTE.dt AS DateHistorique
--, NULL AS DateHistorique_fin --this corrups the db
, '9999-12-31' AS DateHistorique_fin -- this works
, CTE.Cout AS CoutDerniereFacture
, CTE.PO AS NoFactureAchat
FROM
CTE CTE
INNER JOIN dbo.RPT_Historique_Couts hist
ON CTE.NoArticle = hist.NoArticle
AND CTE.Previous = hist.DateHistorique
ORDER BY
CTE.NoArticle
, CTE.dt
August 22, 2011 at 6:07 am
Repro scripts without having to restore.
I had nagging feeling that there might be something in the db itself that was helping this.
Script tested on 2k5 sp3 and 2k8 R2 SP1.
Bug not found anymore on denali with this new script and I don't have time to debug this any further.
August 24, 2011 at 3:48 pm
Ninja's_RGR'us (8/19/2011)
Issue posted on connect
Got a reply today that the connect team was able to reproduce on the latest R2 and denali releases and that this bug will definitely get the proper attention.
(I can't really imagine MS saying to is by design or not important to fix since it breaks the acid requirement of the RDBMS).
This is in the hands of both PSS and connect ATM. I'll keep y'all posted on this.
August 25, 2011 at 7:06 pm
Ninja's_RGR'us (8/18/2011)
@PW. You can see the reason for my confusion => http://sqlcat.com/members/paul-white-nz/default.aspx😀
Especially with all the super deep level 500 stuff you always blog about. Makes one wonder what the heck you do to know all that!
Thanks again for giving this a go! A couple minds on this one can't be too much!
That page would definitely confuse me also.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 26, 2011 at 5:34 am
FYI: I can reproduce both bugs (Remi's and Paul's) in SQL 2008 SP1. I've added my votes and comments to the Connect items.
August 26, 2011 at 10:18 am
Brandie Tarvin (8/26/2011)
FYI: I can reproduce both bugs (Remi's and Paul's) in SQL 2008 SP1. I've added my votes and comments to the Connect items.
Thanks.
August 26, 2011 at 10:19 am
Reposting a private e-mail from wayne with interesting test results.
P.S. @Wayne Don't waste too much time on 6.5 I was mostly curious about a behavior change between 2k and 2k5.
WayneS (8/25/2011)
Ninja's_RGR'us (8/25/2011)
In theory you'd only need to amend the db options section to run pre 2k5.HAH!
SQL 2000 (on Windows 2000):
In the create db script, had to remark out the following:
--ALTER DATABASE Corrupt SET DISABLE_BROKER
--ALTER DATABASE Corrupt SET AUTO_UPDATE_STATISTICS_ASYNC OFF
--ALTER DATABASE Corrupt SET DATE_CORRELATION_OPTIMIZATION OFF
--ALTER DATABASE Corrupt SET TRUSTWORTHY OFF
--ALTER DATABASE Corrupt SET ALLOW_SNAPSHOT_ISOLATION ON
--ALTER DATABASE Corrupt SET PARAMETERIZATION SIMPLE
--ALTER DATABASE Corrupt SET READ_COMMITTED_SNAPSHOT OFF
--ALTER DATABASE Corrupt SET HONOR_BROKER_PRIORITY OFF
--ALTER DATABASE Corrupt SET PAGE_VERIFY CHECKSUM
--ALTER DATABASE Corrupt SET DB_CHAINING OFF
In the script to corrupt, had to convert the CTE (new in 2k5) to a subquery.
When that script was run, it generated following errors:
Server: Msg 601, Level 12, State 6, Line 74
Could not continue scan with NOLOCK due to data movement.
Server: Msg 644, Level 21, State 1, Line 74
Could not find the index entry for RID '3600000000029f000000000000000000000300020100250033384542413332372d35453931' in index page (1:177), index ID 0, database 'Corrupt'.
ODBC: Msg 0, Level 16, State 1
Communication link failure
Connection Broken
Database does not appear to be corrupt. select * from master.dbo.sysdatabases has this db with same status as master, msdb, pubs. (24)
select * from dbo.RPT_Historique_Couts returns 9816 rows, so can connect and do queries. db doesn't appear to be in a suspect stage. However, error message definitely shows something went haywire.
SQL 7 (on NT4)
most of the alter database statements don't work.
ASC not a keyword, had to remove it everywhere (PKs, UQs, etc)
create db script won't run: decimal(38) > max of 28
SQL 6.5 (on NT4)
stuff from SQL 6.5 above. (ALTER DATABASE adds pages to database...)
cannot use [] to quote identifiers... need dbl-quote
table names too long. (script won't run)
If you want, I can try to get a script to run on 6.5... but I don't know if it will cause the error. With the db not going into corrupt status in 2000, I feel that it is doubtful.
August 26, 2011 at 10:19 am
WayneS (8/25/2011)
Ninja's_RGR'us (8/18/2011)
@PW. You can see the reason for my confusion => http://sqlcat.com/members/paul-white-nz/default.aspx😀
Especially with all the super deep level 500 stuff you always blog about. Makes one wonder what the heck you do to know all that!
Thanks again for giving this a go! A couple minds on this one can't be too much!
That page would definitely confuse me also.
I'm still hoping for an explaination on that one... if there's one :-D.
August 26, 2011 at 11:29 am
Ninja's_RGR'us (8/26/2011)
I'm still hoping for an explaination on that one... if there's one :-D.
I signed up as a member when the site first got going and I wanted to leave a comment on one of the articles. That's all I did, and now I can't log in to change that page, even if I wanted to. As it is I'm quite happy for people to think I am a member of SQLCat!
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 7, 2011 at 6:51 am
I'd love it if 1-2 of you guys could rerun the test with 2 modification. Set implicit transactions OFF and put begin tran before the first select into.
PSS is telling me that this bug is "all" caused within implicit tran but I'm able to reproduce with begin tran and not pss with their own repro script.
Which they don't share at the moment.
I'd like a couple more "fails for me too" to make sure this gets the proper attention.
September 7, 2011 at 6:54 am
I've already checked "fails for me too" on this. But give me a few to finish catching up at the office and I'll see if I can get this to corrupt the db again with your mods.
September 7, 2011 at 6:59 am
Brandie Tarvin (9/7/2011)
I've already checked "fails for me too" on this. But give me a few to finish catching up at the office and I'll see if I can get this to corrupt the db again with your mods.
I've followed Paul's repeated advice to contact Product support to open an "urgent" ticket. This is where I need the me too with a slight difference in the script, not on connect. They seem to be heading down a path that only implicit transactions are getting the bug, but I can repro with explicit transactions too.
September 7, 2011 at 10:08 am
fails on Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
with
Msg 4060, Level 11, State 1, Line 65536
Cannot open database "Corrupt" requested by the login. The login failed.
Msg 18456, Level 14, State 1, Line 65536
Login failed for user xxx
Database is marked "suspect"
fails on Microsoft SQL Server 2005 - 9.00.5057.00 (Intel X86)
with
Msg 3316, Level 21, State 2, Line 135
During undo of a logged operation in database 'Corrupt', an error occurred at log record ID (97:136:51). The row was not found. Restore the database from a full backup, or repair the database.
Msg 3314, Level 21, State 4, Line 135
During undoing of a logged operation in database 'Corrupt', an error occurred at log record ID (97:136:51). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.
Msg 9001, Level 21, State 1, Line 135
The log for database 'Corrupt' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
Msg 3314, Level 21, State 5, Line 135
During undoing of a logged operation in database 'Corrupt', an error occurred at log record ID (92:192:1). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.
Database is marked "suspect"
I would have to contact PSS privately since I've tested it on my home system. Kinda scared about the standard billing rate though.... Any "workaround"?
September 7, 2011 at 10:12 am
Thanks Lutz. A couple more "me toos" here should be enough to make sure they consider it correctly. Sounds like they "over simplified" their version of the repro script.
I'll forward them to this thread if need be.
Viewing 15 posts - 61 through 75 (of 78 total)
You must be logged in to reply to this topic. Login to reply