On the Tenth Day of Christmas…2011
On the Tenth day of Christmas I Give a Gift to Thee…Ten Quick Hit Thumbscripts
A Nine Hour Work Day
I FORGOT WHAT EIGHT WAS FOR…
Seven Reports to Start My Day
Six Gb of RAM
Five Volumes Minimum…
Four Words to Live By…
Three Training Events…
Two Twitter Handles…
and Mirrored Disks with Parity
We all have our little toolbox we set aside at the ready for repetitive tasks in our daily work. Today, in the spirit of the Holidays I open up mine and give you 10 of my smallest yet effective scripts. You’ll notice that there is some template syntax included in the code so be sure to read my article on Templates in SQL Server if you’re not familiar with replacing template parameters. (Please don’t judge my formatting. WordPress is playing the Grinch!)
01: Determine percent complete for all backup processes running on the SQL Server instance:
SELECT der.[session_id], der.[percent_complete], dest.[text]
FROM sys.[dm_exec_requests] der
CROSS APPLY sys.[dm_exec_sql_text](der.[sql_handle]) dest
WHERE [percent_complete] IS NOT NULL AND dest.text LIKE 'BACKUP %'
02: Quick hit on what requests are currently running in the query engine
SELECT eS.[session_id],
eST.text,
der.[start_time],
der.STATUS,
der.[wait_type],
der.[wait_resource]
FROM sys.[dm_exec_requests] der INNER JOIN sys.[dm_exec_sessions] eS
ON der.[session_id] = eS.[session_id]
CROSS APPLY sys.[dm_exec_sql_text](der.[sql_handle]) eST
WHERE [eS].[session_id] <> @@SPID
03: Ad Hoc SQL to Script out logins and users creations for all AD logins on the SQL instance (for migration of logins between SQL instances)
SELECT name, dbname,
'CREATE LOGIN [' + name + '] FROM WINDOWS WITH DEFAULT_DATABASE = [tempdb];',
'CREATE USER [' + name + '] FROM LOGIN [' + name + '];'
FROM sys.syslogins
WHERE isntname = 1
04: Ad Hoc SQL to script out logins and users creations for all AD logins in current database only
SELECT name,
'CREATE LOGIN [' + name + '] FROM WINDOWS WITH DEFAULT_DATABASE = tempdb;',
'CREATE USER [' + name + '] FROM LOGIN [' + name + ']'
FROM sys.sysusers
WHERE isntname = 1
05: Backup file size history (Great quick hit script to get an idea of growth in you databases over time)
SELECT BS.backup_finish_date,
BF.backed_up_page_count ,
BF.file_type ,
BF.logical_name ,
BF.physical_name ,
CEILING(BF.[backup_size]/1024/1024) AS file_mb,
BF.backup_set_id
FROM msdb.dbo.[backupfile] BF
INNER JOIN msdb.dbo.[backupset] BS ON [BF].[backup_set_id] = [BS].[backup_set_id]
WHERE logical_name = ''
ORDER BY BS.backup_finish_date DESC;
06: Get column name, data type, and nullability for a database table
SELECT
AC.name,
CASE
WHEN T.[name] LIKE '%char%' THEN T.[name] + '(' + CAST(AC.max_length AS varchar(5)) + ')'
WHEN T.[name] = 'decimal' THEN T.[name] + '(' + CAST(AC.[precision] AS varchar(5)) + ',' + CAST(AC.[scale] AS varchar(5)) + ')'
ELSE T.name
END AS datatype,
CASE AC.is_nullable
WHEN 1 THEN 'NULL'
WHEN 0 THEN 'NOT NULL'
END AS Nullable
FROM sys.all_columns AC
INNER JOIN sys.types T ON AC.system_type_id = T.user_type_id
WHERE OBJECT_NAME(AC.object_id) = ''
ORDER BY AC.column_id;
07: Get a list of all databases and associated file sizes and growth information
DECLARE @cmd varchar(1000)
SELECT @cmd = 'USE [?] SELECT db_name(db_id()), type_desc, physical_name, size*8/1024 as size_mb, max_size as max_size_in_pages, growth FROM sys.[database_files] df'
EXEC sp_msforeachdb @command1 = @cmd;
08: Determine amount of free space and pages in tempdb
–Space: high level
SELECT SUM(unallocated_extent_page_count) AS [free pages],
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage;
–Space: detail
SELECT
SUM (user_object_reserved_page_count)*8/1024 as usr_obj_mb,
SUM (internal_object_reserved_page_count)*8/1024 as internal_obj_mb,
SUM (version_store_reserved_page_count)*8/1024 as version_store_mb,
SUM (unallocated_extent_page_count)*8/1024/1024 as freespace_gb,
SUM (mixed_extent_page_count)*8/1024 as mixedextent_mb
FROM sys.dm_db_file_space_usage;
09: Which databases are using snapshot isolation?
SELECT SD.[name] ,
SD.snapshot_isolation_state_desc ,
SD.is_read_committed_snapshot_on
FROM sys.databases SD
WHERE SD.snapshot_isolation_state_desc = 'ON';
10–Core and socket count for a SQL Server instance
SELECT
oSI.cpu_count AS [cores],
oSI.cpu_count/oSI.hyperthread_ratio AS [sockets]
FROM sys.[dm_os_sys_info] oSI
There you have it! Merry Christmas and Happy Hanukkah! See you on Day Eleven with some milking maid or dancing lords. It will be like Cirque de SQL meets Rankin & Bass.