shrinking a datafile file

Question

shrinking a datafile file

doug turner

SSChasing Mays

Points: 657
More actions
December 1, 2009 at 5:36 am

#137172

I have a replicated database that has grown to 51Gb. I'm limited to 60Gb, so I need to do something to shrink this guy. I've deleted several thousand rows of data, but the File Manager is still showing 51Gb. Can I reclaim this space or is it ok as is?

Viewing 14 posts - 1 through 13 (of 13 total)

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

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 1

doug turner (12/1/2009)
I have a replicated database that has grown to 51Gb. I'm limited to 60Gb, so I need to do something to shrink this guy. I've deleted several thousand rows of data, but the File Manager is still showing 51Gb. Can I reclaim this space or is it ok as is?

Hi,

Delete ll not reclaim the space .

QotD-->Reclaiming freed space [/url]

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

steveb. SSC-Forever Points: 46733 More actions · Answer 2

How much of the 51gb is free space?

SQL server will hold the free space for re-use unless you shrink the file, if there is space avialable then SQL server will use this before growing the file

george sibbald SSC Guru Points: 104210 More actions · Answer 3

you will have freed up space within the database file so what you have done has helped and bought you time. As the previous poster said deleting data does not cause the database file size to decrease, extents within the database just become free.

You need to investigate whether extra data will be added over time which will cause the database to grow, if so extra disc space might be a necessity.

In the meantime check out the growth factor for the file and make sure it is something sensible (100 - 200MB).

If you still feel you need to shrink the database file check out DBCC SHRINKFILE in books Online. shrink it to your desired size in chunks not one go. This is not a task to run other than ad-hoc as it will fragment your data badly, so run a reindex afterwards.

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

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 4

Hi,

Read the Paul article

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

doug turner SSChasing Mays Points: 657 More actions · Answer 5

Running sp_spaceused shows:

Reserved Data Index Size unused

51204224 KB22733928 KB28460576 KB9720 KB

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

So there's only 9 MB free in a 51 GB database. Definitely not worth a shrink.

If you rebuild all the indexes on the table that you deleted rows from, does the unused space increase?

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

doug turner SSChasing Mays Points: 657 More actions · Answer 7

I'm more of an Oracle person than SQL Server. How do I reindex?

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

Alter Index

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

george sibbald SSC Guru Points: 104210 More actions · Answer 9

having read up on it there are scripts for it on this site and also in BOL under sys.dm_db_index_physical_stats.

Another quick way is to use the reindex maintenance plan task via SSMS.

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

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 10

doug turner (12/1/2009)
I'm more of an Oracle person than SQL Server. How do I reindex?

Hi

Read the 1'st link added in my Signature.

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

doug turner SSChasing Mays Points: 657 More actions · Answer 11

Thanks. The indexing helped a little. I'm up to 1.6Gb free now. It looks like some major table cleanup is in order to really free up some space.

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 12

doug turner (12/1/2009)
Thanks. The indexing helped a little. I'm up to 1.6Gb free now. It looks like some major table cleanup is in order to really free up some space.

1.6 GB free space is approx 3% of your DB size, which would still cause the problem in near future.

Yes, you need to either perform a clean up of unwanted data or get additional storage space.

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 13

doug turner (12/1/2009)
Thanks. The indexing helped a little. I'm up to 1.6Gb free now. It looks like some major table cleanup is in order to really free up some space.

Please schedule the Rebuild index job weekly

INDEX DEFRAGMENTATION[/url]

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/