February 7, 2007 at 2:10 am
Hi Guys,
I have the following script:
create proc sp__ServerSize
as
create table #tbl1
(
servername varchar(500),
databasename varchar(500),
totalsizeMB float)
insert into #tbl1
--
SELECT @@servername as ServerName, databasename, Sum(CAST((DataSize+LogSize) as dec(20,2))) as TotalSizeMB
FROM
(SELECT dbid,
CASE Sum(size*8/1024)
WHEN 0 THEN 1
ELSE Sum(size*8/1024)
END AS DataSize
FROM master..sysaltfiles
WHERE groupid <> 0
GROUP BY dbid) q1
INNER JOIN
(SELECT dbid,
CASE Sum(size*8/1024)
WHEN 0 THEN 1
ELSE Sum(size*8/1024)
END AS LogSize
FROM master..sysaltfiles
WHERE groupid = 0
GROUP BY dbid) q2
ON q1.dbid = q2.dbid
INNER JOIN
(SELECT dbid, [name] AS databasename FROM master.dbo.sysdatabases
where name not in ('tempdb','pubs','northwind')) q3
ON q1.dbid = q3.dbid
Group By databasename, CAST((DataSize+LogSize)/1024 as dec(20,2)), CAST((DataSize+LogSize) as dec(20,2))
--ORDER BY Databasename
select servername,count(databasename) as TotalDB, sum(totalsizeMB) as Total_Size_MB, Sum(CAST((totalsizeMB)/1024 as dec(20,2))) as Total_Size_GB from #tbl1
group by servername
drop table #tbl1
The script works fine, it is just that I have to pull the data from different servers and archive the history on a different server. I have to do this without dropping the table.
Can anyone pls assist on this?
Regards;
February 7, 2007 at 9:12 am
Add the servers from which you need to fetch the data as linked server using sp_addlinkedserver
Please refer to BOL and prefix the server name before servername.master.. in your stored procedure
Prasad Bhogadi
www.inforaise.com
February 8, 2007 at 10:14 am
If you are considering third party tools- look at SQL Farms- they have a tool that allows you to run anything across databases and servers.
Editor's Note: : Dr. Omri Bahat works for SQLFarms, a software vendor of SQL Server tools.
February 9, 2007 at 6:48 am
February 9, 2007 at 5:41 pm
It can be done using SSIS
OR
SQLcmd with BCP...
MohammedU
Microsoft SQL Server MVP
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply