knowledge base


Powershell MS SQL Server Query



Batchdatei für Powershell Script:
echo "----------------------------------------------- ------------------------" > C:\temp\SQLDB-INFO.txt @echo off @del /Q C:\temp\MDF.txt @del /Q C:\temp\LOG.txt @del /Q C:\temp\SQLDB-INFO.txt @del /Q C:\temp\allg.txt @del /Q C:\temp\RESULT.txt cls echo. echo ----------------------------------------------------------------------- echo. echo DBInfo (c) 09/2017 by IT-FRICKE.de echo. echo. echo Please wait, starting SQLPS... echo. echo. echo. echo. echo. echo. echo. echo. echo. echo. echo. echo. echo. echo ----------------------------------------------------------------------- echo. echo ----------------------------------------------------------------------- >> C:\temp\allg.txt echo. >> C:\temp\allg.txt echo DATEISYSTEM: >> C:\temp\allg.txt echo. >> C:\temp\allg.txt echo ----------------------------------------------------------------------- >> C:\temp\allg.txt echo. >> C:\temp\allg.txt echo DIR I:\Data\ >> C:\temp\allg.txt echo. >> C:\temp\allg.txt DIR I:\Data\*.* >> C:\temp\allg.txt echo. >> C:\temp\allg.txt echo ----------------------------------------------------------------------- >> C:\temp\allg.txt echo. >> C:\temp\allg.txt echo DIR J:\Log\ >> C:\temp\allg.txt echo. >> C:\temp\allg.txt DIR J:\Log\*.* >> C:\temp\allg.txt echo. >> C:\temp\allg.txt echo ----------------------------------------------------------------------- >> C:\temp\allg.txt echo. >> C:\temp\allg.txt @START "SQLPS" /MIN /WAIT "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\ SQLPS.exe" C:\temp\SQLGETINFO.ps1 type C:\temp\SQLDB-INFO.txt >> C:\temp\RESULT.txt type C:\temp\allg.txt >> C:\temp\RESULT.txt @del /Q C:\temp\MDF.txt > NUL @del /Q C:\temp\LOG.txt > NUL @del /Q C:\temp\SQLDB-INFO.txt > NUL @del /Q C:\temp\allg.txt > NUL @MOVE /Y C:\temp\*.txt C:\temp\LOG cls REM notepad.exe "C:\temp\LOG\RESULT.txt" exit
Powershell Script: C:\temp\SQLGETINFO.ps1
echo "----------------------------------------------------- ------------------" > C:\temp\SQLDB-INFO.txt echo " " >> C:\temp\SQLDB-INFO.txt echo "DBInfo (c) 09/2017 by IT-FRICKE.de" >> C:\temp\SQLDB-INFO.txt echo " " >> C:\temp\SQLDB-INFO.txt echo " " >> C:\temp\SQLDB-INFO.txt date >> C:\temp\SQLDB-INFO.txt cd SQL\dbINSTANZ\DEFAULT\Databases\ echo " " >> C:\temp\SQLDB-INFO.txt echo "-----------------------------------------------------------------------" >> C:\temp\SQLDB-INFO.txt echo " " >> C:\temp\SQLDB-INFO.txt echo "INFO ABOUT DATABASES: " >> C:\temp\SQLDB-INFO.txt Get-ChildItem | select Name, Size, SpaceAvailable, DataSpaceUsage, IndexSpaceUsage, RecoveryModel, LastBackupDate, LastLogBackupDate >> C:\temp\SQLDB-INFO.txt cd "Path-to-Database" echo " " >> C:\temp\SQLDB-INFO.txt echo "-----------------------------------------------------------------------" >> C:\temp\SQLDB-INFO.txt echo "INFO MDB DATA: " >> C:\temp\SQLDB-INFO.txt ls I:\Data\*.mdf | select name, @{Name="MB";Expression={$_.Length / 1Mb}} | Export-Csv c:\temp\MDF.txt echo "-----------------------------------------------------------------------" >> C:\temp\SQLDB-INFO.txt type c:\temp\MDF.txt >> C:\temp\SQLDB-INFO.txt echo " " >> C:\temp\SQLDB-INFO.txt echo "-----------------------------------------------------------------------" >> C:\temp\SQLDB-INFO.txt echo "INFO LDF LOG: " >> C:\temp\SQLDB-INFO.txt ls J:\Log\*.ldf | select name,Length, @{Name="MB";Expression={$_.Length / 1Mb}} | Export-Csv c:\temp\LOG.txt echo "-----------------------------------------------------------------------" >> C:\temp\SQLDB-INFO.txt type c:\temp\LOG.txt >> C:\temp\SQLDB-INFO.txt echo " " >> C:\temp\SQLDB-INFO.txt echo "-----------------------------------------------------------------------" >> C:\temp\SQLDB-INFO.txt echo " SQL-JOBS " >> C:\temp\SQLDB-INFO.txt echo "-----------------------------------------------------------------------" >> C:\temp\SQLDB-INFO.txt cd SQLSERVER:\SQL\dbINSTANZ\DEFAULT\JobServer\Jobs Get-ChildItem | select Name, LastRunDate, NextRunDate >> C:\temp\SQLDB-INFO.txt echo " " >> C:\temp\SQLDB-INFO.txt echo "-----------------------------------------------------------------------" >> C:\temp\SQLDB-INFO.txt echo " STATUS WINDOWS SQL DIENSTE: " >> C:\temp\SQLDB-INFO.txt echo "-----------------------------------------------------------------------" >> C:\temp\SQLDB-INFO.txt Get-service *SQL* | Where-Object {$_.status -eq "Running"} >> C:\temp\SQLDB-INFO.txt echo " " >> C:\temp\SQLDB-INFO.txt exit
Send Mail via Powershell
Param([String]$xVariable) $smtp = "mailsrv.domain.de" $to = "mailadress@domain.de" $from = "mailadress@domain.de" $subject = "PSSENDMail (c) 09/2017 by IT-FRICKE.de" $body = "Status: TESTMAIL" + "
" + "
" $body += "TESTMail (c) 09/2017 by IT-FRICKE.de" + "
" $attachment="C:\temp\LOG\RESULT.txt" send-MailMessage -SmtpServer $smtp -To $to -From $from -Subject $subject -Body $body -BodyAsHtml -Attachment $attachment -Priority high
Index fragmentation info
  • Show fragmentation info on all indexes in a database
  • SET NOCOUNT ON USE pubs DBCC SHOWCONTIG WITH ALL_INDEXES GO SET NOCOUNT OFF ...turn NOCOUNT back OFF when done
  • Show fragmentation info on all indexes on a table
  • SET NOCOUNT ON USE pubs DBCC SHOWCONTIG (authors) WITH ALL_INDEXES GO SET NOCOUNT OFF
  • Show fragmentation information on a specific index
  • SET NOCOUNT ON USE pubs DBCC SHOWCONTIG (authors,aunmind) GO SET NOCOUNT OFF

    Server Availability Replicas Query
    # Get detailed info about SQL Server Availability Replicas:
    $MSSQLServerManager = New-Object 'Microsoft.SqlServer.Management.SMO.Server'
    # Get SQL Availability Replicas
    $HostSQLAvailabilityReplica = @($MSSQLServerManager.AvailabilityReplicas)[0]
    # Get SQL Availability Group Replicas Properties
    $HostSQLName = $HostSQLAvailabilityReplica.Name; $HostSQLAvailabilityReplicaGuid = $HostSQLAvailabilityReplica.UniqueId; $HostSQLRole = $HostSQLAvailabilityReplica.Role; $HostSQLAvailabilityMode = $HostSQLAvailabilityReplica.AvailabilityMode; $HostSQLConnectionModeInPrimaryRole = $HostSQLAvailabilityReplica.ConnectionModeInPrimaryRole; $HostSQLConnectionModeInSecondaryRole = $HostSQLAvailabilityReplica.ConnectionModeInSecondaryRole; $HostSQLEndpointUrl = $HostSQLAvailabilityReplica.EndpointUrl; $HostSQLFailoverMode = $HostSQLAvailabilityReplica.FailoverMode; $HostSQLJoinState = $HostSQLAvailabilityReplica.JoinState; $HostSQLQuorumVoteCount = $HostSQLAvailabilityReplica.QuorumVoteCount; $HostSQLSessionTimeout = $HostSQLAvailabilityReplica.SessionTimeout; $HostSQLBackupPriority = $HostSQLAvailabilityReplica.BackupPriority; $HostSQLCreateDate = $HostSQLAvailabilityReplica.CreateDate; $HostSQLDateLastModified = $HostSQLAvailabilityReplica.DateLastModified;
    Check other properties by listing all properties of $HostSQLAvailabilityReplica. Get detailed info about SQL Server Database Replica States via following code:
    $MSSQLServerManager = New-Object 'Microsoft.SqlServer.Management.SMO.Server'
    # Get SQL Database Replica States
    $HostSQLDatabaseReplicaState = @($MSSQLServerManager.DatabaseReplicaStates)[0]
    # Get SQL Database Replica Properties
    $HostSQLReplicaGuid = $HostSQLDatabaseReplicaState .AvailabilityReplicaId; $HostSQLDatabaseID = $HostSQLDatabaseReplicaState .DatabaseID; $HostSQLAvailabilityReplicaServerName = $HostSQLDatabaseReplicaState .AvailabilityReplicaServerName; $HostSQLAvailabilityDatabaseName = $HostSQLDatabaseReplicaState .AvailabilityDatabaseName; $HostSQLReplicaAvailabilityMode = $HostSQLDatabaseReplicaState .ReplicaAvailabilityMode; $HostSQLReplicaRole = $HostSQLDatabaseReplicaState .ReplicaRole;
    You can also check other properties by listing all properties of $HostSQLDatabaseReplicaState. Get detailed info about SQL Server Availability Group Listeners via following code:
    $MSSQLServerManager = New-Object 'Microsoft.SqlServer.Management.SMO.Server'
    # Get SQL Availability Group Listener
    $HostSQLAvailabilityGroupListener = @($MSSQLServerManager.AvailabilityGroupListeners)[0]
    # Get SQL Availability Group Listener Properties
    $HostSQLName = $HostSQLAvailabilityGroupListener.Name; $HostSQLAvailabilityGroupListenerGuid = $HostSQLAvailabilityGroupListener.UniqueId; $HostSQLIPAddresses = $HostSQLAvailabilityGroupListener.AvailabilityGroupListenerIPAddresses; $HostSQLPortNumber = $HostSQLAvailabilityGroupListener.PortNumber;
    You can also check other properties by listing all properties of $HostSQLAvailabilityGroupListener. Get detailed info about SQL Server Availability Groups via following code:
    $MSSQLServerManager = New-Object 'Microsoft.SqlServer.Management.SMO.Server'
    # Get SQL Availability Groups
    $HostSQLAvailabilityGroup = @($MSSQLServerManager.AvailabilityGroups)[0]
    # Get SQL Availability Groups Properties
    $HostSQLAvailabilityGroupName = $HostSQLAvailabilityGroup.Name; $HostSQLAvailabilityGroupID = $HostSQLAvailabilityGroup.Id; $HostSQLAvailabilityGroupGuid = $HostSQLAvailabilityGroup.UniqueId; $HostSQLLocalReplicaRole = $HostSQLAvailabilityGroup.LocalReplicaRole; $HostSQLAutomatedBackupPreference = $HostSQLAvailabilityGroup.AutomatedBackupPreference; $HostSQLFailureConditionLevel = $HostSQLAvailabilityGroup.FailureConditionLevel; $HostSQLPrimaryReplicaServerName = $HostSQLAvailabilityGroup.PrimaryReplicaServerName; $HostSQLHealthCheckTimeout = $HostSQLAvailabilityGroup.HealthCheckTimeout;
    You can also check other properties by listing all properties of $HostSQLAvailabilityGroup. Get detailed info about SQL Server Resource Pools via following code:
    $MSSQLServerManager = New-Object 'Microsoft.SqlServer.Management.SMO.Server'
    # Get SQL Resource Pools
    $MSSQLResourcePool = @($MSSQLServerManager.ResourceGovernor.ResourcePools)[0]
    # Get SQL Resource Pools Properties
    $HostSQLResourcePoolName = $MSSQLResourcePool.Name; $HostSQLMaximumCpuPercentage = $MSSQLResourcePool.MaximumCpuPercentage; $HostSQLMaximumIopsPerVolume = $MSSQLResourcePool.MaximumIopsPerVolume; $HostSQLMaximumMemoryPercentage = $MSSQLResourcePool.MaximumMemoryPercentage; $HostSQLMinimumCpuPercentage = $MSSQLResourcePool.MinimumCpuPercentage; $HostSQLMinimumIopsPerVolume = $MSSQLResourcePool.MinimumIopsPerVolume; $HostSQLMinimumMemoryPercentage = $MSSQLResourcePool.MinimumMemoryPercentage;
    You can also check other properties by listing all properties of $MSSQLResourcePool. How to use jscript on PoSHServer? (PowerShell Web Server)
    There is no jscript limitation on PoSHServer. However you need to use escape characters to make it work. If you need to use something like this: $(function() { $.ajax({ Then you should change your codes like this: `$(function() {`$.ajax({ ` is a escape character in PowerShell.
    You can get detailed info about SQL Server Role properties via following code:
    $MSSQLServerManager = New-Object 'Microsoft.SqlServer.Management.SMO.Server'
    # Get SQL Roles
    $MSSQLRoles = @($MSSQLServerManager.Roles)[0] # Get SQL Logins Properties
    $HostSQLRoleName = $MSSQLRoles.Name; $HostSQLOwner = $MSSQLRoles.Owner; $HostSQLIsFixedRole = $MSSQLRoles.IsFixedRole; $HostSQLCreateDate = $MSSQLRoles.DateCreated; $HostSQLDateLastModified = $MSSQLRoles.DateModified;
    You can also check other properties by listing all properties of $MSSQLRoles. Getting MSSQL Server Login Information via PowerShell Get detailed SQL Server properties via following code:
    $MSSQLServerManager = New-Object 'Microsoft.SqlServer.Management.SMO.Server'
    # Get SQL Logins
    $MSSQLLogin = @($MSSQLServerManager.Logins)[0]
    # Get SQL Logins Properties
    $HostSQLLoginName = $MSSQLLogin.Name; $HostSQLLoginType = $MSSQLLogin.LoginType; $HostSQLLanguage = $MSSQLLogin.Language; $HostSQLHasAccess = $MSSQLLogin.HasAccess; $HostSQLDenyWindowsLogin = $MSSQLLogin.DenyWindowsLogin; $HostSQLIsDisabled = $MSSQLLogin.IsDisabled; $HostSQLIsLocked = $MSSQLLogin.IsLocked;
    You can also check other properties by listing all properties of $MSSQLLogin. Get detailed SQL Server properties via following code:
    $MSSQLServerManager = New-Object 'Microsoft.SqlServer.Management.SMO.Server' $HostSQLAuditLevel = $MSSQLServerManager.AuditLevel; $HostSQLBuildClrVersionString = $MSSQLServerManager.BuildClrVersionString; $HostSQLIsCaseSensitive = $MSSQLServerManager.IsCaseSensitive; $HostSQLIsClustered = $MSSQLServerManager.IsClustered; $HostSQLIsFullTextInstalled = $MSSQLServerManager.IsFullTextInstalled; $HostSQLIsHadrEnabled = $MSSQLServerManager.IsHadrEnabled; $HostSQLIsSingleUser = $MSSQLServerManager.IsSingleUser; $HostSQLIsXTPSupported = $MSSQLServerManager.IsXTPSupported; $HostSQLTcpEnabled = $MSSQLServerManager.TcpEnabled; $HostSQLDefaultTextMode = $MSSQLServerManager.DefaultTextMode; $HostSQLMaxPrecision = $MSSQLServerManager.MaxPrecision; $HostSQLNamedPipesEnabled = $MSSQLServerManager.NamedPipesEnabled;
    You can also check other properties by listing all properties of $MSSQLServerManager. Get SQL Server Endpoint information via following code:
    $MSSQLServerManager = New-Object 'Microsoft.SqlServer.Management.SMO.Server' # Get SQL Endpoints
    $MSSQLEndpoint = @($MSSQLServerManager.Endpoints)[0]
    # Get SQL Endpoint Properties
    $HostSQLEndpointName = $MSSQLEndpoint.Name; $HostSQLEndpointType = $MSSQLEndpoint.EndpointType; $HostSQLProtocolType = $MSSQLEndpoint.ProtocolType; $HostSQLOwner = $MSSQLEndpoint.Owner; $HostSQLEndpointState = $MSSQLEndpoint.EndpointState; $HostSQLIsAdminEndpoint = $MSSQLEndpoint.IsAdminEndpoint; $HostSQLIsSystemObject = $MSSQLEndpoint.IsSystemObject;
    You can also check other properties by listing all properties of $MSSQLEndpoint. Get detailed Service Account information via following code:
    $MSSQLServerManager = New-Object 'Microsoft.SqlServer.Management.SMO.Server' $HostSQLServiceAccount = $MSSQLServerManager.ServiceAccount; $HostSQLServiceInstanceId = $MSSQLServerManager.ServiceInstanceId; $HostSQLServiceName = $MSSQLServerManager.ServiceName; $HostSQLServiceStartMode = $MSSQLServerManager.ServiceStartMode;
    You can use [Microsoft.Win32.RegistryKey] to get remote registry values. $ComputerName = "Server"; # Create Registry Connection
    $RegistryConn = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey([Microsoft.Win32.RegistryHive]"LocalMachine", $ComputerName)
    # Get Sub Key Names $GetSubKeyNames = $RegistryConn.OpenSubKey("SOFTWARE\Microsoft\Windows\CurrentVersion\X\").GetSubKeyNames()
    Getting File Content Mime Type with PowerShell # Create Content Type Map
    $ContentTypeMap = @{ ".jpg" = "image/jpeg"; ".jpeg" = "image/jpeg"; ".gif" = "image/gif"; ".png" = "image/png"; ".tiff" = "image/tiff"; ".zip" = "application/zip"; ".json" = "application/json"; ".xml" = "application/xml"; ".rar" = "application/x-rar-compressed"; ".gzip" = "application/x-gzip"; } $File = Get-ChildItem C:\YourFilePath.json $FileMimeType = $ContentTypeMap[$File.Extension.ToLower()]; Write-Output $FileMimeType You can get mime types from IIS. MSSQL Database Performance Tricks Check following query to improve your database performance: EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE GO EXEC sys.sp_configure N'max server memory (MB)', N'11000' GO RECONFIGURE WITH OVERRIDE GO EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE GO EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE GO EXEC sys.sp_configure N'max degree of parallelism', N'1' GO EXEC sys.sp_configure N'max server memory (MB)', N'11000' GO EXEC sys.sp_configure N'optimize for ad hoc workloads', N'1' GO RECONFIGURE WITH OVERRIDE GO EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE MSSQL Database Index Fragmentation Reports You can get Database Index Fragmentation Reports with following query: SELECT object_name(ps.object_id) AS [name], ps.index_id, i.name AS IndexName, ps.avg_fragmentation_in_percent, ps.fragment_count, ps.avg_fragment_size_in_pages, ps.page_count FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL) ps LEFT JOIN sys.indexes i ON i.object_id=ps.object_id AND i.index_id=ps.index_id WHERE ps.avg_fragmentation_in_percent > 10 AND ps.page_count > 100 ORDER BY ps.avg_fragmentation_in_percent DESC