| Author |
 Topic  |
|
|
vinayu123
Starting Member
India
2 Posts |
 Posted - 12/05/2012 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 12/05/2012 : 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. |
Edited by - nigelrivett on 12/05/2012 05:00:01 |
 |
|
|
vinayu123
Starting Member
India
2 Posts |
Posted - 12/05/2012 : 10:24:50
|
| Thanks for looking into the issue i will follow the instruction said below, and let you know the result, thanks and appreciated. |
|
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 12/05/2012 : 11:21:12
|
| What time are you running this job? |
|
|
|
Lamprey
Flowing Fount of Yak Knowledge
3833 Posts |
Posted - 12/05/2012 : 12:00:03
|
| Are you using linked servers? If so, are you joining across the linked server or are you just selecting from the linked server? |
|
|
| |
Topic |
|
SQL Query creating blockages and freezing the runn
