how to find in the database, from last one week which table size is increasing ananymously.
asked Apr 19, 2011 at 03:41 AM in Default
Have you got a backup from last week? If so, you could restore it to a new database, and run something like Atlantis's Data Space utility against both databases to see which tables are of different sizes... If you have any questions on the utility itself, then AskSSC's very own @Matt Whitfield will, I'm sure, be glad to help!
You would need to know how big the table(s) were last week.
Do you capture this info - I'm guessing not by the question!
So without the aid of a list of tables and their sizes, what can you do? Have you got a copy of the database backup from last week? Can you safely restore it somewhere? Or even virtually mount it (as discussed here and here)?
In the absence of all these - maybe now is the time to start monitoring table/file/database sizes, so that when you get unexpected growth again in the future, you have a better place to start to find the issue.
There are many commercially available trending/monitoring tools, and even free ones, but you could build your own simply by capturing table sizes on a regular basis.
If you have a minimum o updates and mostly only writes to the tables, when you can count the amount by data pages changed since last full backup. (you do not need the last backup for that).
For this, you can find inspiration in the Pul S. Randal article and script: How much of the database has changed since the last full backup?
You can modify the script to collect the information per table and as a result you will see, in which table was highest amount of data changed. In case you have mostly inserts, you will see in which table most data was inserted.
Another option in case of mostly inserts could be to count number of pages per table, which LSN is higher, than LSN of particular backup from last week.
Both of the scenarios counts changed pages so as I mentioned it could help inc case you know, that minimum of data is modified and a lot of data is inserted. This depends on your situation.
answered Apr 19, 2011 at 06:06 AM