We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Course Of The Month
3 days, 14 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Troubleshoot Job that continues to run into the next day
Posted on 2011-02-24
I have a job that references an SSIS package which inserts, from a file, a bunch of data into a table with over 100 million rows. I've performance tuned the insert statement and watched it take less than 2 seconds to run for each file (sometimes it takes as much as 10 seconds for a file with a large amount of data).
I run the job at 1:00 AM and for the past week I come in the next day and the job is still running. But when I just run the job manually DURING THE DAY WHEN DB USE IS HIGHER, I watch it complete in about 1-2 hours.
I don't really have any other jobs running at night (that aren't also running during the day) and none of them are hitting that table. The only exception to that I can think of is I back up the transaction logs every 2 hours. I also have a scheduled task that moves a back up of the DB and all the transaction log backups to an external drive at 2AM. Perhaps all the disk I/O is preventing the job from running successfully and then it just gets bogged down until I manually restart it?
Question 1: should I stop the transaction log backups at night?
Question 2: where would I begin to try to figure out what could be happening at night to this job. Is there any history stored somewhere that would show what delays it seems to face at night?
I run the job at 1:00 AM and for the past week I come in the next day and the job is still running. But when I just run the job manually DURING THE DAY WHEN DB USE IS HIGHER, I watch it complete in about 1-2 hours.
I don't really have any other jobs running at night (that aren't also running during the day) and none of them are hitting that table. The only exception to that I can think of is I back up the transaction logs every 2 hours. I also have a scheduled task that moves a back up of the DB and all the transaction log backups to an external drive at 2AM. Perhaps all the disk I/O is preventing the job from running successfully and then it just gets bogged down until I manually restart it?
Question 1: should I stop the transaction log backups at night?
Question 2: where would I begin to try to figure out what could be happening at night to this job. Is there any history stored somewhere that would show what delays it seems to face at night?
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
3
2
2- +1
8 Comments
Here's what I would do:
1. Don't touch the backup jobs until you wasted all any other options
2. Check physical location of your input file for the job, database T-log, mdf/ndf files, and tempdb location. Ideally they should be all different for best I/O
3. Check the batch size of your import - should be somewhere in the SSIS step properties. Sometimes lower batch size can make it faster sometimes higher but do not go beyond 10.000 rows per batch. All these depend on the constraints/clustered indexes on the destination table.
4. You should add multiple files to tempdb - 1 per socket but no more than 4 to improve SQL performance - I'll try find the Microsoft link to support this statement.
5. Check make sure no viruscan is running against your SQL files and input file ASSUMING all data going into your DB is scanned at the app level and the input file was already checked.
1. Don't touch the backup jobs until you wasted all any other options
2. Check physical location of your input file for the job, database T-log, mdf/ndf files, and tempdb location. Ideally they should be all different for best I/O
3. Check the batch size of your import - should be somewhere in the SSIS step properties. Sometimes lower batch size can make it faster sometimes higher but do not go beyond 10.000 rows per batch. All these depend on the constraints/clustered indexes on the destination table.
4. You should add multiple files to tempdb - 1 per socket but no more than 4 to improve SQL performance - I'll try find the Microsoft link to support this statement.
5. Check make sure no viruscan is running against your SQL files and input file ASSUMING all data going into your DB is scanned at the app level and the input file was already checked.
It can be a message pop up from the SSIS during the run at night time.It might be accessing some files and getting error and there might be some script to throw the error as a message box. Or a message box due to some other reasons.
@lcohan
1,2,3,5 are good to go
4 -- I would have to double check
But the strange thing is that when I run it either manually (by right clicking on the job and starting it) or run it via the debugger in VS it runs without any issues. Granted, I have not tried to run it that at the same time as the job is normally scheduled.
1,2,3,5 are good to go
4 -- I would have to double check
But the strange thing is that when I run it either manually (by right clicking on the job and starting it) or run it via the debugger in VS it runs without any issues. Granted, I have not tried to run it that at the same time as the job is normally scheduled.
@lcohan
also, I only have 2 "drives" to split between. Data is on d:, logs on c:. Does it actually make sense to reverse that for tempdb? that way primary DB data is on d: except for tempDB which is on c: and therefore operations on primary DB that need tempDB aren't using the same drive?
also, I only have 2 "drives" to split between. Data is on d:, logs on c:. Does it actually make sense to reverse that for tempdb? that way primary DB data is on d: except for tempDB which is on c: and therefore operations on primary DB that need tempDB aren't using the same drive?
Active 7 days ago
>>I'll try find the Microsoft link to support this statement.<<
I have got it:
Myth #12: tempdb should always have one data file per processor core.
http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(1230)-tempdb-should-always-have-one-data-file-per-processor-core.aspx
I have got it:
Myth #12: tempdb should always have one data file per processor core.
http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(1230)-tempdb-should-always-have-one-data-file-per-processor-core.aspx
@lcohan and @acperkins -- does my theory, that I should actually put the tempdb and my primary DB (the one I'm inserting data into) on different drive letters. currently all mdf's on are one drive and all ldf's are on another. i'm thinking that only with tempDB I should switch that.
Active 7 days ago
>> currently all mdf's on are one drive and all ldf's are on another. <<
If you are talking about physical drives and not logical drives, I would leave it that way.
If you are talking about physical drives and not logical drives, I would leave it that way.
Featured Post
With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
I am showing a way to read/import the excel data in table using SQL server 2005...
Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name".
Now to import this Excel data into the table, we will use…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data.
I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses
Course of the Month3 days, 14 hours left to enroll
Earn Certification
Administering Microsoft SQL Server 2012 Databases (Exam 70-462)
$79.00$71.10
691 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.