0

We are running SharePoint 2010 with MS SQL Server 2008 (Windows Server 2008 R2 Enterprise). We have not taken regular SP backups this far which has led to high disk usage. However, even if I shrink databases, we still have a strange disk usage issue. If I take a listing of the all database disk usages, their total disk usage is 21 GB (plus logs taking 9 GB). Still, the allocated hard disk space is 145 GB. If I look at the files in the hard disk using Windows Explorer, I only find a few little files even when hidden files are shown.

The hard drives of the machine are:

  • C (System drive); 32 GB allocated of 40 GB
  • D (SQL Server); 8 GB allocated of 15 GB
  • E (SQL-DB); 145 GB allocated of 180 GB
  • F (SQL-Logs); 8 GB allocated of 30 GB

If I shrink database logs, it seems that F drive receives free space. Shrinking other databases does not seem to release space on E - even if it did, E still uses 145 GB (versus 21 GB of database sizes in total). Naturally, SQL Server might not release disk space without explicitly telling it to do it. Still, the database size has never been even close to 145 GB but at most some 30 GB.

Where does the space go?

Below is the script I use to retrieve database sizes in MS SQL Server Management Studio. It seems to list all the databases as I compare it to the databases treeview.

with fs
as
(
    select database_id, type, size * 8.0 / 1024 size
    from sys.master_files
)
select 
    name,
    (select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeMB,
    (select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeMB
from sys.databases db
Improve this question
2
  • Have you checked for shadow copies, Recycle bin etc.?
    – dwjv
    Oct 16, 2015 at 9:57
  • No shadow copies exist, and recycle bin is empty.
    – Petri K
    Oct 16, 2015 at 11:38

1 Answer 1

Reset to default
0

Try running a tool like http://www.uderzo.it/main_products/space_sniffer/ (or one of the many alternatives) to scan the drive and see what is taking the space. Remember to run it elevated so that it can see any protected files that might be large enough to be significant.

Improve this answer
2
  • I feel stupid, really stupid now. The E drive is filled by BizTalk 2010 logs and backups. I am almost sure this is the first time they appear in Windows Explorer (though I cannot see why such files would not appear). I first discovered the files using the Space Sniffer application. Thank you for help!
    – Petri K
    Oct 16, 2015 at 12:36
  • Just to report, I think the files did not appear in Windows Explorer as they were too many. As Windows did not seem to have enough resources to process such a number of files, I had to use the command line to delete them. Even there, a "dir" command did not succeed as such (the console got unresponsive) but with the option "/b" where only filenames are printed.
    – Petri K
    Nov 26, 2015 at 8:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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