Help To Determine Cause For Package Hung Up
Apr 7, 2008
Hi all,
My SSIS packages seems to run well in test environment.They are scheduled to run as jobs on a daily basis on the
production system.Most of the days they run well,but somedays they keep running and never end,till we manually kill the jobs and re run them.It's really difficult to find the cause as it does not even fill the log file.
If the package starts the first task is to fill in a table,but its not filling it,so i infer that the package has not started itself,
Did anybody of you have come across such a situation?Pls give your inputs.
Any help on this is greatly appreciated.
Thanks
View 5 Replies
ADVERTISEMENT
Oct 12, 2006
I should have written this down when I came across it -- but I recently saw an example of how to determine a package's currently-executing folder. Can anyone help?
Thanks!
- Mike
View 5 Replies
View Related
Jun 20, 2006
I create a DTS package that copy data from oracle to SQL 2000. In the package, I have 2 connections, 1 transform data task, and 1 execute SQL task to truncate the table before copying the data. The DTS works by right clicking and EXECUTING.
however, it does not work when I run it from a job. The job keeps saying
"Executing...." and it runs for hours when it only takes a few seconds to run using DTS package
DTS package owner is "sa"
Job owner is "sa"
What did I do wrong? Any suggestions are greatly appreciated.
thank you
View 3 Replies
View Related
Jul 20, 2005
using SQL7 and we have very occasionally had ascheduled nightly jobhang - it does not fail so we don't get an email it is just"executing" - forever.How could I test for this situation to generate an email if the jobruns longer than say 15 minutes?ThanksBill
View 3 Replies
View Related
May 12, 2000
I have a scheduled task (a database dump) that shows it is running the Manage Schedule Tasks
window on the running tab but the task is not in sysprocess or current activity.
I checked the msdb database and it is not full, I checked systasks and syshistory and
it shows that the task is no longer running and it completed successfully. The task
did complete successfully, but why is it showing that it is running?
View 2 Replies
View Related
Feb 21, 2001
I am experiencing on our Sql 7 sp1 database orphaned connections that I cannot kill. Is there another way to kill these connections besides stopping and starting the service. I have been to the microsoft support page and found some information on it but not any relating directly to this problem in sql 7, most in 6.0 and 6.5.
Thanks
Tom
View 1 Replies
View Related
Nov 7, 2005
Hello ,
We have a problem running our clean up stored procedure along with our application. When this happens, both the application as well as the job that runs the stored procedure seems to hang.
Here is the explanation of the problem:
The cleanup stored procedure deletes entries from a set of related tables as follows:
1.DELETE FROM TABLE2 WHERE F1 IN ( SELECT F1 from TABLE1 tm WHERE F2 < tm.currentdate AND tm.currentCount = 0);
2.DELETE FROM TABLE3 WHERE F1 IN (SELECT F1 from TABLE1 tm WHERE F2 < tm.curentdate AND tm.currentCount = 0);
3.DELETE FROM TABLE4 WHERE (F1 IN (SELECT F1 from TABLE1 tm WHERE F2 < tm.curentdate AND tm.currentCount = 0) AND CURRENTID IS NOT NULL);
4.DELETE FROM TABLE4 WHERE F1 IN (SELECT F1 from TABLE1 tm WHERE F2 < tm.curentdate AND tm.currentCount = 0)
5.UPDATE T_ TABLE1 SET CURRENTID =null WHERE (CURRENTID IN (SELECT F1 from TABLE1 WHERE F2 < curentdate AND currentCount =0));
6.DELETE FROM TABLE1 WHERE F2 < currentdate AND currentCount = 0;
7.COMMIT;
The application inserts an entry into the 4 tables (TABLE1..TABLE4) in the following order:
1.Invokes a stored proc to insert a row into table TABLE1
2.Invokes a stored proc to insert a row into table TABLE3
3.Invokes a stored proc to insert a row into table TABLE4
4.Invokes a stored proc to insert a row into table TABLE2
The application I refer to is a Java application that uses CallableStatement to invoke the stored procedures and the “java.sql.Connection” has been configured with “auto commit”. Since, the application uses a connection pool, the stored procedures are executed in the context of different “Connections”. The problem happens only with Oracle and not with SQL server.
If you have come across this type of problem, please let me know how you have resolved this problem. Alternatively, if you identified the problem from the description, let me know your suggestions.
Waiting for the precious reply….
Regards
SS
View 2 Replies
View Related
Oct 28, 2004
Hello Friends,
A user is calling stating that their application is hung - sitting with an hour glass or with the browser not responding. Assume you know the server and the instance and that the server supports multiple instances.
You need to determine if the database could be contributing to the problem or could be the cause of the problem. Describe at a high level what process you would follow to rule the database out or in as the problem.
Any idea!
Thanks,
Frank
View 6 Replies
View Related
Feb 7, 2007
Hello,
I have a process which is hung in Sql Agent 2005. The first step is an SSIS package which loads our data warehouse. The final step of the SSIS package is running a DBCC CHECKDB Task. Looking through the logs I notice that this task ran, and that the package reported Success in the System logs. Sql Agent Monitor shows that it is still executing this step. Moreover, no errors have been reported in the following logs; Sql Agent, Sql Server or the System logs.
We currently have this step executing under a proxy account which has administrative rights. We ran the agent for the first time yesterday as a test and it ran through correctly.
If you need more information I would be happy to provide it.
Any advice would be greatly appreciated!
Best regards,
View 5 Replies
View Related
Mar 5, 2007
I am trying to install SP2 and it seems to have hung. The setup screen says that it had success with the setup support files and is now on the database services "in progress" it is installing windows installer file sqlrun_sql.msp and has been here for 45 minutes. Any help would be appreciated.
Thanks
Chris
View 3 Replies
View Related
Feb 4, 2008
Inside my dataflow task, I've got 5 OLEDB Source components pulling data in from various SQL Server Instances.
I have an OLEDB Destination pointing to a single table that should receive all of records create by the combination of Merge Joins of these five sources.
Right now I'm working with a set of test data, and I've got 8 records coming through after all of my merge joins filter things out.
The problem I am seeing is that my OLEDB Destination turns yellow and then just sits there. I've looked in my Progress (Execution Results) tab when I run the package, and there's nothing alarming there.
I've run SQL Profiles on the database table I'm trying to insert data into. Nothing.
I've also change the destination from 'Table or view - fast load' to 'Table or view' . I've unchecked the table lock option for fast load. No dice.
The only thing I can say I've seen that looks suspicious is that I have added some data views between data flow components just before the data gets to the OLEDB Destination and a few of the fields in the data view will show <Missing LineageID> in the field rather than the value. I removed all of the columns that were behaving this way from the flow just to see if that was the problem or if, by removing these fields, I was able to get data to write to the table. This did not work. It does leave me wondering though.
Anyone have any idea why this might be happening or have had similar behavior?
Any help/ ideas of things to check would be greatly appreciated.
Thanks in advance.
Isaac
View 3 Replies
View Related
Aug 22, 2007
As we are beginning to use sqlserver 2000 more and more on a daily basis, the database appears to be hanging more frequently. The solution so far has been to bring the database down and then restart it, or in some cases backing it up. Is there some log file I can look at that will give me an indication as to what is wrong ?
View 5 Replies
View Related
Apr 26, 2004
Hi,
Below messages got throwed to sql ErrorLog. I could'nt understand what it means.. any help is appericated.
The Scheduler 2 appears to be hung. SPID 0, ECID 0, UMS Context 0x03CBAA40.
Error: 17883, Severity: 1, State: 0
WARNING: EC 36b9c098, 8 waited 300 sec. on latch 444f3b84. Not a BUF latch.
thanks,
-J
View 3 Replies
View Related
Aug 13, 2004
Hello,
The users of my microsoft SQLServer 7 database could not log on to the database after the following message has appeared in the log file;
The Scheduler 0 appears to be hung. PSS 0x5B1630C8, EC 0x5B163278, UMS Context 0x3FC37E88
The problem was solved after I have restarted the database. I would be grateful if you could show me how to avoid such a problem in the near future.
Regards,
Albert
View 4 Replies
View Related
Oct 7, 2004
I'm restoring a complete backup of a database from Enterprise Manager. This has been running for 3 hours and shows no sign of progress. The "Restore Progress" dialog is on the screen and the progress bar hasn't showed any progress. The disk is at full capacity in performance monitor. Nothing else is using the system.
The data file is 14 GB. It seems like something is wrong. Anything I can do to check?
SQL Server 2000 SP3a Enterprise Edition
Windows 2000
View 2 Replies
View Related
Jul 21, 2000
Hi,
Looking through the archives, I didn't see any articles that specifically addressed the problem, so here it is:
SQL 7.0, NT4SP6, 2G ram, 4x
I've got a user process as follows:
Status: ROLLBACK
Command: SELECT
Application: Enterprise Manager
Wait Type: EXCHANGE
Login time: 06/25/00 4:07:05AM
Last batch: 06/25/00 4:07:20 AM
The last TSQL command batch is a correlated subquery with grouping...
Apparently it hung and the user quit ungracefully.
No other processes are blocking it, but periodically it blocks other processes, including some index maintenance I need to perform.
I have tried to kill it with Enterprise Manager and with the KILL [id] command. Neither have worked.
Mgmt is reluctant to bounce the database, as am I.
Any ideas?
Thanks in advance!
--Nassif
View 2 Replies
View Related
Apr 17, 2008
Folks,
I have a SSIS package that works fine 90 % of the time. Every once in a while it gets hung and when I notice that the package has been running for several hours I check the job activity monitor and notice that the package is in executing state for several hours..my question is ...Is there a way I can check for this incidence through a c# app ? Does the package name show up in the process task if so I can poll to see if the process corresponding to the package has been executing for > 30 mins then I can kill it.
Any suggestions would be great.
Thanks
View 6 Replies
View Related
Mar 4, 1999
I'm attempting to develop a course whose objective is to present the users with several scenarios of broken or hung databases caused by different things that they then have to fix.
Do you have any ideas or examples of how to break a database and the reasons behind it, also how to repair it afterwards !!
Richard
View 1 Replies
View Related
May 6, 2004
Environment:
Clustered SQL Server 2K sp3a (single instance)
Product version: 8.00.760
Problem:
I end up in error 17883.
I am aware that the solution is to apply this patch:
821277 MS03-031:Security Patch for SQL Server 2000 Service Pack 3.
I am also aware that after you install this cumulative patch you will end up in this problem:
826161 FIX: You are prompted for password after you change a standard SQL Server login.
After reading this one I found that there are 2 possibility to solve this problem, one if you have already installed the security patch from MS03-031 and another one if you do NOT have installed this patch.
Question:
Do I only need to apply this last one (is it also cumulative?) or do I need to first apply MS03-031 and then later the FIX 826161?
Please advise.
Kind regards.
View 1 Replies
View Related
Jun 26, 2007
I'm having an issue in SQL Server 2005 with jobs that execute SSIS packages. The jobs run fine for a week or so, then I'll come to find that four or five (of the ten or so jobs) are hung in "executing" status. They seem to hang indefinitely (as some have been "executing" for hours with no end. The schedules of the hung jobs are all different, varying from every 10 minutes to nightly. The packages perform completely diffent tasks, as well. I can't seem to find any common thread with the jobs that get hung, other than they are all executing SSIS packages.
I've tried manually stopping the jobs and restarting the agent and SQL Server but the jobs hang again on there next scheduled run. The only thing that fixes the issue is rebooting the box, and then the jobs hang again in a week or so. Could some sort of memory leak be consuming resources throughout the week and be causing the jobs to eventually hang? I just rebooted the box and the sqlagent90.exe process is currently using about 7mb of memory. I'll keep an eye on it. Any other suggestions?
I've thought of creating another job that stops jobs that are hung, but what's to say that this job won't get hung as well? Plus this seems like a band-aid fix...
I don't recall having these problems until installing SQL Server 2005 SP2. Could this be related? I've searched like crazy and still can't find a resolution to this. It's becoming a big PITA...
Anyway, any suggestions would be very much appreciated!
View 6 Replies
View Related
Sep 14, 2006
I am having some problem in running SSIS scheduled job.
The job is processing (updating and inserting) batches of data using Execute SQL Task inside Foreach loop container. But to make sure the batch complete successfully I put the "Begin Tran" Execute SQL Task and "Commit Tran" Execute SQL Task before and after the foreach loop
My workflow is like the following....
- Execute SQL Task "Begin Tran"
- ForEach Loop Container for a batch of records.
- Insert the records into the database
- End ForEach Loop
-Execute SQL Task "Commit Tran" if the above ForEach Loop complete successfully
-Execute SQL Task "Rollback Tran" if the ForEach Loop failed
Normally the insert records are around 10~200 and the job is running very fine...
But if the records are more than 1000s then sometime the job hung (I would say hung because the DTEXEC.exe can be seen inside the windows task manager.) There was no indication what so ever whether the job finished or the job failed or the job else....
Can anybody help to find the cause.....
Rgds,
KyawAM
View 5 Replies
View Related
Jan 10, 2007
Have an interesting situation.
I have a NeverFail cluster on which I loaded SQL Server 2005 and SP1.
All was great. Both nodes had no issues with the initial setup or the SP1 update.
When I forced a failover to the inactive node, MOST of the services like SSIS, Full-Text and SQL Browser came up like a champ.
BUT ... the SQL Server service and the SQL Agent did not come up (or shall I say not fully).
I am now looking at the SQL Server Configuration Manager and I see the following for the SQL Server service:
"Name" shows the RED block icon for the SQL Server
"State" shows "Change pending ..."
"Start Mode" is set to Manual
"Log on as" has my domain account listed
"Process ID" has the number 2956 (so it has started somewhat).
I then click open the SQL Service item and it shows that it is stopped.
I am given the option to START the service. When I try to start it, the meter bar comes up and moves slowly to the end and then an error returns of ...
"The request failed or the service did not respond in a timely manner. Consult the event log or other applicable errors logs for details"
If I look under windows Services in the Admin Tools section... I see the services for SSIS, FT and Browser listed and started BUT I see SQL Server says its "starting".
If I click open the service from here, once again it show "starting" and all of the option buttons to Stop-Start-Pause-Resume are grayed out and I am not able to use them.
I have looked in the Windows Event Logs for any events but none are present.
Now I know why the SQL Agent is not running, and of course that is due to is dependency on SQL Server being up and running.
First off WHAT does the "Change pending" message mean?
Then what other logs can I look at for some help or is there someone who has the answers to this dilemma.
Thanks and have a great evening
View 11 Replies
View Related
Feb 5, 2008
Greetings,
I am trying to restore a SQL Server 2005 database from a backup file and experiencing a hanging issue after its "finished"
I am doing this in SQL Server Management Studio, generating the following SQL for Restore:
RESTORE DATABASE [AdventureWorks2]
FROM DISK = N'C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLBackupAdventureWorks.bak'
WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 10
GO
When I run this on the machine i originated the backup with (creating AdvWorks2) it runs fine in no time.
When I run this command on another SQL Sever 2005 instance on another host. It appears to run fine, and I see progress going up to 100% and it says "Restore Completed Successfully"
BUT, for some reason, the database in object explorer is stuck with a "(Restoring...)" label attached to its tree item and I am unable to perform any activities on that database instance. It claims, it's in the middle of a restore operation! again this is after it had reached 100% on progress and declared successfull completion.
Any ideas what could be causing this?
(Note: Both instances are SQL Server 2005 - Service Pack 2)
Thanks,
-ali
View 4 Replies
View Related
Jan 22, 2008
Hi all,
How to determine the value for the bar in Neural Network Viewer? I know the value is displaying in the tooltips when we pointing to the bar in the table but i don't know how to get them. So where can i get those of calculation or data of score, probability of value1 and 2, and lift for value1 and 2 ? Is it get from the Microsoft Neural Network Content Viewer? which column and how to calculate? If not, please advise.
Hope my question is clear.
I am looking forward to hearing from you shortly and thanks a lot in advance.
Thanks!
With best regards,
Yours sincerely,
xuenly
View 8 Replies
View Related
Sep 21, 2006
I am trying to determine the next available order id using the method below. It works provided the table has a record in it. If it doesn't I get the error "Input string was not in a correct format." I am certain that it is because the query is returning a value of NULL. How can get around that or check for the NULL value?' Establish data connection...Dim sqlConn As New SqlConnection(ConfigurationSettings.AppSettings("connectionstring"))'Determine order id number...Dim order_id As IntegerDim strSQL As StringstrSQL = "Select MAX(order_id) from mkt_order"Dim sqlCmd As New SqlCommand(strSQL, sqlConn)Dim sqlDA As New SqlDataAdapter(sqlCmd)Dim sqlDS As New DataSetsqlDA.Fill(sqlDS, "item")If sqlDS.Tables(0).Rows.Count <> 0 Thenorder_id = Convert.ToInt32(sqlDS.Tables(0).Rows(0)(0).ToString()) + 1Elseorder_id = 1End If
View 1 Replies
View Related
Aug 3, 1999
Good day,
We have a web application (ASP) running on SQL Server 7.0.
Recently, the users are getting quite a lot of timeouts on
the database:
Microsoft OLE DB Provider for ODBC Drivers error '80040e31'
[Microsoft][ODBC SQL Server Driver]Timeout expired
The database is not supposed to be doing too much work, so I can't
understand why these timeouts are occuring. How can I determine
the cause of the timeouts?
The cause could probably anything from a trigger that's taking
too long, a query that's taking too long, or simply bad database design.
I've looked at SQL Server's Profiler, but could not yet use it
successfully to give me any hints of what could cause the timeouts.
Any ideas of how I can use Profiler, Performance Monitor, or any other
tool(s) to see what is happening in the background in the database,
i.e. how much processing a trigger is using, etc.
Thanks very much!
---
Gert Lombard
OSI Airport Systems
South Africa
View 1 Replies
View Related
Dec 28, 2006
I'm new to full text catalogs and we have a vendor who's code utilizes them. The database server is SQL 2005 and I am noticing the following message in the SQL log every minute.
Changing the status to MERGE for full-text catalog "ResearchCatalog" (5) in database "DBA_Test" (11). This is an informational message only. No user action is required.
A SQL job is running the following command every minute.
exec sp_fulltext_catalog 'ResearchCatalog', 'start_incremental'
What is the typical frequency for running an incremental?
Can the messages be suppressed?
Thanks, Dave
View 3 Replies
View Related
Jul 23, 2005
for sql servers how can i determine who [what accounts] are the dbas?
View 1 Replies
View Related
Jul 20, 2005
I've been doing a lot of reading on this and my head is starting tohurt! It seems to be quite a feat to work out how much memory isactually being used by our server.I'm running W2K advanced server with SQL 2000 EE, 8GB of RAM, a min of4GB and a max of 6GB is assigned to SQL server.I'm trying to work out whether we've assigned enough or toomuch/little memory to SQL server. My first thought was to let SQLdymanically manage its own memory and see how much it uses, of coursewhen AWE (/3GB /PAE) is enabled it will just use all that isavailable.In perfmon "target server memory" = 6.1GB, "total server memory" =6.1GB, "total pages" = 768000 ( x 8KB = 6.1GB).My second thought was to use "total pages" - the average "free pages"= average mem used, therefore giving me the average amount of memoryused by SQL. I found out that SQL uses a min of 4GB (the min weassigned) and the max of all the memory, 6GB.Is there an easier way of finding out how much memory is actually usedin this situation or is going by the above average the best way?What i'm unsure about is will SQL just use all memory assigned to ituntil it has the whole DB in memory? 20GB including indexes etc....Any help would be greatly apprechiated.
View 2 Replies
View Related
Dec 1, 2006
I need to determine when (maybe) and if (definitely) a SQL Agent job will run again. I need to maintain a table of the next pending execution for each job. I need to be able to update this table from within a SQL Agent job, but preferably from within an executing SSIS package in the job. Is this possible and if so, any suggestions on how?
Thanks
View 3 Replies
View Related
May 3, 2008
Hi all... This is the definition on the M/S site:
"Deterministic functions always return the same result any time they are called with a specific set of input values and given the same state of the database. Nondeterministic functions may return different results each time they are called with a specific set of input values even if the database state that they access remains the same."
Good... straight forward, right? Ok.... try entering thses command seperately:
Create Table Readings (ReadingDate DateTime Not Null);
Create Function [dbo].[funct_SameDate](@datReadingDate Datetime)
Returns DateTime
As
Begin
return @datReadingDate;
End
Alter table Readings add
[TempColumn] as (dbo.[funct_SameDate](ReadingDate)) PERSISTED NOT NULL;
Error on last command returns:
Computed column 'TempColumn' in table 'Readings' cannot be persisted because the column is non-deterministic.
Can someone please explain this to me? The same value is always being returned.
This does work:
Create Table Readings (ReadingDate DateTime Not Null);
Alter table Readings add [TempColumn] as (ReadingDate) PERSISTED NOT NULL;
I obviously want to do more things inside the function, but I can't get by the first step.
Any suggestions?
Thanks!
Forch
View 6 Replies
View Related
Dec 27, 2007
Hi,
I have a report that has a table with detail grouping. This table shows the sales by day for each product. The users only want to see the date field for the first item in the group. After that, they do not want to display this field (to reduce the data on the report). However, when the data wraps to a second page, they want the date to appear on the first row of the new page.
Is there any way to determine if a row is the first row on a page?
I tried using the RowCount, but that continues from the previous page.
Any thoughts?
Thanks!
David.
View 8 Replies
View Related
Jun 18, 2007
hi,
since I am kind o'new with SQL, I preffer get an advice fro you pro's: I created an application which performs access to a database on an SQL server. the application will be used by a few different users, each on a different computer. the application calls stored procedures, updatesinserts records in tables on the SQL and delete rows. what would be the best role to define the users activity ? How do I limit their activity ONLY to the specified actions ?
View 1 Replies
View Related
