Kill all current user processes connecting to database

Sometimes you may wish to sever any user connections to a database before performing some sort of action. One way to do this, is as follows, by getting a list of all SPIDs relating to a domain user, and appending KILL to the start. Run the following SQL, then copy, paste and run the output:

select 'Kill ' + cast(spid as varchar) from sys.sysprocesses

where loginame like 'DOMAIN\%'

Alternatively, as suggested by Pinal Dave in his blog post, you could set the server to Single User Mode and back to Multi User Mode as follows. This method is very quick and neat, however, this will kill ALL connections to the database, not just those from domain users:

USE master;

GO

ALTER DATABASE AdventureWorks

SET SINGLE_USER

WITH ROLLBACK IMMEDIATE;

ALTER DATABASE AdventureWorks

SET MULTI_USER;

GO

Credit for Pinal Dave blog post: http://blog.sqlauthority.com/2009/02/06/sql-server-quickest-way-to-kill-all-threads-kill-all-user-session-kill-all-processes/

Installing SQL Server Reporing Services onto a cluster

SQL Server Reporting Services (SSRS) is not cluster aware - it isn't possible for the web front end of SSRS to fail over with a cluster. It is, however, possible to install an instance of SSRS onto each node of a cluster, and then point each instance to a shared, clustered ReportServer database using the Scale Out functionality of SSRS. This option is only available in Enterprise Edition. 

However, it is not recommended to install SSRS onto a SQL Server cluster, due to competition between SQL Server and SSRS for resources etc.

More information here: http://blogs.msdn.com/b/psssql/archive/2010/05/27/reporting-services-scale-out-and-clusters.aspx

Free SQL Server eBooks

Link to lots of really good, and best of all, FREE SQL Server eBooks:

https://github.com/vhf/free-programming-books/blob/master/free-programming-books.md#sql-server

Little Bobby Tables - SQL Injection Humour

DBA lols. 

Credit: http://xkcd.com/327/

Find last time a table has been accessed

The following SQL script will give you information about the last time a table has been accessed, or updated:

select

      so.name,

      last_user_seek = max(last_user_seek),

      last_user_scan = max(last_user_scan),

      last_user_lookup = max(last_user_lookup),

      last_user_update = max(last_user_update)

from

      sys.dm_db_index_usage_stats s

inner join sysobjects so

      on s.object_id = so.id

where

      [database_id] = db_id()

group by

      so.name

Manipulating dates - get first day of current month, year etc

It's often useful to be able to return the first of the current year, month, or week, or even to strip off the time portion of a DATETIME field. One way to do this is to work out the difference between the current date and the "start of time", in the units of time you're interested in, and then add this many units of time to the "start of time".

The "start of time" in SQL Server is 1st Jan 1900. The following query returns 1st Jan 1900:

declare @dt datetime
set @dt = 0
select @dt 

To get the date and time right now, you could use:

select getdate()   

Then, to get the number of years between the "start of time" and now, you could use the following:

select datediff(year,0,getdate()) 

The query above basically says "what is the difference (datediff), in whole years (year), between 1st Jan 1900 (0) and today's date (getdate())". You can then add this number of whole years to 1st Jan 1900, to give you the 1st of Jan this year, by using the DATEADD function:

select dateadd(year,datediff(year,0,getdate()),0)

The query above can be adapted to get the first of the current month, week, or to strip off the time portion of today's date, by substituting "year" for "week" or "day" respectively:

select dateadd(week,datediff(week,0,getdate()),0)
select dateadd(day,datediff(day,0,getdate()),0)

To get the first of next month, you can find the first of this month, and add an extra month, as follows (note the "+1"):

select dateadd(month,datediff(month,0,getdate())+1,0)

Last day of this month:

select dateadd(day,-1,dateadd(month,datediff(month,0,getdate())+1,0))

Last day of last year:

select dateadd(day,-1,dateadd(year,datediff(year,0,getdate()),0))

Search content of SQL Server Agent Jobs

If you have many SQL Server Agent jobs on a server, it is useful to be able to search for a job programatically, based on it's contents, rather than manually viewing each step of each job. The following SQL allows you to search for code within all SQL jobs:

USE [msdb]
GO
SELECT     
      j.name,
      js.step_id,
      js.command,
      j.enabled,
      j.job_id
FROM  dbo.sysjobs j
JOIN  dbo.sysjobsteps js
      ON    js.job_id = j.job_id
WHERE js.command LIKE N'%Insert search string here%'
ORDER BY j.name