Kill all SPID's in a database

One way to kill the the connections in a database is by setting it to 'offline' mode with rollback immediate and then setting it back to 'online' mode. The rollback immediate clause kill all the connections by force.

use master

go

alter database dbname set offline with rollback immediate

go

alter database dbname set online

go

Another way of killing all the connections to a database is by identifying all the SPID's in that database and killing all of them. It can be done using a simple script below. Just use the appropriate database name in place of 'dbname' below.

use master

go

set quoted_identifier off

declare @cmd nvarchar(200)

declare @spid varchar(100)

declare varcursor cursor for

select spid from master..sysprocesses where db_name(dbid)='dbname'

open varcursor

fetch next from varcursor into @spid

while (@@fetch_status=0)

begin

select @cmd= 

"USE [master]

kill "+@spid+"

"

print @cmd

exec sp_executesql @cmd

fetch next from varcursor into @spid

end

close varcursor

deallocate varcursor

Compare SQL Server 'Max Server Memory' with the Physical Server Memory of the Windows Server

Ideally, the SQL Server's max server memory settings should be configured at 80% of the total phyical memory of the Windows Server. That will ensure the OS has enough memory to run all the other processes besides the SQL. If the SQL Server's max server memory is assigned to a value higher than 80% of the total server memory, SQL captures all the memory within its limit when needed and the OS starts feeling the pressuere. If you have any monitoring tools to page you when the available server memory falls below 5%, we will get these annoying alerts.

All that is needed in this case is properly configure the SQL's max server memory to not more than 80% of the server memory. What if you needed to run this check across all the Instances in your company? The below query will give you a quick snapshot of those details in terms of percentage. This query captures the SQL settings from the system table called 'sys.configurations' and physical server memory details from the dmv called 'sys.odm_os_sys_info'. Run this query in Central Management Server and it will give you some valuable information.

Use the below query for SQL 2005, 2008, 2008 R2:

declare @mem1 int

declare @mem2 int

select @mem1 = (select (physical_memory_in_bytes/1048576) as server_memory_in_MB from sys.dm_os_sys_info)

select @mem2 =(select convert(decimal,value) from sys.configurations where name='max server memory (MB)')

select @mem2 as SQL_Max_Memory, @mem1 as Physical_Server_Memory,

cast((cast(@mem2 as float)/ cast(@mem1 as float)) as decimal(4,2))*100 as SQL_Physical_memory_ratio

For SQL 2012 and above use the below query:

declare @mem1 int
declare @mem2 int
select @mem1 = (select (physical_memory_kb/1024) as server_memory_in_MB from sys.dm_os_sys_info)
select @mem2 =(select convert(decimal,value) from sys.configurations where name='max server memory (MB)')
select @mem2 as SQL_Max_Memory, @mem1 as Physical_Server_Memory,
cast((cast(@mem2 as float)/ cast(@mem1 as float)) as decimal(4,2))*100 as SQL_Physical_memory_ratio

Identify the versions & editions of all SQL Instances in a company

What if you are assigned to a task of identifying the versions & editions of each and every SQL Instance in the company? Your job has just been made easier with the script below! Run the below script on a CMS (Central Management Server) and you should have a neatly formatted result set.

set nocount on
declare @version varchar (10)
declare @servername varchar(250)
declare @ver varchar(10)
set @servername=(select @@servername)
set @version = substring(cast(SERVERPROPERTY('productversion') as varchar(100)),0,CHARINDEX('.',(cast(SERVERPROPERTY('productversion') as varchar(100))))+2)
set @ver=case @version
       when '8.0' then '2000'
       when '9.0' then '2005'
       when '10.0' then '2008'
       when '10.5' then '2008 R2'
       when '11.0' then '2012'
       when '12.0' then '2014'
       when '13.0' then '2016'
       end
select SERVERPROPERTY('MachineName') as Physical_Server_Name, @ver as Version, SERVERPROPERTY('productversion') as Build_Number,
SERVERPROPERTY('Edition') as Edition
set nocount off

Windows batch file to parse thru server names in a text file and execute scripts against them

DBA's often need to execute the same set of scripts against multiple instances. The job is made a lot easier by using a windows batch file that executes a sqlcmd against all the instances one after the other. All the server names can be placed in a text file and the below code parses thru the text file one after the other and executes sqlcmd against them. The output of each sqlcmd command is logged to the file 'output.txt'. '>>' is the operator that appends the output to the same text file each time sqlcmd runs rather than creating a new file and over-writing it every time.


@echo off
for /F "tokens=*" %%a in (servernames.txt) do sqlcmd -S%%a -E -i "C:\check\script.sql" >> "C:\check\output.txt" -b

Dropping user from all databases and then the login

There may be situations where DBA's get requests to drop a login. Right-click, drop login, done! But it leaves a lot of orphan users in the databases it had access to before dropping the login. In order to have a clean way of dropping the login, we need to drop the user from all the databases it exists in and then drop the login. Below is a simple script to do that.

EXEC master..sp_MSForeachdb 'USE [?] if exists (select 1 from sysusers where name=''domain\username'') drop user [domain\username]';

if exists(select 1 from master..syslogins where name='domain\username') drop login [domain\username];

It uses sp_msforeachdb to check and drop the user if it exists, finally drops the login if it exists. The same script can be used for dropping windows as well as sql logins..

SQL Server 2005 install fails while starting the services up

I was doing a SQL Server 2005 install and it fails at the end of the install when it attempts to start the SQL services. The messages in Event viewer look like the below:

Faulting application sqlservr.exe, version 2005.90.1399.0, faulting module sqlservr.exe, version 2005.90.1399.0, fault address 0x0000000000b323f0.

The application, E:\Program Files\MSSQL.1\MSSQL\Binn\sqlservr.exe, generated an application error The error occurred on 05/19/2010 @ 16:26:00.026 The exception generated was c0000005 at address 0000000001B323F0 (sqlservr!DmpGetClientExport)

Below are the steps to work around this problem:

1. At the time you get the failure to start SQL Sevrver during setup you are presented with a retry option.

2. At this time, replace the SQLSERVR.exe and SQLOS.dll in the BINN folder at your install location from another SQL instance which already has Service Pack 2 installed and then click on 'Retry'.

Immediately patch it with SP3 or the latest SP available

Search for text in any database objects

If we ever get in a situation where we need to find a particular string in a stored procedure or trigger or any other database object, use the below script:

-- SQL 2000
select * from ( SELECT o.name, d.colid, o.type, convert(varchar(4000), c.text)
+ CASE d.colid WHEN 1 THEN '' ELSE convert(varchar(4000), d.text) END as LineText
FROM syscomments c, syscomments d, sysobjects o
WHERE c.id = d.id and c.id = o.id
and ( (c.colid = d.colid - 1) or (c.colid = d.colid and d.colid=1) ) ) a
where LineText like '%string%'
order by name, colid


-- SQL 2005
select * from sys.sql_modules
where definition like '%string%'