Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a Sql server 2008 database that contains many images. In theory they should make up most of the space on the database.

I moved the images to Filestrem fields but the size of the mdf didn't decrease much at all. How is that possible? How can I check what is taking up all this space inside the mdf file? I tried to shrink the mdf file keeping some free space for growth but it wouldn't do much. I am tring to keep the size low enough to be able to keep the mdf file in the faster C hard drive.

I also expected that the Filestream folder would take some more space than it currently does.

Size before the Filestream implementation:

mdf -> 40Gb or less
ldf -> raughly 90Gb

Current size:

mdf -> 39Gb
ldf -> 119Gb
Filestream folder -> 7.8Gb
share|improve this question

2 Answers 2

up vote 1 down vote accepted

I moved the images to Filestrem fields but the size of the mdf didn't decrease much at all. How is that possible?

Because file shrinks are not something that magically happens. It happens when you tell sql server to try to shring it. THere is also the point that blog data is slowly discarded - at a quite low rate. Can take a while for large data.

I am tring to keep the size low enough to be able to keep the mdf file in the faster C hard drive.

That makes me cringe, as long as C is not an SSD ;)

I also expected that the Filestream folder would take some more space than it currently does.

Why?

Or: un a report and find out wha the heck takes space. Maybe you overlooked something? If you expect a lot more data than filestream now has, maybe you di d not move all the data? I would run a report to see what usesu p the space -they are avaiable as standard report in management studio.

share|improve this answer
    
1. as I said I did try to shrink it but the mdf file wouldn´t. 2. The C drive is composed of 2 very expensive SSD for servers :). 3. My feeling says that images take up more than 20% of space. I think 60%. –  Durden81 Mar 30 '12 at 12:17
    
Thanks to the report that you mentioned I found out that the fields in 1 table didn't migrate to Filestream for some reasons.. That's why it was taking so much space on the mdf file. Also I did a shrink to the log file that now has a size of 12Gb. –  Durden81 Mar 30 '12 at 12:22
    
Now I know why the columns lost the filestream attribute.. it was because of SSMS table designer, like it says in this answer: stackoverflow.com/questions/6749684/… –  Durden81 Mar 30 '12 at 13:50

I don't have any experience with filestream and I am using SQL 2005, but I can right-click a db in MSSMS, go to Reports > Standard Reports > Disk Usage and get a nice little pie chart on space used in the mdf. You can also run this query to figure out which tables in the db have the most rows (not necessarily taking up the most space, but may help you out - from http://blog.sqlauthority.com/2009/01/13/sql-server-find-row-count-in-table-find-largest-table-in-database-t-sql/).

SELECT OBJECT_NAME(OBJECT_ID) TableName, st.row_count
FROM sys.dm_db_partition_stats st
WHERE index_id < 2
ORDER BY st.row_count DESC
GO

Lastly, and again I have never done this, I believe you can create multiple mdfs and split up your data between them, keeping the data you want to access quickly on the faster drive. http://blog.sqlauthority.com/2008/01/25/sql-server-2005-database-table-partitioning-tutorial-how-to-horizontal-partition-database-table/

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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