Background

For a performance exercise that I will be undertaken I need to figure out how much my user databases and tempdb are growing.

Lineage

Here are some past posts along those same lines.

SQL Server – List Database Size
Use sys.master-files
Published On :- 2017-March-29th
Link

SQL

Pasted below are some sample SQL for getting file sizes and in use portion aggregated by file types.

Database Size for current database

Code



; with cte
as
(

  select 
		    [dbName]
			= DB_NAME() 

		, [type]				

		, [fileSizeMB]
			= cast
				(
					sum(size)/128.0
					as decimal(10, 2)
				)

		, [spaceUsedMB]
			= cast
				(
					sum(
							CAST
							(
								FILEPROPERTY(name, 'SpaceUsed') AS INT
							)
					)/128.0
					as decimal(10, 2)
				)

		, [freeSpaceMB]
			= sum
				(
					tblSDF.[size]
					- CAST
					(
						FILEPROPERTY
						(
							  tblSDF.[name]
							, 'SpaceUsed'
						) AS INT
					)
				)/128.0

	from sys.database_files tblSDF

	group 
		by tblSDF.[type] 

)

select 
		  [dbName]

		, [DataAllocatedSizeInMB] = sum(isNull([alloc0], 1))

		, [DataInUseSizeInMB] = sum(isNull([inuse0], 1))

		, [dataInUse%]
			= cast
				( 
					(
						( sum(isNull([inuse0], 1)) * 100.00 )
							/ sum(isNull([alloc0], 1))
					)
					as decimal(10, 2)
				)

		, [LogAllocatedSizeInMB] = sum(isNull([alloc1], 0))
		, [LogInuseSizeInMB] = sum(isNull([inuse1], 0))	
		, [logInUse%]
			= cast
				(
					( 
						sum
						(
							isNull([inuse1], 1)
						) * 100.00 
					)
					/ sum
					  (
						isNull([alloc1], 1)
					  )

					as decimal(10, 2)
				)

		, [fileStreamAllocatedSizeInMB] 
			= sum
				(
					isNull([alloc2], 0)
				)


from   
		(
		
			select 
					  [dbName]
					, [type] = 'alloc' + cast([type] as char(1))
					, [fileSizeMB]
					, [spaceUsedMB]
					, [typeInUse] = 'inuse' + cast([type] as char(1))
			from   cte

		) cteFS

		PIVOT
		(

			  sum 
			  ( 
				cteFS.[fileSizeMB] 
			  )

			  for [type]
			  in 
			  (
				  [alloc0]
				, [alloc1]
				, [alloc2]
			  )

		) as cteDataFileSizeInMB

		PIVOT
		(

			  sum 
			  ( 
				[spaceUsedMB]
			  )

			  for [typeInUse]
			  in 
			  (
				  [inuse0]
				, [inuse1]
				, [inuse2]
			  )

		) as cteInUseFileSizeInMB


group by
		  [dbName]

Output

Database Size for all databases

Code




set nocount on;
go

use [master]
go

declare @tblDatabase  TABLE
(
	  [dbName]					sysname not null
	, [DataAllocatedSizeInMB]   int
	, [DataInUseSizeInMB]	    bigint

	, [dataInUse%] 
		as 
			convert
			(
				  decimal(10, 2)
				, (
					[DataInUseSizeInMB] * 100.00
					/ NULLIF
						(
							[DataAllocatedSizeInMB]
							, 0
						)	
					)
			)

	, [LogAllocatedSizeInMB] bigint
	, [LogInuseSizeInMB]	 bigint
	, [logInUse%] as 
		convert
		(
			decimal(10, 2)
			, ( [LogInuseSizeInMB] * 100.00 )
				/ NULLIF([LogAllocatedSizeInMB], 0)
		)

	, [fileStreamAllocatedSizeInMB]  bigint


)

insert into @tblDatabase

EXEC sp_MSforeachdb 
	'
	IF DATABASEPROPERTYEX(''?'', ''Collation'') IS NOT NULL
	begin

		USE [?];
		; with cte
		as
		(

		  select 
					[dbName]
					= DB_NAME() 

				, [type]				

				, [fileSizeMB]
					= sum(size)/128.0

				, [spaceUsedMB]
					= sum(
							CAST
								(
									FILEPROPERTY(name, ''SpaceUsed'') AS INT
								)
						)/128.0

				, [freeSpaceMB]
					= sum
						(
							tblSDF.[size]
							- CAST
							(
								FILEPROPERTY
								(
									  tblSDF.[name]
									, ''SpaceUsed''
								) AS INT
							)
						)/128.0

			from sys.database_files tblSDF

			group 
				by tblSDF.[type] 

		)

		select 
				  [dbName]

				, [DataAllocatedSizeInMB] = sum(isNull([alloc0], 1))

				, [DataInUseSizeInMB] = sum(isNull([inuse0], 1))

				, [LogAllocatedSizeInMB] = sum(isNull([alloc1], 0))
				, [LogInuseSizeInMB] = sum(isNull([inuse1], 0))	

				, [fileStreamAllocatedSizeInMB] 
					= sum
						(
							isNull([alloc2], 0)
						)

		from   
				(
		
					select 
							  [dbName]
							, [type] = ''alloc'' + cast([type] as char(1))
							, [fileSizeMB]
							, [spaceUsedMB]
							, [typeInUse] = ''inuse'' + cast([type] as char(1))
					from   cte

				) cteFS

				PIVOT
				(

					  sum 
					  ( 
						cteFS.[fileSizeMB] 
					  )

					  for [type]
					  in 
					  (
						  [alloc0]
						, [alloc1]
						, [alloc2]
					  )

				) as cteDataFileSizeInMB

				PIVOT
				(

					  sum 
					  ( 
						[spaceUsedMB]
					  )

					  for [typeInUse]
					  in 
					  (
						  [inuse0]
						, [inuse1]
					  )

				) as cteInUseFileSizeInMB


			group by
				  [dbName]

		end

	  '

select *
from   @tblDatabase
order by [dbName]

Lineage

In our last couple of posts we focused our lenses on partial database restores.

Those posts are here:

SQL Server – Database Restore – “Defunct State”
here
SQL Server – Database Restore – Skipping File Groups
here

Current

In this post we will look a bit more at the benefits and costs of restoring just a portion of database.

Reviews

GUI

Database

File Groups

Files

Tables

dbo.ten95B

Storage

Table Name :- dbo.ten95B
File Group :- PRIMARY
Text filegroup :- fgLOB
Data Space
- 1370 MB
- or 1.4 GB
Row Count :- 4800

Query

Database

Database – Files

Let us query the sys.database_files system table to get the name, type, state, and size of the individual files that are in our current database.

SQL


select 
		  tblSDF.[file_id]
		, tblSDF.[name]
		, tblSDF.[physical_name]
		, tblSDF.[type_desc]
		, tblSDF.[state_desc]
		, tblSDF.size
--		, tblSDF.*
from   sys.database_files tblSDF