JosiahSiegel’s gists

JosiahSiegel / attach_database.sql

Last active August 29, 2015 14:17

#MSSQL Attach Database

	USE [master]
	GO
	CREATE DATABASE [AdventureWorks2008R2] ON
	( FILENAME = N'C:\AdventureWorks2008R2_Data.mdf' ),
	( FILENAME = N'C:\AdventureWorks2008R2_Log.ldf' )
	FOR ATTACH
	GO

	--Databases>Attach>Add

JosiahSiegel / verify_integrity.sql

Created March 17, 2015 01:13

#MSSQL #Research Verify Logical and Physical Integrity of Database

	DBCC CHECKDB ('AdventureWorks2012_Data')
	GO

JosiahSiegel / backup_database.sql

Last active January 5, 2017 23:47

#MSSQL #Backup Backup Database https://ola.hallengren.com/

JosiahSiegel / monitor_progress.sql

Last active August 29, 2015 14:17

#MSSQL #Research Database Backup/Restore Progress

	SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete)
	AS [PERCENT Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GETDATE()),20) AS [ETA COMPLETION TIME],
	CONVERT(NUMERIC(6,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed MIN],
	CONVERT(NUMERIC(6,2),r.estimated_completion_time/1000.0/60.0) AS [ETA MIN],
	CONVERT(NUMERIC(6,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],
	CONVERT(VARCHAR(100),(SELECT SUBSTRING(TEXT,r.statement_start_offset/2,
	CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
	FROM sys.dm_exec_sql_text(sql_handle)))
	FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')

JosiahSiegel / who_is_active.sql

Created March 18, 2015 14:32

#MSSQL #Research Who Is Active

	exec sp_WhoIsActive @get_full_inner_text = 1,
	@get_plans = 2,
	@get_outer_command = 1,
	@get_transaction_info = 1,
	@get_task_info = 2,
	@get_locks = 1,
	@find_block_leaders = 1;

JosiahSiegel / fragmentation_percent.sql

Last active August 29, 2015 14:17

#MSSQL #Research Fragmentation Percent

	DECLARE @DatabaseID int

	SET @DatabaseID = DB_ID()

	SELECT DB_NAME(@DatabaseID) AS DatabaseName,
	schemas.[name] AS SchemaName,
	objects.[name] AS ObjectName,
	indexes.[name] AS IndexName,
	indexes.fill_factor,
	objects.type_desc AS ObjectType,

JosiahSiegel / command_log.sql

Created March 18, 2015 14:37

#MSSQL #Research Command Log

	SELECT DatabaseName,
	SchemaName,
	ObjectName,
	CASE WHEN ObjectType = 'U' THEN 'USER_TABLE' WHEN ObjectType = 'V' THEN 'VIEW' END AS ObjectType,
	IndexName,
	CASE WHEN IndexType = 1 THEN 'CLUSTERED' WHEN IndexType = 2 THEN 'NONCLUSTERED' WHEN IndexType = 3 THEN 'XML' WHEN IndexType = 4 THEN 'SPATIAL' END AS IndexType,
	PartitionNumber,
	ExtendedInfo.value('(ExtendedInfo/PageCount)[1]','int') AS [PageCount],
	ExtendedInfo.value('(ExtendedInfo/Fragmentation)[1]','float') AS Fragmentation,
	CommandType,

JosiahSiegel / dbcc_shrinkfile.sql

Last active February 1, 2021 15:33

#MSSQL DBCC Shrink Log File

	USE [AdventureWorks2008]
	GO
	-- Truncate the log by changing the database recovery model to SIMPLE.
	ALTER DATABASE AdventureWorks2008
	SET RECOVERY SIMPLE;
	GO
	-- Shrink log file to 10GB
	DBCC SHRINKFILE (AdventureWorks2008_Log, 10000);
	GO
	-- Reset the database recovery model.

JosiahSiegel / gist:1ec4c1eabbd24bc04a7b

Created March 21, 2015 03:09

#MSSQL #Research Run SP Blitz Index

exec sp_BlitzIndex

JosiahSiegel / index_sizes.sql

Last active February 26, 2016 20:39

#MSSQL #Research Size of all indexes for table

	DECLARE @TableName varchar(50) = 'TableName'
	SELECT
	OBJECT_NAME(i.OBJECT_ID) AS TableName,
	OBJECT_SCHEMA_NAME(i.OBJECT_ID) AS SchemaName,
	i.name AS IndexName,
	i.index_id AS IndexID,
	i.type_desc AS IndexType,
	SUM(DISTINCT a.used_pages) / 128 AS 'Indexsize(MB)',
	DB_NAME(MAX(s.database_id)) AS DatabaseName
	FROM sys.indexes AS i

Josiah Siegel JosiahSiegel