Tech Blog – Scripts – MSSQL Operational Cheat Sheet
Heal Logins
EXEC sp_change_users_login 'Report'; EXEC sp_change_users_login 'update_one', 'loginName', 'loginName' EXEC GrantExecToReportsStoredProcs 'loginName'
Kill SPIDs
--Kill A SPID --It's Always A Risk To Kill Anything - Rollbacks May Be Painful. KILL 60; GO --KILL WITH STATUSONLY Does Not KILL- Shows The Current Progress Of The Rollback.*/ KILL 60 WITH STATUS
Kill All User SPIDs
--Kill all user SPIDs USE [master] GO DECLARE @kill VARCHAR(8000) = ''; SELECT @kill = @kill + 'kill ' + CONVERT(VARCHAR(5), spid) + ';' FROM master..sysprocesses WHERE dbid = db_id('DATABASE123') PRINT @kill --EXEC(@kill) --uncomment when ready
BCP With Xp_cmdshell
Exec Master..Xp_cmdshell 'Net Use I: "\Fileserver\Private\SQL Exports\" /USER: DOMAIN\Peter.Whyte ThisISmyPASSWORD0123!' Exec Master..Xp_cmdshell 'Bcp "Select * From [Database].[Schema].[Table]" Queryout "I:\Test.Csv" -C -T -X' Exec Master..Xp_cmdshell 'Net Use I: /Delete '
Add Primary Key Constraint
ALTER TABLE Production.TransactionHistoryArchive ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARY KEY CLUSTERED (TransactionID);
Move TempDB Files
-- Move TempDB Files. USE tempdb GO -- Get logical file names. EXEC sp_helpfile -- Move data files. ALTER DATABASE tempdb MODIFY FILE ( NAME = 'tempdev', FILENAME = 'c:\sql_temp\tempdb.ndf') ALTER DATABASE tempdb MODIFY FILE ( NAME = 'temp2', FILENAME = 'c:\sql_temp\tempdb2.ndf') ALTER DATABASE tempdb MODIFY FILE ( NAME = 'temp3', FILENAME = 'c:\sql_temp\tempdb3.ndf') ALTER DATABASE tempdb MODIFY FILE ( NAME = 'temp4', FILENAME = 'c:\sql_temp\tempd4.ndf') -- Move og files. ALTER DATABASE tempdb MODIFY FILE ( NAME = 'templog', FILENAME = 'c:\sql_temp\templog.ldf') -- Restart SQL Service. -- Verify change. -- Delete old files.
Batch Deletions
SET NOCOUNT ON DECLARE @today_floored DATETIME, @retention_period INT, @retention_date DATETIME -- Get today, clearing time to 12:00am SET @today_floored = (SELECT DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)) --+(SELECT [rollover_time] FROM [table]) -- Set retention period (in months, unless the line above is changed) SET @retention_period = 13 -- Apply retention date deduction to floored date SELECT @retention_date = DATEADD(m, -@retention_period, @today_floored) PRINT 'Deleting data < '+ CAST(@retention_date AS VARCHAR(20)) -- Delete in batches; set preferred rowcount and enter the table/column names DECLARE @BatchSize INT = 1, @total BIGINT = 0 SET rowcount 10000 WHILE @BatchSize <> 0 BEGIN DELETE [dbo].[FactResellerSalesXL_CCI] WHERE [ShipDate] < @retention_date SET @BatchSize = @@rowcount SET @total = @total + @batchsize PRINT @total END
Shrink DB In Chunks
/* This script is used to shrink a database file in increments until it reaches a target free space limit. Run this script in the database with the file to be shrunk. 1. Set @DBFileName to the name of database file to shrink. 2. Set @TargetFreeMB to the desired file free space in MB after shrink. 3. Set @ShrinkIncrementMB to the increment to shrink file by in MB 4. Run the script http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80355 Comments : - This script will help shrink database in chunks. - Remember : Shriking a database file should be done as a last practice. - http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/ - https://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/ */ declare @DBFileName sysname declare @TargetFreeMB int declare @ShrinkIncrementMB int -- Set Name of Database file to shrink set @DBFileName = 'nameOfDatabaseFileGoesHere' --<--- CHANGE HERE !! -- Set Desired file free space in MB after shrink set @TargetFreeMB = 1000 --<--- CHANGE HERE !! -- Set Increment to shrink file by in MB set @ShrinkIncrementMB = 500 --<--- CHANGE HERE !! -- Show Size, Space Used, Unused Space, and Name of all database files select [FileSizeMB] = convert(numeric(10,2),round(a.size/128.,2)), [UsedSpaceMB] = convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) , [UnusedSpaceMB] = convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) , [DBFileName] = a.name from sysfiles a declare @sql varchar(8000) declare @SizeMB int declare @UsedMB int -- Get current file size in MB select @SizeMB = size/128. from sysfiles where name = @DBFileName -- Get current space used in MB select @UsedMB = fileproperty( @DBFileName,'SpaceUsed')/128. select [StartFileSize] = @SizeMB, [StartUsedSpace] = @UsedMB, [DBFileName] = @DBFileName -- Loop until file at desired size while @SizeMB > @UsedMB+@TargetFreeMB+@ShrinkIncrementMB begin set @sql = 'dbcc shrinkfile ( '+@DBFileName+', '+ convert(varchar(20),@SizeMB-@ShrinkIncrementMB)+' ) ' print 'Start ' + @sql print 'at '+convert(varchar(30),getdate(),121) exec ( @sql ) print 'Done ' + @sql print 'at '+convert(varchar(30),getdate(),121) -- Get current file size in MB select @SizeMB = size/128. from sysfiles where name = @DBFileName -- Get current space used in MB select @UsedMB = fileproperty( @DBFileName,'SpaceUsed')/128. select [FileSize] = @SizeMB, [UsedSpace] = @UsedMB, [DBFileName] = @DBFileName end select [EndFileSize] = @SizeMB, [EndUsedSpace] = @UsedMB, [DBFileName] = @DBFileName -- Show Size, Space Used, Unused Space, and Name of all database files select [FileSizeMB] = convert(numeric(10,2),round(a.size/128.,2)), [UsedSpaceMB] = convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) , [UnusedSpaceMB] = convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) , [DBFileName] = a.name from sysfiles a
Create Database
-- Create Database with options. USE [master] GO CREATE DATABASE [funky_db] ON ( NAME = funk_dat, FILENAME = N'C:\sql\funkdata.mdf', SIZE = 100, FILEGROWTH = 50 ) LOG ON ( NAME = funk_log, FILENAME = 'C:\sql\funklog.ldf', SIZE = 100MB, FILEGROWTH = 10MB ); GO
Create Quick Table & Insert Data
CREATE DATABASE [funky_db] ON ( NAME = funkydb_data, FILENAME = 'c:\sql\funkdata.mdf' ) LOG ON ( NAME = funkydb_log, FILENAME = 'c:\sql\funklog.ldf') USE [funky_db] GO CREATE TABLE [funky_people] (firstName VARCHAR(64), surname VARCHAR(64), dateofbirth DATE, funkLevel INT) INSERT INTO [funky_people] VALUES ('funk','master','1969-01-01',100) SELECT * FROM [funky_people]
Disable All SQL Agent Jobs
USE MSDB; GO UPDATE MSDB.dbo.sysjobs SET Enabled = 0 WHERE Enabled = 1; GO