SQL Server – Reducing Database File Size: DBCC Shrinkfile vs Data Compression

database-tuningperformancesql server

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.

Best Answer

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.
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
See the answer to this question here asked earlier.

Related Solutions

Sql-server – How to reduce the log file size without shrinking it in SQL server

Once the log claims disk space the only way to get it back is to shrink. Checkpoints and truncation will free up space within the log file (now available for future use by the log) but the actual footprint of the file on disk stays the same.

Read these articles for good information not only about how this works but the pro's and con's of messing with it. Most DBA's would agree that shrinking is not a good idea in most normal cases (as you eluded to in your question).

http://msdn.microsoft.com/en-us/library/ms365418.aspx

pros and cons

http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/a9e92f1b-7a27-4999-b241-eadadbb0a057

Sql-server – Shrinking the log file does not reduce size

Here is the answer to my own question.

Run the below query to get information about the log file's reuse wait:

SELECT log_reuse_wait_desc
FROM sys.databases
WHERE name = 'DBName'

I got the following output:

log_reuse_wait_desc
-------------------
REPLICATION

There were some replication-related objects remaining in the database, even after removing the replication.

To remove the replication from the database, sp_removedbreplication can be used. But it didn't work for us as replication was not active at the time and actually replication had been removed long before.

The solution was to import the database contents to another database using the import option of SQL Server.

Best Answer

Related Solutions

Sql-server – How to reduce the log file size without shrinking it in SQL server

Sql-server – Shrinking the log file does not reduce size

Related Question