Pulling data from different servers

Question

Post reply

Pulling data from different servers

Imke Cronje

SSCarpal Tunnel

Points: 4822
More actions
February 7, 2007 at 2:10 am

#68702

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;