The Multifunctioning DBA


October 30, 2011  10:00 AM

PowerShell SQL Server Health Check Script – Revisited



Posted by: Colin Smith
Powershell, SQL Server

In the past couple months I have received a few comments and questions about my SQL Server Health Check Script. So I thought it might be a good time to revisit the script and clear some things up and give some other options as well. This will be done in a series of posts so that we can dive more deeply into the script.

This script is still good and does still work if you set it up properly, but the script was written early in my Powershell career and i have made a couple modifications over time that may help you out and I can clear up some of the confusion about the sqltab.txt file.

Lets start off by attempting to clear up some of the confusion about the sqltab.txt file. This is the file that is used as an input for the script. The script reads this file in, using the import-csv cmdlet, and the script is able to set up all the variables that you need for the input. This file must be comma delimited and can store anything you want in it. So mine looked like the following:

monitor,Server,Instance,TorP,ErrorLog,Ping

Those were the headers in my file but you can have any headers that you need to make the script work for you the way you want it. I used this file for many scripts but for just the health check you really only need the instancename. In my script, since I have named and default instances, I broke that into ‘Server’ and ‘Instance’. This allowed me have the script determine the instance name. The other headers are up to you, just use headers that you think you might need and you can always add or subtract things from this file.

As for the location of the file, it really does not matter. Some people were confused about the location I had mine.

$servers = Import-Csv “\\ent-pocpacapcx01d$\monitoring\sqltab.txt”

I just had the script getting to the file with a UNC and that was for portability. I could run the script from anywhere on my network and it would find that one file and run. The location, name, and contents of the file are up to you. You may have to modify the script a bit if you modify the file headers and content and you will have to change the location of the file for sure. That is easy to do though.

Now that we have talked about the input file and the things that you can do to change the input file lets go over where in the script you will have to make changes if you make changes to the input file.

You have to change the location of the input file and that is done with the following line.

$servers = Import-Csv “\\ent-pocpacapcx01d$\monitoring\sqltab.txt”

Just make this where you put the file and whatever name you gave your file. In powerwhell if you run the following you will be able to verify that you are able to read the file in and that the values are being set up the way you need them.

$servers = import-csv “yourfilepath\yourfilename.txt”

$servers

Then in the console window you should see all your servers being listed out on the screen with whatever headers you put in the file. Like this:

PS C:\Documents and Settings\cosmith> $servers = import-csv “c:\input\sqltab.txt”
PS C:\Documents and Settings\cosmith> $servers

server                           instance                         TORP                           monitor
——                        ——–                      —-                          ——-
server1                         tst                                 Test                                 Y
Server2                         PRD                               Prod                                 Y
Server3                         Default                         Prod                                  N

Now that you have verified that the script is going to be able to read the file in correctly, you will need to modify the script to set up the variables so that they are easy to use.

So that is all about the input file and how to use the input file. More on this in a few days.

Hope that this helps.





October 27, 2011  1:18 PM

SQL Monitoring with Ignite



Posted by: Colin Smith
Montoring, Tools

Over the past few weeks, we have been using the Ignite Monitoring Tool to monitor some of our SQL Server instances and we have been pleased with our findings. This tool gives us the ability to look and see what is currently going on with the SQL Server instance and respond quickly to any issues that we may be having. Not only can we more quickly respond to current issues, but this tool also gives us the ability to look into the past and do troubleshooting on issues that may have occurred when no one was looking.

Some of the things that this tool brings us that we find useful are:

  • Historical tracking of SQL Statements that were executed against a database.
  • Tracking of the waits that occurred while the SQL Statements were executing and the ability to tie those waits directly to the SQL that caused them.
  • Blocking monitoring and historical tracking.
  • Plan capturing and comparing.
  • The ability to tie a SQL event or events to resource bottlenecks.
  • The ability to send the report that we see via email to application teams so they can see where they may have opportunity to tune queries.
  • This tool centralizes the ability to do many things that we currently either do not have the ability to do at all or we have to use multiple tools to do all that this one tool does.
  • The ability to set up custom alerts on almost any condition that we would like to be notified on.
  • The user Interface that is designed to quickly lead you to the most problematic queries.
  • The < 1% load that monitoring brings to the instance.
    and we have not even really started on the Oracle side yet but for SQL Server I really like it. I think Confio has done a really good job keeping this product light on the production databases and they are holding a good history of events and the retention and rollup of those events are all configurable so your datastore of this data can be what you want it to be.

    Hope this helps.


October 19, 2011  1:16 PM

Powershell Disable db File Autogrow



Posted by: Colin Smith
autogrow, file managemnt, Powershell, SMO, SQL Server
In order to Disable autogrow on your database files it is a bit tricky. One would think that in SMO you would have an autogrow flag that could be turned on and off like there is in the GUI. Well this is not the case. So if you take my script from http://itknowledgeexchange.techtarget.com/dba/powershell-script-to-change-data-and-log-files-that-are-growing-by-percent/
and make a little bit of a change then the autogrow will be turned off.
Just set the Growth to 0 and most important is to change the growthtype to none:
$file.growth = 0
$file.growthtype = “none”
$file.alter()
That is it and then when you look in the GUI autogrow will be off.


October 18, 2011  1:33 PM

Klok



Posted by: Colin Smith
Project tracking, time tracking
Do you need to do time tracking? I do, my new job requires that I track how many hours I am spending on project work by project vs break fix issues. This is all new to me and I have been struggling to find a way to do this that is not too much overhead. So I started looking around and found Klok. This is a fantastic application that lets you do just this. So go to http://www.getklok.com/ and get it now. I am using the free version and that is going to work for me but check out the feature list and if an upgrade helps you, it is pretty cheap.
Hope that helps.


October 14, 2011  8:25 AM

Powershell Script to change data and log files that are growing by percent



Posted by: Colin Smith
Powershell, SMO, SQL Server
This is the next step after finding all the files that are growing by percent and not by a fixed value. Below is the modified script to make the changes that I needed.

#### Check Log and Data Growth for all User Databases. If they are not set up correctly then change them to the correct setting.
$servers = invoke-sqlcmd -serverinstance server -database Database -query “select InstanceName, Port from instance where IsActive = 1″
$output = @()
foreach($instance in $servers)
{
$name = $instance.instancename
$port = $instance.port
$server = “$name,$port”
#echo “`n###########################

######”
#$server

$s = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) $server

$databases = $s.databases
#$databases | get-member | where {$_.membertype -eq “Property”}
foreach($database in $databases)
{
$dbsize = $database.size
$name = $database.name
## Check the log File
$logfiles = $database.logfiles
foreach($logfile in $logfiles)
{
$filename = $logfile.filename
$growth = $logfile.growth
$growthtype = $logfile.growthtype
$size = $logfile.size
if(($growthtype -eq “Percent”) -and ($name -ne “Master”) -and ($name -ne “Model”))
{
$logfile.growth = 65536
$logfile.growthtype = “kb”
$logfile.alter()

}
}
## Datafiles
$filegroups = $database.filegroups
foreach($group in $filegroups)
{
$files = $group.files
foreach($file in $files)
{
$filename = $file.filename
$type = $file.growthtype
$growth = $file.growth
$size = $file.size
if(($type -eq “Percent”) -and ($name -ne “Master”) -and ($name -ne “Model”))
{
if($dbsize -lt 10240)
{
$file.growth = 51200
$file.growthtype = “kb”
$file.alter()
}

if(($dbsize -gt 20480) -and ($dbsize -lt 51200))
{
$file.growth = 307200
$file.growthtype = “kb”
$file.alter()
}

if(($dbsize -gt 51200) -and ($dbsize -lt 102400))
{
$file.growth = 512000
$file.growthtype = “kb”
$file.alter()
}

if($dbsize -gt 102400)
{
$file.growth = 1048576
$file.growthtype = “kb”
$file.alter()
}

}
}
}
}
}

##############################################################################
So you can see that this script is a bit more code, but I was able to make all the changes that I needed to all the log and all the data files and I was able to have the datafile growth depend on the current size of the database.
Hope this will help.


October 13, 2011  2:24 AM

Denali Gets a Name



Posted by: Colin Smith
Denali, PASS, SQL Server
During the Keynote at the Pass Session today. Ted Kummert of MS made the announcement that SQL Server Codename Delai is actually going to be called SQL Server 2012. Not very exciting but he went on to say that we would have a full RTM release in the first half of the 2012 calendar year. That is more exciting and that means that I need to get CTP3 up and running somewhere and start playing with it a bit. I am really looking foreward to this release. Some things that i am excited about and I am sure you all are as well are.
Always On – this will give us the ability to do some great things with Mirroring type technology and have multipe read only copies of databases. This is a huge improvement over the current mirroiring in 2005 and 2008.
Powershell – looks like we are getting a pretty good number of new cmd-lets that we will be able to use to automate even more tasks and ease administration. If you do not know Powershell yet now is the time. Go learn it, you will not regret taking the short amount of time it will take.


October 12, 2011  2:34 PM

Happy Anniversary



Posted by: Colin Smith
Anniversary, Wife

Today is my 14th wedding anniversary. I thought I would throw up a little note to my wife and say,

I Love you very much and I would not trade the last 14 years for anything in the world. And now that we have a baby coming to us I am very excited to start this next chapter in our lives together. Thanks for always being there for me and understanding all the late nights that i have to work due to upgrades or calls in the middle of the night. I know that you hate my cell phone but you put up with it and I appreciate that. Thanks for understanding when I go out of town for training or other events for a week at a time. None of this goes un noticed or unappreciated.

Simply, I love you and I can not wait to spend the rest of my life with you still.


October 12, 2011  1:56 PM

Powershell Script to check if data and log files are growing by percent



Posted by: Colin Smith
Data Files, Growth, Log Files, Powershell
In my new shop we try to grow our data and log files by a specific amount and not by a percentage. Our team lead ran into a database that had not been set up properly and wanted someone to check all our databases to verify that they have been set up correctly. Since I am the new guy and still eager to impress I said I would take that task. So it took me about 30 minutes to complete the task but now it will only take me a few seconds to execute a powershell script.This script grabs a list of all our SQL Servers from a table in a database that we have the tracks this information. So the script will get the list and then loop through them all and report on the databases that have data and or log files set to grow by percent. here is the code.

#### Check Log and Data Growth for all User Databases. If they are not set up correctly then change them to the correct setting.
$servers = invoke-sqlcmd -serverinstance server -database db -query “select InstanceName, Port from instance where IsActive = 1″
$output = @()
foreach($instance in $servers)
{
$name = $instance.instancename
$port = $instance.port
$server = “$name,$port”
#echo “`n###########################

######”
#$server

$s = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) $server

$databases = $s.databases
#$databases | get-member | where {$_.membertype -eq “Property”}
foreach($database in $databases)
{
$name = $database.name
## Check the log File
$logfiles = $database.logfiles
foreach($logfile in $logfiles)
{
$filename = $logfile.filename
$growth = $logfile.growth
$growthtype = $logfile.growthtype
if(($growthtype -eq “Percent”) -and ($name -ne “Master”) -and ($name -ne “Model”))
{
$Object = New-Object PSObject
$Object | add-member Noteproperty Database $name
$Object | add-member Noteproperty Growth $growth
$Object | add-member Noteproperty GrowthType $growthtype
$Object | add-member Noteproperty File $Filename
$Object | add-member Noteproperty Instnace $server

$output = $output + $object
#echo “$name $growth $growthtype $filename”
}
}
## Datafiles
$filegroups = $database.filegroups
foreach($group in $filegroups)
{
$files = $group.files
foreach($file in $files)
{
$filename = $file.filename
$type = $file.growthtype
$growth = $file.growth
if(($type -eq “Percent”) -and ($name -ne “Master”) -and ($name -ne “Model”))
{

$Object = New-Object PSObject

$Object | add-member Noteproperty Database $name
$Object | add-member Noteproperty Growth $growth
$Object | add-member Noteproperty GrowthType $type
$Object | add-member Noteproperty File $Filename
$Object | add-member Noteproperty Instnace $server
$output = $output + $object
}
}
}
}
}
$output | Out-GridView
$output.count
$output | Export-Csv c:\out.csv
##############################################################################
So you can see that n about 65 lines of code I was able to get a list of all of the databases that are configured incorrectly and it also reports on what file it is that is not set up correctly.
Hope this will help.


October 10, 2011  5:35 AM

PASS 2011



Posted by: Colin Smith
PASS, PASS Summit, Summit

Tomorrow is the beginning of PASS. I wish I was able to attend this year again but I think that my time and money was better spent at the SQL Skills immersion event that I attended. I know that I will be missing out on some great content, networking, and parties.

As much as I would like to be at PASS this year I am actually OK with not being there since I am only in my fourth week at my new job and I think I am really going to like this job. I think that it is going to turn into everything that I thought and hoped it would be. But I digress, I will try to follow as much about PASS as I can but not too sure how much that will actually be. I am looking forward to reading blogs about the event and the keynotes as well as the sessions.  I am thinking about ordering the DVDs this year. I did last yer even though I was at the event and I have been very glad that I have them. So even if you are at the summit I would highly recommend that you get them.

If it is your first time at the Summit then I have this advice for you. Network and find the parties. You will have a good time and you will meet some great people that are having or have had the same challenges that you are facing right now. they will be able to help you with those issues even if that is just letting you talk them thru the issue. Sometimes just that dialog helps you figure it out and that is just awesome when that happens.

So go out and have a great time and learn as much as you can. Get the DVDs and meet as many people as you can. You will not regret it and I hope to see you next year, fingers crossed, if I get to go.


October 6, 2011  1:10 PM

The Passing of Steve Jobs



Posted by: Colin Smith
Steve Jobs

Yesterday Steve Jobs left this world at only 56. It is a sad time in the tech community, and the world at large.. I was never a big Apple fan, the only Apple device in my house is an Ipod Touch and that is only because my wife wanted it. Even not being a fan of Apple I recognize all that Steve Jobs has done for this world. Not only with Apple but also with Pixar Animation Studios. it is amazing to me that a man of only 56 was able to really impact the world in such a positive and huge way. He really changed the way people live their lives. Many people would not know what to do without the Ipad or Iphone these days, and even if you are an android person like me, who do you think helped shape what a smartphone and a tablet should be? He really helped shape the age of personal computing and took it to another level and always strived to make the user experience of Apple products an amazing experience. He was very successful in doing so. If you go ask anyone that has an Apple product and ask them why they like it, odds are they will say “It just works”. That really is an amazing feat these days with how fast technology changes. For Apple to show restraint and maybe not be at the cutting edge, Iphone with no 4G as an example, has to be tough in this market. But they have done so and they have done amazing while doing so. this is because of the overall experience is that “it just works”.

Steve was an amazing man working as CEO up to just a few weeks before his death, he was a visionary and pioneer in anything that he set out to do. He was a man that left this world a better place then he found it. That is hard to do. Think about that, one man having a HUGE impact on so many around the world. Not many can say they accomplished anything like that. That does not mean that we should not all strive, like Steve would, to do our part and make our worlds better for us and the people we care about. If everyone did that then the world truly would be a better place. I think the best way to honor the memory of Steve Jobs is to find something that you love and are passionate about, and be the best at it. if you do that you can make the world a better place.

With that I will close and say, Rest in Peace Steve Jobs, and thank you for all that you have done in the very short time you were here with us all.


Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: