How do we get the filesize of the promoted project

Problem

How do I get the the size of data that got promoted?

Resolution

Run the following query on the promoted project database. Please provide source project name and replace 'databasename'(on the fifth line of this query) with the source project databasename. This will return the file size of each file that was promoted in kb and file name of the promoted files.

declare @sourceprojectname nvarchar(1000)
 set @sourceprojectname = '' --set the name of the source project
select t2.filesize/(1024.0) as [FileSize in KB], t2.[fileName]
 from tbl_pr_FileInfoPromoteFileAssociation t1 inner join
 sourcedatabasename..tbl_ex_FileInfo t2  --replace sourcedatabasename with the source projectdatabasename
on
    t1.SourceFileId = t2.FileId where t1.SourceProjectName = @sourceprojectname

The total file size can be calculated using the following query:

declare @sourceprojectname nvarchar(1000)
 set @sourceprojectname = '' --set the name of the source project
select sum(isnull(t2.filesize,0))/(1024.0) as [FileSize in KB]
 from tbl_pr_FileInfoPromoteFileAssociation t1 inner join
sourcedatabasename..tbl_ex_FileInfo t2  --replace sourcedatabasename with the source projectdatabasename
on
  t1.SourceFileId = t2.FileId where t1.SourceProjectName = @sourceprojectname