0

I have a DB with 50 GB of size I need to reduce the size of it. How to reduce the file size of it?

I already did this: shrink | files | selected 'Data' as filetype. but still it shows 50 GB Reserved space is using.

enter image description here

migrated from stackoverflow.com Jan 14 at 10:14

This question came from our site for professional and enthusiast programmers.

  • 2
    Use DBCC SHRINKFILE instead of GUI, it may write additional information – sepupic Jan 14 at 9:35
0

The actual syntax you want is:

USE [Savix_reporting] -- or whatever the actual name of the DB is
GO
DBCC SHRINKFILE (N'Savix_Reporting_Mis' , 5120mb)
GO

5120mb is a 5GB data file. Be advised, shrink the file could take 30 seconds, or 30 hours. There is no way to tell. e

0

You probably used the default in the GUI, which resulted that the GUI added the TRUNCATEONLY option. This means "don't move any data" and you probably had a situation where the last page in the database was in use. Hence the file didn't shrink.

By using the "Reorganize ..." option, SQL Server will move pages towards the beginning of the files, thus making the end of the file free and the file can be shrunk. Such page movements can take a terrible long time, fragment your indexes etc etc, so don't skrink unless you have a high enough payback. My article on shrink.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.