March 17, 2010 at 6:32 am
iam facing problem in my production system that the jobs are taking longtime from past days to complete , even backup jobs are taking long time as usually it complete with in mins , as we rebooted the services and sql server too , doesnt work to solve the problem , checked in the logs does not find any error . no blockings on the server . we tried to kill the spids but unable to kill the processes. we droped and created new mainatainence paln but stil we r facing the same problem that the jobs are working showing status as running . seems processes are hanged , how to solve the problem .
replies are appreciated .
March 17, 2010 at 8:01 am
Your post is a little hard to read, so forgive me if I don't quite understand the problem.
Is it just Maint. Plans that are taking forever or is it all jobs, even jobs running T-SQL code or SSIS packages?
What edition & version & Service Pack are you running? Has anything changed with the software (service pack upgrades, new installs, increase in network traffic, etc.) recently? Check both OS and SQL Server.
March 17, 2010 at 8:26 am
If server reboot also did not solve your problem means there is some issue with the processes running on the server or with in the database which are consuming more resources.
Is there any other service than SQLServer which is taking more resources CPU/RAM etc?
If not can you please identify the statement/ command in SQL Server which is taking more resources?
March 17, 2010 at 8:58 am
we r using sql 2000 enterprise edition SP4 , we checked the memory utilisation all r fine .
March 17, 2010 at 9:07 am
Please answer the other questions so we know what you've checked and what you haven't checked. Otherwise, we can't help you further.
March 17, 2010 at 9:15 am
ramyours2003 (3/17/2010)
iam facing problem in my production system that the jobs are taking longtime from past days to complete , even backup jobs are taking long time as usually it complete with in mins , as we rebooted the services and sql server too , doesnt work to solve the problem , checked in the logs does not find any error . no blockings on the server . we tried to kill the spids but unable to kill the processes. we droped and created new mainatainence paln but stil we r facing the same problem that the jobs are working showing status as running . seems processes are hanged , how to solve the problem .replies are appreciated .
What else do you have running on the server outside of SQL Server? Have you got a virus scanner running perhaps? We just went through much the same thing you are... it turned out to be that someone thought it would be a good idea to re-enable the virus scanner that someone else had turned off.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 17, 2010 at 9:35 am
Jeff Moden (3/17/2010)
ramyours2003 (3/17/2010)
iam facing problem in my production system that the jobs are taking longtime from past days to complete , even backup jobs are taking long time as usually it complete with in mins , as we rebooted the services and sql server too , doesnt work to solve the problem , checked in the logs does not find any error . no blockings on the server . we tried to kill the spids but unable to kill the processes. we droped and created new mainatainence paln but stil we r facing the same problem that the jobs are working showing status as running . seems processes are hanged , how to solve the problem .replies are appreciated .
What else do you have running on the server outside of SQL Server? Have you got a virus scanner running perhaps? We just went through much the same thing you are... it turned out to be that someone thought it would be a good idea to re-enable the virus scanner that someone else had turned off.
Virus scanner are not bad, but definitively horrible if they start scanning SQL related files.... We have those files excluded in automatic/background/on demand scanning....
March 18, 2010 at 6:30 am
the jobs which we create dthrough maintainence palns are taking long time , and iam not sure is any update activites done in the server , if so how can we check and how can we find the virus scanned is enabled or not ? how can we check in the OS on this ?
we observerd in server settings -security - sql server priority boost is set to 20 is this factor influences in taking long time ?
pls clarify
March 18, 2010 at 10:58 am
Who is responsible for the actual server, the physical box? Who installs the OS, monitors patch updates, and might install an anti-virus program on the box?
This is the person you need to talk to. You need to find out the following information:
1) What anti-virus software (if any) is running on the box and is it scanning the .mdf, .ndf, and .ldf files (the SQL server files). If it is, ask the person in charge to exclude those file extensions from the anti-virus scanning.
2) Is the server used as a print or file or email or (other program name) server? If the server is sharing SANs or the OS with other programs, it generally creates performance problems in SQL Server.
3) Run SQL Server Profiler and see if you're having problems with I/O, network bandwidth, CPU, or if there are odd things in your maintenance plans (viewing the text of what runs might help) that is causing the issue.
4) Have the server admin check your Paging File to see if it's too small or if it keeps getting overwhelmed.
Unfortunately, the information you have given us is very limited and doesn't help us understand the issue very well, so all we can do is give you generic advice that sometimes helps and sometimes doesn't. Troubleshooting is an art that requires attention to detail, a checklist, and the process of elimination. You can start learning how to troubleshoot with the question of "What changed right before this problem occurred?"
Then ask yourself other questions (and find the answers to them). Other questions include:
Are *all* your maint. plans running a long time? Or just some of them? If the later, what tasks do these plans have in common? (There are a lot of tasks available for MPs).
What time of the day do these plans run? What other jobs or processes are running when these plans run? Do you have a baseline of how long they used to take compared to how long they are taking now? What is the difference? Five minutes last year compared to 24 hours today? What is "a long time" to you?
Are there any scheduled Windows tasks that run at the same time? Have you run PerfMon (a Windows performance monitoring tool) to see if it's the box, rather than SQL, that is having problems?
If the plans have always been running this long for as long as you've known, you may not even have a problem. Or there may have always been a problem that will take a while to diganose.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply