How can we help you today? How can we help you today?

Generator causing a SQL deadlock

Hey all, I'm getting a deadlock with a table I'm trying to populate. Up to 15,000 rows its fine, as I try to go to 20,000 (adding 5,000 more rows) I'm gettting a deadlock from SQL Data Generator between the bulk copy and the query to find data for a column based on a foreign key where its trying to figure out what values haven't been used yet (using Repeat Keys at random). If I add a row or two it works. Suggestions? I've tried disabling triggers on the target table but they aren't involved with this lock.

The blocker (generated by Data Generator):
SELECT tlb0.[ID]
FROM [dbo].[RECORDS] AS tlb0
LEFT OUTER JOIN [dbo].[SOLICITOR_GOAL] AS ExistingRows
ON ExistingRows.[RECORDS_ID] = tlb0.[ID]
WHERE ExistingRows.[ID] IS NULL
ORDER BY tlb0.[ID]

The blockee:
insert bulk [dbo].[SOLICITOR_GOAL] ([RECORDS_ID] Int, [CAMPAIGN_ID] Int, [FUND_ID] Int, [TYPE] Int, [CATEGORY] Int, [GOAL] Decimal(30,6), [TEAM_ID] Int, [REPORTS_TO_ID] Int, [NOTES] Text COLLATE SQL_Latin1_General_CP1_CI_AS, [SEQUENCE] Int, [IMPORT_ID] VarChar(20) COLLATE SQL_Latin1_General_CP1_CI_AS, [Depth] Int, [Lineage] VarChar(255) COLLATE SQL_Latin1_General_CP1_CI_AS, [APPEAL_ID] Int) with (KEEP_NULLS, CHECK_CONSTRAINTS, FIRE_TRIGGERS)
bostonaudi
0

Comments

3 comments

Sort by
  • Linda Hawksworth
    Please can you run the generation again, select the small SQL Data Generator icon in the top left-hand corner of the screen, Locate temporary files and Log file and send across the text files from those locations (by default C:\Documents and Settings\user.name\Local Settings\Temp\Red Gate\SDG). If possible, please also send across your object creation SQL and your SQL Data Generator project file.

    Please quote this forum post in your mail.
    Linda Hawksworth
    0
  • bostonaudi
    Please can you run the generation again, select the small SQL Data Generator icon in the top left-hand corner of the screen, Locate temporary files and Log file and send across the text files from those locations (by default C:\Documents and Settings\user.name\Local Settings\Temp\Red Gate\SDG). If possible, please also send across your object creation SQL and your SQL Data Generator project file.

    Please quote this forum post in your mail.

    Just got back from vacation, and my trial expired. Can I renew the trial? If I can get the tool to work I will purchase a copy.

    thanks, John
    bostonaudi
    0
  • bostonaudi
    bostonaudi wrote:
    Please can you run the generation again, select the small SQL Data Generator icon in the top left-hand corner of the screen, Locate temporary files and Log file and send across the text files from those locations (by default C:\Documents and Settings\user.name\Local Settings\Temp\Red Gate\SDG). If possible, please also send across your object creation SQL and your SQL Data Generator project file.

    Please quote this forum post in your mail.

    Just got back from vacation, and my trial expired. Can I renew the trial? If I can get the tool to work I will purchase a copy.

    thanks, John

    May have found the cause, the database I was using is a demo database and has a record count restriction of 15,000, which may have manifested this error. Investigating further.
    bostonaudi
    0

Add comment

Please sign in to leave a comment.