October 13, 2014 at 8:52 am
Ok here is the basic premise of what I am attempting to do. I have a group of about 5 servers (which will likely grow toabout 25 in the near future) with their names listed in a table in a database on one of the servers. I want to query all servers in that table using the following query to pull the storage drive, database name, created date, age and size of the databases for each server listed in the table:
SELECT left(mf.Physical_Name,2) AS Storage_Drive,
DB_NAME(mf.database_id) AS DatabaseName,
db.create_Date,
DateDiff(day, db.create_date, getDate()) Age,
sum((mf.size*8))/1024 SizeMB
FROM sys.master_files mf inner join sys.databases db on (mf.database_id=db.database_id)
where DB_NAME(mf.database_id) not in (''Master'', ''TempDB'', ''MSDB'', ''Model'', ''ReportServer'', ''ReportServerTempDB'')
group by left(mf.Physical_Name,2), DB_NAME(mf.database_id), Create_Date, DateDiff(day, db.create_date, getDate())
order by DB_NAME(mf.database_id), Age desc
How would I best accomplish this if I want to implement it using a TSQL procedure?
October 13, 2014 at 9:08 am
That's the kind of thing that SSIS was designed for. If you insist on doing it in a single stored procedure, you can try creating linked servers, or shelling out to sqlcmd for each server.
John
October 13, 2014 at 12:50 pm
Thanks John,
Thats what I was thinking. I am just not very familiar with SSIS and am in need of something like yesterday... Ok well looks like I am going to be burning the midnight oil to get up to speed on SSIS in this capacity so I can keep the timeline 😀 thanks for the insight!
October 13, 2014 at 12:59 pm
For future users who are searching for the same solution, I found another feed at the link below which discusses the exact problem I am working:
http://www.sqlservercentral.com/Forums/Topic839545-147-1.aspx
October 13, 2014 at 1:40 pm
If you need something working right now, there's a feature in SSMS that might help you here. If you have a group of registered servers, you can right-click the name of the group and click New Query. This will open a new query window that will execute against all the servers in the group. The Server Name will be returned with your query results.
I know this isn't a long-term solution to your problem, but it'll get you what you're after and you sounded like you need it quickly.
HTH
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply