Which Options I have if I don't want to shrink a datafile due to the known performance issues but I need to re-gain some space that the database won't use ever again??

Question

Which Options I have if I don't want to shrink a datafile due to the known performance issues but I need to re-gain some space that the database won't use ever again??

VossSQL

Grasshopper

Points: 17
More actions
January 3, 2014 at 1:02 pm

#286696

Hi.
I understand that SHRINK data/log files should be the last option due to all problems it generates. But how can I proceed on the following situation?
SQL Server 2008 R2. One database has only the primary filegroup. This filegroup has 111 gb of space reserved and it is using only 33 gb. Application team said this database won't grow more than 50 gb. So we want to reclaim the rest of space that this database will never use. How can I do this without shrinking the primary filegroup? Or is shrinking justified in this specific situation?
Thanks!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Do a once-off shrink and then rebuild your indexes afterwards. Don't shrink to the minimum, leave some space free.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

VossSQL Grasshopper Points: 17 More actions · Answer 2

Thanks!

Yeah, I was considering execute a reindex after the shrink to fix the fragmentation generated. I'll do it and let's see how it goes with the fragmentation before and after. 🙂

I was just wondering if there was a different option before create a mess but I think this is the only way. 🙂

Best Regards!

Perry Whittle SSC Guru Points: 233841 More actions · Answer 3

VossSQL (1/3/2014)
Hi.
I understand that SHRINK data/log files should be the last option due to all problems it generates. But how can I proceed on the following situation?
SQL Server 2008 R2. One database has only the primary filegroup. This filegroup has 111 gb of space reserved and it is using only 33 gb. Application team said this database won't grow more than 50 gb. So we want to reclaim the rest of space that this database will never use. How can I do this without shrinking the primary filegroup? Or is shrinking justified in this specific situation?
Thanks!

I'd be tempted to perform a one off shrink to around 75-80GB, that'll hopefully leave enough space for the index rebuilds and natural growth of the data.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

VossSQL Grasshopper Points: 17 More actions · Answer 4

Thanks Perry. I did exactly that. I left the file with 75 gb reserved and it is only using 33 gb now in order to have some space for reindexes and data growth just in case.

Before I ran the shrink command I analyzed the fragmentation and then I did it again after the shrink. Just few indexes were affected and I only rebuilt those.

Thanks! 🙂

Perry Whittle SSC Guru Points: 233841 More actions · Answer 5

What command did you use to check the frags?

Can you post the before and after outputs?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

dan-572483 SSCoach Points: 15870 More actions · Answer 6

When someone writing about SQL server says "Never do this: <insert action here>!" it is almost never 100 percent true. For every action you can do, there exists a set of circumstances that call for it. You just need to understand when you are actually in the sitation that calls for that action, what the ramifications are, and how to mitigate the effects of that action.

VossSQL Grasshopper Points: 17 More actions · Answer 7

Perry Whittle (1/6/2014)
What command did you use to check the frags?
Can you post the before and after outputs?

Hi Perry.

To obtain the fragmentation I made the following query:

SELECT

DB_NAME(ips.database_id) DBName

, o.name TableName

, i.name IndexName

, ips.[avg_fragmentation_in_percent]

, ips.index_type_desc

FROM sys.dm_db_index_physical_stats (

DB_ID (N'DBNAME'), NULL, NULL, NULL, 'LIMITED'

) ips

INNER JOIN sys.sysobjects o ON o.id = ips.object_id

INNER JOIN sys.indexes i ON i.index_id = ips.index_id AND o.id = i.object_id

/*WHERE

avg_fragmentation_in_percent > 0*/

ORDER BY

TableName;

GO

Results were 138 rows, not all of them indexes, some of the tables are heaps. Of all those only 6 indexes/heaps were affected and those are the ones I rebuild.

index_type_desc - avg_fragmentation_in_percent BEFORE SHRINKING - avg_fragmentation_in_percent AFTER SHRINKING

HEAP - 82.25108225 - 98.04347826

HEAP - 61.20271341 - 84.08010694

HEAP - 12.16216216 - 92.95774648

CLUSTERED INDEX - 0.01 - 91.85990864

NONCLUSTERED INDEX - 1.99556541 - 2.2172949

HEAP - 14.51824626 - 99.81283422

Regards!