0

I need to reduce the size of Database file. I am confused between DBCC Shrinkfile and Data compression option.

DBCC shrinkfile will reduce the actual database file size as far as I know but Data compression will not do the same. please advice or their are any other options because I cannot delete any file from my prod DB.

Improve this question
1
  • 3
    These are two entirely different concepts. Have you read anything about either of them?
    – Erik Reasonable Rates Darling
    Commented Apr 4, 2017 at 1:25

1 Answer 1

Reset to default
0

  1. DBCC SHRINKFILE will release unused but allocated space. From books online: DBCC SHRINKFILE does not shrink a file past the size needed to store the data in the file. For example, if 7 MB of a 10-MB data file is used, a DBCC SHRINKFILE statement with a target_size of 6 shrinks the file to only 7 MB, not 6 MB.

  2. The data compression feature in SQL Server helps compress the data inside a database, and it can help reduce the size of the database. Apart from the space savings, data compression provides another benefit: Because compressed data is stored in fewer pages, queries need to read fewer pages from the disk, thereby improving the performance of I/O intensive workloads. However, extra CPU resources are required on the database server to compress and decompress the data, while data is exchanged with the application. Therefore, it is important to understand the workload characteristics when deciding which tables to compress. Compression will not release allocated space. You can do so by shrinking the file once you compress. Ref: https://technet.microsoft.com/en-us/library/dd894051(v=sql.100).aspx

  3. See the answer to this question here asked earlier.

Improve this answer
4
  • @Taiob..Thank you for the detailed response. my actual constraint is my current db size is 420 Gb and my drive size where data file is stored is of 430 GB. So, only 10 gb is left and this DB grows faster, hence I was thinking I will use some otion to free up space on my hard drive. their is nothing else on that drive except this DB data file. please suggest.
    – Puskar
    Commented Apr 4, 2017 at 1:58
  • 1. Can you add a 2nd data file on a separate drive (LUN)? 2. You can consider compressing some of the tables (index) after testing performance impact. 3. There are few options sp_BlitzErik mentioned here dba.stackexchange.com/questions/168949/….
    – SqlWorldWide
    Commented Apr 4, 2017 at 2:07
  • @Taiob..I cannot add a new drive. is theier wa way I can squeeze the db file at current state. ??
    – Puskar
    Commented Apr 4, 2017 at 17:34
  • Please read my earlier comments. 1. you can compress objects (indexes). 2. If your indexes are fragmented you and reorg (you cannot rebuild as you do not have extra space)and that will free up space.
    – SqlWorldWide
    Commented Apr 4, 2017 at 17:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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