NOLOCK in a DTS - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

NOLOCK in a DTS

Author

Topic

Ian Cockcroft
Starting Member

8 Posts

Posted - 2004-03-23 : 08:00:57

Hi, I am running a DTS which keeps getting locked out. I run all DTSs' at night between 10pm and 4am. The server is very quiet then and no changes are made to the data. Will putting a NOLOCK on my data pump make it more reliable? Or is there a better option? Can I put a NOLOCK option on all my DTS's?

my sql script::

SELECT
A.CALLNBR,
A.SRVSTAT,
A.[NAME],
A.CUSTNMBR,
A.OFFID,
A.ENTDTE,
A.ETADTE,
B.AV_Service_Type_Desc,
B.Short_Description,
C.probcde,
D.ITEMNMBR,
D.XTNDPRCE

FROM
SVC00200 A
INNER JOIN av00300 B ON A.CALLNBR = B.CALLNBR AND A.SRVRECTYPE = B.SRVRECTYPE
INNER JOIN SVC0201V C ON A.SRVRECTYPE = C.SRVRECTYPE AND A.CALLNBR = C.CALLNBR
LEFT OUTER JOIN SVC00203 D ON C.SRVRECTYPE = D.SRVRECTYPE AND C.CALLNBR = D.CALLNBR

WHERE

A.SRVSTAT IN ('99', '040','100', '200', '300', '400', '005')

will become:

SELECT
A.CALLNBR,
A.SRVSTAT,
A.[NAME],
A.CUSTNMBR,
A.OFFID,
A.ENTDTE,
A.ETADTE,
B.AV_Service_Type_Desc,
B.Short_Description,
C.probcde,
D.ITEMNMBR,
D.XTNDPRCE

FROM
SVC00200 A WITH (NOLOCK)
INNER JOIN av00300 B WITH (NOLOCK)ON A.CALLNBR = B.CALLNBR AND A.SRVRECTYPE = B.SRVRECTYPE
INNER JOIN SVC0201V C WITH (NOLOCK) ON A.SRVRECTYPE = C.SRVRECTYPE AND A.CALLNBR = C.CALLNBR
LEFT OUTER JOIN SVC00203 D WITH (NOLOCK)ON C.SRVRECTYPE = D.SRVRECTYPE AND C.CALLNBR = D.CALLNBR

WHERE

A.SRVSTAT IN ('99', '040','100', '200', '300', '400', '005')

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-03-23 : 08:33:45

If you're sure the data is not being changed, there really is no harm in putting nolock on. Are you doing reindexes at night? That's most likely what is causing the locks. Most people do this type of maintenance after hours in that timeframe.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources