PowerSQL – Get SQL TraceFlag SQL Service and Config Information

Get SQL Server configuration, service info, start type along with enabled Trace Flags of local or remote servers.

This script outputs sql server configuration, service, start type, Service Account along with startup trace flags. The script will work on SQL Server 2005 and up.

function Get-SQLAndGlobalTraceFlagsDetails { 
<# 
.SYNOPSIS 
Returns SQL Server Configuration, Service and global TraceFlag details 
 
.DESCRIPTION 
Get-SQLAndGlobalTraceFlagsDetails is a function that returns server high level server configuration information for 
one or more Microsoft SQL Server 
 
.PARAMETER ComputerName 
The computer that is running Microsoft SQL Server that you’re targeting 
 
.EXAMPLE 
Get-SQLAndGlobalTraceFlagsDetails -ComputerName sql01 
 
.EXAMPLE 
Get-SQLAndGlobalTraceFlagsDetails -ComputerName HQDBSP18 
 
.EXAMPLE 
"hqdbst03","hqdbsp16" | % { Get-SQLAndGlobalTraceFlagsDetails -ServerName $_ } 
 
.EXAMPLE 
 
ForEach ($server in Get-ChildItem C:\server.txt) 
{ 
Get-SQLAndGlobalTraceFlagsDetails -ServerName $server 
 
} 
 
.INPUTS 
String 
 
.OUTPUTS 
PSCustomObject , console or CSV 
#> 
[CmdletBinding()] 
param ( 
[Parameter(Mandatory=$true, 
ValueFromPipelineByPropertyName=$true)] 
[Alias('ServerName','PSComputerName','ComputerName')] 
[string[]]$Ser 
) 
 
 
# Used to store the result 
 
$Results = @() 
 
# The below array used to concatenate the Trace flag details 
 
$TraceFlag=@() 
 
#create an smo object for the SQL Server 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")|out-null 
 
$SQLServer = new-object ('Microsoft.SQLServer.Management.Smo.Server') $Ser 
$SQL= $SQLServer | select Name, Edition, BuildNumber, Product, ProductLevel, Version, Processors, PhysicalMemory, DefaultFile, DefaultLog, MasterDBPath, MasterDBLogPath, BackupDirectory, ServiceAccount,ServiceStartMode,State 
 
$SQLServer.EnumActiveGlobalTraceFlags()|%{ 
[string]$b=$_.status 
[string]$c=$_.TraceFlag 
$TraceFlag+=$c.ToString() +'->'+ $b.ToString() 
 
} 
 
$Properties = @{ 
Name = $SQL.name 
Edition = $SQL.Edition 
BuildNumber = $SQL.BuildNumber 
Product=$SQL.Product 
ProductLevel=$SQL.ProductLevel 
Version=$SQL.Version 
Processors=$SQL.Processors 
DefaultLog=$SQL.DefaultLog 
MasterDBPath=$SQL.MasterDBPath 
MasterDBLogPath=$SQL.MasterDBLogPath 
BackupDirectory=$SQL.BackupDirectory 
ServiceAccount=$SQL.ServiceAccount 
StartupMode=$SQL.ServiceStartMode 
State=$SQL.State 
TraceAndStatus=($TraceFlag -join ',')} 
 
 
 
$Results += New-Object psobject -Property $properties  
$Results | Select-Object Name, Edition, BuildNumber,TraceAndStatus,Product,ProductLevel,Version,Processors,DefaultLog,MasterDBPath,MasterDBLogPath,BackupDirectory,ServiceAccount,StartupMode,State 
 
 
} 
 
"hqdbst03","hqdbsp16" | % { Get-SQLAndGlobalTraceFlagsDetails1 -ServerName $_ }
 SQLTraceFlag
Advertisements

About Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application. My expertise lies in T-SQL programming, Replication and PowerShell You can connect me via https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ https://www.tumblr.com/blog/prashantjayaram http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ http://www.toadworld.com/members/prashanthjayaram/ My Articles are published in following sites http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/
This entry was posted in PowerShell, SQL and tagged , , . Bookmark the permalink.

2 Responses to PowerSQL – Get SQL TraceFlag SQL Service and Config Information

  1. RK says:

    Hi Prashanth,

    I’m looking for powershell offline databases list for all the SQL server. Please help me on this

    • Hi RK,

      Try this below code

      ForEach ($instance in Get-Content “C:\SQL_Servers.txt”)
      { $s = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) $instance
      $dbs=$s.Databases
      foreach ($DB in $dbs)
      {if ($DB.status -eq ‘offline’ ) { $DB.name }}
      }

      Prashanth

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s