Hourly and Daily basis_Log_backup_size in log shipping

Question

Hourly and Daily basis_Log_backup_size in log shipping

manu.manohar033

Old Hand

Points: 328
More actions
December 15, 2017 at 1:07 am

#402753

Hi,
I want to know with script, Hourly log backup size in log shipping. Hourly and daily basis.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

manu.manohar033 - Friday, December 15, 2017 1:07 AM
Hi,
I want to know with script, Hourly log backup size in log shipping. Hourly and daily basis.

Welcome to SSC Manu,

Please take a look at MSDB tables, primarily backupset, and the other logshipping tables, that will give you the information you need.

If you get stuck please post back with the query you have generated along with what is not working for you specifically.

manu.manohar033 Old Hand Points: 328 More actions · Answer 2

select subquery.DATABASE_NAME,
   SUM(bkSize_KB) as SumBkSize_KB,
   SUM(bkSize_MB) as SumBkSize_MB,
   SUM(bkSize_GB) as SumBkSize_GB
from
(
   SELECT
    A.[Server],
    A.DATABASE_NAME,
    A.last_db_backup_date,
    B.backup_start_date,
    B.expiration_date,
    CAST(b.backup_size / 1000 AS INT) AS bkSize_KB,
    CAST(b.backup_size / 1000000 AS INT) AS bkSize_MB,
    CAST(b.backup_size / 1000000000 AS INT) AS bkSize_GB,
    B.logical_device_name,
    B.physical_device_name,
    B.backupset_name,
    B.description
   FROM
    (
    SELECT
     CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
     msdb.dbo.backupset.database_name,
     msdb.dbo.backupset.backup_finish_date AS last_db_backup_date
    FROM msdb.dbo.backupmediafamily
     INNER JOIN msdb.dbo.backupset
     ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
    WHERE msdb..backupset.type = 'l'
    --GROUP BY
    -- msdb.dbo.backupset.database_name
    ) AS A

LEFT JOIN

    (
    SELECT
    CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
    msdb.dbo.backupset.database_name,
    msdb.dbo.backupset.backup_start_date,
    msdb.dbo.backupset.backup_finish_date,
    msdb.dbo.backupset.expiration_date,
    msdb.dbo.backupset.backup_size,
    msdb.dbo.backupmediafamily.logical_device_name,
    msdb.dbo.backupmediafamily.physical_device_name,
    msdb.dbo.backupset.name AS backupset_name,
    msdb.dbo.backupset.description
   FROM msdb.dbo.backupmediafamily
    INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
   WHERE msdb..backupset.type = 'l'
    ) AS B
    ON A.[server] = B.[server] AND A.[database_name] = B.[database_name] AND A.[last_db_backup_date] = B.[backup_finish_date]
   WHERE backup_start_date BETWEEN DATEADD(hh, -24, GETDATE()) AND GETDATE()

) subquery

group by subquery.DATABASE_NAME

manu.manohar033 Old Hand Points: 328 More actions · Answer 3

Above script showing only hourly, I want calendar date and hourly and day wise

Ant-Green SSC Guru Points: 113507 More actions · Answer 4

manu.manohar033 - Friday, December 15, 2017 1:43 AM
Above script showing only hourly, I want calendar date and hourly and day wise

That query seems overly complex for what the original post details.

Can you do a sample mockup of your expected outcome?

manu.manohar033 Old Hand Points: 328 More actions · Answer 5

I want this pattern output in primary server log shipping.

Ant-Green SSC Guru Points: 113507 More actions · Answer 6

And how are you calculating the hourly size? Are you only bothered about the last hour, every hour, do you want 24 columns one for each hour, is this done per database or per server, so you have multiple lines or one line? Do you only want it for the specific day, or you want historical

manu.manohar033 Old Hand Points: 328 More actions · Answer 7

I want log backup size in hourly/daily (sum of size) in log shipping. Historically last 10 days.

Ant-Green SSC Guru Points: 113507 More actions · Answer 8

manu.manohar033 - Friday, December 15, 2017 4:00 AM
I want log backup size in hourly/daily (sum of size) in log shipping. Historically last 10 days.

But how are you calculating hourly? AVG by hour? Exact by last hour? 24 columns with exact for each hour?

manu.manohar033 Old Hand Points: 328 More actions · Answer 9

select subquery.DATABASE_NAME,
SUM(bkSize_KB) as SumBkSize_KB,
SUM(bkSize_MB) as SumBkSize_MB,
SUM(bkSize_GB) as SumBkSize_GB
from
(
SELECT
A.[Server],
A.DATABASE_NAME,
A.last_db_backup_date,
B.backup_start_date,
B.expiration_date,
CAST(b.backup_size / 1000 AS INT) AS bkSize_KB,
CAST(b.backup_size / 1000000 AS INT) AS bkSize_MB,
CAST(b.backup_size / 1000000000 AS INT) AS bkSize_GB,
B.logical_device_name,
B.physical_device_name,
B.backupset_name,
B.description
FROM
(
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date AS last_db_backup_date
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset
ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE msdb..backupset.type = 'l'
--GROUP BY
-- msdb.dbo.backupset.database_name
) AS A

LEFT JOIN

(
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name,
msdb.dbo.backupset.description
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE msdb..backupset.type = 'l'
) AS B
ON A.[server] = B.[server] AND A.[database_name] = B.[database_name] AND A.[last_db_backup_date] = B.[backup_finish_date]
WHERE backup_start_date BETWEEN DATEADD(hh, -24, GETDATE()) AND GETDATE()

) subquery

group by subquery.DATABASE_NAME

manu.manohar033 Old Hand Points: 328 More actions · Answer 10

manu.manohar033

Old Hand

Points: 328

December 15, 2017 at 4:15 am

#1972277

above script for daily, you mention -1 (for hourly)

Ant-Green SSC Guru Points: 113507 More actions · Answer 11

manu.manohar033 - Friday, December 15, 2017 4:15 AM
above script for daily, you mention -1 (for hourly)

So for the hour, you just want the last hours size? So at the time of me posting this 13:50, you want the size of the logs for the 12th hour >=12:00 < 13:00?

manu.manohar033 Old Hand Points: 328 More actions · Answer 12

Here two scenarios, I want to check daily size of log backup's in log shipping and i want to monitor hourly log backup size

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 13

See the BackupFile table in MSDB for the individual file sizes.

https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/backupfile-transact-sql

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)