February 7, 2007 at 7:12 am
Hi Guys;
I have a DTS package that pull data from a server and and pushes it into another server. The DTS is supposed to be scheduled to run every week. It should include all the data of the past week. Right now I only have it to display data for the day it was run. This is what my query looks like in the Transform Data Task:
SELECT @@servername as ServerName, databasename, Sum(CAST((DataSize+LogSize) as dec(20,2))) as TotalSizeMB,
getdate() as RunDate
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))
Can anyone pls give me any suggestions on how I will load the data of the past week. Is there anything I need to change on this query?
Regards
February 7, 2007 at 8:09 am
Imke
This query returns the data and log size for all databases on the server, right? But it only provides a snapshot of the sizes at the time the query is run. So if you want data from the past week, you will need to schedule this query to run regularly (daily, perhaps) and insert its results into a separate table. Then you can change your weekly DTS package so that it copies the data from that separate table to your other server.
I hope I've understood you correctly and that this is helpful.
John
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply