What’s Happening in SQL Server?

One of the most important tools for your toolbox is some mechanism to see what’s happening in the engine. Who is running what in my system, and how is it behaving? I liken this to “Task Manager” or “Resource Monitor” in Windows. SQL Server has “Activity Monitor” as a graphical tool within SQL Server Management Studios, but I’ve found that to be a bit limiting.

There are many other fantastic tools for such diagnostics… Off the top of my head, I’d recommend looking at sp_Blitz from Brent Ozar and team or sp_WhoIsActive from Adam Machanic. I have not used them a lot myself, but I’ve heard nothing but great things about each tool.

I have developed the following script over the course of several years. I have iterated over it and updated it hundreds of times. I used it daily, and I’m never done changing it. So why write my own? A few reasons (none of them may be particularly compelling to you):

  1. It’s been a great learning experience to dive in and discover SQL’s DMVs
  2. It’s extremely customized for my needs (what can I say- I like what I like)
  3. It’s a no-trace solution… meaning that it’s just a SELECT statement, and no database objects are created, nor are any settings or configurations changed

I believe the only permission you’ll need on your SQL instance is VIEW SERVER STATE. It should also be compatible with at least SQL 2008 – SQL 2017. Furthermore, it’s written with dynamic SQL so it will return more information with newer versions of SQL, as Microsoft has augmented DMVs over time.

Keep in mind, I never claimed this code was pretty 😉

Without further ado, here it is:

/*
Written by: Ryan Snee (http://www.sqlsnee.com)

*/

DECLARE @ShowSleepingThreads BIT;
DECLARE @SkipQP BIT;
DECLARE @V  varchar(50);
DECLARE @V1 INT;
DECLARE @V2 INT;
DECLARE @V3 INT;



/*<Setable Parameters>*/
SET @ShowSleepingThreads=0;
SET @SkipQP=0;					--Sometimes this query will be blocked when querying the sys.dm_exec_query_plan DMF
/*</Setable Parameters>*/




select @V=cast(SERVERPROPERTY ('ProductVersion')  as varchar(50));

SELECT @V1=CAST(substring(@V,1,charindex('.',@V)-1) as int)
,@V2=CAST(substring(@V,charindex('.',@V)+1,(charindex('.',@V,charindex('.',@V)+1))-(charindex('.',@V)+1)) as int)
,@V3=CAST(substring(@V,	charindex('.',@V,CHARINDEX('.',@V)+1)+1,(charindex('.',@V,charindex('.',@V,CHARINDEX('.',@V)+1)+1))-(charindex('.',@V,CHARINDEX('.',@V)+1)+1)) as int);


declare @sql nvarchar(max);
SET @sql= CAST('' AS nvarchar(max))
+N'SELECT
	cast(s.session_id  as varchar(10))
		+ case when (r.blocking_session_id=0 or r.blocking_session_id is null) and LB.LeadBlocker=1 then ''*'' else '''' end
	AS [Session]
	,'
	+CASE
	WHEN @V1 >= 11 THEN N'concat(case when dbt.database_transaction_status2=258 then ''ROLLBACK'' ELSE s.status END,'':''+r.status)'
	ELSE N'case when dbt.database_transaction_status2=258 then ''ROLLBACK'' ELSE (s.status) END --This column is undocumented by MS, and this is not guranteed to be accurate!!!' 
	END+N' as [Status]
	
	
	,CASE
	
		WHEN ((datediff(day,s.last_request_start_time,getdate())>28) AND (r.session_id IS NOT NULL))
			THEN cast(datediff(day,s.last_request_start_time,getdate()) as varchar(50))+''d+''
		WHEN [r].[total_elapsed_time] > 86400000 --Days, hours, and minutes
			THEN cast([r].[total_elapsed_time]/86400000 as varchar(50)) +''d ''+cast(([r].[total_elapsed_time]/3600000)%24 as varchar(50)) +''h ''+cast(([r].[total_elapsed_time]/60000)%60 as varchar(50)) +''m''
		WHEN [r].[total_elapsed_time] > 3600000 --Hours, minutes and seconds
			THEN cast([r].[total_elapsed_time]/3600000 as varchar(50)) +''h ''+cast(([r].[total_elapsed_time]/60000)%60 as varchar(50)) +''m ''+cast(([r].[total_elapsed_time]/1000)%60 as varchar(50))+''s''
		WHEN [r].[total_elapsed_time] > 60000 --Minutes, seconds, and milliseconds
			THEN cast([r].[total_elapsed_time]/60000 as varchar(50)) +''m ''+cast(([r].[total_elapsed_time]/1000)%60 as varchar(50))+''s ''+cast(([r].[total_elapsed_time])%1000 as varchar(50))+''ms''
		WHEN [r].[total_elapsed_time] > 1000 --Seconds and milliseconds
			THEN cast([r].[total_elapsed_time]/1000 as varchar(50))+''s ''+cast(([r].[total_elapsed_time])%1000 as varchar(50))+''ms''
		ELSE cast([r].[total_elapsed_time] as varchar(50))+''ms'' --Milliseconds

	END AS [ElapsedTime]
	
	
	
	,coalesce(nullif(s.host_name+'' '',''''),''(''+c.client_net_address+'')'') as Client
	
	,

	case
		when s.login_name <> s.original_login_name
			THEN s.login_name +'' '' +quotename(s.original_login_name ,''('')
		ELSE s.login_name
	END AS [login_name]
	
	,coalesce(s.program_name,'''')+ coalesce('' (PID:''+cast(s.host_process_id as varchar(20))+'')'','''') as [ClientProgram]
	
	,'+CASE
	WHEN @V1 >= 11 THEN N'DB_NAME(COALESCE(r.database_id, s.database_id))'
	ELSE N'DB_NAME(r.database_id)'
	END+N' AS [Database Name]
	
	
	,nullif(r.percent_complete,0) AS [%Complete]
	

	

	,nullif(
		case
			WHEN(
				select count(distinct scheduler_id)
				FROM sys.dm_os_tasks ost
				where r.request_id=ost.request_id and s.session_id=ost.session_id
				--and ost.task_state = ''RUNNING''
				and scheduler_id < 1000000
			) > 1
			THEN(
				select count(distinct scheduler_id)
				FROM sys.dm_os_tasks ost
				where r.request_id=ost.request_id and s.session_id=ost.session_id
				--and ost.task_state = ''RUNNING''
				and scheduler_id < 1000000
			)-1
			ELSE(
				select count(distinct scheduler_id)
				FROM sys.dm_os_tasks ost
				where r.request_id=ost.request_id and s.session_id=ost.session_id
				--and ost.task_state = ''RUNNING''
				and scheduler_id < 1000000
			)
		END
	,0) as [Tasks]
	,'+CASE
	WHEN @V1 >= 11 THEN N'format(coalesce(r.cpu_time,s.cpu_time),''#,###'')'
	ELSE N'coalesce(r.cpu_time,s.cpu_time)'
	END+N' AS [CPU(ms)]
	
	
	,CASE
		WHEN eqmg.granted_memory_kb >= 1048576
			THEN cast(cast(eqmg.granted_memory_kb/1048576.0 as decimal(18,2)) as varchar(30))+ '' GB''
		WHEN eqmg.granted_memory_kb >= 1024
			THEN cast(cast(eqmg.granted_memory_kb/1024.0 as decimal(18,2)) as varchar(30))+ '' MB''
		ELSE
			cast(eqmg.granted_memory_kb as varchar(30))+ '' KB''
	 END AS [granted_mem]
	,CASE
		WHEN ([dtdt].[TLogBytesUsed]) > 1073741824000 THEN CAST(CAST((([dtdt].[TLogBytesUsed]) / 1024.0/1024.0/1024.0/1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + '' TB''
		WHEN ([dtdt].[TLogBytesUsed]) > 1048576000 THEN CAST(CAST((([dtdt].[TLogBytesUsed]) / 1024.0/1024.0/1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + '' GB''
		WHEN ([dtdt].[TLogBytesUsed]) > 1024000 THEN CAST(CAST((([dtdt].[TLogBytesUsed]) / 1024.0/1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + '' MB''
		WHEN ([dtdt].[TLogBytesUsed]) > 1000 THEN CAST(CAST((([dtdt].[TLogBytesUsed]) / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + '' KB''
		ELSE CAST(CAST((([dtdt].[TLogBytesUsed]) ) AS DECIMAL(18,2)) AS VARCHAR(20)) + '' B''
	END as [TLogBytesUsed]

,'+case WHEN @V1 >= 11 THEN N'format(try_cast(eqmg.query_cost as decimal(38,0)),''#,###'')' else 'cast(eqmg.query_cost as decimal(38,0))' END +' AS [estQueryCost]	
	

	


	,cast((cast(coalesce(r.logical_reads,s.logical_reads) as bigint)*8)/1024.0/1024. as decimal(18,2)) as [LogicalReads(GB)]
	,CASE
	WHEN (r.logical_reads IS NOT NULL AND r.reads IS NOT NULL AND r.logical_reads <> 0)
		THEN CAST(
			(1-(CAST(r.reads as decimal)/cast(r.logical_reads as decimal)))*100
		
		as decimal(18,2))
	WHEN s.logical_reads <> 0
		THEN CAST((1-(CAST(s.reads as decimal)/cast(s.logical_reads as decimal)))*100 as decimal(18,2))
	ELSE
		NULL
	END AS [BPHitRate]
	
	

	,case when r.wait_type = ''CXPACKET'' THEN coalesce(ParallelWait.wait_duration_ms, r.wait_time)  ELSE r.wait_time END AS [wait_time(ms)]
	,case when r.wait_type = ''CXPACKET'' THEN coalesce(ParallelWait.wait_type, r.wait_type)+''*''  ELSE r.wait_type END AS [wait_type]
	,case when r.wait_type = ''CXPACKET'' THEN coalesce(ParallelWait.wait_resource, r.wait_resource)  ELSE r.wait_resource END AS [wait_resource]'+
	case when @V1 >= 13
	THEN N'
	,SesWaits.wait_type + '' (''+CAST(SesWaits.wait_time_ms as varchar(10))+'')'' as [BiggestSessionWait (ms)]'
	ELSE N''
	END +N'




	,nullif(r.blocking_session_id, 0) AS [Blocker]
	,r.last_wait_type
	
	
	
	,r.command



	,CASE
		WHEN s.transaction_isolation_level=1 THEN ''*Read Uncommitted*''
		WHEN s.transaction_isolation_level=2 THEN ''Read Committed''
		WHEN s.transaction_isolation_level=3 THEN ''*Repeatable Read*''
		WHEN s.transaction_isolation_level=4 THEN ''*Serializable*''
		WHEN s.transaction_isolation_level=5 THEN ''*Snapshot*''
		ELSE ''Unspecified''
	END AS [Transaction Isolation]
	
	,c.auth_scheme
	,c.net_transport
	,c.encrypt_option
	
	,CASE
		WHEN at.transaction_state=0 THEN ''The transaction has not been completely initialized yet.''
		WHEN at.transaction_state=1 THEN ''The transaction has been initialized but has not started.''
		WHEN at.transaction_state=2 THEN ''The transaction is active.''
		WHEN at.transaction_state=3 THEN ''The transaction has ended. This is used for read-only transactions.''
		WHEN at.transaction_state=4 THEN ''The commit process has been initiated on the distributed transaction. This is for DTs only. The DT is still active but further processing cannot take place.''
		WHEN at.transaction_state=5 THEN ''The transaction is in a prepared state and waiting resolution.''
		WHEN at.transaction_state=6 THEN ''The transaction has been committed.''
		WHEN at.transaction_state=7 THEN ''The transaction is being rolled back.''
		WHEN at.transaction_state=8 THEN ''The transaction has been rolled back.''
		ELSE ''Unspecified''
	END AS [Transaction_State_desc]

	
	
	,at.transaction_begin_time
	,coalesce(st.open_transaction_count ,0) as OpenTranCount
	,case WHEN st.is_local =1
	THEN cast(at.transaction_id as varchar(50))
	ELSE cast(at.transaction_id as varchar(50))+''*''
	END AS transaction_id
	

	
	,N''(''+cast('+
	case when @V1 > 11
	THEN N'FORMAT('
	ELSE N''
	END +
	N'r.statement_start_offset'+
CASE WHEN @V1 > 11
THEN N', ''#,###'')'
ELSE N''
END +

	N' as nvarchar(max))+N'') ''+txt.text AS [BatchExecuting]
	
	,'+CASE WHEN
	
	(
		(@V1 >= 13)
		OR (@V1 = 12
			AND (
				(@V2 > 0)
				OR (@V2=0 AND  @V3 >= 5000)
			)
		)
	) --13 or > 12.0.5000
	THEN N'buf.event_info'
	ELSE N'''DBCC INPUTBUFFER(''+cast(s.session_id as varchar(10))+'');'''
	END +N'
	 AS [InputBuffer]
	
	'+CASE WHEN ((@V1 >= 13) OR (@V1 = 12 AND ((@V2 > 0) OR (@V2=0 AND  @V3 >= 5000)))) --13 or > 12.0.5000
	THEN N',buf.parameters AS BufParameters'
	ELSE N''
	END +N'
	 

	--Greg Larsen
	,SUBSTRING(txt.text,
		CASE WHEN r.statement_start_offset = 0
			OR r.statement_start_offset IS NULL
				THEN 1
				ELSE r.statement_start_offset/2 + 1 END,
		CASE WHEN r.statement_end_offset = 0
				OR r.statement_end_offset = -1
				OR r.statement_end_offset IS NULL
					THEN LEN(txt.text)
					ELSE r.statement_end_offset/2 END -
		 	CASE WHEN r.statement_start_offset = 0
				OR r.statement_start_offset IS NULL
					THEN 1
					ELSE r.statement_start_offset/2 END + 2
	) AS [Statement]
	
	,qs.creation_time as [PlanCompiled]
	,CASE
		WHEN r.plan_handle =0x0
			THEN ''''
		ELSE ''DBCC FREEPROCCACHE (''+convert(varchar(200),r.plan_handle,1)+'');''
	END as [RemovePlanFromCacheCmd]
	
	'+case when @SkipQP=0 THEN ' ,qp.query_plan' ELSE '' END +'
	
	,[txt2].text AS [MostRecentQuery]
	,s.login_time
	,CASE c.endpoint_id
		WHEN 1
		THEN 1
		ELSE 0
	END AS [isDAC]	
	

	
	

	,quotename(db_name(txt.dbid))+''.''+quotename(object_schema_name(txt.objectid,cast(txt.dbid as int)))+''.''+quotename(object_name(txt.objectid,cast(txt.dbid as int))) AS [ObjectName]
	
	
	,s.row_count as [RowsReturnedLastRequest]
	,r.row_count as [RequestRowCount]
	,m.text as [LastErrorEncountered]
	
	,wlg.name AS [WorkloadGroup]
	,rp.name AS [ResourcePool]
	
	
	,s.client_interface_name
	,net_packet_size
	,CASE
		WHEN (cast(c.num_reads as bigint)*net_packet_size) >= 1073741824
			THEN cast(cast((cast(c.num_reads as bigint)*net_packet_size)/1073741824.0 as decimal(18,2)) as varchar(30))+ '' GB''
		WHEN (cast(c.num_reads as bigint)*net_packet_size) >= 1048576
			THEN cast(cast((cast(c.num_reads as bigint)*net_packet_size)/1048576.0 as decimal(18,2)) as varchar(30))+ '' MB''
		WHEN (cast(c.num_reads as bigint)*net_packet_size) >= 1024
			THEN cast(cast((cast(c.num_reads as bigint)*net_packet_size)/1024.0 as decimal(18,2)) as varchar(30))+ '' KB''
		ELSE
			cast((cast(c.num_reads as bigint)*net_packet_size) as varchar(30))+ '' B''
	 END AS [NetworkBytesIngress]
	
	,CASE
		WHEN (cast(c.num_writes as bigint)*net_packet_size) >= 1073741824
			THEN cast(cast((cast(c.num_writes as bigint)*net_packet_size)/1073741824.0 as decimal(18,2)) as varchar(30))+ '' GB''
		WHEN (cast(c.num_writes as bigint)*net_packet_size) >= 1048576
			THEN cast(cast((cast(c.num_writes as bigint)*net_packet_size)/1048576.0 as decimal(18,2)) as varchar(30))+ '' MB''
		WHEN (cast(c.num_writes as bigint)*net_packet_size) >= 1024
			THEN cast(cast((cast(c.num_writes as bigint)*net_packet_size)/1024.0 as decimal(18,2)) as varchar(30))+ '' KB''
		ELSE
			cast((cast(c.num_writes as bigint)*net_packet_size) as varchar(30))+ '' B''
	 END AS [NetworkBytesEgress]
	,s.last_request_start_time
	,getdate() as [TimeRun]
	
from sys.dm_exec_sessions [s]
	LEFT JOIN sys.dm_exec_requests [r]
		ON s.session_id=r.session_id

	OUTER APPLY (
		SELECT top 1 *
		FROM sys.dm_exec_connections con
		where s.session_id=con.session_id
		--Grab a connection even if we don''t have a corresponding request
			and (r.connection_id=con.connection_id OR r.connection_id is null)
		--In that case, pick any connection related to this session that has a SQL handle... no good way to determine most recent
		ORDER BY CASE WHEN con.most_recent_sql_handle = 0x0 THEN 0 ELSE 1 END DESC
		
	) AS c
		
	OUTER APPLY sys.dm_exec_sql_text(c.[most_recent_sql_handle]) AS [txt2]
	
	left JOIN sys.dm_exec_query_stats AS [qs]
		ON qs.plan_handle=r.plan_handle
			AND r.statement_start_offset=qs.statement_start_offset
			AND r.statement_end_offset=qs.statement_end_offset
	OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS [txt]

	'+case when @SkipQP=0 THEN '
	--Including exec_query_plan can cause this query to be blocked
	OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) AS [qp]' ELSE '' END+ '

	LEFT JOIN sys.dm_tran_session_transactions [st]
		ON s.session_id=st.session_id
	LEFT JOIN sys.dm_tran_active_transactions [at]
		ON st.transaction_id=at.transaction_id
	
	
	
	outer apply(SELECT TOP 1 [database_transaction_status2] FROM sys.dm_tran_database_transactions dbt WHERE st.transaction_id=dbt.transaction_id and database_transaction_status2=258) dbt
	
	LEFT JOIN sys.dm_resource_governor_workload_groups wlg
		on s.group_id=wlg.group_id
	LEFT JOIN sys.dm_resource_governor_resource_pools rp
		on wlg.pool_id=rp.pool_id

	LEFT JOIN sys.messages m
		on s.prev_error=m.message_id
			AND language_id=1033 --English
	
	OUTER APPLY (SELECT TOP 1 wait_type ,wait_duration_ms,wait_resource from sys.dm_os_waiting_tasks a where a.session_id=s.session_id ORDER BY CASE WHEN wait_type=''CXPACKET'' THEN 2 when wait_type=''SLEEP_TASK'' THEN 1 ELSE 0 END, wait_duration_ms desc) ParallelWait
	LEFT JOIN sys.dm_exec_query_memory_grants eqmg
		ON s.session_id=eqmg.session_id
		AND r.request_id=eqmg.request_id
	OUTER APPLY (SELECT sum(database_transaction_log_bytes_used) as TLogBytesUsed FROM sys.dm_tran_database_transactions tr WHERE tr.transaction_id= at.transaction_id) AS dtdt
	'
	+CASE WHEN ((@V1 >= 13) OR (@V1 = 12 AND ((@V2 > 0) OR (@V2=0 AND  @V3 >= 5000)))) --13 or > 12.0.5000
	THEN N'
	OUTER APPLY sys.dm_exec_input_buffer(s.session_id, r.request_id) buf'
	ELSE N''
	END+

	CASE WHEN @V1>=13
	THEN N'
	--Biggest wait type (by time) besides CXPACKET
	OUTER APPLY (SELECT top 1 * FROM sys.dm_exec_session_wait_stats desws WHERE desws.session_id=s.session_id AND wait_time_ms > 0 ORDER BY case when wait_type=''CXPACKET'' THEN 1 ELSE 0 END, wait_time_ms desc) SesWaits'
	ELSE N''
	END+
	

	N'
	OUTER APPLY(select top 1 1 AS LeadBlocker from sys.dm_exec_requests r2 where s.session_id=r2.blocking_session_id) LB
WHERE 1=1
	AND (s.is_user_process = 1 OR command IN (''DB STARTUP'', ''ROLLBACK TRANSACTION'',''ALTER DATABASE ENCRYPTION KEY''))
	AND s.session_id <> @@SPID
	AND coalesce(s.program_name, '''') NOT LIKE ''%IntelliSense''
	--AND transaction_begin_time is not null
	AND ((r.wait_type IS NULL) OR (r.wait_Type <> ''SP_SERVER_DIAGNOSTICS_SLEEP''))'+
		CASE WHEN @ShowSleepingThreads=0 THEN N'
	AND ((s.status <> ''sleeping'') OR(transaction_state IS NOT NULL) OR  command IN (''DB STARTUP'', ''ROLLBACK TRANSACTION'',''ALTER DATABASE ENCRYPTION KEY''))'
	ELSE N'' END+N'
	--AND s.login_name LIKE ''%%''
	--AND '+CASE	WHEN @V1 >= 11 THEN N'DB_NAME(COALESCE(r.database_id, s.database_id))'	ELSE N'DB_NAME(r.database_id)'END+' LIKE ''%%''
ORDER BY
	--transaction_begin_time asc,
	dbt.database_transaction_status2 asc,
	s.status asc,
	CASE when r.status=''running'' THEN 1
	WHEN r.status=''runnable'' THEN 2
	WHEN r.status=''suspended'' THEN 3
	ELSE 4 END,
	
	
	CASE WHEN s.status=''sleeping''
		THEN s.session_id
	ELSE -1
	END DESC,
	s.last_request_start_time desc
	'

	

exec sp_executesql @sql
	

As a fun aside, it is possible to create this as a stored procedure, possibly with with filters (like limiting it to seeing sessions from a specific login), and allow somebody rights to execute just your pre-defined version of this query. This can be done without granting them the broad VIEW SERVER STATE, and can be accomplished through code-signing. While this is out-of-scope for this blog post, I may do one on that topic in the future.

The full script can be found here: SessionsAndRequests.txt.

Please let me know if you found this script useful or if you have any suggestions to improve it.

LLAP,
Ryan


As always, please note the waive of liability published on the About page.

The text of this entire blog site (including all scripts) is under the copyright of Ryan Snee. You are free to reproduce it, as outlined by the Creative Commons Attribution-ShareAlike 3.0 Unported License.

User Clone Script

One of my favorite subjects in SQL server is security, and one of my favorite security topics is permissions.  This post will cover a script I wrote to recreate a user’s permissions within a database.  I call it my User Clone Script, and use it quite heavily.

Use Cases

  • Troubleshooting Permissions
  • Cloning accounts
  • Switching service accounts
  • Copying permissions between development/test/production environments
  • Refactoring permissions to use a SQL Server Role instead of explicit user permissions
  • Refactoring permissions to use an AD group instead of explicitly named users

Highlights of the Script

This script has support for the following:

  • Most object/column permissions (includes stored procedures, views, tables, and functions)
  • Object ownerships
  • Type permissions
  • Schema permissions
  • Schema ownerships
  • Database permissions
  • Role memberships

 Implementation

In a few separate queries, I dump meta-data into a temp table from the following catalog views:

  1. Database permissions ( sys.database_permissions )
  2. Schema ownerships ( sys.schemas )
  3. Object Ownership ( sys.objects )
  4. Role Memberships ( sys.database_role_members )

Then— and this is the key to unraveling role memberships— I use a Recursive CTE to generate the final result set.

A recursive CTE takes the following form:

;with RecursiveCTE as(

--1) Base/Anchor Member
SELECT
    Columns
FROM #SomeTable
WHERE [ID]=@BaseMemberID

--2) Always a UNION/UNION ALL
UNION ALL

--3) Recursive Member
Select
    Columns
FROM #SomeTable T
INNER JOIN RecursiveCTE RCTE ON T.ParentID=RCTE.ID

)

SELECT * FROM RecursiveCTE;

More information about Recursive CTEs can be found here on TechNet.

Output Sample

The following screenshot shows the output of the script, including commands to copy the permissions of User1 to User2.

PermissionsClone

A couple of key points:

  • If the @NewUser variable is NULL, the script will generate the commands used to grant [User1] the permissions they currently have (to copy permissions to another database, for example).
    • Otherwise, it will replace [User1] with [User2].
  • In this example:
    • [User1] is an immediate and direct member of three roles: [CoolUsers], [Level2] and [Level2a].
    • [CoolUsers] is a member of the [db_owner] fixed role.
    • The [Level2] role is a member of the [Level1] role.
  • The commands output by the script will create the roles, if they do not exist, and assign them the same permissions they had before (for the case that you’re trying to copy the permissions to a different database, for example)
  • If a schema or object ownership is detected, the script will generate the command to transfer ownership to the new user, but it will be commented-out.  This is done as a precautionary measure.  As only one user can own any given schema/object, running the commands generated would possibly break existing rights for [User1].  If this is your intention, simply uncomment the ALTER AUTHORIZATION commands before running the scripts.

 The Script

The only thing you’ll need to change before running the script are the two variables (@OldUser and possibly @NewUser) in the <setable> section.

/*
* Author: Ryan Snee
* Version: 1.0
* Date 20130120
* URL: http://sqlsnee.com/r/userclone
*
* License: This work is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.
* License Details: http://creativecommons.org/licenses/by-sa/3.0/deed.en_US
*
* LIMITATIONS
*	Does not currently support a number of database-level securables (see list below)
*	Does not unroll AD group memberships
*
* Does handle the following permissions:
*
*		--From BOL for SQL 2012... class/class_desc columns
*		--"X" indicates that the securable is supported by this script
*		 
*		 	[X] 0 = Database
*		 	[X] 1 = Object or Column
*		 	[X] 3 = Schema
*		 	[ ] 4 = Database Principal
*		 	[ ] 5 = Assembly
*		 	[X] 6 = Type
*		 	[ ] 10 = XML Schema Collection
*		 	[ ] 15 = Message Type
*		 	[ ] 16 = Service Contract
*		 	[ ] 17 = Service
*		 	[ ] 18 = Remote Service Binding
*		 	[ ] 19 = Route
*		 	[ ] 23 = Full-Text Catalog
*		 	[ ] 24 = Symmetric Key
*		 	[ ] 25 = Certificate
*		 	[ ] 26 = Asymmetric Key
*
*  Also handles:
*		Schema/Object Ownership
*		Role Membership
*
*/

declare @sqlvers varchar(5);

--Determine which version of SQL we are running
declare @v numeric(4,2)
select @v = cast(left(cast(serverproperty('productversion') as varchar), 4) AS numeric(4,2))

select @sqlvers =CASE
	WHEN @V >=  12	THEN '120'
	WHEN @V >=  11	THEN '110'
	WHEN @V >= 10	THEN '100'
	WHEN @V >= 9 THEN '90'
	WHEN @V >= 8 THEN '80'
	ELSE 'No Version Found'
END

DECLARE
	@OldUser sysname=NULL,
	@NewUser sysname=NULL;

--<setable>
	SET	@OldUser = 'JohnDoe'
	--SET	@NewUser = 'AdminAssistant'
--</setable>

IF OBJECT_ID('tempdb..#Perms') IS NOT NULL
BEGIN	
	DROP TABLE #Perms
END

create table #Perms(
		 Class sysname
		,Principal nvarchar(300)
		,[NewPrincipal] nvarchar(300)
		,[Role] sysname					NULL
		,type_desc	nvarchar(60)		NULL
		,is_fixed_role bit				NULL
		,[State] nvarchar(60)			NULL
		,[Permission] nvarchar(128)		NULL
		,[Type] sysname					NULL
		,[Schema] sysname				NULL
		,[Object] sysname				NULL
		,[Column] sysname				NULL
		,[Cmd] nvarchar(max)
		,[SortOrder] tinyint
)

--Base Permissions
INSERT INTO #Perms
select
	dp.class_desc as [Class]
	,princ.name as [Principal]
	,case when princ.name=@OldUser
		THEN Coalesce(@NewUser,princ.name)
		ELSE princ.name
	end as [NewPrincipal]
	,NULL as [Role]
	,princ.type_desc
	,princ.is_fixed_role
	,dp.state_desc as [State]
	,dp.permission_name as [Permission]
	,CASE
		WHEN dp.class=0
			THEN 'DATABASE'
		WHEN dp.class=1
			THEN o.type_desc
		WHEN dp.class=3
			THEN 'SCHEMA'
		WHEN dp.class=6
			THEN 'TYPE_TABLE'
		ELSE ''
		END as [Type]
	,CASE
		WHEN dp.class=3
		THEN s3.name
		ELSE s.name
	END AS [Schema]

	,o.name as [Object]
	,c.name as [Column]

	,CASE
		WHEN dp.class=1 --OBJECT_OR_COLUMN level permission
			THEN
				case
					when dp.state='W'
						THEN 'GRANT'
					ELSE state_desc
				END
				+' '+cast(permission_name As varchar(max)) COLLATE database_default + ' ON '
				+quotename(s.name,']')+'.' +quotename(o.name,']')
				+CASE
					WHEN dp.minor_id=0 --Object Permission
						THEN ' '
					WHEN dp.minor_id > 0 --Column Permission
						THEN ' '+quotename(quotename(c.name,']'),')')+' '
					ELSE NULL
				END

		WHEN dp.class=0 --Database level permission
			THEN
				case
					when dp.state='W'
						THEN 'GRANT'
					ELSE state_desc
				END

				+' '+cast(permission_name As varchar(max)) COLLATE database_default 

		WHEN dp.class=6 --Types
			THEN
				case
					when dp.state='W'
						THEN 'GRANT'
					ELSE state_desc
				END

				+' '+cast(permission_name As varchar(max)) COLLATE database_default 
				+ ' ON TYPE::'
				+quotename(s2.name,']')+'.' +quotename(t.name,']')

		WHEN dp.class=3 --Schemas
			THEN
				case
					when dp.state='W'
						THEN 'GRANT'
					ELSE state_desc
				END

				+' '+cast(permission_name As varchar(max)) COLLATE database_default 
				+ ' ON SCHEMA::'
				+quotename(s3.name,']')

		ELSE '--Not yet supported by this script'

	END
		+CASE
			WHEN dp.class in(0,1,6,3)
				THEN
					' TO '
					+case
						when princ.name=@OldUser
							THEN quotename(Coalesce(@NewUser,princ.name)) 
						ELSE quotename(princ.name) 
					end 
					+CASE
						WHEN dp.state='W'
							THEN ' WITH GRANT OPTION;'
						ELSE ';'
					END
			ELSE ''
			END
	 AS [Cmd]
	,1 as [SortOrder]

from sys.database_permissions dp
	left join sys.all_objects o
		on dp.major_id=o.object_id
		and class=1
	left join sys.all_columns c
			on dp.major_id=c.object_id
			and dp.minor_id=c.column_id
	left join sys.database_principals princ
		on dp.grantee_principal_id=princ.principal_id
	left join sys.schemas s
		on o.schema_id=s.schema_id
	left join sys.extended_properties ep
		on ep.major_id = o.object_id and 
					ep.minor_id = 0 and 
					ep.class = 1 and 
					ep.name = N'microsoft_database_tools_support'
	left join sys.types t
		on dp.major_id=t.user_type_id
	left join sys.schemas s2
		on t.schema_id=s2.schema_id
	left join sys.schemas s3
		on dp.major_id=s3.schema_id
		and dp.class=3
where 1=1
	--AND (dp.class<>1 OR o.is_ms_shipped=0) --If it's an object, can't be MS_Shipped
	AND (dp.class<>6 OR t.is_user_defined=1) --If it's a type, only check if it's a  user-defined type
	and  ep.value is null --Not a Microsoft Database Tool support object

--Schema Ownerships
INSERT INTO #Perms
select 

		'Schema OWNERSHIP' as [Class]
	,dp.name as [Principal]
	,case when dp.name=@OldUser
		THEN Coalesce(@NewUser,dp.name)
		ELSE dp.name
	end as [NewPrincipal]
	,NULL as [Role]
	,dp.type_desc
	,dp.is_fixed_role
	,NULL as [State]
	,NULL as [Permission]
	,'SCHEMA' as [Type]
	,s.name AS [Schema]
	,NULL as [Object]
	,NULL as [Column]

	,'--ALTER AUTHORIZATION ON SCHEMA::['+s.name+'] TO '
		+case when dp.name=@OldUser
			THEN quotename(Coalesce(@NewUser,dp.name)) 
			ELSE quotename(dp.name) 
		end
	+'; --Warning, a schema can have only 1 owner' as [Cmd]
	,2 as [SortOrder]
	from sys.schemas s
inner join sys.database_principals dp
on s.principal_id=dp.principal_id

--Object Ownership

INSERT INTO #Perms
select 

	'Object OWNERSHIP' as [Class]
	,dp.name as [Principal]
	,case when dp.name=@OldUser
		THEN Coalesce(@NewUser,dp.name)
		ELSE dp.name
	end as [NewPrincipal]
	,NULL as [Role]
	,dp.type_desc
	,dp.is_fixed_role
	,NULL as [State]
	,NULL as [Permission]
	,'OBJECT' as [Type]
	,s.name AS [Schema]
	,o.name as [Object]
	,NULL as [Column]

	,'--ALTER AUTHORIZATION ON OBJECT::'+quotename(s.name)+'.'+quotename(o.name)+' TO '
		+case when dp.name=@OldUser
			THEN quotename(Coalesce(@NewUser,dp.name)) 
			ELSE quotename(dp.name) 
		end
	+'; --Warning, an object can have only 1 owner' as [Cmd]
	,3 as [SortOrder]
	from sys.objects o
inner join sys.database_principals dp
on o.principal_id=dp.principal_id
inner join sys.schemas s
on o.schema_id=s.schema_id
WHERE o.type IN('U','TT','FN','IF','P','TF','V')

--Role Memberships
INSERT INTO #Perms
select
'ROLE MEMBERSHIP' as Class
	,p2.name as Principal
	,case when p2.name=@OldUser
		THEN Coalesce(@NewUser,p2.name)
		ELSE p2.name
	end as [NewPrincipal]
	,p1.name as [Role]
	,p2.type_desc
	,p2.[is_fixed_role]
	,NULL as [State]
	,NULL as [Permission]
	,'ROLE' as [Type]
	,NULL as [Schema]
	,NULL as [Object]
	,NULL as [Column]
	,CASE when p1.is_fixed_role=1
	THEN ''
	ELSE
	'IF NOT EXISTS (select 1 from sys.database_principals where type=''R'' and name='''+p1.name+''') CREATE ROLE '+quotename(p1.name)+';'+char(13)+char(10)
	END
	+
	CASE
		WHEN @sqlvers >=110
			THEN 'ALTER ROLE '+quotename(p1.name,']')+' ADD MEMBER '+
			case when p2.name=@OldUser
		THEN quotename(Coalesce(@NewUser,p2.name)) 
		ELSE quotename(p2.name) 
	end 
			+';'
		ELSE 'EXEC sp_addrolemember N'''+p1.name+''', N'''+
		case when p2.name=@OldUser
		THEN Coalesce(@NewUser,p2.name)
		ELSE p2.name
	end 
		+''';'
	END as [Cmd]
	,4 as [SortOrder]
from sys.database_role_members rm
	left join sys.database_principals p1
		on rm.role_principal_id=p1.principal_id
	left join sys.database_principals p2
		on rm.member_principal_id=p2.principal_id

;with RecursiveCTE as(

	--Base Member
	SELECT
		 [Class]
		,[Principal]
		,[NewPrincipal]
		,[Role]
		,[State]
		,[Permission]
		,[Type]
		,[Schema]
		,[Object]
		,[Column]

		,[Cmd]
		,[SortOrder]
		,[is_fixed_role]
		,0 as [Level]
	 from #Perms where [Principal]=@OldUser

	UNION ALL
	Select
	 [Perms].[Class]
	,[Perms].[Principal]
	,[Perms].[NewPrincipal]
	,[Perms].[Role]
	,[Perms].[State]
	,[Perms].[Permission]
	,[Perms].[Type]
	,[Perms].[Schema]
	,[Perms].[Object]
	,[Perms].[Column]

	,[Perms].[Cmd]
	,[Perms].[SortOrder]
	,[Perms].[is_fixed_role]
	,[Level]+1

	FROM #Perms Perms
		INNER JOIN RecursiveCTE RCTE on Perms.Principal=RCTE.[Role]

 )

 SELECT 
	 [Level]
	,[Class]
	,[Principal]
	,[NewPrincipal]
	,[Role]
	,[State]
	,[Permission]
	,[Schema]
	,[Object]
	,[Column]
	,[Cmd]
	--,[Type]
	--,[SortOrder]
	--,is_fixed_role

FROM RecursiveCTE
WHERE is_fixed_role=0
ORDER BY [Level], [Principal], [SortOrder], [Class], [Type], [State] DESC, [Permission], [Schema], [Object], [Column], [Role]

IF OBJECT_ID('tempdb..#Perms') IS NOT NULL
BEGIN	
	DROP TABLE #Perms
END

Remember, like many of my scripts, this script doesn’t change anything; rather it generates TSQL commands you can choose to run.

Other Considerations

While this script does not generate a user’s effective permissions, it can help you to reverse-engineer them.  It works on the level of the database user.  This works fine if you have a named database user.  If you are inheriting your rights through a single AD group membership, you can still ascertain this information by supplying the database user for that AD group as the @OldUser.  What happens if you are a named user in the database, but are also granted certain privileges through a number of different AD groups?  Well, that’s where it gets more difficult.  One trick I like to employ is to use two catalog views, sys.login_token and sys.user_token.  Both views are similar, but the first works at the server login level, whereas the second works at the database user level.

These two views can help you to unravel your AD group memberships.  For the current user, they display the role memberships and the Windows group memberships of the user.  In the case of a domain environment, they connect to AD and actually enumerate all your domain security groups.  If a particular group actually matches a server login or database user, this view will also show you the principal_id you can use to join it back to sys.server_principals (in the case of sys.login_token) or sys.database_principals (in the case of sys.user_token).

I hope you’re beginning to see how powerful this is 🙂 .

This works great if you were to want to troubleshoot your own permissions.  How though, as a DBA, would you be able to use this to troubleshoot a different account’s permissions?  You can have a user run these queries, as themselves, and return the output to you… or, as a DBA, you can use the power of IMPERSONATE.  If you are the DBA (or otherwise have impersonation rights on another account), you can run any command as that user.  Be aware that this may violate security policies at your company, and could be ill-advised in certain security-sensitive environments.  Also, if you have any type of auditing enabled, chances are good that you will leave footprints all over your audit logs that you are running commands on behalf of another user.  This may or may not be a problem for you.  That being said, if you determine that it’s acceptable to do, you can run a command such as:

use Adventureworks;
GO

execute as login='DOMAIN\User'
	select * from sys.login_token where principal_id <> 0
	select * from sys.user_token where principal_id <> 0
revert

This will show you [DOMAIN\User]’s login token for the server and user token for the Adventureworks database.  Using this technique in conjunction with the User Clone script can go a long way to managing and troubleshooting database permissions.

 

The full script can be found here: UserClone.txt.

Please let me know if you found this script useful or if you have any suggestions to improve it.

LLAP,
Ryan


As always, please note the waive of liability published on the About page.

The text of this entire blog site (including all scripts) is under the copyright of Ryan Snee. You are free to reproduce it, as outlined by the Creative Commons Attribution-ShareAlike 3.0 Unported License.

Files and Freespace

UPDATE 2014/02/01: Script is now version 1.2 to accommodate CS collations

Happy new year, everyone! With this post, I will be sharing a script I wrote to examine how many files you have for an instance, where they are, how big they are, and how full they are. It will also help you to reclaim some space, if that’s your goal. The script outputs 1 row per file.

DISCLAIMER: I am not advocating you shrink the free space out of your files.  Generally, having free space is a good thing.  Also, there are negative consequences of doing a file-shrink.

My advice

  • Disable auto-shrink!
  • Don’t use this, or any other mechanism, to otherwise routinely shrink files.
  • Use it as the far exception, not the rule.
  • Try to plan ahead, size your files, your drives, etc.
  • Understand the side-effects of shrinking a file!
    • This script will help give you information which will help you make a better decision.
    • There may be non-technical factors that influence  your decision on what to shrink or not shrink.
  • Sometimes life happens… you may find that, in full awareness of the consequences, shrinking a file is the best option.

Perhaps I will blog about the effects of a fileshrink in a future post… Otherwise, there are many, many other posts on the matter.  A quick web-search will get you started.  I recommend Brent Ozar’s or Paul Randal’s, in particular.  Bottom line: it’s really bad for performance; don’t do it unless you understand the costs and they are outweighed by the benefits.  You’re the professional, and you’ll have to make that call, ultimately.

And that’s all I will say about that.

This script can also be used to snapshot space usage and trend it over time.  There are a lot of things you probably want to remove if you’ll be doing that… but there’s a lot of useful data there.

Highlights of the Script

  • Supports Filestream/FileTable, Database Snapshots
  • Understands Filegroups
  • Visual representation (careful, it is normalized)
  • Generates convenient commands you can choose to run
  • Displays VLF info
  • Written for SQL 2012… works in 2008 with minor tweaks
  • Converts sizes to human-readable format (but preserves raw bytes for sorting, filtering, etc)

Implementation

3 primary DMOs:

  • sys.master_files
    • I prefer this DMV… contains info for all DBs in one place
    • Only way to get info for offline databases
    • tempdb shows its initial size here, not its current size
    • For user databases, I’ve seen times when this was inaccurate
  • sys.database_files
    • One view for each database
    • Only way to get info for Filestream data
  • sys.dm_io_virtual_file_stats
    • DMF… can’t use CROSS APPLY prior to SQL 2012
    • Only way to get true size, on disk, for database snapshots (NTFS sparse file)

Credit where Credit is Due

A big thanks to Ken Simmons and Tim Ford (cited at the bottom of this post) for their blog posts on the same topic. I derived many ideas and some coding techniques from their examples.

Output Sample

FilesOutput

Additional columns:

FilesOutput2

And finally:

FilesOutput3

The entire result set can be found here:

FilesOutputFull

The Script

/*
* Author: Ryan Snee
* Version: 1.2
* Date 20140201
* URL: http://sqlsnee.com/r/spaceused
*
* With inspiration from Tim Ford and Ken Simmons
* Tim's Original Script: http://www.mssqltips.com/sqlservertip/1629/determine-free-space-consumed-space-and-total-space-allocated-for-sql-server-databases/
* Ken's Original Script: http://www.mssqltips.com/sqlservertip/1510/script-to-determine-free-space-to-support-shrinking-sql-server-database-files/
*
* License: This work is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.
* License Details: http://creativecommons.org/licenses/by-sa/3.0/deeD.en_US
*
* LIMITATIONS
*    No support for Hekaton
*    Modifications needed to work on SQL 2008 R2
*
* Does handle:
*    Snapshots
*    Filestream
*    Filegroups
*    Offline databases (partial support)
*
* Grabs Virtual Log File count
*
***************************
** Change History
**************************
** Version		Date			Author		Description	
** -------		--------		-------		--------------------------------------------------------------------------------------------------------------------
** 01.0			2013/12/27		Ryan		v1 Published
** 01.1			2014/02/01		Ryan		Fixed to work in a database with a CS collation

*
*/

SET NOCOUNT ON;
DECLARE
	@LoopSafetyBefore int,
	@LoopSafetyAfter int,
	@sql nvarchar(max),
	@dbName sysname,
	@dbID int,
	@PrintMsg varchar(100),
	@Debug bit;

Set @Debug=0;

IF OBJECT_ID('tempdb..#DBs') IS NOT NULL
BEGIN
	DROP TABLE #DBs	
END

IF OBJECT_ID('tempdb..#FileGroup') IS NOT NULL
BEGIN	
DROP TABLE #FileGroup
END

IF OBJECT_ID('tempdb..#TMPSPACEUSED') IS NOT NULL
BEGIN	
DROP TABLE #TMPSPACEUSED
END

IF OBJECT_ID('tempdb..#LogInfo') IS NOT NULL
BEGIN
	DROP TABLE #LogInfo
END

CREATE TABLE #TMPSPACEUSED (
	 database_id		int
	,DBNAME				sysname
	,SPACEUSED			FLOAT
	,size				bigint
	,data_space_id		int
	,FileID				int
	,[type]				tinyint
	,[type_desc]		nvarchar(60)
	,Name				sysname
	,physical_name		nvarchar(260)
	,growth				bigint
	,is_percent_growth	bit
	,[max_size]			bigint
	,is_read_only		bit
	,[VLFs]				int				NULL

);

CREATE TABLE #LogInfo(
	RecoveryUnitID int,
	FileId tinyint,
	FileSize bigint,
	StartOffset bigint,
	FSeqNo int,
	Status tinyint,
	Parity tinyint,
	CreateLSN numeric(25,0)
);

create table #DBs (id int primary key not null, dbname sysname, is_distributor bit, state tinyint, user_access tinyint, is_read_only bit, source_database_id int NULL, isDone bit default 0)
create table #FileGroup(id int identity(1,1) primary key not null, DatabaseID int not null, data_space_id int, name sysname, is_read_only bit)

INSERT INTO #DBs (id, dbname, is_distributor, state, user_access, is_read_only, source_database_id, isDone)
select sdb1.database_id, sdb1.name, sdb1.is_distributor, sdb1.state, sdb1.user_access, sdb1.is_read_only, sdb1.source_database_id, 0
	from sys.databases sdb1
	left join sys.databases sdb2
	on sdb1.source_database_id=sdb2.database_id
	where 1=1
		AND sdb1.state=0 --Online
		AND sdb1.user_access =0
		AND sdb1.source_database_id is null OR (sdb2.state=0 and sdb2.user_access=0 )

WHILE EXISTS (SELECT dbname FROM #DBs WHERE isDone=0)
BEGIN
	SET @dbName= NULL
	SET @dbID= NULL

	select @LoopSafetyBefore=count(*) FROM #DBs WHERE isDone=0;

	--Do work here
	select top 1 @dbName= dbname, @dbID=id from #DBs WHERE isDone=0 order by dbname
	set @PrintMsg='--Scanning '+quotename(@dbName,']')

	if(@Debug=1)
	RAISERROR(@PrintMsg,0,0) WITH NOWAIT

	SET @sql=nchar(9) + N'
	USE '+QUOTENAME(@dbName)+';
	INSERT INTO #FileGroup (DatabaseID, data_space_id, name, is_read_only)
	select '+cast(@dbId as nvarchar(10))+', data_space_id, name, is_read_only from '+quotename(@dbName)+'.[sys].[filegroups];

	INSERT INTO #TMPSPACEUSED ([database_id], [DBNAME], [SPACEUSED], [size], [data_space_id], [FileID], [type], [type_desc], [Name], [physical_name], [growth], [is_percent_growth], [max_size], [is_read_only])
	Select '+cast(@dbID as varchar(50))+', '''+@DBName+''' as DBName, fileproperty(Name,''SpaceUsed'') SpaceUsed, size, data_space_id, file_ID, type, type_desc, Name, physical_name, growth, is_percent_growth, [max_size], is_read_only from '+quotename(@dbName)+'.[sys].[database_files]

	TRUNCATE TABLE #LogInfo;

	insert into #LogInfo
	exec(''dbcc loginfo'');

	update t
	SET t.[VLFs]=
	(select count(*) from #LogInfo LI
	where LI.FileID=t.FileID and t.database_id='+cast(@dbID as varchar(50))+')
	FROM #TMPSPACEUSED t
	WHERE [t].[database_id]='+cast(@dbID as varchar(50))+'	
			' 

	if(@Debug=1)
	print @sql

	--Execute our statement
	exec sp_executesql @sql;			

	UPDATE TOP (1) #DBs SET isDone=1 WHERE dbname=@dbName

	select @LoopSafetyAfter =count(*) FROM #DBs WHERE isDone=0;

	if(@LoopSafetyBefore<=@LoopSafetyAfter)
	BEGIN
		RAISERROR('Probable infinite Loop in databases',11,1) WITH NOWAIT
		BREAK
	END

END

;WITH a as(
SELECT 
	 vs.volume_mount_point AS [Drive]
	,vs.total_bytes as [DriveSize]
	,cast((cast(vs.available_bytes as decimal(32,4))/(cast(vs.total_bytes as decimal(32,4))))*100 as decimal(18,2)) as [VolumePercentFree]
	,vs.available_bytes as [DriveFreeBytes]
	,DB.database_id,
	[DB].NAME AS [Database],

	[FG].name AS [FileGroup],
	coalesce([D].NAME, [MF].[Name]) AS [LogicalFileName],
    CASE [MF].[type]
		WHEN 0 THEN 'DATA'
		ELSE [MF].[type_desc]
	END AS [FileType]

	--master_files seems like inaccurate some times.  Doesn't reflect true tempdb size, but rather its initial size
	--database_files or io_virtual_file_stats seem best, in general.  For Filestream, data_files is the only way.
	--For snapshots, io_virtual_file_stats is the best way to get the true size, on disk
	--master_files is the only option if the database is offline, in single_user mode, or otherwise unavailable

	,CASE 
		WHEN [MF].[type] = 2 --FileStream
			THEN ([D].size*8192)
		WHEN DB.source_database_id is not null
			THEN iovfs.size_on_disk_bytes
		ELSE --Everything else
			coalesce(iovfs.size_on_disk_bytes,(cast([MF].[size] as bigint)*8192))
	END AS [FileSizeBytes]

	,DB.source_database_id
	,coalesce([D].[type], [MF].[type]) as [type]
	,[D].[type_desc]
	,CASE
		WHEN (DB.source_database_id IS NOT NULL) OR ([D].[type]=2) THEN cast(0 as decimal(18,2))
		ELSE CAST(([D].size-[D].spaceused)*8192 AS DECIMAL(18,2))
	END AS [SpaceFree]
	,CASE
		WHEN (DB.source_database_id IS NOT NULL) THEN cast(iovfs.size_on_disk_bytes  as decimal(18,2))
		WHEN ([MF].[type]=2) THEN ([D].size*8192)
		ELSE CAST(([D].spaceused)*8192 AS DECIMAL(18,2)) 
	END AS [SpaceUsed]

	,CASE
		WHEN (DB.source_database_id IS NOT NULL) OR ([D].[type]=2) THEN cast(0 as decimal(18,2))
		ELSE CAST(([D].size - D.SPACEUSED )*100.00/([D].size) AS decimal(9,2))
	END AS [FilePercentFree]
	,CASE 
		WHEN (DB.source_database_id IS NOT NULL) OR ([MF].[type]=2) 
			THEN 'N/A'
		WHEN coalesce([D].is_percent_growth,[MF].[is_percent_growth]) =0
			THEN CAST((coalesce([D].[growth], [MF].[growth]) *8/1024) AS VARCHAR(30))+' MB'
		WHEN coalesce([D].is_percent_growth,[MF].[is_percent_growth]) =1
			THEN CAST(coalesce([D].[growth], [MF].[growth]) AS VARCHAR(30))+'%'
	END	AS [GrowAt]

	,CASE
		WHEN DB.source_database_id IS NOT NULL --DB is a snapshot.  Per BOL, use [D].SIZE to get max snapshot size
			THEN (coalesce([D].[size], cast([MF].[size] as bigint))*8192)
		WHEN ((coalesce([D].[max_size],[MF].[max_size])=0) AND ([D].[type]<>2)) --Autogrow is disabled.  File is as big as it's going to get.
			THEN coalesce(iovfs.size_on_disk_bytes,(cast([MF].[size] as bigint)*8192))
		WHEN coalesce([D].[max_size],[MF].[max_size])=-1 --No limit
			THEN NULL
		WHEN coalesce([D].[max_size],[MF].[max_size])=268435456 --Max size possible in SQL Server
			THEN NULL
		ELSE			
			(coalesce([D].[max_size],[MF].[max_size])*8192)
	END AS [MaxSizeBytes],

	CASE
		WHEN vs.available_bytes is null --We can't even get volume info for inaccessable databases
			THEN null

		WHEN ((DB.source_database_id IS NOT NULL) and ([D].[type]<> 2) and (([D].[size]*8192) > (vs.available_bytes + iovfs.size_on_disk_bytes))) --DB is a snapshot.  Per BOL, use [D].SIZE to get max snapshot size. Here, we've over-provisioned.  Number below will be negative.
			THEN ((vs.available_bytes + iovfs.size_on_disk_bytes)-([D].[size]*8192))
		WHEN ((DB.source_database_id IS NOT NULL) and ([D].[type]<> 2) and (([D].[size]*8192) <= (vs.available_bytes + iovfs.size_on_disk_bytes))) --DB is a snapshot. Per BOL, use [D].SIZE to get max snapshot size.
			THEN (cast([D].[size] as bigint)*8192)
		WHEN (([D].[max_size]=0) AND ([D].[type]<>2)) --Autogrow is disabled.  File is as big as it's going to get.
			THEN coalesce(iovfs.size_on_disk_bytes,(cast([MF].[size] as bigint)*8192))
		WHEN (([D].[max_size])=-1 OR ([D].[max_size]=268435456)) --No limit/Max size possible in SQL Server
			THEN
				CASE
					WHEN ([D].[type]=2) --When it's a filestream file, use database_files + availble bytes on the volume
						THEN
							(vs.available_bytes + ([D].size*8192))
				ELSE
					(vs.available_bytes + coalesce(iovfs.size_on_disk_bytes,(cast([MF].[size] as bigint)*8192)))

				END
		ELSE	
			CASE
				WHEN (([D].[type]<> 2) and (([D].[max_size]*8192) > (vs.available_bytes + coalesce(iovfs.size_on_disk_bytes,(cast([MF].[size] as bigint)*8192))))) --Not Filestream and the max size > current size + freespace on the volume
					THEN (vs.available_bytes + coalesce(iovfs.size_on_disk_bytes,(cast([MF].[size] as bigint)*8192)))
				WHEN (([D].[type]=2) AND (([D].[max_size]*8192) > (vs.available_bytes + ([D].size*8192)))) --Filestream and the max size > current size + freespace on the volume
					THEN vs.available_bytes + ([D].size*8192)
				ELSE
					([D].[max_size]*8192)
				END
	END AS [EffectiveMaxBytes],
	[DB].recovery_model_desc AS [RecoveryModel],
	coalesce([D].PHYSICAL_NAME, [MF].[Physical_Name]) AS [PhysicalName],
	CASE 
		WHEN [DB].name='tempdb'					THEN	'--Don''t Shrink tempdb Online.  It is known to cause corruption.  Must start SQL in Single-User Mode.'
		WHEN [DB].name='model'					THEN	'--Don''t shrink model'
		WHEN [DB].name='master'					THEN	'--Don''t shrink master'
		WHEN [DB].name='msdb'					THEN	'--Don''t shrink msdb'
		WHEN [DB].[state]<>0					THEN	'--Database is not ''Online'''
		WHEN [DB].[is_read_only]=1				THEN	'--Database is read_only and cannot be modified'
		WHEN DB.source_database_id IS NOT NULL	THEN	'--Can''t shrink a snapshot'
		WHEN [FG].[is_read_only]=1				THEN	'--Filegroup is read-only'
		WHEN [D].[type]=2						THEN	'use '+quotename([DB].[Name])+';'+char(13)+char(10)+'CHECKPOINT;'+char(13)+char(10)+'GO'+char(13)+char(10)+'sp_filestream_force_garbage_collection  @dbname = '''+[DB].[Name]+'''  , @filename = '''+[D].[Name]+''';'
		ELSE 
			CASE
				WHEN [D].type_desc='LOG' --If it's a log file, shrink it as much as possible
					THEN 'USE ['+[DB].name+']; DBCC SHRINKFILE (N'''+[D].name+''' , 1);'
				WHEN (max(cast([D].spaceused as bigint)) OVER(PARTITION BY [FG].name, DB.database_id, [D].[type]))>=[D].size --If the biggest amount of space used (out of all the files in the filegroup) is larger than our file is, we are not going to GROW the file
					THEN '--Not going to grow our file'
				ELSE 'USE ['+[DB].name+']; DBCC SHRINKFILE (N'''+[D].name+''' , '+cast(ceiling((max(cast([D].spaceused as bigint)) OVER(PARTITION BY [FG].name, DB.database_id, [D].[type]) * 8 / 1024.0)) as nvarchar(100))+');'  --Otherwise, shrink all files in the filegroup to the greatest number of MB used (out of all the files in the filegroup)
			END
	END AS [ShrinkCmd],

	CASE 
		WHEN [DB].name='tempdb'					THEN	'--Don''t change tempdb'
		WHEN [DB].name='model'					THEN	'--Don''t change model'
		WHEN [DB].name='master'					THEN	'--Don''t change master'
		WHEN [DB].name='msdb'					THEN	'--Don''t change msdb'
		WHEN [DB].name='dba_admin'				THEN	'--Don''t change dba_admin'
		WHEN [DB].[state]<>0					THEN	'--Database is not ''Online'''
		WHEN DB.source_database_id IS NOT NULL	THEN	'--Can''t change a snapshot'
		WHEN [D].[type]=2						THEN	'--Filestream has no autogrow'
		WHEN [DB].[is_read_only]=1				THEN	'--Database is read_only and cannot be modified'

		ELSE 
			CASE [D].type
			WHEN 0
			THEN 
				CASE
					WHEN [D].growth=65536 AND [D].is_percent_growth=0
						THEN ''
						ELSE 'ALTER DATABASE ['+[DB].name+'] MODIFY FILE ( NAME = N'''+[D].name+''', FILEGROWTH = 524288KB )'
				END
			ELSE 
				CASE WHEN [D].growth=32768 AND [D].is_percent_growth=0
					THEN ''
					ELSE 'ALTER DATABASE ['+[DB].name+'] MODIFY FILE ( NAME = N'''+[D].name+''', FILEGROWTH = 262144KB )'
				END
			END

	END AS [AutoGrowth]
	,CASE
		WHEN [DB].name='tempdb'					THEN	'--Don''t change tempdb'
		WHEN [DB].name='model'					THEN	'--Don''t change model'
		WHEN [DB].name='master'					THEN	'--Don''t change master'
		WHEN [DB].name='msdb'					THEN	'--Don''t change msdb'
		WHEN [DB].[state]<>0					THEN	'--Database is not ''Online'''
		WHEN DB.source_database_id IS NOT NULL	THEN	'--Can''t change a snapshot'
		WHEN [DB].[is_read_only]=1				THEN	'--Database is read_only and cannot be modified'

	ELSE
		CASE 
			WHEN ([DB].recovery_model=3 AND [D].FileID= min(D.FileID) OVER(PARTITION BY DB.database_id)) THEN 'ALTER DATABASE ['+[DB].name+'] SET RECOVERY FULL'
			WHEN ([DB].recovery_model=1 AND [D].FileID= min(D.FileID) OVER(Partition BY DB.database_id)) THEN 'ALTER DATABASE ['+[DB].name+'] SET RECOVERY SIMPLE'
			ELSE ''
		END
	END AS [RecoveryModelCmd]
	,[DB].log_reuse_wait_desc
	,CASE
		WHEN [MF].[type]=1
			THEN [D].[VLFs]
		ELSE 0
	END AS [VLFs]

	,CASE 
	WHEN [DB].name='tempdb'					THEN	cast(0 as bigint)
	WHEN [DB].name='model'					THEN	cast(0 as bigint)
	WHEN [DB].name='master'					THEN	cast(0 as bigint)
	WHEN [DB].name='msdb'					THEN	cast(0 as bigint)
	WHEN DB.source_database_id IS NOT NULL	THEN	cast(0 as bigint)
	WHEN [D].[type]=2						THEN	cast(0 as bigint)

	WHEN [DB].[is_read_only]=1				THEN	cast(0 as bigint)
	WHEN [DB].[state]<>0					THEN	cast(0 as bigint)
	WHEN [FG].[is_read_only]=1				THEN	cast(0 as bigint)

	ELSE 
		CASE
			WHEN [D].type_desc='LOG' --If it's a log file, shrink it as much as possible
				THEN [D].size-[D].spaceused  --Not neccessarily, but a good guess
			WHEN (max(cast([D].spaceused as bigint)) OVER(PARTITION BY [FG].name, DB.database_id, [D].[type]))>=[D].size --If the biggest amount of space used (out of all the files in the filegroup) is larger than our file is, we are not going to GROW the file
				THEN 0
			ELSE [D].size-(max(cast([D].spaceused as bigint)) OVER(PARTITION BY [FG].name, DB.database_id, [D].[type]))
		END
	END AS [ReclaimablePages]
FROM     sys.databases [DB]

				 inner JOIN sys.master_files [MF]
           ON [DB].database_id= [MF].database_id

         left JOIN #TMPSPACEUSED D
           ON [DB].database_id = D.database_id
		   AND [D].FileID = [MF].[file_id] 
		outer APPLY sys.dm_os_volume_stats([DB].database_id, [MF].[File_ID]) vs
		left join #FileGroup FG
		on [D].data_space_id=[FG].data_space_id
		and DB.database_id=[FG].DatabaseID
		outer apply sys.dm_io_virtual_file_stats([DB].database_id, [D].[FileID]) iovfs	

), b as (

select 

	[a].[Drive] as [MountPoint]

	,CASE 
		WHEN (a.DriveSize) > 1073741824000 THEN CAST(CAST(((a.DriveSize) / 1024.0/1024.0/1024.0/1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' TB'
		WHEN (a.DriveSize) > 1048576000 THEN CAST(CAST(((a.DriveSize) / 1024.0/1024.0/1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' GB'
		ELSE CAST(CAST(((a.DriveSize) / 1024.0/1024) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' MB'
	END AS [DriveSize]

	,CASE
		WHEN ([a].[DriveFreeBytes]) > 1073741824000 THEN CAST(CAST((([a].[DriveFreeBytes]) / 1024.0/1024.0/1024.0/1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' TB'
		WHEN ([a].[DriveFreeBytes]) > 1048576000 THEN CAST(CAST((([a].[DriveFreeBytes]) / 1024.0/1024/1024) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' GB'
		ELSE CAST(CAST((([a].[DriveFreeBytes]) / 1024.0/1024) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' MB'
	END AS [DriveFreeSpace]
	,nchar(9621)+replicate(nchar(9608),floor((100-VolumePercentFree)/10))+replicate(nchar(9601),ceiling(VolumePercentFree/10))+nchar(9615) as [VolumePercentUsed]
	,[a].[database_id] as [DatabaseID]
	,[a].[Database] as [DatabaseName]
	 ,CASE
		WHEN (sum([a].[FileSizeBytes]) OVER(PARTITION BY [a].[database_id])) > 1073741824000 THEN CAST(cast((((sum([a].[FileSizeBytes]) OVER(PARTITION BY [a].[database_id])) /1024.0 /1024.0/1024.0/1024.0)) as decimal(18,2)) AS VARCHAR(20)) + ' TB'
		WHEN (sum([a].[FileSizeBytes]) OVER(PARTITION BY [a].[database_id])) > 1048576000 THEN CAST(cast((((sum([a].[FileSizeBytes]) OVER(PARTITION BY [a].[database_id])) /1024.0/1024.0/1024.0)) as decimal(18,2)) AS VARCHAR(20)) + ' GB'
		ELSE CAST(cast((((sum([a].[FileSizeBytes]) OVER(PARTITION BY [a].[database_id])) /1024.0 / 1024.0)) as decimal(18,2)) AS VARCHAR(20)) + ' MB'

	END AS [DatabaseSize]
	,[FileGroup] as [FileGroupName]
	,[LogicalFileName]
	,[FileType]
	,CASE 
		WHEN ([a].[FileSizeBytes]) > 1073741824000 THEN CAST(CAST((([a].[FileSizeBytes]) / 1024.0/1024.0/1024.0/1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' TB'
		WHEN ([a].[FileSizeBytes]) > 1048576000 THEN CAST(CAST((([a].[FileSizeBytes]) / 1024.0/1024.0/1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' GB'
		ELSE CAST(CAST((([a].[FileSizeBytes]) / 1024.0/1024) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' MB'
	END AS [FileSize]

	,CASE WHEN (a.source_database_id IS NOT NULL) OR ([a].[type]=2) THEN 'N/A'
		WHEN ([a].[SpaceFree]) > 1073741824000 THEN CAST(CAST((([a].[SpaceFree]) / 1024.0/1024.0/1024.0/1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' TB'
		WHEN ([a].[SpaceFree]) > 1048576000 THEN CAST(CAST((([a].[SpaceFree]) / 1024.0/1024.0/1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' GB'
		ELSE CAST(CAST((([a].[SpaceFree]) / 1024.0/1024) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' MB'
	END as [SpaceFree]

	,CASE
		WHEN ([a].[SpaceUsed]) > 1073741824000 THEN CAST(CAST((([a].[SpaceUsed]) / 1024.0/1024.0/1024.0/1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' TB'
		WHEN ([a].[SpaceUsed]) > 1048576000 THEN CAST(CAST((([a].[SpaceUsed]) / 1024.0/1024.0/1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' GB'
		ELSE CAST(CAST((([a].[SpaceUsed]) / 1024.0/1024) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' MB'
	END as [SpaceUsed]

	,CASE WHEN (a.source_database_id IS NOT NULL) OR ([a].[type]=2) THEN 'N/A'
		ELSE nchar(9621)+replicate(nchar(9608),floor((100-FilePercentFree)/10))+replicate(nchar(9601),ceiling(FilePercentFree/10))+nchar(9615)
	END as [FilePercentUsed]
	,[a].[GrowAt]		

	,CASE
		WHEN ([a].[MaxSizeBytes]) > 1073741824000 THEN CAST(CAST((([a].[MaxSizeBytes]) / 1024.0/1024.0/1024.0/1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' TB'
		WHEN ([a].[MaxSizeBytes]) > 1048576000 THEN CAST(CAST((([a].[MaxSizeBytes]) / 1024.0/1024.0/1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' GB'
		ELSE CAST(CAST((([a].[MaxSizeBytes]) / 1024.0/1024) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' MB'
	END as [MaxSize]

	,CASE
		WHEN ([a].[EffectiveMaxBytes]) > 1073741824000 THEN CAST(CAST((([a].[EffectiveMaxBytes]) / 1024.0/1024.0/1024.0/1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' TB'
		WHEN ([a].[EffectiveMaxBytes]) > 1048576000 THEN CAST(CAST((([a].[EffectiveMaxBytes]) / 1024.0/1024.0/1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' GB'
		ELSE CAST(CAST((([a].[EffectiveMaxBytes]) / 1024.0/1024) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' MB'
	END as [EffectiveFileMax]

	,nchar(9621)+replicate(nchar(9608),floor((CAST(100*((cast(a.SpaceUsed as decimal(18,2)))/(cast(a.EffectiveMaxBytes as decimal(18,2)))) AS decimal(18,2)))/10))+replicate(nchar(9601),ceiling((100-(CAST(100*((cast(a.SpaceUsed as decimal(18,2)))/(cast(a.EffectiveMaxBytes as decimal(18,2)))) AS decimal(18,2))))/10))+nchar(9615) as [UsedPercentOfEffectiveMax]

	,CASE
		WHEN ([a].[EffectiveMaxBytes]-[a].[SpaceUsed]) > 1073741824000 THEN CAST(CAST((([a].[EffectiveMaxBytes]-[a].[SpaceUsed]) / 1024.0/1024.0/1024.0/1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' TB'
		WHEN ([a].[EffectiveMaxBytes]-[a].[SpaceUsed]) > 1048576000 THEN CAST(CAST((([a].[EffectiveMaxBytes]-[a].[SpaceUsed]) / 1024.0/1024.0/1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' GB'
		ELSE CAST(CAST((([a].[EffectiveMaxBytes]-[a].[SpaceUsed]) / 1024.0/1024) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' MB'
	END as [AvailableSpaceToUse]

	,[a].[PhysicalName]
	,[a].[ShrinkCmd]
	,[a].[AutoGrowth]
	,[a].[RecoveryModel]
	,[a].[RecoveryModelCmd]
	,CASE
		WHEN [a].[type]=1
			THEN [a].[log_reuse_wait_desc]
		ELSE ''
	END AS [LogReuseWait]
	,CASE
		WHEN (([a].[type]=1) and ([a].[VLFs] is not null))
			THEN cast([a].[VLFs] as varchar(50))
		WHEN (([a].[type]=1) and ([a].[VLFs] is null))
			THEN 'unknown'
		ELSE ''
	END AS [VLFCount]
	,sum(ReclaimablePages) OVER(PARTITION BY a.database_id, a.FileGroup) as [ReclaimableInTheFilegroup]

	--Raw data
	,a.DriveSize as [drive_size]
	,[a].[DriveFreeBytes] as [drive_free_bytes]
	,sum([a].[FileSizeBytes]) OVER(PARTITION BY [a].[database_id]) as [db_size]
	,[a].[FileSizeBytes] as [file_size_bytes]
	,CASE
		WHEN (a.source_database_id IS NOT NULL) OR ([a].[type]=2) THEN NULL
			ELSE a.SpaceFree
		END as [space_free]
	,[a].SpaceUsed as [space_used]
	,[a].[MaxSizeBytes] as [max_size_bytes]
	,[a].[EffectiveMaxBytes] as [effective_max_bytes]
	,ReclaimablePages

from a
)

select 
	 [MountPoint]
	,[DriveSize]
	,[DriveFreeSpace]
	,[VolumePercentUsed]
	,[DatabaseID]
	,[DatabaseName]
	,[DatabaseSize]
	,[FileGroupName]
	,[LogicalFileName]
	,[FileType]
	,[FileSize]
	,[SpaceFree]
	,[SpaceUsed]
	,[FilePercentUsed]
	,[GrowAt]
	,[MaxSize]
	,[EffectiveFileMax]
	,[UsedPercentOfEffectiveMax]
	,[AvailableSpaceToUse]
	,[PhysicalName]
	,[ShrinkCmd]
	,[AutoGrowth]
	,[RecoveryModel]
	,[RecoveryModelCmd]
	,[LogReuseWait]
	,[VLFCount]

	--Other columns, not displayed, but useful for filtering/ordering:
		--,[ReclaimableInTheFilegroup]
		--,[drive_size]
		--,[drive_free_bytes]
		--,[db_size]
		--,[file_size_bytes]
		--,[space_free]
		--,[space_used]
		--,[max_size_bytes]
		--,[effective_max_bytes]
		--,[ReclaimablePages]
from b
WHERE 1=1
	--AND MountPoint='G:\'
	--AND DatabaseName LIKE 'AdventureWor%'
	--AND DatabaseName not in ('master','model','tempdb','msdb')
	--AND RecoveryModel = 1 --1=Full
	--AND FileGroupName='PartitionFG9'

ORDER BY [ReclaimableInTheFilegroup] DESC, [DatabaseID] ASC, FileGroupName ASC

DROP TABLE #TMPSPACEUSED
DROP TABLE #DBs
DROP TABLE #FileGroup
DROP TABLE #LogInfo

 

“Filegroup Aware”

One of the things I’m most proud of with this script is actually easy to miss… the sort order.  I’m not an expert on filegroups, but based on my understanding of SQL’s proportional file-fill algorithm, you want to try to keep the files in a filegroup as close as possible to the same size.  As such, the script works as follows:

  1. It finds the file in each filegroup that’s using the most space
  2. The script attempts to shrink all files to that size
  3. No files are grown (that would defeat the purpose of freeing-up space)

Based on how much the script estimates can be reclaimed in the whole filegroup, the results are sorted (by filegroup), with the biggest space-savers on top.  If you aren’t using explicit filegroups, don’t worry- the files with the most free space will float to the top.

Below is a picture from my slide-deck to help you visualize the calculation:

 FilegroupAware

The green space is summed for the filegroup, and the filegroup with the most “free space” (calculated as described above) will be on top.  All the files in a filegroup will sort together.

Ratio Bar

The last thing I wanted to point out is visual ratio bar:

VisualRatioBar

It’s a normalization of a ratio, like a percentage, but with a resolution of 10 instead of 100.  Having 100 characters resulted in too much scrolling, so I opted for just 10.  I tried to find a word for this, but had no success.  There’s a word for a ratio normalized to 1,000 (permillage) and one for 10,000 (permyriad), but I found nothing for 10.  I wanted to call it a perdecage, but Google/Bing don’t seem to agree with me that it’s a real word.

As with any normalization, I wanted to point out that, while useful, it can be misleading.  A file with 1MB free of 10 MB will look the same as a file with 100GB out of 1 TB.  It’s not really a problem, but an observation.

For anybody wondering how I did it, here’s the magic line:

nchar(9621)+replicate(nchar(9608),floor((100-FilePercentFree)/10))+replicate(nchar(9601),ceiling(FilePercentFree/10))+nchar(9615)

For reference, the Unicode characters are as follows:

  • nchar(9621): “▕”
  • nchar(9608): “█”
  • nchar(9601): “▁”
  • nchar(9615): “▏”

 

So we have vertical bars on either end, and 10 characters between.. each either a full bar, or a “bottom 1/8 of a bar”, depending on the ratio.

Comparability with SQL 2008/2008 R2

There are two changes needed for the script to run in versions prior to 2012

  1. Wrapper function- you can’t use correlated parameters for DMFs like sys.dm_io_virtual_file_stats prior to 2012.  The workaround is to create a user-defined wrapper function for it.
  2. dbcc loginfo- A new column was added to its output in 2012, [RecoveryUnitId].  The definition of the temp table in the script, #LogInfo, must be modified to remove that column if running it pre-2012.

These changes can be found in the second script below.

 

The full script can be found here: SpaceUsage.txt.
And a SQL 2008 version: SpaceUsage2008.txt.

Please let me know if you found this script useful or if you have any suggestions to improve it.

LLAP,
Ryan

A few sources I’d like to cite for contributing ideas:


As always, please note the waive of liability published on the About page.

The text of this entire blog site (including all scripts) is under the copyright of Ryan Snee.  You are free to reproduce it, as outlined by the Creative Commons Attribution-ShareAlike 3.0 Unported License.

Moving a Set of Tables to a Read-Only Filegroup

This is my first post.  My goal with this post is to share how I moved a subset of the tables in my database to another filegroup.  Why would you want to do this, you ask?  A few possible use-cases:

  • You want to move a set of tables to a different drive
  • You like playing with SQL Server Files and Filegroups
  • You want to move some tables out-of a database, but are not ready to drop them from their source DB.

The third option was my reason for researching it, but obviously you may have other reasons yet.

My overall approach to the problem above was to:

  1. Create a new filegroup
  2. Create a file in that filegroup
  3. Move the tables, online where possible (SQL Server Enterprise is required), to the new filegroup
  4. Flip that filegroup to READ_ONLY mode… unfortunately, I found out while doing this that this step requires that nobody else be in the database… I ultimately flipped the database to SINGLE_USER mode after-hours.  This may not be an easy option for you.
  5. Using SSMS and the Import/Export Data Wizard (SSIS under-the-covers), I copied the data to its new destination where I had earlier prepared empty tables

I was only moving a sub-set of the tables… luckily for me, they all had the same prefix in their table name, so it was easy for me to grab them programmatically.  It’s important to note that running my script doesn’t do anything to your database… rather it generates TSQL commands for you to review and, optionally, run.

The Issue

I wanted to move the tables… every part of them.  I wanted my code to account for heaps, clustered indexes, and non-clustered indexes.  I found some very useful links (included before) which did part of what I wanted… but not everything.  I also wanted to accommodate as many index options as possible (and not lose settings like fill factor, filters, and ALLOW_TABLE_LOCK options).  This is my first attempt, and I’ve tried to note exceptions to what I’ve tested.

The Approach

I’m sure I’ll catch a lot of flack for the iterative nature of my script… I’d normally be the first one to offer criticism!  As it turns out, it’s very difficult to build a set of things into a single string (eg, building a set of index columns into a command).  I found a few alternatives (including a very clever use of the STUFF() function and some XML generation… but I felt that was unintuitive and it seemed “hacky” to me.  In the end, I make heavy use of WHILE loops.  In TSQL.  I know.  Please feel free to disagree with me on this, but this is the choice I made in the end.

The Demo Setup

CREATE DATABASE FileGroupTesting;
GO
USE FileGroupTesting;

--Heap
CREATE TABLE [aHeap](id int, name nvarchar(50))
--Heap w/NC
CREATE TABLE [aHeapWithNCs](id int, name nvarchar(50))
CREATE NONCLUSTERED INDEX [HeapsNC] ON [aHeapWithNCs](name desc)
--Heap w/PK
CREATE TABLE [heapWithPK](id int primary key NONCLUSTERED, name nvarchar(50))
--Clustered no PK
CREATE TABLE [ClusteredNoPK] (id int, name nvarchar(50))
CREATE CLUSTERED INDEX [PK_ClusteredNoPK] ON [ClusteredNoPK](id)
--Clustered no PK with NCs
CREATE TABLE [ClusteredNoPK2] (id int, name nvarchar(50))
CREATE CLUSTERED INDEX [PK_ClusteredNoPK2] ON [ClusteredNoPK2](id)
CREATE NONCLUSTERED INDEX [ClusteredNoPKNC] ON [ClusteredNoPK2] (name desc)

--Clustered with PK
CREATE TABLE [ClusteredPK] (id int primary key clustered, name nvarchar(50))

--Clustered with PK with NCs
CREATE TABLE [ClusteredPK2] (id int primary key clustered, name nvarchar(50))
CREATE NONCLUSTERED INDEX [IX_ClusteredPK2] ON [ClusteredPK2] (name, id desc)

--Heap w/Unique
CREATE TABLE [HeapWithUnique](id int , name nvarchar(50))
ALTER TABLE [HeapWithUnique] ADD CONSTRAINT  [UC_HeapWithUnique] UNIQUE NONCLUSTERED(name)
CREATE UNIQUE NONCLUSTERED INDEX [UC_HeapWithUnique2] ON [HeapWithUnique] (name)

--PK/Clustered w/Unique
CREATE TABLE [ClustWithUnique](id int , name nvarchar(50))
ALTER TABLE [ClustWithUnique] ADD CONSTRAINT [UC_ClustWithUnique] UNIQUE NONCLUSTERED(name)
CREATE UNIQUE NONCLUSTERED INDEX [UC_ClustWithUnique2] ON [ClustWithUnique] (name)

--PK/Clustered w/filter
CREATE TABLE [ClusteredFilter] (id int primary key clustered, name nvarchar(50))
CREATE NONCLUSTERED index [ix_clusteredfilter] on [ClusteredFilter](name)  WHERE Name > 'M'

--PK/clustered w/included
CREATE TABLE [ClusteredIncluded] (id int primary key clustered, name nvarchar(50), isActive bit)
CREATE NONCLUSTERED index [IncludedInd] on [ClusteredIncluded] (name) include(isActive)

--PK/clustered w/desc
CREATE TABLE [ClusteredDesc] (id int not null, name nvarchar(50))
ALTER TABLE [ClusteredDesc] ADD CONSTRAINT [DescConstraint] PRIMARY KEY CLUSTERED (ID desc)

--Clustered with two cols
CREATE TABLE [ClusteredWithTwoCols] (id int not null, name nvarchar(50) not null)
ALTER TABLE [ClusteredWithTwoCols] ADD CONSTRAINT [PK_ClusteredWithTwoCols] PRIMARY KEY CLUSTERED (ID, name desc)

--Heap with an uncool name
CREATE TABLE [Table With A Bad Name] (id int)

Check Current Filegroup Layout

Of course, as we didn’t specify any filegroup options, we have SQL’s default: a single filegroup called [PRIMARY] with everything on it.  We can verify this by running the following query:

SELECT
	 fg.name			AS		[FileGroup]
	,fg.is_read_only
	,s.name				AS		[Schema]
	,o.name				AS		[TABLE]
	,i.index_id
	,i.name				AS		[Index]
	,i.[type]
	,i.type_desc			AS		[IndexType]
FROM sys.objects o
INNER JOIN [sys].[schemas] [s]
	ON [o].[schema_id]=[s].[schema_id]
INNER JOIN [sys].[indexes] [i]
	ON [o].[object_id]=[i].[object_id]
INNER JOIN [sys].[filegroups] [fg]
	ON [i].[data_space_id]=[fg].[data_space_id]
WHERE 1=1
	AND [o].[type]='U'
	AND [o].[is_ms_shipped]=0
	--AND [s].[name]='dbo'
	--AND [o].[name] LIKE 'TABLEPrefix%'
ORDER BY [fg].[name], [s].[name], [o].[name], [i].[index_id]

Let’s start by setting up our new filegroup and creating a file in it:

ALTER DATABASE [FileGroupTesting] ADD FILEGROUP [FGTest]
--Be sure to replace the file path below with a valid directory on your system!
ALTER DATABASE [FileGroupTesting]
ADD FILE
( NAME = FileGroupTest,
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\FileGroupTest.ndf'
)
TO FILEGROUP [FGTest]

The Solution

Next, we can use the script below (after changing to the appropriate database context and modifying any of the “setable” parameters.

/*
* Author: Ryan Snee
* Version: 1.0
* Date 20131015
*
* License: This work is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.
* License Details: http://creativecommons.org/licenses/by-sa/3.0/deed.en_US
*
* LIMITATIONS
* If an index is partitioned, it comes off the partition to go be put onto a filegroup
* Not sure how compression is handled... I assume we lose our compression settings, as they are per index, per partition
* Statistics_norecompute setting is defaulted back to OFF (should rarely be ON anyway, but something to be aware of)
* Assumes that no heap has a column called [TempPK]
* Does not account for ColumnStore Indexes
* Not tested with indexed views

Does handle:
 Online Index Rebuilds, where possible
 Filtered Indexes

*/

SET NOCOUNT ON;
DECLARE @LoopSafetyBefore int,
@LoopSafetyAfter int,

@LoopSafetyInternalBefore int,
@LoopSafetyInternalAfter int,
@Execute bit,
@CurrentObject int,
@CurrentIndex int,
@IndexColID int,
@colListBase nvarchar(max),
@colListInclude nvarchar(max),
@cmd nvarchar(max),
@TryOnlineRebuild bit,
@NewFG sysname,
@TableFilter sysname

--
set @NewFG='FGTest'
set @TryOnlineRebuild=1
set @TableFilter='%'
--

------------------
--1) Prep work----
------------------

	if object_id('tempdb..#Indexes') is not null
	drop table #Indexes

	if object_id('tempdb..#IndexDetail') is not null
	drop table #IndexDetail

	CREATE TABLE #Indexes (
		ID int IDENTITY(1,1) PRIMARY KEY CLUSTERED ,
		SchemaName sysname,
		ObjectName sysname,
		ObjectID int,
		IndexName sysname null,
		IndexID int,
		IndexType int,
		Is_Unique bit,
		Is_Primary bit,
		Type_Desc nvarchar(60),
		Is_Padded bit,
		Ignore_Dup_Key bit,
		Allow_Row_Locks bit,
		Allow_Page_Locks bit,
		Fill_Factor tinyint,
		isDisabled bit,
		FileGroupName sysname,
		Has_Filter bit,
		Filter_Definition nvarchar(max),
		--key_ordinal tinyint
		LOBColumns int,
		isDone bit,
		Cmd nvarchar(max)

	)

	CREATE TABLE #IndexDetail (
		ID int IDENTITY(1,1) PRIMARY KEY CLUSTERED ,
		SchemaName sysname,
		ObjectName sysname,
		ObjectID int,
		IndexName sysname null,
		IndexID int,
		IndexType int,
		Is_Unique bit,
		Is_Primary bit,
		Type_Desc nvarchar(60),
		Is_Padded bit,
		Ignore_Dup_Key bit,
		Allow_Row_Locks bit,
		Allow_Page_Locks bit,
		Fill_Factor tinyint,
		isDisabled bit,
		Is_Descending_Key bit,
		ColumnName sysname null,
		Is_Included_Column bit,
		FileGroupName sysname,
		Has_Filter bit,
		Filter_Definition nvarchar(max),
		key_ordinal tinyint,
		isDone bit default(0)
	)

	--First I get info on all of the indexes we need to move
	--This includes the columns that make up the index, if applicable
	--The set includes 1 row per index per column it includes
	INSERT INTO #IndexDetail
	SELECT	 s.name as [SchemaName]
			,o.Name as ObjectName
			,o.object_id as ObjectID
			,si.Name as IndexName
			,si.index_id
			,si.type
			,si.Is_Unique
			,si.is_primary_key
			,si.Type_Desc
			,si.Is_Padded
			,si.Ignore_Dup_Key
			,si.Allow_Row_Locks
			,si.Allow_Page_Locks
			,si.Fill_Factor
			,si.is_disabled
			,sic.Is_Descending_Key
			,sc.Name as ColumnName
			,sic.Is_Included_Column
			,sf.Name as FileGroupName
			,si.has_filter as Has_Filter
			,si.filter_definition as Filter_Definition
			,sic.key_ordinal
			,0 as [isDone]
	 FROM 
		sys.Objects o
		INNER JOIN sys.Indexes si
			ON o.Object_Id = si.Object_id
		left join sys.schemas s
		on o.schema_id=s.schema_id
		INNER JOIN sys.FileGroups sf
			ON sf.Data_Space_Id = si.Data_Space_Id
		LEFT JOIN  sys.Index_columns sic
			ON si.Object_Id = sic.Object_Id
				AND si.Index_id = sic.Index_id
		LEFT JOIN  sys.Columns sc
			ON sic.Column_Id = sc.Column_Id
				and sc.Object_Id = sic.Object_Id 
	 WHERE 
		o.type='U'
		and o.is_ms_shipped=0
		AND o.Name LIKE COALESCE(@TableFilter, '%')
	 ORDER BY ObjectName, IndexName, sic.Key_Ordinal

	 --From the #IndexDetail table, I extract a set containing just 1 row per index
	INSERT INTO #Indexes 
	(SchemaName, ObjectName, ObjectID, IndexID, IndexName, IndexType, is_Unique, is_primary,Type_Desc, is_padded, ignore_dup_key, allow_row_locks,allow_page_locks,fill_factor, isDisabled, FileGroupName, has_filter,filter_definition, LOBColumns, isDone, Cmd)
	SELECT DISTINCT
	 SchemaName, ObjectName, ObjectID, IndexID, IndexName, IndexType, is_Unique, is_primary,Type_Desc, is_padded, ignore_dup_key, allow_row_locks,allow_page_locks,fill_factor, isDisabled, FileGroupName,has_filter,filter_definition, 0, 0, N''
	FROM #IndexDetail;

	--Find indexes that include LOB data
	--Calculate how many LOB columns each index has and write it to the LobColumns column of #Indexes
	WITH IndexesWithLOBs AS (

		SELECT ObjectID, IndexID, COUNT(*) AS LOBColumns
		FROM #Indexes i
			LEFT JOIN sys.index_columns ic --Grabbing INCLUDEd columns
				on i.ObjectID=ic.object_id
				AND i.IndexID=ic.index_id
			left join sys.columns c
				on i.ObjectID=c.object_id
				AND ic.column_id=c.column_id
			left join sys.types t
				on c.system_type_id=t.user_type_id
		WHERE	1=1
				AND i.Type_Desc='NONCLUSTERED'
				and ((
						t.system_type_id IN (231, 165, 167)-- nvarchar, varbinary, varchar
							AND c.max_length = -1 -- (MAX)
					)
					OR t.system_type_id IN (35, 34, 241, 99)-- text, image, xml, ntext
				)

		GROUP BY ObjectID, INDEXID

		UNION ALL

		SELECT ObjectID, IndexID, count(*) as LobColumns
		FROM #Indexes i
			left join sys.columns c --Grabbing columns that are part of the index
				on i.ObjectID=c.object_id
			left join sys.types t
				on c.system_type_id=t.user_type_id
		WHERE	1=1
				AND (i.Type_Desc='CLUSTERED' OR i.Type_Desc='HEAP')
				and ((
						t.system_type_id IN (231, 165, 167)--nvarchar, varbinary, varchar
							AND c.max_length = -1-- (MAX)
					)
					OR t.system_type_id IN (35, 34, 241, 99)-- text, image, xml, ntext
				)
		GROUP BY ObjectID, IndexID

	)

	UPDATE I
	SET i.LobColumns=LOB.LobColumns
	FROM #Indexes i
		INNER JOIN IndexesWithLOBs LOB
			ON i.ObjectID=LOB.ObjectID
				AND i.IndexID=LOB.IndexID

------------------------
--2) Process the Heaps--
------------------------
	UPDATE #Indexes
		SET [Cmd]=
			'ALTER TABLE '+QUOTENAME(SchemaName)+'.'+QUOTENAME(ObjectName) +' ADD [TempPK] bigint identity(1,1);'
			+' CREATE CLUSTERED INDEX [IX_Temp] ON '+QUOTENAME(SchemaName)+'.'+QUOTENAME(ObjectName) +' ([TempPK] ASC) WITH(ONLINE='
			+CASE WHEN (@TryOnlineRebuild=1 AND LOBColumns=0 AND isDisabled=0)
				THEN 'ON'
				ELSE 'OFF'
			END
			+') ON ['+@NewFG+'];'
			+'DROP INDEX [IX_Temp] ON '+QUOTENAME(SchemaName)+'.'+QUOTENAME(ObjectName)+' WITH(ONLINE='
			+CASE WHEN (@TryOnlineRebuild=1 AND LOBColumns=0)
				THEN 'ON'
				ELSE 'OFF'
			END
			+');'
			+'alter table '+QUOTENAME(SchemaName)+'.'+QUOTENAME(ObjectName)+' drop column [TempPK];'
			--Handle when Page Locks were disabled
			+CASE
				WHEN [Allow_Page_Locks]=0
				THEN ' ALTER INDEX ALL ON '+QUOTENAME(SchemaName)+'.'+QUOTENAME(ObjectName) +' SET  (ALLOW_Page_LOCKS = OFF );'+char(13)+char(10)
				ELSE ''
				END
			--Handle when Row Locks were disabled
			+CASE
				WHEN [Allow_Row_Locks]=0
				THEN ' ALTER INDEX ALL ON '+QUOTENAME(SchemaName)+'.'+QUOTENAME(ObjectName) +' SET  (ALLOW_Row_LOCKS = OFF ); '
				ELSE ''
			END
		,isDone=1
	WHERE Type_Desc='Heap'
		AND isDone=0

	--Assumption: Padding, ignore_duplicate, fillfactor, is_disabled, and filtering don't apply to a heap 

------------------------------------------
--3) Process the Nonclustered Indexes-----
------------------------------------------
	WHILE EXISTS (SELECT ID FROM #Indexes WHERE Type_Desc='NONCLUSTERED' AND isDone=0)
	BEGIN

		SET @CurrentObject= NULL
		SET @CurrentIndex= NULL

		select @LoopSafetyBefore=count(*) FROM #Indexes WHERE Type_Desc='NONCLUSTERED' AND isDone=0;

		--Grab One Index
		SELECT TOP 1 @CurrentObject=ObjectID, @CurrentIndex=IndexID FROM #Indexes WHERE Type_Desc='NONCLUSTERED' AND isDone=0;

		set @colListBase=''
		set @colListInclude=''

		--For each NC Index, loop through every column in the core index to build up @colListBase
		WHILE EXISTS (SELECT 1 FROM #IndexDetail WHERE isDone=0 and Is_Included_Column=0 AND ObjectID=@CurrentObject AND IndexID=@CurrentIndex )
		BEGIN
			select @LoopSafetyInternalBefore=count(*) FROM #IndexDetail WHERE isDone=0 and Is_Included_Column=0 and ObjectID=@CurrentObject and IndexID=@CurrentIndex;

			SELECT TOP 1 @IndexColID=ID FROM #IndexDetail WHERE isDone=0  and Is_Included_Column=0 AND ObjectID=@CurrentObject AND IndexID=@CurrentIndex ORDER BY key_ordinal ASC

			if @LoopSafetyInternalBefore=1
				BEGIN
					select @colListBase=@colListBase+quotename(ColumnName) + CASE WHEN Is_Descending_Key=1 THEN ' DESC' ELSE '' END FROM #IndexDetail WHERE ID=@IndexColID
				END
			ELSE
				BEGIN
					select @colListBase=@colListBase+quotename(ColumnName) + CASE WHEN Is_Descending_Key=1 THEN ' DESC, ' ELSE ', ' END FROM #IndexDetail WHERE ID=@IndexColID
				END

			UPDATE #IndexDetail SET isDone=1 WHERE ID=@IndexColID

			select @LoopSafetyInternalAfter=count(*) FROM #IndexDetail WHERE isDone=0 and Is_Included_Column=0 and ObjectID=@CurrentObject and IndexID=@CurrentIndex;
			if(@LoopSafetyInternalBefore<=@LoopSafetyInternalAfter)
			BEGIN
				RAISERROR('Infinite Loop while adding columns for object %i',11,1, @CurrentObject) WITH NOWAIT
				BREAK
			END

		END

		--Then Loop through the included columns to build up @colListInclude
		WHILE EXISTS (SELECT 1 FROM #IndexDetail WHERE isDone=0 and Is_Included_Column=1 AND ObjectID=@CurrentObject AND IndexID=@CurrentIndex )
		BEGIN

			select @LoopSafetyInternalBefore=count(*) FROM #IndexDetail WHERE isDone=0 and Is_Included_Column=1 and ObjectID=@CurrentObject and IndexID=@CurrentIndex;

			SELECT TOP 1 @IndexColID=ID FROM #IndexDetail WHERE isDone=0  and Is_Included_Column=1 AND ObjectID=@CurrentObject AND IndexID=@CurrentIndex

			if @LoopSafetyInternalBefore=1
				BEGIN
					select @colListInclude=@colListInclude+quotename(ColumnName) FROM #IndexDetail WHERE ID=@IndexColID
				END
			ELSE
				BEGIN
					select @colListInclude=@colListInclude+quotename(ColumnName)+', ' FROM #IndexDetail WHERE ID=@IndexColID
				END

			UPDATE #IndexDetail SET isDone=1 WHERE ID=@IndexColID

			select @LoopSafetyInternalAfter=count(*) FROM #IndexDetail WHERE isDone=0 and Is_Included_Column=1 and ObjectID=@CurrentObject and IndexID=@CurrentIndex;

			if(@LoopSafetyInternalBefore<=@LoopSafetyInternalAfter) 			BEGIN 				RAISERROR('Infinite Loop while adding included columns for object %i',11,1, @CurrentObject) WITH NOWAIT 				BREAK 			END 		 		END 	 		--Now that the column lists are unrolled, form the command 		SELECT @cmd= 			'CREATE ' 			+CASE 				WHEN Is_Unique=1 					THEN 'UNIQUE ' 				ELSE '' 			END 			+'NONCLUSTERED INDEX '+quotename(IndexName) +' ON '+QUOTENAME(schemaname)+'.'+QUOTENAME(ObjectName) 			+'('+@colListBase+')' 			+CASE WHEN len(@colListInclude)>0
				THEN 'INCLUDE('+@colListInclude+')'
				ELSE ''
			END
			+CASE WHEN Filter_Definition is not null
				THEN ' WHERE '+Filter_Definition
				ELSE ''
			END
			+' WITH(DROP_EXISTING=ON,PAD_INDEX='
			+CASE
				WHEN is_padded=1
					THEN 'ON,'
				ELSE 'OFF,'
			END
			+CASE
				WHEN Fill_Factor>0
					THEN 'FILLFACTOR='+cast(fill_factor as varchar(5))+','
				ELSE ''
			END
			+'IGNORE_DUP_KEY='
			+CASE
				WHEN Ignore_Dup_Key=1
					THEN 'ON,'
				ELSE 'OFF,'
			END

			+'ALLOW_ROW_LOCKS='
			+CASE
				WHEN Allow_Row_Locks=1
					THEN 'ON,'
				ELSE 'OFF,'
			END
			+'ALLOW_PAGE_LOCKS='
			+CASE
				WHEN Allow_Page_Locks=1
					THEN 'ON,'
				ELSE 'OFF,'
			END
			+'ONLINE='
			+CASE
				WHEN (@TryOnlineRebuild=1 AND LOBColumns=0 AND isDisabled=0)
					THEN 'ON'
				ELSE 'OFF'
			END
			+') ON '+quotename(@NewFG)+';'
			+CASE
				WHEN isDisabled=1
					THEN ' ALTER INDEX '+quotename(IndexName)+' ON '+QUOTENAME(SchemaName)+'.'+QUOTENAME(ObjectName)+' DISABLE;'
				ELSE ''
			END

		FROM #Indexes
		WHERE ObjectID=@CurrentObject AND IndexID=@CurrentIndex

		--Write the command back to the table
		UPDATE #Indexes
		SET [Cmd]=@cmd, isDone=1
		WHERE ObjectID=@CurrentObject
			AND IndexID=@CurrentIndex

		SELECT @LoopSafetyAfter=count(*)
		FROM #Indexes
		WHERE Type_Desc='NONCLUSTERED'
			AND isDone=0;

		if(@LoopSafetyBefore<=@LoopSafetyAfter)
		BEGIN
			RAISERROR('Infinite Loop in Indexes, object %i',11,2,@CurrentObject) WITH NOWAIT
			BREAK
		END

	END

----------------------------------------------------
--4) Repeat the pattern for Clustered Indexes-------
----------------------------------------------------

	WHILE EXISTS (SELECT ID FROM #Indexes WHERE Type_Desc='CLUSTERED' AND isDone=0)
	BEGIN

		SET @CurrentObject= NULL
		SET @CurrentIndex= NULL

		select @LoopSafetyBefore=count(*) FROM #Indexes WHERE Type_Desc='CLUSTERED' AND isDone=0;

		--Grab One Index
		SELECT TOP 1 @CurrentObject=ObjectID, @CurrentIndex=IndexID FROM #Indexes WHERE Type_Desc='CLUSTERED' AND isDone=0;

		set @colListBase=''
		set @colListInclude=''

		--For each Clustered Index, loop through every column in the core index
		WHILE EXISTS (SELECT 1 FROM #IndexDetail WHERE isDone=0 AND ObjectID=@CurrentObject AND IndexID=@CurrentIndex )
		BEGIN

			select @LoopSafetyInternalBefore=count(*) FROM #IndexDetail WHERE isDone=0 and ObjectID=@CurrentObject and IndexID=@CurrentIndex;

			SELECT TOP 1 @IndexColID=ID FROM #IndexDetail WHERE isDone=0  and ObjectID=@CurrentObject AND IndexID=@CurrentIndex ORDER BY key_ordinal ASC

			if @LoopSafetyInternalBefore=1
				BEGIN
					select @colListBase=@colListBase+quotename(ColumnName) + CASE WHEN Is_Descending_Key=1 THEN ' DESC' ELSE '' END FROM #IndexDetail WHERE ID=@IndexColID
				END
			ELSE
				BEGIN
					select @colListBase=@colListBase+quotename(ColumnName) + CASE WHEN Is_Descending_Key=1 THEN ' DESC, ' ELSE ', ' END FROM #IndexDetail WHERE ID=@IndexColID
				END

			UPDATE #IndexDetail SET isDone=1 WHERE ID=@IndexColID

			select @LoopSafetyInternalAfter=count(*) FROM #IndexDetail WHERE isDone=0 and ObjectID=@CurrentObject and IndexID=@CurrentIndex;

			if(@LoopSafetyInternalBefore<=@LoopSafetyInternalAfter) 			BEGIN 				RAISERROR('Infinite Loop while adding columns for object %i',11,1, @CurrentObject) WITH NOWAIT 				BREAK 			END 		 		END 	 		--Form the command 		SELECT 			@cmd= 'CREATE ' 			+CASE 				WHEN Is_Unique=1 					THEN 'UNIQUE ' 				ELSE '' 			END 			+'CLUSTERED INDEX '+quotename(IndexName) +' ON '+QUOTENAME(schemaname)+'.'+QUOTENAME(ObjectName) 			+'('+@colListBase+')' 			+' WITH(DROP_EXISTING=ON,PAD_INDEX=' 			+CASE WHEN is_padded=1 				THEN 'ON,' 					ELSE 'OFF,' 			END 			+CASE 				WHEN Fill_Factor>0
					THEN 'FILLFACTOR='+cast(fill_factor as varchar(5))+','
				ELSE ''
			END
			+'IGNORE_DUP_KEY='
			+CASE
				WHEN Ignore_Dup_Key=1
					THEN 'ON,'
				ELSE 'OFF,'
			END
			+'ALLOW_ROW_LOCKS='
			+CASE
				WHEN Allow_Row_Locks=1
					THEN 'ON,'
				ELSE 'OFF,'
			END
			+'ALLOW_PAGE_LOCKS='
			+CASE
				WHEN Allow_Page_Locks=1
					THEN 'ON,'
				ELSE 'OFF,'
			END
			+'ONLINE='
			+CASE
				WHEN (@TryOnlineRebuild=1 AND LOBColumns=0 AND isDisabled=0)
					THEN 'ON'
				ELSE 'OFF'
			END
			+') ON '+quotename(@NewFG)+';'
			+CASE
				WHEN isDisabled=1
					THEN ' ALTER INDEX '+quotename(IndexName)+' ON '+QUOTENAME(SchemaName)+'.'+QUOTENAME(ObjectName)+' DISABLE;'
				ELSE ''
			END	
		FROM #Indexes
		WHERE ObjectID=@CurrentObject AND IndexID=@CurrentIndex

		--Write the command back to the @Indexes table
		UPDATE #Indexes
		SET [Cmd]=@cmd, isDone=1
		WHERE ObjectID=@CurrentObject
		AND IndexID=@CurrentIndex

		select @LoopSafetyAfter=count(*) FROM #Indexes WHERE Type_Desc='CLUSTERED' AND isDone=0;

		if(@LoopSafetyBefore<=@LoopSafetyAfter)
		BEGIN
			RAISERROR('Infinite Loop in Indexes, object %i',11,2,@CurrentObject) WITH NOWAIT
			BREAK
		END

	END

--select * from #IndexDetail
--select * FROM #Indexes

SELECT
	FileGroupName
	,SchemaName
	,ObjectName
	,IndexName
	,IndexID
	,IndexType
	,Is_Unique
	,is_Primary
	,Type_Desc
	,Is_Padded
	,Ignore_Dup_Key
	,Allow_Row_Locks
	,Allow_Page_Locks
	,Fill_Factor
	,isDisabled
	,Has_Filter
	,Filter_Definition
	,LOBColumns
	,[Cmd]
FROM #Indexes
WHERE FileGroupName <> @NewFG
ORDER BY SchemaName, ObjectName

Remember, the script doesn’t change anything; rather it generates TSQL commands you can choose to run.

Running the Generated Commands and Verification

The final step would be to review and possibly run the commands in the last column of the table.

After doing so, we can run the query mentioned in the “Check Current Filegroup Layout” section above to verify that every index of every (user) table has moved to the new filegroup/file.

As a note, system tables are forever in the first, primary data file.  It can be moved (an offline operation, unfortunately), but it cannot be removed.  Attempting to do so will yield the following error:

Msg 5020, Level 16, State 1, Line 1
The primary data or log file cannot be removed from a database.

Changing the Filegroup to Read_Only

Now that my objective of moving a subset of the user tables to a new filegroup has been accomplished, I can continue on to step 4 of my approach:

use FileGroupTesting;

--You need to be the only one in the database.  You may need to kick everyone else off:
alter database [FileGroupTesting] set single_user with rollback immediate;

--Make FileGroup ReadOnly
ALTER DATABASE [FileGroupTesting]
MODIFY FILEGROUP [FGTest] Read_only;

alter database [FileGroupTesting] set multi_user with rollback immediate;

SELECT
name
,is_read_only
,is_default
FROM sys.filegroups

The full script can be found here: MovingTablesToNewFG.txt.

That’s it! Please let me know if you found this script useful!

LLAP,
Ryan

A few sources I’d like to cite for contributing ideas:


The text of this entire blog site (including all scripts) is under the copyright of Ryan Snee.You are free to reproduce it, as outlined by the Creative Commons Attribution-ShareAlike 3.0 Unported License.