Reporting From Linked Servers

Dave Mason has a few scripts he uses to pull database metrics from his SQL Server instances:

There is a total of “N” linked servers. The query selects from each one and combines the results via UNION ALL. Here’s a sample of the output on my CMS:

What we’ve seen so far could be used as a template for other queries, such as a backup report, a database files report, etc. The code could be put in a stored procedure and run as needed for automated tasks. I’ve used similar code to retrieve data for an SSRS report.

Let’s examine a problem you’re likely to encounter by including @@SERVERNAME within the query

Give it a read.

Related Posts

Data File Migration With Minimal Downtime

Nate Johnson weaves a yarn around moving from one storage system to another with minimal downtime: Our ERP database has been chosen by the IT gods to get moved to the shiny new flash storage array, off the old spinning-rust SAN.  This is fantastic news for the business users.  But lo, the executives warn us, […]

Read More

Failed To Open Loopback Connection

Arun Sirpal diagnoses a connection problem: I could not read my error log on one of my local SQL Servers, when I executed the following code: EXEC sp_readerrorlog I received the below: Msg 22004, Level 16, State 1, Line 2 Failed to open loopback connection. Please see event log for more information. Msg 22004, Level 16, State […]

Read More

Categories

September 2016
MTWTFSS
« Aug Oct »
 1234
567891011
12131415161718
19202122232425
2627282930