Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

I have a 10 GB .mdf file with 250 mb .LDF.

I want to shrink the 10 GB .mdf (if possible)

I read about the dbcc command, but I read it in context of shrinking the .LDF (into any size desired using truncate).

However I want to shrink the .MDF file without loosing data.

What is the precise command for it ?

share|improve this question
1  
Do you know why you want to do this? – gbn Dec 29 '11 at 13:02
@gbn ,Im building a hash / scramble mechanism which takes a bak - hash it - and saves it. I really dont need to deal with the LDF here... – Royi Namir Dec 29 '11 at 13:29
I dont understand why this question was downrated . it is a valid question. if its bad practice - so the action is to responde why is it bad practice and not to downvote.... people dont know the meaning of downvote here...@marc_s ... was that you ? :) – Royi Namir Dec 29 '11 at 13:34
1  
So are you trying to do your own back up solution? BTW I didn't downvote – gbn Dec 29 '11 at 13:35
1  
Here - I found the link I was looking for - Paul Randal's blog post Why you should not shrink your data files - read it, embrace it - don't shrink your MDF's - it's pointless and counter productive – marc_s Dec 29 '11 at 13:47

migrated from stackoverflow.com Dec 29 '11 at 14:06

1 Answer

up vote 1 down vote accepted

Simply answer:

DBCC SHRINKFILE('DB filename', 10240)

OR for MDF file

DBCC SHRINKFILE(1, 10240)

But if you have more than 10240 Mb of data in MDF file - it woun't help, then you have to add extra file to DB - where to move extra data from MDF file.

From BOL:

DBCC SHRINKFILE

Shrinks the size of the specified data or log file for the current database or empties a file by moving the data from the specified file to other files in the same filegroup, allowing the file to be removed from the database. You can shrink a file to a size that is less than the size specified when it was created. This resets the minimum file size to the new value.

How to know filenames in DB:

SELECT file_Id, name FROM sys.database_files

Shrinking data files always affects performance - it makes it worse, so you really need to have a REASON to do that.

share|improve this answer
DBCC SHRINKFILE(1, 10240) where is the db param goes here ? – Royi Namir Dec 29 '11 at 13:28
Use this in context of the DB, which the file you want to shrink – Oleg Dok Dec 29 '11 at 13:29
does 10240 is calculated or just a upper margin limit ? If I have 10 GB dB , What should i use ? is it in KB ? MB ? – Royi Namir Dec 29 '11 at 13:30
,Im building a hash / scramble mechanism which takes a bak - hash it - and saves it. I really dont need to deal with the LDF here... – Royi Namir Dec 29 '11 at 13:30
10240 - this parameter is in MB, you cannot avoid using LDF file EXCEPT - your database is in READONLY MODE – Oleg Dok Dec 29 '11 at 13:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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