I try to find out how many jobs where run in parallel on my server in an interval of time. For example: between 1:00 AM and 2:00 AM there were MAX 66 jobs that run in parallel and MIN 4 jobs. I am not sure if I can find this info out from a system view or I need to play with sysjobhistory view.
View 10 RepliesWhen I give job Id in filter of this query this will give job status of "Success" but actually my job is currently in executing stage. I want to get all jobs that are currently in executing status.
Use msdb
go
select distinct j.Name as "Job Name", --j.job_id,
case j.enabled
when 1 then 'Enable'
when 0 then 'Disable'
[Code] ....
I need a sql script for Jobs is running more-than 3 hours in a current server, based on a particular jobs.
View 2 Replies View RelatedHow to find the list of queries currently running in the Database with User Login Information.
Since my database application is running slow, to find the slow queries.
One of the developers here wants to run a PB script consisting of 11
inserts to 11 respective tables at the same time. This is on a development
box.
Currently, we are running on MSSQL 6.5 ,sp4. We have very general
parameters set, nothing special. We have max async io set to the default
The inserts run serially now. What I need to do is run them in parallel.
My questions are:
1) How do I do this -- run in parallel?
2) What parameters do I need to set?
3)What else would I need to do?
Any information you can provide will be greatly appeciated. Thanks.
David Spaisman
Scenario
I have a package that is doing some file transformation (Text, XML, and Excel) job based on a variable value. This package is called by a Parent package, where I am calling this package parallel through a script Task. So there are three parallel script task and all variables are local to script task.
In Script Task I am assigning value to child package variable using following code.
packToLaunch = appLaunch.LoadPackage("SSISPackage.dtsx", Nothing)
For iVarCtr = 0 To Dts.Variables.Count - 1
VarName = Dts.Variables(iVarCtr).Name
packToLaunch.Variables(VarName).Value = Dts.Variables(VarName).Value
Next
Dts.TaskResult = packToLaunch.Execute()
Problem1
Out of three packages some are getting failed saying variables not found in collection.
To read a variable in child package I am setting it to ReadOnlyVariables List of Script Task.
In some places I am using following code
object objValue = null;
Microsoft.SqlServer.Dts.Runtime.Variables variables = null;
if (varDispenser.Contains(variableName))
{
varDispenser.LockOneForRead(variableName, ref variables);
objValue = variables[variableName].Value;
if (variables.Locked) variables.Unlock();
}
return objValue;
Problem2
I have created a hash table in child package and stored in a package variable through a script task.
When trying to access the variable through a custom task, in some of threads it€™s returning me System.Object not System.HashTable.
Both the problems are working fine on a single processor machine, it starts giving errors on multi processor 64 bit machine.
Need Urgent Help!!!!!
Hi
I have a SQL Server 2000 instance running on a Windows Server 2003 box with 4 processors. SQL Server is configured to use all 4 processors, and use all available processors for parallelism.
I have created a simple DTS package which has 2 "execute external process" tasks with no precedence constraints between them. There are no connections required or defined for the two tasks (sequential
processing is forced on tasks sharing connections). The DTS package
properties have the "limit the number of tasks to execute in parallel"
set to 4.
However, despite the above configuration, the two steps are never executed in parallel, but always sequentially.
Does anyone have any ideas as to why these tasks are not being executed in parallel?
Any suggestions welcome.
Thanks.
Hi all,
Does an UPDATE statment lock the entire table or just the rows that will be affected by the UPDATE?
I ask because -
Can I run UPDATE statements in parallel on the same table on the same column. The need for doing this is because the table is a large fact table. I plan to execute the same UPDATE statements on different time sections of the data to expedite the processing.
If the UPDATE statment lock the entire table then I cannot run an UPDATE in parallel. If the UPDATE statement just locks the rows that will be affected then maybe I can because rows affected will be different for each UPDATE.
Let me know.
Thanks,
Vivek
In my application code I am trying to invoke multiple threads in which each thread is loading an instance of the same SSIS package and would initialize the package variables with different values and execute the different instances in parallel. In each thread - after the package execution has completed successfully - I read that instance's SSIS package variables to get result information from that Instance run.
When I load the same package in different thread using LoadFromSqlServer() method
- does the code create multiple instances of the SSIS package and load the distinct instances in each of the thread
- Will the Package Execution ID be different for the different instances?
- Are the package level variables instance safe?
I have 3 requests to do and all 3 are below in SQL Â , i am planning to keep them as job and run on regular intervals as per request ..
1.Poll the contract approval table for no prepared new contracts.  Check every 30 minutes from 9am to 10pm – 7 days a week.
IF NOT EXISTS (SELECT * FROM ContractApproval WITH (NOLOCK) WHERE ContractPrePared>DATEADD(MINUTE, -30, GETDATE()))
BEGINÂ
SEND DBMAIL ---i have this part working alreadyÂ
ENDÂ
Â
2.Poll the contract approval table for no new signed contracts.  Check every 30 minutes from 9am to 10pm – 7 days a week.
 IF NOT EXISTS (SELECT * FROM ContractApproval  WITH (NOLOCK) WHERE DateSigned>DATEADD(MINUTE, -30, GETDATE())
BEGINÂ
SEND DBMAIL ---i have this part working alreadyÂ
ENDÂ
3.Poll the lead table for no new leads. Â Check every 5 minutes. Â 24/7.
  IF NOT EXISTS (SELECT *    FROM  Lead WITH ( NOLOCK )    WHERE  DateCreated  > DATEADD(MINUTE, -5, GETDATE()))
BEGINÂ
SEND DBMAIL ---i have this part working alreadyÂ
ENDÂ
I am planning to keep 1,2,3, in seperate sp's and run them as JOBS....how can i achieve below a,b ?
a.The procedures should accept a parameter that indicates the interval, in minutes, that they should look back
b.Each step in 3 jobs should invoke the procedures, specifying the interval with which the job runs at
I'm a bit confused about how ssis handles concurrent package runs
let's say I'm running this package and I 've got 3 variables set in it
VARA
VARB
VARC
and by default they are all set to 0
if I run
dtexec /File "C:ControlRoom.dtsx" /SET PackageVersion_Builder.Variables[VARA].Value;1
dtexec /File "C:ControlRoom.dtsx" /SET PackageVersion_Builder.Variables[VARB].Value;1
dtexec /File "C:ControlRoom.dtsx" /SET PackageVersion_Builder.Variables[VARC].Value;1
I'm expecting to run 3 isloated version of the package with in
first version
VARA=1
VARB=0
VARC=0
second version
VARA=0
VARB=1
VARC=0
third version
VARA=0
VARB=0
VARC=1
but it doesn't seem like doing that the maxconcurrent variable is set to 40 to be on the safe side.
when I run I get
first version
VARA=1
VARB=0
VARC=0
second version
VARA=1
VARB=1
VARC=0
third version
VARA=0
VARB=1
VARC=1
any ideas?
Thanks
I have a simple table as shown:

I want to have values on the last column to represent the time interval between the 2 date columns (visits); i.e for event-ID 2 for example, I will have
entry(EventID = 2) Â - exit(EventID = 1), and so on
I have a system of SSIS packages in which several packages perform the same lookup on the same table. E.g., i have PackageA, PackageB and PackageC all doing a lookup on TableA. All of these packages are spawned by the same PackageD and run frequently. In some cases, there is an issue with concurrency on these lookups. I get the following exception :
"
The ProcessInput method on component "LKP Lookup SecurityID" (6658) failed with error code 0xC004702C. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
"
The hex code of this exception corresponds to the following description : "DTS_E_BUFFERNOTLOCKED. This buffer is not locked and cannot be manipulated." That's as much as i could find on this.
My suspision is that the SSIS engine somehow figures that the lookup in these distinct packages is the same one and builds a shared version of the lookup table in memory. Then there is some sort of a multi-threading issue in accessing this shared memory which leads to the exception above.
Has anyone experienced this? Can someone shed some light on this?
Thanks a lot
-Alex
I'm trying to create a report which will give me a break down of how many unique vehicles have been seen between two dates via a 15 minute interval and what Lane they were seen. My current script looks like this
SELECT l.Name [Name], count(l.Name) Total, p.Created
FROM PlateReads p
inner join Lanes l on p.Lane_ID = l.ID
where LicencePlate in (Select Plate from LPRnet_MelAir_C.dbo.TempVehiclePlates)
group by Name
Name being the Lane they were in and the Total being the amount of times a unique vehicle has been seen and p.Created being the date they were seen (thats what I need the interval powered off)
Ideally the output would look like this
16/03/201408:00 to 08:15Bus Lane 15
16/03/201408:00 to 08:15Elevated Road150
16/03/201408:00 to 08:15Public Pickup75
16/03/201408:15 to 08:30Bus Lane 13
16/03/201408:15 to 08:30Elevated Road120
16/03/201408:15 to 08:30Public Pickup55
All the way to 12/04/2014
I’ve got it so it says Lane and Count just can’t get the interval part
i want to show data Party Name and Time interval wise. here is my table from where i will fetch data. so pasting table data here.
Call start Call duration Ring duration Direction Is_Internal Continuation Party1Name Park_Time
------------------------- ---------------- ------------- --------- ----------- ------------ --------------- -----------
2015/06/08 08:06:08 00:02:28 2 I 0 0 Emily 0
2015/06/08 08:16:38 00:00:21 0 I 0 1 Line 2.0 0
2015/06/08 08:16:38 00:04:13 5 I 0 0 Jen 0
[code]...
now i am not being able to cross join this CTE with my table to get data party name wise and time interval wise. say for if no data exist for a specific time interval then it will show 0 but each party name should repeat for time interval 9:00:00 - 9:30:00 upto 17:30:00. i like to add what filter need to apply to get data for incoming, outgoing, call transfer and miss call.
For Incoming data calculation
where direction='I' and
Is_Internal=0 and continuation=0 and
RIGHT(convert(varchar,[call duration]),8)<> '00:00:00'
For outgoing data calculation
[code]...
I have about 30 different reports that I want to pull into a dashboard. I need to make sure that they don't execute in serial to get good performance.
There are two ways I can approach it
1) I can create a stored procedure for each report and then make sync calls for each of the reports from the web site. So, basically this will be controlled from the web end.
2) I can do this from the SQL Server database, if there is someway to execute these stored procedures in parallel.
Hi,
I have stumbled on a problem with running a large number of SSIS packages in parallel, using the €śdtexec€? command from inside an SQL Server job.
I€™ve described the environment, the goal and the problem below. Sorry if it€™s a bit too long, but I tried to be as clear as possible.
The environment:
Windows Server 2003 Enterprise x64 Edition, SQL Server 2005 32bit Enterprise Edition SP2.
The goal:
We have a large number of text files that we€™re loading into a staging area of a data warehouse (based on SQL Server 2k5, as said above).
We have one €śmain€? SSIS package that takes a list of files to load from an XML file, loops through that list and for each file in the list starts an SSIS package by using €śdtexec€? command. The command is started asynchronously by using system.diagnostics.process.start() method. This means that a large number of SSIS packages are started in parallel. These packages perform the actual loading (with BULK insert).
I have successfully run the loading process from the command prompt (using the dtexec command to start the main package) a number of times.
In order to move the loading to a production environment and schedule it, we have set up an SQL Server Agent job. We€™ve created a proxy user with the necessary rights (the same user that runs the job from command prompt), created an the SQL Agent job (there is one step of type €ścmdexec€? that runs the €śmain€? SSIS package with the €śdtexec€? command).
If the input XML file for the main package contains a small number of files (for example 10), the SQL Server Agent job works fine €“ the SSIS packages are started in parallel and they finish work successfully.
The problem:
When the number of the concurrently started SSIS packages gets too big, the packages start to fail. When a large number of SSIS package executions are already taking place, the new dtexec commands fail after 0 seconds of work with an empty error message.
Please bear in mind that the same loading still works perfectly from command prompt on the same server with the same user. It only fails when run from the SQL Agent Job.
I€™ve tried to understand the limit, when do the packages start to fail, and I believe that the threshold is 80 parallel executions (I understand that it might not be desirable to start so many SSIS packages at once, but I€™d like to do it despite this).
Additional information:
The dtexec utility provides an error message where the package variables are shown and the fact that the package ran 0 seconds, but the €śMessage€? is empty (€śMessage: €ś).
Turning the logging on in all the packages does not provide an error message either, just a lot of run-time information.
The try-catch block around the process.start() script in the main package€™s script task also does not reveal any errors.
I€™ve increased the €śmax worker threads€? number for the cmdexec subsystem in the msdb.dbo.syssubsystems table to a safely high number and restarted the SQL Server, but this had no effect either.
The request:
Can anyone give ideas what could be the cause of the problem?
If you have any ideas about how to further debug the problem, they are also very welcome.
Thanks in advance!
Eero Ringmäe
Hi,
I'm using SQL SERVER 7.0. I'm wondering if it is possible to start a job with TSQL from within Query Analyzer?
Thanks,
Darrin Wilkinson
This is the first time, I am creating a job in SQL server 2000. I have to create few jobs, which can restore the databases at midnight, daily, on the server. We use lite speed backup sofware to take the backup and backup is stored at different location. We have a lite speed script, which we use to restore the backup.
In Job step I have wrote down the script and scheduled the job. But when I try to test the job It never runs. I do not know if something else I am supposed to do after creating a job, even I tried to run it manually it doesnot work.
Please help !!
I need to know if there is any way to extract all our jobs/dts packages
where the 'sa' password is being used in either the job steps or any stored proc the sa pwd harcoded in it.
I have a backup job which runs on sqll server 2000. About every two weekes the job doesn't complete. I t doesn't fail but just stays at executing until it's stopped. I t then won't run again until the server is rebooted. Anyone any ideas on what might course this. The database is approx 1.5 gig. It backs up between 5 and 8 hours. Normally there should be none or very little access to the database whilst it is backing up. The db is used for an intranet application.Any help much appreciated!Cheers
View 6 Replies View RelatedJobs not running within SQL Server Agent on SQL7 despite being schedulled. The only explanation
I can think of for causing this is due to a date correction on the NT Server ( backwards ) by the NT Administrator.
I can run jobs manually, but the scheduled run times are being ignored.
I'm hoping that the jobs will start running again as schedulled when the system catches up to the date it was
previously at.
Has anyone had experiences of this and found a way to get things running again ?
I have a DTS package that exports data to a file, then run Win32 process pkzip to compress it to make it 4MB from 30MB so it can be emailed.
As this is to be run daily, I scheduled it, i.e. created a Job.
When running as a Job it fails in the pkzip section. The sqlservice account is not an NT system account for security reasons.
any ideas ?
TIA
Neil.
We have DTS Packages that are scheduled to run nightly and show up in EM under JOBS under SQL Server Agent. A password got changed and some of the nightly jobs blew up.
Went into the Packages and entered the new password and successfully executed the packages BUT when I go back to jobs under the SQL Server Agent, the jobs STILL will not run - they fail with a login failure - as if i never fixed the DTS package. HELP
hi every 1,
i am having a real tough time to figure this one out .
i need to find out what jobs are schedule in the server that sends data out to ever .
we are doing the migration from 2000 to 2005 and need to know which jobs send data to the another server .
i looked at the sysjobs_view , it tells the origination server and all the jobs that are running on it, but that's pretty much it.
on the sql agent , i see all the jobs but dint know which jobs are sending data out side the server .
some jobs are regular backup
maintenance jobs
how will I figure out which once are gong out .
hope you people can help . any help will be so much appreciated.
bobby
HI Guys.
i cannt' understand what's going on the server.. actually i have created a two jobs for full backup for my databases.. One is running perfect but one is giving me following error.
Can any one tells me where i m doing mistake.
Message
Executed as user: Domainuser. Cannot open backup device 'Database_Full(\192.0.0.1BackupsFULL_Backupsdatabase_Full.BAK)'. Operating system error 53(error not found). [SQLSTATE 42000] (Error 3201) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
Hi all,
View 1 Replies View RelatedHi,
I have SQLServer 6.5 SP5a update running on Windows NT 4.0 SP6
with 4 gig RAM and 4 processor.
Suddenly the SQL 6.5 jobs running on the production server started running very very slow. A job that suppose to run in 30 minutes are running like 2 hours and completing successfully.
(I suspect the after the Norton Anti virus automatic live update may be the reason but not the Second Vulnerability as mentioned by Microsoft Bulletin last week)
I check the SQLServer, ran the performance monitor, checked pagefiles, disk space, databases,memory, tempdb. Everything seems to be normal.
I rebooted the server, checked any other process making that slow. But no use.
Please help me out with this issue as this is a production and the CRM applications from the clients uses the database server.
Thanks in advance,
Anu
I've got the stored procedure which first creates and then starts a job.
This stored procedure can be invoked from a number of triggers (after insert, after update)
The problem is:
when a sequence of statements is being executed one by one (like an insert immediately followed by several updates) and the stored procedure is invoked from the propper insert or update trigger once for each statement, the jobs are created in the right order (first for insert statement, then for updates in the order of the initial statements), but are executed in the wrong order.
Is there any explanation to this? And any solution?
The order of such jobs execution is vital for my application.
Thanx a lot in advance.
Please see the enclosed screenshot for an example list of jobs. Name of the job contains time of it's creation and another column shows the time the job was executed.
I'm trying to find out if there is a way within sql server or via script of emailing when a particular job or jobs has over run?
View 1 Replies View RelatedI’m trying to write a script that will detect long running agent jobs.
Having looked at this article:
http://www.databasejournal.com/features/mssql/article.php/3500276
It appears that agent job job id’s don’t necessarily get stored in the programname of the sysprocesses table. This is true if the agent executes an os command. It also appears that job steps do not get stored in the sysjobhistory until the step is complete so that cannot be used accurately.
Does anyone know of an effective way to find if there are long running jobs other than these methods?
Hi,
Is it possible to make jobs visible to people other than the job owner?
Can jobs run under non-sysadmin account?
Thanks,
Gulden
Hi,
I'm trying to configure mirroring with High Availability, Automatic Failover.
I know that all the jobs and maintenance plans need to be copied to the mirror server, and enabled if a mirror database takes over the principal role.
I wonder if it is a good solution to have all agent jobs on the Witness server (no jobs on principal and mirror). And all the jobs select the server where they should run (depending on current role).
One of the advantages of this approach would be that the jobs have to be created only once on the witness.
Will this solution work? What are the downsides of it?
Thanks,
Anna Sibirtseva.