1

I have been all over looking for the solution to logging sp_blitzfirst results to table on a linked server. I see github posts where a contributor was adding this to sp_blitz, but that's been closed for a while. I see vague rumblings that this might already be in sp_blitzfirst, but am ready to be punished for my inability to find the solution on my own.

Can someone point me to a definitive answer?

1

The limitation of the script can be overcome by using a wrapper process to collect the info locally and then dump that info into destination server. One way to do this is to use dbatools.

  1. Run dbo.sp_BlitzFirst locally since you can log the info locally to a table.

        EXEC dbo.sp_BlitzFirst 
         @OutputDatabaseName = 'tempdb'-- change here !!
        , @OutputSchemaName = 'dbo'
        , @OutputTableName = 'BlitzFirst'
        , @OutputTableNameFileStats = 'BlitzFirst_FileStats'
        , @OutputTableNamePerfmonStats = 'BlitzFirst_PerfmonStats'
        , @OutputTableNameWaitStats = 'BlitzFirst_WaitStats'
        , @OutputTableNameBlitzCache = 'BlitzCache'
        , @OutputServerName ='--YourServerName--'-- change here !!
    
  2. Use dbatools to dump that info into your central server for reporting etc.

    e.g. a simple example that pulls data from your source server and performs a bulk insert into destination sql server with the ability to auto generate table

          $tableName = "BlitzFirst_FileStats"
          $query = "select * from [dbo].[BlitzFirst_FileStats]"
          $results = Invoke-DbaQuery -SqlInstance 'yourserverName,port' -SqlCredential $Cred -Database tempdb -Query $query
          $results = Write-DbaDataTable -SqlInstance 'yourTargetInstance,portNo' -SqlCredential $Cred -Database CentralDBName -Table BlitzFirst_FileStats -Schema dbo -AutoCreateTable
    

Note: If you have large amount of servers, its best to multithread dbatools (I recently blogged this on how to leverage multithreading using PoshRSJob).

4

There is a note in the script on GitHub:

Known limitations of this version:

...

  • @OutputServerName is not functional yet.

And indeed, that parameter is not used in the proc. So for now, storing the output to a table across a linked server appears to not be supported.

Since you included the SQL Server 2008 tag in your question, I'll also mention that they don't go out of their way to keep the scripts working on unsupported versions of SQL Server, and you're only a few months away from that end of support date (July 2019!). I imagine the scripts will continue to work for a while, but there are no guarantees at that point.

  • When we move to the newer version of SQL Server, the Blitzfirst script will not change, nor will this facet of its functionality. I'm not sure the purpose of the jab. – Mitch Stein Dec 31 '18 at 15:41
  • @MitchStein Oh man, sorry if that came across as a jab! I didn't intend it that way at all. The BOU folks, and the community contributors, are always adding new things to the Blitz scripts. So I was just letting you know that the SQL Server version might hold you back from upgrading to newer versions of BlitzFirst. – Josh Darnell Dec 31 '18 at 15:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.