kill active sql server connections

This small script kill all the active connections of the specified database. Select 'Kill '+ CAST(p.spid AS VARCHAR)KillCommand into #temp from master.dbo.sysprocesses p (nolock) join master..sysdatabases d (nolock) on p.dbid = d.dbid Where d.[name] = 'your db name' Declare @query nvarchar(max) --Select * from #temp Select @query =STUFF(( select ' ' + KillCommand from #temp FOR XML PATH('')),1,1,'') Execute sp_executesql @query Drop table #temp U ...more

Change Tracking in Sql 2008

In this post we will discuss how to track the changes on a table. First of all we enable the tracking on database, let's say we have that database name 'Emplyee' ALTER DATABASE Employee SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON); After enabling the tracking of database we should have to enable the tracking of table. In our case it is 'tblEmployee'. ...more

View to obtain Failed Jobs from SQL Server Agent

CREATE VIEW dbo.View_Failed_Jobs AS SELECT Job.instance_id ,SysJobs.job_id ,SysJobs.name as 'JOB_NAME' ,SysJobSteps.step_name as 'STEP_NAME' ,Job.run_status ,Job.sql_message_id ,Job.sql_severity ,Job.message ,Job.exec_date ,Job.run_duration ,Job.server ,SysJobSteps.output_file_name FROM (SELECT Instance.instance_id ,DBSysJobHistory.job_id ,DBSysJobHistory.step_id ,DBSysJobHistory.sql_message_id ,DBSysJobHistory.sql_severity ,DBSysJobHistory.message ,(CA ...more

Sql Server- function to get Previous And Next Work Day

This function excludes the Weekends (Saturday and Sunday), and it gets previous as well as next work day. Script to create function to get previous and next working day. CREATE FUNCTION dbo.udf_GetPrevNextWorkDay (@dtDate DATETIME, @strPrevNext VARCHAR(10)) RETURNS DATETIME AS BEGIN DECLARE @intDay INT DECLARE @rtResult DATETIME SET @intDay = DATEPART(weekday,@dtDate) --To find Previous working day IF @strPrevNext = 'Previous' IF @intDay = 1 ...more

Important sql server tsql code snippets

To call a stored procedure with out parameters   declare @abc int exec [uspSPName] 975,'7/18/2007',@abc out print @abc   Getting date 5 days ahead or back date 5 days ahead or prior to current date select DATEADD(dd, -5, GETDATE()) and to find difference between two dates ISNULL ( DATEDIFF(dd,convert(datetime,@vFrom,105),convert(datetime, @vTo,105) + 1) ,0) as TotalDays to get time from ...more