------------------------------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
Practical Business Intelligence Solutions using the
Microsoft BI Suite of Tools provided along with Microsoft SQL Server
Wednesday, November 26, 2014
Monday, November 24, 2014
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; |
Subscribe to:
Posts (Atom)