Improve company productivity with a Business Account.Sign Up

x
?
Solved

How to generate Total data intek per day for individual DB

Posted on 2016-11-15
6
Medium Priority
?
55 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 38

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 38

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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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 38

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.

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 …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

595 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