Hello, SQL 2005 My manager needs a Reporting Service report with these parameters: - total size of database on disk - size for each data file - empty space in each data file - comparison to last month - total growth for the period (based on months) I checked out the existing 'Standard reports' in SSMS and the report 'Disk Usage' came close to my need. My questions are: 1. How I can see the query and report behind this report file so that I can modify it and then host it in my Report Server? 2. If I need to export the same 'Disk Usage' report, as it is, to a real-time URL so that one does not open up Management Studio, how can I do that? 3. How can I capture "comparison to last month" and "total growth for the period" ? Thank you, Abba
Hi I think you will have to write soem scripts to get the info for all parameters that your manager wants to see. > - total size of database on disk SELECT SUM(size)*1.0/128 AS [size in MB] FROM dbname.sys.database_files > - size for each data file SELECT * FROM dbname.sys.database_files > - empty space in each data file Thake a look at sp_spaceused ,(it gets a table name as a parameter) > - comparison to last month Kepp all info abput file growth each month and compare it later. > - total growth for the period (based on months) Query the table (where you keep the info) "msnews.microsoft.com" wrote in message news:%231sS0H4JJHA.728@TK2MSFTNGP03.phx.gbl... > Hello, > > SQL 2005 > > My manager needs a Reporting Service report with these parameters: > > - total size of database on disk > - size for each data file > - empty space in each data file > - comparison to last month > - total growth for the period (based on months) > > I checked out the existing 'Standard reports' in SSMS and the report 'Disk > Usage' came close to my need. My questions are: > > 1. How I can see the query and report behind this report file so that I > can modify it and then host it in my Report Server? > 2. If I need to export the same 'Disk Usage' report, as it is, to a > real-time URL so that one does not open up Management Studio, how can I do > that? > 3. How can I capture "comparison to last month" and "total growth for the > period" ? > > Thank you, > Abba > >
You can use Profiler to trace any commands that SSMS issues. Simply set up a trace and run the report. -- Andrew J. Kelly SQL MVP Solid Quality Mentors "msnews.microsoft.com" wrote in message news:%231sS0H4JJHA.728@TK2MSFTNGP03.phx.gbl... > Hello, > > SQL 2005 > > My manager needs a Reporting Service report with these parameters: > > - total size of database on disk > - size for each data file > - empty space in each data file > - comparison to last month > - total growth for the period (based on months) > > I checked out the existing 'Standard reports' in SSMS and the report 'Disk > Usage' came close to my need. My questions are: > > 1. How I can see the query and report behind this report file so that I > can modify it and then host it in my Report Server? > 2. If I need to export the same 'Disk Usage' report, as it is, to a > real-time URL so that one does not open up Management Studio, how can I do > that? > 3. How can I capture "comparison to last month" and "total growth for the > period" ? > > Thank you, > Abba > >
1) you can profile as andy suggested, or you can go to the rdl for the report itself and examine what it contains. 2) Reporting services can expose reports via a website very easily. See BOL. Sounds like you got thrown into stuff you aren't trained for. Maybe a beginner's reporting services book is in order for you? 3) You will need to start storing data somewhere to do comparison reports. Simplest is to take the query and make it a select into ... where 1 = 0 to get the correct data structure then have a job that runs on some frequency to populate the table. -- Kevin G. Boles Indicium Resources, Inc. SQL Server MVP kgboles a earthlink dt net "msnews.microsoft.com" wrote in message news:%231sS0H4JJHA.728@TK2MSFTNGP03.phx.gbl... > Hello, > > SQL 2005 > > My manager needs a Reporting Service report with these parameters: > > - total size of database on disk > - size for each data file > - empty space in each data file > - comparison to last month > - total growth for the period (based on months) > > I checked out the existing 'Standard reports' in SSMS and the report 'Disk > Usage' came close to my need. My questions are: > > 1. How I can see the query and report behind this report file so that I > can modify it and then host it in my Report Server? > 2. If I need to export the same 'Disk Usage' report, as it is, to a > real-time URL so that one does not open up Management Studio, how can I do > that? > 3. How can I capture "comparison to last month" and "total growth for the > period" ? > > Thank you, > Abba > >
Very helpful. Thank you Uri. "Uri Dimant" wrote in message news:eQMQNX4JJHA.456@TK2MSFTNGP06.phx.gbl... > Hi > I think you will have to write soem scripts to get the info for all > parameters that your manager wants to see. > >> - total size of database on disk > > SELECT SUM(size)*1.0/128 AS [size in MB] > > FROM dbname.sys.database_files > >> - size for each data file > > > SELECT * > > FROM dbname.sys.database_files > > > >> - empty space in each data file > > > Thake a look at sp_spaceused ,(it gets a table name as a parameter) > > > >> - comparison to last month > > > Kepp all info abput file growth each month and compare it later. > > > >> - total growth for the period (based on months) > > > Query the table (where you keep the info) > > > > > > > > > "msnews.microsoft.com" wrote in message > news:%231sS0H4JJHA.728@TK2MSFTNGP03.phx.gbl... >> Hello, >> >> SQL 2005 >> >> My manager needs a Reporting Service report with these parameters: >> >> - total size of database on disk >> - size for each data file >> - empty space in each data file >> - comparison to last month >> - total growth for the period (based on months) >> >> I checked out the existing 'Standard reports' in SSMS and the report >> 'Disk Usage' came close to my need. My questions are: >> >> 1. How I can see the query and report behind this report file so that I >> can modify it and then host it in my Report Server? >> 2. If I need to export the same 'Disk Usage' report, as it is, to a >> real-time URL so that one does not open up Management Studio, how can I >> do that? >> 3. How can I capture "comparison to last month" and "total growth for the >> period" ? >> >> Thank you, >> Abba >> >> > >
