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.
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.
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
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?
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.
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.
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.
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.
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.
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 ?
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
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.
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.
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.
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 !!
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.
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!
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....
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.
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)
have SQL Server 2005 std edition SP1 installed on Windows 2003 Std edition .Configured Transactional (single Publisher and no clustered environment.) Replication past two months working fine, Now 1.Distrib.exe application err is coming.
Due to which my job is failing (Distributor to Subscriber). Iam attaching thw file. Thanks Sandeep
Hey, don't know if it's the right place for this question but i hope you help me. I made an application with VS 2005 that connecting to sql server file db.mdf Now i want this application work on another user computer, and of course i don't want to install vs 2005 there. I did install .net framework, but what i need to do to make the database work? do i have to install sql server on his computer? or is there something more simple? I know that if i was using access file than i need to install nothing else. please help! thanx. max
ALTER procedure [dbo].[AHD_EmpID_GET] as begin declare @User varchar declare @empid varchardeclare @Pword varchar select E.EmpID as Code, E.Empcode as Description from AHD_glb_Employees E inner join AHD_users U on E.empid=U.empid --where E.Empid=@empid end i have tried this but it is not getting a unique value........... can u help me pls??
I've got an application that needs to:1. Look on the user's machine and locate a file2. Pull that file over to DTS3. Allow DTS to insert this file into a table I'm sure this can be done....anyone got any article on how to do it? Thanks!
I have created a Visual Basic application the connects to my SQL database. The appliction lets a user click on a button, data is retrieved from the database and then formatted in Excel. Everything works fine on my machine (this is probably because i have both VB and SQL installed on my machine) I have created a .exe and deployed the application on some users desktops. The problem is that when they click on a button - nothing happens. I dont even get any error messages! Everything else appears to be fine. I have tested the DSNs and they appear to be fine too. Does anyone know why this would happen ??
hI, EVERY ONE. 1)IF I EXECUTE QUERY FROM QUERY ANAL , QUERY & STORE PROC RUN VERY FINE , BUT SAME REPORT IF I DO FROM FRONT END(i.e from web application tool) THEN IT TAKE VERY LONG TIME.WHAT COULD BE THE REASON ?
2) IF I REBOOT MACHINE - SQL SERVER. THEN FROM FRONT END if I RUN ANY REPORT THEN IT WORK FAST BUT NOT same AS OF QUERY ANALY .I DON'T UNDERSTAND THIS . PLEASE ANY ONE HAS COME ACORSS THIS PROBLEM & HAVE ANY SUGGECTION PLEASE REPLY ME
Whenever a user logs on to SQL-Server there´s a message in the application log of windows NT like "Login succeeded for user 'Domainusername'. Connection: Trusted." Since we have lots of users. The application log fills up twice a day. Is there any way to prevent SQL-Server from doing this ? (I know that there are settings in the event-viewer which clear events whenever space is needed).
i hv developed an application in vb.net, database is in server and more than 50 PCs i have installed my application. Is this the correct procedure, or else i hv to install it on the server and get the shortcut file in all the PCs.because if suppose i want to send a new patch file that time iam going to uninstall my application in all the PCs and installing it again. It takes time.
if i install this in server and create shortcut on every client pc, then how should i management variables value while coding. Please do let me know. thanks for your help
I'm not sure if this is the right section for the question, but here goes...
I have an application that needs to be run in network client mode. This app is supposed to connect to a SQL 2005 db hosted on a server. As I procede through the installation process I get a prompt when i populate the serverdb information.
'The listener port was responsice, but did not return a primary port number for <servername>. Please verify that <servername> is a valid and running database before proceeding.
A little background is in order:
Host:
Server 2003 Standard
SQL Server 2005
Installed instance of said db
Client:
Windows XP Pro sp2
Network config:
We have a domain with about 125 workstationslaptops. Windows firewall is turned off using a group policy (makes it easier to push apps out to clients). For giggles I made exceptions to the 'turned off' firewall policies for the needed ports etc. This was done on both the host server and the client.
is it possible to call a SQL job from an ASP.NET application? if so, how? I'm using VS.net2003 and am programming in C#I've been searching for a while, but haven't found anything. It doesn't help that one of my keywords when searching is "job(s)."
