Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to generate Total data intek per day for individual DB

Posted on 2016-11-15
6
Medium Priority
?
47 Views
Last Modified: 2016-11-18
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
0
Comment
Question by:michalek19
  • 3
  • 2
6 Comments
 
LVL 35

Expert Comment

by:Pawan Kumar
ID: 41889111
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
 

Author Comment

by:michalek19
ID: 41889783
Do you have any good query to pull this information?
0
 
LVL 35

Expert Comment

by:Pawan Kumar
ID: 41890765
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 66

Expert Comment

by:Jim Horn
ID: 41891938
What's an 'intek', and what exactly do you mean by 'How to generate Total data intek per day'?
0
 

Author Comment

by:michalek19
ID: 41892134
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
 
LVL 35

Accepted Solution

by:
Pawan Kumar earned 2000 total points
ID: 41892426
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

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Screencast - Getting to Know the Pipeline
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question