| Author |
Topic |
|
mark_b
Starting Member
24 Posts |
 Posted - 2007-08-08 : 08:57:17
|
| All,I think i know the answer to this one already, but would like to check before going back to my management.Background. In the past 2 weeks, a number of our databases have shot up in size, and are now at 100% utilisation of allocated disk space. My management have asked me to look into what is causing these to fill up so quickly.Unfortunatly there were no snap shots or information relating to the databases / tables so i can not determine which tables have grown and are causing the problems.I have also looked through the sql logs and the event viewer logs to see if there is anything out of the ordinary, but again apart from log / database backups there is nothing of note in there.I am going to be implementing a solution that I got off another thread which will give me some database / table history to help me in the future, but for now is there anything else i can do? or is it a case of me getting back to the application guys and getting them to reduce data (as there is no more disk space to give them).Any thoughts or advice you can give me would be greatfully received.Mark-noob dba- |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-08-08 : 10:09:08
|
| are the datafiles totally full, or is there a significant amount of empty space in them? I have a feeling that you ran an optimization job and that caused the datafiles to autogrow and fill all available disk space. -ec |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-08-08 : 10:36:37
|
| Database files or log files?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
mark_b
Starting Member
24 Posts |
Posted - 2007-08-08 : 12:15:16
|
| Database files.Update on this is that it would appear that the training area have just implemented some new elements into the training area which require more database space (but obviosuly didnt tell me about it!) which is what is causing the big jump over a short space of time! so in terms of this problem it would appear to be resolved.from a purely academic perspective though, apart from monitoring databases and tables in a historic fashion, was there any other method i could of used to identify where the space inside the database files was being filled up? I cant think of anywhere, but as i have said previously, i am still new at this DBA thing, and am sure i know 0.0000005% of what there is to know!Thanks for the help on this and my other threads. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-08-08 : 13:11:48
|
| What's your sql version? There is db file growth history report in sql2k5, and you can use 'sp_spaceused' to find out used space and free space in the db. |
|
|
|
mark_b
Starting Member
24 Posts |
Posted - 2007-08-08 : 13:46:57
|
| we are on SQL 2K at the moment (but slowly moving to 2K5), and have been using the sp_spaceused proc which gives me a point in time, but not history. I have now written a script which will store table space for each table on a database with a date and time, so this will be able to give me history in the future in case this happens again and i get the question from management, i can then identify which database, and which tables have grown significantly over the period. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-08-08 : 14:15:01
|
| If you have backups of your system, then how about restoring those to a test machine and then do compares between those and production to see what has changed?Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
mark_b
Starting Member
24 Posts |
Posted - 2007-08-08 : 14:28:04
|
| had that thought ... but the cheap skates here dont have a test machine that has the same disk capacity as prod! but nice thought :) |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-08-08 : 18:00:29
|
quote:
Originally posted by rmiao
There is db file growth history report in sql2k5
Is that one of the inbuilt reports in SSMS or are you talking about a dm view? Would you mind pointing me in the right direction please? I've recently spent some time putting together our in house file & disk monitoring....  |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-08-08 : 21:10:36
|
| It's buildin report. |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-08-09 : 05:36:30
|
| Hmmm. I've recently installed SP2 on my client tools to use the performance dashboard (http://www.microsoft.com/downloads/details.aspx?FamilyId=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en). My Reports tab has now gone but I can access these reports via the new method (righht click stuff in the object pane). I can't, however, find anything that shows historical information. Please could you let me know the exact report name in case I am just being dim? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-08-09 : 07:26:30
|
| I often implement something to record the space used by all tables every day or weekly.Can just get rows from sysindexes or use sp_spaceused.seehttp://www.nigelrivett.net/SQLAdmin/SpaceUsedAllTables.html==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-08-09 : 22:16:38
|
| Right click on the db in ssms and go to reports -> standard reports -> disk usage. |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-08-10 : 06:23:49
|
quote:
Originally posted by rmiao
Right click on the db in ssms and go to reports -> standard reports -> disk usage.
Ah - thanks for that. I had seen that one. It was the growth history that got me excited. This is just a graphical layout of the current space allocations, rather than something that could be immediately used for trend analysis. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-08-11 : 00:34:24
|
| It shows data/log files autogrow/autoshrink events also, if ever happened in the db. |
|
|
|
|
Disk Space investigation