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

Get physical size and rowcount of all tables in database

The following script will return table name and corresponding physical size and rowcount for each table in a database:

select
    t.NAME as TableName
    ,s.Name as SchemaName
    ,p.rows as RowCounts
    ,sum(a.total_pages) * 8 as TotalSpaceKB
    ,sum(a.used_pages) * 8 as UsedSpaceKB
    ,(sum(a.total_pages) - sum(a.used_pages)) * 8 as UnusedSpaceKB
from
    sys.tables t
    inner join sys.indexes i
       on t.OBJECT_ID = i.object_id
    inner join sys.partitions p
       on i.object_id = p.OBJECT_ID
       and i.index_id = p.index_id
    inner join sys.allocation_units a
       on p.partition_id = a.container_id
    left outer join sys.schemas s
       on t.schema_id = s.schema_id
where
    t.NAME not like 'dt%'
    and t.is_ms_shipped = 0
    and i.OBJECT_ID > 255
group by
    t.Name,
    s.Name,
    p.Rows
order by
    t.Name

Credit: http://stackoverflow.com/questions/7892334/get-size-of-all-tables-in-database

Upload a Report Model to Report Server (without deploying it via BIDS)

Deploying a report model to a reporting server, it embeds the data source (.dsv) file into the report model (.smdl) file automatically. When we need to upload a single report model file to the report server, we can manually copy the entire code of the .dsv file into the .smdl file just before the </SemanticModel> attribute which is in the last line.

Change collation of database (including all objects)

Restoring a backup of a database from Server A to Server B can result in issues if the server level collations differ. The restore of the database will succeed, however, the collation of the restored database will remain as it was on Server A, and will not inherit the collation of Server B. To change the collation of the restored database to match Server B's collation, perform the following steps:

1, Change the collation at database level as follows:

alter database [DatabaseName] collate [Collation]

 2, Next, change the collation of all columns in all tables. Changing the collation of columns within the database will fail, if there are constraints enabled on any of the columns. Before changing any collations at column level, disable the constraint checking for each table by running the following:

EXEC sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL"

3, After constraint checking has been disabled, change the collation of all objects within the database, as follows. Run the following SQL. Copy and paste the output into a new window, and run:

SELECT 'ALTER TABLE [' + SYSOBJECTS.Name + '] ALTER COLUMN [' + SYSCOLUMNS.Name + '] ' +
SYSTYPES.name +
CASE systypes.NAME
WHEN 'text' THEN ' '
ELSE
'(' + RTRIM(CASE SYSCOLUMNS.length
WHEN -1 THEN 'MAX'
ELSE CONVERT(CHAR,SYSCOLUMNS.length)
END) + ') '
END
+ ' ' + ' COLLATE Latin1_General_CI_AS ' + CASE ISNULLABLE WHEN 0 THEN 'NOT NULL' ELSE 'NULL' END
FROM SYSCOLUMNS , SYSOBJECTS , SYSTYPES
WHERE SYSCOLUMNS.ID = SYSOBJECTS.ID
AND SYSOBJECTS.TYPE = 'U'
AND SYSTYPES.Xtype = SYSCOLUMNS.xtype
AND SYSCOLUMNS.COLLATION IS NOT NULL
AND NOT ( sysobjects.NAME LIKE 'sys%' )
AND NOT ( SYSTYPES.name LIKE 'sys%' )
GO

 4, Check the output to ensure the previous script completed for each column (i.e. check there were no errors). Finally, re-enable the constraint checking for each table by running the following:

EXEC sp_MSforeachtable @command1="ALTER TABLE ? CHECK CONSTRAINT ALL"