SQL Instance checkings.

  • Hey all,

    I have a random Question, Is there a way, I can run a T-SQL script from one sql server instance and have it connect to multiple SQL servers and run the script and save the output into a "output-<servername>.csv File format?

    and How would you create a t-SQL script to automatically save the output(s) to a CSV file?

    Here is the script im hoping to run from my servers instance to check about 40 other servers details in my enviroment, so I dont need to run it manually from each one every now and then...

    Thanks ahead of time for any and all assistance.

    ------------------------------------------------------

    -- Script to get file sizes from SQL Database

    -- Script to calculate information about the Log Files

    -- Script by Gareth Thompson (2010/08/06)

    -- Tested on SQL 2005 and 2008.

    ------------------------------------------------------

    -- Version and Server Name

    ------------------------------------------------------

    Select @@SERVERNAME

    Select @@VERSION

    ------------------------------------------------------

    -- Database Size's and Names

    ------------------------------------------------------

    SET QUOTED_IDENTIFIER OFF

    SET NOCOUNT ON

    DECLARE @dbname varchar(50)

    declare @string varchar(250)

    set @string = ''

    create table #datafilestats

    ( Fileid tinyint,

    FileGroup1 tinyint,

    TotalExtents1 dec (8, 2),

    UsedExtents1 dec (8, 2),

    [Name] varchar(50),

    [FileName] sysname )

    create table #dbstats

    ( dbname varchar(50),

    FileGroupId tinyint,

    FileGroupName varchar(25),

    Total_Size_in_MB dec (8, 2),

    Used_Size_in_MB dec (8, 2),

    Free_Size_in_MB dec (8, 2))

    DECLARE dbnames_cursor CURSOR FOR SELECT name FROM master..sysdatabases

    OPEN dbnames_cursor

    FETCH NEXT FROM dbnames_cursor INTO @dbname

    WHILE (@@fetch_status = 0)

    BEGIN

    SELECT @dbname AS DatabaseName, DATABASEPROPERTYEX('master', 'Status') AS DBStatus

    set @string = 'use [' + @dbname + ']; DBCC SHOWFILESTATS'

    insert into #datafilestats exec (@string)

    insert into #dbstats (dbname, FileGroupId, Total_Size_in_MB, Used_Size_in_MB)

    select @dbname, FileGroup1, sum(TotalExtents1)*65536.0/1048576.0,

    sum(UsedExtents1)*65536.0/1048576.0

    from #datafilestats group by FileGroup1

    set @string = 'use [' + @dbname + ']; update #dbstats set FileGroupName =

    sysfilegroups.groupname from #dbstats, sysfilegroups where

    #dbstats.FileGroupId = sysfilegroups.groupid and #dbstats.dbname =''' +

    @dbname + ''''

    exec (@string)

    update #dbstats set Free_Size_in_MB = Total_Size_in_MB - Used_Size_in_MB where

    dbname = @dbname

    truncate table #datafilestats

    FETCH NEXT FROM dbnames_cursor INTO @dbname

    END

    CLOSE dbnames_cursor

    DEALLOCATE dbnames_cursor

    drop table #datafilestats

    select * from #dbstats

    drop table #dbstats

    ------------------------------------------------------

    --Calculate information about the Log Files

    ------------------------------------------------------

    set nocount on

    create table #LogUsageInfo

    ( db_name varchar(50),

    log_size dec (8, 2),

    log_used_percent dec (8, 2),

    status dec (7, 1) )

    insert #LogUsageInfo exec ('dbcc sqlperf(logspace) with no_infomsgs')

    select * from #LogUsageInfo

    drop table #LogUsageInfo

  • Hi Gareth

    What's your overall objective with this script?

    If you simply want to monitor database and log file useage from a central location there are a number of things you could do.

    Buy some monitoring software such as SCOM or regate (I think 40 servers warrants the expense).

    Look into policy based management (you can also use central management server to run queries against server groups, search BOL for "How to: Execute Statements Against Multiple Servers Simultaneously (SQL Server Management Studio)")

    Set up SQL Agent performance alerts on each server.

    MCITP SQL Server 2005/2008 DBA/DBD

  • Its not for a monitoring purpose.

    What I am doing is a tracking of every database of their online status and database sizes and growth of the instances.

    Its not continually run. only once every now and then, it just takes me over a day to log into each system and run the script. where running remotely would be alot faster.

  • gareth.thompson (8/30/2010)


    Its not for a monitoring purpose.

    What I am doing is a tracking of every database of their online status and database sizes and growth of the instances.

    Its not continually run. only once every now and then, it just takes me over a day to log into each system and run the script. where running remotely would be alot faster.

    Hello Gareth

    That's monitoring!

    Because of the irregular nature I think you should look into central management as stated above. This can be found in BOL. It's easy to set up and allows you to query a group of servers with 1 statement.

    MCITP SQL Server 2005/2008 DBA/DBD

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply