1

Using sql server can i get the row count and size of the table as well as the database size for the past six months?

  • 1
    If you have datetime as a column and not modified, you can get the row count. Post your table definition. – SqlWorldWide Nov 20 '17 at 14:50
5

Unless you are tracking tables, table size can not be known. To get current size:

sp_spaceused 'TableName'

To get row count:

Select count(*) from 'TableName'

To get row count with a timestamp field:

    Select
          DATEPART(YEAR, 'timestamp_field') AS 'Year',
          DATEPART(MONTH, 'timestamp_field') AS 'Month',
          DATEPART(DAY, 'timestamp_field') AS 'Day',
          COUNT(*) AS Check

    from
         'TableName'
    group by
         DATEPART(DAY, 'timestamp_field'),
          DATEPART(MONTH, 'timestamp_field'),
          DATEPART(YEAR, 'timestamp_field')  
ORDER BY  'Year',  
          'Month',  
          'Day'

Database growth:

SELECT
[database_name] AS DB,
DATEPART(day,[backup_start_date]) AS Day,
DATEPART(month,[backup_start_date]) AS Month,
DATEPART(year,[backup_start_date]) AS Year,
AVG([backup_size]/1024/1024) AS "Backup Size MB",
AVG([compressed_backup_size]/1024/1024) AS "Compressed Backup Size MB",
AVG([backup_size]/[compressed_backup_size]) AS "Compression Ratio"
FROM msdb.dbo.backupset
WHERE [database_name] = N'AdventureWorks'
AND [type] = 'D'
GROUP BY [database_name],DATEPART(m,[backup_start_date]),DATEPART(d,[backup_start_date]),DATEPART(y,[backup_start_date]),backup_start_date;  

for database size details. Check this link
Create table size tracking report

  • >>>updated the code<<< The code that calculates backup size still adds all the years under the same month – sepupic Nov 20 '17 at 15:24
2

If you did not set up Data Collector or any custom job that saves periodically database/table sizes you cannot get these sizes in the past

1

There's no built-in mechanism for SQL to track table or database growth over time. Lots of 3rd-party monitoring tools can do this.

Where I work we have custom daily SQL jobs that query the database sizes (and lots of other info) on all our servers and store it in a DBAInfo database so we can query it later if necessary.

If your table has a "created_date" column or something equivalent, then you can use that to get some results for your table:

SELECT YEAR(created_date) as yr, MONTH(created_date) as mth, 
       COUNT(*) as rws
FROM mytable
GROUP BY YEAR(created_date), MONTH(created_date)
ORDER BY YEAR(created_date), MONTH(created_date)

(WARNING: this query will run for a very long time on a good-sized table.)

To estimate table size growth per month, just use the above row counts by month, and divide by the total table size, which you can get from sp_spaceused 'TableName'.

If you want the actual row sizes as stored in your table, that's much more difficult, you'd have to do some weird stuff with DATALENGTH(), see this post for some ideas.

EDIT: To use the output of sp_spaceused, set up a temp table, then parse the string:

create table #tbl(
  name nvarchar(128),
  rows varchar(50),
  reserved varchar(50),
  data varchar(50),
  index_size varchar(50),
  unused varchar(50))

insert into #tbl exec sp_spaceused 'dbo.AccountLogons'

select name, rows,
    convert(int, substring(reserved, 1, len(reserved)-3)) as reserved_kb,
    convert(int, substring(data, 1, len(data)-3)) as data_kb,
    convert(int, substring(index_size, 1, len(index_size)-3)) as index_kb,
    convert(int, substring(unused, 1, len(unused)-3)) as free_kb
 from #tbl

drop table #tbl

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.