Report database sizes and status

------------------------------Data file size----------------------------
if exists (select * from tempdb.sys.all_objects where name like '%#dbsize%')
drop table #dbsize
create table #dbsize
(Dbname sysname,dbstatus varchar(50),Recovery_Model varchar(40) default ('NA'), file_Size_MB decimal(30,2)default (0),Space_Used_MB decimal(30,2)default (0),Free_Space_MB decimal(30,2) default (0))
go
 
insert into #dbsize(Dbname,dbstatus,Recovery_Model,file_Size_MB,Space_Used_MB,Free_Space_MB)
exec sp_msforeachdb
'use [?];
  select DB_NAME() AS DbName,
    CONVERT(varchar(20),DatabasePropertyEx(''?'',''Status'')) , 
    CONVERT(varchar(20),DatabasePropertyEx(''?'',''Recovery'')), 
sum(size)/128.0 AS File_Size_MB,
sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as Space_Used_MB,
SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS Free_Space_MB 
from sys.database_files  where type=0 group by type'
go

-------------------log size--------------------------------------
  if exists (select * from tempdb.sys.all_objects where name like '#logsize%')
drop table #logsize
create table #logsize
(Dbname sysname, Log_File_Size_MB decimal(38,2)default (0),log_Space_Used_MB decimal(30,2)default (0),log_Free_Space_MB decimal(30,2)default (0))
go
 
insert into #logsize(Dbname,Log_File_Size_MB,log_Space_Used_MB,log_Free_Space_MB)
exec sp_msforeachdb
'use [?];
  select DB_NAME() AS DbName,
sum(size)/128.0 AS Log_File_Size_MB,
sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as log_Space_Used_MB,
SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS log_Free_Space_MB 
from sys.database_files  where type=1 group by type'
go

--------------------------------database free size
  if exists (select * from tempdb.sys.all_objects where name like '%#dbfreesize%')
drop table #dbfreesize
create table #dbfreesize
(name sysname,
database_size varchar(50),
Freespace varchar(50)default (0.00))
 
insert into #dbfreesize(name,database_size,Freespace)
exec sp_msforeachdb
'use [?];SELECT database_name = db_name()
    ,database_size = ltrim(str((convert(DECIMAL(15, 2), dbsize) + convert(DECIMAL(15, 2), logsize)) * 8192 / 1048576, 15, 2) + ''MB'')
    ,''unallocated space'' = ltrim(str((
                CASE 
                    WHEN dbsize >= reservedpages
                        THEN (convert(DECIMAL(15, 2), dbsize) - convert(DECIMAL(15, 2), reservedpages)) * 8192 / 1048576
                    ELSE 0
                    END
                ), 15, 2) + '' MB'')
FROM (
    SELECT dbsize = sum(convert(BIGINT, CASE 
                    WHEN type = 0
                        THEN size
                    ELSE 0
                    END))
        ,logsize = sum(convert(BIGINT, CASE 
                    WHEN type <> 0
                        THEN size
                    ELSE 0
                    END))
    FROM sys.database_files
) AS files
,(
    SELECT reservedpages = sum(a.total_pages)
        ,usedpages = sum(a.used_pages)
        ,pages = sum(CASE 
                WHEN it.internal_type IN (
                        202
                        ,204
                        ,211
                        ,212
                        ,213
                        ,214
                        ,215
                        ,216
                        )
                    THEN 0
                WHEN a.type <> 1
                    THEN a.used_pages
                WHEN p.index_id < 2
                    THEN a.data_pages
                ELSE 0
                END)
    FROM sys.partitions p
    INNER JOIN sys.allocation_units a
        ON p.partition_id = a.container_id
    LEFT JOIN sys.internal_tables it
        ON p.object_id = it.object_id
) AS partitions'
----------------------------------- 
if exists (select * from tempdb.sys.all_objects where name like '%#alldbstate%')
drop table #alldbstate 
create table #alldbstate 
(dbname sysname,
DBstatus varchar(55),
R_model Varchar(30))
  
--select * from sys.master_files
 
insert into #alldbstate (dbname,DBstatus,R_model)
select name,CONVERT(varchar(20),DATABASEPROPERTYEX(name,'status')),recovery_model_desc from sys.databases
--select * from #dbsize
 
insert into #dbsize(Dbname,dbstatus,Recovery_Model)
select dbname,dbstatus,R_model from #alldbstate where DBstatus <> 'online'
 
insert into #logsize(Dbname)
select dbname from #alldbstate where DBstatus <> 'online'
 
insert into #dbfreesize(name)
select dbname from #alldbstate where DBstatus <> 'online'
 
select   
d.Dbname,d.dbstatus,d.Recovery_Model,
(file_size_mb + log_file_size_mb) as DBsize,
d.file_Size_MB,d.Space_Used_MB,d.Free_Space_MB,
l.Log_File_Size_MB,log_Space_Used_MB,l.log_Free_Space_MB,fs.Freespace as DB_Freespace
from #dbsize d join #logsize l 
on d.Dbname=l.Dbname join #dbfreesize fs 
on d.Dbname=fs.name
order by Dbname

Reporting Metrics for SSRS

Here is a practical query that can be easily modified to provide SSRS Reporting Metrics and help find unused or misconfigured objects;  Modify the granularity by simply uncommenting the UserName lines of code below. 

USE REPORTSERVER; SELECT COUNT(RE.REPORTID) AS EXECUTION_COUNT ,CAT.NAME ,CAT.PATH ,SUM(TIMEDATARETRIEVAL) / COUNT(NAME) AS AVG_MS_DATA_RETRIEVAL ,SUM(TIMEPROCESSING) / COUNT(NAME) AS AVG_MS_PROCESSING ,SUM(TIMERENDERING) / COUNT(NAME) AS AVG_MS_RENDERING ,SUM(BYTECOUNT) / COUNT(NAME) AS AVG_BYTE_COUNT ,SUM([ROWCOUNT]) / COUNT(NAME) AS AVG_ROW_COUNT ,CAST(MIN(TIMESTART) AS DATE) AS FIRST_RUN ,CAST(MAX(TIMESTART) AS DATE) AS LAST_RUN --,RE.USERNAME FROM CATALOG CAT LEFT JOIN ( SELECT EL.REPORTID ,EL.TIMESTART ,EL.TIMEDATARETRIEVAL ,EL.TIMEPROCESSING ,EL.TIMERENDERING ,EL.BYTECOUNT ,EL.[ROWCOUNT] --,EL.USERNAME FROM EXECUTIONLOG EL) AS RE ON CAT.ITEMID = RE.REPORTID WHERE CAT.TYPE = 2 GROUP BY RE.REPORTID ,CAT.NAME ,CAT.PATH --,RE.USERNAME ORDER BY NAME ,PATH;

ERROR: User does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed.

This happens when you are on the server and want to administer report content from the server.  In short, the problem is that the local user does not have permissions. It appears to be a bug in the how permissions get set up within SSRS.

RESOLUTION

Run IE, Firefox or whatever as administrator

Navigate to http://localhost/Reports - (Do not use http://<server name>/Reports)

From the Root Folder, Click on  "Folder Settings" as below



Click on New "Role Assignment" and add "Content Manager" to the person you want to administer your content

SSRS Divide by Zero Handling Alternative

So in creating an SSRS report recently which contained percent calculations in the totals I came across a divide by zero error I could not get around.  All the checks for null/0 in the world wouldn't get rid using the expression editor.




Here is the formula for the detail level where it works fine:


Margin = (Price-Cost)/Price


and checking for zero values


Margin = Iif((Price-Post)=0 or Price=0),0, (Price-Cost)/Price




But for totals, it throws an error




Margin = (Sum(Price) - Sum(Cost))/Sum(Price)


Margin = Iif(((Sum(Price)-Sum(Cost))=0 or Price=0)),0, (sum(Price)-sum(Cost))/sum(Price)




SHOULD be straight forward,,,,, but not.  It appears to be too complex in some way for the expression to be calculated and just returns errors.




So, and I'm borrowing here, I'm going to repost something simple but brilliant that was posted by William Mendoza on his blog site:




In the Menu; got to Report > Report Properties > Code and paste the code bellow

Public Function Quotient(ByVal numerator As Decimal, denominator As Decimal) As Decimal If denominator = 0 Then Return 0 Else Return numerator / denominator End If End Function
 
To call the function go to the the Textbox expresion and type:
=Code.Quotient(SUM(fields!FieldName.Value),SUM(Fields!FieldName2.Value))


 in this case I am putting the formula at the Group level so I am using sum. Otherwise it would be:
=Code.Quotient(fields!FieldName.Value,Fields!FieldName2.Value)


Thanks William for your post and help