Difference between revisions of "Microsoft SQL Server (Notes)"
(→Log File Backup Script) |
(No difference)
|
Latest revision as of 16:17, 22 June 2018
Microsoft SQL Server - Assorted maintenance queries.
Contents
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