Compatibility Level

https://msdn.microsoft.com/en-us/library/bb510680.aspx?f=255&MSPPError=-2147217396 Compatibility-level setting of 120 SQL Server 2014 SQL Server 2014 includes substantial improvements to the component that creates and optimized query plans. This new query optimizer feature is dependent upon use of the database compatibility level 120. New database applications should be developed using database compatibility level 120 to take advantage of these improvements. […]

Powershell and SQL Server – 3- Script SQL Server Logins with SMO

The Script is to quickly script out Login from Single or Multiple instance cls $SQLserverDB=@() $SQLserverDB=Get-Content “C:\PowershellScript\ServerList.txt” foreach($SqlServer in $SQLserverDB) { $server = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServer) Write-Host “—Login Script of :”$server”—” -ForegroundColor Yellow foreach($login in $Logins) { $login.Name $LoginName=$login.Name $server.Logins[$LoginName].Script() } }

Monitoring Database Mirroring Latency

DECLARE @MirroredDBToTest sysname SET @MirroredDBToTest = DB_NAME() DECLARE @MirroringResults TABLE (database_name sysname, role int, mirroring_state int, witness_status int, log_generation_rate int, unsent_log int, send_rate int, unrestored_log int, recovery_rate int, transaction_delay int, transactions_per_sec int, average_delay int, time_recorded datetime, time_behind datetime, local_time datetime) INSERT INTO @MirroringResults EXEC msdb..sp_dbmmonitorresults @database_name = @MirroredDBToTest SELECT DATEDIFF(S,time_behind, time_recorded) AS Latency FROM @MirroringResults

Script out Server – Database Level Permission for all users and from all databases

This is always an issue that all the permission are gone when ever databases are refreshed from a backup.The below script will script Server ,Database,Object and Database Role Level Permission for all Databases and all users. Create a  ServerList.txt Download the Script_Server_Database_Level_Permission.sql and Powershell Script and save it to local drive location. Replace the “Path” with correct […]

Powershell and SQL Server – 5- List Login Information from Multiple SQL Server Instance with Invoke-Sqlcmd

The script pull Login information from single or multiple SQL Instance cls if (-not (Get-Module SQLPS)) {Import-Module SQLPS} #Get list of server from text files $SQLserverDB=@() $SQLserverDB=Get-Content “C:\PowershellScript\ServerList.txt” foreach($SqlServer in $SQLserverDB) { Write-Host “|- SQL Server : ” $SqlServer -ForegroundColor Yellow try { $result=Invoke-Sqlcmd -ServerInstance $SqlServer -Database master -Query “SELECT * FROM sys.syslogins” -QueryTimeout 0 […]

Powershell and SQL Server – 2- List SQL Server Service Name and Service Account with SMO

The Script pulls out SQL Server Service Name (Instance Name) and Service Account (Log on Account) $SQLserverDB=@() $SQLserverDB=Get-Content “C:\PowershellScript\ServerList.txt” foreach($SqlServer in $SQLserverDB) { Write-Host “Server :”$SqlServer -ForegroundColor Yellow $server = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServer) $server|SELECT ServiceName,ServiceAccount,Status,PhysicalMemory |ft -AutoSize }

Powershell and SQL Server – 2- List Databases and Mirroring Status from Multiple SQL Server Instance with SMO

The Script will list out all database configured for DBMirroring $SQLserverDB=@() $SQLserverDB=Get-Content “C:\PowershellScript\ServerList.txt” foreach($SqlServer in $SQLserverDB) { Write-Host “Server :”$SqlServer -ForegroundColor Yellow $server = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServer) $server.Databases |Where-Object{$_.IsMirroringEnabled -eq ‘True’}| select Name, Size, DataSpaceUsage, IndexSpaceUsage, SpaceAvailable,MirroringStatus,IsMirroringEnabled | ft }

Powershell and SQL Server – 1- List Databases and Size from Multiple SQL Server Instance with SMO

It’s quick to pull Database List with Size,Database Space Usage,Index Space Usage and Free Space with in databases using SMO with Powersehll. $SQLserverDB=@() $SQLserverDB=Get-Content “C:\PowershellScript\ServerList.txt” foreach($SqlServer in $SQLserverDB) { Write-Host “Server :”$SqlServer -ForegroundColor Yellow $server = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServer) $server.Databases | select Name, Size, DataSpaceUsage, IndexSpaceUsage, SpaceAvailable | ft } You can use SSMS Object explorer […]

Powershell and SQL Server – Server-Property – Edition-Version

The script loops through each of the sql instance and pulls SERVERPROPERTY information such as Version,Productversion,ProductLevel,Edition cls if (-not (Get-Module SQLPS)) {Import-Module SQLPS} #Get list of server from text files $SQLserverDB=@() $SQLserverDB=Get-Content “C:\PowershellScript\ServerList.txt” foreach($SqlServer in $SQLserverDB) { $result=Invoke-Sqlcmd -ServerInstance $SqlServer -Database master -Query “SELECT SERVERPROPERTY(‘ServerName’) ServerName,substring(@@version,0,charindex(‘-‘,@@version)) Version, SERVERPROPERTY(‘productversion’) Productversion, SERVERPROPERTY(‘productlevel’) Productlevel, SERVERPROPERTY(‘edition’)EngineEdition” -QueryTimeout 0 $result […]

Powershell and SQL Server – 4- List Databases from Multiple SQL Server Instance with Invoke-Sqlcmd

Get the database list from Multiple SQL Instances.Create a Server.txt and populate it with SQL Instance Name and access the content of file in an array. To access the Server.txt content in an array and loop it for each server $SQLserverDB=@() #Array variable $SQLserverDB=Get-Content “C:\PowershellScript\ServerList.txt” if (-not (Get-Module SQLPS)) {Import-Module SQLPS} #Get list of server from […]

Monitor running spid in SQL Profilor from SSMS

Monitor a running process in SQL Profiler. Normally we use to open the SQL profiler and filter the trace with selected counters. SSMS Activity Monitor provides a quick and easiest way. Select the spid right click and select “Trace Process in SQL Server Profiler”.It will open the profiler with filtered spid.  

Powershell and SQL Server – 3- Access SQL Server Instance – List Databases and Properties with Invoke-Sqlcmd

List out database name from a SQL Instance if (-not (Get-Module SQLPS)) { Import-Module SQLPS } $SqlServerName=”LENOVO-PC\SQL2016″ $result=Invoke-Sqlcmd -ServerInstance $SqlServerName -Database master -Query “SELECT * FROM sys.sysdatabases” -QueryTimeout 0 foreach ($dbname in $result.name) { $dbname } or foreach ($dbname in $result) { $dbname.name } List Database properties for each database if (-not (Get-Module SQLPS)) {Import-Module […]