SQL Server

Celebrating Pi Day

To All The SQL DBA’s and Programmers Happy Pi Day.

SQL Server

Select pi()

SQL_Pi

PowerShell

[math]::Pi

PowerShell_Pi

Visual C#

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Pi
{
    class Program
    {
        static void Main(string[] args)
        {
            double Pi = Math.PI;
            Console.WriteLine(Pi);
            Console.ReadKey();
        }
    }
}

VisualC#_Pi

Python

import math
print(math.pi)

Python

Advertisements

What To Do When SQL Server Stops Responding, Did You Try Using the Dedicated Administrator Connection?

Over the years I have seen my share of SQL Servers that have stopped responding. Most of the time in these situations there is a process that has consumed all the available resources on the server. You will need to use the Dedicated Administrator Connection (DAC) to gain access to the SQL Server to diagnose and collect information on the request that caused the issue and to bring SQL back online. The DAC is a safety line so you don’t have to restart the SQL Service and possibly put the database into recovery for several hours. Since DAC uses minimal resources, you will need to keep your queries simple. I have provided some queries below to view the requests on the SQL Server.

Dedicated Administrator Connection

  1. Open SQL Server Management Studio (SSMS)
  2. Click on the Database Engine Query Icon to open the login prompt or from SSMS go to File>>New>>Database Engine Query
  3. Enter ADMIN:Server Name, if using an instance use ADMIN:Server Name\Instance Name
  4. Enter the sysadmin credentials

DAC

 

If you get the following error, re-login using the Database Engine Query login prompt

BAC_Error

 

Now your connected to DAC what do you run?

When using DAC or when SQL Server is under a lot of resource pressure you need to keep you queries simple. Avoid using large complex queries and resource expensive DMV’s.

1.      View the Requests on SQL Server

Select Session_id, 
       Start_Time, 
       Status, 
       plan_handle, 
       database_id, 
       blocking_session_id, 
       wait_type, 
       cpu_time, 
       reads, 
       logical_reads,
       writes
from sys.dm_exec_requests
where status not in ('background','sleeping')

2. View the Session Information

Note you will need the Session ID from the First query

Select session_id, 
       login_name, 
       host_name, 
       program_name
from sys.dm_exec_sessions
where session_id = '[session_id]'

3.      View the SQL Text and Execution Plan.

Note you will need the Plan Handle from the First query

Select text from sys.dm_exec_sql_text([plan_handle);

Select query_plan from sys.dm_exec_query_plan (plan_handle);

4.       View the Database Name

Note you will need the Database ID from the First query

Select name from sys.databases where database_id = '(database_id)'

 

 

 

 

 

 

 

How To Create an HTML Table from PowerShell

 

PowerShell has made it easy to output data to an HTML table with just a few lines of code. In the examples below I will demonstrate on how to create an HTML table like the one below.

Blogpic1

 

Example 1. Create HTML Table

The ConvertTo-HTML Cmdlet converts the Get-service results to HTML and then the Out-File Cmdlet saves the output to c:\temp\ReportHTML.html. The Invoke-Item Cmdlet is used to open the HTML file. You can Copy & Paste the code below into your favorite PowerShell Editor or open Windows PowerShell ISE. Note if you don’t have SQL Server installed on the computer that your using, you can remove *SQL*.

$GService = Get-service *SQL* | Where-Object {$_.status} | Select Name, Status | ConvertTo-HTML -Fragment 

#Create and Save the HTML Web Page
ConvertTo-HTML -head "SQL Services" -Body "$GService" |Out-File c:\temp\TableHTML.html

#Open TableHTML.html
 Invoke-Item c:\temp\TableHTML.html

Example 2. Create HTML Table With Style

The HTML <style> tag is used to define the look of your HTML web page. The TABLE element is for displaying the tabular data. The TH element is to define the Table Header attributes  and the TD element is to define the Table Cell attributes.  Take a few minutes and change the border-width and the padding pixel (px) size.

$Style = "
<style>
    TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}
    TH{border-width: 1px;padding: 3px;border-style: solid;border-color: black;}
    TD{border-width: 1px;padding: 3px;border-style: solid;border-color: black;}
</style>
"
$GService = Get-service *SQL* | Where-Object {$_.status} | Select Name, Status | ConvertTo-HTML -AS Table -Fragment -PreContent '<h2>SQL Services</h2>'|Out-String 

#Save the HTML Web Page
ConvertTo-HTML -head $Style -PostContent $GService|Out-File c:\temp\TableHTML.html

#Open TableHTML.html
Invoke-Item c:\temp\TableHTML.html

Example 3. Background Color

To add a background color to your HTML web page, you can add BODY{background-color:#b0c4de;} to the <style> tag. To view all the HTML color codes you can go to http://html-color-codes.info/.

BODY{background-color:#b0c4de;}

Example 4. Table Header Background Color

To make the table header easier to read you can add a background color, add background-color:#778899 to the end of the TH element.

TH{border-width: 1px;padding: 3px;border-style: solid;border-color: black;background-color:#778899}

Example 5. Table Row Color

To make the table rows easier to read you can add alternating row colors by adding tr:nth-child(odd) and tr:nth-child(even) to the <style> tag.

tr:nth-child(odd) { background-color:#d3d3d3;} 
tr:nth-child(even) { background-color:white;}

 

Example 6. Cell Color

In this step I will show you how to change the table cell background color by using some logic and PowerShell scripting. If the SQL Service is Stopped the background color will be Red and Running will be Green. You can add the $StatusColor code right underneath the $Style code.

$StatusColor = @{Stopped = ' bgcolor="RED">Stopped<';Running = ' bgcolor="Green">Running<';}

The following line of code below it will use Find\Replace the HTML source code in the $GService parameter with the background color (<td>Stopped</td> is being replaced with <td bgcolor=”Red”>Stopped</td>). You can add the $StatusColor.keys code below right underneath the $GService parameter.

$StatusColor.Keys | foreach { $GService = $GService -replace ">$_<",($StatusColor.$_) }

Example 7. Multiple Tables

PowerShell has made it very simple to add multiple tables to your HTML web page. As you can see below I have added $Reboots parameter to collect last five entries of Windows Event 6005 from the event log. Event 6005 is logged at boot time noting that the Event Log service was started. You can add the $Reboots parameter right underneath the $StatusColor.keys parameter.

$Reboots = Get-WinEvent -FilterHashtable @{logname='system';id=6005} -MaxEvents 5| Select Message, TimeCreated  |ConvertTo-HTML -AS Table -Fragment -PreContent '<h2>Last 5 Reboots</h2>'|Out-String

To display the $Reboots results you just need to add the $Reboots parameter to the PostContent in the ConvertTo-HTML Cmdlet line of code.

ConvertTo-HTML -head $Style -PostContent $GService, $Reboots -PreContent '<h1>Web Page Title</h1>'|Out-File c:\temp\TableHTML.html

Example 8. Change the Font Attributes

I will show you a couple of ways to change the Font Attribute. In this example I am using the <h1> tag, but this will work in any of the HTML tags that contain text. The first way  is to add the following h1 tag line of code below to the <style> tag. Making the change in the <Style> tag is a global setting.

h1{color:blue;font-family:courier;font-size:300%}

The second way is to change the individual <h1> tag.

<h1 style="color:blue;font-family:courier;font-size:300%">

Full Code

$Style = "
<style>
    BODY{background-color:#b0c4de;}
    TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}
    TH{border-width: 1px;padding: 3px;border-style: solid;border-color: black;background-color:#778899}
    TD{border-width: 1px;padding: 3px;border-style: solid;border-color: black;}
    tr:nth-child(odd) { background-color:#d3d3d3;} 
    tr:nth-child(even) { background-color:white;}    
</style>
"
#Cell Color - Logic
$StatusColor = @{Stopped = ' bgcolor="Red">Stopped<';Running = ' bgcolor="Green">Running<';}

#SQL Service Status
$GService = Get-service *SQL* | Where-Object {$_.status} | Select Name, Status | ConvertTo-HTML -AS Table -Fragment -PreContent '<h2>SQL Services</h2>'|Out-String 

#Cell Color - Find\Replace
$StatusColor.Keys | foreach { $GService = $GService -replace ">$_<",($StatusColor.$_) }

#Last 5 Reboots
$Reboots = Get-WinEvent -FilterHashtable @{logname='system';id=6005} -MaxEvents 5| Select Message, TimeCreated  |ConvertTo-HTML -AS Table -Fragment -PreContent '<h2>Last 5 Reboots</h2>'|Out-String

#Save the HTML Web Page
ConvertTo-HTML -head $Style -PostContent $GService, $Reboots -PreContent '<h1>Web Page Title</h1>'|Out-File c:\temp\TableHTML.html #Open TableHTML.html Invoke-Item c:\temp\TableHTML.html
#Open TableHTML.html
Invoke-Item c:\temp\TableHTML.html

SQL Tips for a Successful Rollback

Rolling back can be a very painful experience but it doesn’t need to be. Bad things will happen even if you have tested and planned your deployment. People tend to forget until it is too late that the database is a key piece of the business.

What can we do on the SQL Server side to minimize rollback failures?

1. DBCC CheckDB \ Backup \ Restore
How do these three items affect my rollback?

  • Is the database free of errors?
  • When was the last time you ran DBCC CheckDB or reviewed the results?
  • Are you performing good backups?
  • Are you performing regular restores, the ones from tape?
  • When was the last time you restored the master database or any system databases?

2. Adding a Log Table to your deployment scripts.
A log table, this is a great way to keep track of the deploy script progress. For each progress step insert a comment.

Create table ##LogTable
(rownbr int IDENTITY(1,1),
dt datetime,
comment varchar(250))

INSERT INTO ##LogTable
(dt, comment) values (GETDATE(), ‘Add Your Progress Comments’)

3. Create a Rollback Script
A Rollback Script will undo the changes from the deployment script, for example drop the new objects (table, store procedure, index) that was created.

4. Explicit Transactions
By adding Explicit Transactions (Begin Transaction, Commit and Rollback) your deployment scripts will rollback on any error in your code. I am working on an Explicit Transaction demo for an upcoming blog post.

A few thoughts
No matter how much testing you do, Bad Things Happen.
What takes seconds to run, will take hours to unwind.