Windows Powershell: Useful commands for the SQL DBA

What is Powershell?
The Windows Powershell tool is available on Windows Servers by default and is using to scripting purposes as well as many other functionalities such as Automating windows functions.

How to Access:
Powershell can be accessed through this icon on desktop area Pshell1 or  through executable file on the root drive (2.0 is the version here, there are versions 1.0,2.0 and 3.0). –  %SystemRoot%\system32\WindowsPowerShell\v2.0\powershell.exe

Useful Commands for SQL Server DBA

For a database administrator is very useful to have an utility to run remote commands to query data from multiple servers across the same domain. This can be done also using native DOS prompt (SQLCMD), but powershell has some interesting features as well and allows more customization. Here is a way to deploy health check scripts to route the commands to any other Server on the same domain.

1. Make sure the environment has RDP connection to other servers (such as Production to Dev/Test and back/forth).
2. Save your script on any location. Example C:\Scripts\SQL\Health_check.sql
3. On this same directory, create a new empty text file and enter this piece of code. The first 2 lines are to make Powershell able to use SQLCMD cmdlets, otherwise the string fails to start.
Then, $sql is a variable which receive user input (the SQL Instance name)  and once this data is given, the invoke-sqlcmd will execute the script.

Add-PSSnapin SqlServerCmdletSnapin100 -ErrorAction SilentlyContinue
Add-PSSnapin SqlServerProviderSnapin100 -ErrorAction SilentlyContinue
$sql = Read-Host ‘MSSQL Job Failure Check: Enter SQL Instance Name’
invoke-sqlcmd -inputfile “F:\MSSQL\jobfailure.sql” -serverinstance $sql -Verbose

Save the file with .ps1 extension so Powershell can recognize it as a Script. This is the result (example using sqlhc.ps1 file)

Pshell2

Here we input the SQL Instance name for a quick Instance health check.
Note:  This may different from each client environment: If the single instance name does not resolve, try using with full qualified domain name + port or VIP Address + Port or Cluster Group name + Virtual Server Name.

Result:

Pshell3.jpg

The same script can be adapted to hold another variable. Very useful when you want to run scripts agains a specific SQL Server database. Example below is for a User Database Space Check script.  The $sql variable is the input for SQL instance name, and $db the Database name.

Add-PSSnapin SqlServerCmdletSnapin100 -ErrorAction SilentlyContinue
Add-PSSnapin SqlServerProviderSnapin100 -ErrorAction SilentlyContinue
$sql = Read-Host ‘MSSQL Database Files Check: Enter SQL Instance Name’
$db = Read-Host ‘MSSQL Database Files Check: Enter Database Name’
invoke-sqlcmd -inputfile “F:\MSSQL\dbspace.sql” -serverinstance $sql -database $db  -Verbose

Pshell4

Power_prompt_space.jpg

With Powershell we can also manipulate the Services Console and Event Viewer:

Querying a remote server for running SQL Instances (any status)
get-service MSSQL*,SQLAGENT*  -computername <servername>  | Format-Table -AutoSize -Wrap

Pshell5

Querying a remote server for running SQL Instances (stopped or started status)

get-service MSSQL*,SQLAGENT*  -computername <servername>  | where-object {$_.Status -eq “Running”}  | Format-Table -AutoSize -Wrap
get-service MSSQL*,SQLAGENT*  -computername <servername>  | where-object {$_.Status -eq “Stopped”}  | Format-Table -AutoSize -Wrap

Querying a list of Servers for running SQL Instances:
notepad comp.txt
get-service MSSQL*,SQLAGENT* -computername (gc .\servers.txt) | where-object {$_.Status -eq “Running”}  | Format-Table -AutoSize -Wrap

Pshell6.jpg

Retrieving Event Viewer Logs data from remote servers:

In the -logname parameter, change to System, Application or Security. You can also modify the amount of data to be displayed on screen.
get-eventlog -logname System -entrytype Error -newest 20 -computername <insert_server_here> | Format-Table -AutoSize -Wrap

Finding 10 recent reboot commands on a computer
get-eventlog -logname System -source User32 -newest 10 -computername <insert_server_here> | Format-Table -AutoSize -Wrap

SQL Server – How to view the link between the Transaction Log x Data Page

One of the first things that a DBA learn is that on SQL Servers, the data that is displayed in the Tables are in fact internally stored in the Data Pages. Data Pages are the smallest storage unit and have 8KB max. Using the below script, we are able to actually see a transaction doing an insert and that data being produced directly in the data page.

Check it out:

 

Step 1 . Create a Test database and a user table.

USE [master]
GO

CREATE DATABASE PageTest
GO

USE [PageTest]
GO

CREATE TABLE Names
    (fname varchar(10)
    ,lname varchar(10)
      )
GO

Step 2. Change the recovery model to “Simple” and issue a manual Checkpoint – then insert sample data. The reason for the simple recovery model is because it generates less data in the transaction log , and by entering the manual checkpoint it forces SQL Server to synchronize the current records in the T-log to the data file, so there will be less rows to show in the fn_dblog undocumented function.

ALTER DATABASE [PageTest] SET RECOVERY SIMPLE WITH NO_WAIT
GO

CHECKPOINT
GO

USE [PageTest]
GO

INSERT INTO NamesVALUES (‘Walter’,’White’)
GO

Step 3.  Now, we are inspecting the function fn_dblog that keeps actual data from recent data changes. In this case we are looking for footprints on the INSERT statement we just did.And since it was entered after the checkpoint, it should be there.

SELECT a.operation
        ,a.[Current LSN]
        ,a.[Minimum LSN]
        ,a.[Format LSN]
        ,a.[Oldest Active LSN]
        ,a.[Page ID] FROM fn_dblog(NULL,NULL) a
        WHERE operation LIKE ‘%INSERT%’

The result should be similar to this (values may vary according to your environment and columns length).

lsn_1

Step 4.  Enter the following string to browse the data page information

DBCC TRACEON(3604)
DBCC IND (N’PageTest’,N’Names’,-1)

 

Output should be similar to this:

lsn2

Pick up the number of the PagePID for the row where PageType = 1 (Data Page)

Now, enter this DBCC to browse the actual data page content:

DBCC PAGE (N’PageTest’,1,296,1)

You will then see a lot of data that is not in a readable fashion. What we are looking for, is the m_lsn

lsn3

36:121:29

Convert this value from Decimal to Hexadecimal:

36 = 24
121 = 79
29 = 1D

Sounds familiar? Yes – this was our LSN found on step 3

00000024:00000079:001d

The m_lsn is the Log Sequence Number of the Last log record that  changed the data page. The INSERT statement from step 2.

If we scroll down a little bit, and look at the “Primary Record” information, Walter White will be there too:

lsn4

You can read more about Data Pages and Log Records on Paul Randal’s blog:

http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-anatomy-of-a-page/

http://www.sqlskills.com/blogs/paul/using-fn_dblog-fn_dump_dblog-and-restoring-with-stopbeforemark-to-an-lsn/

SQL Server Overview Training

This SQL Server Overview Training was created by me originally to help IBM professionals from another support areas, such as SAP, Production Control and Storage, to have a good first look at the SQL Server fundamentals and the day to day tasks of a Database Administrator.  Now, I have updated the content and published on YouTube so it can be helpful for the DBA community worldwide. If you have no experience with SQL Server or are a professional that works with another technology in the RDBMS world, and are seeking for fundamentals on SQL Server, this material might be useful for you. I hope you enjoy the content! Send me any feedback or questions you have.

You can also view the Playlist and browse the Training by each Module according to your interest.

SQL Server Overview Training Space

Transact-SQL – Merge Statement Demonstration

I have recorded this  video with a demonstration of the MERGE statement in Transact-SQL which can be applicable for SQL Server 2008 onwards. If you have interest in taking the Microsoft Exam 70-461 (Querying SQL Server 2012) or just wants to know how it works,  it might be useful.  For a better display please watch in HD mode or full screen mode  on youtube. Thanks for watching!