1

I have a Windows Server 2008 R2 virtual machine running MS SQL 2005 on top of a VMWare ESXi 5.5 host on a SAN datastore.

Consistently it has been "losing" 2GB of hard disk space per day and I can't figure out why. It only appears to occur some time in the evening as I checked the free space on the server yesterday afternoon and it was at 34.7 GB. In the morning I checked and it was at 34.6 GB. In the evening it had lowered to 32.5 GB of free space.

There are no snapshots on the VM. RAM is consistently at around 8 GB with +/- 200 MB fluctuations. Page file is consistent at 16 GB. Does not occur over a weekend period. So on Friday it will be say 30 GB of free space. Monday morning it will be 30 GB of free space then by Monday evening it's at 28.3 GB. I'm out of ideas.

Here is a picture of the directory statistics for yesterday evening and today evening, side by side.

enter image description here

TLOG enter image description here

  • 2
    Have you checked the shadow copies on the server? – Rex Sep 18 '14 at 21:37
  • 1
    I don't recognise that program, but any program like that needs to be run with elevated permissions, otherwise it can't dive into protected folders. – Mark Henderson Sep 18 '14 at 23:40
  • I would also check for maintenance plans inside the SQL Server to see if it's doing an index rebuild, or something that would generate a lot of log data that hasn't had time to be truncated. But then it would typically grow once, but then re-use the space after log truncation. You are taking backups that will cause the log to truncate, right? – Mark Henderson Sep 18 '14 at 23:41
  • tempdb growth?... – joeqwerty Sep 19 '14 at 0:26
  • @MarkHenderson it's WinDirStat. The only DB activities occur at 10 PM or later so from 9 AM until 5 PM when I checked it today there was a definite 2.2G of disk space lost in between that time frame. – Travis Sep 19 '14 at 1:52
2

I've personally run into a VSS bug where shadow copies were turned off but running anyway. the workaround was to turn VSS on and set the maximum size to a very small amount (like 250 meg). It might be worth trying to see if that helps.

| improve this answer | |
  • 1
    Minimum is 320 so I set it to that. We will see how that works out. Thanks. – Travis Sep 22 '14 at 12:38
  • Nope, did not help. I'm down to 25.6 GB of free space which is about 10 GB less than when we first started. – Travis Sep 30 '14 at 12:05
  • Alas. :( Have you used SpaceMonger on it? edgerunner.com/spacemonger – Katherine Villyard Sep 30 '14 at 22:15
  • No but WinDirStat does everything that SpaceMonger does. – Travis Oct 1 '14 at 13:23
  • I tried Space Monger and it showed the same thing as WinDirStat. There is Unscannable space in Space Monger and also in WinDirStat to the tune of 220 GB but that space never increases by GB, just MBs so I doubt anything in that unscannable space is relevant to the issue. – Travis Oct 1 '14 at 14:14
1

In SQL Server, if you have a lot of I/O, you may be collecting a lot of transaction log files. Take a look, and if that's what is causing your issue you have two options:

You can clear them, or you can compress them, depending on what your DBA tells you they want.

Here's Microsoft's instructions for how to shrink the transaction log

To clear the transaction log: How do you clear the SQL Server Transaction Log

| improve this answer | |
  • Updated with screenshot of TLOG stats. Haven't changed much from last time I looked at them a few weeks ago. – Travis Sep 19 '14 at 2:08
1

I figured it out. It was the event log files in %SystemRoot%\System32\Winevt\Logs. This particular server did not have the standard "overwrite events as needed" as all of the other servers did. The log files, however, were not being pruned so they kept stacking up.

This explains exactly why over a weekend period there would be hardly any change in the free space file size.

The WinDirStat software showed this space as "Unknown" space despite "running as Administrator" and I did start to notice the trend of "Unknown" space increasing as I started to track the WinDirState screenshots twice a day.

What eventually led me to the log files was using Disk Space Fan and comparing the reported sizes. As you can see, the Windows folder on WinDirStat shows 24.6 GB while Disk Space Fan shows 116 GB. Drilling down further into Windows\System32 and looking at the "Top List" shown in Disk Space Fan I noticed that WinDirStat did not have the winevt folder and that it showed 89.7 GB of space usage. Deleting all log files older than 90 days freed up nearly 60 GB.

The local administrator group, which has the Domain Admins added to it, had full control over the folder so understanding why WinDirStat did not pick up these folders is beyond me.

enter image description here

enter image description here

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.