Solved

How to generate Total data intek per day for individual DB

Posted on 2016-11-15
6
38 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 28

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 28

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 28

Accepted Solution

by:
Pawan Kumar 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 is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
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.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

777 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