I/O Bottleneck With SQL Agent
Mar 29, 2007
Hello,
I have an SP that transfers data from one DB to another.
As I run the SP with the management studio - it takes about 15min. But when I scheduled it as an SQL agent job it ran all night and never completed.
The difference I noticed was that the % disk time (of the destination DB) was very high (~100) when I used the agent (which never happened when I ran the SP with the studio).
Only when I restart the SQL service, the disk gets back to normal % disk time.
What could be the reason for that?
My server - Windows 2003 R2 SP1 / SQL 2005 SP2
p.s - could it be related to SQL SP2 ? I didn't have this problem with SP1, but then - I had many other changes since.
View 3 Replies
ADVERTISEMENT
Mar 18, 2004
Hi,
We have been getting really bad performance from one of our databases recently.
This is a three tier system with two services (.dll) connecting to the database, we have only experienced problems since the second service has been installed.
These two services both use the same user and make about 300 connections to the database.
I have noticed IO bottleneck when the system is under load, and the Average disk queue at this time is 15.
How do I fix this? We have the logs and data on the same raid 10 array.
View 10 Replies
View Related
Feb 15, 2005
I am trying to figure out if we have a memory bottlneck on our SQL Server. We have a large database (90 GB) on Standard Edition of SQL 2000. Currently we have 2 GB of Memory. To me this seems low already, but Standard edition can't handle more than 2 GB anyway. Our cache hit ratio is very low (40%). This is also telling me we need memory. The thing that gets me is we still have 147 MB of memory available. If Memory really was the problem wouldn't that 147 MB be used?
Thanks much.
View 14 Replies
View Related
Aug 6, 1999
Good morning,
I've just updated my databases to V7.0 from 6.5. I am having some issues with CPU utilization after rebuilding the databases. I rebuilt the databases from scripts after modifying the scripts to deal with any SQL issues between the versions.
Symptoms:
When a bunch of stored procedures are executed the % Processor Time his 100% on a single processor system, the Processor Queue Length hits 13, the Batch Request/Sec starts at 20 and rises to 47 before dropping off, Context Switches go from 370/sec to about 833/sec before dropping off and there is an increase in page faulting.
My testing was done on a single processor but my real system is dual-300+ with 128Mb RAM. The database is only 40Mb and after running the index wizard there were no changes advised.
After reading a lot in help areas for MS and here I am not sure if this has to do with how SQL Server runs now (i.e. system configuration) or if it is a query issue with parallelism. I'm assuming query parallelism since the performance spikes occur when stored procedures are run. On a single processor is there a way to address this. Then, hopefully, I can deal with the real multiprocessor system.
Thanks much,
Don
View 1 Replies
View Related
Jul 20, 2005
I understand that there is much involved in figuring up I/Othroughput, but I'm hoping the answer to the following question willbe a simple A, B OR C.Given the configuration below, what hardware component wouldbottleneck first.A. Drive Spindle ThroughputB. SCSI ControllerC. Other Component(if so what component?)Configuration:Controller: Ultra320RAID Config: RAID 5Drives on RAID: 14 - 73gb/15k - SCSI DrivesType of Writes: RandomQuestion Background:I currently have the configuration above on a local box. Our companyhas ordered a SAN and and has space set aside. What I'm trying toacertain is whether or not I'd be better off staying local or ifputting my DB on a SAN would be more beneficial.Other Notes:I've determined using a formula that determines the max theoreticalI/O operations/sec for the # of drives (spindles) and RAID Config...but I've not been able to establish a relationship betweenOperation/sec -> MB/Sec. I'm sure this is for obvious reasons...If anyone has had a related scenario, I'd be interested in hearingyour thoughts.Thanks,Ornac
View 9 Replies
View Related
Nov 30, 2006
when I run a package from a command window using dtexec, the job immediately says success.
DTExec: The package execution returned DTSER_SUCCESS (0).
Started: 3:37:41 PM
Finished: 3:37:43 PM
Elapsed: 2.719 seconds
However the Job is still in th agent and the status is executing. The implications of this are not good. Is this how the sql server agent job task is supposed to work by design.
Thanks,
Larry
View 1 Replies
View Related
Nov 30, 2006
Hi All,
I would appreciate any help here at all. I am pulling my hair out!
I am unable to start the snapshot agent or log reader agent from within SQL management studio. it fails on executing step 2 with unknown username or bad password.
I have checked all account details and they are correctly using my domain admin account. I can connect to SQL using teh same account and it also has sysadmin permissions.
If i copy the step 2 paramters and start from the cmd prompt (again logged in using the same domain account) they both start fine.
Any ideas would be gratefully received.
View 1 Replies
View Related
May 18, 2007
I am testing peer to peer replication in our environment. I simulated a three node peer to peer topology and a local distributor.
For some wierd reason I cannot get the Log Reader Agent and snapshot agent to start. The domain account under which SQL Server Agent runs has administrator previlage on the box. I also use a domain account for SQL Server Service. (none of the passwords changed).
This is the error I am getting - "Executed as user: abc. A required privilege is not held by the client. The step failed"
Any ideas???
Also this domain account is a member of
SQLServer2005MSSQLUsers$ServerName$MSSQLSERVER
SQLServer2005MSAgentUser$ServerName$MSSQLSERVER
View 3 Replies
View Related
Feb 19, 2007
We just moved source server to newer, bigger box ... Windows 2003 and Active Directory ... Snapshot agent worked but distribution failed ... Same login as on older machine, login is sysadm, used DCOMCNFG to allow ability to launch process ... What are we missing?
View 4 Replies
View Related
Feb 7, 2002
SQLServerAgent could not be started (reason: Unable to connect to server '(local)'; SQLServerAgent cannot start).
Is any has the same problem ?
View 6 Replies
View Related
Sep 9, 2004
hi.I have a sql2000 database.I have orders in a table with orderdate and ordertime.I want to force database to delete any order if person didnt pay for it after 3 days.I think I can check that with sqlAgent.but I dont know how.I checked most sites but couldnt find any sources.any help would be appreciated.
View 11 Replies
View Related
Jan 30, 2004
I am running SQL 2000 with SP3a and Windows Server 2003 successfully. I have a associate that is attempting to set up the same configuration. Our settings match identically, but his SQL agent will not start. I have checked everything I can think of but it still will not start on his network, yet works perfectly fine on mine. The error he is getting is that the SA account failed to log in properly, yet the password is correct. Any suggestions?
View 3 Replies
View Related
Jul 13, 2007
hi iam new to SSIS. I have two packages which are almost identical, only difference is the table name in the query to extract data just a test package.the package is created under my windows account, with the default protection level. I have scheduled them using SQL server agent. one runs succesfully and the other fails with error
Message
Executed as user: NAdbasvc-alticor. ... 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:28:25 AM Error: 2007-07-10 11:28:26.21 Code: 0xC020838F Source: Staging LKPDataReader Source [1] Description: An error occurred executing the provided SQL command. End Error Error: 2007-07-10 11:28:26.21 Code: 0xC004706B Source: Staging DTS.Pipeline Description: "component "LKPDataReader Source" (1)" failed validation and returned validation status "VS_ISBROKEN". End Error Error: 2007-07-10 11:28:26.21 Code: 0xC004700C Source: Staging DTS.Pipeline Description: One or more component failed validation. End Error Error: 2007-07-10 11:28:26.21 Code: 0xC0024107 Source: Staging Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 11:28:25 AM Finished: 11:28:26 AM Elapsed: 0.906 seconds. The package execution fai... The step failed.
I understand that it is something to do with
1.protection level
2.the user account under which the package is running
3.the SQL agent account used to run job
can someone be a detailed in explaining how to get this running and one big ? is why one package fails while the other with exactly same settings is running perfectly.
Please help i bid a lot of google on this but still confusing
View 1 Replies
View Related
Jul 13, 2007
Hi, I am new to SSIS. I createdtwo packages which are almost identical, only difference is the table name in the query to extract data just a test package.the package is created under my windows account, with the default protection level. I have scheduled them using SQL server agent. one runs succesfully and the other fails with error
Message
Executed as user: NAdbasvc-alticor. ... 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:28:25 AM Error: 2007-07-10 11:28:26.21 Code: 0xC020838F Source: Staging LKPDataReader Source [1] Description: An error occurred executing the provided SQL command. End Error Error: 2007-07-10 11:28:26.21 Code: 0xC004706B Source: Staging DTS.Pipeline Description: "component "LKPDataReader Source" (1)" failed validation and returned validation status "VS_ISBROKEN". End Error Error: 2007-07-10 11:28:26.21 Code: 0xC004700C Source: Staging DTS.Pipeline Description: One or more component failed validation. End Error Error: 2007-07-10 11:28:26.21 Code: 0xC0024107 Source: Staging Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 11:28:25 AM Finished: 11:28:26 AM Elapsed: 0.906 seconds. The package execution fai... The step failed.
I understand that it is something to do with
1.protection level
2.the user account under which the package is running
3.the SQL agent account used to run job
can someone be a detailed in explaining how to get this running and one big ? is why one package fails while the other with exactly same settings is running perfectly.
Please help i bid a lot of google on this but still confusing
View 3 Replies
View Related
Dec 14, 2007
I got a foglight alert that sql agent is down on one sql 2000 server
But now sql services are up and running fine.
How do i investigate that why sql agent was down
Please advice?
View 5 Replies
View Related
Jan 31, 2008
I have successfully created a DTS package, and would like to use SQL Server Job Agent to schedule it (instead of right-clicking on the package and scheduling a plan because the dates that I need to run this package are not fixed). How would I proceed? I am using SQL Server 2000.
Thanks...
View 2 Replies
View Related
Mar 15, 2008
A scheduled job use a SELECT query to check a record in a table,
the query returns 1 or 0.
how to let the job agent know if the return values is 1 then go next step and stop checking,
if return values is 0 then keep try for every 30 minutes for 10 times.
thanks
View 1 Replies
View Related
Aug 17, 2006
Hi all,
I am using SQL Server Express with Advanced features. I used to use the SQL agent in SQL Server 2000 to run a stored procedure that populated a table in my database each night.
I cant seem to do this with SQL Server Express. Is there another way to run this stored procedure each night and populate the table with the data returned.
I read about using Scheduled Tasks in Windows but Im not really familar with this.
Would really appreciate any ideas.
Thanks,
Cathie
View 4 Replies
View Related
Mar 15, 2008
A scheduled job use a SELECT query to check a record in a table,
the query returns 1 or 0.
how to let the job agent know if the return values is 1 then go next step and stop checking,
if return values is 0 then keep try for every 30 minutes for 10 times.
thanks
View 10 Replies
View Related
Dec 15, 2007
I got a foglight alert that sql agent is down on one sql 2000 server
But now sql services are up and running fine.
How do i investigate that why sql agent was down
Please advice?
View 8 Replies
View Related
Dec 18, 2006
Hello everyone !!!
I need to do a process that run every day to update some data, but my sql server version (express 2005) does not have job agent service. Does anyone can give me a suggestion of what to do?
Thank you!
View 2 Replies
View Related
May 11, 2007
Hi
I have a .Net 2.0 application written in VB.Net and I am programatically trying to create a SQL Agent Job, so a user can:
1) Add SQL Statement to job2) Add Schedule to job
Im not sure how you do this or what namespace to use, i would be greate for any help
Many thanks in advance
View 2 Replies
View Related
Mar 14, 2006
Hi there,
was wondering if any can tell me why the following jobs keeps failing:
INSERT INTO TblActivityBookingArchive SELECT * FROM TblActivityBooking WHERE (expiryDate <= GETDATE())
it retuens that it is successful when there is no data to archive from TblActivityBooking, it's when there is data to archive that it returns that the job has failed.
Hope you can help
Thanks
Mel
View 4 Replies
View Related
Feb 12, 2002
The SQLAgent service was modified to login as a domain account. SQLAgent would not start. I made sure that the account was in the Admin group on the server, I setup the account as a System Administrator in SQL. I made sure that the network protocols in Client Network Utiliity and Server Network Utility where the same as other servers using the domain account. The message I'm getting is...
SQLServerAgent could not be started (reason: Unable to connect to server; SQLServerAgent cannot start).
I modified the properties for SQLAgent back to login as local account and I still get the same problem.
Any suggestions would be appreciated.
Sidney Ives
Database Administrator
Sentara Healthcare.
View 4 Replies
View Related
Jul 29, 2002
I am unable to start SQL Server Agent. When I try, I get this message: "An Error 1053 - (The service did not respond to the stort or control request in a timely fashion) occurred while performing this service operation on the SqlServerAgent service."
View 1 Replies
View Related
Aug 7, 2002
Is it possible to schedule a job to automatically send e-mails based on criteria in a database table, maybe using CDONTS? I don't want to use SQLMail, since my administrator has not set that up. Also, I don't want to write the code in an ASP page for the following reason: If the page is not being viewed at the time I want the e-mail to be sent, it won't work. Besides, it's not efficient to have to read this code for no reason every time a page is loaded in the hopes someone loads it at the correct time. Any suggestions?
View 1 Replies
View Related
Mar 20, 2001
We received the error messages below (in the SQLAgent Log) when trying to execute a SQL Job manually. These errors do not appear if the job is executed automatically, and consequently runs smoothly. Only when we try to kick off the job manually does it fail and report these errors.
3/20/01 4:27:16 AM - ! [000] Password verification of the 'SQLAgentCmdExec' proxy account failed (reason: A required privilege is not held by the client)
3/20/01 4:27:16 AM - ! [000] Password verification of the 'SQLAgentCmdExec' proxy account failed (reason: A required privilege is not held by the client)
If anyone has any insight into this issue it would be greatly appreciated.
Thank you,
Nick
View 3 Replies
View Related
Jun 6, 2001
I have a series of jobs in the SQL server Agent that will begin and appear to run fine, but will never finish. Is there a way to limit the time that a job will run in the agent, for instance stop with failure if not completed within 1 hour?
Thanks for the help
Ken
View 1 Replies
View Related
Aug 21, 2001
Hello everyone,
Any ideas of how to start a SQL Agent job, from OTHER-THAN Enterprise Manager !!!
Does Microsoft provide any stored procs, extended procs or any SDK that can help me force-start a scheduled task ?
There has got to be a way, coz' Enterprise Manager is doing it !!!!..
Any help is greately appreciated.
Thanks..
View 3 Replies
View Related
Jul 21, 2000
I am configuring a new Server running SQL Server 7.0 sp2.
I have a job that runs a large process. From the SQL Agent job, the processing time is over 8 hours. If I run the same process from a query window, it takes an hour and a half. On my 6.5 database, the job takes 2 - 3 hours from the scheduled task. What is going on with SQL Agent? I have not been able to find any information on memory and SQL Agent.
Any help would be greatly appreciated!
Thanks
Trina Blazek
View 2 Replies
View Related
Aug 12, 2000
Hi
My sql server agent is not starting and whenever I try to start a job it gives me an error saying that job csnnot be started as sql server agent is not running...and it open a black window(with title bar d:mssql7insqlagent.exe)..can anyone tell me the reason for that and how do I get around this problem.
Thanks
VENU
View 1 Replies
View Related
Apr 5, 2000
I want to give a couple users the ability to run Agent Jobs without adding them to the sysadmin server role. I don't see another role that would provide for this function. Any ideas?
Thanks,
Dan
View 1 Replies
View Related
Nov 13, 2000
I have a problem where developers will stop the SQLServer service during the day and then they will not remember to restart the SQLAgent. It does not seem to restart when you stop the Server service and restart the services with out a reboot. Is there a setting somewhere in Enterprise Manager where I can tell the Agent service to restart with the Service service?
View 4 Replies
View Related
