Schedule Sproc "stops" half way though. (Been running for months without issue)
Running it manually suggests it is failing on a particular DELETE. I changed the DELETE to a SELECT COUNT(*) and ran that one statement manually and got:
The command(s) completed successfully.
surely I should get a row output from SELECT COUNT(*)?
RAISERROR (N'Delete Start', 10, 1) WITH NOWAIT
-- SELECT COUNT(*)
FROM dbo.MyTable AS D
JOIN ##TEMP_MyTable AS S
ON S.MyPK1 = D.MyPK1 COLLATE SQL_Latin1_General_CP1_CI_AS
AND S.MyPK2 = D.MyPK2
(S.MyCol3 <> D.MyCol3 COLLATE SQL_Latin1_General_CP1_CI_AS
OR (S.MyCol3 IS NULL AND D.MyCol3 IS NOT NULL)
OR (S.MyCol3 IS NOT NULL AND D.MyCol3 IS NULL))
OR (S.MyCol4 <> D.MyCol4 COLLATE SQL_Latin1_General_CP1_CI_AS
OR (S.MyCol4 IS NULL AND D.MyCol4 IS NOT NULL)
OR (S.MyCol4 IS NOT NULL AND D.MyCol4 IS NULL))
... etc. ...
SELECT @intRowDel = @@ROWCOUNT
RAISERROR (N'Delete Completed', 10, 1) WITH NOWAIT
Running the SProc I see the "Delete Start", but nothing after that - not even the "Delete Completed". Transaction the Sproc is running in is terminated, no value returned, the batch is aborted (so I can't display the return value even if there is one)
This in the (application) Event Log which tallies with the time that the query stopped working properly:
"A page that should have been constant has changed (expected checksum: 148697dd, actual checksum: 143eb63d, database 8, file 'E:\MSSQL\DATA\MyDatabase.mdf', page (1:1866135)). This usually indicates a memory failure or other hardware or OS corruption."
DBCC CHECKDB WITH NO_INFOMSGS, EXTENDED_LOGICAL_CHECKS, DATA_PURITY
on the database, TEMPDB, MASTER and MSDB. No errors found.
SELECT @@version =
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
Apr 2 2010 15:48:46
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 5.2 <X64> (Build 3790: Service Pack 2) (Hypervisor)
Database is set to SIMPLE Recovery Model