Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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
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:
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.
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.
