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

Travis
  • 870
  • 8
  • 23
  • 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 00: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 01:52
  • @Rex shadow copies are disabled and show 0 bytes used. Tempdb growth would show up in file/folder stats wouldn't it. Still in a MDF file If I remember correctly. – Travis Sep 19 '14 at 01:58
  • It looks like your tlogs are truncating correctly, else they would have little free space and be large. How does windirstat handle symlinks? Perhaps those lost gigs are not really lost. Very odd anyhow. – Mark Henderson Sep 19 '14 at 02:12
  • @MarkHenderson running the program with elevated privileges netted no difference. Number of files was about 20 more than without and file sizes did not change. – Travis Sep 19 '14 at 02:13
  • @MarkHenderson a bug in the software is that it does miscount symlinks as larger than they are. For my what I'm using it for, simply as a comparison tool I don't think it would flae my data. 191.2 GB in C:\ProgramData today and 191.3 GB tomorrow to me means that it is bigger but not 2 GB bigger. I wouldn't think symlinks would change on the server daily either but I may be wrong. – Travis Sep 19 '14 at 02:24

3 Answers3

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.

Katherine Villyard
  • 18,550
  • 4
  • 37
  • 59
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

Community
  • 1
Kate
  • 652
  • 5
  • 18
  • 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 02: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

Travis
  • 870
  • 8
  • 23