Hi there!,
Problem: I schedule a job that calls a stored procedure which loads around 1.5 million records. The Job takes 19 hrs to complete. However, if i run that stored procedure manually in Query Analyser it takes only 45 minutes..
Did anyone faced this problem? Is this known problem..Any suggestions/recommendations?
thank you.
regards,
Nana
If I start a long running query running on a background thread is there a way to abort the query so that it does not continue running on SQL server?
The query would be running on SQL Server 2005 from a Windows form application using the Background worker component. So the query would have been started from the background workers DoWork event using ado.net. If the user clicks an abort button in the UI I would want the query to die so that it does not continue to use sql server resources.
Is there a way to do this?
Thanks
I am using VS2005 (VB) to develop a PPC WM5.0 Program. And I am using SQLCE 3.0. My PPC Hardware is in 400MHz.
The question is when the program try to insert the first record into sdf database after each time the program started. It takes a long time. Does anyone know why and how can I fix it?
I will load the whole database into a dataset when the program start and do all the "Insert", "Update", "Delete" in this dataset and fill it into database after each action.
cn.Open()
sda = New SqlCeDataAdapter(SQL, cn) 'SQL = Select * From Table
scb = New SqlCeCommandBuilder(sda)
sda.Update(dataset)
cn.Close()
I check the sda.update(), it takes about 0.08s for filling one record into database normally. But:
1. Start the PPC Program
2. Load DB into dataset
3. Create a ONE new record in dataset
4. Fill back to DB
When I take this four steps everytime, the filling time is almost 1s or even more!
Actually, 0.08s is just a normal case. Sometimes, it still takes over 1s to filling back a dataset which only inserted one record when the program is running. (Even all inserted records are exactly the same in data jsut different in the integer key)
However, when I give up the dataset and using the following code:
cn.Open()
Dim cmd As New SqlCeCommand(SQL, cn) ' I have build the insert SQL before (Insert Into Table values(XXXXXXXXXXXXXXX All field)
cmd.CommandType = CommandType.Text
cmd.ExecuteNonQuery()
cn.Close()
StartTime = Environment.TickCount
I found that it is still the same that the first inserted record takes more time, but just about 0.2s. And the normal insert time is around 0.02s. It is 4 times faster!!!
I created a DTS Local Package that should run on a specified time (ex. 9:00 AM) using EnterPrise Manager on my PC.
It is basically copying a file from our AS400 to the SQL server. It works fine if you execute it but the scheduler does not run at the specified time.
Am I missing anything here?
TIF
Hi, I need to write a script to run an automation in MS SQL 2005. It will run every 10 hours. What the script will do is just import a .cvs file into a database table. Can someone let me know how can I acheive this? Thanks in advanced.
View 2 Replies View RelatedHey how are you.
I'm going crazy with EM's Sheduled Tasks.
I've setup couple of backups and weekly maintenance plans from EM.
After couple of days I viewed sheduled tasks from EM toolbar
and found that all the tasks have not executed as I have scheduled.
the task history does not show any history at all.
Clicking refresh does not do any good either.
However if i right click on the database and choose restore i see all the backups
that i have scheduled.
Is this a bug?
Can someone tell me what's wrong???
THANKS IN ADVANCE
We have DTS Packages that are scheduled to run nightly and show up in EM under JOBS under SQL Server Agent. A password got changed and some of the nightly jobs blew up.
Went into the Packages and entered the new password and successfully executed the packages BUT when I go back to jobs under the SQL Server Agent, the jobs STILL will not run - they fail with a login failure - as if i never fixed the DTS package. HELP
Hi!
I've written an scheduled job for importing an excel file into a sql table.When I try to run the job from Sql server agent , I get the status 'Not running',and in the last run status,it's written 'failed'.I've tested the command before in the query analyzer,and it works.Can someone tell me why isn't this working as a scheduled job?
Thanks in advance!
I have created a DTS package to read data from a SQL Server table and copy it to an Excel file on one sheet. I have tested the DTS package manually and it runs. I scheduled the package but it keeps failing giving the following error message. The path to the Excel file is valid and the SQL server is connected to the server where the Excel file resides.
... DTSRun: Executing... DTSRun OnStart: Copy Data from vw_Symbols to vw_Symbols$ Step DTSRun OnError: Copy Data from vw_Symbols to vw_Symbols$ Step, Error = -2147467259 (80004005) Error string: 'S:Mission Critical EnterprisesCurrent ProjectsJPMC Aperture Rollout 05-100-1-0001Symbol RequestsJPMC - Symbols Added.xls' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides. Error source: Microsoft JET Database Engine Help file: Help context: 5003044 Error Detail Records: Error: -2147467259 (80004005); Provider Error: -534774783 (E01FFC01) Error string: 'S:Mission Critical EnterprisesCurrent ProjectsJPMC Aperture Rollout 05-100-1-0001Symbol RequestsJPMC - Symbols Added.xls' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides. Error sour... Process Exit Code 1. The step failed.
I've tried finding an answer to this on this website but I don't see any. I'm running SQL Enterprise Manager v8.0 and have a job in the SQL server agent folder that won't run as scheduled nor will it run manually if I Right-Click and choose Start Job.
It's designed to run a DTS. I can run the DTS manually without errors but the job won't run it for me. I will try to provide as many details in this post as I can in hopes that someone who is more experienced at this can help me.
DTS DETAILS
The DTS is designed to transfer data from a table on one server and transfer it through a firewall to another server into a table on that server. Then delete the entries from the originating table. This works very well if I run it manually. The purpose of deleting entries from the web server is for security. Once the data in on the other side of the firewall it's safe.
The job however will not run either manually or from it's schedule. I've tried creating a new job and setting up a very simple schedule that runs daily every 2 minutes for example. I've played around with different times but no luck. So I will try and provide you with job details below:
JOB DETAILS
Name: TransferData
Enabled, Target = Local Server
Category: Uncategorized Local
Description: Execute package: TransferData
STEPS
ID: 1
Step Name: TransferData
Type: Operating System Command
On Success: Quit with Success
On Failure: Quit with Failure
PROPERTIES OF STEP
Step Name: TransferData
Type: Operating System Command (CmdExec)
Command: ( I have no idea what this translates to as I did not write it) DTSRun/~Z0xF6EE76ED6259A60AC370F465DE7F3095F4B37F7176C05B8E72E396DEBE60135E53ACFEDB00E1F216DD8EC8206782439BA435771E3C1E8A7A5D0DAE3F3AAA9F48BF08A9A9FA23C010A85573546F6B6B900323F76DC8A27EACAFD63904464DD5A9B28137
Nothing special in the advanced tab
SCHEDULES TAB
ID: 20
Name: TransferData
Enabled: No
Description: Occurs every 1 day(s), every 2 minute(s) between 12:00:00 AM and 11:59:59 PM
and is setup as Recurring
Nothing in Notifications Tab
I only have two SQL books to reference. This is a production fire to put out and I really need the help here as I am not a certified SQL guru.
I hope I have provided enough information here for someone to help. I'm happy to provide an email address for easier contact should you request it.
Thanks in advance for any assistance with this.
Hi all,
I have MS Time Seeries model using a database of over a thousand products each of which has hundreds of cases. It amazingly takes only a few minutes to finish processing the model, but when I click Mining Model Viewer to view the models, it takes many hours to show up. Once the window is open, I can choose model for different products almost instantly. Is this normal?
A few pointers would be appreciated.
I am looking at building multiple SSIS packages. There will be some similarities. Flexibility is of highest importance. The main packages will need to connect to SQL Server1 as a source and SQL Server2 as a destination to transfer over dimenion data from multiple databases. (other SSIS packages may need to use SQL Server2 as a source and SQL Server1 as a destination)
For a single dimension table containing column dim_id on the target server (SQLServer2). I need to pass the results of the following SQL and insert into SQLServer2.database.dim_table
select dim.id
from SQLServer1.database08.dim_table
union
select dim.id
from SQLServer1.database07.dim_table
union
select dim.id
from SQLServer1.database06.dim_table
Now next year the names of the databases on SQLServer1 will be database09,database08,database07!
Now so far my best thought is creating views in my destination SQL Server. So I need some way of dropping and recreating the views. Previously in DTS I would expect to see SQL Server connection that I could use as source and destination. Now I can see SQL Server destination but not source? Also How do I just use SSIS to run some SQL. i.e execute a stored procedure, drop and creat views?
Many thanks,
Ells
p.s Flexibility is the key, in the last three months all the ip and server names have changed more than once so need to be as flexible as possible.
I have a pretty complex query that aggregates lots of data and inserts multiple rows of that data into a reporting table. When I call this SPROC from SQL Server Management Studio, it executes in under 3 seconds. When I try to execute the same SPROC using .NET's SqlCommand object the query runs indefinitely until the CommandTimeout is reached. Why would this SPROC behave differently with the same inputs, but being called from .NET? Thanks for your help!
View 3 Replies View RelatedHi everyone.... I'm trying to execute this update statement... It takes an eternity... any ideas on how to rewrite or speed it up?
It's a several step process... below is everything that i run, one step at a time. The final update statement is what takes so long. It should only affect about 2600 rows out of a potential 9000. That's why I'm confused on the response time
select d.olddevicename, de.device, d.newdevicename into #temp9
from dns d, devices de
where de.device = d.olddevicename
update #temp9 set device = newdevicename where olddevicename = device
update devices set device = #temp9.device from #temp9, devices where #temp9.device in
(select #temp9.device from #temp9, devices where #temp9.olddevicename = devices.device)
Thank you!
J.
Hi all,
I have 3 three scheduled job: one runs onece a day, one runs once per hour, and another runs every 17 minutes. It is a NetIQ application. I just scheduled SQL Server maintianace job last night which ran at 2:00Am and 4:00Am. This morning, I came in office and found all my jobs were still running; and they were all blocked by the first 3 jobs. I had to kill all of them. In this afternoon, I kicked off one of my many DTS packages which runs usually about 40 minutes, but it failed. I tried several times but no luck. I suspected one of user tables corrupted or one of stored procedures corrupted. After I recycle the server, and dropped the table and the stored procedure, and recreated them, the package went fine. The store procedure involves many updates and inserts.
The question I have is: is it possible to cause this problem because I killed the unfinished jobs (especially the sql maintanace job)?
NOTE: the sql maintanace job does not include the backup of database and transaction log.
When I execute the following stored procedure it runs for about a minute.
CREATE PROCEDURE EquipmentListByProduct
(
@iProdTypeId int
)
AS
SET NOCOUNT ON
DECLARE@iError int, @iRows int
SELECT pn.prodTypeId, pn.prodId, pn.prodName
FROM prodNames pn
WHERE pn.prodTypeId = @iProdTypeId
SELECT@iError = @@ERROR, @iRows = @@ROWCOUNT
IF ( @iError <> 0 )
BEGIN
RETURN@iError
END
IF ( @iRows = 0 )
BEGIN
RETURN-1
END
RETURN@iError
GO
The table only has 22 records.
Do I need to index the table? If so how do I do this?
My backups are running 5-6 hours on SQL2000. I'm sure they only used to take 1 hour or so. On another server, backing up the same database (both about 50 gig), the backup only takes 45 min - 1 hour. What can I look at to see why it's taking so long ?
(edit) I'm using a maint plan to backup to disk
Trying to come up with a way to monitor (without profiler, hopefully with a job and a select statement) a specific sql job that may cause a problem if the duration is too long. It seems that there is an sp called sp_sqlagent_log_jobhistory that shoves a record in sysjobhistory, but only after all the job steps run. Anyone tried this before?
View 1 Replies View Related1. could any one tell me in a simple way how to troubleshoot long running queries.
2.what is the default recovery model
Hello,
I want to find long running queries?
Can any one help me?
Thanks
Prashant Hirani
Hello Gurus
I am using sql 2005 and one job status is executing in job monitor in 2005,How can i check since how long this job is running?
Please advice
Nitin
I've got a server (SQL 2K, Win2K) where the backupshave started running long.The database is a bit largish -- 150GB or so. Up untillast month, the backups were taking on the order of4 to 5 hours -- depending on the level of activity on theserver.I'm using a T-SQL script in the SQLAgent to run thebackups. Native SQL backup to an AIT tape drive.Now, for no apparent reason, the backups are takingon the order of 24 to 26 hours. The backups completesuccessfully -- no errors, just taking an outrageouslylong time to complete. DBCCs check out AOK, noproblems with the database.No changes to the machine. No hardware changes. Nosoftware changes. Weird.Multiple tape media have been tried -- it's not a caseof a tape going bad.We've had no problems with this box for almost 4years. Now it's gettin' jiggy with us!Any ideas on where to start with this one?Thanks in advance.
View 1 Replies View RelatedHi. I'm fairly new to SSRS, and very new to this forum. I have a report based on a stored procedure. I've optimized the procedure so that it runs from 2-4 minutes (previously over half an hour). However, when I run the report that calls the sp, it runs forever (well over 45 minutes in some cases), and the users basically give up on it. Any ideas of why this happens and what steps I can take to improve performance?
Thanks,
Marianne
I need to execute a long running package (it takes about 16 hours to finish) to load a data warehouse for the first time with all historical data. This package it's a master package and execute other packages; I log the start time and the finish time of the package in a table to manage future incremental loads.
I executed the package on sql server where it is saved, but after 8 hours it was running, a new package was started automatically. Then two more packages started .. each every two hours.
I set the MaxConcorrentExecutable = 4, this could affect this strange behavoir ?
Anyone could imagine wath happened ?
Thanks
Cosimo
Hi,
I'm trying to optimize a long running (several hours) query. This query is a cross join on two tables. Table 1 has 3 fields - ROWID, LAt and Long. Table 2 has Name, Addr1,Addr2,City,State,Zip,Lat,Long.
Both tables has LatRad - Lat in radians, LonRad- Lon in Radians. Sin and Cos values of Lat and Lon are calulated and stored to be used in the distance formula.
What I'm trying to do here is find the nearest dealer (Table 2) for each of Table 1 rows. The Select statement takes a long time to execute as there are about 19 million recs on table 1 and 1250 rows in table 2. I ran into Log issues- filling the transaction log, so I'm currently using table variables and split up the process into 100000 recs at a time. I cross join and calculate the distance (@DistValues) and then find the minimum distance (tablevar2) for each rowid and then the result is inserted into another Table (ResultTable).
My Code looks like this:
Declare @DistValues table (DataSeqno varchar(10),Lat2 numeric(20,6),Lon2 numeric(20,6),StoreNo varchar(60), Lat1 numeric(20,6),Long1 numeric(20,6),Distance numeric(20,15))
Declare @MinDistance table (DataSeqno varchar(10) primary key,distance numeric(20,15))
Insert into @DistValues
Select DataSeqno,T.Lat Lat2,T.Lon Lon2,S.StoreNo,S.Lat Lat1,S.Long Long1,
distance=3963.1*Case when cast(S.SinLat * T.SinLat + S.CosLat * T.cosLat * cos(T.Lonrad - s.Lonrad) as numeric(20,15)) not between -1.0 and 1.0 then 0.0 else acos(cast(S.SinLat * T.SinLat + S.CosLat * T.cosLat * cos(T.Lonrad - s.Lonrad) as numeric(20,15))) end
from dbo.TopNForProcess T , dbo.Table2 S where Isnull(T.Lat,0) <> 0 and Isnull(T.Lon,0)<> 0
Insert into @MinDistance
Select DataSeqno,Min(distance) From @DistValues Group by DataSeqno
Insert into ResultTable (DataSeqno,Lat2,Lon2,StoreNo,LAt1,Long1,distance)
Select D.DataSeqno, D.Lat2, D.Lon2, D.StoreNo, D.LAt1, D.Long1, M.distance from @DistValues D Inner Join @MinDistance M on D.DataSeqno = M.DataSeqno and D.Distance = M.Distance
I created a View called TopNForProcess which looks like this. This cut down the processing time compared to when I had this as the Subquery.
SELECT TOP (100000) DataSeqno, lat, Lon, LatRad, LonRad, SinLat, cosLat, SinLon, CosLon FROM Table1 WHERE (DataSeqno NOT IN (SELECT DataSeqno FROM dbo.ResultTable)) AND (ISNULL(lat, 0) <> 0) AND (ISNULL(Lon, 0) <> 0)
I have indexes on table table1 - Rowid and another one with Lat and lon. Table2 - Lat and Long.
Is there any way this can be optimized/improved? This is already in a stored procedure.
Thanks in advance.
Hi!
I just wonder if it is possible to run a scheduled task from a SQL Server 6.5 Client. Whenever I would try to run a scheduled task from a SQL Server Client a would get the error message that says "The SQLExecutive service is not currently running on server 'POFDS1099'. This prevents task 'ACORS2 FS90 People Pull' from being run", but when I would try to go the server itself, the service is runnning. Can anybody please tell me what could be the possible cause of this or perhaps 6.5 doesn't really support running scheduled task using SQL Server client. Thanks.
By the way, I also have SQL 2000 Client running on my local.
Thanks again,
Lhot
i had scheduled to run a package it failed but a part of the package still shows up on the integration system running packages. i tried to stop it or delete it nothing works. when i try toi run the package even in visual studio that part of the package fails.
how do i remove the running package???
Thanks
jas
I have a SQL procedure that can take several minutes to complete. I allow users to initiate the process through a web site and view a progress bar. When the process is running, though, the site slows to a crawl or times out completely. That long running process seems to block all other queries on the database. Is there a way to give this process a low priority or somehow throttle its resource use so that the other web processes can get a chance to run in a timely manner?
Thanks for any advice.
Hello,
I am having some difficulty with data-driven processes in an internal web application using ASP.NET framework 2.0 with SQL Server 2000.
I have a facility where the user enters search criteria to retrieve a list with the idea being that when they click a button the list set is migrated to a table as a batch. The SQL Server processes to perform the database operations can take up to 5min because of volume of data and relational complexity, which is fine.
The only problem is that I'm having trouble managing the SQL Server process if the user closes the browser or go to a new URL etc. If this happens on the client side it's like the HTTP request is terminated but the SQL statement left on the SQL Server still running, eventually it runs to completion but I would prefer the SQL process to also be abandoned and rolled back.
Any idea what is happening here and how it can be handled?
Thanks for any help.
A.C
Hello All
I have queries which take over 30 secs to run, which I wish to monitor.
Currently, I am monitoring using sql profiler.
Is there any way of setting up mail to e-mail me when such a query happens.
Could I set up an Alert, or is there some other method.
I want to be able to react to these events faster before the users complain.
I am using sql server 7 enterprise and I have exchange set up.
Any help would be great
Pargat
Hello,
I have a very long running stored proc (import and transformation of 2-3 M. records). The duration of about 1h is not the problem.
My problem is that i want to send some notifications to the UI for showing a progress counter in the UI.
Is there any possibility to send send out a msg after certain steps (e.g. after positioning the cursor to next row) via an extended stored procedure which I could catch in my UI?
I would be very appreciative if somebody would send me a hint.
Thank you,
selo
I’m trying to write a script that will detect long running agent jobs.
Having looked at this article:
http://www.databasejournal.com/features/mssql/article.php/3500276
It appears that agent job job id’s don’t necessarily get stored in the programname of the sysprocesses table. This is true if the agent executes an os command. It also appears that job steps do not get stored in the sysjobhistory until the step is complete so that cannot be used accurately.
Does anyone know of an effective way to find if there are long running jobs other than these methods?
I just wanted to post a follow up to a message I posted some months agoabout a long running transaction that was blocking all other users...The link is belowhttp://groups.google.com/group/comp...649bee2002646a2By using the new "Row versioning" functionality of SQL 2005, itcompletely solved this problem. By reading the books online, it saysthere is a performance impact, but that the better performance of SQL2005 in general might offset it. So far this seems to be the case. justposting it here in case anyone else has the problem. The SQL command Iihad to execute to get everything working properly was:ALTER DATABASE DBnameSET READ_COMMITTED_SNAPSHOT ON;
View 1 Replies View Related