0

My requirement is to know the size occupied(count of records and size in mb) by a table during every 24 hours, I found query to get the whole size of a table but not sure on where to start with or how to achieve this.

I have an audit column(record_inserted_time) by which I can filter the records and get the count but how to know the size occupied during this period by the table(size of the filtered records).

I have to get this stats for around 100 tables all of which has the same audit column

  • If you are looking for total table size and rows, I would suggest you clarify the question by removing reference to "after filtering the rows." It is a bit confusing because if you want the totals for the entire table, that information is irrelevant and it makes me wonder if you're wanting the size of the filtered rows or the size of the table. – Tony Hinkle Jan 24 at 14:41
0

You are not going to get accurate figures for space used back in time because of differences in how efficiently things are laid out in the pages as your data changes over time.

Your "inserted date" column will give you an estimate of the number of rows present, by taking the current row count and subtracting the count of rows inserted since the date you are measuring for (or just counting those inserted on/before that date). It may give an accurate reading rather than an estimate if:

  • the table is never deleted from
  • the audit date is only updated on insert not also on update
  • any and all updates are performed with UPDATE or MERGE and not DELETE-then-reINSERT.

You can use the row-count-at-a-date-in-the-past to estimate the total size of the pages it occupied by taking calculating (data_size_now * rows_then) / rows_now.

That is for trying to look back in time when you haven't kept logs. Maintaining accurate records going forward is easy: setup a job that runs the analysis for each table and have it store the results for later review.

0

I think this does exactly what you need.

You can just set up a SQL Agent job to run the query every 24h and insert the results to a table in your management database, if you have one, or in any other DB you see fit.

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.