How to generate Total data intek per day for individual DB

Hi


How to generate Total data intek per day for individual DB?

Is there any SQL queries I can run against DB to generate this info?

Regards, m
michalek19Asked:
Who is Participating?
 
Pawan KumarDatabase ExpertCommented:
Try this.

SELECT 
	*
FROM 
MSDB..BACKUPSET

Open in new window


Complete Query from Microsoft
https://gallery.technet.microsoft.com/scriptcenter/f1df9f50-9cd9-4c75-a8d9-e2faba6b8574

-- Transact-SQL script to analyse the database size growth using backup history. 
DECLARE @endDate datetime, @months smallint; 
SET @endDate = GetDate();  -- Include in the statistic all backups from today 
SET @months = 6;           -- back to the last 6 months. 
 
;WITH HIST AS 
   (SELECT BS.database_name AS DatabaseName 
          ,YEAR(BS.backup_start_date) * 100 
           + MONTH(BS.backup_start_date) AS YearMonth 
          ,CONVERT(numeric(10, 1), MIN(BF.file_size / 1048576.0)) AS MinSizeMB 
          ,CONVERT(numeric(10, 1), MAX(BF.file_size / 1048576.0)) AS MaxSizeMB 
          ,CONVERT(numeric(10, 1), AVG(BF.file_size / 1048576.0)) AS AvgSizeMB 
    FROM msdb.dbo.backupset as BS 
         INNER JOIN 
         msdb.dbo.backupfile AS BF 
             ON BS.backup_set_id = BF.backup_set_id 
    WHERE NOT BS.database_name IN 
              ('master', 'msdb', 'model', 'tempdb') 
          AND BF.file_type = 'D' 
          AND BS.backup_start_date BETWEEN DATEADD(mm, - @months, @endDate) AND @endDate 
    GROUP BY BS.database_name 
            ,YEAR(BS.backup_start_date) 
            ,MONTH(BS.backup_start_date)) 
SELECT MAIN.DatabaseName 
      ,MAIN.YearMonth 
      ,MAIN.MinSizeMB 
      ,MAIN.MaxSizeMB 
      ,MAIN.AvgSizeMB 
      ,MAIN.AvgSizeMB  
       - (SELECT TOP 1 SUB.AvgSizeMB 
          FROM HIST AS SUB 
          WHERE SUB.DatabaseName = MAIN.DatabaseName 
                AND SUB.YearMonth < MAIN.YearMonth 
          ORDER BY SUB.YearMonth DESC) AS GrowthMB 
FROM HIST AS MAIN 
ORDER BY MAIN.DatabaseName 
        ,MAIN.YearMonth

Open in new window



Hope it helps !!
0
 
Pawan KumarDatabase ExpertCommented:
You can get information when is the last time table was updated, when it was created , when it was used etc..

other than that I think this is not possible with any auditing mechanism.

You should try out Change Data Capture , Change Tracking or some other custom tracking method.

Hope it helps !!
0
 
michalek19Author Commented:
Do you have any good query to pull this information?
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
Pawan KumarDatabase ExpertCommented:
No there is no query for these. You have to enable these things. For example Change data capture.

Refer links for step by step info. <<CDC  - Change Data Capture>>

https://msdn.microsoft.com/en-IN/library/cc627369.aspx
http://sqlmag.com/sql-server-2012/tracking-changes-sql-server-2012

Hope it helps !!
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
What's an 'intek', and what exactly do you mean by 'How to generate Total data intek per day'?
0
 
michalek19Author Commented:
What I am looking for is to compare  data size between day 1 to day 2,

I believe this will give me  some rough estimate of how much data was entered into do.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.