Disk Info

I have a daily interest to know how the disks are used, and this script give some useful data from the Windows point of view.

function Get-Disk {
[CmdletBinding()]
param(
  [Parameter(Mandatory=$true,ValueFromPipeline=$true)]
  [String]$ComputerName
)
Begin {
  "Get-Disk( '$ComputerName' )" | Write-Verbose
  $Stopwatch = [System.Diagnostics.Stopwatch]::StartNew()
}

Process {
  $CimSessionOption = New-CimSessionOption -Protocol Dcom
  $CimSession = New-CimSession -SessionOption $CimSessionOption -Verbose:$false -ComputerName $ComputerName
  $SqlDisks = @()
  'Get Local Disks...' | Write-Verbose
  $Disks = Get-CimInstance -ClassName Win32_LogicalDisk -Filter 'DriveType=3' -CimSession $CimSession -Verbose:$false
  foreach ($Disk in $Disks) {
    "Disk.DeviceID = '$($Disk.DeviceID)'." | Write-Verbose
    $SqlDisk = New-Object -TypeName PSObject
    $SqlDisk.PSObject.TypeNames.Insert(0, 'SqlAdmin.Disk')
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name DriveLetter -Value $Disk.DeviceID
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name DiskFreeSpaceBytes -Value $Disk.FreeSpace
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name DiskSizeBytes -Value $Disk.Size
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name FileSystem -Value $Disk.FileSystem
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name VolumeDirty -Value $Disk.VolumeDirty
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name VolumeName -Value $Disk.VolumeName
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name VolumeSerialNumber -Value $Disk.VolumeSerialNumber

    $Partition = Get-CimAssociatedInstance -CimInstance $Disk -ResultClassName Win32_DiskPartition -Verbose:$false
    "Partition.DeviceID = '$($Partition.DeviceID)'." | Write-Verbose
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name PartitionDeviceID -Value $Partition.DeviceID
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name BlockSizeBytes -Value $Partition.BlockSize
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name BlockCount -Value $Partition.NumberOfBlocks
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name PartitionDiskIndex -Value $Partition.DiskIndex
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name PartitionSizeBytes -Value $Partition.Size
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name StartingOffset -Value $Partition.StartingOffset
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name PartitionType -Value $Partition.Type

    $Drive = Get-CimAssociatedInstance -CimInstance $Partition -ResultClassName Win32_DiskDrive -Verbose:$false
    "Drive.DeviceID = '$($Drive.DeviceID)'." | Write-Verbose
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name DrivePartitionCount -Value $Drive.Partitions
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name DriveBytesPerSector -Value $Drive.BytesPerSector
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name DriveDeviceID -Value $Drive.DeviceID
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name InterfaceType -Value $Drive.InterfaceType
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name DriveFirmwareRevision -Value $Drive.FirmwareRevision
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name DriveModel -Value $Drive.Model
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name DriveSizeBytes -Value $Drive.Size
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name DriveStatus -Value $Drive.Status
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name DriveSerialNumber -Value $Drive.SerialNumber

    $Volume = $Volumes = Get-CimInstance -ClassName Win32_Volume -Filter "DriveLetter='$($Disk.Name)'" -CimSession $CimSession -Verbose:$false
    "Volume.Label = '$($Volume.Label)'." | Write-Verbose
    'Running Defrag Analysis...' | Write-Verbose
    $DefragStopwatch = [System.Diagnostics.Stopwatch]::StartNew()
    $Report = Invoke-CimMethod -CimSession $CimSession -InputObject $Volume -MethodName DefragAnalysis -Verbose:$false
    $DefragStopwatch.Stop()
    "Defrag Analysis completed. Duration = $($DefragStopwatch.Elapsed.ToString()) [hh:mm:ss.ddd]." | Write-Verbose
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name AverageFileSize -Value $Report.DefragAnalysis.AverageFileSize
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name AverageFragmentsPerFile -Value $Report.DefragAnalysis.AverageFragmentsPerFile
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name AverageFreeSpacePerExtent -Value     $Report.DefragAnalysis.AverageFreeSpacePerExtent
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name ClusterSize -Value $Report.DefragAnalysis.ClusterSize
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name VolumeFreeSpace -Value $Report.DefragAnalysis.FreeSpace
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name FreeSpacePercentFragmentation -Value     $Report.DefragAnalysis.FreeSpacePercentFragmentation
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name LargestFreeSpaceExtent -Value $Report.DefragAnalysis.LargestFreeSpaceExtent
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name MFTPercentInUse -Value $Report.DefragAnalysis.MFTPercentInUse
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name MFTRecordCount -Value $Report.DefragAnalysis.MFTRecordCount
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name TotalExcessFragments -Value $Report.DefragAnalysis.TotalExcessFragments
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name TotalFiles -Value $Report.DefragAnalysis.TotalFiles
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name TotalFolders -Value $Report.DefragAnalysis.TotalFolders
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name TotalFragmentedFiles -Value $Report.DefragAnalysis.TotalFragmentedFiles
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name TotalFreeSpaceExtents -Value $Report.DefragAnalysis.TotalFreeSpaceExtents
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name TotalMFTFragments -Value $Report.DefragAnalysis.TotalMFTFragments
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name TotalMFTSize -Value $Report.DefragAnalysis.TotalMFTSize
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name TotalUnmovableFiles -Value $Report.DefragAnalysis.TotalUnmovableFiles
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name UsedSpace -Value $Report.DefragAnalysis.UsedSpace
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name VolumeSize -Value $Report.DefragAnalysis.VolumeSize

    $SqlDisks += $SqlDisk
  }
}

End {
  $Stopwatch.Stop()
  "$($SqlDisks.Count) disks found on the computer. Duration = $($Stopwatch.Elapsed.ToString()) [hh:mm:ss.ddd]." | Write-Verbose
  $SqlDisks
}

} # Get-Disk()

### INVOKE ###
$MyDisks = Get-Disk -ComputerName '.' -Verbose
$MyDisks | Out-GridView

I use the DCOM protocol due to some restrictions in some network segments.

The usage of the CmdLet Get-CimAssociatedInstance makes associating the classes mush easier compared to associating the classes in WMI.
The execution of the method DefragAnalysis is different used from CIM than WMI. The use of the CmdLet Invoke-CimMethod took some time to get, but I find it much cleaner to use.

When the script is executed, the output could look like this:

DriveLetter                   : C:
DiskFreeSpaceBytes            : 488987631616
DiskSizeBytes                 : 750153363456
FileSystem                    : NTFS
VolumeDirty                   : False
VolumeName                    : ********
VolumeSerialNumber            : ********
PartitionDeviceID             : Disk #0, Partition #0
BlockSizeBytes                : 512
BlockCount                    : 1465143296
PartitionDiskIndex            : 0
PartitionSizeBytes            : 750153367552
StartingOffset                : 1048576
PartitionType                 : Installable File System
DrivePartitionCount           : 1
DriveBytesPerSector           : 512
DriveDeviceID                 : \\.\PHYSICALDRIVE0
InterfaceType                 : IDE
DriveFirmwareRevision         : EXT0
DriveModel                    : Samsung SSD 840 EVO 750G
DriveSizeBytes                : 750153761280
DriveStatus                   : OK
DriveSerialNumber             : ********    
AverageFileSize               : 142
AverageFragmentsPerFile       : 1,15
AverageFreeSpacePerExtent     : 6483968
ClusterSize                   : 4096
VolumeFreeSpace               : 489014767616
FreeSpacePercentFragmentation : 31
LargestFreeSpaceExtent        : 334636171264
MFTPercentInUse               : 100
MFTRecordCount                : 473855
TotalExcessFragments          : 54011
TotalFiles                    : 339026
TotalFolders                  : 76795
TotalFragmentedFiles          : 12689
TotalFreeSpaceExtents         : 75369
TotalMFTFragments             : 2
TotalMFTSize                  : 485228544
TotalUnmovableFiles           : 147
UsedSpace                     : 261138595840
VolumeSize                    : 750153363456

The verbose output shows some durations on the execution:
VERBOSE: Get-Disk( '.' )
VERBOSE: Get Local Disks...
...
VERBOSE: Disk.DeviceID = 'C:'.
VERBOSE: Partition.DeviceID = 'Disk #0, Partition #0'.
VERBOSE: Drive.DeviceID = '\\.\PHYSICALDRIVE0'.
VERBOSE: Volume.Label = '********'.
VERBOSE: Running Defrag Analysis...
VERBOSE: Defrag Analysis completed. Duration = 00:00:56.9287395 [hh:mm:ss.ddd].
VERBOSE: 2 disks found on the computer. Duration = 00:00:59.5802508 [hh:mm:ss.ddd].

The script is not optimized for performance and do have a response time on some seconds. The response time increased especially when I added the defragmentation data.

Please notice that the classes Win32_Volume and Win32_DefragAnalysis are not available on Windows XP or earlier.

Reference

MSDN Library: Win32_DiskDrive, Win32_DiskPartition, Win32_LogicalDisk, Win32_Volume and „WMI Tasks: Disks and File Systems“.
Richard Saddaway: "Defrag Analysis Part 2".

History

2010-12-22 First release of the script.
2015-06-25 Second release of the script, now using CIM CmsLets.

SQL Server 2008 SP installation

I have installed SQL Server 2008 SP2 on some Windows Server 2008 servers, and have some notes about the process.
When I started the version of SQL Server was „10.0.2531.0“, and after the installation the version is „10.0.4000“. This is also what is reported through SMO.

This update has been done in a database mirroring setup, where the principal database instance was update and restarted first. No action was taken to keep the database mirrors running.
After each update and restart the database mirrors came up by themselves.

The installation checks for files in use and reports that the file „fdhost.exe“ is in use and not controlled by the update wizard. This file is used by the Full-Text Search feature. To get a clean check I stopped the service „SQL Full-text Filter Daemon Launcher“ and end the process „fdhost.exe“ on each database instance.

When the installation is completed, the log files and other files from the process are placed in the folder "%ProgramFiles%\Microsoft SQL Server\100\Setup Bootstrap\Log\{time stamp: YYYYMMDD_hhmmss}". The file "Summary_{database instance name and time stamp}.txt" is the most interesting as a start.
Also the Error Log contains a lot about the update.

On one occasion the update failed with a message about „One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files.  If this is an existing database, the file may be corrupted and should be restored from a backup.“.
The service for the database instance could not start, and the same error message was given each time.
In the file "Detail.txt" there is a exception "Microsoft.SqlServer.Chainer.Infrastructure.ChainerInvalidOperationException" and it looks like some write operations to the Registry failed.
A restart of the computer made the update to finish correct and the database to start. Now with the updated version number.

Even though services was stopped and processes was ended you are prompted to restart the computer. In the Summary log file there are several entries about a restart is required. I did restart the computer to comply.

Reference

SQL Server Team Blog: „SQL Server 2008 SP2 is available for download today!“.
KB2285068: „List of the bugs that are fixed in SQL Server 2008 Service Pack 2“
Microsoft TechNet Library: „Full-Text Search Architecture“.

SQL Server versions and builds

This is a very nice and usefull list of SQL Server versions and builds:
http://sqlserverbuilds.blogspot.com/

if operator (not) in PowerShell

I do miss the if operator (?:) that I have in C++, C#, JavaScript and likewise but not in PowerShell.

A quick workaround is the subexpression ($()) in a string surrounded by ".

$ref = 'blue'
"The color is $(if ($ref -eq 'blue'){''} else{'not '})blue."
The output is
The color is blue.

If the $ref value is changed
$ref = 'yellow'
The output is
The color is not blue.

Reference

wikipedia: ?:

Automatic SPN registration

First read the blog entry „What SPN do I use and how does it get there?“ by the CSS SQL Server Engineers.
Then read the Knowledge Base article „How to use Kerberos authentication in SQL Server“ (KB319723).

All configuration I have done on a Domain Controller. It might be possible to use another server.
You have to be domain Administrator to do this.
Before configuration you can check by the commandline statement
setspn -l sqlaccount01
Which should give a output like
Registered ServicePrincipalNames for CN=SQLACCOUNT01,OU=DBA,DC=SQLADMIN,DC=LAN:

Then configure the SPN registration by „Step 3: Configure the SQL Server service to create SPNs dynamically“ in KB319723.
Restart the SQL Server database service.

Check the configuration by the command line statement
setspn -l sqlaccount01
This should give a output like
Registered ServicePrincipalNames for CN=SQLACCOUNT01,OU=DBA,DC=SQLADMIN,DC=LAN:
MSSQLSvc/SANDY.SQLADMIN.LAN:1433
MSSQLSvc/SANDY.SQLADMIN.LAN
When the database service is restarted two entries are made in the SQL Error Log
2010-09-08 07:06:05.04 Server The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/SANDY.SQLADMIN.LAN ] for the SQL Server service.
2010-09-08 07:06:05.04 Server The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/SANDY.SQLADMIN.LAN:1433 ] for the SQL Server service.

This is a manual process that should be automated...

SQL PowerShell output

There are several CmdLets for output in PowerShell. I got some by the statements
man write-*
and
man out-*

Putting the CmdLets in a script file
$DebugPreference = "Continue"

":: (default)"
":: Out-Null" | Out-Null
":: Out-Default" | Out-Default
":: Out-Host" | Out-Host
#":: Out-File"
#":: Out-Printer"
":: Out-String" | Out-String

Write-Host ":: Write-Host"
#Write-Progress ":: Write-Progress"
Write-Debug ":: Write-Debug"
Write-Verbose ":: Write-Verbose"
Write-Warning ":: Write-Warning"
Write-Error ":: Write-Error"
Write-Output ":: Write-Output"
I have disabled the calls of the CmdLets Out-File, Out-Printer and Write-Progress as I find them irrelevant to this little experiment.

Executing the script file gives in PowerShell (powershell.exe)
:: (default)
:: Out-Default
:: Out-Host
:: Out-String

:: Write-Host
DEBUG: :: Write-Debug
WARNING: :: Write-Warning
H:\Script Library\Output.ps1 : :: Write-Error
At line:1 char:13
+ .\Output.ps1 <<<<
    + CategoryInfo          : NotSpecified: (:) [Write-Error], WriteErrorException
    + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Output.ps1

:: Write-Output

and in SQL PowerShell (sqlps.exe)
:: (default)
:: Out-Default
:: Out-Host
:: Out-String

DEBUG: :: Write-Debug
WARNING: :: Write-Warning
H:\Script Library\Output.ps1 : :: Write-Error
At line:1 char:13
+ .\Output.ps1 <<<<
    + CategoryInfo          : NotSpecified: (:) [Write-Error], WriteErrorException
    + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Output.ps1

:: Write-Output
In the script for SQL PowerShell I removed the call to Write-Host as is fails with
A job step received an error at line 18 in a PowerShell script. The corresponding line is 'Write-Host ":: Write-Host"'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Cannot invoke this function because the current host does not implement it.

When I execute the script in a PowerShell SQL Agent job step using the Invoke operator (& "H:\Script Library\Output.ps1"), the output file contains
:: (default)
:: Out-String

:: Write-Output

The job script encountered the following errors. These errors did not stop the script:
A job step received an error at line 1 in a PowerShell script. The corresponding line is '& "H:\Script Library\Output.ps1"'. Correct the script and reschedule the job. The error information returned by PowerShell is: ':: Write-Error
'

I was a little surprised to see that the Debug Preference and the CmdLets Out-Default and Out-Host were ignored.
A quick conclusion is that the implicit pipe and the CmdLet Write-Output is general usable.

SQL Server TCP port

A SQL Server database instance has dynamic TCP port by default, and when installing a instance it is "Best Practice" to set a static TCP port.
Getting the actual TCP port configuration is usual done quickly and manual by SQL Server Configuration Manager, but when I have several installations it is rather cumbersome to logon to each Windows Server to check.
The PowerShell snippet below gets the basic information using SQL Server Management Objects (SMO).

$ComputerName = 'sandbox.sqladmin.lan'
$SsdbName = 'sandy'

[void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SQLWMIManagement')

$smoComputer = New-Object 'Microsoft.SqlServer.Management.Smo.WMI.ManagedComputer' $ComputerName
if ($SsdbName -eq '.') { # Default database instance
  $smoTcp = $smoComputer.ServerInstances['MSSQLSERVER'].ServerProtocols['Tcp']
}
else {  # Named database instance
  $smoTcp = $smoComputer.ServerInstances[$SsdbName].ServerProtocols['Tcp']
}
if ($smoTcp.IPAddresses['IPAll'].IPAddressProperties['TcpDynamicPorts'].Value -eq '') {
  [bool]$smoTcpIsDynamic = $false
  [int]$smoTcpPortNumber = $smoTcp.IPAddresses['IPAll'].IPAddressProperties['TcpPort'].Value
}
else {
  [bool]$smoTcpIsDynamic = $true
  [int]$smoTcpPortNumber = $smoTcp.IPAddresses['IPAll'].IPAddressProperties['TcpDynamicPorts'].Value
}

"ssdb_tcp_port = $smoTcpPortNumber; ssdb_tcp_is_dynamic = $smoTcpIsDynamic"

The variable $ComputerName hold the name of the computer... and the variable $SsdbName holds the name of the database instance. If the instance is default I use the value '.' (dot).
If the script invoked on a named instance with static TCP port, the output is like
ssdb_tcp_port = 54321; ssdb_tcp_is_dynamic = False
and from a named instance with dynamic TCP port the output is like
ssdb_tcp_port = 1930; ssdb_tcp_is_dynamic = True

Reference
Allen White: „Accessing WMI Information Using SMO“

Correct Disk Alignment?

Correct alignment of the sectors on a disk is somewhat important. This is described in several places.
The PowerShell script below checks the alignment of all disks on a given computer.
$wql = "SELECT DiskIndex,Index,StartingOffset FROM Win32_DiskPartition"
Get-WmiObject -Query $wql -ComputerName '.' | Select-Object DiskIndex,Index,@{Name='Offset (KB)';Expression={$_.StartingOffset / 1024}} | Format-Table -AutoSize

The output could be something like
DiskIndex Index Offset (KB)
--------- ----- -----------
        0     0        31,5
        1     0        31,5
        2     0        31,5
        3     0        31,5
        4     0        31,5
        5     0        31,5
        6     0        31,5
If the Offset is fractional, like above, the disk is not aligned correct.

Reference
Microsoft KB929491
Jimmy May, Denny Lee (SQLCAT): „Disk Partition Alignment Best Practices for SQL Server“

CodePlex novice

I have entered CodePlex.
After some looking around, my first project is published. Please take a look.

The subject is maintaining the database „msdb“ by cleaning up history data.

Reference
„SQL Server History Cleanup“ (CodePlex)

Errorlog Path

I need the path of the Errorlog directory when I define a SQL Server Agent jobstep. Using the Errorlog directory for the history (log files) of all jobsteps makes maintenance more easy and robust.
My favorite way of getting the path of the Errorlog diretory is by reading the Errorlog, while it contains a entry in the beginning about the path of the directory like
Logging SQL Server messages in file 'E:\MSSQL\MSSQL10_50.SANDY\MSSQL\Log\ERRORLOG'.
Using a (undocumented) stored procedure, I get the path by the script
DECLARE @_errorlog TABLE (
    LogDate DATETIME
    ,ProcessInfo NVARCHAR(MAX)
    ,ErrorText NVARCHAR(MAX));
INSERT INTO @_errorlog ([LogDate], [ProcessInfo], [ErrorText])
EXECUTE [master].[dbo].[sp_readerrorlog]
    @p1 = 0  -- 0 = current errorlog
    ,@p2 = 1  -- 1 = SQL Server Errorlog
    ,@p3 = N'Logging SQL Server messages in file ';

DECLARE @errorlog_directory NVARCHAR(256);
SELECT @errorlog_directory = REPLACE(REPLACE([ErrorText],N'Logging SQL Server messages in file ''',''),N'\ERRORLOG''.','')
FROM @_errorlog;

DELETE @_errorlog;

SELECT @errorlog_directory AS N'errorlog_directory';
The result is like
E:\MSSQL\MSSQL10_50.SANDY\MSSQL\Log
The (undocumented) procedure "sp_readerrorlog" takes up to four parameters

1. @p1:Generation by integer value of the Errorlog to get. 0 (zero) is current errorlog, 1 is the last archived and so on.
2. @p2: Define by integer or NULL which log to read. 1 (one) or NULL to read SQL Server Errorlog, 2 to read SQL Server Agent Log.
3. @p3: Primary search string, max. 255 characters.
4. @p4: Secondary search string, max. 255 characters.

The last part („\ERRORLOG“) of the string is filtered out as it is the name of the file.
The path of the Errorlog directory is also available through the Windows Registry. Unfortunately it is indirect so I don't like it. But it could be by the script
DECLARE @reg_key_data NVARCHAR(255);
EXECUTE [master].[dbo].[xp_instance_regread]
  N'HKEY_LOCAL_MACHINE'
 ,N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\Parameters'
 ,N'SQLArg1'
 ,@reg_key_data OUTPUT;
DECLARE @errorlog_directory NVARCHAR(256);
SELECT @errorlog_directory = REPLACE(REPLACE(@reg_key_data,N'-e',''),N'\ERRORLOG','');

SELECT @errorlog_directory AS N'errorlog_directory';

Reference
Greg Robidoux: „Reading the SQL Server log files using T-SQL“ (MSSQLTips)

Backup Directory

With SQL Server 2005 we were able to define a default backup directory. This is a nice thing to maintain, so that a restore can be done quickly and correct.
The directory path is stored in the Windows Registry, and is available with a (undocumented) stored procedure.
DECLARE @reg_key_data NVARCHAR(255);
EXECUTE [master].[dbo].[xp_instance_regread]
  N'HKEY_LOCAL_MACHINE'
 ,N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer'
 ,N'BackupDirectory'
 ,@reg_key_data OUTPUT;
SELECT @reg_key_data AS N'backup_directory';

(Fig. 1) SQL Server Management Studio, Facets

The script works on SQL server 2005 and 2008.
You could set the backup directory path by the (undocumented) stored procedure master.dbo.xp_instance_regwrite, but I prefer to change the value at the installation or manually using SQL Server Management Studio.
The name of the backup directory is available for read and write in SQL Server Management Studio as a facet to the database instance. Right-click on the instance in Object Explorer and select Facets. In the facet "Server" it is the second item.

Reference

Dinesh Asanka: „Working with the Registry“ (SQLServerCentral).

Secure password

A SQL Server installation requires a lot of service accounts - and then a lot of secure passwords.
I have tried various solutions, and for now I am using PC Tools Secure Password Generator on the web. It works nice, and is secured by HTTPs communication.
I still have to store the passwords in a secure and yet available place. The old school solution using paper, envelopes and a solid safe still works in some degree in smaller organisations.
In a larger organisation, or if availability with a audit is required, I have for some time used thycotic Secret Server in a local installation. It requires a software installation - with a SQL Server database, but it is secure and easy to use.

Set Recovery Model Simple

On a test installation with quite a few databases we decided to alter the Recovery Model to Simple.
This statement generated the ALTER statements.
SELECT N'ALTER DATABASE [' + [name] + '] SET RECOVERY SIMPLE WITH NO_WAIT;' AS N'DDL'
FROM [master].[sys].[databases]
WHERE [recovery_model] <> 3 AND [name] <> N'model';
The output is copied to the (same) query window in SQL Server Management Studio and executed.
The total time of delivery is measured in seconds :-)

ATI Driver on Windows 7

I did not downloaded a driver for my ATI 4850 card when I installed Windows 7 (x64) as the installation found the card and installed a suitable driver. But inspired by a colleague I took a look at the installed driver version, which were 8.6 while the the ATI driver site gives version 10.6 on the latest driver suite.
I downloaded the full Catalyst Software Suite, but made a custom installation where I unselected "The Lord of the Rings free trial", "HDMI/DP Audio driver" and "Communication Opt-In".
After installation the driver version is 8.7. Not a major leap in driver version, but the date of the driver is now 2010-05-27 instead of 2009-08-17.
At the end of the day I have learned that I can not trust Microsoft to update my drivers, even the hardware have been identified correct by the installation.
By the way - I can not feel any difference...

NTFS Cluster Size

When formatting a partition for SQL Server data, it is recommended by storage vendors, MVPs and other that the allocation unit size should be the largest size possible. For NTFS this means 64 KB. Often the DBA is called in after the server is build, installed and configured, but I still want to confirm the allocation unit size. This can be done bu the command-line tool FSUTIL (%SystemRoot%\system32\fsutil.exe), and wrapping a generic command-line statement in PowerShell gives me the value itself to be viewed or used by the automation.
When using FSUTIL, the allocation unit size is typically called NTFS Cluster Size, and is named "Bytes Per Cluster". On newer Windows installations you need to execute FSUTIL with administrative privileges.

function Get-NtfsClusterSize {
param ( [char[]]$driveLetter = 'c' )

 $driveLetter | ForEach-Object {
  if ( Test-Path "$($_):" ) {
   $cs = New-Object PSObject
   $cs | Add-Member -MemberType NoteProperty Drive $_
   # Get 7th line of output from FSUTIL
   $cs | Add-Member -MemberType NoteProperty Size (fsutil fsinfo ntfsinfo "$($_):")[7].split()[-1]
   $cs
  }
 }
}

The function can be called with one drive letter as parameter value.
Get-NtfsClusterSize c

Or with several drive letters as parameter value.
Get-NtfsClusterSize c,h

The output values can be referred as object attribute values and on implicit drive reference.
$clusterSize = Get-NtfsClusterSize
$clusterSize.Drive
$clusterSize.Size

I find it a rather cumbersome way to get a simple value, but by using WMI it can be done much simpler.
$wql = "SELECT BlockSize,DriveLetter,Label FROM Win32_Volume WHERE FileSystem='NTFS'"
Get-WmiObject -Query $wql -ComputerName '.' | Select-Object DriveLetter,Label,BlockSize | Format-Table -AutoSize

Reference
SQLCAT: "Disk Partition Alignment Best Practices for SQL Server"
Brent Ozar: "Ten Things DBAs Need to Know About Storage"
Chad Miller: "Disk Alignment Partitioning: The Good, the Bad, the OK and the Not So Ugly"
VistaForums: "Getting the 'cluster size' of your hard disk?"
MSDN Library: "Win32_Volume Class"

Backlog
Get the NTFS Cluster Size of a Mount Point.

New Mail shortcut

This is a handy one-click shortcut for a new Outlook mail.
"C:\Programmer\Microsoft Office\Office14\OUTLOOK.EXE" /c ipm.note

I have altered the shortcut ikon to distinct it from a Outlook shortcut. The example to the right is from a Danish Windows XP.
The shortcut is placed in the Quick Start toolbar with a helpfull comment.

Cycle SQL Server Errorlog

The standard SQL Server Errorlog has six generations and is recycled implicit by a service restart.
This might be an issue when the database instance Login auditing is „Both failed and successful logins“ (Full), because the Errorlog will contain 100000+ entries.
I have several times seen more than a million entries.
A Errorlog of this size it takes some time to open in SQL Server Management Studio.
You could „cheat“ by opening it in a editor on a UNC path. This could require an editor that can handle a file larger than 1 GB.

With only six generations of the Errorlog, you will experience to loose the last usefull Errlog when the instance is restarted a handfull times. This will happen when a patch is failing, power blackout or another unpleasent event.

Configure SQL Server Error Logs.

To make the Errorlog available I have increased the number of generations.
This can be done using SQL Server Management Studio by expanding the database instance in the Object Explorer and expanding the „Management“ section. Right-click on „SQL Server Logs“ and click „Configure“.
Then enter the number of Errorlog generations (see figure „Configure SQL Server Error Logs“).

The number of Errorlog generations can not be altered by sp_configure or an ALTER statement, but in the Registry.

To make the Errorlog available I have also sheduled a recycle every day at 23:59:30. Recycling at this time gives the Errorlog a timestamp matching the day it contains log entries from.

The two tasks above can be done using this T-SQL script:
EXECUTE [master].[sys].[xp_instance_regwrite]
@rootkey = N'HKEY_LOCAL_MACHINE',
@key = N'Software\Microsoft\MSSQLServer\MSSQLServer',
@value_name = N'NumErrorLogs',
@type = N'REG_DWORD',
@value = N'42';
GO

DECLARE @sa_name sysname = ( -- Name of 'sa' account in hardended installation
SELECT [server_principals].[name]
FROM [master].[sys].[server_principals]
WHERE [server_principals].[principal_id] = 1
);
DECLARE @jobId BINARY(16);
EXECUTE [msdb].[dbo].[sp_add_job]
@job_name = N'MsSqlDbErrorlogCycle',
@enabled = 1,
@notify_level_eventlog = 2,
@notify_level_email = 2,
@notify_level_netsend = 2,
@notify_level_page = 2,
@delete_level = 0,
@description = N'Cycle SQL Server Database Engine Errorlog.
Created by Niels Grove-Rasmussen.',
@category_name = N'Database Maintenance',
@owner_login_name = @sa_name,
@job_id = @jobId OUTPUT;
EXECUTE [msdb].[dbo].[sp_add_jobserver]
@job_name = N'MsSqlDbErrorlogCycle';
EXECUTE [msdb].[dbo].[sp_add_jobstep]
@job_name = N'MsSqlDbErrorlogCycle',
@step_name = N'Execute MsSqlDbErrorlogCycle',
@step_id = 1,
@cmdexec_success_code = 0,
@on_success_action = 1,
@on_fail_action = 2,
@retry_attempts = 0,
@retry_interval = 0,
@os_run_priority = 0,
@subsystem = N'TSQL',
@command = N'EXECUTE [master].[dbo].[sp_cycle_errorlog];',
@database_name = N'master',
@flags = 4;
EXECUTE [msdb].[dbo].[sp_update_job]
@job_name = N'MsSqlDbErrorlogCycle',
@notify_level_eventlog = 2,
@notify_level_email = 2,
@notify_level_netsend = 2,
@notify_level_page = 2,
@delete_level = 0,
@description = N'Cycle SQL Server Database Engine Errorlog',
@category_name = N'Database Maintenance',
@owner_login_name = @sa_name;
DECLARE @schedule_id INT;
EXECUTE [msdb].[dbo].[sp_add_jobschedule]
@job_name = N'MsSqlDbErrorlogCycle',
@name = N'Schedule MsSqlDbErrorlogCycle',
@freq_type = 4,
@freq_interval = 1,
@freq_subday_type = 1,
@freq_subday_interval = 0,
@freq_relative_interval = 0,
@freq_recurrence_factor = 1,
@active_start_time = 235930,
@schedule_id = @schedule_id OUTPUT;


History

2010-06-08  First blog entry.
2013-11-19  Name of sa–principal in local variable.

Installing SQL Server 2008 R2

On my private workstation (Windows 7 Ultimate x64) I first tried to in-place install the new SQL Server 2008 R2 (SQL2008R2), but it look like the installation collided with old installations of Visual Studio 2008. The last weeks I have upgraded Visual Studio and Office to 2010, but I have not before had problems by having them installed before installing SQL Server.
OK - I uninstalled all previous installed SQL Server and Visual Studio parts I could find in the Control Panel. Still no success.
This was beginning to be annoying, so I deleted all SQL Server and Visual Studio fils and folders. Hrmf - still the same error while installing SQL2008R2.

This morning I found the solution in the blog post "Rule "Previous releases of Microsoft Visual Studio 2008" failed" by Mark Smith at LessThanDot.
The trick is to start the installation with the Windows Shell statement

setup.exe /ACTION=install /SkipRules=VSShellInstalledRule

The installation finished with success and I can continue on working with the latest version of SQL Server.

SQL script file output using SQLPS

To automate tasks I often need to execute one or more script files (*.sql). Usually I have used sqlcmd.exe, but I would like to convert to sqlps, as it seems to be the future of SQL Server automation according to Microsoft. That is at least my personal guess...

A test SQL script (SqlScript.sql) like this tests three ways of output. That is standard output by SELECT, event output by PRINT and error output by RAISERROR.
SET NOCOUNT ON;
SELECT @@VERSION AS [sql_version];
GO
PRINT N':: Hello world from PRINT';
GO
SELECT ':: Hello world from SELECT' AS [hello];
GO
RAISERROR( N':: Hello world from RAISERROR', 18, 0 ) WITH NOWAIT;
GO
SELECT ':: End of script.' AS [script_end];

The calling PowerShell script file is
& sqlcmd.exe -S "Titanium" -E -r1 -i "SqlScript.sql" > SqlScript.log 2>&1

After execution by SQL Server PowerShell (sqlps.exe) the log file is
sql_version                                                                                                                                                                                                                                                                                                
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)
    Mar 29 2009 10:11:52
    Copyright (c) 1988-2008 Microsoft Corporation
    Developer Edition (64-bit) on Windows NT 6.1 (Build 7600: )
                                                                                                             
SQLCMD.EXE : :: Hello world from PRINT
At H:\Script Library\sqlps\Invoke-SqlCmd.ps1:14 char:2
+ & <<<<  sqlcmd.exe -S "Titanium" -E -r1 -i "SqlScript_1.sql" > SqlScript_1.log 2>&1
    + CategoryInfo          : NotSpecified: (:: Hello world from PRINT:String) [], RemoteException
    + FullyQualifiedErrorId : NativeCommandError

hello                    
--------------------------
:: Hello world from SELECT
Msg 50000, Level 18, State 1, Server TITANIUM, Line 2
:: Hello world from RAISERROR

script_end      
-----------------
:: End of script.

As the PRINT statement is a event, the placement of the PRINT output can vary.

Okay, I kind of cheated by still using sqlcmd.exe.
But this way I get the wanted output in the log file and in the right order. Also I get the features of PowerShell that I find much more powerfull than sqlcmd.exe or Windows Shell (cmd.exe).

Generate sqlcmd statements

I have a change implemented by several (10+) T-SQL script files in sizes from about 200 KB to 739 MB. Executing a 739 MB T-SQL script in Microsoft SQL Server Management Studio (SSMS) will fail, and typing several sqlcmd statements manually will be tedious and faulty.
By placing alle the T-SQL script files in one folder and using a single PowerShell statement all the sqlcmd statements can be created nice and quick:
ls *.sql | %{"`n@ECHO $($_.BaseName)...`nsqlcmd.exe -E -S %DbInstanceName% -i `"$($_.Name)`" -o `"$($_.BaseName).log`" -r1 -b`n@ECHO :: ErrorLevel = %ERRORLEVEL%`n@ECHO."}
The generated sqlcmd statements I can then copy into a Windows Shell script file (.cmd) where the variable DbInstanceName is defined by
@SET DbInstanceName="sandbox.sqladmin.lan\SSDB01"
It is on purpose that the Windows Shell script continues on error - in this specific case.

The logfile defined by the parameter -o in each sqlcmd statement holds output as results (e.g. SELECT), events (e.g. PRINT) and errors (e.g. RAISERROR).
This is verified by using a T-SQL test script
SELECT @@VERSION AS [sql_version];
GO
PRINT N':: Hello from PRINT';
GO
RAISERROR( N':: Hello from RAISERROR', 18, 0 );
and calling this from a sqlcmd statement generated by the PowerShell statement above.

SQL Server Agent schedule owner

When a database mirroring session is created using Microsoft SQL Server Management Studio (SSMS) the job "Database Mirroring Monitor Job" is created automatic. But if you create the database mirroring session by script the job is not created. This is described in more details tin the MSDN Library article "Monitoring Database Mirroring".

A database instance had a mirroring session running against one host, and the mirroring host was recycled. Then the database mirroring sessions were recreated against another host. This time by script.
No Problem I thought.
The job "Database Mirroring Monitor Job" was scripted out in SSMS and then created on the mirroring database instance. It then turns out that the jobs was not created exact as on the principal database instance.

The owner of the SQL Agent schedule "Database Mirroring Monitor Schedule" was NULL on the mirroring installation where is was "sa" on the principal installation.
This does not show in SSMS windows or by calling the procedure msdb.dbo.sp_help_schedule. But the table msdb.dbo.sysschedules holds the SID of the schedule owner.
SELECT [msdb].[dbo].[sysschedules].[name],[msdb].[dbo].[sysschedules].[owner_sid]
FROM msdb.dbo.[sysschedules]
WHERE [msdb].[dbo].[sysschedules].[name]=N'Database Mirroring Monitor Schedule';

I could not find a way to alter the owner of a given schedule in SSMS. The procedure msdb.dbo.sp_update_schedule does the trick.

EXECUTE [msdb].[dbo].[sp_update_schedule]
@name = N'Database Mirroring Monitor Schedule',
@owner_login_name = N'sa';

You could also do it quick and dirty.

UPDATE [msdb].[dbo].[sysschedules]
SET [owner_sid]=0x01
WHERE [sysschedules].[name]=N'Database Mirroring Monitor Schedule';

Not nice!

Now the schedules are identical and I can disregard the finding.

SQL Server consolidation

This Technical White Paper was published by Microsoft March 2009:
SQL Server Consolidation at Microsoft
The document is rather interesting as it in a structured way describes what we as concerned and dedicated DBAs thinkabout when we have a spare moment...
I like the Servers Ratings based on CPU usage (p 7), as it is a simple and yet effective way of rating the existing SQL Server installations. The existing monitoring tools in the infrastructure such as Microsoft System Center Operations Manager (SCOM) or Idera SQL Diagnostics Manager (SQLdm) will have the historic data needed to rate the existing SQL Server installations.
The three consolidation approaches (p 7) are also usefull. I only miss an approach like "Network Consolidation", to handle a needed segregation of duties and environments and still manage the entire enterprise SQL Server platform.
In general the half hour spend reading the paper was worth it.

Copy SQL login

In my daily surroundings we have a lot of mirrored SQL2005 databases, and to be ready to fail over we do a daily compare of the instances in each mirroring setup.
Among other things we compare the logins, and is a SQL login missing on the mirror instance, it must be created. It could be nice to have a single statement to do this...
The obstacle has been that the password hash is type varbinary(max), but using my favorite internet search engine inspired a solution.

The basic statement is
DECLARE @login_name sysname;
SET @login_name = N'Siegfried';
DECLARE @pw_hash varbinary(max);
SELECT @pw_hash = [password_hash] FROM master.sys.sql_logins WHERE [name]=@login_name;
SELECT 'CREATE LOGIN ['+@login_name+'] WITH PASSWORD=0x'+ CAST('' as xml).value('xs:hexBinary(sql:variable("@pw_hash"))','varchar(max)') + ' HASHED;' AS [DCL];
The value of the variable @login_name must be entered manual, but this is the basic statement...

Discussion
The statement could be implemented in a single function (UDF), procedure (USP) or a complete procedure.
This depends on the given database infrastructure. Among other things the remoting possibilities must be take in consideration.

Reference
The inspiration is the blog entry at "SQL Server Engine Tips":
http://blogs.msdn.com/sqltips/archive/2008/07/02/converting-from-hex-string-to-varbinary-and-vice-versa.aspx
Also the documentation of [sys].[sql_logins] at MSDN Library:
http://msdn.microsoft.com/en-us/library/ms174355(SQL.90).aspx

PowerShell version

It seems like there are several ways to get the version of my PowerShell.

I mostly use the variable $host

TryMe> $Host

The result is not a sigle value, but such I get by

TryMe> $Host.Version

Again not a single value. The major version is what I mostly need

TryMe> $Host.Version.Major

Actually $Host return the same as Get-Host.

Another usefull variable is $PSVersionTable. It gives a collection of objects

TryMe> $PSVersionTable

The funny part is when you access the PSVersion element

TryMe> $PSVersionTable.PSVersion

You get the same result as from $Host.Version.