How to Stop SQL Server Agent Job while it is actively running?

How to Stop SQL Server Agent Job while it is actively running?

Try running sp_who2 and find the SPID and kill the process using KILL SPID with status only so that it will show the rollbackup completed as well.

Difference between KILL and KILL WITH STATUSONLY – Estimate Rollback completion time

While I was talking to a customer during a replication scenario, customer asked me a question about understanding the estimated completion time for a rollback.

I was explaining that we have KILL WITH STATUSONLY but it will just report the progress only if the SPID was killed already and it will not kill the SPID also KILL WITH STATUSONLY will not help to track the progress of ROLLBACK issued by ROLLBACK TRAN. This was something new to him that KILL WITH STATUSONLY will not kill the SPID instead it only reports the progress. 

I thought of sharing this in a blog so that it will be useful for others who overlooked the KILL command.

Here is what BOL says:

KILL WITH STATUSONLY does not terminate or roll back the session ID or UOW; the command only displays the current progress of the rollback.

Lab:

Step 1: I ran a long running batch from SPID 52 

Step 2: Issued a KILL using the command KILL 52

Step 3: Here is what I observed from SP_WHO2 52:

SPID	Status	Login	HostName	BlkBy	DBName	Command
52	SUSPENDED	abc	def	.	microsoft	KILLED/ROLLBACK

Step 4: Here is what I got when I ran KILL 52 WITH STATUSONLY:

spid 52: Transaction rollback in progress. Estimated rollback completion: 80% Estimated time left: 10 seconds.

Step 5: I tried to run the same statement from a different SPID 53 inside an explicit transaction and issued a ROLLBACK TRAN and this is the status when I ranSP_WHO2 53:

SPID	Status	Login	HostName	BlkBy	DBName	Command
53	ROLLBACK	abc	def	.	microsoft	ROLLBACK TRANSAC

Step 6: Here is the output of KILL 53 WITH STATUSONLY :

Msg 6120, Level 16, State 1, Line 1

Status report cannot be obtained. Rollback operation for Process ID 53 is not in progress.

So KILL WITH STATUS ONLY cannot be used to track the progress of  ROLLBACK issued by ROLLBACK TRAN. Hope this helps!

Reference: Sakthivel Chidambaram

Move and Rename File with File System Task in SSIS

Move and Rename File with File System Task

All Credit goes to Katie & Emil Youtube Channel

Connect to SQL Server with Windows Authentication from others Windows desktop

If you want to use your windows credentials to connect to server from your colleagues desktop in process of fixing/debugging a issue.

Try this Steps

• Press SHIFT button 
• Right CLICK on the SQL Server Management Studio button
• Select "Run as different user" and give your windows credential to connect to the server.

Change the ownership of SQL Server agent jobs

The jobs may start failing when their AD accounts are removed. If the users have left it is better to change the job owner to a different account.
To ensure such issues wont happen, it is better to create a generic AD account with only necessary permissions and use it exclusively for this purpose.

List down all the jobs which are tied to the users and share it with all the respective project managers and technical architects and get a approval .
Decide on a common user account and create new one .

Script to rename all tables Starting with "wd_" with script in SQL Server

-- using my tempdb as a sandbox...

USE tempdb

GO

CREATE TABLE wd_table01(x int)

CREATE TABLE wd_table02(x int)

CREATE TABLE wd_table03(x int)

GO

IF OBJECT_ID('tempdb..#tables') IS NOT NULL DROP TABLE #tables;

SELECT TABLE_NAME AS tbl

INTO #tables

FROM INFORMATION_SCHEMA.TABLES

WHERE table_name LIKE 'wd_%'

DECLARE @sql varchar(8000);

SELECT @sql=

(       SELECT 'exec sp_rename '''+tbl+''', '''+REPLACE(tbl,'wd_','')+''';'+CHAR(10)

        FROM #tables

        FOR XML PATH(''));

EXEC(@sql);

How do I create foreign Keyrelationship with a table in a different database?

You would need to manage the referential constraint across databases using a Trigger.

Basically you create an insert, update trigger to verify the existence of the Key in the Primary key table. If the key does not exist then revert the insert or update and then handle the exception.

Example:

Create Trigger dbo.MyTableTrigger ON dbo.MyTable, After Insert, Update
As
Begin

   If NOT Exists(select PK from OtherDB.dbo.TableName where PK in (Select FK from inserted) BEGIN
      -- Handle the Referential Error Here
   END

END

---

Edited: Just to clarify. This is not the best approach with enforcing referential integrity. Ideally you would want both tables in the same db but if that is not possible. Then the above is a potential work around for you.

Credits:John Hartsock

Enable or Disable Logins with Stored Procedure

create procedure toggle_login (@login_name nvarchar(125), @set_enable bit)
as
begin
declare @sql_command nvarchar(500)
set @sql_command = 'ALTER LOGIN [' + @login_name + '] ' + case when @set_enable = 1 then 'ENABLE' else 'DISABLE' end
print @sql_command
exec sp_executesql @sql_command
end

 
--usage of above stored procedure

exec toggle_login 'domain\loginname,0