Solved

How to generate Total data intek per day for individual DB

Posted on 2016-11-15
6
25 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 17

Expert Comment

by:Pawan Kumar Khowal
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 17

Expert Comment

by:Pawan Kumar Khowal
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 65

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 17

Accepted Solution

by:
Pawan Kumar Khowal earned 500 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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now