0

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

2 Answers 2

1

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.

3
  • 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, 2012 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, 2012 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, 2012 at 13:50
0

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/

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .