March 7, 2025 at 12:00 am
Comments posted to this topic are about the item Tracking Table Sizes
March 7, 2025 at 8:31 am
Even if you have immense amount of storage what you won't have is infinite budget and time for processing. A job that must run within a time window eventually can't.
DBs like Snowflake and BigQuery can handle huge quantities of data but you will pay for it.
Table storage is a good indicator of growth so I'd regard it as the bare minimum. I'd also track job execution times.
If you authentication using some form of Single-Sign-On means that you might not have visibility of new users until they start hitting your DBs so they need tracking also.
I'd also have a means of tracking the equivalent of SQL Server's extended events for things like memory and CPU utilisation.
In one company I worked for we tracked a large number of metrics and our high uptime was fuelled by prevention rather than cure.
I think a good monitoring regime is one of those things where it is good to start thinking about it early as a foundation stone rather than find out you need it later.
March 7, 2025 at 9:08 am
Primarily we track disk space and database sizes. If a disk starts to get full, like it did earlier this week, we look to see which database(s) have grown and address them. We don't normally track table size, in the past few years we've only had once instance of a table, and therefore database, having a 'runaway' growth. I guess we're lucky, but then our purpose is Business Intelligence and Reporting which is important but not mission critical.
In answer to Steves' question, for us the total size of the database is enough.
March 7, 2025 at 1:04 pm
For me, the question is, why wouldn't you track table size? It doesn't take much time to create a table, a sproc to query the DBs in question and populate that table with the sizes, and a job to schedule that sproc. Top it off with a line of code to delete data older than x months, and you have all the history you want. Another few minutes to write a query to interrogate the table and you have all the answers you want.
Whether or not it's explicitly stated, as the DBA, I'm responsible for monitoring space of our DBs, so if I tell management that they need to spend money on extra storage, I want to be prepared to justify it. If I'm asked why's the data's grown so much (ignoring my potential response of "It's your application"), I want to be able to tell them that this table's grown by x GB during y days. Maybe we can use that information to track down what we did to cause that table growth. Or to estimate further growth if we carry out the same or other processes.
I certainly don't want to have to meekly say "I don't know"!
So, if only for my own self-protection, yes, I definitely want a history of table sizes.
March 7, 2025 at 1:05 pm
This was removed by the editor as SPAM
March 7, 2025 at 2:14 pm
I track tables sizes daily using DBA Tools using Get-DbaDbTable and log them to a DBA Utilities database. I also have an SSRS report to display that data in a nice graph that also calculated the average daily growth in both size and row count. I find it to be very valuable.
March 7, 2025 at 3:07 pm
Another table size tracker here! Although at the moment it is a weekly job. I use the same SQL as the Disk Usage by Top Tables report from within SSMS.
Found this most useful in helping identify which databases where still in use and having new rows added to them.
March 7, 2025 at 5:32 pm
Nice to see I'm not alone. I agree this isn't that hard, and it's nice to keep an eye on if something goes sideways.
March 7, 2025 at 7:52 pm
I've never had to do this before, but I love the idea. I track everything I can think of, and table sizes would be a great addition.
Be still, and know that I am God - Psalm 46:10
March 10, 2025 at 2:02 pm
We have Ola Hallengren index/statistics maintenance deployed on each of our servers. I created a custom Grafana dashboard to analyze the CommandLog table, so I can get a report of how long the jobs run at the aggregate level, and also at the index level, it contains logic to highlight runtime or fragmentation where it exceeded 30 day standard deviation. While this is not tracking table sizes per se, it is essentially tracking index sizes or the effect they have on the index maintenance schedule.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 11, 2025 at 10:00 am
Using some crude tracking with SP_DATABASE_TABLE_SIZE. Lately switched to extended events database file changes to identify the culprit or using query story to find the queries just before that happened
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply