12 Days of Christmas 2011/ MSSQLTips/ Syndicated/ Tips and Tricks

On the Tenth Day of Christmas…2011

, , , 3 Comments

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.