Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

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... Expand / Collapse
Author
Message
Posted Friday, January 3, 2014 1:02 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 18, 2014 3:03 PM
Points: 4, Visits: 44
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!
Post #1527700
Posted Friday, January 3, 2014 2:06 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 8:36 AM
Points: 40,615, Visits: 37,080
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 2008, MVP
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

Post #1527721
Posted Friday, January 3, 2014 2:19 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 18, 2014 3:03 PM
Points: 4, Visits: 44
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!
Post #1527726
Posted Saturday, January 4, 2014 3:33 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:55 AM
Points: 6,752, Visits: 14,397
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"
Post #1527869
Posted Sunday, January 5, 2014 10:55 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 18, 2014 3:03 PM
Points: 4, Visits: 44
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! :)
Post #1527970
Posted Monday, January 6, 2014 1:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:55 AM
Points: 6,752, Visits: 14,397
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"
Post #1527987
Posted Monday, January 6, 2014 3:12 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 4:51 PM
Points: 558, Visits: 1,651
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.
Post #1528273
Posted Tuesday, January 7, 2014 8:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 18, 2014 3:03 PM
Points: 4, Visits: 44
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!
Post #1528525
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse