windows:powershell:insertdbfilestats
Differences
This shows you the differences between two versions of the page.
|
windows:powershell:insertdbfilestats [2013/10/14 22:13] 127.0.0.1 external edit |
windows:powershell:insertdbfilestats [2018/05/14 22:00] |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | Here is a PowerShell script to gather SQL Server database file information such as names, size, size used, max size, growth property (percentage or not). Please create a SQL Server Agent job for this. The domain account that this job runs under needs to have access to the instances. | ||
| - | Database file usage changes fairly frequently, therefore scheduling this to run daily would be my suggestion. You can always manually kick off this job if needed. | ||
| - | |||
| - | ** Remember to change "sql1" to your host instance name ** | ||
| - | |||
| - | <code powershell insertDbFileStats.ps1> | ||
| - | function getInstanceUserDb($ServerInstance) | ||
| - | { | ||
| - | $UserDbList = Invoke-Sqlcmd -ServerInstance $ServerInstance -Query "select name | ||
| - | from master.sys.databases where name not in ('master', 'model', 'msdb', | ||
| - | 'tempdb') and state_desc = 'online'" | ||
| - | $UserDbList | ||
| - | } | ||
| - | function getDbFileInfo($ServerInstance, $DbName) | ||
| - | { | ||
| - | $DbFileQuery = @" | ||
| - | select | ||
| - | a.name as FileLogicalName | ||
| - | , a.filename as FilePhysicalName | ||
| - | , c.name as FileGroupName | ||
| - | , a.size/128 as FileSizeInMB | ||
| - | , (a.size-fileproperty(a.name,'SpaceUsed'))/128 as FreeSizeInMB | ||
| - | , b.max_size | ||
| - | , b.growth | ||
| - | , case b.is_percent_growth | ||
| - | when 1 then 'Y' | ||
| - | else 'N' end as is_percent_growth | ||
| - | from [$DbName].sys.sysfiles a inner join [$DbName].sys.database_files b on a.fileid = b.file_id | ||
| - | left outer join [$DbName].sys.filegroups c on a.groupid = c.data_space_id | ||
| - | "@ | ||
| - | try { | ||
| - | $DbFileList = Invoke-Sqlcmd -ServerInstance $ServerInstance -Query $DbFileQuery -Database $DbName | ||
| - | } | ||
| - | catch {} | ||
| - | $dataIndexArray = New-Object System.Collections.ArrayList | ||
| - | $DbFileList | foreach { | ||
| - | $myHashtable = @{FileLogicalName = $_.FileLogicalName; FilePhysicalName = $_.FilePhysicalName; FileGroupName = $_.FileGroupName; FileSizeInMB = $_.FileSizeInMB; FreeSizeInMB = $_.FreeSizeInMB; max_size = $_.max_size; growth = $_.growth; is_percent_growth = $_.is_percent_growth} | ||
| - | [void] $dataIndexArray.add($myHashtable) | ||
| - | } | ||
| - | $dataIndexArray | ||
| - | } | ||
| - | function storeDbFileInfo($sql) | ||
| - | { | ||
| - | $serverResults = Invoke-Sqlcmd -ServerInstance "sql1" -Database "SysMetrics" -Query $sql | ||
| - | $serverResults | forEach { | ||
| - | $InstanceName, $InstanceID = $_.InstanceName, $_.InstanceID | ||
| - | Try { | ||
| - | $dbResults = getInstanceUserDb -ServerInstance $InstanceName | ||
| - | } | ||
| - | Catch { Return } | ||
| - | $dbResults | ForEach { | ||
| - | $dbName = $_.name | ||
| - | Try { | ||
| - | $a = getDbFileInfo -ServerInstance $InstanceName -DbName $dbName | ||
| - | } | ||
| - | Catch { Return } | ||
| - | $a | ForEach { | ||
| - | if ($_.FileLogicalName) | ||
| - | { | ||
| - | $FileLogicalName, $FilePhysicalName, $FileGroupName, $FileSizeInMB, $FreeSizeInMB, $max_size, $growth, $is_percent_growth = $_.FileLogicalName, $_.FilePhysicalName, $_.FileGroupName, $_.FileSizeInMB, $_.FreeSizeInMB, $_.max_size, $_.growth, $_.is_percent_growth | ||
| - | $sql = "EXEC Windows.DbFileStats_Insert $InstanceID, '$DbName', '$FileLogicalName', '$FilePhysicalName', '$FileGroupName', $FileSizeInMB, $FreeSizeInMB, $max_size, $growth, '$is_percent_growth'" | ||
| - | Invoke-Sqlcmd -Query $sql -ServerInstance "sql1" -Database "SysMetrics" | ||
| - | } } } } } | ||
| - | |||
| - | |||
| - | $sql = @" | ||
| - | Windows.Instance_Select_InstanceID_InstanceName | ||
| - | "@ | ||
| - | storeDbFileInfo -sql $sql | ||
| - | </code> | ||
windows/powershell/insertdbfilestats.txt ยท Last modified: 2018/05/14 22:00 (external edit)
Page Tools
Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Share Alike 4.0 International
