SSMS – Linked Server – Column Metadata

Background

Had a good meeting this morning while we try to figure out how to better support our Developers.

One of the issues they brought up was an inability to view the datatype of linked Server tables.

Pictorial

Here is the deepest granularity when we connect to our Linked Server.

Image

ssms.columns.20190114.0425PM.PNG

Explanation

  1. We see the following
    • Server
    • Databases
    • Objects
      • Tables
      • Views

View Columns

Script

Outline

Here are avenues we can explore to view column metadata :-

  1. sp_columns_ex
  2. Openquery/sys
    • sys.all_columns
  3. Openquery/informational_schema
    • informational_schema.columns

Choices

exp_columns_ex

Syntax

exec sp_columns_ex
		  @table_server
		, @table_catalog
		, @table_schema
		, @table_name   

Sample

declare @linkedServer   sysname
declare @linkedDatabase sysname
declare @linkedSchema   sysname
declare @linkedTable    sysname

set @linkedServer= 'AWS-JobBuilder'
set @linkedDatabase = 'acs_ap'
set @linkedSchema = 'dbo'
set @linkedTable = 'ap_user'

exec sp_columns_ex
		  @table_server  = @linkedServer
		, @table_catalog = @linkedDatabase
		, @table_schema  = @linkedSchema
		, @table_name    = @linkedTable

Output

sp_tables_ex.2019014.0443pm

openquery/sys.*

Syntax

select top 10 *

from   openquery
        (
              [AWS-JobBuilder]

            , '
                    select
                              [server] = serverproperty(''servername'')
                            , [database] = db_name()
                            , [schema] = tblSS.name
                            , [object] = tblSAO.name
                            , [column] = tblSAC.name
                            , [type]   = tblST.[name]
                            , tblST.max_length
                            , tblST.is_nullable

                    from   sys.schemas tblSS

                    inner join sys.all_objects tblSAO

                            on tblSS.schema_id = tblSAO.schema_id

                    inner join sys.all_columns tblSAC

                        on tblSAO.object_id = tblSAC.object_id

                    inner join sys.types tblST

                        on  tblSAC.system_type_id = tblST.system_type_id
                        and tblSAC.user_type_id = tblST.user_type_id

              '
        )

Output

openquery.sys.all.2019014.0452pm

openquery/information_schema.columns

Syntax
select top 10 *

from   openquery
        (
              [AWS-JobBuilder]

            , '
                select top 100 

                          [server] = serverproperty(''servername'')

                        , [database] = tblSIC.[TABLE_CATALOG]

                        , [schema] = tblSIC.[TABLE_SCHEMA]

                        , [object] = tblSIC.[TABLE_NAME]

                        , [column] = tblSIC.[COLUMN_NAME]

                        , [position] = tblSIC.[ORDINAL_POSITION]

                        , [dataType] = tblSIC.[DATA_TYPE]

                        , [charMaxLength] = tblSIC.[CHARACTER_MAXIMUM_LENGTH]

                        , [charOctetLength] = tblSIC.[CHARACTER_OCTET_LENGTH]

                from   information_schema.columns tblSIC

              '
        )			   	

Output

openquery.openquery.informational_columns.2019014.0515PM.PNG

Summary

Unfortunately, SQL Server Management Studio ( SSMS ) v17.x does not let us view columns on Linked Servers.

To gather column level metadata, one has to write code.

SQL Server – SSMS – Query Plan – Compare

Background

One of my favorite go to tool with SQL Server Query troubleshooting is to simple use SQL Server Management Studio (SSMS) to compare the query plans generated via running the same SQL across different machines or SQL Server Versions.\

Use

To use, please follow these simple steps :-

  1. Invoke SSMS
    • Query 1
      • Enable “Include Actual Execution Plan”
      • Run Query
      • Save resultant plan
    • Query 2
      • Enable “Include Actual Execution Plan”
      • Run Query
      • Save resultant plan
    • Access Query Plan pane
      • Access either Query’s Plan tab
      • Right click in an empty area of the pane
      • The drop-down menu appears
      • From the drop down menu, please choose “Compare Showplan”
      • From the File Open window,  choose the saved query plan from the opposing query

 

Sample

Sample – 01

Showplan Comparison

Pictorial

queryPlan.compare.showPlanComparison.20190109.0535PM.PNG

Explanation

  1. Certificates
    • Top Query :- Index Seek
    • Bottom Query :- Index Scan

 

Showplan Analysis

queryPlan.compare.showPlanAnalysis.20190109.0534PM.PNG

Explanation

  1. Scenarios
    • Difference :- 15316%
    • Estimated top/left :- 3274
    • Estimated bottom/right :- 504816

 

Requirement

As for client, requires at least SQL Server Management Studio v2017.

Works across various SQL Server Database Engine Versions.

Tested here against Server versions :-

  1. v2005
  2. v2017

 

SQL Server On Linux – Post Installation – Configuration – Set Folders

Background

Upon installation, SQL Server on Linux utilizes the same storage folders for data, log, and backups.

Outline

  1. Review Current Settings
  2. Provision Folders
  3. Register Folder Settings on SQL Server
  4. Restart SQL Server
  5. Review new folder settings

Processing

Review Current Settings

SQL Server Management Studio ( SSMS )

Let us use our GUI Tool, SSMS, to determine current default folder settings.

Server Properties

Server Properties – Database Settings
Images

configuration.defaultLocations.ssms.20181127.1135AM

Explanation
  1. Data, Log, Backup
    • /var/opt/mssql/data/

Provision Folders

Outline

  1. Create An Array
  2. Fill Array with list of folders
  3. Iterate Array List
    • Get Folder
    • Determine if folder exists
    • If Folder does not exist, please create it
    • Set Folder Attributes
      • Set Folder Owner to mssql
      • Set Folder Group to mssql

Script


#!/bash/bash

echo "Preparing folders"

declare -a _folderList=(
                          "/app/Microsoft/sqlServer/data"
                          "/app/Microsoft/sqlServer/log"
                          "/app/Microsoft/sqlServer/backup"
                          "/app/Microsoft/sqlServer/dumpCrash"
)

#Get Number of elements
_iNumberofElements=${#_folderList[*]}

echo "Number of elements $_iNumberofElements "

_i=0

while [ $_i -lt $_iNumberofElements ]
do

  #Current Folder
  _folder=${_folderList[_i]}

  _folderExistence=false

   #Does Folder Exist
   if [ -d $_folder ]
   then

     _folderExistence=true

   fi

   #Speak Folder
   echo "$_i ) $_folder "

   #if Folder does not exist, create it
   if [ $_folderExistence = false ]
   then

       echo -e "\tCreating Folder $_folder"

       mkdir --parents $_folder

  fi

  #Change Folder owner
  echo -e "\tChange Folder Owner"
  chown mssql $_folder

  #Change Folder Group
  echo -e "\tChange Folder Group"
  chgrp mssql $_folder

  #Move to next array element
  _i=$((_i+1))

done

Configure SQL Server Default Folders

Outline

  1. Issue folderlocation.default[x]
    • Data
    • Log
    • Backup
    • Dump

Script


/opt/mssql/bin/mssql-conf set filelocation.defaultdatadir /app/Microsoft/sqlServer/data

/opt/mssql/bin/mssql-conf set filelocation.defaultlogdir /app/Microsoft/sqlServer/log

/opt/mssql/bin/mssql-conf set filelocation.defaultbackupdir /app/Microsoft/sqlServer/backup

/opt/mssql/bin/mssql-conf set filelocation.defaultdumpdir /app/Microsoft/sqlServer/dumpCrash

Restart SQL Server Service

Outline

Restart SQL Server Service to effect changes

Script


sudo systemctl restart mssql-server.service

Review new settings

SQL Server Management Studio ( SSMS )

Let use again use SSMS to review folder settings.

Server Properties

Server Properties – Database Settings
Images

configuration.defaultLocations.ssms.post.20181127.0245PM

Explanation
  1. We were able to change our targeted folders
    • Data, Log, Backup

 

SQL Server Agent – Configuration on Linux

Background

Post installation of SQL Server on our Linux Box, it is time to review our SQL Server Agent component.

Lineage

  1. SQL Server – Installation on Linux/CentOS
    Link

Check List

Let us go through our checklist.

Outline

Is SQL Server Configured?

SQL Server

Metadata

sys.dm_server_services

SQL

select *
from   sys.dm_server_services 

Output

sys.dm_server_services.pre.20181127.0602AM.PNG

Explanation
  1. sys.dm_server_services
    • Startup Type
      • Automatic
    • Status
      • Stopped
    • Service Account
      • LocalSystem
    • Clustered
      • No

 

Configuration File

/opt/mssql/bin/mssql-conf

List

Let us utilize the SQL Server Configuration Utility ( /opt/mssql/bin/mssql-conf ) to identify configurable elements.

SQL

sudo /opt/mssql/bin/mssql-conf list

Output

mssql-conf.pre.20181127.0607AM.PNG

Explanation
  1. sqlagent.databasemailprofile
    • Database Mail Profile
  2. sqlagent.enabled
    • Is SQL Server Agent Enabled
  3. sqlagent.errorlogfile
    • Error Log File
  4. sqlagent.errorlogginglevel
    • Error Logging Level

 

Configure

/opt/mssql/bin/mssql-conf

Process

Let us utilize the SQL Server Configuration Utility ( /opt/mssql/bin/mssql-conf ) to make configuration changes.

Make Changes

Command

sudo /opt/mssql/bin/mssql-conf set sqlagent.enabled true

Output

mssql-conf.set.20181127.0610AM.PNG

Explanation
  1. sqlagent.enabled
    • Enabled SQL Server Agent

 

Effect Changes

To effect the changes, please use systemctl

Command

sudo systemctl restart mssql-server.service

Output

mssql-conf.set.systemctl.20181127.0732AM

Explanation
  1. Restarted SQL Server Service to effect the changes

 

Configuration

SQL Server

Metadata

sys.dm_server_services

SQL

select *
from   sys.dm_server_services

Output

sys.dm_server_services.post.20181127.0735AM

Explanation
  1. sys.dm_server_services
    • Startup Type
      • Automatic
    • Status
      • Running
    • Process ID
      • 4116 ( pid )
        • Obviously yours will be different
    • Last Startup Time
      • 1600-12-31 16:00:00.0000000 -08:00
        • Not Accurate
    • Service Account
      • LocalSystem
    • Clustered
      • No

Usage

SQL Server Management Studio ( SSMS )

Object Explorer

Images
Images – Before

sqlServerExplorer.pre.20181127.0739AM.PNG

Images – After

sqlServerExplorer.post.20181127.0740AM.PNG

 

SQL Server – Script Statistics using SSMS

Background

This is another post that covers how to tackle issues when data in Production is a lot more that corresponding data in our Development environment.

In this post, we script out statistics from our originating environment.

Lineage

  1. SQL Server – Statistics – Fake Stats
    Link
  2. SQL Server – Clone Database – Schema & Statistics
    Link

 

SSMS

Identify Statistics

SQL

Syntax


exec sp_helpstats
         @objname = [object_name]
       , @results = 'ALL'

Sample

declare @objname sysname
declare @results varchar(30)

set @objname = '[Dimension].[Date]'
set @results = 'ALL'

exec sp_helpstats
          @objname = @objname
	, @results = @results

Output

sp_helpstats.20181117.0253PM

Script Objects

Steps

  1. Select Database
    • Right click on Database and from the drop-down menu choose Tasks \ Generate Scripts
    • The “Generate and Publish Scripts” Window appears
      • Tabs
        • Tab – “Choose Objects”
          • Please select Objects
        • Tab – “Scripting Options”
          • click on the Advanced button
          • Please ensure that the options listed below are checked
            • General
              • Check for Object Existence
                • True
              • Script Statistics
                • Script Statistics and Histograms
              • Type of data to script
                • Schema Only
            • Table and View Options
              • Script Check Constraints
                • True
              • Script Indexes
                • True
              • Script Primary Keys
                • True
              • Script Unique Keys
                • True

Images

Choose Objects

GenerateAndPublishScripts.Tab.ChooseObjects.2018117.0257PM.PNG

Set Scripting Options

GenerateAndPublishScripts.Tab.SetScriptingOptions.AdvancedScriptingOptions.2018117.0300PM.PNG

Summary

GenerateAndPublishScripts.Tab.Summary.2018117.0302PM.PNG

Save or Publish Scripts

GenerateAndPublishScripts.Tab.SaveOrPublishScripts.2018117.0304PM.PNG

SQL

Review generated SQL

Dimension.Date

Sample


if  exists (select * from sys.stats where name = N'PK_Dimension_Date' and object_id = object_id(N'[Dimension].[Date]'))
UPDATE STATISTICS [Dimension].[Date]([PK_Dimension_Date]) WITH STATS_STREAM = 0x01000000010000000000000000000000CDF5EE6F000000001C19000000000000DC18000000000000280300002800000003000A00000000000000000000000000070000004DE1CF001DA60000B505000000000000B5050000000000000000803F846D333A0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000C8000000C800000001000000130000000000404000A0B644000000000000404000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001100000000000000000000000000000070170000000000007817000000000000400600000000000056060000000000006C0600000000000082060000000000009806000000000000AE06000000000000C406000000000000DA06000000000000F00600000000000006070000000000001C07000000000000320700000000000048070000000000005E0700000000000074070000000000008A07000000000000A007000000000000B607000000000000CC07000000000000E207000000000000F8070000000000000E0800000000000024080000000000003A08000000000000500800000000000066080000000000007C080000000000009208000000000000A808000000000000BE08000000000000D408000000000000EA08000000000000000900000000000016090000000000002C09000000000000420900000000000058090000000000006E0900000000000084090000000000009A09000000000000B009000000000000C609000000000000DC09000000000000F209000000000000080A0000000000001E0A000000000000340A0000000000004A0A000000000000600A000000000000760A0000000000008C0A000000000000A20A000000000000B80A000000000000CE0A000000000000E40A000000000000FA0A000000000000100B000000000000260B0000000000003C0B000000000000520B000000000000680B0000000000007E0B000000000000940B000000000000AA0B000000000000C00B000000000000D60B000000000000EC0B000000000000020C000000000000180C0000000000002E0C000000000000440C0000000000005A0C000000000000700C000000000000860C0000000000009C0C000000000000B20C000000000000C80C000000000000DE0C000000000000F40C0000000000000A0D000000000000200D000000000000360D0000000000004C0D000000000000620D000000000000780D0000000000008E0D000000000000A40D000000000000BA0D000000000000D00D000000000000E60D000000000000FC0D000000000000120E000000000000280E0000000000003E0E000000000000540E0000000000006A0E000000000000800E000000000000960E000000000000AC0E000000000000C20E000000000000D80E000000000000EE0E000000000000040F0000000000001A0F000000000000300F000000000000460F0000000000005C0F000000000000720F000000000000880F0000000000009E0F000000000000B40F000000000000CA0F000000000000E00F000000000000F60F0000000000000C10000000000000221000000000000038100000000000004E1000000000000064100000000000007A100000000000009010000000000000A610000000000000BC10000000000000D210000000000000E810000000000000FE1000000000000014110000000000002A11000000000000401100000000000056110000000000006C1100000000000082110000000000009811000000000000AE11000000000000C411000000000000DA11000000000000F01100000000000006120000000000001C12000000000000321200000000000048120000000000005E1200000000000074120000000000008A12000000000000A012000000000000B612000000000000CC12000000000000E212000000000000F8120000000000000E1300000000000024130000000000003A13000000000000501300000000000066130000000000007C130000000000009213000000000000A813000000000000BE13000000000000D413000000000000EA13000000000000001400000000000016140000000000002C14000000000000421400000000000058140000000000006E1400000000000084140000000000009A14000000000000B014000000000000C614000000000000DC14000000000000F21400000000000008150000000000001E1500000000000034150000000000004A15000000000000601500000000000076150000000000008C15000000000000A215000000000000B815000000000000CE15000000000000E415000000000000FA15000000000000101600000000000026160000000000003C16000000000000521600000000000068160000000000007E160000000000009416000000000000AA16000000000000C016000000000000D616000000000000EC16000000000000021700000000000018170000000000002E1700000000000044170000000000005A17000000000000100013000000803F000000000000803F94360B040000100013000000803F000000400000803F97360B040000100013000000803F0000E0400000803F9F360B040000100013000000803F0000E0400000803FA7360B040000100013000000803F0000E0400000803FAF360B040000100013000000803F000040400000803FB3360B040000100013000000803F0000E0400000803FBB360B040000100013000000803F0000E0400000803FC3360B040000100013000000803F000040400000803FC7360B040000100013000000803F0000E0400000803FCF360B040000100013000000803F0000E0400000803FD7360B040000100013000000803F0000E0400000803FDF360B040000100013000000803F0000E0400000803FE7360B040000100013000000803F0000E0400000803FEF360B040000100013000000803F0000E0400000803FF7360B040000100013000000803F0000A0400000803FFD360B040000100013000000803F0000A0400000803F03370B040000100013000000803F0000E0400000803F0B370B040000100013000000803F000040400000803F0F370B040000100013000000803F0000E0400000803F17370B040000100013000000803F0000E0400000803F1F370B040000100013000000803F0000E0400000803F27370B040000100013000000803F0000A0400000803F2D370B040000100013000000803F0000A0400000803F33370B040000100013000000803F0000E0400000803F3B370B040000100013000000803F0000E0400000803F43370B040000100013000000803F0000E0400000803F4B370B040000100013000000803F000010410000803F55370B040000100013000000803F0000E0400000803F5D370B040000100013000000803F0000E0400000803F65370B040000100013000000803F0000E0400000803F6D370B040000100013000000803F0000E0400000803F75370B040000100013000000803F0000E0400000803F7D370B040000100013000000803F0000E0400000803F85370B040000100013000000803F0000A0400000803F8B370B040000100013000000803F0000E0400000803F93370B040000100013000000803F000040400000803F97370B040000100013000000803F0000A0400000803F9D370B040000100013000000803F000040400000803FA1370B040000100013000000803F0000A0400000803FA7370B040000100013000000803F0000E0400000803FAF370B040000100013000000803F000040400000803FB3370B040000100013000000803F0000E0400000803FBB370B040000100013000000803F0000E0400000803FC3370B040000100013000000803F0000A0400000803FC9370B040000100013000000803F0000E0400000803FD1370B040000100013000000803F0000A0400000803FD7370B040000100013000000803F000010410000803FE1370B040000100013000000803F0000E0400000803FE9370B040000100013000000803F000040400000803FED370B040000100013000000803F0000A0400000803FF3370B040000100013000000803F0000E0400000803FFB370B040000100013000000803F0000E0400000803F03380B040000100013000000803F0000E0400000803F0B380B040000100013000000803F000010410000803F15380B040000100013000000803F000010410000803F1F380B040000100013000000803F000010410000803F29380B040000100013000000803F0000E0400000803F31380B040000100013000000803F0000E0400000803F39380B040000100013000000803F0000E0400000803F41380B040000100013000000803F000040400000803F45380B040000100013000000803F0000E0400000803F4D380B040000100013000000803F000040400000803F51380B040000100013000000803F0000E0400000803F59380B040000100013000000803F000040400000803F5D380B040000100013000000803F0000E0400000803F65380B040000100013000000803F0000E0400000803F6D380B040000100013000000803F000040400000803F71380B040000100013000000803F0000E0400000803F79380B040000100013000000803F000040400000803F7D380B040000100013000000803F0000E0400000803F85380B040000100013000000803F0000E0400000803F8D380B040000100013000000803F000040400000803F91380B040000100013000000803F0000E0400000803F99380B040000100013000000803F0000E0400000803FA1380B040000100013000000803F0000E0400000803FA9380B040000100013000000803F0000E0400000803FB1380B040000100013000000803F0000E0400000803FB9380B040000100013000000803F0000E0400000803FC1380B040000100013000000803F000040400000803FC5380B040000100013000000803F0000E0400000803FCD380B040000100013000000803F0000E0400000803FD5380B040000100013000000803F0000E0400000803FDD380B040000100013000000803F0000E0400000803FE5380B040000100013000000803F0000E0400000803FED380B040000100013000000803F0000E0400000803FF5380B040000100013000000803F0000E0400000803FFD380B040000100013000000803F000040400000803F01390B040000100013000000803F0000E0400000803F09390B040000100013000000803F000040400000803F0D390B040000100013000000803F0000E0400000803F15390B040000100013000000803F0000E0400000803F1D390B040000100013000000803F0000E0400000803F25390B040000100013000000803F0000E0400000803F2D390B040000100013000000803F0000E0400000803F35390B040000100013000000803F0000E0400000803F3D390B040000100013000000803F000040400000803F41390B040000100013000000803F0000E0400000803F49390B040000100013000000803F000040400000803F4D390B040000100013000000803F0000E0400000803F55390B040000100013000000803F0000E0400000803F5D390B040000100013000000803F000040400000803F61390B040000100013000000803F0000E0400000803F69390B040000100013000000803F0000E0400000803F71390B040000100013000000803F000040400000803F75390B040000100013000000803F0000E0400000803F7D390B040000100013000000803F000040400000803F81390B040000100013000000803F0000E0400000803F89390B040000100013000000803F0000E0400000803F91390B040000100013000000803F0000A0400000803F97390B040000100013000000803F0000A0400000803F9D390B040000100013000000803F0000A0400000803FA3390B040000100013000000803F0000A0400000803FA9390B040000100013000000803F0000A0400000803FAF390B040000100013000000803F0000A0400000803FB5390B040000100013000000803F0000A0400000803FBB390B040000100013000000803F0000A0400000803FC1390B040000100013000000803F0000A0400000803FC7390B040000100013000000803F0000A0400000803FCD390B040000100013000000803F0000A0400000803FD3390B040000100013000000803F0000A0400000803FD9390B040000100013000000803F0000A0400000803FDF390B040000100013000000803F0000A0400000803FE5390B040000100013000000803F0000A0400000803FEB390B040000100013000000803F0000E0400000803FF3390B040000100013000000803F0000E0400000803FFB390B040000100013000000803F0000E0400000803F033A0B040000100013000000803F0000E0400000803F0B3A0B040000100013000000803F0000E0400000803F133A0B040000100013000000803F0000E0400000803F1B3A0B040000100013000000803F0000E0400000803F233A0B040000100013000000803F0000E0400000803F2B3A0B040000100013000000803F0000E0400000803F333A0B040000100013000000803F0000E0400000803F3B3A0B040000100013000000803F0000E0400000803F433A0B040000100013000000803F0000E0400000803F4B3A0B040000100013000000803F0000E0400000803F533A0B040000100013000000803F0000E0400000803F5B3A0B040000100013000000803F0000E0400000803F633A0B040000100013000000803F0000E0400000803F6B3A0B040000100013000000803F0000E0400000803F733A0B040000100013000000803F0000E0400000803F7B3A0B040000100013000000803F0000E0400000803F833A0B040000100013000000803F0000E0400000803F8B3A0B040000100013000000803F0000E0400000803F933A0B040000100013000000803F0000E0400000803F9B3A0B040000100013000000803F0000E0400000803FA33A0B040000100013000000803F0000E0400000803FAB3A0B040000100013000000803F0000E0400000803FB33A0B040000100013000000803F0000E0400000803FBB3A0B040000100013000000803F0000E0400000803FC33A0B040000100013000000803F0000E0400000803FCB3A0B040000100013000000803F0000E0400000803FD33A0B040000100013000000803F0000E0400000803FDB3A0B040000100013000000803F0000E0400000803FE33A0B040000100013000000803F0000E0400000803FEB3A0B040000100013000000803F0000E0400000803FF33A0B040000100013000000803F0000E0400000803FFB3A0B040000100013000000803F0000E0400000803F033B0B040000100013000000803F0000E0400000803F0B3B0B040000100013000000803F0000E0400000803F133B0B040000100013000000803F0000E0400000803F1B3B0B040000100013000000803F0000E0400000803F233B0B040000100013000000803F0000E0400000803F2B3B0B040000100013000000803F0000E0400000803F333B0B040000100013000000803F0000E0400000803F3B3B0B040000100013000000803F0000E0400000803F433B0B040000100013000000803F0000E0400000803F4B3B0B040000100013000000803F0000E0400000803F533B0B040000100013000000803F0000E0400000803F5B3B0B040000100013000000803F0000E0400000803F633B0B040000100013000000803F0000E0400000803F6B3B0B040000100013000000803F0000E0400000803F733B0B040000100013000000803F0000E0400000803F7B3B0B040000100013000000803F0000E0400000803F833B0B040000100013000000803F0000E0400000803F8B3B0B040000100013000000803F0000E0400000803F933B0B040000100013000000803F0000E0400000803F9B3B0B040000100013000000803F0000E0400000803FA33B0B040000100013000000803F0000E0400000803FAB3B0B040000100013000000803F0000E0400000803FB33B0B040000100013000000803F0000E0400000803FBB3B0B040000100013000000803F0000E0400000803FC33B0B040000100013000000803F0000E0400000803FCB3B0B040000100013000000803F0000E0400000803FD33B0B040000100013000000803F0000E0400000803FDB3B0B040000100013000000803F0000E0400000803FE33B0B040000100013000000803F0000E0400000803FEB3B0B040000100013000000803F0000E0400000803FF33B0B040000100013000000803F0000E0400000803FFB3B0B040000100013000000803F0000E0400000803F033C0B040000100013000000803F0000E0400000803F0B3C0B040000100013000000803F0000E0400000803F133C0B040000100013000000803F0000E0400000803F1B3C0B040000100013000000803F0000E0400000803F233C0B040000100013000000803F000010410000803F2D3C0B040000100013000000803F000010410000803F373C0B040000100013000000803F000010410000803F413C0B040000100013000000803F0000A0400000803F473C0B040000100013000000803F000000000000803F483C0B040000B505000000000000, ROWCOUNT = 1826, PAGECOUNT = 34
GO

/****** Object:  Statistic [statFiscalYear]    Script Date: 11/17/2018 3:04:50 PM ******/
if not exists (select * from sys.stats where name = N'statFiscalYear' and object_id = object_id(N'[Dimension].[Date]'))
CREATE STATISTICS [statFiscalYear] ON [Dimension].[Date]([Fiscal Year]) WITH STATS_STREAM = 0x01000000010000000000000000000000BCAE575B0000000066020000000000002602000000000000380300003800000004000A000000000000000000000000000700000000F6DE009BA900002207000000000000220700000000000000000000ABAA2A3E000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000006000000060000000100000014000000000080400040E4440000000000008040000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000011000000000000000000000000000000BA00000000000000C200000000000000300000000000000047000000000000005E0000000000000075000000000000008C00000000000000A3000000000000001000140000009843000000000000803FDC070000040000100014000080B643000000000000803FDD070000040000100014000080B643000000000000803FDE070000040000100014000080B643000000000000803FDF070000040000100014000000B743000000000000803FE00700000400001000140000007442000000000000803FE10700000400002207000000000000

Explanation

  1. Create Statistics
    • Does Statistics exist
    • If not
      • Create Statistics
        • Stats Stream
          • Binary Blob
        • Predicates
          • ROWCOUNT = 1826
          • PAGECOUNT = 34

SQL Server – Database Files – Minimum File Size

Background

There is a setting that is unfortunately difficult to determine within Transact SQL.

That setting is the smallest size a data or log file can be shrunk down to.

GUI

SQL Server Management Studio ( SSMS )

Database Property

Image

bigfix_Enterprise_initialSizes_20180711_1119AM

Code

Syntax


dbcc page
(
      {'dbname' | dbid}
    , filenum
    , pagenum
    [, printopt={0|1|2|3} ]
)

Explanation

  1. DBCC PAGE
    • Database
    • FileID
    • Pagenum
      • 0
    • printopt
      • 3 – page header plus detailed per-row interpretation

SQL

Data files

Code


set nocount on;
go

set XACT_ABORT on
go

declare @database		    sysname
declare @dbid			    int

declare @fileID			    int
declare @fileIDAsString		varchar(255)

declare @fileSymbolicName   sysname
declare @filePhysicalName	nvarchar(600)

declare @sql				nvarchar(255)

declare @FORMAT_SQL			varchar(255)

declare @tblDBCCPage Table
(

	  [parentObject] sysname not null
	, [object]		 sysname not null
	, [field]		 sysname not null
	, [value]		 varchar(255) null	

	, [computedInKB]
		as case
				when ([field] like '%size%') then
						[value] * 8

				else null
		  end

	, [computedInMB]
		as case
				when ([field] like '%size%') then
						(
							[value] * 8
						)
						/ ( 1024)

				else null
		  end
)

set @FORMAT_SQL = 'DBCC PAGE(''%s'',%s,0,3) with tableresults, no_infomsgs';

set @database = 'BFEnterprise';

set @fileID = 1
set @fileID = 1
set @dbid = db_id(@database);

select
		  @fileSymbolicName = tblSMF.[name]
		, @filePhysicalName = tblSMF.physical_name

from   sys.master_files tblSMF 

where  tblSMF.database_id = @dbid

and    tblSMF.[file_id] = @fileID

set @fileIDAsString = cast(@fileID as varchar(10));

if (@fileID is not null)
begin

	DBCC TRACEON(3604) with no_infomsgs;

	exec master..xp_sprintf
			  @sql output
			, @FORMAT_SQL
			, @database
			, @fileIDAsString

	print @sql

	insert into @tblDBCCPage
	(
		  [parentObject]
		, [object]
		, [field]
		, [value]
	)
	exec(@sql)

	-- exec sp_helpdb [model]
	select
		      [database] = @database
			, [fileSymbolicName] = @fileSymbolicName
			, [filePhysicalName] = @filePhysicalName
			, [parentObject]
			, [object]
			, [field]
			, [value]
			, [computedInKB]
			, [computedInMB]

	from   @tblDBCCPage tbl

	where  (
			( field in
				(
					'MinSize'
					, 'Size'
				)
			)

	DBCC TRACEOFF(3604) with no_infomsgs

end -- if (@fileID is not null)

Output

bigfix_Enterprise_Code_20180711_1130AM.png

Log File

Explanation

Unfortunately, same does not work for log files.

You will get the error pasted below.

Output


DBCC PAGE('BFEnterprise',2,0,3) with tableresults, no_infomsgs
Msg 8939, Level 16, State 98, Line 6
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID -1585190943824412672 (type Unknown), page (0:0). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 2057 and -1.
Msg 8909, Level 16, State 1, Line 6
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 13792724832485376 (type Unknown), page ID (2:3) contains an incorrect page ID in its page header. The PageId in the page header = (102:7274528).
Msg 8909, Level 16, State 1, Line 6
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 12947986374328320 (type Unknown), page ID (2:6) contains an incorrect page ID in its page header. The PageId in the page header = (117:7077985).
Msg 8909, Level 16, State 1, Line 6
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 9007714657435648 (type Unknown), page ID (2:7) contains an incorrect page ID in its page header. The PageId in the page header = (32:7536756).
Msg 8939, Level 16, State 98, Line 6
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 7036874954637312 (type Unknown), page (0:0). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 2057 and -14.

<span id="mce_SELREST_start" style="overflow:hidden;line-height:0;"></span>

Crediting

Edward Dortland
File Header Properties
Link

Summary

Unfortunately, the word size is imprecise and thus need to be surrounded to facilitate context.

When Database properties are viewed through SSMS, for each data and log file, there is a number identified as “Initial Size“.

I think one has to be careful and read more into what it means and how it is sourced.

References

  1. Edward Dortland
    • Stack Overflow
      • File Header Properties
        Link
  2. Paul Randal
    • Server & Tools Blogs > Data Platform Blogs > SQL Database Engine Blog
      • How to use DBCC PAGE
        Link