Footer rows to skip: below

Showing posts with label below. Show all posts

Tuesday, March 27, 2012

Field Alias as Function

I want to change the alias of the fields below to the current month and the
month +1 ie datename(month, getdate()) and datename(month,
dateadd(mm,1,getdate())), so instead of '1' and '2' I have January and
February
Select
case when p1.ExpectedDeliveryDate >= @.Start and p1.ExpectedDeliveryDate <=
dateadd(dd,-1,dateadd(mm,1,@.Start)) then convert(int,ROUND(Q1.TotalValue /
1000, 0)) end as '1',
case when p1.ExpectedDeliveryDate >= dateadd(dd,-1,dateadd(mm,1,@.Start)) and
p1.ExpectedDeliveryDate <= dateadd(dd,-1,dateadd(mm,2,@.Start)) then
convert(int,ROUND(Q1.TotalValue / 1000, 0)) end as '2',
Can anyone please advise how i can acheive this
Regards
JohnJohn wrote:
> I want to change the alias of the fields below to the current month and th
e
> month +1 ie datename(month, getdate()) and datename(month,
> dateadd(mm,1,getdate())), so instead of '1' and '2' I have January and
> February
> Select
> case when p1.ExpectedDeliveryDate >= @.Start and p1.ExpectedDeliveryDate <=
> dateadd(dd,-1,dateadd(mm,1,@.Start)) then convert(int,ROUND(Q1.TotalValue /
> 1000, 0)) end as '1',
> case when p1.ExpectedDeliveryDate >= dateadd(dd,-1,dateadd(mm,1,@.Start)) a
nd
> p1.ExpectedDeliveryDate <= dateadd(dd,-1,dateadd(mm,2,@.Start)) then
> convert(int,ROUND(Q1.TotalValue / 1000, 0)) end as '2',
> Can anyone please advise how i can acheive this
> Regards
> John
A query can't change aliases dynamically at runtime unless you
construct the query itself dynamically (dynamic SQL).
Why is the alias important to you? You don't have to use that column
name when you display or print the result so it ought to be easier to
retrieve a fixed column name and just display the month name in its
place in the client app or report. Most reporting tools for example
will allow you to add whatever dynamic column headings you require.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks David, took you advise and formated column heading in report
John
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1138357083.736038.251280@.g44g2000cwa.googlegroups.com...
> John wrote:
> A query can't change aliases dynamically at runtime unless you
> construct the query itself dynamically (dynamic SQL).
> Why is the alias important to you? You don't have to use that column
> name when you display or print the result so it ought to be easier to
> retrieve a fixed column name and just display the month name in its
> place in the client app or report. Most reporting tools for example
> will allow you to add whatever dynamic column headings you require.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>

Monday, March 26, 2012

Fetch cursor help

Let's say i have 5 unique RRID's, column APID and ITID

RRID - APID - ITID
1 13 700
2 13 700
3 13 700
4 14 700
5 15 700

If I run the stored procedure below, I get the results above however, I want my result to be

RRID - APID - ITID
1 13 700
2 13 700
3 13 700
4 14 701
5 15 702

I want my cursor to loop at the same APID then assign one ITID then move to the next APID and so on...

Any help is highly appreciated...

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

ALTER PROCEDURE InsNewEmployeeImpTaskP2
@.REID int,
@.LOID int,
@.RetValintoutput

Declare @.RRID int
Declare @.APID int
Declare @.intREID varchar(20)
Declare @.intIMID varchar(20)

Declare crReqRec cursor for
select RRID from RequestRecords where REID = @.REID and RRSTatus = 'AC' and APID is not null
open crReqRec
fetch next from crReqRec
into
@.RRID

set @.APID = (select APID from RequestRecords where REID = @.REID and RRID = @.RRID)

set @.intIMID = (SELECT ImplementationGroup.IMID
FROM ImplementationGroup_Location INNER JOIN
ImplementationGroup ON ImplementationGroup_Location.IMID = ImplementationGroup.IMID INNER JOIN
Applications_ImplementationGroup ON ImplementationGroup.IMID = Applications_ImplementationGroup.IMID where APID = @.APID and ImplementationGroup_Location.LOID = @.LOID )

insert into ImplementationTasks
(
IMID,
ITStatus,
ITStatusDate
)
VALUES
(
@.intIMID,
'2',
GetDate()
)
SET @.RetVal = @.@.Identity
while @.@.fetch_status = 0
Begin

Update RequestRecords
set ITID = @.RETVal, RRStatus = 'IA'
where REID = @.REID and RRID = @.RRID

FETCH NEXT FROM crReqRec
into
@.RRID
end

close crReqRec
deallocate crReqRec

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

This is the newer version but still getting the same results. PLEASE HELP...

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER PROCEDURE InsNewEmployeeImpTaskP2
@.REID int,
@.LOID int,
@.RetValintoutput

Declare @.RRID int
Declare @.APID int
Declare @.APID2 int
Declare @.FS1 int
Declare @.FS2 int
Declare @.intREID varchar(20)
Declare @.intIMID varchar(20)

Declare crReqRec cursor local for
select RRID from RequestRecords where REID = @.REID and RRSTatus = 'AC' and APID is not null

open crReqRec
fetch next from crReqRec into @.RRID
set @.FS1 = @.@.fetch_status

Declare crAPID cursor local for
select APID from RequestRecords where REID = @.REID and RRID = @.RRID
open crAPID
fetch next from crAPID into @.APID2
set @.FS2 = @.@.fetch_status
set @.APID2 = (select APID from RequestRecords where APID = @.APID)
set @.intIMID = (SELECT ImplementationGroup.IMID FROM ImplementationGroup_Location INNER JOIN
ImplementationGroup ON ImplementationGroup_Location.IMID = ImplementationGroup.IMID INNER JOIN
Applications_ImplementationGroup ON ImplementationGroup.IMID = Applications_ImplementationGroup.IMID where APID = @.APID2 and ImplementationGroup_Location.LOID = @.LOID )

insert into ImplementationTasks
(IMID, ITStatus,ITStatusDate) VALUES (@.intIMID,'2',GetDate())
SET @.RetVal = @.@.Identity

while @.FS2 = 0
while @.FS1 = 0

Begin
Update RequestRecords
set ITID = @.RETVal, RRStatus = 'IA'
where REID = @.REID and RRID = @.RRID

fetch next from crAPID into @.APID2
FETCH NEXT FROM crReqRec into @.RRID
end

close crReqRec
deallocate crReqRec
close crAPID
deallocate crAPID

--EXEC TrigRetReqRecIDP2 @.REID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOsql

Wednesday, March 21, 2012

Fatal exception error in mSSQL 2000 sp4

Hi all,

We have intermittent fatal exception error since MSSQL 2000 sp3. We have applied sp4 but the problem still can't be solved. Below is part the the SQLdump. Any advise on it? Thanks in advance!

BEGIN STACK DUMP:
* 05/13/06 11:26:08 spid 0
*
* Exception Address = 05EB8187 (vsnupr + 00021F7C Line 0+00000000)
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 000001F0
*
* MODULE BASE END SIZE
* sqlservr 00400000 00CBAFFF 008bb000
* Invalid Address 77F80000 77FFCFFF 0007d000
* ADVAPI32 7C2D0000 7C331FFF 00062000
* KERNEL32 7C570000 7C622FFF 000b3000
* RPCRT4 77D30000 77DA0FFF 00071000
* MSVCP71 7C080000 7C0FBFFF 0007c000
* MSVCR71 7D000000 7D057FFF 00058000
* opends60 41060000 41065FFF 00006000
* SHELL32 782F0000 78534FFF 00245000
* GDI32 77F40000 77F7AFFF 0003b000
* USER32 77E10000 77E6EFFF 0005f000
* SHLWAPI 63180000 631E8FFF 00069000
* msvcrt 78000000 78044FFF 00045000
* COMCTL32 71710000 71793FFF 00084000
* sqlsort 42AE0000 42B6FFFF 00090000
* ums 41070000 4107DFFF 0000e000
* IMM32 75E60000 75E79FFF 0001a000
* sqlevn70 41080000 4108AFFF 0000b000
* NETAPI32 02BF0000 02C3EFFF 0004f000
* Secur32 02C40000 02C4EFFF 0000f000
* NTDSAPI 02C50000 02C60FFF 00011000
* DNSAPI 02C70000 02C93FFF 00024000
* WSOCK32 02CA0000 02CA7FFF 00008000
* WS2_32 02CB0000 02CC3FFF 00014000
* WS2HELP 02CD0000 02CD7FFF 00008000
* WLDAP32 02CE0000 02D09FFF 0002a000
* NETRAP 02D10000 02D15FFF 00006000
* SAMLIB 02D20000 02D2EFFF 0000f000
* AUTHZ 02D80000 02D8FFFF 00010000
* ole32 02FA0000 0308EFFF 000ef000
* XOLEHLP 03090000 03097FFF 00008000
* MSDTCPRX 030A0000 03156FFF 000b7000
* MTXCLU 03160000 0316FFFF 00010000
* VERSION 03170000 03176FFF 00007000
* LZ32 03180000 03185FFF 00006000
* CLUSAPI 03190000 0319FFFF 00010000
* RESUTILS 031A0000 031ACFFF 0000d000
* USERENV 031B0000 03210FFF 00061000
* rnr20 03220000 0322BFFF 0000c000
* iphlpapi 03270000 03282FFF 00013000
* ICMP 03290000 03294FFF 00005000
* MPRAPI 032A0000 032B6FFF 00017000
* OLEAUT32 032C0000 0335AFFF 0009b000
* ACTIVEDS 03360000 0338EFFF 0002f000
* ADSLDPC 03390000 033B2FFF 00023000
* RTUTILS 033C0000 033CDFFF 0000e000
* SETUPAPI 033D0000 0345DFFF 0008e000
* RASAPI32 03460000 03492FFF 00033000
* RASMAN 034A0000 034B0FFF 00011000
* TAPI32 034C0000 034E1FFF 00022000
* DHCPCSVC 034F0000 03508FFF 00019000
* winrnr 039B0000 039B7FFF 00008000
* rasadhlp 039C0000 039C4FFF 00005000
* SSNETLIB 04090000 040A5FFF 00016000
* NTMARTA 040E0000 040FCFFF 0001d000
* WINSPOOL 04110000 0412DFFF 0001e000
* MPR 04170000 0417FFFF 00010000
* security 04430000 04433FFF 00004000
* msafd 04770000 0478DFFF 0001e000
* wshtcpip 047D0000 047D6FFF 00007000
* SSmsLPCn 048F0000 048F7FFF 00008000
* SSnmPN70 04930000 04936FFF 00007000
* mswsock 049D0000 049E1FFF 00012000
* kerberos 04A40000 04A75FFF 00036000
* CRYPTDLL 04A80000 04A8DFFF 0000e000
* MSASN1 04A90000 04A9FFFF 00010000
* rsabase 04DA0000 04DC2FFF 00023000
* CRYPT32 04DD0000 04E56FFF 00087000
* SQLFTQRY 05050000 05075FFF 00026000
* CLBCATQ 05080000 0510FFFF 00090000
* sqloledb 05220000 0529CFFF 0007d000
* MSDART 052C0000 052E3FFF 00024000
* comdlg32 052F0000 0532DFFF 0003e000
* MSDATL3 05330000 05344FFF 00015000
* msv1_0 055D0000 055F0FFF 00021000
* oledb32 05700000 0576FFFF 00070000
* OLEDB32R 05770000 05780FFF 00011000
* xpsqlbot 05790000 05795FFF 00006000
* xpstar 05B00000 05B4CFFF 0004d000
* SQLRESLD 05B50000 05B5BFFF 0000c000
* SQLSVC 05B60000 05B7AFFF 0001b000
* ODBC32 05B80000 05BB9FFF 0003a000
* odbcbcp 05BC0000 05BC5FFF 00006000
* W95SCM 05BD0000 05BDCFFF 0000d000
* SQLUNIRL 05BE0000 05C0CFFF 0002d000
* SHFOLDER 05C10000 05C17FFF 00008000
* odbcint 05D60000 05D76FFF 00017000
* NDDEAPI 05D80000 05D86FFF 00007000
* SQLSVC 05D90000 05D95FFF 00006000
* xpstar 05DA0000 05DA8FFF 00009000
* xplog70 05DC0000 05DCEFFF 0000f000
* xplog70 05DD0000 05DD4FFF 00005000
* msdaora 05DF0000 05E26FFF 00037000
* MSDAORAR 05E30000 05E33FFF 00004000
* ociw32 10000000 1000DFFF 0000e000
* ORA73 05E80000 05ED8FFF 00059000
* CORE35 05EE0000 05F0BFFF 0002c000
* NLSRTL32 05F10000 05F57FFF 00048000
* MSVCRT40 05F60000 05F74FFF 00015000
* MSVCIRT 05F80000 05F91FFF 00012000
* CORE35O 05FA0000 05FC5FFF 00026000
* SQLLib18 05FF0000 06007FFF 00018000
* KG73 06010000 0605AFFF 0004b000
* comsvcs 06060000 061CEFFF 0016f000
* TxfAux 061D0000 06233FFF 00064000
* NLNT 06400000 06419FFF 0001a000
* WINMM 06420000 0644FFFF 00030000
* nttnt 06540000 0654BFFF 0000c000
* msdasql 06730000 06779FFF 0004a000
* MSDASQLR 06780000 06783FFF 00004000
* vfpodbc 06790000 06883FFF 000f4000
* odbccp32 076D0000 076E9FFF 0001a000
* cwbodbc 07C70000 07CE5FFF 00076000
* CWBNL 07CF0000 07D09FFF 0001a000
* cwbrw 07D10000 07D42FFF 00033000
* CwbAb1 07D50000 07D56FFF 00007000
* CWBSV 07D60000 07D7EFFF 0001f000
* CwbCf 07D80000 07D9DFFF 0001e000
* CwbAdNrt 07DA0000 07DB3FFF 00014000
* CwbBb1 07DC0000 07DC8FFF 00009000
* CWBCFTFT 07DD0000 07DD7FFF 00008000
* CWBNL1 07DE0000 07DECFFF 0000d000
* CwbAb 07DF0000 07DFEFFF 0000f000
* CWBCF1 07E00000 07E06FFF 00007000
* CWBNLTRN 07E10000 07E16FFF 00007000
* CwbCo 07E20000 07E57FFF 00038000
* CwbAd 07E60000 07E6CFFF 0000d000
* CwbMsgbx 07E70000 07E7AFFF 0000b000
* CWBNLDLG 07E80000 07E86FFF 00007000
* cwbuireg 07E90000 07E97FFF 00008000
* CwbAd1 07EA0000 07EA7FFF 00008000
* CWBUNPLA 07EB0000 07EE1FFF 00032000
* cwbsof 07EF0000 07F25FFF 00036000
* CwbSy 07F30000 07F39FFF 0000a000
* CWBRC 07F40000 07F51FFF 00012000
* CwbBb 07F60000 07F68FFF 00009000
* CWBUNSSL 07F70000 07F7FFFF 00010000
* MFC42 07F80000 0807AFFF 000fb000
* cwbstp 08080000 08096FFF 00017000
* CWBMSGB 084C0000 084C3FFF 00004000
* cwbcomsg 084D0000 084D4FFF 00005000
* CWBSOMRI 084E0000 084EDFFF 0000e000
* CWBODDLG 084F0000 084F6FFF 00007000
* CWBODMSG 08500000 08505FFF 00006000
* SQLTNSNT 08750000 0877FFFF 00030000
* NSNT 08780000 0879FFFF 00020000
* nasnsnt 087A0000 087C3FFF 00024000
* ntnt 087D0000 087D9FFF 0000a000
* NCRNT 087E0000 08828FFF 00049000
* msadce 08840000 0888CFFF 0004d000
* msadcer 08890000 08894FFF 00005000
* odsole70 09110000 09120FFF 00011000
* MQOA 09270000 092A9FFF 0003a000
* MQRT 092B0000 092CDFFF 0001e000
* MQSec 092D0000 092E3FFF 00014000
* MQUtil 092F0000 0930EFFF 0001f000
* MQDSCli 09310000 09326FFF 00017000
* SQLSRV32 095C0000 09626FFF 00067000
* sqlsrv32 09630000 09645FFF 00016000
* Cwbnetnt 09660000 09666FFF 00007000
* sqlvdi 0A260000 0A26CFFF 0000d000
* adsldp 04720000 04741FFF 00022000
* adsmsext 09EC0000 09ED1FFF 00012000
* sqlmap70 05000000 0502CFFF 0002d000
* MAPI32 05E40000 05E60FFF 00021000
* MSMAPI32 0A320000 0A3E7FFF 000c8000
* GAPI32 0A3F0000 0A407FFF 00018000
* dbghelp 0B540000 0B63FFFF 00100000
*
* Edi: 00000000:
* Esi: 0536AB20: 05DF2B28 00000007 00000001 00001000 05381978 0B1DB
F08
* Eax: 000001F0:
* Ebx: 77E1A7BD: 53EC8B55 A1645756 00000018 F08BC933 0F0C4D39 00008
B85
* Ecx: 00003931:
* Edx: 0B503032: 00000000 00000000 00000000 00000000 00000000 00000
000
* Eip: 05EB8187:
* Ebp: 0B50F98C: 0B50F9B8 05EAFCE3 00000002 00000000 0536AB20 77E1A
7BD
* SegCs: 0000001B:
* EFlags: 00010206: 0057005C 004E0049 0054004E 0073005C 00730079 00650
074
* Esp: 0B50F97C: 00000000 0536AB20 77E1A7BD 000001F0 0B50F9B8 05EAF
CE3
* SegSs: 00000023:
* *****************************************************************************
**
* --
--
* Short Stack Dump
* 05EB8187 Module(ORA73+00038187) (vsnupr+00021F7C)
* 05EAFCE3 Module(ORA73+0002FCE3) (vsnupr+00019AD8)
* 05EC4499 Module(ORA73+00044499) (vsnupr+0002E28E)
* 05EAFB95 Module(ORA73+0002FB95) (vsnupr+0001998A)
* 05EA1959 Module(ORA73+00021959) (vsnupr+0000B74E)
* 05E920A9 Module(ORA73+000120A9) (upiosd+00000063)
* 05E8BA77 Module(ORA73+0000BA77) (oparse+0000007C)
* 10001D88 Module(ociw32+00001D88) (oparse+00000028)
* 05E0EC77 Module(msdaora+0001EC77) (DllRegisterServer+000017B5)
* 780085BC Module(msvcrt+000085BC) (endthreadex+000000BC)
* 7C57B388 Module(KERNEL32+0000B388) (lstrcmpiW+000000B7)
-
* Location : 05EB8187 Module(ORA73+00038187) (vsnupr+00021F7C)
* Return Addr: 05EAFCE3 Module(ORA73+0002FCE3) (vsnupr+00019AD8)
Frame : 0B50F98C
Parameters:
[1] 00000002:
[2] 00000000:
[3] 0536AB20: 05DF2B28 00000007 00000001 00001000 05381978 0B1DBF08
[4] 77E1A7BD: 53EC8B55 A1645756 00000018 F08BC933 0F0C4D39 00008B85
160 bytes of stack data from 0B50F8EC to 0B50F98C
0B50F8EC: 000B0CE8 0A9E7C98 00000104 08A60048 [.....|......H...]
0B50F8FC: 05490178 08A60048 05490178 08A60040 [x.I.H...x.I.@....]
0B50F90C: 000001B7 00000001 00000000 00000000 [................]
0B50F91C: 00000000 00000000 00000000 00000000 [................]
:::: 2 Duplicate lines detected
0B50F94C: 00000000 0A9E7854 0000001C 77F82A8C [....Tx.......*.w]
0B50F95C: 0A9E7860 00000005 77F82A8C 000B0000 [`x.......*.w....]
0B50F96C: 000B0778 00000005 001761A8 0A9E7838 [x........a..8x..]
0B50F97C: 00000000 0536AB20 77E1A7BD 000001F0 [.... .6....w....]

Hi Stephanie,

What OS are you running on (e.g. Windows Server 2003)?

Is the SQL Server configured for AWE and are you using the /PAE switch?
|||OS is Win2000 SP4 and awe is not enabled. Any advise? Thanks|||Hi Stephanie,

I once had a Win 2K SP4 server that spit out errors like the one you are seeing occasionally.

This server had multiple processors on it but the error seemed to go away after I turned down the degree of parallelism so that it was less than the # of processors on the server.

If you've got multiple processors on the server, you might want to consider doing the same thing (turning down the degree of parallelism) and see if that helps any.
|||

Hi Nate,

Thanks for your advise. we have already turn off the parallelism and use 1 processor only (there are 2 processors in the machine). And I just found that the /PAE is turned on in boot.ini. Probably, it is root cause... Many Thanks for your advise.

|||Ah OK....I've read before that /PAE can cause problems so hopefully turning it off would make things better.

Glad I could be of a little help

Fatal exception error in mSSQL 2000 sp3 and sp4

Hi all,
We have intermittent fatal exception error since MSSQL 2000 sp3a. We have
applied sp4 but the problem still can't be solved. Below is part the the
SQLdump. Any advise on it? Thanks in advance!
BEGIN STACK DUMP:
* 05/13/06 11:26:08 spid 0
*
* Exception Address = 05EB8187 (vsnupr + 00021F7C Line 0+00000000)
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 000001F0
*
* MODULE BASE END SIZE
* sqlservr 00400000 00CBAFFF 008bb000
* Invalid Address 77F80000 77FFCFFF 0007d000
* ADVAPI32 7C2D0000 7C331FFF 00062000
* KERNEL32 7C570000 7C622FFF 000b3000
* RPCRT4 77D30000 77DA0FFF 00071000
* MSVCP71 7C080000 7C0FBFFF 0007c000
* MSVCR71 7D000000 7D057FFF 00058000
* opends60 41060000 41065FFF 00006000
* SHELL32 782F0000 78534FFF 00245000
* GDI32 77F40000 77F7AFFF 0003b000
* USER32 77E10000 77E6EFFF 0005f000
* SHLWAPI 63180000 631E8FFF 00069000
* msvcrt 78000000 78044FFF 00045000
* COMCTL32 71710000 71793FFF 00084000
* sqlsort 42AE0000 42B6FFFF 00090000
* ums 41070000 4107DFFF 0000e000
* IMM32 75E60000 75E79FFF 0001a000
* sqlevn70 41080000 4108AFFF 0000b000
* NETAPI32 02BF0000 02C3EFFF 0004f000
* Secur32 02C40000 02C4EFFF 0000f000
* NTDSAPI 02C50000 02C60FFF 00011000
* DNSAPI 02C70000 02C93FFF 00024000
* WSOCK32 02CA0000 02CA7FFF 00008000
* WS2_32 02CB0000 02CC3FFF 00014000
* WS2HELP 02CD0000 02CD7FFF 00008000
* WLDAP32 02CE0000 02D09FFF 0002a000
* NETRAP 02D10000 02D15FFF 00006000
* SAMLIB 02D20000 02D2EFFF 0000f000
* AUTHZ 02D80000 02D8FFFF 00010000
* ole32 02FA0000 0308EFFF 000ef000
* XOLEHLP 03090000 03097FFF 00008000
* MSDTCPRX 030A0000 03156FFF 000b7000
* MTXCLU 03160000 0316FFFF 00010000
* VERSION 03170000 03176FFF 00007000
* LZ32 03180000 03185FFF 00006000
* CLUSAPI 03190000 0319FFFF 00010000
* RESUTILS 031A0000 031ACFFF 0000d000
* USERENV 031B0000 03210FFF 00061000
* rnr20 03220000 0322BFFF 0000c000
* iphlpapi 03270000 03282FFF 00013000
* ICMP 03290000 03294FFF 00005000
* MPRAPI 032A0000 032B6FFF 00017000
* OLEAUT32 032C0000 0335AFFF 0009b000
* ACTIVEDS 03360000 0338EFFF 0002f000
* ADSLDPC 03390000 033B2FFF 00023000
* RTUTILS 033C0000 033CDFFF 0000e000
* SETUPAPI 033D0000 0345DFFF 0008e000
* RASAPI32 03460000 03492FFF 00033000
* RASMAN 034A0000 034B0FFF 00011000
* TAPI32 034C0000 034E1FFF 00022000
* DHCPCSVC 034F0000 03508FFF 00019000
* winrnr 039B0000 039B7FFF 00008000
* rasadhlp 039C0000 039C4FFF 00005000
* SSNETLIB 04090000 040A5FFF 00016000
* NTMARTA 040E0000 040FCFFF 0001d000
* WINSPOOL 04110000 0412DFFF 0001e000
* MPR 04170000 0417FFFF 00010000
* security 04430000 04433FFF 00004000
* msafd 04770000 0478DFFF 0001e000
* wshtcpip 047D0000 047D6FFF 00007000
* SSmsLPCn 048F0000 048F7FFF 00008000
* SSnmPN70 04930000 04936FFF 00007000
* mswsock 049D0000 049E1FFF 00012000
* kerberos 04A40000 04A75FFF 00036000
* CRYPTDLL 04A80000 04A8DFFF 0000e000
* MSASN1 04A90000 04A9FFFF 00010000
* rsabase 04DA0000 04DC2FFF 00023000
* CRYPT32 04DD0000 04E56FFF 00087000
* SQLFTQRY 05050000 05075FFF 00026000
* CLBCATQ 05080000 0510FFFF 00090000
* sqloledb 05220000 0529CFFF 0007d000
* MSDART 052C0000 052E3FFF 00024000
* comdlg32 052F0000 0532DFFF 0003e000
* MSDATL3 05330000 05344FFF 00015000
* msv1_0 055D0000 055F0FFF 00021000
* oledb32 05700000 0576FFFF 00070000
* OLEDB32R 05770000 05780FFF 00011000
* xpsqlbot 05790000 05795FFF 00006000
* xpstar 05B00000 05B4CFFF 0004d000
* SQLRESLD 05B50000 05B5BFFF 0000c000
* SQLSVC 05B60000 05B7AFFF 0001b000
* ODBC32 05B80000 05BB9FFF 0003a000
* odbcbcp 05BC0000 05BC5FFF 00006000
* W95SCM 05BD0000 05BDCFFF 0000d000
* SQLUNIRL 05BE0000 05C0CFFF 0002d000
* SHFOLDER 05C10000 05C17FFF 00008000
* odbcint 05D60000 05D76FFF 00017000
* NDDEAPI 05D80000 05D86FFF 00007000
* SQLSVC 05D90000 05D95FFF 00006000
* xpstar 05DA0000 05DA8FFF 00009000
* xplog70 05DC0000 05DCEFFF 0000f000
* xplog70 05DD0000 05DD4FFF 00005000
* msdaora 05DF0000 05E26FFF 00037000
* MSDAORAR 05E30000 05E33FFF 00004000
* ociw32 10000000 1000DFFF 0000e000
* ORA73 05E80000 05ED8FFF 00059000
* CORE35 05EE0000 05F0BFFF 0002c000
* NLSRTL32 05F10000 05F57FFF 00048000
* MSVCRT40 05F60000 05F74FFF 00015000
* MSVCIRT 05F80000 05F91FFF 00012000
* CORE35O 05FA0000 05FC5FFF 00026000
* SQLLib18 05FF0000 06007FFF 00018000
* KG73 06010000 0605AFFF 0004b000
* comsvcs 06060000 061CEFFF 0016f000
* TxfAux 061D0000 06233FFF 00064000
* NLNT 06400000 06419FFF 0001a000
* WINMM 06420000 0644FFFF 00030000
* nttnt 06540000 0654BFFF 0000c000
* msdasql 06730000 06779FFF 0004a000
* MSDASQLR 06780000 06783FFF 00004000
* vfpodbc 06790000 06883FFF 000f4000
* odbccp32 076D0000 076E9FFF 0001a000
* cwbodbc 07C70000 07CE5FFF 00076000
* CWBNL 07CF0000 07D09FFF 0001a000
* cwbrw 07D10000 07D42FFF 00033000
* CwbAb1 07D50000 07D56FFF 00007000
* CWBSV 07D60000 07D7EFFF 0001f000
* CwbCf 07D80000 07D9DFFF 0001e000
* CwbAdNrt 07DA0000 07DB3FFF 00014000
* CwbBb1 07DC0000 07DC8FFF 00009000
* CWBCFTFT 07DD0000 07DD7FFF 00008000
* CWBNL1 07DE0000 07DECFFF 0000d000
* CwbAb 07DF0000 07DFEFFF 0000f000
* CWBCF1 07E00000 07E06FFF 00007000
* CWBNLTRN 07E10000 07E16FFF 00007000
* CwbCo 07E20000 07E57FFF 00038000
* CwbAd 07E60000 07E6CFFF 0000d000
* CwbMsgbx 07E70000 07E7AFFF 0000b000
* CWBNLDLG 07E80000 07E86FFF 00007000
* cwbuireg 07E90000 07E97FFF 00008000
* CwbAd1 07EA0000 07EA7FFF 00008000
* CWBUNPLA 07EB0000 07EE1FFF 00032000
* cwbsof 07EF0000 07F25FFF 00036000
* CwbSy 07F30000 07F39FFF 0000a000
* CWBRC 07F40000 07F51FFF 00012000
* CwbBb 07F60000 07F68FFF 00009000
* CWBUNSSL 07F70000 07F7FFFF 00010000
* MFC42 07F80000 0807AFFF 000fb000
* cwbstp 08080000 08096FFF 00017000
* CWBMSGB 084C0000 084C3FFF 00004000
* cwbcomsg 084D0000 084D4FFF 00005000
* CWBSOMRI 084E0000 084EDFFF 0000e000
* CWBODDLG 084F0000 084F6FFF 00007000
* CWBODMSG 08500000 08505FFF 00006000
* SQLTNSNT 08750000 0877FFFF 00030000
* NSNT 08780000 0879FFFF 00020000
* nasnsnt 087A0000 087C3FFF 00024000
* ntnt 087D0000 087D9FFF 0000a000
* NCRNT 087E0000 08828FFF 00049000
* msadce 08840000 0888CFFF 0004d000
* msadcer 08890000 08894FFF 00005000
* odsole70 09110000 09120FFF 00011000
* MQOA 09270000 092A9FFF 0003a000
* MQRT 092B0000 092CDFFF 0001e000
* MQSec 092D0000 092E3FFF 00014000
* MQUtil 092F0000 0930EFFF 0001f000
* MQDSCli 09310000 09326FFF 00017000
* SQLSRV32 095C0000 09626FFF 00067000
* sqlsrv32 09630000 09645FFF 00016000
* Cwbnetnt 09660000 09666FFF 00007000
* sqlvdi 0A260000 0A26CFFF 0000d000
* adsldp 04720000 04741FFF 00022000
* adsmsext 09EC0000 09ED1FFF 00012000
* sqlmap70 05000000 0502CFFF 0002d000
* MAPI32 05E40000 05E60FFF 00021000
* MSMAPI32 0A320000 0A3E7FFF 000c8000
* GAPI32 0A3F0000 0A407FFF 00018000
* dbghelp 0B540000 0B63FFFF 00100000
*
* Edi: 00000000:
* Esi: 0536AB20: 05DF2B28 00000007 00000001 00001000 05381978
0B1DB
F08
* Eax: 000001F0:
* Ebx: 77E1A7BD: 53EC8B55 A1645756 00000018 F08BC933 0F0C4D39
00008
B85
* Ecx: 00003931:
* Edx: 0B503032: 00000000 00000000 00000000 00000000 00000000
00000
000
* Eip: 05EB8187:
* Ebp: 0B50F98C: 0B50F9B8 05EAFCE3 00000002 00000000 0536AB20
77E1A
7BD
* SegCs: 0000001B:
* EFlags: 00010206: 0057005C 004E0049 0054004E 0073005C 00730079
00650
074
* Esp: 0B50F97C: 00000000 0536AB20 77E1A7BD 000001F0 0B50F9B8
05EAF
CE3
* SegSs: 00000023:
*
*****************************************************************************
**
*
----
--
* Short Stack Dump
* 05EB8187 Module(ORA73+00038187) (vsnupr+00021F7C)
* 05EAFCE3 Module(ORA73+0002FCE3) (vsnupr+00019AD8)
* 05EC4499 Module(ORA73+00044499) (vsnupr+0002E28E)
* 05EAFB95 Module(ORA73+0002FB95) (vsnupr+0001998A)
* 05EA1959 Module(ORA73+00021959) (vsnupr+0000B74E)
* 05E920A9 Module(ORA73+000120A9) (upiosd+00000063)
* 05E8BA77 Module(ORA73+0000BA77) (oparse+0000007C)
* 10001D88 Module(ociw32+00001D88) (oparse+00000028)
* 05E0EC77 Module(msdaora+0001EC77) (DllRegisterServer+000017B5)
* 780085BC Module(msvcrt+000085BC) (endthreadex+000000BC)
* 7C57B388 Module(KERNEL32+0000B388) (lstrcmpiW+000000B7)
----
* Location : 05EB8187 Module(ORA73+00038187) (vsnupr+00021F7C)
* Return Addr: 05EAFCE3 Module(ORA73+0002FCE3) (vsnupr+00019AD8)
Frame : 0B50F98C
Parameters:
[1] 00000002:
[2] 00000000:
[3] 0536AB20: 05DF2B28 00000007 00000001 00001000 05381978 0B1DBF08
[4] 77E1A7BD: 53EC8B55 A1645756 00000018 F08BC933 0F0C4D39 00008B85
160 bytes of stack data from 0B50F8EC to 0B50F98C
0B50F8EC: 000B0CE8 0A9E7C98 00000104 08A60048 [....|.....H...]
0B50F8FC: 05490178 08A60048 05490178 08A60040 [x.I.H...x.I.@....]
0B50F90C: 000001B7 00000001 00000000 00000000 [............]
0B50F91C: 00000000 00000000 00000000 00000000 [............]
:::: 2 Duplicate lines detected
0B50F94C: 00000000 0A9E7854 0000001C 77F82A8C [...Tx......*.w]
0B50F95C: 0A9E7860 00000005 77F82A8C 000B0000 [`x......*.w...]
0B50F96C: 000B0778 00000005 001761A8 0A9E7838 [x......a..8x..]
0B50F97C: 00000000 0536AB20 77E1A7BD 000001F0 [... .6...w...]Hi Stephanie,
Is the machine you are running on a multiprocessor machine? What sort
of storage system are you using (e.g. RAID, SAN)?
If you have a contract with MS Support (I'm under the impression you
need a contract now to get phone or email support for SQL Server 2000,
could be wrong though) you might consider forwarding them the SQL Dump
and also your mini-dump file (you should've got 2 files out of the
exception, a .txt file and another file which would be your mini-dump
file).
Sorry I can't be of more help.

Fatal exception error in mSSQL 2000 sp3 and sp4

Hi all,
We have intermittent fatal exception error since MSSQL 2000 sp3a. We have
applied sp4 but the problem still can't be solved. Below is part the the
SQLdump. Any advise on it? Thanks in advance!
BEGIN STACK DUMP:
* 05/13/06 11:26:08 spid 0
*
* Exception Address = 05EB8187 (vsnupr + 00021F7C Line 0+00000000)
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 000001F0
*
* MODULE BASE END SIZE
* sqlservr 00400000 00CBAFFF 008bb000
* Invalid Address 77F80000 77FFCFFF 0007d000
* ADVAPI32 7C2D0000 7C331FFF 00062000
* KERNEL32 7C570000 7C622FFF 000b3000
* RPCRT4 77D30000 77DA0FFF 00071000
* MSVCP71 7C080000 7C0FBFFF 0007c000
* MSVCR71 7D000000 7D057FFF 00058000
* opends60 41060000 41065FFF 00006000
* SHELL32 782F0000 78534FFF 00245000
* GDI32 77F40000 77F7AFFF 0003b000
* USER32 77E10000 77E6EFFF 0005f000
* SHLWAPI 63180000 631E8FFF 00069000
* msvcrt 78000000 78044FFF 00045000
* COMCTL32 71710000 71793FFF 00084000
* sqlsort 42AE0000 42B6FFFF 00090000
* ums 41070000 4107DFFF 0000e000
* IMM32 75E60000 75E79FFF 0001a000
* sqlevn70 41080000 4108AFFF 0000b000
* NETAPI32 02BF0000 02C3EFFF 0004f000
* Secur32 02C40000 02C4EFFF 0000f000
* NTDSAPI 02C50000 02C60FFF 00011000
* DNSAPI 02C70000 02C93FFF 00024000
* WSOCK32 02CA0000 02CA7FFF 00008000
* WS2_32 02CB0000 02CC3FFF 00014000
* WS2HELP 02CD0000 02CD7FFF 00008000
* WLDAP32 02CE0000 02D09FFF 0002a000
* NETRAP 02D10000 02D15FFF 00006000
* SAMLIB 02D20000 02D2EFFF 0000f000
* AUTHZ 02D80000 02D8FFFF 00010000
* ole32 02FA0000 0308EFFF 000ef000
* XOLEHLP 03090000 03097FFF 00008000
* MSDTCPRX 030A0000 03156FFF 000b7000
* MTXCLU 03160000 0316FFFF 00010000
* VERSION 03170000 03176FFF 00007000
* LZ32 03180000 03185FFF 00006000
* CLUSAPI 03190000 0319FFFF 00010000
* RESUTILS 031A0000 031ACFFF 0000d000
* USERENV 031B0000 03210FFF 00061000
* rnr20 03220000 0322BFFF 0000c000
* iphlpapi 03270000 03282FFF 00013000
* ICMP 03290000 03294FFF 00005000
* MPRAPI 032A0000 032B6FFF 00017000
* OLEAUT32 032C0000 0335AFFF 0009b000
* ACTIVEDS 03360000 0338EFFF 0002f000
* ADSLDPC 03390000 033B2FFF 00023000
* RTUTILS 033C0000 033CDFFF 0000e000
* SETUPAPI 033D0000 0345DFFF 0008e000
* RASAPI32 03460000 03492FFF 00033000
* RASMAN 034A0000 034B0FFF 00011000
* TAPI32 034C0000 034E1FFF 00022000
* DHCPCSVC 034F0000 03508FFF 00019000
* winrnr 039B0000 039B7FFF 00008000
* rasadhlp 039C0000 039C4FFF 00005000
* SSNETLIB 04090000 040A5FFF 00016000
* NTMARTA 040E0000 040FCFFF 0001d000
* WINSPOOL 04110000 0412DFFF 0001e000
* MPR 04170000 0417FFFF 00010000
* security 04430000 04433FFF 00004000
* msafd 04770000 0478DFFF 0001e000
* wshtcpip 047D0000 047D6FFF 00007000
* SSmsLPCn 048F0000 048F7FFF 00008000
* SSnmPN70 04930000 04936FFF 00007000
* mswsock 049D0000 049E1FFF 00012000
* kerberos 04A40000 04A75FFF 00036000
* CRYPTDLL 04A80000 04A8DFFF 0000e000
* MSASN1 04A90000 04A9FFFF 00010000
* rsabase 04DA0000 04DC2FFF 00023000
* CRYPT32 04DD0000 04E56FFF 00087000
* SQLFTQRY 05050000 05075FFF 00026000
* CLBCATQ 05080000 0510FFFF 00090000
* sqloledb 05220000 0529CFFF 0007d000
* MSDART 052C0000 052E3FFF 00024000
* comdlg32 052F0000 0532DFFF 0003e000
* MSDATL3 05330000 05344FFF 00015000
* msv1_0 055D0000 055F0FFF 00021000
* oledb32 05700000 0576FFFF 00070000
* OLEDB32R 05770000 05780FFF 00011000
* xpsqlbot 05790000 05795FFF 00006000
* xpstar 05B00000 05B4CFFF 0004d000
* SQLRESLD 05B50000 05B5BFFF 0000c000
* SQLSVC 05B60000 05B7AFFF 0001b000
* ODBC32 05B80000 05BB9FFF 0003a000
* odbcbcp 05BC0000 05BC5FFF 00006000
* W95SCM 05BD0000 05BDCFFF 0000d000
* SQLUNIRL 05BE0000 05C0CFFF 0002d000
* SHFOLDER 05C10000 05C17FFF 00008000
* odbcint 05D60000 05D76FFF 00017000
* NDDEAPI 05D80000 05D86FFF 00007000
* SQLSVC 05D90000 05D95FFF 00006000
* xpstar 05DA0000 05DA8FFF 00009000
* xplog70 05DC0000 05DCEFFF 0000f000
* xplog70 05DD0000 05DD4FFF 00005000
* msdaora 05DF0000 05E26FFF 00037000
* MSDAORAR 05E30000 05E33FFF 00004000
* ociw32 10000000 1000DFFF 0000e000
* ORA73 05E80000 05ED8FFF 00059000
* CORE35 05EE0000 05F0BFFF 0002c000
* NLSRTL32 05F10000 05F57FFF 00048000
* MSVCRT40 05F60000 05F74FFF 00015000
* MSVCIRT 05F80000 05F91FFF 00012000
* CORE35O 05FA0000 05FC5FFF 00026000
* SQLLib18 05FF0000 06007FFF 00018000
* KG73 06010000 0605AFFF 0004b000
* comsvcs 06060000 061CEFFF 0016f000
* TxfAux 061D0000 06233FFF 00064000
* NLNT 06400000 06419FFF 0001a000
* WINMM 06420000 0644FFFF 00030000
* nttnt 06540000 0654BFFF 0000c000
* msdasql 06730000 06779FFF 0004a000
* MSDASQLR 06780000 06783FFF 00004000
* vfpodbc 06790000 06883FFF 000f4000
* odbccp32 076D0000 076E9FFF 0001a000
* cwbodbc 07C70000 07CE5FFF 00076000
* CWBNL 07CF0000 07D09FFF 0001a000
* cwbrw 07D10000 07D42FFF 00033000
* CwbAb1 07D50000 07D56FFF 00007000
* CWBSV 07D60000 07D7EFFF 0001f000
* CwbCf 07D80000 07D9DFFF 0001e000
* CwbAdNrt 07DA0000 07DB3FFF 00014000
* CwbBb1 07DC0000 07DC8FFF 00009000
* CWBCFTFT 07DD0000 07DD7FFF 00008000
* CWBNL1 07DE0000 07DECFFF 0000d000
* CwbAb 07DF0000 07DFEFFF 0000f000
* CWBCF1 07E00000 07E06FFF 00007000
* CWBNLTRN 07E10000 07E16FFF 00007000
* CwbCo 07E20000 07E57FFF 00038000
* CwbAd 07E60000 07E6CFFF 0000d000
* CwbMsgbx 07E70000 07E7AFFF 0000b000
* CWBNLDLG 07E80000 07E86FFF 00007000
* cwbuireg 07E90000 07E97FFF 00008000
* CwbAd1 07EA0000 07EA7FFF 00008000
* CWBUNPLA 07EB0000 07EE1FFF 00032000
* cwbsof 07EF0000 07F25FFF 00036000
* CwbSy 07F30000 07F39FFF 0000a000
* CWBRC 07F40000 07F51FFF 00012000
* CwbBb 07F60000 07F68FFF 00009000
* CWBUNSSL 07F70000 07F7FFFF 00010000
* MFC42 07F80000 0807AFFF 000fb000
* cwbstp 08080000 08096FFF 00017000
* CWBMSGB 084C0000 084C3FFF 00004000
* cwbcomsg 084D0000 084D4FFF 00005000
* CWBSOMRI 084E0000 084EDFFF 0000e000
* CWBODDLG 084F0000 084F6FFF 00007000
* CWBODMSG 08500000 08505FFF 00006000
* SQLTNSNT 08750000 0877FFFF 00030000
* NSNT 08780000 0879FFFF 00020000
* nasnsnt 087A0000 087C3FFF 00024000
* ntnt 087D0000 087D9FFF 0000a000
* NCRNT 087E0000 08828FFF 00049000
* msadce 08840000 0888CFFF 0004d000
* msadcer 08890000 08894FFF 00005000
* odsole70 09110000 09120FFF 00011000
* MQOA 09270000 092A9FFF 0003a000
* MQRT 092B0000 092CDFFF 0001e000
* MQSec 092D0000 092E3FFF 00014000
* MQUtil 092F0000 0930EFFF 0001f000
* MQDSCli 09310000 09326FFF 00017000
* SQLSRV32 095C0000 09626FFF 00067000
* sqlsrv32 09630000 09645FFF 00016000
* Cwbnetnt 09660000 09666FFF 00007000
* sqlvdi 0A260000 0A26CFFF 0000d000
* adsldp 04720000 04741FFF 00022000
* adsmsext 09EC0000 09ED1FFF 00012000
* sqlmap70 05000000 0502CFFF 0002d000
* MAPI32 05E40000 05E60FFF 00021000
* MSMAPI32 0A320000 0A3E7FFF 000c8000
* GAPI32 0A3F0000 0A407FFF 00018000
* dbghelp 0B540000 0B63FFFF 00100000
*
* Edi: 00000000:
* Esi: 0536AB20: 05DF2B28 00000007 00000001 00001000 05381978
0B1DB
F08
* Eax: 000001F0:
* Ebx: 77E1A7BD: 53EC8B55 A1645756 00000018 F08BC933 0F0C4D39
00008
B85
* Ecx: 00003931:
* Edx: 0B503032: 00000000 00000000 00000000 00000000 00000000
00000
000
* Eip: 05EB8187:
* Ebp: 0B50F98C: 0B50F9B8 05EAFCE3 00000002 00000000 0536AB20
77E1A
7BD
* SegCs: 0000001B:
* EFlags: 00010206: 0057005C 004E0049 0054004E 0073005C 00730079
00650
074
* Esp: 0B50F97C: 00000000 0536AB20 77E1A7BD 000001F0 0B50F9B8
05EAF
CE3
* SegSs: 00000023:
*
****************************************
************************************
*
**
*
----
-
--
* Short Stack Dump
* 05EB8187 Module(ORA73+00038187) (vsnupr+00021F7C)
* 05EAFCE3 Module(ORA73+0002FCE3) (vsnupr+00019AD8)
* 05EC4499 Module(ORA73+00044499) (vsnupr+0002E28E)
* 05EAFB95 Module(ORA73+0002FB95) (vsnupr+0001998A)
* 05EA1959 Module(ORA73+00021959) (vsnupr+0000B74E)
* 05E920A9 Module(ORA73+000120A9) (upiosd+00000063)
* 05E8BA77 Module(ORA73+0000BA77) (oparse+0000007C)
* 10001D88 Module(ociw32+00001D88) (oparse+00000028)
* 05E0EC77 Module(msdaora+0001EC77) (DllRegisterServer+000017B5)
* 780085BC Module(msvcrt+000085BC) (endthreadex+000000BC)
* 7C57B388 Module(KERNEL32+0000B388) (lstrcmpiW+000000B7)
----
--
* Location : 05EB8187 Module(ORA73+00038187) (vsnupr+00021F7C)
* Return Addr: 05EAFCE3 Module(ORA73+0002FCE3) (vsnupr+00019AD8)
Frame : 0B50F98C
Parameters:
[1] 00000002:
[2] 00000000:
[3] 0536AB20: 05DF2B28 00000007 00000001 00001000 05381978 0B1DBF08
[4] 77E1A7BD: 53EC8B55 A1645756 00000018 F08BC933 0F0C4D39 00008B85
160 bytes of stack data from 0B50F8EC to 0B50F98C
0B50F8EC: 000B0CE8 0A9E7C98 00000104 08A60048 [....|.....H...]
0B50F8FC: 05490178 08A60048 05490178 08A60040 [x.I.H...x.I.@....]
0B50F90C: 000001B7 00000001 00000000 00000000 [............]
0B50F91C: 00000000 00000000 00000000 00000000 [............]
:::: 2 Duplicate lines detected
0B50F94C: 00000000 0A9E7854 0000001C 77F82A8C [...Tx......*.w]
0B50F95C: 0A9E7860 00000005 77F82A8C 000B0000 [`x......*.w...]
0B50F96C: 000B0778 00000005 001761A8 0A9E7838 [x......a..8x..]
0B50F97C: 00000000 0536AB20 77E1A7BD 000001F0 [... .6...w...]Hi Stephanie,
Is the machine you are running on a multiprocessor machine? What sort
of storage system are you using (e.g. RAID, SAN)?
If you have a contract with MS Support (I'm under the impression you
need a contract now to get phone or email support for SQL Server 2000,
could be wrong though) you might consider forwarding them the SQL Dump
and also your mini-dump file (you should've got 2 files out of the
exception, a .txt file and another file which would be your mini-dump
file).
Sorry I can't be of more help.

Friday, March 9, 2012

Faster Deletion from a production database

I need to delete several million transactions from a production database. I
have tried the SP below but it takes 4 1/2 hours to delete 5,000
transactions. Can someone please look over this and let me know if there is
someway to speed up this process? I can't truncate the table because I stil
l
need the most recent (6 months) worth of transactions. The remaining
transactions date back to 10/21/2003 and have been backed up and now need to
be deleted.
Thanks in advance,
Matt
CREATE PROC dbo.ArchiveProduction
(
@.CutOffDate DATETIME = NULL
)
AS
BEGIN
SET NOCOUNT ON
IF @.CutOffDate IS NULL
BEGIN
SET @.CutOffDate = DATEADD(mm, -6, CURRENT_TIMESTAMP)
END
ELSE
BEGIN
IF @.CutOffDate > DATEADD(mm, -3, CURRENT_TIMESTAMP)
BEGIN
RAISERROR ('Cannot delete transactions from last three months', 16, 1)
RETURN -1
END
END
BEGIN TRAN
-- Fill a temp table with the transaction id's to delete
DECLARE @.tmp table(TranID int, BatchID int, OutboundFileID int,
FileContentsID int, FileReceiveID int)
INSERT INTO @.tmp
SELECT t.[ID] as TranID, b.[ID] as BatchID, t.OutboundFileID, f.[ID] AS
FileContentsID,
r.[ID] AS FileReceiveID
FROM FileReceive r LEFT OUTER JOIN FileContents f ON r.[ID] =
f.FileReceiveID
LEFT OUTER JOIN Batches b ON f.[ID] = b.FileContentsID
LEFT OUTER JOIN Transactions t ON b.[ID] = t.BatchID
WHERE r.FileFindDate < @.CutOffDate
-- Acknowledgments
DELETE FROM dbo.Acknowledgments
WHERE TransactionID IN
(
Select TranID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.Acknowledgments',
16, 1)
RETURN -1
END
-- OutboundTranSet
DELETE FROM dbo.OutboundTranSet
FROM dbo.OutboundTranSet o INNER JOIN dbo.OutboundBatch b
ON o.OutboundBatchID = b.[ID] INNER JOIN OutboundFile f
ON f.[ID] = b.FileID INNER JOIN dbo.Transactions t
ON f.[ID] = t.OutboundFileID
WHERE t.OutboundFileID IN
(
SELECT OutboundFileID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.OutboundTranSet',
16, 1)
RETURN -1
END
-- OutboundBatch
DELETE FROM dbo.OutboundBatch
FROM dbo.OutboundBatch o INNER JOIN dbo.OutboundFile f
ON o.FileID = f.[ID] INNER JOIN dbo.Transactions t
ON f.[ID] = t.OutboundFileID
WHERE t.OutboundFileID IN
(
SELECT OutboundFileID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.OutboundBatch',
16, 1)
RETURN -1
END
-- OutboundFile
DELETE FROM dbo.OutboundFile
WHERE [ID] IN
(
SELECT OutboundFileID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.OutboundFile', 16,
1)
RETURN -1
END
-- PayorResponse
DELETE FROM dbo.PayorResponse
WHERE TransactionID IN
(
SELECT TranID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.PayorResponse',
16, 1)
RETURN -1
END
-- ClaimInfo
DELETE FROM dbo.ClaimInfo
WHERE TransactionID IN
(
SELECT TranID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.ClaimInfo', 16, 1)
RETURN -1
END
-- Transactions
-- AttachmentImages
DELETE FROM dbo.AttachmentImages
FROM dbo.AttachmentImages a INNER JOIN dbo.AttachmentsToClaims c
ON a.A2CID = c.[ID]
WHERE c.TranID IN
(
SELECT TranID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.AttachmentImages',
16, 1)
RETURN -1
END
-- AttachmentsToClaims
DELETE FROM dbo.AttachmentsToClaims
WHERE TranID IN
(
SELECT TranID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from
dbo.AttachmentsToClaims', 16, 1)
RETURN -1
END
-- ClaimsToAttachments
DELETE FROM dbo.ClaimsToAttachments
WHERE TranID IN
(
SELECT TranID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from
dbo.ClaimsToAttachments', 16, 1)
RETURN -1
END
-- Transactions
DELETE FROM dbo.Transactions
WHERE [ID] IN
(
SELECT TranID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.Transactions', 16,
1)
RETURN -1
END
-- Batches
-- Batches
DELETE FROM dbo.Batches
WHERE [ID] IN
(
SELECT BatchID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.Batches', 16, 1)
RETURN -1
END
-- FileContents
DELETE FROM dbo.FileContents
WHERE [ID] IN
(
SELECT FileContentsID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.FileContents', 16,
1)
RETURN -1
END
-- FileReceive
DELETE FROM dbo.FileReceive
WHERE [ID] IN
(
SELECT FileReceiveID FROM @.tmp
)
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.FileReceive', 16, 1)
RETURN -1
END
IF @.@.TRANCOUNT > 0
BEGIN
COMMIT TRAN
RETURN 0
END
END
GO1) Don't use table variables for large amounts of data (more than say 500
rows), use temporary tables.
2) Use joins or EXISTS, not IN.
3) Create indexes on all columns that you join on, including those in the
temporary tables.
4) By the looks of it you can cut out quite a few joins, specially between
the OutboundFile and Transactions tables.
Jacco Schalkwijk
SQL Server MVP
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:E40D3532-FD70-43BE-A437-0B2168A71C75@.microsoft.com...
>I need to delete several million transactions from a production database.
>I
> have tried the SP below but it takes 4 1/2 hours to delete 5,000
> transactions. Can someone please look over this and let me know if there
> is
> someway to speed up this process? I can't truncate the table because I
> still
> need the most recent (6 months) worth of transactions. The remaining
> transactions date back to 10/21/2003 and have been backed up and now need
> to
> be deleted.
> Thanks in advance,
> Matt
>
> CREATE PROC dbo.ArchiveProduction
> (
> @.CutOffDate DATETIME = NULL
> )
> AS
> BEGIN
> SET NOCOUNT ON
> IF @.CutOffDate IS NULL
> BEGIN
> SET @.CutOffDate = DATEADD(mm, -6, CURRENT_TIMESTAMP)
> END
> ELSE
> BEGIN
> IF @.CutOffDate > DATEADD(mm, -3, CURRENT_TIMESTAMP)
> BEGIN
> RAISERROR ('Cannot delete transactions from last three months', 16, 1)
> RETURN -1
> END
> END
> BEGIN TRAN
> -- Fill a temp table with the transaction id's to delete
> DECLARE @.tmp table(TranID int, BatchID int, OutboundFileID int,
> FileContentsID int, FileReceiveID int)
> INSERT INTO @.tmp
> SELECT t.[ID] as TranID, b.[ID] as BatchID, t.OutboundFileID, f.[ID] AS
> FileContentsID,
> r.[ID] AS FileReceiveID
> FROM FileReceive r LEFT OUTER JOIN FileContents f ON r.[ID] =
> f.FileReceiveID
> LEFT OUTER JOIN Batches b ON f.[ID] = b.FileContentsID
> LEFT OUTER JOIN Transactions t ON b.[ID] = t.BatchID
> WHERE r.FileFindDate < @.CutOffDate
> -- Acknowledgments
> DELETE FROM dbo.Acknowledgments
> WHERE TransactionID IN
> (
> Select TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.Acknowledgments',
> 16, 1)
> RETURN -1
> END
> -- OutboundTranSet
> DELETE FROM dbo.OutboundTranSet
> FROM dbo.OutboundTranSet o INNER JOIN dbo.OutboundBatch b
> ON o.OutboundBatchID = b.[ID] INNER JOIN OutboundFile f
> ON f.[ID] = b.FileID INNER JOIN dbo.Transactions t
> ON f.[ID] = t.OutboundFileID
> WHERE t.OutboundFileID IN
> (
> SELECT OutboundFileID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.OutboundTranSet',
> 16, 1)
> RETURN -1
> END
> -- OutboundBatch
> DELETE FROM dbo.OutboundBatch
> FROM dbo.OutboundBatch o INNER JOIN dbo.OutboundFile f
> ON o.FileID = f.[ID] INNER JOIN dbo.Transactions t
> ON f.[ID] = t.OutboundFileID
> WHERE t.OutboundFileID IN
> (
> SELECT OutboundFileID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.OutboundBatch',
> 16, 1)
> RETURN -1
> END
> -- OutboundFile
> DELETE FROM dbo.OutboundFile
> WHERE [ID] IN
> (
> SELECT OutboundFileID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.OutboundFile', 16,
> 1)
> RETURN -1
> END
> -- PayorResponse
> DELETE FROM dbo.PayorResponse
> WHERE TransactionID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.PayorResponse',
> 16, 1)
> RETURN -1
> END
> -- ClaimInfo
> DELETE FROM dbo.ClaimInfo
> WHERE TransactionID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.ClaimInfo', 16, 1)
> RETURN -1
> END
> -- Transactions
> -- AttachmentImages
> DELETE FROM dbo.AttachmentImages
> FROM dbo.AttachmentImages a INNER JOIN dbo.AttachmentsToClaims c
> ON a.A2CID = c.[ID]
> WHERE c.TranID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.AttachmentImages',
> 16, 1)
> RETURN -1
> END
> -- AttachmentsToClaims
> DELETE FROM dbo.AttachmentsToClaims
> WHERE TranID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from
> dbo.AttachmentsToClaims', 16, 1)
> RETURN -1
> END
> -- ClaimsToAttachments
> DELETE FROM dbo.ClaimsToAttachments
> WHERE TranID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from
> dbo.ClaimsToAttachments', 16, 1)
> RETURN -1
> END
> -- Transactions
> DELETE FROM dbo.Transactions
> WHERE [ID] IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.Transactions', 16,
> 1)
> RETURN -1
> END
> -- Batches
> -- Batches
> DELETE FROM dbo.Batches
> WHERE [ID] IN
> (
> SELECT BatchID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.Batches', 16, 1)
> RETURN -1
> END
> -- FileContents
> DELETE FROM dbo.FileContents
> WHERE [ID] IN
> (
> SELECT FileContentsID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.FileContents', 16,
> 1)
> RETURN -1
> END
>
> -- FileReceive
> DELETE FROM dbo.FileReceive
> WHERE [ID] IN
> (
> SELECT FileReceiveID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.FileReceive', 16,
> 1)
> RETURN -1
> END
> IF @.@.TRANCOUNT > 0
> BEGIN
> COMMIT TRAN
> RETURN 0
> END
> END
> GO
>|||Thanks, Jacco! I'll give it a shot.
"Jacco Schalkwijk" wrote:

> 1) Don't use table variables for large amounts of data (more than say 500
> rows), use temporary tables.
> 2) Use joins or EXISTS, not IN.
> 3) Create indexes on all columns that you join on, including those in the
> temporary tables.
> 4) By the looks of it you can cut out quite a few joins, specially between
> the OutboundFile and Transactions tables.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Matt" <Matt@.discussions.microsoft.com> wrote in message
> news:E40D3532-FD70-43BE-A437-0B2168A71C75@.microsoft.com...
>
>|||Depending on how many records are in the tmp Table, Your problem *might* be
because there are no indices on that tmp table. WHich means that each delet
e
must do a complete table scan on that table. SO, If there are a chunk of
records in there, I might suggest using four table variables instead, as
follows:
Declare @.TranIDs Table(TId Integer Primary Key Not Null)
Declare @.BatchesIDs Table(BId Integer Primary Key Not Null)
Declare @.FileIDs Table(FId Integer Primary Key Not Null)
Declare @.ContentIDs Table(CId Integer Primary Key Not Null)
Declare @.ReceiveIDs Table(RId Integer Primary Key Not Null)
And then use these tables in all the delete queries instead... This will
allow each delete query to use Indexes on Table variables and may improve
performace substantially..
Also I suggest you put the errhandling at the end, and just "Goto" a named
label... It'll make the SP much cleaner and easier to maintain, as so:
CREATE PROC dbo.ArchiveProduction
@.CutOffDate DATETIME = NULL
AS
BEGIN
SET NOCOUNT ON
Declare @.Msg VarChar(300)
Declare @.Err Integer
Set @.CutOffDate = IsNUll(@.CutOffDate, DATEADD(mm, -6, CURRENT_TIMESTAMP))
Set @.Msg = 'Cannot delete transactions from last three months'
IF @.CutOffDate > DATEADD(mm, -3, CURRENT_TIMESTAMP) Goto ErrHandler
-- ----
Declare @.TranIDs Table(TId Integer Primary Key Not Null)
Declare @.BatchIDs Table(BId Integer Primary Key Not Null)
Declare @.FileIDs Table(FId Integer Primary Key Not Null)
Declare @.ContentIDs Table(CId Integer Primary Key Not Null)
Declare @.ReceiveIDs Table(RId Integer Primary Key Not Null)
BEGIN TRAN
-- Fill a temp table with the transaction id's to delete
INSERT @.ReceiveIDs(RId)
Select Distinct ID FROM FileReceive r
WHERE FileFindDate < @.CutOffDate
-- --
Insert @.ContentIDs(CId)
Select Distinct C.ID From FileContents C
Join @.ReceiveIDs R On R.RID = C.FileReceiveID
-- --
Insert @.BatchIDs(BId)
Select Distinct B.ID From Batches B
Join @.ContentIDs C On C.CId = B.FileContentsID
Insert @.TranIDs (TId)
Select Distinct ID From Transactions
Join @.BatchIDs B On B.Bid = T.ID
/*SELECT t.[ID] as TranID,
b.[ID] BatchID,
t.OutboundFileID,
f.[ID] FileContentsID,
r.[ID] FileReceiveID
FROM FileReceive r
LEFT JOIN FileContents f ON f.FileReceiveID = r.[ID]
LEFT JOIN Batches b ON b.FileContentsID = f.[ID]
LEFT JOIN Transactions t ON t.BatchID = b.[ID]
WHERE r.FileFindDate < @.CutOffDate */
Begin Transaction
-- Acknowledgments
'Error occured while deleting data from dbo.Acknowledgments'
DELETE dbo.Acknowledgments
WHERE TransactionID IN (Select TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- OutboundTranSet
Set @.Msg = 'Error occured while deleting data from dbo.OutboundTranSet'
DELETE dbo.OutboundTranSet
FROM dbo.OutboundTranSet o
JOIN dbo.OutboundBatch b ON o.OutboundBatchID = b.[ID]
JOIN OutboundFile f ON f.[ID] = b.FileID
JOIN dbo.Transactions t ON f.[ID] = t.OutboundFileID
WHERE t.ID IN (Select TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- OutboundBatch
Set @.Msg = 'Error occured while deleting data from dbo.OutboundBatch'
DELETE dbo.OutboundBatch
FROM dbo.OutboundBatch o
JOIN dbo.OutboundFile f ON o.FileID = f.[ID]
JOIN dbo.Transactions t ON f.[ID] = t.OutboundFileID
WHERE t.ID IN (Select TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- OutboundFile
Set @.Msg = 'Error occured while deleting data from dbo.OutboundFile'
DELETE dbo.OutboundFile
From OutboundFile F Join Transactions T
On T.OutboundFileID = F.ID
WHERE T.ID IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- PayorResponse
Set @.Msg = 'Error occured while deleting data from dbo.PayorResponse'
DELETE dbo.PayorResponse
WHERE TransactionID IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- ClaimInfo
Set @.Msg = 'Error occured while deleting data from dbo.ClaimInfo'
DELETE dbo.ClaimInfo
WHERE TransactionID IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- Transactions
-- AttachmentImages
Set @.Msg = 'Error occured while deleting data from dbo.AttachmentImages'
DELETE dbo.AttachmentImages
FROM dbo.AttachmentImages a
JOIN dbo.AttachmentsToClaims c
ON a.A2CID = c.[ID]
WHERE c.TranID IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- AttachmentsToClaims
Set @.Msg = 'Error occured while deleting data from
dbo.AttachmentsToClaims'
DELETE FROM dbo.AttachmentsToClaims
WHERE TranID IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- ClaimsToAttachments
Set @.Msg = 'Error occured while deleting data from
dbo.ClaimsToAttachments'
DELETE FROM dbo.ClaimsToAttachments
WHERE TranID IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- Transactions
Set @.Msg = 'Error occured while deleting data from dbo.Transactions'
DELETE dbo.Transactions
WHERE [ID] IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- Batches
-- Batches
Set @.Msg = 'Error occured while deleting data from dbo.Batches'
DELETE dbo.Batches
WHERE [ID] IN (SELECT BatchID FROM @.BatchIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- FileContents
Set @.Msg = 'Error occured while deleting data from dbo.FileContents'
DELETE dbo.FileContents
WHERE [ID] IN (SELECT FID FROM @.ContentIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- FileReceive
Set @.Msg = 'Error occured while deleting data from dbo.FileReceive'
DELETE dbo.FileReceive
WHERE [ID] IN (SELECT RID FROM @.ReceiveIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
COMMIT TRAN
RETURN (0) -- This is the end of normal processing
-- ***********************************
ErrHandler:
If @.@.TranCount > 0 RollBack Transaction
Raiserror(@.Msg, 16, 1)
If @.Err = 0 Set @.Err = -1
Return(@.Err)|||oh oh, some extra stuff in there I neglected to delete.. Here's correctted
version...
CREATE PROC dbo.ArchiveProduction
@.CutOffDate DATETIME = NULL
AS
BEGIN
SET NOCOUNT ON
Declare @.Msg VarChar(300)
Declare @.Err Integer
Set @.CutOffDate = IsNUll(@.CutOffDate, DATEADD(mm, -6, CURRENT_TIMESTAMP))
Set @.Msg = 'Cannot delete transactions from last three months'
IF @.CutOffDate > DATEADD(mm, -3, CURRENT_TIMESTAMP) Goto ErrHandler
-- ----
Declare @.TranIDs Table(TId Integer Primary Key Not Null)
Declare @.BatchIDs Table(BId Integer Primary Key Not Null)
Declare @.FileIDs Table(FId Integer Primary Key Not Null)
Declare @.ContentIDs Table(CId Integer Primary Key Not Null)
Declare @.ReceiveIDs Table(RId Integer Primary Key Not Null)
INSERT @.ReceiveIDs(RId)
Select Distinct ID FROM FileReceive r
WHERE FileFindDate < @.CutOffDate
-- --
Insert @.ContentIDs(CId)
Select Distinct C.ID From FileContents C
Join @.ReceiveIDs R On R.RID = C.FileReceiveID
-- --
Insert @.BatchIDs(BId)
Select Distinct B.ID From Batches B
Join @.ContentIDs C On C.CId = B.FileContentsID
Insert @.TranIDs (TId)
Select Distinct ID From Transactions
Join @.BatchIDs B On B.Bid = T.ID
Begin Transaction
-- Acknowledgments
'Error occured while deleting data from dbo.Acknowledgments'
DELETE dbo.Acknowledgments
WHERE TransactionID IN (Select TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- OutboundTranSet
Set @.Msg = 'Error occured while deleting data from dbo.OutboundTranSet'
DELETE dbo.OutboundTranSet
FROM dbo.OutboundTranSet o
JOIN dbo.OutboundBatch b ON o.OutboundBatchID = b.[ID]
JOIN OutboundFile f ON f.[ID] = b.FileID
JOIN dbo.Transactions t ON f.[ID] = t.OutboundFileID
WHERE t.ID IN (Select TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- OutboundBatch
Set @.Msg = 'Error occured while deleting data from dbo.OutboundBatch'
DELETE dbo.OutboundBatch
FROM dbo.OutboundBatch o
JOIN dbo.OutboundFile f ON o.FileID = f.[ID]
JOIN dbo.Transactions t ON f.[ID] = t.OutboundFileID
WHERE t.ID IN (Select TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- OutboundFile
Set @.Msg = 'Error occured while deleting data from dbo.OutboundFile'
DELETE dbo.OutboundFile
From OutboundFile F Join Transactions T
On T.OutboundFileID = F.ID
WHERE T.ID IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- PayorResponse
Set @.Msg = 'Error occured while deleting data from dbo.PayorResponse'
DELETE dbo.PayorResponse
WHERE TransactionID IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- ClaimInfo
Set @.Msg = 'Error occured while deleting data from dbo.ClaimInfo'
DELETE dbo.ClaimInfo
WHERE TransactionID IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- Transactions
-- AttachmentImages
Set @.Msg = 'Error occured while deleting data from dbo.AttachmentImages'
DELETE dbo.AttachmentImages
FROM dbo.AttachmentImages a
JOIN dbo.AttachmentsToClaims c
ON a.A2CID = c.[ID]
WHERE c.TranID IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- AttachmentsToClaims
Set @.Msg = 'Error occured while deleting data from
dbo.AttachmentsToClaims'
DELETE FROM dbo.AttachmentsToClaims
WHERE TranID IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- ClaimsToAttachments
Set @.Msg = 'Error occured while deleting data from
dbo.ClaimsToAttachments'
DELETE FROM dbo.ClaimsToAttachments
WHERE TranID IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- Transactions
Set @.Msg = 'Error occured while deleting data from dbo.Transactions'
DELETE dbo.Transactions
WHERE [ID] IN (SELECT TID FROM @.TranIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- Batches
-- Batches
Set @.Msg = 'Error occured while deleting data from dbo.Batches'
DELETE dbo.Batches
WHERE [ID] IN (SELECT BatchID FROM @.BatchIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- FileContents
Set @.Msg = 'Error occured while deleting data from dbo.FileContents'
DELETE dbo.FileContents
WHERE [ID] IN (SELECT FID FROM @.ContentIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
-- FileReceive
Set @.Msg = 'Error occured while deleting data from dbo.FileReceive'
DELETE dbo.FileReceive
WHERE [ID] IN (SELECT RID FROM @.ReceiveIDs)
Set @.Err = @.@.Error If @.Err <> 0 Goto ErrHandler
-- ---
COMMIT TRAN
RETURN (0)
-- ***********************************
ErrHandler:
If @.@.TranCount > 0 RollBack Transaction
Raiserror(@.Msg, 16, 1)
If @.Err = 0 Set @.Err = -1
Return(@.Err)|||What is the recovery model on your database? Try setting to simple recovery.
Also, create a loop where you delete only 100,000 records in each iteration
followed by a checkpoint. You will also want to perform a backup just prior
to this.
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:E40D3532-FD70-43BE-A437-0B2168A71C75@.microsoft.com...
> I need to delete several million transactions from a production database.
I
> have tried the SP below but it takes 4 1/2 hours to delete 5,000
> transactions. Can someone please look over this and let me know if there
is
> someway to speed up this process? I can't truncate the table because I
still
> need the most recent (6 months) worth of transactions. The remaining
> transactions date back to 10/21/2003 and have been backed up and now need
to
> be deleted.
> Thanks in advance,
> Matt
>
> CREATE PROC dbo.ArchiveProduction
> (
> @.CutOffDate DATETIME = NULL
> )
> AS
> BEGIN
> SET NOCOUNT ON
> IF @.CutOffDate IS NULL
> BEGIN
> SET @.CutOffDate = DATEADD(mm, -6, CURRENT_TIMESTAMP)
> END
> ELSE
> BEGIN
> IF @.CutOffDate > DATEADD(mm, -3, CURRENT_TIMESTAMP)
> BEGIN
> RAISERROR ('Cannot delete transactions from last three months', 16, 1)
> RETURN -1
> END
> END
> BEGIN TRAN
> -- Fill a temp table with the transaction id's to delete
> DECLARE @.tmp table(TranID int, BatchID int, OutboundFileID int,
> FileContentsID int, FileReceiveID int)
> INSERT INTO @.tmp
> SELECT t.[ID] as TranID, b.[ID] as BatchID, t.OutboundFileID, f.[ID] AS
> FileContentsID,
> r.[ID] AS FileReceiveID
> FROM FileReceive r LEFT OUTER JOIN FileContents f ON r.[ID] =
> f.FileReceiveID
> LEFT OUTER JOIN Batches b ON f.[ID] = b.FileContentsID
> LEFT OUTER JOIN Transactions t ON b.[ID] = t.BatchID
> WHERE r.FileFindDate < @.CutOffDate
> -- Acknowledgments
> DELETE FROM dbo.Acknowledgments
> WHERE TransactionID IN
> (
> Select TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.Acknowledgments',
> 16, 1)
> RETURN -1
> END
> -- OutboundTranSet
> DELETE FROM dbo.OutboundTranSet
> FROM dbo.OutboundTranSet o INNER JOIN dbo.OutboundBatch b
> ON o.OutboundBatchID = b.[ID] INNER JOIN OutboundFile f
> ON f.[ID] = b.FileID INNER JOIN dbo.Transactions t
> ON f.[ID] = t.OutboundFileID
> WHERE t.OutboundFileID IN
> (
> SELECT OutboundFileID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.OutboundTranSet',
> 16, 1)
> RETURN -1
> END
> -- OutboundBatch
> DELETE FROM dbo.OutboundBatch
> FROM dbo.OutboundBatch o INNER JOIN dbo.OutboundFile f
> ON o.FileID = f.[ID] INNER JOIN dbo.Transactions t
> ON f.[ID] = t.OutboundFileID
> WHERE t.OutboundFileID IN
> (
> SELECT OutboundFileID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.OutboundBatch',
> 16, 1)
> RETURN -1
> END
> -- OutboundFile
> DELETE FROM dbo.OutboundFile
> WHERE [ID] IN
> (
> SELECT OutboundFileID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.OutboundFile', 16,
> 1)
> RETURN -1
> END
> -- PayorResponse
> DELETE FROM dbo.PayorResponse
> WHERE TransactionID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.PayorResponse',
> 16, 1)
> RETURN -1
> END
> -- ClaimInfo
> DELETE FROM dbo.ClaimInfo
> WHERE TransactionID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.ClaimInfo', 16, 1)
> RETURN -1
> END
> -- Transactions
> -- AttachmentImages
> DELETE FROM dbo.AttachmentImages
> FROM dbo.AttachmentImages a INNER JOIN dbo.AttachmentsToClaims c
> ON a.A2CID = c.[ID]
> WHERE c.TranID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.AttachmentImages',
> 16, 1)
> RETURN -1
> END
> -- AttachmentsToClaims
> DELETE FROM dbo.AttachmentsToClaims
> WHERE TranID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from
> dbo.AttachmentsToClaims', 16, 1)
> RETURN -1
> END
> -- ClaimsToAttachments
> DELETE FROM dbo.ClaimsToAttachments
> WHERE TranID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from
> dbo.ClaimsToAttachments', 16, 1)
> RETURN -1
> END
> -- Transactions
> DELETE FROM dbo.Transactions
> WHERE [ID] IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.Transactions', 16,
> 1)
> RETURN -1
> END
> -- Batches
> -- Batches
> DELETE FROM dbo.Batches
> WHERE [ID] IN
> (
> SELECT BatchID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.Batches', 16, 1)
> RETURN -1
> END
> -- FileContents
> DELETE FROM dbo.FileContents
> WHERE [ID] IN
> (
> SELECT FileContentsID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.FileContents', 16,
> 1)
> RETURN -1
> END
>
> -- FileReceive
> DELETE FROM dbo.FileReceive
> WHERE [ID] IN
> (
> SELECT FileReceiveID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.FileReceive', 16,
1)
> RETURN -1
> END
> IF @.@.TRANCOUNT > 0
> BEGIN
> COMMIT TRAN
> RETURN 0
> END
> END
> GO
>|||Thanks to all of you for your help. With your input the delete now only
takes 2 1/2 minutes to delete 5,000 transactions and all associated items.
"Matt" wrote:

> I need to delete several million transactions from a production database.
I
> have tried the SP below but it takes 4 1/2 hours to delete 5,000
> transactions. Can someone please look over this and let me know if there
is
> someway to speed up this process? I can't truncate the table because I st
ill
> need the most recent (6 months) worth of transactions. The remaining
> transactions date back to 10/21/2003 and have been backed up and now need
to
> be deleted.
> Thanks in advance,
> Matt
>
> CREATE PROC dbo.ArchiveProduction
> (
> @.CutOffDate DATETIME = NULL
> )
> AS
> BEGIN
> SET NOCOUNT ON
> IF @.CutOffDate IS NULL
> BEGIN
> SET @.CutOffDate = DATEADD(mm, -6, CURRENT_TIMESTAMP)
> END
> ELSE
> BEGIN
> IF @.CutOffDate > DATEADD(mm, -3, CURRENT_TIMESTAMP)
> BEGIN
> RAISERROR ('Cannot delete transactions from last three months', 16, 1)
> RETURN -1
> END
> END
> BEGIN TRAN
> -- Fill a temp table with the transaction id's to delete
> DECLARE @.tmp table(TranID int, BatchID int, OutboundFileID int,
> FileContentsID int, FileReceiveID int)
> INSERT INTO @.tmp
> SELECT t.[ID] as TranID, b.[ID] as BatchID, t.OutboundFileID, f.[ID] AS
> FileContentsID,
> r.[ID] AS FileReceiveID
> FROM FileReceive r LEFT OUTER JOIN FileContents f ON r.[ID] =
> f.FileReceiveID
> LEFT OUTER JOIN Batches b ON f.[ID] = b.FileContentsID
> LEFT OUTER JOIN Transactions t ON b.[ID] = t.BatchID
> WHERE r.FileFindDate < @.CutOffDate
> -- Acknowledgments
> DELETE FROM dbo.Acknowledgments
> WHERE TransactionID IN
> (
> Select TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.Acknowledgments'
,
> 16, 1)
> RETURN -1
> END
> -- OutboundTranSet
> DELETE FROM dbo.OutboundTranSet
> FROM dbo.OutboundTranSet o INNER JOIN dbo.OutboundBatch b
> ON o.OutboundBatchID = b.[ID] INNER JOIN OutboundFile f
> ON f.[ID] = b.FileID INNER JOIN dbo.Transactions t
> ON f.[ID] = t.OutboundFileID
> WHERE t.OutboundFileID IN
> (
> SELECT OutboundFileID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.OutboundTranSet'
,
> 16, 1)
> RETURN -1
> END
> -- OutboundBatch
> DELETE FROM dbo.OutboundBatch
> FROM dbo.OutboundBatch o INNER JOIN dbo.OutboundFile f
> ON o.FileID = f.[ID] INNER JOIN dbo.Transactions t
> ON f.[ID] = t.OutboundFileID
> WHERE t.OutboundFileID IN
> (
> SELECT OutboundFileID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.OutboundBatch',
> 16, 1)
> RETURN -1
> END
> -- OutboundFile
> DELETE FROM dbo.OutboundFile
> WHERE [ID] IN
> (
> SELECT OutboundFileID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.OutboundFile', 1
6,
> 1)
> RETURN -1
> END
> -- PayorResponse
> DELETE FROM dbo.PayorResponse
> WHERE TransactionID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.PayorResponse',
> 16, 1)
> RETURN -1
> END
> -- ClaimInfo
> DELETE FROM dbo.ClaimInfo
> WHERE TransactionID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.ClaimInfo', 16,
1)
> RETURN -1
> END
> -- Transactions
> -- AttachmentImages
> DELETE FROM dbo.AttachmentImages
> FROM dbo.AttachmentImages a INNER JOIN dbo.AttachmentsToClaims c
> ON a.A2CID = c.[ID]
> WHERE c.TranID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.AttachmentImages
',
> 16, 1)
> RETURN -1
> END
> -- AttachmentsToClaims
> DELETE FROM dbo.AttachmentsToClaims
> WHERE TranID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from
> dbo.AttachmentsToClaims', 16, 1)
> RETURN -1
> END
> -- ClaimsToAttachments
> DELETE FROM dbo.ClaimsToAttachments
> WHERE TranID IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from
> dbo.ClaimsToAttachments', 16, 1)
> RETURN -1
> END
> -- Transactions
> DELETE FROM dbo.Transactions
> WHERE [ID] IN
> (
> SELECT TranID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.Transactions', 1
6,
> 1)
> RETURN -1
> END
> -- Batches
> -- Batches
> DELETE FROM dbo.Batches
> WHERE [ID] IN
> (
> SELECT BatchID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.Batches', 16, 1)
> RETURN -1
> END
> -- FileContents
> DELETE FROM dbo.FileContents
> WHERE [ID] IN
> (
> SELECT FileContentsID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.FileContents', 1
6,
> 1)
> RETURN -1
> END
>
> -- FileReceive
> DELETE FROM dbo.FileReceive
> WHERE [ID] IN
> (
> SELECT FileReceiveID FROM @.tmp
> )
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RAISERROR ('Error occured while deleting data from dbo.FileReceive', 16
, 1)
> RETURN -1
> END
> IF @.@.TRANCOUNT > 0
> BEGIN
> COMMIT TRAN
> RETURN 0
> END
> END
> GO
>

Wednesday, March 7, 2012

Fast Way To "Insert Into" a million records?

>
> INSERT INTO MaintHist (DebtorID, AssignCollector,
> ChangeCollector, DateChanged, TableName, FieldName,
> OldValue, NewValue)
> SELECT TempUpdate.RecordUniqueValue, CAST
> (TempUpdate.CurrentCollectorID AS VarChar(4)), 'BATC',
> GetDate(), TempUpdate.TableName, TempUpdate.FieldName,
> TempUpdate.OldValue, TempUpdate.NewValue FROM TempUpdate
> WHERE TempUpdate.BatchID = 505
> Thanks!
> Jason|||How would a BULK COPY be done?
Jason Roozee

>--Original Message--
>you can try to Bulk copy it in as this is non-logged.
However that may NOT
>be the right solution for you.
>it's also fairly common to drop indexes before you do
huge inserts and then
>rebuild them when inserts are complete.
>
>just food for thought
>
>Greg Jackson
>PDX, Oregon
>
>.
>|||To add some to Geoff's comments. You might try inserting them in smaller
batches if at all possible. When inserting into an existing table it is
usually faster to do insert them in batches of say 10,000 rows vs all 3
million in one transaction. If you have a clustered index on the table
being inserted into you should try to insert them in that order as well.
Andrew J. Kelly
SQL Server MVP
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:O7ZfMlLSEHA.3056@.TK2MSFTNGP11.phx.gbl...
> Comments Inline
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Jason Roozee" <jason@.camcoinc.net> wrote in message
> news:16e3c01c448b6$d7c55ca0$a601280a@.phx
.gbl...
> Note that your example indicates an insert speed of 5000 records/minute.
> That means 200 minutes for 1M rows. 200 minutes = 3.33 hours, so you have
> better than linear scaling. This is good.
>
> Yes. Expand the database before adding the records. This will improve
> performance some.
> SQL handles this automagically. I doubt you could improve performance
with
> a locking hint.
> You could try a DTS package to do the transfer, but I am not sure if that
> will help.
> There is always faster hardware.

>|||look up BCP in Books On Line for all the details.
Commonly used utility for blasting lots of data into sql server.
GAJ|||Im already using Bulk Insert to get the new data in to the
server, but now I need to update data from one table to
another table.
Jsason
>--Original Message--
>look up BCP in Books On Line for all the details.
>Commonly used utility for blasting lots of data into sql
server.
>
>GAJ
>
>.
>|||to do massive updates, you'll probably want to batch the updates into groups
as others have suggested.
Cheers,
GAJ

Fast Way To "Insert Into" a million records?

>
> INSERT INTO MaintHist (DebtorID, AssignCollector,
> ChangeCollector, DateChanged, TableName, FieldName,
> OldValue, NewValue)
> SELECT TempUpdate.RecordUniqueValue, CAST
> (TempUpdate.CurrentCollectorID AS VarChar(4)), 'BATC',
> GetDate(), TempUpdate.TableName, TempUpdate.FieldName,
> TempUpdate.OldValue, TempUpdate.NewValue FROM TempUpdate
> WHERE TempUpdate.BatchID = 505
> Thanks!
> Jason
|||How would a BULK COPY be done?
Jason Roozee

>--Original Message--
>you can try to Bulk copy it in as this is non-logged.
However that may NOT
>be the right solution for you.
>it's also fairly common to drop indexes before you do
huge inserts and then
>rebuild them when inserts are complete.
>
>just food for thought
>
>Greg Jackson
>PDX, Oregon
>
>.
>
|||To add some to Geoff's comments. You might try inserting them in smaller
batches if at all possible. When inserting into an existing table it is
usually faster to do insert them in batches of say 10,000 rows vs all 3
million in one transaction. If you have a clustered index on the table
being inserted into you should try to insert them in that order as well.
Andrew J. Kelly
SQL Server MVP
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:O7ZfMlLSEHA.3056@.TK2MSFTNGP11.phx.gbl...
> Comments Inline
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Jason Roozee" <jason@.camcoinc.net> wrote in message
> news:16e3c01c448b6$d7c55ca0$a601280a@.phx.gbl...
> Note that your example indicates an insert speed of 5000 records/minute.
> That means 200 minutes for 1M rows. 200 minutes = 3.33 hours, so you have
> better than linear scaling. This is good.
> Yes. Expand the database before adding the records. This will improve
> performance some.
> SQL handles this automagically. I doubt you could improve performance
with
> a locking hint.
> You could try a DTS package to do the transfer, but I am not sure if that
> will help.
> There is always faster hardware.

>
|||look up BCP in Books On Line for all the details.
Commonly used utility for blasting lots of data into sql server.
GAJ
|||Im already using Bulk Insert to get the new data in to the
server, but now I need to update data from one table to
another table.
Jsason
>--Original Message--
>look up BCP in Books On Line for all the details.
>Commonly used utility for blasting lots of data into sql
server.
>
>GAJ
>
>.
>
|||to do massive updates, you'll probably want to batch the updates into groups
as others have suggested.
Cheers,
GAJ

Fast Way To "Insert Into" a million records?

See the SQL below, on our SQL server this takes about
10min for 50,000 records, and about 3 hours for a million
records. Is there ANYTHING I can do to speed this up?
-Can I allocate DB space ahead of time?
-Can I put a table in to some type of lock mode'
-Is there something better than insert into?
INSERT INTO MaintHist (DebtorID, AssignCollector,
ChangeCollector, DateChanged, TableName, FieldName,
OldValue, NewValue)
SELECT TempUpdate.RecordUniqueValue, CAST
(TempUpdate.CurrentCollectorID AS VarChar(4)), 'BATC',
GetDate(), TempUpdate.TableName, TempUpdate.FieldName,
TempUpdate.OldValue, TempUpdate.NewValue FROM TempUpdate
WHERE TempUpdate.BatchID = 505
Thanks!
Jasonyou can try to Bulk copy it in as this is non-logged. However that may NOT
be the right solution for you.
it's also fairly common to drop indexes before you do huge inserts and then
rebuild them when inserts are complete.
just food for thought
Greg Jackson
PDX, Oregon|||Comments Inline
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jason Roozee" <jason@.camcoinc.net> wrote in message
news:16e3c01c448b6$d7c55ca0$a601280a@.phx.gbl...
> See the SQL below, on our SQL server this takes about
> 10min for 50,000 records, and about 3 hours for a million
> records. Is there ANYTHING I can do to speed this up?
>
Note that your example indicates an insert speed of 5000 records/minute.
That means 200 minutes for 1M rows. 200 minutes = 3.33 hours, so you have
better than linear scaling. This is good.
> -Can I allocate DB space ahead of time?
Yes. Expand the database before adding the records. This will improve
performance some.
> -Can I put a table in to some type of lock mode'
SQL handles this automagically. I doubt you could improve performance with
a locking hint.
> -Is there something better than insert into?
You could try a DTS package to do the transfer, but I am not sure if that
will help.
There is always faster hardware. :)
>
> INSERT INTO MaintHist (DebtorID, AssignCollector,
> ChangeCollector, DateChanged, TableName, FieldName,
> OldValue, NewValue)
> SELECT TempUpdate.RecordUniqueValue, CAST
> (TempUpdate.CurrentCollectorID AS VarChar(4)), 'BATC',
> GetDate(), TempUpdate.TableName, TempUpdate.FieldName,
> TempUpdate.OldValue, TempUpdate.NewValue FROM TempUpdate
> WHERE TempUpdate.BatchID = 505
> Thanks!
> Jason|||How would a BULK COPY be done?
Jason Roozee
>--Original Message--
>you can try to Bulk copy it in as this is non-logged.
However that may NOT
>be the right solution for you.
>it's also fairly common to drop indexes before you do
huge inserts and then
>rebuild them when inserts are complete.
>
>just food for thought
>
>Greg Jackson
>PDX, Oregon
>
>.
>|||To add some to Geoff's comments. You might try inserting them in smaller
batches if at all possible. When inserting into an existing table it is
usually faster to do insert them in batches of say 10,000 rows vs all 3
million in one transaction. If you have a clustered index on the table
being inserted into you should try to insert them in that order as well.
--
Andrew J. Kelly
SQL Server MVP
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:O7ZfMlLSEHA.3056@.TK2MSFTNGP11.phx.gbl...
> Comments Inline
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Jason Roozee" <jason@.camcoinc.net> wrote in message
> news:16e3c01c448b6$d7c55ca0$a601280a@.phx.gbl...
> > See the SQL below, on our SQL server this takes about
> > 10min for 50,000 records, and about 3 hours for a million
> > records. Is there ANYTHING I can do to speed this up?
> >
> Note that your example indicates an insert speed of 5000 records/minute.
> That means 200 minutes for 1M rows. 200 minutes = 3.33 hours, so you have
> better than linear scaling. This is good.
> > -Can I allocate DB space ahead of time?
> Yes. Expand the database before adding the records. This will improve
> performance some.
> > -Can I put a table in to some type of lock mode'
> SQL handles this automagically. I doubt you could improve performance
with
> a locking hint.
> > -Is there something better than insert into?
> You could try a DTS package to do the transfer, but I am not sure if that
> will help.
> There is always faster hardware. :)
> >
> >
> >
> > INSERT INTO MaintHist (DebtorID, AssignCollector,
> > ChangeCollector, DateChanged, TableName, FieldName,
> > OldValue, NewValue)
> >
> > SELECT TempUpdate.RecordUniqueValue, CAST
> > (TempUpdate.CurrentCollectorID AS VarChar(4)), 'BATC',
> > GetDate(), TempUpdate.TableName, TempUpdate.FieldName,
> > TempUpdate.OldValue, TempUpdate.NewValue FROM TempUpdate
> > WHERE TempUpdate.BatchID = 505
> >
> > Thanks!
> > Jason
>|||look up BCP in Books On Line for all the details.
Commonly used utility for blasting lots of data into sql server.
GAJ|||Im already using Bulk Insert to get the new data in to the
server, but now I need to update data from one table to
another table.
Jsason
>--Original Message--
>look up BCP in Books On Line for all the details.
>Commonly used utility for blasting lots of data into sql
server.
>
>GAJ
>
>.
>|||to do massive updates, you'll probably want to batch the updates into groups
as others have suggested.
Cheers,
GAJ

Sunday, February 19, 2012

Failure Audit for SQL Server

I am getting the below Failure Audit in the SQL Server event log for 3 users.
All the users have access to a database in SQL Server with their domain
account. Below message keeps poping every second. How can I trouble shoot
this ? What is causing it ?
Thanks.
Source: Security
Type: Failure
Event ID: 560
Object Open:
Object Server:SC Manager
Object Type:SERVICE OBJECT
Object Name:MSSQLSERVER
New Handle ID:-
Operation ID:{0,1170910191}
Process ID:316
Primary User Name:DEVSERV1$
Primary Domain:CMR_PO
Primary Logon ID:(0x0,0x3E7)
Client User Name:aclint
Client Domain:CMR_PO
Client Logon ID:(0x0,0x3BAC59FE)
AccessesREAD_CONTROL
Query service configuration information
Query status of service
Enumerate dependencies of service
Query information from service
Privileges-
An event id 560 is just an open event. Check aclint's permission carefully.
Maybe ther is something he/she doesn't have access to like a particular proc
or table. Ask them if they see a message on their screen or if they are
experiencing any problems. see
http://www.microsoft.com/technet/arc...mspx?mfr=true
for more info on security event logs
"DXC" wrote:

> I am getting the below Failure Audit in the SQL Server event log for 3 users.
> All the users have access to a database in SQL Server with their domain
> account. Below message keeps poping every second. How can I trouble shoot
> this ? What is causing it ?
> Thanks.
> ----
> Source: Security
> Type: Failure
> Event ID: 560
> ----
>
> Object Open:
> Object Server:SC Manager
> Object Type:SERVICE OBJECT
> Object Name:MSSQLSERVER
> New Handle ID:-
> Operation ID:{0,1170910191}
> Process ID:316
> Primary User Name:DEVSERV1$
> Primary Domain:CMR_PO
> Primary Logon ID:(0x0,0x3E7)
> Client User Name:aclint
> Client Domain:CMR_PO
> Client Logon ID:(0x0,0x3BAC59FE)
> AccessesREAD_CONTROL
> Query service configuration information
> Query status of service
> Enumerate dependencies of service
> Query information from service
> Privileges-
>
|||My guess is that this is either EM or Service Manager which tries to see whether the service is
running.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Francis" <Francis@.discussions.microsoft.com> wrote in message
news:5844A73A-7208-480C-BDC8-8DDAADA4C86B@.microsoft.com...[vbcol=seagreen]
> An event id 560 is just an open event. Check aclint's permission carefully.
> Maybe ther is something he/she doesn't have access to like a particular proc
> or table. Ask them if they see a message on their screen or if they are
> experiencing any problems. see
> http://www.microsoft.com/technet/arc...mspx?mfr=true
> for more info on security event logs
> "DXC" wrote:
|||Thanks for the replies but I will not pursue this any longer since I can not
get any info from our developers who are having this problem in terms of what
they are trying to run/access. It seems like there is no way to find out what
they are doing from my end. I don't see anything under "Current Activity"
window either.
Windows 2000, SQL Server 2000 SE-SP4
Thanks again.
"Tibor Karaszi" wrote:

> My guess is that this is either EM or Service Manager which tries to see whether the service is
> running.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Francis" <Francis@.discussions.microsoft.com> wrote in message
> news:5844A73A-7208-480C-BDC8-8DDAADA4C86B@.microsoft.com...
>
|||Again, it is probably EM checking whether the server is running. You can check this, by asking one
of the users to stop EM, see if these events stop, and start EM again.
Also, you can uncheck the option "Display server state in console" for the server registration in EM
and see if these events disappear.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:2C1753AF-910D-4F62-A781-5870EE0C702E@.microsoft.com...[vbcol=seagreen]
> Thanks for the replies but I will not pursue this any longer since I can not
> get any info from our developers who are having this problem in terms of what
> they are trying to run/access. It seems like there is no way to find out what
> they are doing from my end. I don't see anything under "Current Activity"
> window either.
> Windows 2000, SQL Server 2000 SE-SP4
> Thanks again.
> "Tibor Karaszi" wrote:
|||In reference to SQL2000 at this site:
You may want to check if users are signed on to SQL Enterprise using
SQL logins and have 'Poll Server' checked
<Tools><Options>[Panel]General.
If this is checked, it will generate audit failures trying to
poll/acquire data using their domain account(s).
DXC wrote:
> I am getting the below Failure Audit in the SQL Server event log for 3 users.
> All the users have access to a database in SQL Server with their domain
> account. Below message keeps poping every second. How can I trouble shoot
> this ? What is causing it ?
> Thanks.
> ----
> Source: Security
> Type: Failure
> Event ID: 560
> ----
>
> Object Open:
> Object Server:SC Manager
> Object Type:SERVICE OBJECT
> Object Name:MSSQLSERVER
> New Handle ID:-
> Operation ID:{0,1170910191}
> Process ID:316
> Primary User Name:DEVSERV1$
> Primary Domain:CMR_PO
> Primary Logon ID:(0x0,0x3E7)
> Client User Name:aclint
> Client Domain:CMR_PO
> Client Logon ID:(0x0,0x3BAC59FE)
> AccessesREAD_CONTROL
> Query service configuration information
> Query status of service
> Enumerate dependencies of service
> Query information from service
> Privileges-

Tuesday, March 27, 2012

Field Alias as Function

Monday, March 26, 2012

Fetch cursor help

Wednesday, March 21, 2012

Fatal exception error in mSSQL 2000 sp4

Fatal exception error in mSSQL 2000 sp3 and sp4

Fatal exception error in mSSQL 2000 sp3 and sp4

Friday, March 9, 2012

Faster Deletion from a production database

Wednesday, March 7, 2012

Fast Way To "Insert Into" a million records?

Fast Way To "Insert Into" a million records?

Fast Way To "Insert Into" a million records?

Sunday, February 19, 2012

Failure Audit for SQL Server

Footer rows to skip

Blog Archive

About Me