SQL Server – Install Date – Application & Last Service Pack

Background

It is that time of year again for our Microsoft “True Up” and we need to come clean with new Installs.

 

What is “True Up”?

Link

 

Install & Last SP Date

Let us ask each database its install date and while at it get the date the last SP was applied.

Outline

  1. Install Date
    • Install
      • When SQL Service is installed some OS Accounts are automatically provisioned, as well
        • List of OS Accounts:
          • NT SERVICE\ReportServer
          • NT SERVICE\SQLSERVERAGENT
          • NT AUTHORITY\SYSTEM
          • NT Service\MSSQLSERVER
          • NT SERVICE\Winmgmt
          • NT SERVICE\SQLWriter
  2. Last Service Pack Apply Date
    • Service Pack Applied
      • When a Service Pack is applied, new certificates are applied, as well
        • ##MS_AgentSigningCertificate##
        • ##MS_PolicySigningCertificate##
        • ##MS_SQLAuthenticatorCertificate##
        • ##MS_SQLReplicationSigningCertificate##
        • ##MS_SQLResourceSigningCertificate##
        • ##MS_SmoExtendedSigningCertificate##

 

Code



set nocount on;
set transaction isolation level read uncommitted;
go
 
; with cteServerPrincipalNT
(
      [name]
    , [create_date]
)
as
(
 
    select
 
              tblSSP.[name]
            , tblSSP.[create_date]
 
    from   sys.server_principals tblSSP
 
    where   tblSSP.[name] in
                (
                      'NT SERVICE\ReportServer'
                    , 'NT SERVICE\SQLSERVERAGENT'
                    , 'NT AUTHORITY\SYSTEM'
                    , 'NT Service\MSSQLSERVER'
                    , 'NT SERVICE\Winmgmt'
                    , 'NT SERVICE\SQLWriter'
                )
 
)
, cteServerPrincipalCertificate
(
      [name]
    , [create_date]
)
as
(
 
    select
 
              tblSSP.[name]
            , tblSSP.[create_date]
 
    from   sys.server_principals tblSSP
 
    inner join master.sys.certificates tblSC
  
        on tblSSP.[name] = tblSC.[name]
  
)
 
select top 1
 
          [sqlInstance]
            = cast(serverproperty('servername') as sysname)
 
        , [installDate] 
            = convert(varchar(30), cteSP.[create_date], 100)
 
        , [servicePackDate] 
            = case

				when (
						   ( cteSPC.[create_date] > cteSP.[create_date] ) 
						or ( @@version not like '%RTM%') 
					) then
						convert(varchar(30), cteSPC.[create_date], 100)
						
				else null

			  end
 
from   cteServerPrincipalNT cteSP
 
outer apply cteServerPrincipalCertificate cteSPC
 
order by
          cteSP.[create_date] asc



Output

SQL Server – Database Size Aggregated By File Type

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 – List Database Size

Background

Need to show a tally of database sizes, here is what we have thus far.

Code

Script




; with cteFile
(
 	  [database]
	, [fileType]
	, [filename]
	, [size]

)
as
(
 
	select
			  [database]
				= db_name(tblMF.database_id)
 
			, tblMF.[type_desc]
 
			, [filename]
				= tblMF.physical_name
 
			, [size]
				= tblMF.size
 
	from   master.sys.master_files tblMF
 
) 
 
select 

		  [database] 

		, [dataInGB]
			= 
				cast
				(
					( [ROWS] * 1.000/128 ) 
						/ 1000
					as decimal(20, 3)
				)

		, [logInGB]
			 =	cast
				(
					( [LOG] * 1.000/128 ) 
						/ 1000
					as decimal(20, 3)
				)

		, [fileStreamInGB]
			= cast
				(
					( [FILESTREAM] * 1.000/128 ) 
						/ 1000
					as decimal(20, 3)
				)
from
		(
			select
 
				  [database] 
			    , [fileType] 
				, [size]
					= isNull(size, 0)
 
			from   cteFile

			where fileType in 
					( 
						  'ROWS'
						, 'LOG'
						, 'FILESTREAM' 
					)
		
		) tblA

PIVOT
(
	sum(
			[size]
	   )
	FOR [fileType] in 
			( 
				  ROWS
				, LOG
				, FILESTREAM 
			)
) AS pvtTable

order by 
		[database] asc


Output

 

Commentary

We are using the master.sys.master_files to quickly get stats on all databases in a SQL Server Instance.

Unfortunately for the FileStream file type the size is left at 0.

 

Connect Item

  1. sys.master_files does not show accurate size information
    • ID :- 377223
    • Link :- Link
    • Opened By :- Michael Hotek
    • Status :- Closed
    • Type :- Bug
    • Opened On :- 2015-Nov-13th
  2. Filestream file does not have correct max file size after restoring and then standbying database – by RansomingSQL
    • ID :- 3131240
    • Link :- Link
    • Opened By :- RansomingSQL
    • Status :- Active
    • Type :- Bug
    • Opened On :- 2017-March-29th

SQL Server – Partial Database Restores – Benefits & Impact

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.