sys.database_files

SQL Server – Create Database from existing files

Daniel Adeniji Microsoft, SQL Server, sys.database_files, sys.master_files, Technical

Background

Hard crash from running out of storage and now it is time to get a new LUN/Volume up.

 

Outline

  1. Get database filenames of database to be moved
    • sys.master_files ( Master Database )
    • sys.database_files ( Contextual Database )
  2. Take database offfline
  3. Re-attach database files
    • Create database for attach

 

Processing

Get database filenames of database to be moved

sys.master_files

SQL

set nocount on;
go

declare @database sysname
declare @dbid     int
declare @sqlFileName  nvarchar(max)
declare @sql  nvarchar(max)

declare @CHAR_TAB           char(1)
declare @CHAR_COMMA         char(1)
declare @CHAR_CRLF          char(2)
declare @CHAR_SINGLEQUOTES  char(1)

set @CHAR_TAB = char(9)
set @CHAR_COMMA = ','
set @CHAR_CRLF = char(13) + char(10)
set @CHAR_SINGLEQUOTES = ''''

/*	
	Specify database name
*/
set @database = 'hrdb'

/*	
	Get database ID
*/
set @dbid = db_id(@database)

select 
        @sqlFileName = 
            isNull(@sqlFileName, '')
            + @CHAR_TAB
            + case
                when tblSMF.file_id = 1 then ' '
                else @CHAR_COMMA
              end   
            + '(FILENAME = '
            + @CHAR_SINGLEQUOTES
            + tblSMF.physical_name
            + @CHAR_SINGLEQUOTES
            + ')'
            + @CHAR_CRLF

from   sys.master_files tblSMF

where  tblSMF.database_id = @dbid

set @sql = 'CREATE DATABASE '
            + quoteName(@database)
            + @CHAR_CRLF
            + ' ON '
            + @CHAR_CRLF
            + @sqlFileName
            + ' FOR ATTACH '
            + @CHAR_CRLF

print @sql

Output



CREATE DATABASE [hrdb]
 ON 
	 (FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb.mdf') --1
	,(FILENAME = 'Z:\MSSQL12.MSSQLSERVER\MSSQL\Log\hrdb_8.ldf') --2
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb_0.ndf') --3
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb_1.ndf') --4
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb_2.ndf') --5
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb_3.ndf') --6
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb_4.ndf') --7
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb_5.ndf') --8
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb_6.ndf') --9
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb_7.ndf') --10
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdbLOB_01.ndf') --11
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb_index_01.ndf') --12
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb_9.memopt') --65537
 FOR ATTACH

sys.database_files

SQL


set nocount on;
go

use [hrdb]
go

declare @database sysname
declare @dbid     int
declare @sqlFileName  nvarchar(max)
declare @sql  nvarchar(max)

declare @CHAR_TAB           char(1)
declare @CHAR_COMMA         char(1)
declare @CHAR_CRLF          char(2)
declare @CHAR_SINGLEQUOTES  char(1)

set @CHAR_TAB = char(9)
set @CHAR_COMMA = ','
set @CHAR_CRLF = char(13) + char(10)
set @CHAR_SINGLEQUOTES = ''''

set @database = db_name()
set @dbid = db_id()

select 
        @sqlFileName = 
            isNull(@sqlFileName, '')
            + @CHAR_TAB
            + case
                when tblSDF.file_id = 1 then ' '
                else @CHAR_COMMA
              end   
            + '(FILENAME = '
            + @CHAR_SINGLEQUOTES
            + tblSDF.physical_name
            + @CHAR_SINGLEQUOTES
            + ')'
            + @CHAR_CRLF

from   sys.database_files tblSDF

set @sql = 'CREATE DATABASE '
            + quoteName(@database)
            + @CHAR_CRLF
            + ' ON '
            + @CHAR_CRLF
            + @sqlFileName
            --+ @CHAR_CRLF
            + ' FOR ATTACH '
            + @CHAR_CRLF

print @sql

Output


CREATE DATABASE [hrdb]
 ON 
	 (FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb.mdf') --1
	,(FILENAME = 'Z:\MSSQL12.MSSQLSERVER\MSSQL\Log\hrdb_8.ldf') --2
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb_0.ndf') --3
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb_1.ndf') --4
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb_2.ndf') --5
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb_3.ndf') --6
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb_4.ndf') --7
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb_5.ndf') --8
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb_6.ndf') --9
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb_7.ndf') --10
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdbLOB_01.ndf') --11
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb_index_01.ndf') --12
	,(FILENAME = 'K:\Microsoft\SQLServer\Datafiles\hrdb_9.memopt') --65537
 FOR ATTACH

Take Database Offline

sp_detach_db

SQL


USE [master]
GO
ALTER DATABASE [hrdb] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'hrdb'
GO

SQL Server – Database Size Aggregated By File Type

Daniel Adeniji Database Size, Microsoft, SQL Server, sys.database_files, Technical, Transact SQL

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.

  1. 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]

SQL Server – Partial Database Restores – Benefits & Impact

Daniel Adeniji Catalog Views, Database Backup\Restore, Microsoft, Option ( Database Backup \ Restore ), Partial Restores, SQL Server, sys.database_files, Technical

Lineage

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

Those posts are here:

  1. SQL Server – Database Restore – “Defunct State”
    here
  2. 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

databaseProperties-FileGroups (cropped-up)

 

Files

databaseProperties-Files

 

 

Tables

dbo.ten95B

tableCount-Table1

 

Storage
  1. Table Name :- dbo.ten95B
  2. File Group :- PRIMARY
  3. Text filegroup :- fgLOB
  4. Data Space
    • 1370 MB
    • or 1.4 GB
  5. 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
Image

Database – Show File Stats

Let us issue “DBCC ShowFileStats” to gather information about our database files.

SQL
dbcc showfilestats
Image

Table

Let us try to read data from table

 

Query Table for Data – All Columns

dbo.ten95C
SQL

select top (1000) *
from [dbo].[ten95C]
Image
filegroupThatCanNotBeAccessed

 

Textual

Large object (LOB) data for table “dbo.ten95C” resides on an offline filegroup (“fgLOB”) that cannot be accessed

 

Query Table for Data – Specific Columns

On the other hand, we are still able to query for specific columns.

When we restrict our column set to those filegroups that were included in our restore, things are good.

dbo.ten95C
SQL

select top (10)
 
   tblT.[ten95C_id]
 , tblT.[ten95C_view_cnt]
 , tblT.[ten95C_view_last]

from [dbo].[ten95C] tblT
Image

Summary

From a cursory look, Microsoft’s design and implementation of Partial database restore is well thought out and solid.

Its preserves all the niceties of having access to the original database design ( database groups and files), metadata for all concerns.

And, rightfully sacrifices storage of the actual data and thus we gain the benefits of not having to provide and maintain storage for uneeded data.