SQL Server instance Info with PowerShell

 The below query will retrieve SQL Server instance info from multiple servers. The below PowerShell command only works when we install "sqlserver" module. If any columns has lengthy values please provide that column in the last

Install-Module sqlserver

Import-Module sqlserver

cls
$ServerList=@("Server1","Server2")
$ServerList|
ForEach-Object{
$SingInstance=$_
$list=@{Name="WindowsServer";Expression={$SingInstance.($_.Name)}},(Get-ChildItem -Path "SQLSERVER:\SQL\$_") 
$list
}|Format-Table @{Name="WindowServerName";Expression={$_.ComputerNamePhysicalNetBIOS}},Status,State,ServerVersion,@{Name="InstanceID";Expression={$_.ServiceInstanceId} `
},ServiceName,@{Name="ServiceMode";Expression={$_.ServiceStartMode}},Product,ProductLevel,Edition,DisplayName,ServiceAccount -AutoSize -Wrap
#,ClusterName,AvailabilityGroups,,DatabaseEngineEdition

Comments

Post a Comment

Popular posts from this blog

Getting SQL Server services info from multiple servers through Powershell

Image
Here the STATE 4 means "RUNNING" 1 Stopped. And the server names should be Windows Server names in which SQL Server instances are installed. cls #SQL Server 2014 $cmt1 = "ComputerManagement12" #sqlserver2016 $cmt2 = "ComputerManagement13" #sqlserver2017 $cmt3 = "ComputerManagement14" #SQL Server 2012 $cmt4 = "ComputerManagement11" #SQL Server 2008/2008 R2 $cmt5 = "ComputerManagement10" #provide only windows server names here $server = @ ( 'SERVER1' , 'SERVER2' , 'SERVER3' ) $server | ForEach -Object{ $singcmpt = $_ cd C : \windows\System32 $CmptMgmt =gwmi -ns 'root\Microsoft\SqlServer' __NAMESPACE -ComputerName $singcmpt | ? { $_ .name -match $cmt1 } |Select Name $CmptMgmt | ForEach -Object{ $singCmptMgmt = $_ if ( $singCmptMgmt = $cmt1 ) { Write-Host "$singcmpt is SQL Server 2014" -Verbose Get-CimInstance -ComputerName $singcmpt -Namespace "root/Microsoft/SqlS
Read more

System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

I got this error in one of my client's production environment, this type of issues might come up when we are not properly updating statistics. System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out Workaround: Please run UPDATE STATISTICS command in all the tables on a particular database EXEC sp_updatestats So in my scenario UPDATE STATISTICS on all the tables resolved the issue.
Read more

The transaction log for database is full due to 'OLDEST_PAGE'

When I see the 'OLDEST_PAGE' in log_reuse_wait_desc column and not allowing to me to shrink the log file of database I followed the below steps 1) When you see 'OLDEST_PAGE' run CHECKPOINT on that particular database 2) Take the Transaction log backup of that particular database 3) Try to shrink the log file of database These steps worked for me when I encountered this issue in my environment.
Read more