how to find which table size is increasing from last week

how to find in the database, from last one week which table size is increasing ananymously.

more ▼

asked Apr 19, 2011 at 03:41 AM in Default

avatar image

906 109 119 123

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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!

more ▼

answered Apr 19, 2011 at 03:47 AM

avatar image

ThomasRushton ♦♦
42.4k 20 60 54

If you have the backup and Red Gate's SQL Virtual Restore then you dont need to restore - it can look into backups and present them as live databases. #JustSayin

Apr 19, 2011 at 04:00 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Apr 19, 2011 at 03:48 AM

avatar image

Kev Riley ♦♦
66.8k 48 65 81

The number of questions here that boil down to monitoring in one form or another is staggering...

Apr 19, 2011 at 03:49 AM ThomasRushton ♦♦

I agree. It does sometimes seem like a majority of the people in this job really don't know what's happening on their servers. Frightening really.

Apr 19, 2011 at 04:01 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Apr 19, 2011 at 06:06 AM

avatar image

Pavel Pawlowski
22.7k 10 15 26

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Apr 19, 2011 at 03:41 AM

Seen: 1208 times

Last Updated: Apr 19, 2011 at 03:41 AM

Copyright 2018 Redgate Software. Privacy Policy