Microsoft SQL Server (Notes)

From Minor Miracle Software
Jump to: navigation, search

Microsoft SQL Server - Assorted maintenance queries.


Database

Database Names and Sizes - Fetch all in an Instance

  • fetch database names and sizes on an instance

EXEC sp_databases;

Log File Shrink Script

Implement this as an SQL Server Agent Job. The owner should be the database service account but SA is used mostly. Target each step to the database, the default database is master and that will fail. This works from SQL Server 2008 to 2016.

-- Step 1
-- Backup Log File
declare
 @DBname  NVARCHAR(100),
 @Path    NVARCHAR(100)
select
@DBname = (select DB_NAME())
set @Path = 'C:\temp\' + @DBname + '_log.bak'
BACKUP LOG @DBname
TO DISK = @Path;

-- Step 2
-- Shrink Log File
declare @fileId as int = (select file_id from sys.database_files where name like '%Log')
DBCC SHRINKFILE ( @fileId )

-- Step 3
-- Delete Backup Log File
declare
 @DBname  NVARCHAR(100),
 @Path    NVARCHAR(100)
select
 @DBname = (select DB_NAME() )
set @Path = 'C:\temp\' + @DBname + '_log.bak'
EXEC master.dbo.xp_delete_file 0, @Path;

Copy database backup to a new name

-- run this on the 'master' database to get an exclusive lock
-- Do not create a blank database, just restore
 RESTORE DATABASE UAInteractionTrackerTEST FROM DISK = 'C:\temp\UAInteractionTracker.bak'
  WITH REPLACE, RECOVERY,
  MOVE 'UAInteractionTracker' TO 'D:\Microsoft SQL Server\MSSQL10_50.PROD\MSSQL\DATA\UAInteractionTrackerTEST.mdf',
  MOVE 'UAInteractionTracker_log' TO 'D:\Microsoft SQL Server\MSSQL10_50.PROD\MSSQL\DATA\UAInteractionTrackerTEST_log.ldf'

Database Restore Runaway

Take database offline to stop the restore.
This leaves the database in an unknown state. Most likely a full restore will be needed.

Encryption

Encryption comes in two forms. Encryption at rest and in transit.

Moving TDE Encrypted databases

Turning encryption off and on does not work. The original certificate is needed to restore the database. The database can be re-encrypted with a new certificate.

BACKUP CERTIFICATE TDECert1
TO FILE = 'E:\Backup\certificate_TDE_Test_Certificate.cer'
WITH PRIVATE KEY
(FILE = 'E:\Backup\certificate_TDE_Test_Key.pvk',
ENCRYPTION BY PASSWORD = 'Password12#')

CREATE CERTIFICATE TDECert2
FROM FILE = 'E:\cert_Backups\ certificate_TDE_Test_Certificate.cer'     
WITH PRIVATE KEY (FILE = 'E:\cert_Backups\certificate_TDE_Test_Key.pvk', 
DECRYPTION BY PASSWORD = 'Password12#')

Indexes

Rebuild All Indexes with 30+ Fragmentation Script

IF OBJECT_ID( 'tempdb.dbo.#tmpIndex', 'U') IS NOT NULL
  DROP TABLE #tmpIndex; 
CREATE TABLE #tmpIndex
(
DBname varchar(128),
SchemaName varchar(128),
TableName varchar(128),
IndexName varchar(128)
)

exec sp_MSforeachdb @command1 = '
USE [?]
IF DB_ID(''?'') > 4
BEGIN
DECLARE 
	@database_id INT = DB_ID( "?");
	INSERT INTO #tmpIndex 
	 SELECT 
	"?"  as db_name,
	SCHEMA_NAME( C.[schema_id] ) as schema_name,
    OBJECT_NAME(A.[object_id]) as Table_Name, 
    B.[name] as Index_Name
FROM 
    sys.dm_db_index_physical_stats ( @database_id ,NULL,NULL,NULL, NULL ) A
	INNER JOIN sys.indexes B ON A.[object_id] = B.[object_id]
	INNER JOIN sys.tables C ON C.[name] = OBJECT_NAME( A.[object_id] )
	AND A.index_id = B.index_id  
	AND A.avg_fragmentation_in_percent > 5.0
	AND A.avg_fragmentation_in_percent < 30.0
	AND B.[index_id] <> 0
	ORDER BY 1,2,3;
END';

DECLARE @db_name varchar(128), @SchemaName varchar(128), @TableName varchar(128), @IndexName varchar(128), @COUNTER int;
SET @COUNTER = 0;
DECLARE Fragment_Cursor CURSOR FOR
select * from #tmpIndex 
Order by 1,2,3;
OPEN Fragment_Cursor;

FETCH NEXT FROM Fragment_Cursor INTO @db_name, @SchemaName, @TableName, @IndexName;
WHILE @@FETCH_STATUS = 0
   BEGIN
		SET @COUNTER = @COUNTER + 1;
		DECLARE @COMMAND varchar(200);
		SET @COMMAND = 'ALTER INDEX [' + @IndexName + '] ON [' + @db_name + '].[' + @SchemaName + '].[' + @TableName + ']' + ' REBUILD';
	BEGIN try
		PRINT CAST( @COUNTER AS varchar(20) ) + ' ' + @COMMAND;
		EXEC( @COMMAND );
	END try
	BEGIN catch
		print 'Error in command ';
	END catch
   		FETCH NEXT FROM Fragment_Cursor INTO @db_name, @SchemaName, @TableName, @IndexName;
   END;

CLOSE Fragment_Cursor;
DEALLOCATE Fragment_Cursor;

DROP TABLE #tmpIndex;

Index Fragmentation

SELECT dbschemas.[name] as 'Schema', 
dbtables.[name] as 'Table', 
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc

Change NONCLUSTERED TO CLUSTERED

ALTER TABLE dbo.YourTable
DROP CONSTRAINT PK_YourTable

and then re-create it as clustered:
ALTER TABLE dbo.YourTable
ADD CONSTRAINT PK_YourTable
PRIMARY KEY CLUSTERED (YourPKField)

Fetch Clustered Indexes

--fetch clustered indexes
SELECT 
	t.name AS table_name, 
	i.name as index_name,
	i.type_desc AS index_type, 
	i.is_unique AS is_unique_index
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
	ON t.schema_id = s.schema_id
INNER JOIN sys.indexes AS i
	ON t.object_id = i.object_id
WHERE i.type_desc = 'CLUSTERED';

ReBuild-Reindex all tables

DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
 SET @fillfactor = 80
DECLARE TableCursor CURSOR FOR
 SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
 SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
 CLOSE TableCursor
DEALLOCATE TableCursor
 GO 

Drop All Indexes on A Database[1]

DECLARE idx CURSOR FOR 
	SELECT	IDX.name AS idxName, OBJ.name AS tblName 
	FROM	sys.indexes IDX inner join
			sys.all_objects OBJ ON IDX.object_id = OBJ.object_id 
	WHERE	(IDX.type) = 2 AND (OBJ.type = 'U')
OPEN idx

DECLARE @name varchar(200)
DECLARE @tablename varchar(200)

FETCH NEXT FROM idx INTO @name, @tablename
WHILE @@fetch_status = 0
BEGIN
	EXEC('DROP INDEX ' + @name + ' ON ' + @tablename) 
	FETCH NEXT FROM idx INTO @name, @tablename
END

CLOSE idx
DEALLOCATE idx
GO

Detect Index Fragmentation

Use
select DB_ID(); --for the database id in line 4.

SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, 
ind.name AS IndexName, indexstats.index_type_desc AS IndexType, 
indexstats.avg_fragmentation_in_percent 
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats 
INNER JOIN sys.indexes ind  
ON ind.object_id = indexstats.object_id 
AND ind.index_id = indexstats.index_id 
WHERE indexstats.avg_fragmentation_in_percent > 30 
ORDER BY indexstats.avg_fragmentation_in_percent DESC;

Queries

Timing a Query

DECLARE @EndTime datetime
DECLARE @StartTime datetime 
SELECT @StartTime=GETDATE() 

-- Write Your Query
SELECT @EndTime=GETDATE()

--This will return execution time of your query
SELECT DATEDIFF(ms,@StartTime,@EndTime) AS [Duration in millisecs] 

Mirror Queries

  • Endpoint config - SELECT (star) FROM sys.tcp_endpoints
  • Endpoint Mirror Status - SELECT (star) FROM sys.database_mirroring_endpoints
  • Trustworthy - SELECT name, database_id, is_trustworthy_on FROM sys.databases;
  • Mirror Status - SELECT (star) FROM sys.database_mirroring;

Tables

All Columns with a given name

--All Columns with a given name
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE upper( c.name ) LIKE '%PersonID%'
ORDER BY schema_name, table_name;

All Table Sizes

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

Column Types in a Table

SELECT 
    c.name 'Column Name',
    t.Name 'Data type',
    c.max_length 'Max Length',
    c.precision ,
    c.scale ,
    c.is_nullable,
    ISNULL(i.is_primary_key, 0) 'Primary Key'
FROM    
    sys.columns c
INNER JOIN 
    sys.types t ON c.user_type_id = t.user_type_id
LEFT OUTER JOIN 
    sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN 
    sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE
    c.object_id = OBJECT_ID('YourTableName')

Change Column Name

  • Doesn't work

EXEC sp_rename 'Sales.SalesTerritory.TerritoryID', 'TerrID', 'COLUMN';

  • Use Management Studio

Foreign Key Relationships

SELECT
    PK_Table = PK.TABLE_NAME,
    PK_Column = PT.COLUMN_NAME,
    FK_Table = FK.TABLE_NAME,
    FK_Column = CU.COLUMN_NAME,
    Constraint_Name = C.CONSTRAINT_NAME
FROM
    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
    ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
    ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
    ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
            SELECT
                i1.TABLE_NAME,
                i2.COLUMN_NAME
            FROM
                INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
            INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
                ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
            WHERE
                i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
           ) PT
    ON PT.TABLE_NAME = PK.TABLE_NAME
	Order by 1;

Row counts

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
-- Quickly get row counts.
SELECT OBJECT_SCHEMA_NAME(p.object_id) AS [Schema]
    , OBJECT_NAME(p.object_id) AS [Table]
    , i.name AS [Index]
    , p.partition_number
    , p.rows AS [Row Count]
    , i.type_desc AS [Index Type]
FROM sys.partitions p 
INNER JOIN sys.indexes i ON p.object_id = i.object_id
                         AND p.index_id = i.index_id
WHERE OBJECT_SCHEMA_NAME(p.object_id) != 'sys'
ORDER BY [Schema], [Table], [Index]

Add Primary Key

alter table _testbinary add PK_ID int identity(1,1);
ALTER TABLE [SDS_CLOCKWORK].[dbo]._testbinary ADD CONSTRAINT PK_test_binary_ID PRIMARY KEY (PK_ID);

Longest Record in Column

-- Fetches the longest record in a column measured by characters
select top 1 <column> as Lng_Record, len(<column>) as Len_In_Char
from <table>
order by 2 desc

Find all Identity Columns

select COLUMN_NAME, TABLE_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = 'dbo'
and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
order by TABLE_NAME

Tables without Primary Keys

SELECT SCHEMA_NAME(schema_id) AS SchemaName,name AS TableName
FROM sys.tables
WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 0
ORDER BY SchemaName, TableName;

PRIMARY KEYs default to CLUSTERED indexes, don't use anything else.br />

Users

Disconnect all users from a database

-- Set the database name for which to kill the connections
set @databaseName = 'database name'
set @execSql = '' 
select  @execSql = @execSql + 'kill ' + convert(char(10), spid) + ' '
from    master.dbo.sysprocesses
where   db_name(dbid) = @databaseName
     and
     DBID <> 0
     and
     spid <> @@spid
exec(@execSql)


Internal Links

Parent Article: Databases