The Multifunctioning DBA


July 17, 2009  2:56 PM

MR. Denny has a webcast

Colin Smith Colin Smith Profile: Colin Smith

I just saw this and I am looking forward to attending. MR. Denny is a great SQL Resource and I am sure that I will learn new things during this webcast. Here is a link to his blog entry about the webcast.

http://itknowledgeexchange.techtarget.com/sql-server/webcast-next-with-on-troubleshooting-the-sql-server-service/

I suggest you check it out and learn something new from a knowledgable SQL Server DBA.

July 14, 2009  3:53 PM

New Job

Colin Smith Colin Smith Profile: Colin Smith

Last Thursday my company did some layoffs and re-orginization. I survived the layoffs but my job has changed. I am no longer a Database Administrator for a major media company in Phoenix Arizona, but now I am a Database Analyst for a nation wide media company. I am still based in the Phoenix area and still use my same desk, computer and all that. I will still be doing Database Administration for the company in Phoenix as well, but my role is expanding. This company is a big MS Shop and I will be essentailly the only Administrator for all instances that this large enterprise has. I am very excited about this opportunity and look forward to the challenges ahead of me. I know that this will put my learning on the fast track and I am hoping to have my first test done in a month or two at the most. Well wish me luck and I am sure that I will have many new stories and experiances to share as I move forward on this new adventure.


July 13, 2009  5:31 PM

MSSQL Server Renaming Physical Host

Colin Smith Colin Smith Profile: Colin Smith

Recently I had a server that hosts a MSSQL 2005 SQL Server named instance and the Windows group decided that the name of the server had to be changed. I thought that this may cuase an issue since the MSSQL Server name is ‘hostname\instancename’. I did a bit of research and found that a solution is available for this problem. At first I thought that I may have to reinstall a new instance of SQL Server in order to get the name correct. I did not want to go thru all of that though.

I found that MS has a couple of Stored Procs that will help with this problem.

sp_dropserver ‘hostname\instancename’ –of the old server\instancename

sp_addserver ‘hostname\instancename’, ‘local’ –of the new server\instancename

I found that when I did a select @@servername I still got the old server name returned. I also found that when I did a sp_helpserver I got the new name. I decided I would restart the server to see if that would update the @@servername variable. I right clicked on the server and said restart. After it cam back up I got a ‘NULL’ from select @@servername. I did not understand why and really still do not. I was able to resolve the issue by going to the services.msc and restarting the SQL Server Service from there. Now I see the new server name from select @@servername as well as sp_helpserver.


June 30, 2009  8:13 PM

Select Folder Dialog Box

Colin Smith Colin Smith Profile: Colin Smith

If you would like your scripts to interact with the user and have a nice popup box for a user to select a folder here is a link to an article that will help you out. I think this is pretty great.

http://powershell.com/cs/blogs/tips/archive/2009/04/01/select-folder-dialog.aspx


June 30, 2009  3:38 PM

Reboot a Remote Windows Host Remotely

Colin Smith Colin Smith Profile: Colin Smith

Say you need to reboot a server and this is something that you would like to automate based on certain criteria. For instance, I might want to reboot a server if I can talk to the server but for somereason my SQL Server is not up and running. If everything is configured correctly the SQL Server should start up again on boot. So you can do something like the following.

$varname = Get-WmiObject Win32_OperatingSystem -computername “remotemachine”

$varname.reboot()

Now you can check your SQL Server once the machine is back up.


June 30, 2009  3:28 PM

Test a path in Powershell

Colin Smith Colin Smith Profile: Colin Smith

this is a simple thing but it can be very useful. I use this is many of my scripts. Sometimes I want to make sure that a file is available for me to append and if not then I would like to create a new one. Take a log file for example, if the file has been moved or deleted, perhaps it grew very large and needed to be removed, the script can just create a new one without running into any problems. If the file is still available though, then the script can just append the current file. here is an example:

if (!(test-path “H:\path\file.txt”))
{
New-Item -type File “H:\path\file.txt”
}

This will check if the file exists and if not then it will create it.


June 26, 2009  5:46 PM

Finding what account a Service is running as

Colin Smith Colin Smith Profile: Colin Smith

Yesterday my boss came to me asking if I knew what server an account was running on. I know that the account is used as a SQL Server service account because of the naming convention that was used. I also know that the account is being used as I used the Quest Software cmdlet get-qaduser to query AD for the account and get the last logon time. Now I just needed to find out what server it was running on. With the account naming conventions that we use this normally would not be an issue but in this case it was as the server that it should have been used on no longer exists. The server had been virtualized and now has a new name. So I put together a script that would identify the server for me. I had the script read in all of my servers from my sqltab file that I have talked about before, and list all of the sql server services and the accounts that they were running as for each server. Here is a nice little one liner that will get you a listing of all sql server services on a server and the account that they are running as. Hope you can get some use out of this.

Get-WmiObject Win32_service -computername $computername | select name, startname | Where-Object{$_.name -like “*SQL*”}


June 26, 2009  3:54 PM

Another Example of why you should learn Powershell

Colin Smith Colin Smith Profile: Colin Smith

I was roaming around the web and reading some articles and I ran across the following blog post:

http://bassplayerdoc.blogspot.com/2008/10/another-one-liner-in-windows-powershell.html

I think this is a great reason why all administrators that deal with Microsoft products should learn powershell. I know some Administrators are happy with VBScript and I know that many people use Pearl or other languages. I am all for knowing them and using them when they make the most sense. Some languages are better at some things then others. I think that Powershell is the Future of Microsoft Administration and this is a great example. Check out the other articles on this Blog as well. Some really good stuff.


June 26, 2009  3:11 PM

Shocking Death of an Icon

Colin Smith Colin Smith Profile: Colin Smith

Since I work for a news media outlet I thought that I would comment on this news. Michael Jackson’s untimely death. Everyone knows about Michael and all the controversy that has surrounded him for many years now. whether or not you are a fan, a hater, you believe he has been guilty or innocent of all the crimes that he was accused of, all must admit that he made is mark on the world though his music. He entertained us with music, lyrics, and dancing. He could move like nobody else and so many artists today try to imitate him in so many ways. I do not think that I have ever met anyone who has not liked at least one of his songs or videos. How can you not be a fan of Thriller? I remember being very young, 7 or 8, and I was at a rollerskating rink when the video made its premiere. All skating stopped and everyone sat down in front of a big screen and watched the video. Who else has had the world stop to pay attention like that? Not even all Presidents have had the world listen like that.

I am not going to voice my opinion about him as a man but as an entertainer he was one of the best. He has influenced so many in so many ways, and I do think he will be sorely missed. My thoughts go out to his friends and family and all I can say is “Do You Remember the Time”.


June 25, 2009  9:24 PM

PowerShell SQL Server Health Check Script

Colin Smith Colin Smith Profile: Colin Smith

OK so I think that I have everything I want in my script to check on the health of my SQL Servers. I will run this at least once a day and more if needed. I hope that this will help some people out and if you have any questions, comments, or ideas of what else might be good to include please let me know.

SQL Health Check Script:


#Create a new Excel object using COM $Excel = New-Object -ComObject Excel.Application $Excel.visible = $True #$Excel.visible = $False #$workbook = $excel.Workbooks.Open("D:\pac health checks.xlsx") $Excel = $Excel.Workbooks.Add() $Sheet = $Excel.Worksheets.Item(1) #Counter variable for rows $intRow = 2 #Read thru the contents of the SQL_Servers.txt file #$servers = Import-Csv "\\pni-vmdbasql\d$\monitoring\inputs\sqltab.txt" $servers = Import-Csv "\\ent-pocpacapcx01\d$\monitoring\sqltab.txt" ######################################################### foreach ($entry in $servers) { $torp = $entry.TorP $mon = $entry.monitor $machine = $entry.server $errorlog = $entry.errorlog $os = $entry.os2000 $iname = $entry.Instance if ($iname -eq "Null") { $instance = "$machine" } else { $instance = "$machine\$iname" } if ($torp -eq "Prod") { $ServerType = "Production" } else { $ServerType = "Test" } $instance = $instance.toupper() ######################################################### #foreach ($instance in get-content "D:\SQL_Servers.txt") #{ #Create column headers $Sheet.Cells.Item($intRow,1) = "INSTANCE NAME:" $Sheet.Cells.Item($intRow,2) = $instance ################################################ #This script gets SQL Server database information using PowerShell [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null # Create an SMO connection to the instance $s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance $dbs = $s.Databases #$dbs | SELECT Name, Collation, CompatibilityLevel, AutoShrink, RecoveryModel, Size, SpaceAvailable #Formatting using Excel $version = $s.VersionString if ($version -like "*9.00.1399*") { $sqlversion = "SQL Server 2005 No Service Pack" } elseif ($version -like "*9.00.2047*") { $sqlversion = "SQL Server 2005 SP1" } elseif ($version -like "*9.00.3042*") { $sqlversion = "SQL Server 2005 SP2" } elseif ($version -like "*9.00.3068*") { $sqlversion = "SQL Server 2005 SP2" } elseif ($version -like "*9.00.3054*") { $sqlversion = "SQL Server 2005 SP2 with Q934458" } elseif ($version -like "*9.00.3073*") { $sqlversion = "SQL Server 2005 SP2 with Q954606" } elseif ($version -like "*9.00.3186*") { $sqlversion = "SQL Server 2005 SP2 with Q939562" } elseif ($version -like "*9.00.4035*") { $sqlversion = "SQL Server 2005 SP3" } ## SQL Server 2000 Checks elseif ($version -like "*8.00.194*") { $sqlversion = "SQL Server 2000 No SP" } elseif ($version -like "*8.00.384*") { $sqlversion = "SQL Server 2000 SP1" } elseif ($version -like "*8.00.532*") { $sqlversion = "SQL Server 2000 SP2" } elseif ($version -like "*8.00.760*") { $sqlversion = "SQL Server 2000 SP3" } elseif ($version -like "*8.00.818*") { $sqlversion = "SQL Server 2000 SP3 with MS03-031" } elseif ($version -like "*8.00.2039*") { $sqlversion = "SQL Server 2000 SP4" } elseif ($version -like "*8.00.2040*") { $sqlversion = "SQL Server 2000 NoSP with Q274329" } elseif ($version -like "*8.00.2050*") { $sqlversion = "SQL Server 2000 SP4 with MS08-040" } elseif ($version -like "*8.00.2187*") { $sqlversion = "SQL Server 2000 SP1 with Q923849" } elseif ($version -like "*8.00.2282*") { $sqlversion = "SQL Server 2000 QFE" } elseif ($version -like "*8.00.3073*") { $sqlversion = "SQL Server 2000 SP2 with Q954606" } #SQL Server 7 elseif ($version -like "*7.00.1094*") { $sqlversion = "SQL Server 7 SP4 with Q815495, Q821279" } #SQL Server 2008 elseif ($version -like "*10.0.1442.32*") { $sqlversion = "SQL Server 2008 X64 MSDN Beta" } else { $sqlversion = "Check me $version" } $Sheet.Cells.Item($intRow,3) = "Version:" $Sheet.Cells.Item($intRow,4) = $sqlversion $Sheet.Cells.Item($intRow,5) = $ServerType if ($Servertype -eq "Production") { $fgColor = 35 } else { $fgColor = 0 } $Sheet.Cells.item($intRow, 5).Interior.ColorIndex = $fgColor ########################################################### $Sheet.Cells.Item($intRow,1).Font.Bold = $True $Sheet.Cells.Item($intRow,2).Font.Bold = $True $Sheet.Cells.Item($intRow,3).Font.Bold = $True $Sheet.Cells.Item($intRow,4).Font.Bold = $True $Sheet.Cells.Item($intRow,5).Font.Bold = $True ############################################## ## Are full and tranlog backups running? ## DB Reindexing running? ############################################## $intRow ++ $jobsserver = $s.JobServer $jobs = $jobsserver.Jobs $ijob = 0 $backjob = 0 $tranjob = 0 foreach ($job in $jobs) { if (($job.name -like "*backup*") -and ($job.name -notlike "*Tranlog*")) { $backjob = 1 $fullbackup = $job.name $fullbackupstatus = $job.LastRunOutcome $fullbackupdate = $job.LastRunDate } if ($job.Name -like "*tranlog*") { $tranjob = 1 $tranlogbackup = $job.name $tranlogbackupstatus = $job.LastRunOutcome $tranlogbackupdate = $job.LastRunDate } if ($job.Name -like "*index*") { $ijob = 1 $rebuildindex = $job.Name $rebuildindexstatus = $job.LastRunOutcome $rebuildindexdate = $job.LastRunDate } } ## Check to verify full backups are running ## $dayago = [datetime]::Now.AddDays(-1) if (($fullbackupstatus -eq "Succeeded") -and ($fullbackupdate -gt $dayago)) { $fullbackupjob = "Full Backups Running" } elseif(($fullbackupstatus -ne "succeeded") -or ($fullbackupdate -lt $dayago)) { $fullbackupjob = "Full Backups Failing" } if ($backjob -eq 0) { $fullbackupjob = "No Full Backup Job" } ## Check to verify that Tranlogs backups are running fine ## $hourago = [datetime]::Now.AddHours(-1) if (($tranlogbackupstatus -eq "Succeeded") -and ($tranlogbackupdate -gt $hourago)) { $tranbackupjob = "Tran Backup Running" } if(($tranlogbackupstatus -ne "succeeded") -or ($tranlogbackupdate -lt $hourago)) { $tranbackupjob = "Tran BackupFailing" } if ($tranjob -eq 0) { $tranbackupjob = "No Tran Backup Job" } $weekago = [datetime]::Now.AddDays(-7) if (($rebuildindexstatus -eq "Succeeded") -and ($rebuildindexdate -gt $weekago)) { $indexjob = "Indexing Running" } elseif(($rebuildindexstatus -ne "succeeded") -or ($rebuildindexdate -lt $weekago)) { $indexjob = "Indexing Failing" } if ($ijob -eq 0) { $indexjob = "No Indexing Job" } $Sheet.Cells.Item($intRow,1) = $fullbackupjob $Sheet.Cells.Item($intRow,2) = $tranbackupjob $Sheet.Cells.Item($intRow,3) = $indexjob $Sheet.Cells.Item($intRow,1).Font.Bold = $True $Sheet.Cells.Item($intRow,2).Font.Bold = $True $Sheet.Cells.Item($intRow,3).Font.Bold = $True ############################################## $intRow++ $Sheet.Cells.Item($intRow,1) = "DATABASE NAME" $Sheet.Cells.Item($intRow,2) = "RECOVERY MODEL" $Sheet.Cells.Item($intRow,3) = "SIZE (MB)" $Sheet.Cells.Item($intRow,4) = "SPACE AVAILABLE (MB)" $Sheet.Cells.Item($intRow,5) = "DATA DRIVE" $Sheet.Cells.Item($intRow,6) = "SPACE AVAILABLE ON DISK (GB)" $Sheet.Cells.Item($intRow,7) = "MIRROR STATUS" $Sheet.Cells.Item($intRow,8) = "LOG SIZE (MB)" #Format the column headers for ($col = 1; $col –le 8; $col++) { $Sheet.Cells.Item($intRow,$col).Font.Bold = $True $Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48 $Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34 } $intRow++ ####################################################### # #This script gets SQL Server database information using PowerShell # # [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null # # # Create an SMO connection to the instance # $s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance # # $dbs = $s.Databases # # #$dbs | SELECT Name, Collation, CompatibilityLevel, AutoShrink, RecoveryModel, Size, SpaceAvailable # # #Formatting using Excel foreach ($db in $dbs) { $name = $db.name $model = $db.recoverymodel if ($model -eq 1) { $modelname = "Full" } elseif ($model -eq 2) { $modelname = "Bulk Logged" } elseif ($model -eq 3) { $modelname = "Simple" } $logfiles = $db.LogFiles foreach ($log in $logfiles) { $logsize = $log.size/1KB $logsize = [math]::Round($logsize, 2) } #if(($name -ne "master") -and ($name -ne "model")) # -and ($name -ne "msdb")) #{ #Divide the value of SpaceAvailable by 1KB $dbSpaceAvailable = $db.SpaceAvailable/1KB #Format the results to a number with three decimal places $dbSpaceAvailable = "{0:N3}" -f $dbSpaceAvailable $Sheet.Cells.Item($intRow, 1) = $db.Name $Sheet.Cells.Item($intRow, 2) = $modelname $Sheet.Cells.Item($intRow, 3) = "{0:N3}" -f $db.Size #Change the background color of the Cell depending on the SpaceAvailable property value if ($dbSpaceAvailable -eq 0.00) { $fgColor = 38 } else { $fgColor = 0 } $Sheet.Cells.Item($intRow, 4) = $dbSpaceAvailable $Sheet.Cells.item($intRow, 4).Interior.ColorIndex = $fgColor $dblocation = $db.primaryfilepath $dblocation = $dblocation.split(":") $dbdrive = $dblocation[0] $drives = Get-WmiObject -ComputerName $machine Win32_LogicalDisk | Where-Object {$_.DriveType -eq 3} foreach($drive in $drives) { $size1 = $drive.size / 1GB $size = "{0:N2}" -f $size1 $free1 = $drive.freespace / 1GB $free = "{0:N2}" -f $free1 $ID = $drive.DeviceID $a = $free1 / $size1 * 100 $b = "{0:N2}" -f $a if ($dbdrive -eq "C") { $fgColor = 38 } else { $fgColor = 0 } $Sheet.Cells.Item($intRow,5) = $dbdrive $Sheet.Cells.item($intRow, 5).Interior.ColorIndex = $fgColor if ($id -like "$dbdrive*") { if ($free1 -lt 5) { $fgColor = 38 } else { $fgColor = 0 } if (($ID -eq "C:") -and ($free1 -lt 1)) { $fgColor = 38 } $Sheet.Cells.Item($intRow,6) = $free1 $Sheet.Cells.item($intRow, 6).Interior.ColorIndex = $fgColor } } if($version -like "*2000*") {$mirrorstate = 0} else { $mirrorstate = $db.MirroringStatus } if ($mirrorstate -eq 0) { $mirror = "No Mirror" } if ($mirrorstate -eq 1) {$mirror = "Suspended" } if($mirrorstate -eq 5) { $mirror = "Synchronized" } if ($mirrorstate -eq 1) { $fgcolor = 38 } else { $fgcolor = 0 } $Sheet.Cells.Item($intRow,7) = $mirror $Sheet.Cells.item($intRow, 7).Interior.ColorIndex = $fgColor if ($logsize -gt 500) { $fgColor = 38 } else { $fgColor = 0 } $Sheet.Cells.Item($intRow,8) = $logsize $Sheet.Cells.item($intRow, 8).Interior.ColorIndex = $fgColor $intRow ++ #} } $intRow ++ } $Sheet.UsedRange.EntireColumn.AutoFit() cls $workbook.saveas("D:\pac health checks.xlsx") #$excel.Quit() $WorkBook = $Null $WorkSheet = $Null $Excel = $Null [GC]::Collect()

Here is a sample of what the report will look like when you have run it.

Report Output Sample

Report Output Sample


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: