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

SSMS – Error Message – “Property Owner is not available for Database”

Background

Trying to access the database property on one of our databases.

Error

Error Message

Syntax

Property Owner is not available for Database ‘[database]’. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)

Sample

Property Owner is not available for Database ‘[AdminDB]’. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)

 

Troubleshooting

Metadata

sp_helpdb

Syntax

use master
go

exec sp_helpdb
go

Output

Explanation

  1. Database
    • AdminDB
      • ~~UNKNOWN~~

sys.databases

Syntax

select 
		  tblSD.[database_id]
		, tblSD.[name]
		, tblSD.[owner_sid]
		, [owner]
			= SUSER_SNAME(tblSD.owner_sid)

from [master].[sys].[databases] tblSD
go

Output

Explanation

  1. Database
    • AdminDB
      • SUSER_SNAME(sys.databases.owner_sid) returns null

 

Remediation

Alter Authorization

Syntax


use [master]
go

ALTER AUTHORIZATION
DATABASE::[database]
TO [sa]
;

Sample


use master
go

BEGIN TRAN

	exec sp_helpdb

	ALTER AUTHORIZATION 
		ON DATABASE::[AdminDB] 
		TO [sa]
		;    

	exec sp_helpdb

ROLLBACK

Output

 

Reproducible

SSMS – v2017

The error does not occur in SSMS v2017.

SSMS – v2014

Noticed it in v2014 when trying to access the database property for an orphaned database.

 

References

  1. Microsoft
    • Docs > SQL > T-SQL > Statements
      • ALTER AUTHORIZATION (Transact-SQL)
        Link
    • Docs > SQL > T-SQL > Functions

Sql Server Agent – Step – “SQL Server Integration Services Package” – Error – “Errors were detected in the command line arguments…”

Background

Trying to edit the parameters on a SQL Server Agent Job, but ran into a hard wall.

 

Error Message

Error

Image

Textual

TITLE: Microsoft SQL Server Management Studio

Errors were detected in the command line arguments, please make sure all arguments are set correctly. (SqlManagerUI)

 

Advanced Information

Upon clicking the “Advanced Information” we will click the message pasted below.

Image

Textual

===================================

Errors were detected in the command line arguments, please make sure all arguments are set correctly. (SqlManagerUI)

——————————
Program Location:

at Microsoft.SqlServer.Management.SqlManagerUI.DTSJobSubSystemDefinition.Microsoft.SqlServer.Management.SqlManagerUI.IJobStepPropertiesControl.Save(JobStepData data, Boolean isSwitching)
at Microsoft.SqlServer.Management.SqlManagerUI.JobStepProperties.OnGatherUiInformation(RunType runType)
at Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.OnGatherUiInformation(RunType runType)

Scenario

This problem is reproducible this way.

  1. Start Sql Server Management Studio ( SSMS )
  2. Connect to SQL Server Instance
  3. Access SQL Server Agent \ Jobs \ [Job Name]
  4. Edit a Job Step whose type is “SQL Server Integration Services Package
  5. There are two tabs, Package and Configuration
  6. Access the “Configuration” Tab
  7. Within the “Configuration” Tab there are three Tabs.  The Tabs are “Parameters”, “Connection Managers”, and Advanced
  8. Select the “ExcelConnectionManager_ConnectionString” parameter
    • Click on “ellipse” button
    • The “Edit Literal Value for Execution” window appears
    • Access the “value” textbox and make cosmetic changes
    • Click the OK button
  9. The error, “Errors were detected in the command line arguments, please make sure all arguments are set correctly. (SqlManagerUI)“, aforementioned appears

 

Quick Points

  1. Other Parameters can be modified
    • This error only occurs when we tried amending the Excel File Connection String
    • We were able to modify other parameters; such as SQL Server and SMTP Server
  2. Connection Manager
    • All Connections ( Excel, SQL Server, and SMTP) can be successfully modified via the Connection Manager Tab

 

Honorable Mention

  1. Cody Konior
    • Agent job errors when running an SSIS package
      Published On :- 2015-August-2nd
      Link

 

SQL Server Versions

Here are the versions that exhibit this issue.

SQL Server Engine

Tabulated

Version Version Literal Version#
SQL Server v2014
Microsoft SQL Server 2014 (SP2-CU4) (KB4010394) – 12.0.5540.0 (X64) 12.0.5540.0

 

SQL Server Client

SQL Server Management Studio ( SSMS )

Tabulated

Version Version Literal Version#
SQL Server v2017
v 17.2 14.0.17177.0
SQL Server v2014
SQL Server 2014 SP2 CU10 12.0.5571.0

 

Image

SQL Server v2017

SQL Server v2014

SSMS – “An error occurred while executing batch. Error message is: Exception of type ‘System.OutOfMemoryException’ was thrown”

Background

Eventful day this morning, had to take a walk around the office block.

You know it is a good day when you wake @ 4: 30 AM, pulled in the smartphone and heard what smart bloggers have to say, make the 5:30 AM Bus, made the train and walked the lake just to feel good about work.

Error

An error occurred while executing batch. Error message is: Exception of type ‘System.OutOfMemoryException’ was thrown

Remediation

It is not the Server, but your client, Sql Server Management Studio ( SSMS), in this case.

No need to reboot, but closed the Chrome Tabs, and when that did not help, exited SSMS.

 

Culprit

May be too much “Query Execution Plan” digest.

Looking for a lot of things through the Graphics pane; graphics is always more taxing on system.

Heart

Heart and I spoke about the next few days and how everything is sane and good.

 

Listening

Have a nice bit of work in, and it is only 2 PM.

Daryl Worley
Second Wind
Link

Don’t need a second wind, but can relate….