TSQL Tuesday – Monitoring

2 Replies

TSQLTuesday

There are many forms of monitoring. When I thought of monitoring, I used to always refer to things like Red-Gate, SQL Sentry, Idera or Confio Ignite (Solarwinds DPA).  But recently I have began to dive more into monitoring from the perspective of proactive checking things out.

I will share a PowerShell script that I use to monitor or capture state for Tables in an enterprise to allow me to watch trends of growth and activity in a database, mostly related to tables.  An overview of the script is as follows.

What’s in a Table?

When you think about what a table can tell you when you look at it, there are many things that jump right out.

  • Rowcount
  • Size
  • How many Indexes
  • Is it Clustered
  • Defined length of the table
  • and many more

This is just a few.  In my DBA database I have the following tables that will have the code here:

  • stats_Database
  • stats_File
  • stats_Table

Prerequisites:

You need Write-DataTable for loading the results into a table in SQL Server.
SQL Server SMO installed for use in the PowerShell  (Install SqlClrTypes, SharedManagementObject, PowerShellTools in that order)
Create a Database for the tables below in the TSQL Scripts.
Create a Table for the iterations used in the monitoring. (Script in the list below)

These are used for storing or monitoring Database Space, File Stats and Table Stats.  Take a look and see what you find. Remember, these are not polished and they are a work in progress, but for the sake of TSQL Tuesday 66 I decided to unleash them anyway and I will clean them up and edit the post later.

Uses for this monitoring

After you gather the information below and get it into your tables, I can compare with Iteration dates what did the table look like at the beginning of the month?  What did the table look like from then till now and many other questions that can be posed.  Like I said, it is a little raw, but this is something that I have used to capture some information.  There is many more items to store, but this is a good start and for sake of answering questions in the TSQL Tuesday post, I use PowerShell to get information to store for monitoring purposes.

There are 5 scripts below that I will link to in the bottom of the post that combine all these files into a Zip file for you.

 

USE [DBA]
GO

/****** Object:  Table [dbo].[Iterations]    Script Date: 5/12/2015 2:51:57 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Iterations](
	[IterationId] [BIGINT] IDENTITY(1,1) NOT NULL,
	[GatherDate] [DATETIME2](3) NOT NULL DEFAULT (GETDATE()),
 CONSTRAINT [PK_Iterations_ID] PRIMARY KEY CLUSTERED 
(
	[IterationId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

 

USE [DBA]
GO

/****** Object:  Table [dbo].[stats_Database]    Script Date: 5/12/2015 2:27:28 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[stats_Database](
	[database_name] [VARCHAR](256) NOT NULL,
	[server_name] [VARCHAR](256) NOT NULL,
	[database_size] [DECIMAL](12, 2) NULL,
	[data_space_usage] [DECIMAL](12, 2) NULL,
	[index_space_usage] [DECIMAL](12, 2) NULL,
	[last_backup_date] [DATETIME2](3) NULL,
	[last_diff_backup_date] [DATETIME2](3) NULL,
	[last_log_backup_date] [DATETIME2](3) NULL,
	[auto_shrink] [BIT] NULL,
	[auto_close] [BIT] NULL,
	[auto_create_stats_enabled] [BIT] NULL,
	[auto_update_stats_enabled] [BIT] NULL,
	[collation] [VARCHAR](128) NULL,
	[compatibility_level] [VARCHAR](64) NULL,
	[page_verify] [VARCHAR](32) NULL,
	[default_filegroup] [VARCHAR](64) NULL,
	[database_id] [INT] NOT NULL,
	[recovery_model] [VARCHAR](16) NULL,
	[space_available] [DECIMAL](12, 2) NULL,
	[is_online] [BIT] NULL,
	[IterationId] [BIGINT] NOT NULL,
	[create_date] [DATETIME2](3) NULL,
	[is_system_object] [BIT] NOT NULL DEFAULT ((0)),
 CONSTRAINT [PK_statsDatabase_IterationDatabaseId] PRIMARY KEY CLUSTERED 
(
	[IterationId] ASC,
	[server_name] ASC,
	[database_name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO


USE [DBA]
GO

/****** Object:  Table [dbo].[stats_File]    Script Date: 5/12/2015 2:31:25 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[stats_File](
	[server_name] [VARCHAR](256) NOT NULL,
	[database_name] [VARCHAR](256) NOT NULL,
	[bytes_read_from_disk] [BIGINT] NULL,
	[bytes_written_to_disk] [BIGINT] NULL,
	[physical_name] [VARCHAR](256) NULL,
	[growth_rate] [INT] NULL,
	[growth_type] [VARCHAR](64) NULL,
	[max_size] [BIGINT] NULL,
	[logical_name] [VARCHAR](256) NULL,
	[number_of_disk_reads] [BIGINT] NULL,
	[number_of_disk_writes] [BIGINT] NULL,
	[size_in_mb] [BIGINT] NULL,
	[used_space_in_mb] [BIGINT] NULL,
	[file_id] [INT] NOT NULL,
	[file_type] [VARCHAR](16) NULL,
	[IterationID] [BIGINT] NOT NULL,
	[available_space_in_mb] [BIGINT] NULL,
 CONSTRAINT [PK_statsFiles_IterationFileID] PRIMARY KEY CLUSTERED 
(
	[IterationID] ASC,
	[server_name] ASC,
	[database_name] ASC,
	[file_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

 

USE [DBA]
GO

/****** Object:  Table [dbo].[stats_Table]    Script Date: 5/12/2015 2:34:53 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[stats_Table](
	[server_name] [VARCHAR](256) NOT NULL,
	[database_name] [VARCHAR](256) NOT NULL,
	[table_name] [VARCHAR](256) NOT NULL,
	[schema_name] [VARCHAR](256) NOT NULL,
	[data_space] [BIGINT] NOT NULL,
	[index_space] [BIGINT] NOT NULL,
	[row_count] [INT] NOT NULL,
	[column_count] [INT] NOT NULL,
	[is_clustered] [BIT] NOT NULL,
	[is_pk_clustered] [BIT] NOT NULL,
	[primary_key_name] [VARCHAR](256) NULL,
	[primary_key_columns] [VARCHAR](256) NULL,
	[primary_key_length] [INT] NULL,
	[defined_length] [INT] NOT NULL,
	[index_count] [INT] NOT NULL,
	[IterationId] [INT] NOT NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

 

<#
.SYNOPSIS
   Gathers information about each table for use in monitoring growth and activity.
.DESCRIPTION

.PARAMETER <paramName>
   IncludeSystem
.EXAMPLE
   <An example of using the script>
#>
param(
[switch]$IncludeSystem,
[string]$StatsInstance = 'localhost',
[string]$StatsDatabase = 'DBA',
[string]$AssemblyVersion = "12.0.0.0"
)

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=$AssemblyVersion, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
Add-Type -AssemblyName "Microsoft.SqlServer.SmoExtended, Version=$AssemblyVersion, Culture=neutral, PublicKeyToken=89845dcd8080cc91"

# https://gallery.technet.microsoft.com/scriptcenter/2fdeaf8d-b164-411c-9483-99413d6053ae
# Save it somewhere to be able to load it.
. d:\bin\Write-DataTable.ps1

Get-Date

[void][System.Reflection.Assembly]::LoadWithPartialName("System.Text")

$dt = New-Object -TypeName System.Data.DataTable -ArgumentList "AllTablesDef"

$cola = New-Object System.Data.DataColumn server_name, ([string])
$col0 = New-Object system.Data.DataColumn database_name, ([string])
$col0.MaxLength = 256
$col1 = New-Object system.Data.DataColumn table_name,([string])
$col1.MaxLength = 256
$colb = New-Object System.Data.DataColumn schema_name, ([string])
$colb.MaxLength = 256
$col2 = New-Object system.Data.DataColumn data_space,([int64])
$col3 = New-Object system.Data.DataColumn index_space,([int64])
$col4 = New-Object system.Data.DataColumn row_count,([int])
$col5 = New-Object system.Data.DataColumn column_count,([int])
$col6 = New-Object system.Data.DataColumn is_clustered,([boolean])
$col7 = New-Object system.Data.DataColumn is_pk_clustered,([boolean])
$col8 = New-Object system.Data.DataColumn primary_key_name,([string])
$col8.MaxLength = 256
$col9 = New-Object system.Data.DataColumn primary_key_columns,([string])
$col9.MaxLength = 256
$col10 = New-Object system.Data.DataColumn primary_key_length,([int])
$col11 = New-Object system.Data.DataColumn defined_length, ([int])
$col12 = New-Object system.Data.DataColumn index_count,([int])
$col13 = New-Object System.Data.DataColumn IterationId, ([Int64])


$dt.Columns.Add($cola)
$dt.Columns.Add($col0)
$dt.Columns.Add($col1)
$dt.Columns.Add($colb)
$dt.Columns.Add($col2)
$dt.Columns.Add($col3)
$dt.Columns.Add($col4)
$dt.Columns.Add($col5)
$dt.Columns.Add($col6)
$dt.Columns.Add($col7)
$dt.Columns.Add($col8)
$dt.Columns.Add($col9)
$dt.Columns.Add($col10)
$dt.Columns.Add($col11)
$dt.Columns.Add($col12)
$dt.Columns.Add($col13)

$unicodeNarrow = "nvarchar,nchar"
$unicodeWide = "ntext"
$gatherDate = [System.DateTime]::Now
$pkColumns = New-Object System.Text.StringBuilder

$destServer = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -Args $StatsInstance
$destdb = $destServer.Databases[$StatsDatabase]

$idValue = $destdb.ExecuteWithResults("INSERT INTO dbo.Iterations (GatherDate) OUTPUT inserted.IterationId VALUES (GetDate())");
$id = [Int64]($idValue.Tables[0].Rows[0][0]);

$spaceTable = New-Object -TypeName System.Data.DataTable -ArgumentList "DBSpaceStats"

$col1 = New-Object system.Data.DataColumn server_name, ([string])
$col1.MaxLength = 256
$col2 = New-Object system.Data.DataColumn database_name,([string])
$col2.MaxLength = 256
$col3 = New-Object System.Data.DataColumn bytes_read_from_disk, ([Int64])
$col4 = New-Object System.Data.DataColumn bytes_written_to_disk, ([Int64])
$col5 = New-Object system.Data.DataColumn physical_name,([string])
$col5.MaxLength = 256
$col6 = New-Object system.Data.DataColumn growth_rate,([Int])
$col7 = New-Object system.Data.DataColumn growth_type,([string])
$col7.MaxLength = 64
$col8 = New-Object system.Data.DataColumn max_size,([Int64])
$col9 = New-Object system.Data.DataColumn logical_name,([string])
$col9.MaxLength = 256
$col10 = New-Object System.Data.DataColumn number_of_disk_reads, ([Int64])
$col11 = New-Object System.Data.DataColumn number_of_disk_writes, ([Int64])
$col12 = New-Object system.Data.DataColumn size_in_mb,([decimal])
$col13 = New-Object system.Data.DataColumn used_space_in_mb,([decimal])
$col14 = New-Object system.Data.DataColumn file_id,([int])
$col15 = New-Object system.Data.DataColumn file_type,([string])
$col15.MaxLength = 16
$col16 = New-Object System.Data.DataColumn IterationId,([Int64])
$col17 = New-Object system.Data.DataColumn available_space_in_mb, ([decimal])


$spaceTable.Columns.Add($col1)
$spaceTable.Columns.Add($col2)
$spaceTable.Columns.Add($col3)
$spaceTable.Columns.Add($col4)
$spaceTable.Columns.Add($col5)
$spaceTable.Columns.Add($col6)
$spaceTable.Columns.Add($col7)
$spaceTable.Columns.Add($col8)
$spaceTable.Columns.Add($col9)
$spaceTable.Columns.Add($col10)
$spaceTable.Columns.Add($col11)
$spaceTable.Columns.Add($col12)
$spaceTable.Columns.Add($col13)
$spaceTable.Columns.Add($col14)
$spaceTable.Columns.Add($col15)
$spaceTable.Columns.Add($col16)
$spaceTable.Columns.Add($col17)

$dbSpace = New-Object -TypeName System.Data.DataTable -ArgumentList "DBSpace"

$col1 = New-Object system.Data.DataColumn database_name, ([string])
$col1.MaxLength = 256
$col2 = New-Object system.Data.DataColumn server_name,([string])
$col2.MaxLength = 256
$col3 = New-Object System.Data.DataColumn database_size, ([decimal])
$col4 = New-Object System.Data.DataColumn data_space_usage, ([decimal])
$col5 = New-Object System.Data.DataColumn index_space_usage, ([decimal])
$col6 = New-Object system.Data.DataColumn last_backup_date,([datetime])
$col7 = New-Object system.Data.DataColumn last_diff_backup_date,([datetime])
$col8 = New-Object system.Data.DataColumn last_log_backup_date,([datetime])
$col9 = New-Object system.Data.DataColumn auto_shrink,([boolean])
$col10 = New-Object system.Data.DataColumn auto_close,([boolean])
$col11 = New-Object system.Data.DataColumn auto_create_stats_enabled,([boolean])
$col12 = New-Object system.Data.DataColumn auto_update_stats_enabled,([boolean])
$col13 = New-Object system.Data.DataColumn collation,([string])
$col13.MaxLength = 128
$col14 = New-Object system.Data.DataColumn compatibility_level,([string])
$col14.MaxLength = 64
$col15 = New-Object system.Data.DataColumn page_verify,([string])
$col15.MaxLength = 32
$col16 = New-Object system.Data.DataColumn default_filegroup,([string])
$col16.MaxLength = 64
$col17 = New-Object system.Data.DataColumn database_id,([Int])
$col18 = New-Object system.Data.DataColumn recovery_model,([string])
$col18.MaxLength = 16
$col19 = New-Object system.Data.DataColumn space_available,([decimal])
$col20 = New-Object system.Data.DataColumn is_online,([boolean])
$col21 = New-Object System.Data.DataColumn IterationId,([Int64])
$col22 = New-Object system.Data.DataColumn create_date, ([datetime])

$dbSpace.Columns.Add($col1)
$dbSpace.Columns.Add($col2)
$dbSpace.Columns.Add($col3)
$dbSpace.Columns.Add($col4)
$dbSpace.Columns.Add($col5)
$dbSpace.Columns.Add($col6)
$dbSpace.Columns.Add($col7)
$dbSpace.Columns.Add($col8)
$dbSpace.Columns.Add($col9)
$dbSpace.Columns.Add($col10)
$dbSpace.Columns.Add($col11)
$dbSpace.Columns.Add($col12)
$dbSpace.Columns.Add($col13)
$dbSpace.Columns.Add($col14)
$dbSpace.Columns.Add($col15)
$dbSpace.Columns.Add($col16)
$dbSpace.Columns.Add($col17)
$dbSpace.Columns.Add($col18)
$dbSpace.Columns.Add($col19)
$dbSpace.Columns.Add($col20)
$dbSpace.Columns.Add($col21)
$dbSpace.Columns.Add($col22)


$db = Get-SqlDatabase -sqlserver $destdb.Parent.Name -dbname "master"
$server = $db.Parent

$tableFields = $server.GetDefaultInitFields([Microsoft.SqlServer.Management.Smo.Table])
$columnFields = $server.GetDefaultInitFields([Microsoft.SqlServer.Management.Smo.Column])
$databaseFields = $server.GetDefaultInitFields([Microsoft.SqlServer.Management.Smo.Database])

[void]$tableFields.Add("DataSpaceUsed")
[void]$tableFields.Add("IndexSpaceUsed")
[void]$tableFields.Add("RowCount")
[void]$tableFields.Add("HasClusteredIndex")
[void]$tableFields.Add("IsSystemObject")

[void]$columnFields.Add("DataType")
[void]$columnFields.Add("InPrimaryKey")
[void]$columnFields.Add("Name")

[void]$databaseFields.Add("Name")
[void]$databaseFields.Add("Size")
[void]$databaseFields.Add("DataSpaceUsage")
[void]$databaseFields.Add("IndexSpaceUsage")
[void]$databaseFields.Add("LastBackupDate")
[void]$databaseFields.Add("LastDifferentialBackupDate")
[void]$databaseFields.Add("LastLogBackupDate")
[void]$databaseFields.Add("AutoShrink")
[void]$databaseFields.Add("AutoClose")
[void]$databaseFields.Add("AutoCreateStatisticsEnabled")
[void]$databaseFields.Add("AutoUpdateStatisticsEnabled")
[void]$databaseFields.Add("Collation")
[void]$databaseFields.Add("CompatibilityLevel")
[void]$databaseFields.Add("PageVerify")
[void]$databaseFields.Add("DefaultFileGroup")
[void]$databaseFields.Add("ID")
[void]$databaseFields.Add("RecoveryModel")
[void]$databaseFields.Add("SpaceAvailable")
[void]$databaseFields.Add("CreateDate")

foreach($svr in (gc d:\bin\servers.txt)) {

	$opsserver = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -Args $svr
	@{ "ServerName" = "$($opsserver.Name)"; "StartEnd" = "Begin"; "Date" = "$(Get-Date)" } | ft * -AutoSize
	
	$opsserver.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.Table], $tableFields)
	$opsserver.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.Column], $columnFields)
	$opsserver.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.Database], $databaseFields)
	
	$spaceTable.Rows.Clear()
	$dbSpace.Rows.Clear()	
	
	foreach($db in $opsserver.Databases) {
		$dt.Rows.Clear()

	    if($db.Status -match "Normal") {
			$dbrow = $dbSpace.NewRow()
			$dbrow.is_online = $true
			$dbrow.server_name = $server.Name
			$dbrow.database_name = $db.Name
			$dbrow.database_size = $db.Size
			$dbrow.data_space_usage = $db.DataSpaceUsage
			$dbrow.index_space_usage = $db.IndexSpaceUsage
			$dbrow.last_backup_date = $db.LastBackupDate
			$dbrow.last_diff_backup_date = $db.LastDifferentialBackupDate
			$dbrow.last_log_backup_date = $db.LastLogBackupDate
			$dbrow.auto_shrink = $db.AutoShrink
			$dbrow.auto_close = $db.AutoClose
			$dbrow.auto_create_stats_enabled = $db.AutoCreateStatisticsEnabled
			$dbrow.auto_update_stats_enabled = $db.AutoUpdateStatisticsEnabled
			$dbrow.collation = $db.Collation
			$dbrow.compatibility_level = $db.CompatibilityLevel
			$dbrow.page_verify = $db.PageVerify
			$dbrow.default_filegroup =  $db.DefaultFileGroup
			$dbrow.database_id = $db.ID
			$dbrow.recovery_model = $db.RecoveryModel
			$dbrow.space_available = $db.SpaceAvailable
			$dbrow.iterationId = $id
			$dbrow.create_date = $db.CreateDate
			
			$dbSpace.Rows.Add($dbrow)
			
	        $tables = $db.Tables

	        foreach($table in $tables) {
		        if(!$IncludeSystem -and $table.IsSystemObject) {
			        continue;
		        }
		
		        $row = $dt.NewRow();
	            $row.server_name = $db.Parent.Name
		        $row.database_name = $db.Name
		        $row.table_name = $table.Name
		        $row.data_space = $table.DataSpaceUsed
		        $row.index_space = $table.IndexSpaceUsed
		        $row.row_count = $table.RowCount
		        $row.column_count = $table.Columns.Count
		        $row.is_clustered = $table.HasClusteredIndex
	            $row.schema_name = $table.Schema
	            $row.is_pk_clustered = $false
	    
		        $definitionLength = 0;
		        $primaryKeyLength = 0;
		        [void]$pkColumns.Remove(0, $pkColumns.Length);

		        $columns = $table.Columns;

		        foreach($col in $columns) {
			        if($unicodeNarrow.Contains($col.DataType.ToString())) {
				        $definitionLength += $col.DataType.MaximumLength * 2;
				        if($col.InPrimaryKey) {
					        $primaryKeyLength += $col.DataType.MaximumLength * 2;
					        [void]$pkColumns.AppendFormat("{0},", $col.Name);
				        }
			        }
			        elseif($unicodeWide.Contains($col.DataType.ToString())) {
				        $definitionLength += $col.DataType.MaximumLength;
				        if($col.InPrimaryKey) {
					        $primaryKeyLength += $col.DataType.MaximumLength;
					        [void]$pkColumns.AppendFormat("{0},", $col.Name);
				        }
			        }
			        elseif($col.DataType.MaximumLength -eq -1) {
				        $definitionLength += 16;
				        if($col.InPrimaryKey) {
					        $primaryKeyLength += 16;
					        [void]$pkColumns.AppendFormat("{0},", $col.Name);
				        }
			        }
			        else {
				        $definitionLength += $col.DataType.MaximumLength;
				        if($col.InPrimaryKey) {
					        $primaryKeyLength += $col.DataType.MaximumLength;
					        [void]$pkColumns.AppendFormat("{0},", $col.Name);
				        } 
			        }
		        }
		        $row.defined_length = $definitionLength
		        $row.index_count = $table.Indexes.Count
		        $row.IterationId = $id
		        $row.primary_key_length = $primaryKeyLength
		        $row.primary_key_columns = $pkColumns.ToString()
		        try {
		            $dt.Rows.Add($row)
	            }
	            catch {
	                Write-Output "$($opsserver.Name),$($db.Name),$($table.Name)"
					Write-Output $_.Exception.Message
				}
		    }
			try {
				Write-DataTable $StatsInstance $StatsDatabase "stats_Table" $dt	
			}
			catch {
				Write-Error "Could not write to stats_Table"
				Write-Error $_.Exception.Message
			} 

		    foreach($fg in $db.FileGroups) {
			    foreach($dbfile in $fg.Files) {
				    $row = $spaceTable.NewRow()
				    $row.server_name = $opsserver.Name
				    $row.database_name = $db.Name
				    $row.file_type = 'Data'
				    $row.file_id = $dbfile.ID
				    $row.logical_name = $dbfile.Name
				    $row.physical_name = $dbfile.FileName
				    $row.size_in_mb = $($dbfile.Size / 1KB)
				    $row.growth_rate = $dbfile.Growth
				    $row.growth_type = $dbfile.GrowthType
				    $row.max_size = $dbfile.MaxSize
				    $row.used_space_in_mb = $($dbfile.UsedSpace / 1KB)
				    $row.available_space_in_mb = $($dbfile.AvailableSpace / 1KB)
				    $row.iterationid = $id
	                $row.number_of_disk_reads = $dbfile.NumberOfDiskReads
	                $row.number_of_disk_writes = $dbfile.NumberOfDiskWrites
					$row.bytes_read_from_disk = $dbfile.BytesReadFromDisk
					$row.bytes_written_to_disk = $dbfile.BytesWrittenToDisk
				
				    $spaceTable.Rows.Add($row)
			    }
		    }
		    foreach($logfile in $db.LogFiles) {
			    $row = $spaceTable.NewRow()
			    $row.server_name = $opsserver.Name
			    $row.database_name = $db.Name
			    $row.file_type = 'Log'
			    $row.file_id = $logfile.ID
			    $row.logical_name = $logfile.Name
			    $row.physical_name = $logfile.FileName
			    $row.size_in_mb = $($logfile.Size / 1KB)
			    $row.growth_rate = $logfile.Growth
			    $row.growth_type = $logfile.GrowthType
			    $row.max_size = $logfile.MaxSize
			    $row.used_space_in_mb = $($logfile.UsedSpace / 1KB)
			    $row.available_space_in_mb = $(($logfile.Size - $logfile.UsedSpace) / 1KB)
			    $row.iterationid = $id
			    $row.number_of_disk_reads = $logfile.NumberOfDiskReads
	            $row.number_of_disk_writes = $logfile.NumberOfDiskWrites
				$row.bytes_read_from_disk = $dbfile.BytesReadFromDisk
				$row.bytes_written_to_disk = $dbfile.BytesWrittenToDisk

				$spaceTable.Rows.Add($row)
			}
			
			
		}
		
	}
	
	try {
		Write-DataTable $StatsInstance $StatsDatabase "stats_File" $spaceTable
		Write-DataTable $StatsInstance $StatsDatabase "stats_Database" $dbSpace
	}
	catch {
		Write-Error "Could not write to stats_File"
		Write-Error $_.Exception.Message
	}
	@{ "ServerName" = "$($opsserver.Name)"; "StartEnd" = "End"; "Date" = "$(Get-Date)" } | ft * -AutoSize
}

Get-Date

 

Download file for this blog post.

2 thoughts on “TSQL Tuesday – Monitoring”

  1. Pingback: Roundup of T-SQL Tuesday #66: Monitoring - Cathrine Wilhelmsen

  2. Pingback: Roundup of T-SQL Tuesday #66: Monitoring - Cathrine Wilhelmsen

Leave a Reply

Your email address will not be published. Required fields are marked *