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

Earlier 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)

About these ads

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?”

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)

SQL SERVER – Powershell – Importing CSV File Into Database – Video

Laerte Junior is my very dear friend and Powershell Expert. On my request he has agreed to share Powershell knowledge with us. Laerte Junior is a SQL Server MVP and, through his technology blog and simple-talk articles, an active member of the Microsoft community in Brasil. He is a skilled Principal Database Architect, Developer, and Administrator, specializing in SQL Server and Powershell Programming with over 8 years of hands-on experience. He holds a degree in Computer Science, has been awarded a number of certifications (including MCDBA), and is an expert in SQL Server 2000 / SQL Server 2005 / SQL Server 2008 technologies. Let us read the blog post in his own words.


I was reading an excellent post from my great friend Pinal about loading data from CSV files, SQL SERVER – Importing CSV File Into Database – SQL in Sixty Seconds #018 – Video,   to SQL Server and was honored to write another guest post on SQL Authority about the magic of the PowerShell.

The biggest stuff in TechEd NA this year was PowerShell. Fellows, if you still don’t know about it, it is better to run. Remember that The Core Servers to SQL Server are the future and consequently the Shell. You don’t want to be out of this, right?

Let’s see some PowerShell Magic now. To start our tour, first we need to download these two functions from Powershell and SQL Server Master Jedi Chad Miller.Out-DataTable and Write-DataTable.

Save it in a module and add it in your profile. In my case, the module is called functions.psm1.

To have some data to play, I created 10 csv files with the same content. I just put the SQL Server Errorlog into a csv file and created 10 copies of it.

#Just create a CSV with data to Import. Using SQLErrorLog

[reflection.assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”)

$ServerInstance=new-object (Microsoft.SqlServer.Management.Smo.Server) $Env:Computername

$ServerInstance.ReadErrorLog() | export-csv-path“c:\SQLAuthority\ErrorLog.csv”-NoTypeInformation

for($Count=1;$Count-le 10;$count++)  {

      Copy-Item“c:\SQLAuthority\Errorlog.csv”“c:\SQLAuthority\ErrorLog$($count).csv”

}

Now in my path c:\sqlauthority, I have 10 csv files :

Now it is time to create a table. In my case, the SQL Server is called R2D2 and the Database is SQLServerRepository and the table is CSV_SQLAuthority.

CREATE TABLE [dbo].[CSV_SQLAuthority](
[LogDate] [datetime] NULL,
[Processinfo] [varchar](20) NULL,
[Text] [varchar](MAX) NULL
)

Let’s play a little bit.

I want to import synchronously all csv files from the path to the table:

#Importing synchronously

$DataImport=Import-Csv-Path ( Get-ChildItem“c:\SQLAuthority\*.csv”)

$DataTable=Out-DataTable-InputObject$DataImport

Write-DataTable-ServerInstanceR2D2-DatabaseSQLServerRepository-TableNameCSV_SQLAuthority-Data$DataTable

Very cool, right? Let’s do it asynchronously and in background using PowerShell  Jobs:

#If you want to do it to all asynchronously

Start-job-NameImportingAsynchronously `

-InitializationScript  {IpmoFunctions-Force-DisableNameChecking} `

-ScriptBlock {    `

$DataImport=Import-Csv-Path ( Get-ChildItem“c:\SQLAuthority\*.csv”)

$DataTable=Out-DataTable-InputObject$DataImport

Write-DataTable   -ServerInstance“R2D2″`

                  -DatabaseSQLServerRepository`

                  -TableNameCSV_SQLAuthority`

                  -Data$DataTable

            }

Oh, but if I have csv files that are large in size and I want to import each one asynchronously. In this case, this is what should be done:

Get-ChildItem“c:\SQLAuthority\*.csv” | % {

Start-job-Name“$($_)” `

-InitializationScript  {IpmoFunctions-Force-DisableNameChecking} `

-ScriptBlock { $DataImport=Import-Csv-Path$args[0]

               $DataTable=Out-DataTable-InputObject$DataImport

               Write-DataTable-ServerInstance“R2D2″`

                              -DatabaseSQLServerRepository`

                              -TableNameCSV_SQLAuthority`

                              -Data$DataTable

            } -ArgumentList$_.fullname

}

How cool is that?

Let’s make the funny stuff now. Let’s schedule it on an SQL Server Agent Job.

If you are using SQL Server 2012, you can use the PowerShell Job Step. Otherwise you need to use a CMDexec job step calling PowerShell.exe. We will use the second option.

First, create a ps1 file called ImportCSV.ps1 with the script above and save it in a path. In my case, it is in c:\temp\automation. Just add the line at the end:

Get-ChildItem“c:\SQLAuthority\*.csv” | % {

Start-job-Name“$($_)” `

-InitializationScript  {IpmoFunctions-Force-DisableNameChecking} `

-ScriptBlock { $DataImport=Import-Csv-Path$args[0]

               $DataTable=Out-DataTable-InputObject$DataImport

               Write-DataTable-ServerInstance“R2D2″`

                              -DatabaseSQLServerRepository`

                              -TableNameCSV_SQLAuthority`

                              -Data$DataTable

            } -ArgumentList$_.fullname

}

Get-Job | Wait-Job | Out-Null

Remove-Job -State Completed

Why? See my post Dooh PowerShell Trick–Running Scripts That has Posh Jobs on a SQL Agent Job

Remember, this trick is for  ALL scripts that will use PowerShell Jobs and any kind of schedule tool (SQL Server agent, Windows Schedule)

Create a Job Called ImportCSV and a step called Step_ImportCSV and choose CMDexec.

Then you just need to schedule or run it.

I did a short video (with matching good background music) and you can see it at:

That’s it guys. C’mon, join me in the #PowerShellLifeStyle. You will love it. If you want to check what we can do with PowerShell and SQL Server, don’t miss Laerte Junior LiveMeeting on July 18. You can have more information in : LiveMeeting VC PowerShell PASS–Troubleshooting SQL Server With PowerShell–English

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

SQL SERVER – Fix: Error: File cannot be loaded because the execution of scripts is disabled on this system. Please see “get-help about_signing” for more details

Yesterday I formatted my computer and did fresh install as it was due from long time. After the fresh install when I tried to install Semantic Search application using powershell, I was stopped by following error.

File cannot be loaded because the execution of scripts is disabled on this system. Please see “get-help about_signing” for more details

Fix/Solution/Workaround:

The solution is very simple. Open the Powershell window and type following two lines and everything will fine right after that.

Set-ExecutionPolicy Unrestricted
Set-ExecutionPolicy RemoteSigned

Again, this is I have done for my environment where I am very careful what I will run. You can change the policy back to original restricted policy if you want to restrict future execution of the powershell scripts.

Simple – isn’t it? Well all complex looking problems are very simple to solve.

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

SQL SERVER – Powershell – Get a List of Fixed Hard Drive and Free Space on Server

Earlier I have written this article SQL SERVER – Get a List of Fixed Hard Drive and Free Space on Server. I recently received excellent comment by MVP Ravikanth. He demonstrated that how the same can be done using Powershell. It is very sweet and quick solution.

Here is the powershell script. Run the same in your powershell windows.

Get-WmiObject -Class Win32_LogicalDisk | Select -Property DeviceID, @{Name=’FreeSpaceMB’;Expression={$_.FreeSpace/1MB} } | Format-Table -AutoSize

Well, I ran this script in my powershell window, it gave me following result – very accurately and easily.

Get-WmiObject -Class Win32_LogicalDisk | Select -Property DeviceID, @{Name=’FreeSpaceMB’;Expression={$_.FreeSpace/1MB} } | Format-Table -AutoSize

Thanks Ravikanth one more time for excellent tip.

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

SQL SERVER – PowerShell Version Info

I have multiple computer systems at home. I have previously taken a picture of my home office and published it here. Also, I recently had a scenario where I was listing a PowerShell version installed in my computer systems. While searching online, I found two different commands that can determine the version of PowerShell. One of them worked fine in Version 1, while both worked on Version 2.
The commands are:

$PSVersionTable

and

$host

I have run both the commands on different PowerShell versions and found the following output. This is a call to all PowerShell experts to help me out by letting me know the reasons why these became the results. I am sure that I’m missing something very small, so I ask your help to  clarify this.

PowerShell Version 1

PowerShell Version 2

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

SQLAuthority News – Guest Post – Performance Counters Gathering using Powershell

Laerte Junior
Laerte Junior

Laerte Junior has previously helped me personally to resolve the issue with Powershell installation on my computer. He did awesome job to help. He has send this another wonderful article regarding performance counter for readers of this blog. I really liked it and I expect all of you who are Powershell geeks, you will like the same as well.

As a good DBA, you know that our social life is restricted to a few movies over the year and, when possible, a pizza in a restaurant next to your company’s place, of course.

So what we have to do is to create methods through which we can facilitate our daily processes to go home early, and eventually have a nice time with our family (and not sleeping on the couch).

As a consultant or fixed employee, one of our daily tasks is to monitor performance counters using Perfmom. To be honest, IDE is getting more complicated. To deal with this, I thought a solution using Powershell. Yes, with some lines of Powershell, you can configure which counters to use. And with one more line, you can already start collecting data. Let’s see one scenario:

You are a consultant who has several clients and has just closed another project in troubleshooting an SQL Server environment.

You are to use Perfmom to collect data from the server and you already have its XML configuration files made with the counters that you will be using- a file for memory bottleneck f, one for CPU, etc.

With one Powershell command line for each XML file, you start collecting. The output of such a TXT file collection is set to up in an SQL Server. With two lines of command for each XML, you make the whole process of data collection.

Creating an XML configuration File to Memory Counters:

Get-PerfCounterCategory -CategoryName "Memory" | Get-PerfCounterInstance  | Get-PerfCounterCounters |Save-ConfigPerfCounter -PathConfigFile "c:\temp\ConfigfileMemory.xml" -newfile

Creating an XML Configuration File to Buffer Manager, counters Page lookups/sec, Page reads/sec, Page writes/sec, Page life expectancy:

Get-PerfCounterCategory -CategoryName "SQLServer:Buffer Manager" | Get-PerfCounterInstance | Get-PerfCounterCounters -CounterName "Page*" | Save-ConfigPerfCounter -PathConfigFile "c:\temp\BufferManager.xml" –NewFile

Then you start the collection:

Set-CollectPerfCounter -DateTimeStart "05/24/2010 08:00:00" -DateTimeEnd "05/24/2010 22:00:00" -Interval 10 -PathConfigFile c:\temp\ConfigfileMemory.xml -PathOutputFile c:\temp\ConfigfileMemory.txt

To let the Buffer Manager collect, you need one more counters, including the Buffer cache hit ratio.

Just add a new counter to BufferManager.xml, omitting the new file parameter

Get-PerfCounterCategory -CategoryName "SQLServer:Buffer Manager" | Get-PerfCounterInstance | Get-PerfCounterCounters -CounterName "Buffer cache hit ratio" | Save-ConfigPerfCounter -PathConfigFile "c:\temp\BufferManager.xml"

And start the collection:

Set-CollectPerfCounter -DateTimeStart "05/24/2010 08:00:00" -DateTimeEnd "05/24/2010 22:00:00" -Interval 10 -PathConfigFile c:\temp\BufferManager.xml -PathOutputFile c:\temp\BufferManager.txt

You do not know which counters are in the Category Buffer Manager? Simple!

Get-PerfCounterCategory -CategoryName "SQLServer:Buffer Manager" | Get-PerfCounterInstance | Get-PerfCounterCounters

Let’s see one output file as shown below. It is ready to bulk insert into the SQL Server.

As you can see, Powershell makes this process incredibly easy and fast. Do you want to see more examples? Visit my blog at Shell Your Experience

You can find more about Laerte Junior over here:

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