Powershell alert, once server is up and running

 We have got regular server patches every month. When the server is restarted, we have to keep checking whether server is up again. The below powershell script will be helpful, to let us know, once server is up and running.

1:  # Load assembly  
2:  [System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms")  
3:  $ComputerName = "FQDN of Server"  
4:  $ServerOnline = $false  
5:  while($ServerOnline -eq $false)  
6:  {  
7:    if(Test-Connection -BufferSize 32 -Count 1 -ComputerName $ComputerName -Quiet)  
8:    {  
9:     [System.Windows.Forms.Messagebox]::Show("The server $computerName is up now");  
10:     $serverOnline = $true;  
11:     break;  
12:    }  
13:    else  
14:    {  
15:      [System.Windows.Forms.Messagebox]::Show("The server $computerName is offline now");  
16:      Start-Sleep -Seconds 5  
17:    }  
18:  }  

Copy web.config between two web servers

We are having load balancing of web portal, in two different web servers. The web.config has to be same between web portal. If one of them is changed, we want to copy the same to another server. Below script can be used to do the same:

I have put local path just for reference. UNC path can be used in place of local path. 

1:  $strSourceFile = "C:\\dev\\wv02\\test.txt"  
2:  $strDestFile = "C:\\dev\\wv01\\test.txt"  
3:  if ((Get-ChildItem $strSourceFile).LastWriteTimeUtc -gt (Get-ChildItem $strDestFile).LastWriteTimeUtc)  
4:  {  
5:    write-host "$($strSourceFile) is latest than $($strDestFile)"  
6:    copy-item -Path $strSourceFile -Destination $strDestFile  
7:  }  
8:  else  
9:  {  
10:     write-host "$($strDestFile) is latest than $($strSourceFile)"  
11:     copy-item -Path $strDestFile -Destination $strSourceFile  
12:  }  

Sending Email in Powershell

Below script can be used to send email from Powershell, for audit report for yesterday. 

1:  $yesterday = (get-date).AddDays(-1).Date.ToString("yyyy_MM_dd")  
2:  $yesterdayMailSubject = (get-date).AddDays(-1).Date.ToString("yyyy-MM-dd")  
3:  $From = "test@email.com"  
4:  $To ="test@email.com"  
5:  $Cc = "test@email.com"  
6:  $Attachment = "C:\temp\Audit_$yesterday.csv"  
7:  $Subject = " DB Server Audit for Day: $yesterdayMailSubject "  
8:  $Body = "<h2>Please see the audit report attached file</h2><br><br>"  
9:  $SMTPServer = "SMTPServerName"  
10:  $SMTPPort = "587" #SMTPPort  
11:  Send-MailMessage -From $From -to $To -Cc $Cc -Subject "test" -Usessl -Body "test" -SmtpServer $SMTPServer -Port $SMTPPort -Credential $From -Verbose -Debug #-Attachments $Attachment  

How to create covering Index to fix performance Issue

We had a performance issue, where we had to create  covering index to solve the problem, as we were querying many columns in the table in a single query. 

Problem Statement:

we are having a table with 10 columns. We have 9 columns in a query, for getting clustering key (identity column here). 

1:  SELECT IdentityValue FROM TableWithIdentity   
2:  WHERE col1 = @col1 , col2 = @col2, col3 = @col3, col4 = @col4, col5 = @col5, col6 = @col6, col7 = @col7, col8 = @col8, col9 = @col9  

It is a SQL Server 2012 database. We had to create a covering index with 10 columns. How do I decide the order of columns in the covering index for efficient index ?

Solution Adopted:

1. Get the statistics names associated with table columns. 

1:  SELECT s.stats_id StatsID,  
2:   s.name StatsName,  
3:   sc.stats_column_id StatsColID,  
4:   c.name ColumnName  
5:  FROM sys.stats s  
6:   INNER JOIN sys.stats_columns sc  
7:    ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id  
8:   INNER JOIN sys.columns c  
9:    ON sc.object_id = c.object_id AND sc.column_id = c.column_id  
10:  WHERE OBJECT_NAME(s.object_id) = 'TableWithIdentity'  
11:  ORDER BY s.stats_id, sc.column_id;  

2. Get the statistics for each column and note down the density of each column in the first result of the SHOW_STATISTICS. 

1:  DBCC SHOW_STATISTICS('dbo.TableWithIdentity',IndexColumnName1');  
2:  DBCC SHOW_STATISTICS('dbo.TableWithIdentity',IndexColumnName2');  
3:  DBCC SHOW_STATISTICS('dbo.TableWithIdentity',Column3Statistics');  
4:  DBCC SHOW_STATISTICS('dbo.TableWithIdentity',Column4Statistics');  
5:  DBCC SHOW_STATISTICS('dbo.TableWithIdentity',Column5Statistics');  
6:  DBCC SHOW_STATISTICS('dbo.TableWithIdentity',IndexColumn6Statistics');  
7:  DBCC SHOW_STATISTICS('dbo.TableWithIdentity',Column7Statistics');  
8:  DBCC SHOW_STATISTICS('dbo.TableWithIdentity',Column8Statistics');  
9:  DBCC SHOW_STATISTICS('dbo.TableWithIdentity',Column9Statistics');  

3. Now, we know the density of each column. We have to choose the column with least density (meaning more unique column) and followed the next least density etc. 

0.00	column1
0.00	column2
0.01	column3
0.10	column4
0.33	column5
2.09	column6
3.09	column7
5.55	column8
6.46	column9

4. Now, we created covering index based on the uniqueness of the columns order

1:  CREATE INDEX IDX_TableWithIndentity_covering on dbo.TableWithIdentity(column1, column2,column3, column4, column5, column6, column7, column8, column9)   
2:  WITH  
3:  (  
4:  SORT_IN_TEMPDB = ON,  
5:  DROP_EXISTING = OFF  
6:  )  
7:  ON [PRIMARY]  

5. Now, the index is very helpful for fixing the performance Issue.

Getting detailed Performance issue metrics using sp_whoisactive

 

The below sp_whoisactive query can be executed to get detailed performance metrics for slowly running query, which can be later analyzed for troubleshooting performance issue

1:  EXEC sp_WhoIsActive @get_plans = 1, @get_locks = 1, @get_task_info = 2, @show_system_spids =1,  
2:  @show_own_spid =1, @show_sleeping_spids = 1  

Getting SQL Server Instance Services Information

Below query can be run to get SQL Server instance services information:

1:  SELECT DSS.servicename,  
2:  DSS.startup_type_desc,  
3:  DSS.status_desc,  
4:  DSS.last_startup_time,  
5:  DSS.service_account,  
6:  DSS.is_clustered,  
7:  DSS.cluster_nodename,  
8:  DSS.filename,  
9:  DSS.startup_type,  
10:  DSS.status,  
11:  DSS.process_id  
12:  FROM sys.dm_server_services AS DSS;  

Steps to follow for renaming Stored Procedures

There are two approaches to rename stored procedure. Dropping and Recreating Procedure : The problem is it will lead to losing of permissions. sp_rename of Procedure : Permissions will remain intact. The stored procedure will be renamed. But, sys.sql_modules will still be having the old definition. I prefer the second approach. I followed the below steps:

1. Have copy of the stored Procedure content Rename the stored procedure

1:  EXEC sp_rename 'dbo.OldStoredProcedureName','NewStoredProcedureName'  
2:  GO  

2. ALTER PROCEDURE to have the modified code of the procedure in the sys.sql_modules

1:  ALTER PROCEDURE dbo.NewStoredProcedureName  

3. Now, Stored procedure name is also updated and code is refreshed in sys.sql_modules and permissions are also intact.

Counting of files containing keyword in Powershell

When we want to see how many files are using a specific keyword, we can use below powershell script to do this. This will be helpful if we want to estimate the amount of code changes needed for a keyword change.

1:  set-location c:\dev\codefiles  
2:  $count = 0  
3:  $files = Get-ChildItem -Recurse -File `  
4:  #if we want to filter the files based on date range  
5:  `#|   Where-Object { $_.CreationTime -ge "12/13/2017" -and $_.CreationTime -le "12/29/2017" } `  
6:   ForEach($file in $files)  
7:   {  
8:     if ( (Get-Content $file -Raw).Contains("shipsite=`"KeywordToFind`""))  
9:     {  
10:      write-host $file; $count++;  
11:     }   
12:   }  
13:   Write-Host "count of files containing word:$count  

Loading CSV file to SQL Server table

Loading CSV file to database using BULK INSERT

Below are the step by step approach to easily load data from CSV file into SQL Server database table.

1. First create the table in the database corresponding to CSV file

CREATE TABLE NameList
(
FirstName varchar(40),
LastName varchar(40)
)

2. Now, generate format file for the above table using BCP Format option

C:\Users\venkat>BCP master.dbo.NameList format nul 
-x -S ServerName -c -f c:\dev\NameListFormat.xml -t, -T

3. Now, load data into the table using BULK INSERT

TRUNCATE TABLE dbo.NameList; -- (for testing)
BULK INSERT dbo.NameList
FROM 'c:\dev\NameList.csv'
WITH (FORMATFILE = 'C:\dev\NameListFormat.xml');
GO

4. Now, data is successfully loaded from CSV to SQL Server table.

Getting All SQL Agent Job Statuses

We can use below script to get status of all SQL Agent jobs, whether they are running or not. If they are running, we will get how many steps are there, what is the currently running step and elapsed time.


SELECT sj.Name,
CASE
WHEN sja.start_execution_date IS NULL THEN 'Never ran'
WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NULL THEN 'Running'
WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NOT NULL THEN 'Not running'
END AS 'RunStatus',
CASE WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NULL then js.StepCount else null end As TotalNumberOfSteps,
CASE WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NULL then ISNULL(sja.last_executed_step_id+1,js.StepCount) else null end as currentlyExecutingStep,
CASE WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NULL then datediff(minute, sja.run_requested_date, getdate()) ELSE NULL end as ElapsedTime
FROM msdb.dbo.sysjobs sj
JOIN msdb.dbo.sysjobactivity sja
ON sj.job_id = sja.job_id
CROSS APPLY (SELECT COUNT(*) FROM msdb.dbo.sysjobsteps as js WHERE js.job_id = sj.job_id) as js(StepCount)
WHERE session_id = (
SELECT MAX(session_id) FROM msdb.dbo.sysjobactivity)
ORDER BY RunStatus desc