March 22, 2019 at 10:58 am
Hello,
We have a .NET application that executes a stored procedure in SQL that runs a Merge Query for UPDATES, INSERTS, DELETES. I know the MERGE runs a single batch. The source table that is being scanned has over 800 million rows, and each column is unique so there is a ton of sorts and groupings happening during the merge. This is causing the below error. Is there any way to batch a merge query in SQL? BTW, the TEMP space cannot be increased due to hardware limitations, and this is only a temporary MERGE that will need to be run once or twice.
2019-03-22 09:05:34.470 Error Could not allocate a new page for database 'TEMPDB' because of insufficient disk space in filegroup 'DEFAULT'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
March 22, 2019 at 12:04 pm
Without seeing the actual MERGE, can't get into specifics.
But could you try batching it yourself. For example, if the table being MREGEd has a unique clustering key, then do separate MERGEs based on a specific range of clustering key values for each MERGE.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 22, 2019 at 12:30 pm
CREATE PROCEDURE [dbo].[RDS_MergeQuery]
@p_SourceDLSDTable varchar(max),
@p_TargetDLTable varchar(max),
@p_axAppName varchar(max),
@p_timestamp varchar(25),
@rowsAffected INT OUT
AS
SET NOCOUNT ON;
DECLARE @SQLString as varchar(max)
-- Performs UPDATES, DELETES, and INSERTS
SET @SQLString =
'MERGE dbo.' + @p_TargetDLTable + ' as TARGET
USING dbo.' + @p_SourceDLSDTable + ' as SOURCE
ON(TARGET.docid = source.docid and target.objectid = source.objectid and target.subpagenum = source.subpagenum
and target.annote = source.annote and target.pagever = source.pagever and target.pathid = source.pathid)
WHEN MATCHED and source.pagenum != target.pagenum OR source.annote != target.annote OR source.subpagenum != target.subpagenum OR source.pagever != target.pagever OR source.formatid != target.formatid OR source.ftoffset != target.ftoffset OR source.ftcount != target.ftcount and source.clipid != ''X''
THEN UPDATE SET target.objectid = source.objectid,target.pagenum = source.pagenum,target.annote = source.annote ,target.subpagenum = source.subpagenum , target.pagever = source.pagever , target.formatid = source.formatid , target.ftoffset = source.ftoffset , target.ftcount = source.ftcount
WHEN NOT MATCHED BY SOURCE
THEN DELETE
WHEN NOT MATCHED BY TARGET and source.clipid != ''X''
THEN INSERT(docid, pagenum, subpagenum, pagever, objectid, pathid, annote, formatid, ftoffset, ftcount) VALUES(
source.docid, source.pagenum, source.subpagenum, source.pagever, source.objectid, source.pathid, source.annote, source.formatid, source.ftoffset, source.ftcount)
OUTPUT
$action ChangeType,
Inserted.docid ''[NewUpdateDocID]'',
Inserted.objectid ''[NewUpdateObjectID]'',
Inserted.pagenum ''[NewUpdatePageNum]'',
Inserted.pagever ''[NewUpdatePageVer]'',
Inserted.subpagenum ''[NewUpdateSubPageNum]'',
Inserted.annote ''[NewUpdateAnnote'',
Deleted.docid ''[DeletedDocID'',
Deleted.objectid ''[DeletedObjectID'',
Deleted.pagenum ''[DeletedPageNum'',
Deleted.pagever ''[DeletedPageVer'',
Deleted.subpagenum ''[DeletedSubPageNum'',
Deleted.annote ''[DeletedAnnote]'',
(''' + @p_timestamp + ''') ''[TimeStamp]''
INTO [dbo].[Rds_Diff_' + @p_axAppName + '];'
EXECUTE(@SQLString)
SELECT @rowsAffected = @@ROWCOUNT;
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply