Identifying cause of decreasing free disk space

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Identifying cause of decreasing free disk space

Author

Topic

MorrisK
Yak Posting Veteran

83 Posts

Posted - 2008-08-05 : 18:50:26

I could really use some advice to determine what is causing the free space to decline on our server.

I've been using xp_fixeddrives for the last couple of years to monitor the free space on our lone sql server. I have a job setup to run this at the same time each day. This method seems to agree with the amount of free space shown by Windows Explorer.
The capacity of the data drive is 136GB. The average free space was pretty constant at about 80GB the last 3-4 months of 2007 and through the first 2 months of 2008.

Since March the free space has dropped at a fairly constant rate (average of about 1.5GB a day!) so that now we only have about 26GB free. (In case you try to calculate it out I removed some old files that were no longer needed.)

I've just setup more detailed logging using DBCC SHOWFILESTATS so I can look at the growth of individual databases overtime.

At this point I've only two days worth of information but the data I'm getting from DBCC SHOWFILESTATS doesn't seem to indicate any of the databases grew at all yesterday.

The script that I found to get this information runs DBCC UPDATEUSAGE before DBCC SHOWFILESTATS. My understanding is that makes sure the size data is current.

It just doesn't look like the Sql Server data files are the problem.

Any suggestions? What could I be missing?

Thanks in advance,

Kevin

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-08-05 : 23:04:31

How about db log files? Do you use the disk for other files? Did you monitor db files' size? Did you check free space in dbs? Did you check table/index fragmentation? ...

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2008-08-06 : 05:14:04

msdb history....are you purging/curtailing backup history?
it's possible the problem is in the sytem data bases and not the user ones?
is tempDB growing?

MorrisK
Yak Posting Veteran

83 Posts

Posted - 2008-08-06 : 11:52:20

Thank you both for your response. The system databases do not appear to be growing. Backups are made to tape and are not stored on the same drive. Log files also do not appear to be growing and are also kept on a different drive. My server has three drives - c:\ (programs mainly), d:\ (sql .mdf files mainly) and e:\ (sql .ldf files mainly).

When I say data and log files are not growing I mean the size or space allocated. There is some change in the space used but it's not significant.

Just to make sure I'm looking at this right - it's the size (not space used) that I'm concerned with - correct? Since it appears there's no change in size for any of the databases it seems like I need to be looking at other file types on this drive - correct?

One last thing the .mdf files only add up to about 45GB when I select them in Windows Explore so I'm still puzzled as to what is taking up the remaining 64GB (136 total - 45 .mdf files - 27 free)

I'm still searching for answers so, again, thanks for the help.

Kevin

MorrisK
Yak Posting Veteran

83 Posts

Posted - 2008-08-06 : 12:38:29

I think I've found the problem. I right clicked the D:\ drive, selected Properties and then the Shadow Copies tab. All the drives were listed and in the "Next Run Time" column all said "Disabled". However, the "Used" column for the C:\ drive said approximately 65,000MB.

I selected C:\ and clicked the Settings button and the "Storage Area - Located on this volume" box had the D:\ drive selected. "No limit" was also selected for the Maximum size.

I selected "Use limit" and I believe the default value was 13999MB. I clicked OK and when returned to the Shadow Copies tab "Used" for C:\ now said 13,845MB.

I also checked in Windows Explorer and D:\ free space is now 75GB! Now that's more like it.

I'm not sure who set this up or why. I don't really see any need for this on a SQL server, do you?

Kevin

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-08-06 : 22:27:31

What kind of storage do you use?

MorrisK
Yak Posting Veteran

83 Posts

Posted - 2008-08-07 : 09:45:33