SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL Query creating blockages and freezing the runn
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vinayu123
Starting Member

India
2 Posts

Posted - 12/05/2012 :  01:52:54  Show Profile  Reply with Quote
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
3383 Posts

Posted - 12/05/2012 :  04:58:17  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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
Go to Top of Page

vinayu123
Starting Member

India
2 Posts

Posted - 12/05/2012 :  10:24:50  Show Profile  Reply with Quote
Thanks for looking into the issue i will follow the instruction said below, and let you know the result, thanks and appreciated.
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/05/2012 :  11:21:12  Show Profile  Reply with Quote
What time are you running this job?
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 12/05/2012 :  12:00:03  Show Profile  Reply with Quote
Are you using linked servers? If so, are you joining across the linked server or are you just selecting from the linked server?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000