SQL SERVER – PowerShell – Knowing SQL Server Information

If you search this blog, there are a number of PowerShell scripts that I have shared in the recent past. Some of them include:

Using PowerShell and Native Client to run queries in SQL Server

Identify Disk Latency on SQL Server Box Using PowerShell

How to Find Logical and Physical Processors on SQL Server?

As I write a number of them, every time I explore and do something totally different. So in this blog, I want to do a neat trick of using PowerShell. Here I we will try to get the details of SQL Server running on my localhost and then we will try to create an HTML page which will hold and show the data.

</pre>
$OutputFile = "c:\temp\test.html"
$SQLServer = "localhost"
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$Result = @()
$SQLServerInfo = new-object “Microsoft.SqlServer.Management.Smo.Server” $SQLServer
$Result += $SQLServerInfo | Select Name, Edition,ProductLevel, Version, ServerType, Platform, IsClustered, PhysicalMemory, Processors
if($Result -ne $null)
{
$HTML = '<style type="text/css">
#Header{font-family:"Trebuchet MS", Arial, Helvetica, sans-serif;width:100%;border-collapse:collapse;}
#Header td, #Header th {font-size:14px;border:1px solid #98bf21;padding:3px 7px 2px 7px;}
#Header th {font-size:14px;text-align:left;padding-top:5px;padding-bottom:4px;background-color:#A7C942;color:#fff;}
#Header tr.alt td {color:#000;background-color:#EAF2D3;}
</Style>'
$HTML += "<HTML><BODY><Table border=1 cellpadding=0 cellspacing=0 width=100% id=Header>
<TR>
<TH><B>Server Name</B></TH>
<TH><B>Edition</B></TD>
<TH><B>ProductLevel</B></TH>
<TH><B>Version</B></TH>
<TH><B>ServerType</B></TH>
<TH><B>Platform</B></TH>
<TH><B>IsClustered</B></TH>
<TH><B>PhysicalMemory (MB)</B></TH>
<TH><B>Logical Processors</B></TH>
</TR>"
$HTML += "<TR>
<TD>$($Result.Name)</TD>
<TD>$($Result.Edition)</TD>
<TD align=center>$($Result.ProductLevel)</TD>
<TD>$($Result.Version)</TD>
<TD>$($Result.ServerType)</TD>
<TD>$($Result.Platform)</TD>
<TD align=center>$($Result.IsClustered)</TD>
<TD align=center>$($Result.PhysicalMemory)</TD>
<TD align=center>$($Result.Processors)</TD>
</TR>"
$HTML += "</Table></BODY></HTML>"
$HTML | Out-File $OutputFile
}
<pre>

The output of this will be a file on our c:\temp folder. If we open the same in a browser, you can see the SQL Server Information about version, edition, Server type, Memory and Processor on the box. This is a neat way to explore and play with a scripting language and PowerShell to create something interesting.

Have you ever created something really interesting like this in your environments? Will you be willing to share something via the comments so that others can also take advantage of this collective knowledge.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

PowerShell: How to Find Logical and Physical Processors on SQL Server?

Working on PowerShell scripts have been an interesting journey. I have written a couple of posts before on using WMI events. Was pondering on this thought and this blog is inspired by combining using of WMI with PowerShell. So what are we trying to solve?

When SQL Server changed its whole licensing process from processor based licensing to core based licensing, a lot of us were confused to how this has to be calculated. Recently, one of my DBA friends asked how can I find the number of logical processors (cores) on my SQL Server machine? Now this question got me thinking and the first place I always go to search is SQLAuthority – trust me on this. Once figured out there isn’t much on this topic, I thought to write a simple script which I can refer in the future.

$Computer = 'localhost'
$ErrorActionPreference = 'SilentlyContinue'
$Error.Clear()
$ProcessorConfig = Get-WmiObject -class Win32_Processor -computername $Computer -namespace root\CIMV2 | Select PSComputerName, Name, NumberOfCores, NumberOfLogicalProcessors
write-Debug $Error.Count
If ($Error.Count -gt 0)
{
$ProcessorConfig = New-Object psobject
$ProcessorConfig | Add-Member -type NoteProperty -name ComputerName ("$Computer-failed to connect")
$ProcessorConfig | Add-Member -type NoteProperty -name Name -value 'Unable to get ProcessorInfo'
$ProcessorConfig | Add-Member -type NoteProperty -name NumberOfCores -value $null
$ProcessorConfig | Add-Member -type NoteProperty -name NumberOfLogicalProcessors -value $null
Write-Debug "Failed to connect to $Computer"
}
$ErrorActionPreference = 'Continue'
$ProcessorConfig | FT * -AutoSize

When you run this above script inside PowerShell, we will get to see an output like below:

logical physical processor 01 PowerShell: How to Find Logical and Physical Processors on SQL Server?

As you can see I have a 4 core processor and have enabled HT (Hyper-Threading) which makes the number of Logical Processor to 8. I personally felt this was a quick way to learn PowerShell when you have a typical problem in hand.

If you have read this far, please let me know how you have used WMI or PowerShell to solve interesting problems. Let me know over the comments and I will surely try to publish a few more like these in the future too based on your inputs.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – PowerShell to Count Number of VLFs in SQL Server

If you are a seasoned DBA, then the conversations and monitoring VLFs is something you are already doing. To learn about basics of VLF, check my earlier blog at: SQL SERVER – Detect Virtual Log Files (VLF) in LDF. Having large number of VLFs have a performance impact and there are a number of blogs that discuss the same. As a DBA in this blog I thought of showing a simple script that will help identifying the number of VLFs available inside a SQL Server instance or a number of instances.

As part of my powershell learnings, this is a simple script that you can use in your environment and take corrective action if you have large number of VLFs in your servers.

<#
EXAMPLE
   Get-VLFCount -ComputerName ComputerName
EXAMPLE
   Get-Content D:\ServerList.txt | Get-VLFCount

   If you are using Windows Authentication and have a list of servers you can use this.
   Get-VLFCount -ComputerName ComputerName1, ComputerName2
#>
function Get-VLFCount
{
    [CmdletBinding()]
    Param
    (
        # SQLInstance is the name(s) of the SQL Instances to scan
        [Parameter(Mandatory=$true,
                   ValueFromPipeline=$True,
                   ValueFromPipelineByPropertyName=$true,
                   Position=0)]
        $SQLInstance
    )
    Begin
    {
        #Load the Assembly to connect to the SQL Instance.
        [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
        [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.ConnectionInfo') | out-null

    }
    Process
    {
        ForEach ($Instance in $SQLInstance)
        {
            $SrvConn = new-object Microsoft.SqlServer.Management.Common.ServerConnection
            $SrvConn.ServerInstance=$Instance
            #Use Integrated Authentication
            $SrvConn.LoginSecure = $true
            $SrvConn.ConnectTimeout = 5
            Write-Debug "Attempting to check $Instance for VLF Counts"
            $srv = new-object Microsoft.SqlServer.Management.SMO.Server($SrvConn)
            $dbs = $srv.Databases
            try
            {
                ForEach ($db in $dbs)
                {
                    Write-Debug "Getting VLFInfo for $db"
                    if ($db.IsAccessible)
                    {

                        $VLFs = $db.ExecuteWithResults("DBCC LOGINFO")
                        $NumVLFs = $VLFs.Tables[0].Rows.count

                        $VLFinfo = $db | Select @{Name='Instance Name'; expression={$Instance}}, @{Name='Database Name'; Expression = {$_.name}} `
                        , @{Name='VLFCount()'; Expression={$NumVLFs}}

                    }
                    else
                    {
                        $VLFInfo = New-Object psobject
                        $VLFInfo | Add-Member -type NoteProperty -name InstanceName ($Instance)
                        $VLFInfo | Add-Member -type NoteProperty -name DBName ("$DB is Inaccessible")
                        $VLFInfo | Add-Member -type NoteProperty -name VLFCount 0
                    }
                    Write-Output $VLFinfo
                }

            }
            catch
            {
                $ex = $_.Exception
                Write-Debug "$ex.Message"
                Write-Error "Could not pull SQL DB Info on $Instance"
            }

        }
    }
}

#If we had our instances in a file, we can use
Get-VLFCount 'localhost'

The above script when run on my server returns the following:

PS VLF Count 01 SQL SERVER   PowerShell to Count Number of VLFs in SQL Server

As you can see in our example output, our AdventureWorks database has large number of VLFs. Incase you have hundreds of VLFs in your environments, then you can look at SQL SERVER – Reduce the Virtual Log Files (VLFs) from LDF file to reduce the number of VLFs for your databases.

Just out of curiosity, I would love to know what is the highest number of VLF in your databases? How long have these been running in your systems? It would be great if you can share the same with us over comments so that it would a great learning for me too.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Using PowerShell and Native Client to run queries in SQL Server

I have been getting my hands dirty working with PowerShell commands for a fun learning experience and few have made to this blog. The after effect of this is that I have been pinged for few queries on them too. Recently I got a call from one of my friend and he wanted to use PowerShell to connect to SQL and run some queries. Initially I thought that he is talking about SQL PowerShell, but his intention was to connect to SQL using SQL Server Native Client and run the query. That was strange but I was ready and up for the challenge in front of me. The curiosity is just too much that I couldn’t sleep till I found a solution to this challenge he put to me.

I wrote a small sample and sharing here for your benefit. First, we need to create a sample database and a table which would be used by PowerShell.

CREATE DATABASE SQLAuthority
GO
USE SQLAuthority
GO
CREATE TABLE EmployeeMaster (
EmployeeID INT
,FirstName VARCHAR(10)
,
LastName VARCHAR(10)
)
GO

Now, our task is to use PowerShell and insert data into EmployeeMaster. Here is the step by step PowerShell script.

# Make Connecting string to the SQL Server Instance and the Database
$con = New-Object System.Data.SqlClient.SqlConnection("Data Source=.; Initial Catalog=SQLAuthority; Integrated Security=SSPI")
# Define variables and the values to be inserted
$EmployeeID = 1
$FirstName = 'Pinal'
$LastName = 'Dave'
# Open database connection
$con.Open()
#Create SQL Insert Statement for table EmployeeMaster with the values
$stmt_insert = "INSERT INTO [EmployeeMaster]
([EmployeeID],[FirstName],[LastName])
VALUES($EmployeeID ,'$FirstName' ,'$LastName')"
# Create command to execute to SQL connection
$cmd = $con.CreateCommand()
$cmd.CommandText = $stmt_insert
# Invoke the Insert statement
$cmd.ExecuteNonQuery()

Once we execute above, the data would be inserted into the table. Now, to fetch the data, I am going to use invoke-SQLCMD cmdlet

Add-PSSnapin SqlServerCmdletSnapin100
$DataSet = Invoke-Sqlcmd -Database "SQLAuthority" -Query "SELECT * FROM [EmployeeMaster]" -ServerInstance .
foreach ($element in $DataSet)
{
$element
}
# Close database Connection
$con.Close()

We can run all the statements together in one PowerShell script and use Windows PowerShell ISE to execute it.

sql ps sample 01 SQL SERVER   Using PowerShell and Native Client to run queries in SQL Server

You might get a few errors while using Invoke-SQLCMD which I have explained here

SQL SERVER – FIX – The term ‘Invoke-Sqlcmd’ is not recognized as the name of a cmdlet

Hope this would give you a head start of using PowerShell to connect to SQL and run queries. This was a cool experiment for me and I thought was worth sharing. Have you ever had such requirements? Do share your experience over comments below.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Identify Disk Latency on SQL Server Box Using PowerShell

Couple of week back, I was playing around with using Powershell for some simple activities. The fall out of that was the ability to search the SQL Server Error Logs. Though we can search for anything, I just showed you a case of usage. In this blog post, I will talk about one of the most sought after request for searching what the disk latencies are.

Disks are one of the most notorious when it comes to bring the server performance down. It is sometimes quite difficult to identify the problems. A lot of times, the first point of search would be using resource monitor. But in this blog, I thought let us try to automate this collection process using a PowerShell script. This ultimately shows how powerful this scripting language is and how one can use it to collect and process few Performance Counter values.

param (
    [string]$ServerName = "localhost",
    [int]$SampleFrequencySeconds = 10,
    [int]$CollectionDurationSeconds = 120
)

# do a check to ensure that collection duration is greater than
# or equal to the frequency rate
if ($CollectionDurationSeconds -lt $SampleFrequencySeconds) {
    Write-Error "CollectionDurationSeconds cannot be less than SampleFrequencySeconds"
    exit
}

# loop through all of the drives, sampling them
$DrivesOutput = for ($i = 0; $i -lt [int]($CollectionDurationSeconds / $SampleFrequencySeconds); $i++) {
    Get-Counter -Counter "\LogicalDisk(*)\avg. disk sec/transfer" |
        Select-Object -ExpandProperty CounterSamples |
        Where-Object {$_.InstanceName -ne "_total"} |
        Select-Object InstanceName,
            @{Name = "Type"; Expression = {"LOGICAL"}},
            CookedValue

    Get-Counter -Counter "\PhysicalDisk(*)\avg. disk sec/transfer" |
        Select-Object -ExpandProperty CounterSamples |
        Where-Object {$_.InstanceName -ne "_total"} |
        Select-Object InstanceName,
            @{Name = "Type"; Expression = {"PHYSICAL"}},
            CookedValue

    # Sleep for the specified frequency before continuing in the loop
    Start-Sleep -Seconds $SampleFrequencySeconds
}

# Group by the drive and Calculate the average for each drive we have
# round to the nearest [ms]
$DrivesOutput |
    Group-Object InstanceName, Type |
    Select-Object @{Name = "InstanceName"; Expression = {$_.Group.InstanceName[0]}},
        @{Name = "Type"; Expression = {$_.Group.Type[0]}},
        @{Name = "DiskLatencyMs"; Expression = {
            [int](($_.Group.CookedValue | Measure-Object -Average).Average * 1000)}
        } |
    Sort-Object InstanceName

As part of the script we have gone ahead and started our collection of counters once every 10 seconds. In this script, I have used 2 mins interval for the collection process. We have collected two counters – “\LogicalDisk(*)\avg. disk sec/transfer” and “\PhysicalDisk(*)\avg. disk sec/transfer”.

PowerShell DiskLatency 01 SQL SERVER   Identify Disk Latency on SQL Server Box Using PowerShell

I have gone ahead and shown a typical output from my PC. I have two physical drives and 3 logical drives. And we can see the disk latencies in (milliseconds).

I am sure the output would vary in your environments. Do let me know if such scripts are useful and you would want me to write more about them. I am slowly starting to love this scripting language that I plan to explore the various ways people use this powerful features.

Do let me know via comments some of the ways you have used PowerShell in your environments and which of those are even used in Production?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – PowerShell Script – When Was SQL Server Last Restarted?

I have always wanted to learn a bit of scripting and I was always searching for a good reason to learn something new. As I said, this is my learning and I am open to learning some scripting tricks from you too. So do let me know how the below script can be optimized in your comments. Now, what was my reason to learn this script?

Someone asked me, “How can I find out when SQL Server was started?” There are multiple ways to find out, but I took the simple route and said – “Why don’t you check the SQL Server Error logs?” I know, the SQL Server error logs can get recycled and this data maynot be available. But in most cases, this gives us some indication.

So the other question was how can we script this requirement? So the below script is written to read SQL Server ErrorLog, find a specific text and report the number of times the string was found. We also show the time when the last/recent occurrence was found.

param
 (
 $serverinstance = ".", #Change this to your instance name
 $stringToSearchFor = "Starting up database ''master''"
 )
 # Load Assemblies
 [Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo') | Out-Null
 # Server object
 $server = New-Object -TypeName "Microsoft.SqlServer.Management.Smo.Server" -ArgumentList $serverinstance
 $searchCriteria = "Text like '%{0}%'" -f $stringToSearchFor
 $SQLErrorLogs = $Server.EnumErrorLogs()
 $SearchEntry = @()
 ForEach ($SQLErrorLog in $SQLErrorLogs)
 {
 $SearchResult = $server.ReadErrorLog($SQLErrorLog.ArchiveNo).select($searchCriteria) | Select-Object -Property LogDate, Text
 $SearchEntry = $SearchEntry + $searchResult
 }
 $MeasureOccurences = $SearchEntry | Measure-Object -Property LogDate -Minimum -Maximum
 $SQLSearchInfo = New-Object psobject -Property @{
 SearchText = $stringToSearchFor
 Occurances = $MeasureOccurences.Count
 MinDateOccurred = $MeasureOccurences.Minimum
 MaxDateOccurred = $MeasureOccurences.Maximum
 }
 Write-Output $SQLSearchInfo | FT -AutoSize

Do let me know if you ever used such scripting capability to search your errorlogs? You can change the search string and the default instance to your environment.
I wrote this generically because I was planning to use this to find error messages inside ErrorLogs like “I/O requests taking longer than 15 seconds to complete”. The script can be modified to your needs. Do share your scripts too over the comments so that others can benefit from the sharing.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – FIX – The term ‘Invoke-Sqlcmd’ is not recognized as the name of a cmdlet

helppushbutton SQL SERVER   FIX   The term Invoke Sqlcmd is not recognized as the name of a cmdletIt’s always a fun to learn and share new thing which I learn about any technology. So I decided to spend my weekend at home quietly, so I could learn some scripting using PowerShell and SQL Server. My laptop has tons of software installed so I always use my Virtual Machine to learn new things because I want to see how the things work when someone is working as a learner. My day didn’t start well as I was hit by errors as soon as I started Invoke-SQLCMD. This is part of PowerShell command and is worth a look.

This is the first error I got as soon as I wanted to run Invoke-SQLCMD

Error # 1 The term ‘Invoke-Sqlcmd’ is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.

The Error was because of the fact that I just installed SQL Server Engine on the machine. I didn’t install any client components. So essentially the error appears because the Invoke-sqlcmd cmdlet is not included as part of Windows PowerShell, but instead it is of sqlps (SQL Server 2008 PowerShell Host). So, before using invoke-sqlcmd we should install SSMS or the SQL Server Feature Pack (latest is SQL 2014 which can be downloaded from here)

Once installation was done, I ran the command and I got a new error.

Error # 2The ‘invoke-sqlcmd‘ command was found in the module ‘SQLPS’, but the module could not be loaded. For more information, run ‘Import-Module SQLPS’.

Here is the complete error message

Invoke SQLCMD 01 SQL SERVER   FIX   The term Invoke Sqlcmd is not recognized as the name of a cmdlet

As we can see above, I can also run Import-Module SQLPS to know more. Here is the output of the command

Error # 3 Import-Module : File E:\Program Files (x86)\Microsoft SQL Server\120\Tools\PowerShell\Modules\SQLPS\Sqlps.ps1 cannot be loaded because running scripts is disabled on this system. For more information, see about_Execution_Policies at http://go.microsoft.com/fwlink/?LinkID=135170.

Here is the complete error message

Invoke SQLCMD 02 SQL SERVER   FIX   The term Invoke Sqlcmd is not recognized as the name of a cmdlet

Why this error? If you know the basics of PowerShell, every command is called cmdlet (pronounced as command let). Invoke-SQLCmd is also a cmdlet provided either by the snap-in SqlServerCmdletSnapin100 (prior to SQL Server 2012) or module SQLPS (SQL Server 2012+). So, we need any one loaded into PowerShell (for example, at the beginning of your script) before you can call the cmdlet.

Assuming PowerShell is installed on the SQL server. You can open the Windows PowerShell Command prompt as below and get on to SQL power shell environment.

Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100

Once I followed above, I was able to use Invoke-SQLCMD as below

Invoke SQLCMD 03 SQL SERVER   FIX   The term Invoke Sqlcmd is not recognized as the name of a cmdlet

Hope this would help others in finding the solution to the errors. How many of you here are frequent users of PowerShell to administer and automate SQL Server tasks?
Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – PowerShell way of Restarting SQL Server Service

HELP (1) SQL SERVER   PowerShell way of Restarting SQL Server ServiceI learn a lot when I go for conferences all around the world. There are styles, content and cultural differences that I get a chance to learn and enjoy as part of my trips. Most of these trips I make are geared towards at least one learning experience I can get end of the day. With these travels, I also attend and speak at local user groups to get a flavour of what is the neat and coolest way to work with SQL Server. Recently at our local user group (SQL Server Bangalore UG) UG meeting, I was vividly stumped by a question from one of the speakers. Here he was talking about various ways to shut down or start SQL Server.

This is was an interesting quiz and I was pleasantly surprised by the most common methods and wanted to participate in this quiz. The answers were flowing all over and here are some that I would like to share which were part of audience interactions:

  1. Object Explorer in SSMS (SQL server Management Studio)
  2. Net Start Command.
  3. SQL Server Configuration Manager.
  4. Windows Services.
  5. Using the Sqlservr.exe executable

I thought I had known most of these and the usage of sqlservr.exe was something I wanted to check as it had been ages. For complete list of startup parameters refer book online.

Getting back to UG meet, I couldn’t hold my curiosity back and I was quick to raise my hand to give what I thought could be yet another answer. I shouted out loud the “SHUTDOWN” command. This was something I thought would fit the bill too. The speaker was understanding to say, this was one of the partially correct answer. Taken aback, I eagerly waited for the explanation.

Though the answer was correct, it was to shut down the service and didn’t allow us to restart our SQL Server service. And the speaker, waited for more answers. This made me curious of the fact that there was more to what I knew.

Welcome PowerShell

The speaker was quick to say, now that we have exhausted all the options available – let me talk about Windows PowerShell and it can be yet another way to restart SQL Server Service. It completely slipped my mind about this option. PowerShell is a powerful way to script and do activities with SQL Server. If you haven’t played around, then I strongly suggest to have a look into the same.

Now, how can I restart SQL Server Service? In this example below, I have used the PowerShell ISE IDE to execute the command as identified below:

restart sql powershell 01 SQL SERVER   PowerShell way of Restarting SQL Server Service

Restart-Service -Force MSSQLSERVER

 restart sql powershell 02 SQL SERVER   PowerShell way of Restarting SQL Server Service

Since there was no feedback from the console, I was curious to know if the SQL Server service did restart properly. On examining the Task manager I could confirm that the service did restart because the PID values were completely different after the command got executed.

Sometimes, we carry of knowledge that needs a refresher. Hence taking a student approach to everything we do can help a lot in this learning experience.

Do let me know if you found any other way to restart SQL Server and I would be more than happy to learn it from you.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Start Services or Stop Services with PowerShell – Answer to Question

shutdown computer SQL SERVER   Start Services or Stop Services with PowerShell   Answer to QuestionEarlier I asked a very interesting question related to Powershell and T-SQL command. Read my earlier blog post SQL SERVER – Start Services or Stop Services with PowerShell – Question to Readers?.

Here is the question which I asked – Stop-Services ‘mysqlserver in powershell is equivalent to Shutdown or Shutdown With Nowait in T-SQL?

Well, I asked this question because I really had no idea about the answer. However, my personal friend and Powershell Guru Laerte Junior immediately took the challenge to find answers to this question. He comes up with a fantastic explanation of the answer with proper findings and experiments.

Laerte did multiple test to figure out the answer to the question. He used traceflags 2592 and 3605 and to log the status of the checkpoint to the SQL Server log files. I strongly encouraged everyone to read his blog post as it will absolutely teach you something new. Here is the link to his blog post: Stop-Service is like the T-SQL Shutdown or Shutdown with Nowait ?

After doing multiple test Laerte proves that STOP-SERVICE -force  is like to use SHUTDOWN, performing the checkpoint in each database. Indeed a correct answer.

I did not know this before and I learned – Thanks Laerte. Did you know about this one? If you have learned this today as well, please leave a comment. The first step of learning anything is to accept we do the topic. The next step is to learn it.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Start Services or Stop Services with PowerShell – Question to Readers?

system shutdown SQL SERVER   Start Services or Stop Services with PowerShell   Question to Readers?Recently I received a very interesting question- I have no idea about its answer so I will request your help to answer this question.

Question:

“I am using a Powershell script to shutdown SQL Services. Here is the command I use.

Stop-Services 'mysqlserver' - Force

When I have to start the services, I use

Start-Services 'mysqlserver'

If you notice when I start this service, I do not have to use Force flag/command. If I do not use -Force flag when I try to stop services it gives me error that.

Stop-Service : Cannot stop service ‘SQL Server (MSSQLSERVER) (mssqlserver)’ because it has dependent services. It can only be stopped if the Force flag is set.

Now, here is the question to you – When I use -Force flag does it execute SHUTDOWN process internally or it does SHUTDOWN WITH NOWAIT?”

powershell shutdown SQL SERVER   Start Services or Stop Services with PowerShell   Question to Readers?

This is a very interesting question. I honestly have no answer to this question. From the syntax we can guess that it might be doing Shutdown with Nowait but again, guessing is not a good idea. In the past, there have been enough time I have been proven wrong when I have guessed anything. There is always a chance when what we see is not what happens internally.

Here is few additional information. When we execute SHUTDOWN command in T-SQL it waits for all the currently running Transact-SQL statements or stored procedures to finish. However, when we use syntax SHUTDOWN WITH NOWAIT it shuts down server immediately without even doing Checkpoint. This increases more work for the server when it restarts.

So here is the question again – Stop-Services ‘mysqlserver in powershell is equivalent to Shutdown or Shutdown With Nowait in T-SQL?

Reference: Pinal Dave (http://blog.sqlauthority.com)