September 15, 2014 at 7:04 am
I am new to SQL server database administration and currently stuck with an issue where I need to reclaim the unused table space in SQL server.
ISSUE: Due to database size issue we have moved all the images from database to filesystem and want to reclaim the unused space now....I have tried shrinking the database and rebuild the indexes but didn't see any difference in the table or database size, so can someone help me reclaim this unused space.
Simillarly I have identified many Non clustered indexes on big transactional tables(~ 4 million records) that where not used since GO live 1 year back, so I wanted to drop these indexes to cut down the performance and maintenance overhead , so my question is will dropping these indexes reduces the database size?
Thanks
Shashi
September 15, 2014 at 10:57 am
For the first part about images, we'd need to know how the images were stored.
For the second part, removing data from the database, whether by deleting data or dropping objects (indexes), never releases space back to the OS, it just creates free space in your data files.
In these situations, I usually recommend against shrinking the database files since that can cause fragmentation which means you need to defrag indexes, which may cause the database to grow because it needs the space to rebuild the indexes. And also because the database has grown to a certain size over time and is likely to grow back to that size again, so you can save yourself some trouble by leaving the free space in the database file(s).
ShrinkDatabase always shrinks from the end of the file so you may need to rebuild indexes to get the file to shrink. Also it won't shrink below the minumum size of the database/file as set at creation of the file or by a previous shrink or alter database. You should check out the BOL entry for SHRINKDATABASE if you haven't already.
I also recommend doing SHRINKFILE instead of SHRINKDATABASE so I know exactly what I'm shrinking instead of doing both data and log files at once. I definitely don't recommend shrinking the log file unless you have data available to tell you what size it needs to be.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 15, 2014 at 12:06 pm
A little more info can help us better see what you are seeing.
Run the script from this site to get a better view of the tables affected by your image storage. Occasionally, if the data is removed with an image data type, then something more needs to be done. But we need to know if the space has been freed before going down that road.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 15, 2014 at 12:10 pm
Thanks for the reply Jack!
We used to store the document images in a table with a column of image dataType and later when we decided to move these images from database to the filesystems we have added a column named ArchiveFilePath that stores the UNC path of the file location where we moved the image from the database.
As you said if deleting the data or database objects will never releases the space back to the OS and creates the free space in the data files , I am just curious whether this free space is again used by the database.
Shashi
September 15, 2014 at 12:54 pm
shashi_1409 (9/15/2014)
Thanks for the reply Jack!We used to store the document images in a table with a column of image dataType and later when we decided to move these images from database to the filesystems we have added a column named ArchiveFilePath that stores the UNC path of the file location where we moved the image from the database.
As you said if deleting the data or database objects will never releases the space back to the OS and creates the free space in the data files , I am just curious whether this free space is again used by the database.
Shashi
When storing as image datatype, sometimes that space isn't even fully released back to the database. Occasionally, to reclaim that space you actually have to redefine the table and do a mini-table migration. For the times that the space is released (you can tell through the use of the script I provided), then the space is available for reuse by any object within the database.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 15, 2014 at 12:59 pm
Thanks Jason!...will try your suggestion and see if it addresses my issue.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply