My good friend Marcos Freccia (blog | twitter) asked me for a simple and fast way to save the output of running the Get-EventLog cmdlet on a SQL Server table. Well, the quickest and easiest way that I know is to use Chad Miller's Out-DataTable and Write-DataTable functions, because the Write-dataTable function uses sqlbulkcopy. I took the liberty of tweaking the Write-DataTable function to get the output object via Pipeline, and you can download the modified version over on my blog.

Keep in mind that when you pass the object by pipeline, it will be using the SqlBulkCopy too, but line by line. I will show two variations.

First lets create the table to receive the cmdlet output:

USE [Test]
GO
      /****** Object:  Table [dbo].[EventViewer]    
      /Script Date: 08/28/2011 08:56:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[EventViewer](
        [Index] [int] NULL,
        [Time] [datetime] NULL,
        [EntryType] [varchar](MAX) NULL,
        [Source] [varchar](MAX) NULL,
        [InstanceID] [varchar](MAX) NULL,
        [Message] [varchar](MAX) NULL
  ) ON [PRIMARY]
GO
  SET ANSI_PADDING OFF
GO
      

Then let's populated the table, passing the objects by pipeline (which means that I am inserting the data line by line)

Get-EventLog -ComputerName YourComputerName -LogName Security -After "22-08-2011" | select index,TimeGenerated,EntryType,Source,InstanceID,Message | Out-DataTable | Write-DataTable -ServerInstance YourServer -Database YourDatabase -TableName EventViewer

Just as a benchmark, let's see how long that takes:

Measure-Command{ Get-EventLog -ComputerName Vader -LogName Security -After "22-08-2011" | select index,TimeGenerated,EntryType,Source,InstanceID,Message | Out-DataTable | Write-DataTable -ServerInstance Vader -Database Test -TableName EventViewer }

Days                            : 0
Hours                          : 0
Minutes                     : 0
Seconds                     : 1
Milliseconds            : 753
TotalMilliseconds : 1753,707

On the other hand, let´s populate the table using Write-DataTable with an appropriate set of values.

$variable = ( Get-EventLog -ComputerName YourComputer -LogName Security -After "22-08-2011" | select index,TimeGenerated,EntryType,Source,InstanceID,Message); $valuedatatable = Out-DataTable -InputObject $variable ; Write-DataTable -ServerInstance YourServer -Database YourDatabase -TableName EventViewer -Data $valuedatatable

. And how many seconds did that take?

Measure-Command { $variable = ( Get-EventLog -ComputerName YourComputer -LogName Security -After "22-08-2011" | select index,TimeGenerated,EntryType,Source,InstanceID,Message); $valuedatatable = Out-DataTable -InputObject $variable ; Write-DataTable -ServerInstance YourServer -Database YourDatabase -TableName EventViewer -Data $valuedatatable }

We can clearly see the difference ; the first script took 1753 millisecond's and the second only took 1192. Looks like it is not only in the SQL Server that line by line operations are evil.

Scaling Out

First we have to add a column to our SQL Server table that stores the computer name and instance ID which you're applying the Get-EventLog cmdlet to. Remember: because the Write-DataTable cmdlet uses sqlbulkcopy, you need the pass the columns to it in the same order as they occur in the table (as you'll see below). We'll also need to store the locations of the servers we want to survey in a flat text file, called servers.txt in this example. So, let's start by creating the receiving table:

USE [Test]
GO
      /****** Object:  Table [dbo].[EventViewer]  
      /Script Date: 08/28/2011 09:21:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[EventViewer](
        [ComputerName] [varchar](50) NULL,
        [Index] [int] NULL,
        [Time] [datetime] NULL,
        [EntryType] [varchar](MAX) NULL,
        [Source] [varchar](MAX) NULL,
        [InstanceID] [varchar](MAX) NULL,
        [Message] [varchar](MAX) NULL
  ) ON [PRIMARY]
GO
  SET ANSI_PADDING OFF
GO
      

Then we can use the Script:

I got into Powershell a few years after I saw a post mentioning it, here on Simple-Talk, by Ron Dameron (another person who's knowledge and writing I'm a fan of). Having seen the technology mentioned, I then saw a presentation on the topic at TechEd Brazil 2008 by Buck Woody, and I was immediately in love with the technology.

Journey of 1000 Miles, and the First Step

Determined to learn more about PowerShell, I remember seeing posts by the scripting Jedi (and now good friend) Chad Miller, and thinking "My God, he knows a lot. I have to learn from him." I'd certainly never have thought it possible that I would be one of the developers of the SQLPSX project that Chad leads. Whenever I see my name in the list of developers, I am simply grateful for having had the opportunity to work with these people. Of course, all of these people that I admire and respect are legends, and you might think to yourself, as I did, "I'm just some guy, and they're busy people. They must get hundreds of emails from people like me, so if I try and get in touch, they won't get around to responding."

How wrong could I be?

The reality was, and is, quite the opposite. These industry greats have got to where they are today not only because of their tremendous technical knowledge, but also because they are some of the most modest and welcoming people I've ever had the privilege to meet. Even now, I have trouble expressing my gratitude for the care and attention that I've received, and continue to be graced with.

Back to the story:

As I learned more and more about PowerShell, I decided that I'd like to try and take on bigger personal goals, so I sent an email to the Simple-Talk editors, asking if I could write for them. Even though I'd sent the first email, I was still a little surprised when, a few days later, Andrew Clarke (Simple-Talk Editor) answered me with an invitation to write for the site. This might not seem like a big deal to some of you, but the thought running through my head was something like "Holy Mother, I'm going to be writing in a publication alongside some of my gurus and inspirations!"

As an aside, if you're reading this and wanting to learn more about PowerShell, be sure to check out the links at the bottom of this post - they'll take you to some really excellent resources.

That opportunity, in turn, set up others, as I gained confidence and recognition. I have the honor of being the first Brazilian to write about SQL Server / Powershell in an international Magazine, and the first Brazilian to have an article in the SQL Server Standard Magazine. Things were going well, but that's success without friends to share it with?

I remember seeing Fabiano Amorim blogging about the Query Processor, and thinking he would be a star on Simple-Talk. I introduced him to the editors, and it wasn't long before Fabiano was on the rise, and I had the joy of seeing a friend and fellow Brazilian get a meteoric 30,000 views of his article in just a few weeks.

As my fortune rose, I had the opportunity to meet Fernando Guerrero and Eladio Rincon, two wonderful, modest people, and both legends in the Microsoft world. I remember, on my first day with SolidQ mentors, seeing in my IM communicator that Itzik Ben Gan was online. I have to admit that the only conversation-starters I could think of would have made my sound like a hopeless fanboy - which I am, but I was trying to play it cool!

Doing, Learning, and Sharing

I know I'm pretty exuberant and excitable on twitter, and it's likely that my followers think I'm a little crazy. I'm not normal, for sure, but I'm only hyped up because every step forward is a celebration and a victory for me. Every single step is important to me.

That said, maybe I got too caught up in my own good fortune. Maybe I just lost sight of what created those opportunities I was so grateful for. Whatever the cause, I went quiet. I was still working hard, still honing my existing skills and still learning more, but I wasn't sharing.

Unfortunately, my MVP status was not renewed this year, and I recognize that I've not contributed to the community nearly as much as I should have. But now that I've tasted this success, and I know what I'm capable of, I'm not about to give up. I'm carrying on with my writing, and I've submitted sessions for SQLSaturday95, SQLBits and SQLRally Nordic. I wouldn't dream of comparing myself to some of the legends who will be speaking, but we've all got to start somewhere, and all I can do is my best.

As a someone just starting out with presenting, I can tell you that it's a strange feeling to submit sessions. I don't presume to think that I'll necessarily be able to teach something to the attendees - that would be like trying to teach the teachers. I just want to talk about what I'm doing with Powershell and SQL Server. Things like restoring databases asynchronously, receiving notifications and emails when SQL Server services stop, how to think and write in Powershell, advanced functions and, to be honest, just how to create some of the cool things that I learned from the best. And, of course, I hope to personally meet people I admire and get my photo taken with each one of them.

If you're going to be attending either of those events, then I hope I can present to you; but if it doesn't work out this year, I'll keep trying. I certainly didn't come this far just to quit. I know my English isn't very good, but it's not the worst you'll hear, so I hope you'll let me give it a try.

You can be sure I'll do my best, and that I don't do any of this for the reputation it might earn me. It's for the passion that I have for what I do, and I want to repay the community for everything I've been so lucky to receive.

Thank you everyone, especially Simple-Talk. It all started here :)

PS - while I was writing this Post, I received another piece of fantastic news. We are hosting SQLSaturday 100 in Brazil!

Resources for PowerShell and SQL Server

As I mentioned earlier, if you've been reading this and wanting to learn more about PowerShell (and PowerShell with SQL Server), then here are some truly excellent start points...

Links for Powershell

• Ravikanth Chaganti Blog
• Windows Powershell Blog
• Hey, Scripting Guy!Blog
• Jeffery Hicks - The Lonely Administrator
• Sean Kearney Blog
• Don Jones on Powershell
• Jonathan Medd's Blog
• Shay Levy - If you repeat it, PowerShell it!

Links for Powershell with SQL Server

• Chad Miller - Sev17
• Aaron Nelson (SQLVariant) Blog
• Jorge Segarra - SQL University Blog
• Max Trinidad - The PowerShell Front

I have quite a few SQL servers, and monitoring the growth of tables on them is a real hassle. I finally got tired of it and decided to build my own solution which, given my scenario, had to have these features:

1. All the collected data had to be stored in a separate server from the monitored systems.
2. I can't use linked servers.
3. I didn't want to use TSQL scripts in every server to collect the data. If I change the script on one server, I'd then have to change it on all the servers. That's a major headache, so I wanted to centralize the script.
4. I didn't want to use a third-party tool.

I've been hearing about windows PowerShell as a method for administering Windows servers (especially Exchange Server 2007) for quite a while, and since last year I've become an avid enthusiast. By itself, PowerShell has already helped me in several situations, but when you combine it with SMO, as my fellow enthusiasts will know, it becomes a very formidable tool. You'll see this very clearly in my solution, where I used:

• One central Server to collect the data, with a table called tablegrowth on a database called DBA.
• A PowerShell script to collect the data about the various tables.
• One trigger in my data repository to calculate the tables' growth between collections.

I'm NOT going to cover what Powershell is, how to write Powershell scripts or what SMO (SQL Server Management Objects) are. There is already plenty of information about those out on the web, and I'm only here to talk about one thing today: monitoring table growth.

So let's go to the solution.

Setting up a Collect Data Repository

In my first collections I didn't have a SQL server repository, and I was using my desktop computer to hold all my data. These days, I have a server to centralize all my scripts and data (much nicer!), and in the script I'll show you today this server is called "CollectDataServer". To start, let's create a database called DBA - to be honest, the configuration of this server, database...etc is entirely up to you. You just need a SQL server and a database.

CREATE DATABASE [DBA] ON PRIMARY
    ( NAME = N'DBA', FILENAME = N'C:\Program Files\Microsoft SQL Server\
     MSSQL10.MSSQLSERVER\MSSQL\DATA\DBA.mdf' , SIZE = 3072KB , 
     FILEGROWTH = 1024KB )
            LOG ON
    ( NAME = N'DBA_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\
     MSSQL10.MSSQLSERVER\MSSQL\DATA\DBA_log.ldf' , SIZE = 1024KB , 
     FILEGROWTH = 10%)
GO
            

With this done, let's create the table that we'll store the collected data in:

USE [DBA]
GO 
CREATE TABLE [dbo].[tablegrowth](
                [DDate] [datetime] NULL,
                [ServerName] [varchar](50) NULL,
                [DatabaseName] [varchar](50) NULL,
                [TableName] [varchar](50) NULL,
                [SpaceIndexUsed] [float] NULL,
                [SpaceDataUsed] [float] NULL,
                [Total] [float] NULL,
                [growing] [float] NULL
    ) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tablegrowth] ADD DEFAULT (GETDATE()) FOR [DDate]
GO

And finally, we'll need to create the trigger. This trigger is responsible for collecting the information about the size of a table's data and index, and then subtracting the size of the last collection to find the table growth:

CREATE TRIGGER tr_insert ON tablegrowth instead OF 
INSERT
                AS
    BEGIN
    DECLARE @DDate DATETIME,
        @ServerName VARCHAR(50),
        @DatabaseName VARCHAR(50),
        @TableName VARCHAR(50),
        @SpaceIndexUsed FLOAT,
        @SpaceDataUsed FLOAT,
        @Total FLOAT,
        @growing FLOAT,
        @growinglast FLOAT,
        @totallast FLOAT,
        @FirstTime bit
        --retrieve all data, apart from @growing and @growinglast
    SELECT @DDate = ddate,
        @ServerName = Servername,
        @DatabaseName = DatabaseName ,
        @TableName = TableName ,
        @SpaceIndexUsed = SpaceIndexUsed,
        @SpaceDataUsed = SpaceDataUsed ,
        @Total = SpaceIndexUsed + SpaceDataUsed
        FROM inserted --retrieve last total
    SELECT @totallast = @total
        FROM tablegrowth
        WHERE ServerName = @ServerName
            AND DatabaseName = @DatabaseName
            AND TableName = @TableName
            AND ddate = ( 
        SELECT MAX(ddate)
            FROM tablegrowth
            WHERE ServerName = @ServerName
                AND DatabaseName = @DatabaseName
                AND TableName = @TableName
        )
    --If this does not have any values, I assume the current value is the
    --first. First collection for this table:
    SET @firsttime = 0
    IF @totallast IS NULL
        SET @firsttime = 1
        SET @totallast = ISNULL(@totallast,@total)
            --now I calculate the growth since the last collection
        IF @firsttime = 0
        SET @growing = @Total - @totallast
        ELSE
        SET @growing = @Total
        -- now insert the values
    INSERT INTO tablegrowth(
                    DDate ,
                    ServerName ,
                    DatabaseName,
                    TableName ,
                    SpaceIndexUsed ,
                    SpaceDataUsed ,
                    Total ,
                    growing
        )
            VALUES (
            @DDate ,
            @ServerName ,
            @DatabaseName,
            @TableName ,
            @SpaceIndexUsed ,
            @SpaceDataUsed ,
            @Total ,
            @growing
        )
    END

With our environment now configured, we can use the Powershell Script to collect the data.

Powershell Script

First we have to create a flat file (.txt) with all the servers to be monitored. In my case I have 7 servers, so the list (stored in c:\servers\servers.txt folder) will look something like this:

Server1
Server2
Server3
Server4
Server5
Server6
Server7

Now let's see the PowerShell script:

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") 
   | out-null 

# Database and server repository
$ServerCentral = "CollectDataServer" 
$DatabaseCentral = "DBA" 
#Today date
$TodaysDate = get-date -format "yyyy-MM-dd hh:mm:ss" 

foreach ($svr in get-content "C:\servers\servers.txt" )
{     $Server=New-Object "Microsoft.SqlServer.Management.Smo.Server" 
            "$svr"
      $data = $Server.Databases| where-object {$_.IsSystemObject -eq 
            $FALSE -
            and 
      $_.IsAccessible -eq $TRUE -and $_.name -ne "DBA" } | foreach {
        $DatabaseName = $_.name
        $ServerName = $Server.Name
       foreach ($tables in $Server.Databases[$_.name].tables ) {
            if (!$tables.IsSystemObject)
                 {
                    $tablename = $tables.name
                    $SpaceIndexUsed = $tables.IndexSpaceUsed
                    $SpaceDataUsed = $tables.DataSpaceUsed

                    $sql = "insert into TableGrowth (DDate,
                    ServerName,DatabaseName,TableName,
                    SpaceIndexUsed,SpaceDataUsed) values 
                    ('$TodaysDate','$ServerName','$DatabaseName'
                    ,'$TableName',$SpaceIndexUsed,$SpaceDataUsed)"
                    Invoke-Sqlcmd -ServerInstance $ServerCentral
                    -Database $DatabaseCentral -Query $sql
                     }
      }
   }
}

This collects the data from all the servers in my txt file. The system databases and system tables are all ignored, and it can be modified to collect data from only some tables, or some databases... how it's fine-tuned is entirely up to you.

This script uses SQLPS.EXE (SQL Server 2008) because I'm using the Invoke-Sqlcmd cmdlet. If you want to use the Powershell shel, I just add the SQL server snapins :

Set-ExecutionPolicy Unrestricted
Add-PSSnapin SqlServerCmdletSnapin 
Add-PSSnapin SqlServerProviderSnapin

Now it's just a case of scheduling, and we'll have everything in position. I use SQL server 2008 and PowerShell jobs to do this, but you can use powershell.exe to schedule in windows, too.

Retrieving the data

Lastly, to retrieving my data for table growth in January, organized by server (for example), here's what I need:

SELECT      ServerName,
            DatabaseName,
            TableName,
            SUM(growing ) Growing
    FROM      tablegrowth
    WHERE     ddate BETWEEN '2009-01-01 00:00:00' 
        AND '2009-01-01 23:59:00'
    GROUP BY ServerName,
            DatabaseName,
            TableName 

... And the data is collected. Now you just need to retrieve it any way you like it.