Archive for the ‘PowerShell’ Category

I decided to start monitoring the amount of free space in my database files so that I can make sure all my databases are set to auto grow by the right amount and to monitor the amount of free space so I can schedule the expansion of the files rather than let them auto grow.  I still plan to allow the files to auto grow if needed but by properly monitoring the size this will become the exception and not the norm.

I am certain there are numerous tools out there that will handle this for me but today I don’t have those tools.  What I do have is SSMS, Powershell, and a bunch of servers.  I put together a simple query that uses sp_MSforeachdb to gather the information I needed from the entire instance.  For now I am just gathering the Instance Name, database name, fileid, auto grow size in mb, file size in mb, space used in mb, free space in mb, percent free in mb, file name, file path, and the current time.

Running this query against each instance was easy enough but I have over 100 instances.  I don’t want to schedule a job on each server to run this and I haven’t implemented Central Management Server yet so I turned to Powershell to make this happen.  I recalled a conversation I had recently with a good friend Jon Boulineau (blog) where he was collecting database backup information from a list of servers, so I pinged him for advise.  While having an IM chat one night with Jon I was telling him what I was looking to do and within a few minutes Jon sent me a Posh script to model mine after.

Jon is one of the best developers that I know and the script he sent me was very well laid out and already had just about everything I needed minus my query.

I am really looking forward to putting this to use and building upon it.  I know there are several packs of Posh scripts that are available for SQL Server management and I will be looking at them shortly after the Summit.

$listOfServers = "server1", "server2", "server3"            
 $query = "sp_MSforeachdb  'use [?] 
select @@servername AS ServerName

,GROWTH = CASE WHEN [growth] = ''10'' THEN convert(nvarchar(10),''10%'') ELSE convert(nvarchar(10),[growth]/128) END 
[File_Size_MB] = convert(decimal(12,2),round([size]/128.000,2)) 

,[Space_Use_MB] = convert(decimal(12,2),round(fileproperty([name],''SpaceUsed'')/128.000,2))

,[Percent_Free_MB]=cast((([size]-fileproperty([name],''SpaceUsed''))/128.00000)/([size]/128.000) as numeric(10,2))*100 

,RTRIM(LTRIM([name])) AS FileName
,RTRIM(LTRIM([filename])) AS FilePath 

,convert(datetime,getdate(),112) as DateInserted 
FROM dbo.sysfiles'"             
$destinationServerName = "reporting_server"            
$destinationDatabaseName = "db_name"            
foreach($server in $listOfServers)            
    $results = Invoke-Sqlcmd -ServerInstance $server -Query $query            

    $connectionString = "Data Source=$destinationServerName;Integrated Security=true;Initial Catalog=$destinationDatabaseName;" ;                    
    $bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString ;                    
    $bulkCopy.DestinationTableName = "TableName" ;             
    $bulkCopy.WriteToServer($results) ;             

Read Full Post »

More on my recent PowerShell project.  I recon I have taken on the challenge to handle this entire request using Posh.  It started out just moving folders based on the folder name that included the date in format MMDDYY.  Next was having to stop services before moving the folders.  After that came a request to email the number of folders that are being moved.

This gave me two challenges.  One was getting the count of the folders and second was how to email using Posh.  I started my search on getting the folder count, for one that was trouble enough because everything was related to getting file count.  After a few searches I found the Where {$_.PSIsContainer} syntax which helped greatly.

I found the ability to send email very simple using “Send-MailMessage” all you need is the syntax to include.

What ended up working for me was the following code.
.     $item = (Get-ChildItem “\\myserver\directory1\directory2\” | Where {$_.PSIsContainer})

.     $count = $item.count
.     Send-MailMessage -FROM “myemail@company.com” -To “users@company.com” -Subject “Folder Migration”  -body “Guess what, $count folders will be copied today.” -SmtpServer myemailrelay.company.com

Read Full Post »

A recent project I was working on required me to move folders from a staging location to a processing location.  I found that PowerShell was the easiest way to accomplish that.  After getting that part of the project working a new requirement came up.  In order to move the folders I needed to stop an import service prior to the move and then start the service back up.  Since I had a new found love for PowerShell I figured why not use it for this as well.

I spent about 30 minutes researching how to do this online.  Many of the postings I found didn’t work for me.  I would get various errors which after researching seemed very common.  I was then lucky enough to stumble across an MSDN article which was rather helpful.  In the end I was able to get a working script that works very well for my purpose.

I had to combine WMI and PowerShell.  For the purpose of this article I have changed my server name and will use a different service.  I also added a 20 second wait in case you want to test this on your system.  For me I am going to use the Start-Sleep to add a buffer of time between the folder move and the starting of the service as well as a buffer to allow enough time for the import service to stop.

.        (gwmi win32_service -computername myservername -filter "name='RemoteRegistry'").stopservice()
.        Start-Sleep -s 20
.        (gwmi win32_service -computername myservername -filter "name='RemoteRegistry'").startservice()

Read Full Post »

I just had my first encounter with having to write a PowerShell script.  The request seemed pretty harmless when I originally got it.  The request was much like others I had gotten in the past where I needed to check to make sure that data had been loaded and kick off another process.

In the case of this new request I needed to validate a table had been updated with recent work, then move a bunch of folders to another directory where they would be processed by a third party application.  Harmless enough right?  Luckily the folder naming convention contained the two digit month, two digit day and two digit year as the first six characters.  The rest of the folder name had to do with the application but was still significant.

I first thought this would be a nice SSIS package with a for each loop where I would get a directory listing then one by one loop through them and any folders where the date was yesterday would move to the processing folder.  As I started down the SSIS route I quickly found this to be very complex.  Had I been dealing with files it would have been easier as there are enumerators for files.  I then turned to Google and found various C# and VB scripts that could accomplish this but I am not a .Net guy, I am a production DBA that came up through Systems Administration.

I pretty well knew at this point that I would have to do this through PowerShell.  I have sat through a couple of PowerShell presentations at SQL Saturdays to try to get some exposure to it and have a good friend Aaron Nelson who is a PowerShell guru who keeps trying to get me to try more stuff with PowerShell.  Last night after trying to find a way to do this with SSIS I turned to AskSSC and posted a question about my options.  This morning I had one comment where I was told by Blackhawk-17 to consider VB or PowerShell to do the heavy lifting.

So what happened this morning?  I finally bit the bullet and tried to have a go at it with PowerShell.  I made a task list of the individual items that would need to be done in order to accomplish the task then sought out the powershell logic to make it happen. In the end I didn’t need the steps that I thought I would.  I didn’t need to create an array of all the folder names and loop through them, I could simply use a wildcard with the “Move-Item” cmdlet.  So how am I accomplishing this minor task with moving folders from the previous business day to a new folder? Simple, two lines of code.

First I set a variable for my date in the format of the folder structure.  In my case it is MMDDYY

.         $yest = (get-date).AddDays(-1).ToString(‘MMddyy’)

Then I reference the $yest variable in the command to move the folders.

.         Move-Item C:\Posh\Branch\$yest* c:\Posh\Moved

Pretty simple huh?  I think I am sold on this PowerShell thing.

Now it is time to start building on the wee bit of knowledge I picked up today

Read Full Post »