MSSQL Operational Cheat Sheet

SQL Server Cheat Sheet for common operational MS SQL DBA tasks. Includes SQL SPIDs, BCP, database files, SQL Agent, and test databases/tables. Examples here are random.

– Kill SPIDs
– Shrink DB in Chunks
– Move Temp DB Files
– BCP (xp_cmdshell) Example
– Create Test Database & Tables
– Batch Table Deletions
– Enable/Disable SQL Agent Jobs

Kill SPIDs

To kill a specific SQL Server SPID, you can use the following query:

-- Kill SPID MS SQL
KILL {SPID}

Replace {SPID} with the ID of the SPID you want to kill.

To kill all SPIDs for a specific database in SQL Server, you can use the following script. Replace {database_name} with the name of the database for which you want to kill all SPIDs.

-- This script will kill all SPIDs for a specific database in SQL Server
-- Declare the name of the database we want to kill SPIDs for
DECLARE @dbname NVARCHAR(MAX) = '{database_name}';

-- Declare a table variable to hold the IDs of the SPIDs we want to kill
DECLARE @spids TABLE (spid INT);

-- Insert the IDs of all SPIDs for the specified database into the @spids table
INSERT INTO @spids
SELECT spid
FROM sys.sysprocesses
WHERE dbid = DB_ID(@dbname)

-- Declare a variable to hold the ID of the current SPID we are processing
DECLAR @spid INT;

-- Loop through the @spids table and kill each SPID one at a time
WHILE EXISTS (SELECT * FROM @spids)
BEGIN
    -- Get the ID of the next SPID to be killed
    SELECT TOP 1 @spid = spid FROM @spids;

    -- Kill the SPID
    EXEC ('KILL ' + @spid);

    -- Remove the SPID from the @spids table
    DELETE FROM @spids WHERE spid = @spid;
END

These comments provide some additional information and context for the code, which can make it easier to understand and use.

Shrink DB in Chunks

BCP (xp_cmdshell) Example

open a command prompt and navigate to the directory where the bcp utility is installed (usually C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn). Then, run the following command to copy the Employees table from the SourceDB database to the DestinationDB database:

bcp DestinationDB.dbo.Employees in SourceDB.dbo.Employees -S <server_name> -U <username> -P <password>

Common arguments used with BCP:

Argument	Description
-S	Specifies the name of the SQL Server instance to which to connect.
-d	Specifies the name of the database in which the data will be imported or exported.
-U	Specifies the login ID to be used when connecting to the SQL Server instance.
-P	Specifies the password to be used when connecting to the SQL Server instance.
-T	Specifies that BCP should connect to the SQL Server instance using a trusted connection.
-q	Specifies that BCP should use a quoted-identifier style for character fields.
-c	Specifies that the data in the file is character data and should be imported or exported using the native character data type.
-t	Specifies the field terminator to use in the data file.
-r	Specifies the row terminator to use in the data file.

Create Test Database

Here is an example of creating a test SQL Server database with three tables and inserting three rows of data into each table.

-- Create a database named 'CheeseDB'
CREATE DATABASE CheeseDB;

-- Use the CheeseDB database
USE CheeseDB;

-- Create a table named 'CheeseTypes'
CREATE TABLE CheeseTypes (
  ID INT PRIMARY KEY,
  Name VARCHAR(255)
);

-- Insert 3 rows of data into the CheeseTypes table
INSERT INTO CheeseTypes (ID, Name)
VALUES (1, 'Cheddar'), (2, 'Gouda'), (3, 'Brie');

-- Create a table named 'CheeseBrands'
CREATE TABLE CheeseBrands (
  ID INT PRIMARY KEY,
  Name VARCHAR(255)
);

-- Insert 3 rows of data into the CheeseBrands table
INSERT INTO CheeseBrands (ID, Name)
VALUES (1, 'Kraft'), (2, 'Sargento'), (3, 'BelGioioso');

-- Create a table named 'CheeseInventory'
CREATE TABLE CheeseInventory (
  ID INT PRIMARY KEY,
  CheeseTypeID INT,
  CheeseBrandID INT,
  Quantity INT,
  FOREIGN KEY (CheeseTypeID) REFERENCES CheeseTypes(ID),
  FOREIGN KEY (CheeseBrandID) REFERENCES CheeseBrands(ID)
);

-- Insert 3 rows of data into the CheeseInventory table
INSERT INTO CheeseInventory (ID, CheeseTypeID, CheeseBrandID, Quantity)
VALUES (1, 1, 1, 100), (2, 2, 2, 200), (3, 3, 3, 300);

To add two indexes to the tables in our example database, we can use these SQL statements:

-- Add an index to the Name column in the CheeseTypes table
CREATE INDEX CheeseTypes_Name_Index ON CheeseTypes (Name);

-- Add an index to the Name column in the CheeseBrands table
CREATE INDEX CheeseBrands_Name_Index ON CheeseBrands (Name);

We’re adding an index to the Name column in the CheeseTypes table and another index to the Name column in the CheeseBrands table. These indexes will help make queries that search for specific names in these tables faster.

Batch SQL Table Deletions

Deleting a lot of data from a table can take a long time and use up a lot of resources, especially if the table is huge and has a lot of indexes or foreign key constraints. To avoid slow performance, it’s often better to delete the data in smaller batches instead of all at once.

-- Create test table
CREATE TABLE test_table (
  id INT PRIMARY KEY,
  name VARCHAR(120),
  test_date DATETIME
);
-- Insert test rows, one for each month
INSERT INTO test_table (id, name, test_date)
VALUES
  (1, 'Stan', '2022-01-01'),
  (2, 'Kyle', '2022-02-01'),
  (3, 'Eric', '2022-03-01'),
  (4, 'Kenny', '2022-04-01'),
  (5, 'Butters', '2022-05-01'),
  (6, 'Wendy', '2022-06-01'),
  (7, 'Bebe', '2022-07-01'),
  (8, 'Clyde', '2022-08-01'),
  (9, 'Jimmy', '2022-09-01'),
  (10, 'Token', '2022-10-01'),
  (11, 'Craig', '2022-11-01'),
  (12, 'Tweek', '2022-12-01');

-- Delete all rows over 6 months old, in batches
DECLARE @BatchSize INT = 1000;
WHILE 1=1 BEGIN
  DELETE TOP (@BatchSize) FROM test_table
  WHERE test_date < DATEADD(month, -6, GETDATE())

  IF @@ROWCOUNT < @BatchSize
    BREAK;
END

Deleting data in small batches can be faster, but it can still be slow if your table has a lot of extra information (like indexes or foreign key constraints). In these cases, it might be better to turn off the extra information before deleting the data and then turn it back on afterwards. You can use special commands like DISABLE TRIGGER, DISABLE CONSTRAINT, and DROP INDEX to turn off the extra information before deleting the data, and then use ALTER INDEX and ALTER TABLE to put it back on afterwards.

Shrink DB in Chunks

Disable All SQL Agent Jobs

The following script can be used to disable all SQL Server Agent jobs. Please note that this does not delete the jobs or remove any run history, it just disables them.

-- disable all SQL Server Agent jobs
DECLARE @job_id UNIQUEIDENTIFIER
DECLARE job_cursor CURSOR FOR
    SELECT job_id
    FROM msdb.dbo.sysjobs

OPEN job_cursor
FETCH NEXT FROM job_cursor INTO @job_id

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC msdb.dbo.sp_update_job @job_id = @job_id, @enabled = 0

    FETCH NEXT FROM job_cursor INTO @job_id
END

CLOSE job_cursor
DEALLOCATE job_cursor

sp_update_job is a stored procedure in SQL Server that can be used to modify the properties of an existing job, such as its schedule or the script it runs. We can amend the @enabled parameter from 0 to 1 if we want the above script to enable rather than disable SQL Agent jobs.