Find any sql job running

I use this below querey  to find any sql job running when i see performance problem

USE MSDB

SELECT name AS [Job Name]
         ,CONVERT(VARCHAR,DATEADD(S,(run_time/10000)*60*60 /* hours */
          +((run_time - (run_time/10000) * 10000)/100) * 60 /* mins */
          + (run_time - (run_time/100) * 100)  /* secs */
           ,CONVERT(DATETIME,RTRIM(run_date),113)),100) AS [Time Run]
         ,CASE WHEN enabled=1 THEN 'Enabled'
               ELSE 'Disabled'
          END [Job Status]
         ,CASE WHEN SJH.run_status=0 THEN 'Failed'
                     WHEN SJH.run_status=1 THEN 'Succeeded'
                     WHEN SJH.run_status=2 THEN 'Retry'
                     WHEN SJH.run_status=3 THEN 'Cancelled'
               ELSE 'Unknown'
          END [Job Outcome]
FROM   sysjobhistory SJH
JOIN   sysjobs SJ
ON     SJH.job_id=sj.job_id
WHERE  step_id=0
AND    DATEADD(S,
  (run_time/10000)*60*60 /* hours */
  +((run_time - (run_time/10000) * 10000)/100) * 60 /* mins */
  + (run_time - (run_time/100) * 100)  /* secs */,
  CONVERT(DATETIME,RTRIM(run_date),113)) >= DATEADD(d,-1,GetDate())
ORDER BY name,run_date,run_time 


IF OBJECT_ID('tempdb.dbo.#RunningJobs') IS NOT NULL
      DROP TABLE #RunningJobs
CREATE TABLE #RunningJobs ( 
Job_ID UNIQUEIDENTIFIER, 
Last_Run_Date INT, 
Last_Run_Time INT, 
Next_Run_Date INT, 
Next_Run_Time INT, 
Next_Run_Schedule_ID INT, 
Requested_To_Run INT, 
Request_Source INT, 
Request_Source_ID VARCHAR(100), 
Running INT, 
Current_Step INT, 
Current_Retry_Attempt INT, 
State INT )   
    
INSERT INTO #RunningJobs EXEC master.dbo.xp_sqlagent_enum_jobs 1,garbage 

SELECT   
  name AS [Job Name]
 ,CASE WHEN next_run_date=0 THEN '[Not scheduled]' ELSE
   CONVERT(VARCHAR,DATEADD(S,(next_run_time/10000)*60*60 /* hours */
  +((next_run_time - (next_run_time/10000) * 10000)/100) * 60 /* mins */
  + (next_run_time - (next_run_time/100) * 100)  /* secs */,
  CONVERT(DATETIME,RTRIM(next_run_date),112)),100) END AS [Start Time]
FROM     #RunningJobs JSR
JOIN     msdb.dbo.sysjobs
ON       JSR.Job_ID=sysjobs.job_id
WHERE    Running=1 -- i.e. still running
ORDER BY name,next_run_date,next_run_time

Orphan users fix

Attaching and restoring databases from one server instance to another are common tasks executed by a DBA. After attaching or restoring of a database, previously created and configured logins in that database do not provide access. The most common symptoms of this problem are that the application may face login failed errors or you may get a message like the user already exists in the current database when you try to add the login to the database. This is a common scenario when performing an attach or a restore, so how do you resolve this?


 EXEC sp_change_users_login 'REPORT'---find the orphan users

--First, make sure that this is the problem. This will lists the orphaned users:

EXEC sp_change_users_login 'Report'

--If you already have a login id and password for this user, fix it by doing:
--‘Auto_Fix’ attribute will create the user in SQL Server instance if it does not exist.

EXEC sp_change_users_login 'Auto_Fix', 'user'

--If you want to create a new login id and password for this user, fix it by doing:

EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'


Please see below post ::::

How To Avoid Orphaned Database Users with SQL Server Authentication

By: Glenn Berry

Posted on: August 23, 2013 12:10 pm

One common issue that database administrators often run into is the old, familiar “orphaned” user problem. This happens when you use SQL Server Authentication to create a SQL Server login on your database server. When you do this, SQL Server generates a unique SID for that SQL Server login. After you create the SQL Server login, you typically create a database user in a user database on that server instance, and associate the database user with that SQL Server login.
This works fine until you try to restore that user database to another SQL Server instance. If you previously created a SQL Server login with the same UserID on the new server, the SID for that SQL Server login will not match the database user in the user database that you have restored (from the other database instance). Hence the term “orphaned” user.  This is an especially big issue if you are using database mirroring, since your database users will be orphans when you failover from one instance to the other instance. It is also an issue with log shipping, and it often comes up when you migrate from an old database server to a new database server.
There are several ways to fix this, but the best thing (outside of just using Windows Authentication and avoiding the problem), is to create the new server login using the same SID as on the original server. Just like you see below:

-- Get Sids for all SQL Server logins on the old server instance
SELECT name, [sid] 
FROM sys.server_principals
WHERE [type] = 's'; 

-- Create new SQL Login on new server instance
IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'SQLAppUser')
    DROP LOGIN SQLAppUser;
GO

-- Use the sid from the old server instance 
CREATE LOGIN SQLAppUser WITH PASSWORD = N'YourStrongPassword#', sid = 0x2F5B769F543973419BCEF78DE9FC1A64,
DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
GO

Performance Problem on Delete insert update : SSIS :solved

I had a SSIS package which runs multiple times


SSSI has :



Step 1:Execute SQL task:Delete from ABC table with some condition .
Step 2:Execute SQL task:Delete from XYZ table with some condition .

Then Step 3: Data flow task which has text file has Source and having various conditions and  inserting to ABC and XYZ tables

Then Step4:
Stored procedure which  Insert into another 10 tables  selecting data from ABC and XYZ based on conditions .


Here the stored procedure was taking 1 hr to complete , and some times it used to timeout calling from C# .....



So i looked at each individual Delete, Select, Inserts happening on SSIS ....
and i found there are few indexes on tables XYZ and ABC and there were lot of scans , user updates happening on indexes  and  these deletes , inserts happening longer time coz it has to delete/ insert/ update in data pages and index pages .
So
1.i dropped indexes before Delete , insert ( before step 1, 2,3 ) and
2. created the indexes, update statistics( which are not created by indexes)  before step 4 ...

This time SSIS package took 2 mins to run and i monitored for 1 week it is executing in 2mins  ...
Below Script helped me fining usage of indexes :
SELECT
    ObjectName      = object_schema_name(idx.object_id) + '.' + object_name(idx.object_id)
    ,IndexName      = idx.name
    ,IndexType      = CASE
                        WHEN is_unique = 1 THEN 'UNIQUE '
                        ELSE '' END + idx.type_desc
    ,User_Seeks     = us.user_seeks
    ,User_Scans     = us.user_scans
    ,User_Lookups   = us.user_lookups
    ,User_Updates   = us.user_updates
FROM sys.indexes idx
LEFT JOIN sys.dm_db_index_usage_stats us
    ON idx.object_id = us.object_id
    AND idx.index_id = us.index_id
    AND us.database_id = db_id()
WHERE object_schema_name(idx.object_id) != 'sys'
ORDER BY us.user_seeks + us.user_scans + us.user_lookups DESC



 Naresh ...11/26/13