SQL Query creating blockages and freezing the runn

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

General SQL Server Forums

New to SQL Server Programming

SQL Query creating blockages and freezing the runn

Author

Topic

vinayu123
Starting Member

2 Posts

Posted - 2012-12-05 : 01:52:54

HI, I have some query which run daily in Scheduled task, and there is a script which extract heavy data from another server, it takes aroung 3Hrs to extract whole data, it works fine for some days, and some days data will not load upon checking in Enterprise mangaer current jobs i can see some blockages created, the scheduled job will be in running mode but there will not be any transaction will be happeining, Please help us in resolving this issue, we are using SQL Query analyser which is 2000. Since it a business critical help us as soon as possible, Appreciate your quick responce

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-12-05 : 04:58:17

If it is being blocked you should be able to see the blocking spid and what it is doing.
Could be that someone has left a transaction open.

You can use dbcc inputbuffer to view the instruction.
This SP will do it all for you - show what is happening on the server and any blocking commands.
http://www.nigelrivett.net/SQLAdmin/sp_nrInfo.html

You should also look at doing something about the extract - 3 hours is probably going to cause problems.
I would at least do it in increments and probably via files rather than directly into a table. Copy to a file locally on the server, copy the file then import.
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.