August 30, 2010 at 2:23 am
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
August 30, 2010 at 9:13 am
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
August 30, 2010 at 9:19 am
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.
September 4, 2010 at 4:05 pm
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