A way to find out how activity for mssql and oracle-优快云博客

本文介绍了一种通过查询SQL Server和Oracle数据库中特定时间段内的备份文件大小来评估数据库活动的方法。对于SQL Server，提供了查找最活跃数据库及每日交易日志备份大小的SQL语句；对于Oracle，则提供了检查归档日志文件大小的SQL查询。

Dear buddy,

Have you confuse that how activity about my databases, if we conside it by using backup size about archive files(oracle) and transaction log

(mssql). we can use the following way.

First , use the script to find out which DB generate the most transaction size under the same instance in Msssql.

select database_name,

SUM(cast(backup_size/1024.0/1024.0 as numeric(11,1))) as backup_size_MB

from msdb.dbo.backupset a with (nolock)

join msdb.dbo.backupmediafamily b with (nolock) on b.media_set_id = a.media_set_id

where type='L' and (backup_finish_date >= '2018-01-10' AND backup_finish_date < '2018-01-11')

group by database_name

then we can get

Yes, you can see the most big transacrion log backup size DB. That is C4.

Seconday , we can use the follow script to check how much size of archive files generated each day or how much of traction log was backuped.

Oracle:

select SUM(blocks)*512/1024/1024 from v$archived_log where completion_time >= TO_DATE(20180107,'yyyymmdd') and completion_time <TO_DATE(20180108,'yyyymmdd') and thread#=1

union all

select SUM(blocks)*512/1024/1024 from v$archived_log where completion_time >= TO_DATE(20180107,'yyyymmdd') and completion_time <TO_DATE(20180108,'yyyymmdd') and thread#=2

MSSQL:

select

SUM(cast(backup_size/1024.0/1024.0 as numeric(11,1))) as backup_size_MB

from msdb.dbo.backupset a with (nolock)

join msdb.dbo.backupmediafamily b with (nolock) on b.media_set_id = a.media_set_id

where type='L' and (backup_finish_date >= '2018-01-10' AND backup_finish_date < '2018-01-11')

--- and database_name in ('')