Hướng dẫn compress file mdf sql server

Recover Data for SQL Server là phần mềm mạnh mẽ được dùng để khôi phục các file database SQL Server (.MDF) bị lỗi, được tạo ra bởi SQL Server 2000/2005/2008.

Nó có thể khôi phục thành công những file .MDF bị lỗi và không thể mở được do các nguyên nhân như: lỗi hệ thống, format hoặc cài đặt lại. Ngoài ra, nó còn cho phép bạn xem và lưu trữ dữ liệu đã khôi phục vào địa điểm đã xác định.

Tính năng cải tiến:

• Giao diện trực quan, đơn giản, dễ sử dụng.

• Khôi phục dữ liệu nhanh chóng.

• Khôi phục file database SQL Server (.MDF) bị lỗi.

• Dễ dàng khôi phục dữ liệu trên server khi SQL Server vẫn đang chạy.

• Quét và khôi phục dữ liệu từ các database SQL có dung lượng lớn.

• Khôi phục các thuộc tính như Tables, Indexes, Views, Triggers, Constraints, Functions, Procedures,…

It's a pretty widely debated topic. Another answer to this question gives you a more in-depth backstory on it, so I won't duplicate that here.

Anyone know about performance impacts

When it comes to the performance of what you're suggesting, there's not a single answer that works for everyone. It's dependent on a couple of things:

• How frequently will you be inserting/retrieving records from this table?
• Where will the compression/decompression be performed? On the server, or on the client?
• If the compression is performed on the client, is the client a web server or is it an end-user PC?

Different answers to the above questions will result in vastly different changes to performance.

If you're doing the compression on an end-user PC, then you'll potentially notice some benefits; if the data can be compressed very well (and quickly enough), then sending the data to the database might take less time than sending the uncompressed version. Though, if the data can't be compressed very well (or compresses very slowly), then your end-users might complain about a decrease in performance; it may take less time to send it to the server, but the only thing your end-users will notice is the loading bar while the data is being compressed. You might be able to get around this by conditionally compressing files that are known to compress very well, such as text documents.

If the compression is being performed on a web server, which then writes it to the database, you likely won't see a lot of benefit in terms of speed. Servers are usually connected to each other over very fast connections (usually 100/1000mbit connections if they're in the same data center) and you will have already incurred the most likely bottleneck: the upload speed of the user's internet connection.

At this point you're just putting more load on your web server that could potentially be better spent servicing a greater number of concurrent users of your web application. Of course, you could always upload the files to a staging directory and perform the compression at off-peak hours, but then you've added a lot of complexity (what if the file is requested again before it is compressed and sent to the database?) just to save a few megabytes on your server.

Furthermore, you're going to incur a similar performance cost every time a file is requested, since you'll have to spend time and processing power decompressing it. If you get many requests to download files in a short period of time, your server could slow to a crawl trying to decompress everyone's files before sending them down the wire.

As I said in the beginning, there's no single answer to this that will work for everyone, but if you consider all the factors, you can make an informed decision about what will work best for your environment.

No, but SQL Server 2008 does provide data compression at the ROW or PAGE level on a table by table basis. I am looking at this myself right now. I have compressed the top 10 tables in one of our databases and dropped the spaced used by 3 GB. The first 4 tables dropped the space used by 2 GB, the next 6 only reduced it by another 1 GB.

I am looking at PAGE compression at the moment.

Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.

Data compression

• Article
• 10/27/2023

In this article

Applies to:

SQL Server, Azure SQL Database, and Azure SQL Managed Instance support row and page compression for rowstore tables and indexes, and support columnstore and columnstore archival compression for columnstore tables and indexes.

For rowstore tables and indexes, use the data compression feature to help reduce the size of the database. In addition to saving space, data compression can help improve performance of I/O intensive workloads because the data is stored in fewer pages and queries need to read fewer pages from disk. However, extra CPU resources are required on the database server to compress and decompress the data, while data is exchanged with the application. You can configure row and page compression on the following database objects:

• A whole table that is stored as a heap.
• A whole table that is stored as a clustered index.
• A whole nonclustered index.
• A whole indexed view.
• For partitioned tables and indexes, you can configure the compression option for each partition, and the various partitions of an object don't have to have the same compression setting.

For columnstore tables and indexes, all columnstore tables and indexes always use columnstore compression and this isn't user configurable. Use columnstore archival compression to further reduce the data size for situations when you can afford extra time and CPU resources to store and retrieve the data. You can configure columnstore archival compression on the following database objects:

• A whole columnstore table or a whole clustered columnstore index. Since a columnstore table is stored as a clustered columnstore index, both approaches have the same results.
• A whole nonclustered columnstore index.
• For partitioned columnstore tables and columnstore indexes, you can configure the archival compression option for each partition, and the various partitions don't have to have the same archival compression setting.

Note

Data can also be compressed using the GZIP algorithm format. This is an additional step and is most suitable for compressing portions of the data when archiving old data for long-term storage. Data compressed using the COMPRESS function can't be indexed. For more information, see COMPRESS (Transact-SQL).

Row and page compression considerations

When you use row and page compression, be aware the following considerations:

• The details of data compression are subject to change without notice in service packs or subsequent releases.
• Compression is available in Azure SQL Database
• Compression isn't available in every edition of SQL Server. For more information, see the list of editions and supported features at the end of this section.
• Compression isn't available for system tables.
• Compression can allow more rows to be stored on a page, but doesn't change the maximum row size of a table or index.
• A table can't be enabled for compression when the maximum row size plus the compression overhead exceeds the maximum row size of 8,060 bytes. For example, a table that has the columns c1 CHAR(8000) and c2 CHAR(53) can't be compressed because of the additional compression overhead. When the vardecimal storage format is used, the row-size check is performed when the format is enabled. For row and page compression, the row-size check is performed when the object is initially compressed, and then checked as each row is inserted or modified. Compression enforces the following two rules:
  • An update to a fixed-length type must always succeed.
  • Disabling data compression must always succeed. Even if the compressed row fits on the page, which means that it's less than 8,060 bytes; SQL Server prevents updates that don't fit on the row when it's uncompressed.
• Off-row data isn't compressed when enabling data compression. For example, an XML record that's larger than 8,060 bytes uses out-of-row pages, which aren't compressed.
• Several data types aren't affected by data compression. For more detail, see .
• When a list of partitions is specified, the compression type can be set to ROW, PAGE, or

  ALTER TABLE ColumnstoreTable1 REBUILD PARTITION = 1 WITH (

   DATA_COMPRESSION = COLUMNSTORE  
  

  ); ALTER TABLE ColumnstoreTable1 REBUILD PARTITION = ALL WITH (

  DATA_COMPRESSION = COLUMNSTORE  
  

  ); ALTER TABLE ColumnstoreTable1 REBUILD PARTITION = ALL WITH (

  DATA_COMPRESSION = COLUMNSTORE ON PARTITIONS (2, 4)  
  

  );
  0 on individual partitions. If the list of partitions isn't specified, all partitions are set with the data compression property that is specified in the statement. When a table or index is created, data compression is set to NONE unless otherwise specified. When a table is modified, the existing compression is preserved unless otherwise specified.
• If you specify a list of partitions or a partition that is out of range, an error is generated.
• Nonclustered indexes don't inherit the compression property of the table. To compress indexes, you must explicitly set the compression property of the indexes. By default, the compression setting for indexes is set to NONE when the index is created.
• When a clustered index is created on a heap, the clustered index inherits the compression state of the heap unless an alternative compression state is specified.
• When a heap is configured for page-level compression, pages receive page-level compression only in the following ways:
  • Data is bulk imported with bulk optimizations enabled.
  • Data is inserted using

    ALTER TABLE ColumnstoreTable1 REBUILD PARTITION = 1 WITH (

        DATA_COMPRESSION = COLUMNSTORE  
    

    ); ALTER TABLE ColumnstoreTable1 REBUILD PARTITION = ALL WITH (

       DATA_COMPRESSION = COLUMNSTORE  
    

    ); ALTER TABLE ColumnstoreTable1 REBUILD PARTITION = ALL WITH (

       DATA_COMPRESSION = COLUMNSTORE ON PARTITIONS (2, 4)  
    

    );
    1 syntax and the table doesn't have a nonclustered index.
  • A table is rebuilt by executing the

    ALTER TABLE ColumnstoreTable1 REBUILD PARTITION = 1 WITH (

        DATA_COMPRESSION = COLUMNSTORE  
    

    ); ALTER TABLE ColumnstoreTable1 REBUILD PARTITION = ALL WITH (

       DATA_COMPRESSION = COLUMNSTORE  
    

    ); ALTER TABLE ColumnstoreTable1 REBUILD PARTITION = ALL WITH (

       DATA_COMPRESSION = COLUMNSTORE ON PARTITIONS (2, 4)  
    

    );
    2 statement with the PAGE compression option.
• New pages allocated in a heap as part of DML operations don't use PAGE compression until the heap is rebuilt. Rebuild the heap by removing and reapplying compression, or by creating and removing a clustered index.
• Changing the compression setting of a heap requires all nonclustered indexes on the table to be rebuilt so that they have pointers to the new row locations in the heap.
• You can enable or disable ROW or PAGE compression online or offline. Enabling compression on a heap is single threaded for an online operation.
• The disk space requirements for enabling or disabling row or page compression are the same as for creating or rebuilding an index. For partitioned data, you can reduce the space that is required by enabling or disabling compression for one partition at a time.
• To determine the compression state of partitions in a partitioned table, query the

  ALTER TABLE ColumnstoreTable1 REBUILD PARTITION = 1 WITH (

   DATA_COMPRESSION = COLUMNSTORE  
  

  ); ALTER TABLE ColumnstoreTable1 REBUILD PARTITION = ALL WITH (

  DATA_COMPRESSION = COLUMNSTORE  
  

  ); ALTER TABLE ColumnstoreTable1 REBUILD PARTITION = ALL WITH (

  DATA_COMPRESSION = COLUMNSTORE ON PARTITIONS (2, 4)  
  

  );

  7 column of the

  ALTER TABLE ColumnstoreTable1 REBUILD PARTITION = 1 WITH (

   DATA_COMPRESSION = COLUMNSTORE  
  

  ); ALTER TABLE ColumnstoreTable1 REBUILD PARTITION = ALL WITH (

  DATA_COMPRESSION = COLUMNSTORE  
  

  ); ALTER TABLE ColumnstoreTable1 REBUILD PARTITION = ALL WITH (

  DATA_COMPRESSION = COLUMNSTORE ON PARTITIONS (2, 4)  
  

  );
  8 catalog view.
• When you compress indexes, leaf-level pages can be compressed with both row and page compression. Non-leaf-level pages don't receive page compression.
• Because of their size, large-value data types are sometimes stored separately from the normal row data on special purpose pages. Data compression isn't available for the data that is stored separately.
• Tables that implemented the vardecimal storage format in SQL Server 2005 (9.x), retain that setting when upgraded. You can apply row compression to a table that has the vardecimal storage format. However, because row compression is a superset of the vardecimal storage format, there's no reason to retain the vardecimal storage format. Decimal values gain no additional compression when you combine the vardecimal storage format with row compression. You can apply page compression to a table that has the vardecimal storage format; however, the vardecimal storage format columns probably don't achieve additional compression. Note All supported versions of SQL Server support the vardecimal storage format; however, because data compression achieves the same goals, the vardecimal storage format is deprecated. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

For a list of features supported by the editions of SQL Server on Windows, see:

• Editions and supported features of SQL Server 2022
• Editions and supported features of SQL Server 2019
• Editions and supported features of SQL Server 2017
• Editions and supported features of SQL Server 2016

Columnstore and columnstore archive compression

Columnstore tables and indexes are always stored with columnstore compression. You can further reduce the size of columnstore data by configuring an additional compression called archival compression. To perform archival compression, SQL Server runs the Microsoft XPRESS compression algorithm on the data. Add or remove archival compression by using the following data compression types:

• Use

  ALTER TABLE ColumnstoreTable1 REBUILD PARTITION = 1 WITH (

   DATA_COMPRESSION = COLUMNSTORE  
  

  ); ALTER TABLE ColumnstoreTable1 REBUILD PARTITION = ALL WITH (

  DATA_COMPRESSION = COLUMNSTORE  
  

  ); ALTER TABLE ColumnstoreTable1 REBUILD PARTITION = ALL WITH (

  DATA_COMPRESSION = COLUMNSTORE ON PARTITIONS (2, 4)  
  

  );
  9 data compression to compress columnstore data with archival compression.
• Use

  ALTER TABLE ColumnstoreTable1 REBUILD PARTITION = ALL WITH (

  DATA_COMPRESSION = COLUMNSTORE  
      ON PARTITIONS (4, 5),  
  DATA COMPRESSION = COLUMNSTORE_ARCHIVE  
      ON PARTITIONS (1, 2, 3)  
  

  );
  0 data compression to decompress archival compression. The resulting data continue to be compressed with columnstore compression.

To add archival compression, use ALTER TABLE (Transact-SQL) or ALTER INDEX (Transact-SQL) with the

ALTER TABLE ColumnstoreTable1
REBUILD PARTITION = ALL WITH (
    DATA_COMPRESSION = COLUMNSTORE
        ON PARTITIONS (4, 5),
    DATA COMPRESSION = COLUMNSTORE_ARCHIVE
        ON PARTITIONS (1, 2, 3)
);

1 option and

ALTER TABLE ColumnstoreTable1
REBUILD PARTITION = ALL WITH (
    DATA_COMPRESSION = COLUMNSTORE
        ON PARTITIONS (4, 5),
    DATA COMPRESSION = COLUMNSTORE_ARCHIVE
        ON PARTITIONS (1, 2, 3)
);

2.

For example:

ALTER TABLE ColumnstoreTable1
REBUILD PARTITION = 1 WITH (
    DATA_COMPRESSION = COLUMNSTORE_ARCHIVE
);
ALTER TABLE ColumnstoreTable1
REBUILD PARTITION = ALL WITH (
    DATA_COMPRESSION = COLUMNSTORE_ARCHIVE
);
ALTER TABLE ColumnstoreTable1
REBUILD PARTITION = ALL WITH (
    DATA_COMPRESSION = COLUMNSTORE_ARCHIVE ON PARTITIONS (2, 4)
);

To remove archival compression and restore the data to columnstore compression, use ALTER TABLE (Transact-SQL) or ALTER INDEX (Transact-SQL) with the

ALTER TABLE ColumnstoreTable1
REBUILD PARTITION = ALL WITH (
    DATA_COMPRESSION = COLUMNSTORE
        ON PARTITIONS (4, 5),
    DATA COMPRESSION = COLUMNSTORE_ARCHIVE
        ON PARTITIONS (1, 2, 3)
);

1 option and

ALTER TABLE ColumnstoreTable1
REBUILD PARTITION = ALL WITH (
    DATA_COMPRESSION = COLUMNSTORE
        ON PARTITIONS (4, 5),
    DATA COMPRESSION = COLUMNSTORE_ARCHIVE
        ON PARTITIONS (1, 2, 3)
);

4.

For example:

ALTER TABLE ColumnstoreTable1
REBUILD PARTITION = 1 WITH (
     DATA_COMPRESSION = COLUMNSTORE
);
ALTER TABLE ColumnstoreTable1
REBUILD PARTITION = ALL WITH (
    DATA_COMPRESSION = COLUMNSTORE
);
ALTER TABLE ColumnstoreTable1
REBUILD PARTITION = ALL WITH (
    DATA_COMPRESSION = COLUMNSTORE ON PARTITIONS (2, 4)
);

This next example sets the data compression to columnstore on some partitions, and to columnstore archival on other partitions.

ALTER TABLE ColumnstoreTable1
REBUILD PARTITION = ALL WITH (
    DATA_COMPRESSION = COLUMNSTORE
        ON PARTITIONS (4, 5),
    DATA COMPRESSION = COLUMNSTORE_ARCHIVE
        ON PARTITIONS (1, 2, 3)
);

Performance

When you compress columnstore indexes with archival compression, this causes the index to perform slower than columnstore indexes that don't have the archival compression. Use archival compression only when you can afford to use extra time and CPU resources to compress and retrieve the data.

The benefit of archival compression is reduced storage, which is useful for data that isn't accessed frequently. For example, if you have a partition for each month of data, and most of your activity is for the most recent months, you could archive older months to reduce the storage requirements.

Metadata

The following system views contain information about data compression for clustered indexes:

• sys.indexes (Transact-SQL) - The

  ALTER TABLE ColumnstoreTable1 REBUILD PARTITION = ALL WITH (

  DATA_COMPRESSION = COLUMNSTORE  
      ON PARTITIONS (4, 5),  
  DATA COMPRESSION = COLUMNSTORE_ARCHIVE  
      ON PARTITIONS (1, 2, 3)  
  

  );

  5 and

  ALTER TABLE ColumnstoreTable1 REBUILD PARTITION = ALL WITH (

  DATA_COMPRESSION = COLUMNSTORE  
      ON PARTITIONS (4, 5),  
  DATA COMPRESSION = COLUMNSTORE_ARCHIVE  
      ON PARTITIONS (1, 2, 3)  
  

  );

  6 columns include

  ALTER TABLE ColumnstoreTable1 REBUILD PARTITION = ALL WITH (

  DATA_COMPRESSION = COLUMNSTORE  
      ON PARTITIONS (4, 5),  
  DATA COMPRESSION = COLUMNSTORE_ARCHIVE  
      ON PARTITIONS (1, 2, 3)  
  

  );

  7 and

  ALTER TABLE ColumnstoreTable1 REBUILD PARTITION = ALL WITH (

  DATA_COMPRESSION = COLUMNSTORE  
      ON PARTITIONS (4, 5),  
  DATA COMPRESSION = COLUMNSTORE_ARCHIVE  
      ON PARTITIONS (1, 2, 3)  
  

  );
  8.
• sys.partitions (Transact-SQL) - The

  ALTER TABLE ColumnstoreTable1 REBUILD PARTITION = 1 WITH (

   DATA_COMPRESSION = COLUMNSTORE  
  

  ); ALTER TABLE ColumnstoreTable1 REBUILD PARTITION = ALL WITH (

  DATA_COMPRESSION = COLUMNSTORE  
  

  ); ALTER TABLE ColumnstoreTable1 REBUILD PARTITION = ALL WITH (

  DATA_COMPRESSION = COLUMNSTORE ON PARTITIONS (2, 4)  
  

  );

  7 and

  ALTER TABLE REBUILD PARTITION = 1 WITH (

  DATA_COMPRESSION =   
  

  );

  0 columns include

  ALTER TABLE ColumnstoreTable1 REBUILD PARTITION = ALL WITH (

  DATA_COMPRESSION = COLUMNSTORE  
      ON PARTITIONS (4, 5),  
  DATA COMPRESSION = COLUMNSTORE_ARCHIVE  
      ON PARTITIONS (1, 2, 3)  
  

  );

  0 and

  ALTER TABLE ColumnstoreTable1 REBUILD PARTITION = 1 WITH (

   DATA_COMPRESSION = COLUMNSTORE  
  

  ); ALTER TABLE ColumnstoreTable1 REBUILD PARTITION = ALL WITH (

  DATA_COMPRESSION = COLUMNSTORE  
  

  ); ALTER TABLE ColumnstoreTable1 REBUILD PARTITION = ALL WITH (

  DATA_COMPRESSION = COLUMNSTORE ON PARTITIONS (2, 4)  
  

  );
  9.

The procedure sp_estimate_data_compression_savings (Transact-SQL) can also apply to columnstore indexes.

Impact on partitioned tables and indexes

When you use data compression with partitioned tables and indexes, be aware of the following considerations:

• When partitions are split by using the

  ALTER TABLE REBUILD PARTITION = 1 WITH (

  DATA_COMPRESSION =   
  

  );
  3 statement, both partitions inherit the data compression attribute of the original partition.
• When two partitions are merged, the resultant partition inherits the data compression attribute of the destination partition.
• To switch a partition, the data compression property of the partition must match the compression property of the table.
• There are two syntax variations that you can use to modify the compression of a partitioned table or index:
  • The following syntax rebuilds only the referenced partition:

    ALTER TABLE REBUILD PARTITION = 1 WITH (

       DATA_COMPRESSION =   
    

    );

  • The following syntax rebuilds the whole table by using the existing compression setting for any partitions that aren't referenced:

    ALTER TABLE REBUILD PARTITION = ALL WITH (

       DATA_COMPRESSION = PAGE ON PARTITIONS(),  
       ...  
    

    );

    Partitioned indexes follow the same principle using

    ALTER TABLE REBUILD PARTITION = 1 WITH (

    DATA_COMPRESSION =   
    

    );
    4.
• When a clustered index is dropped, the corresponding heap partitions retain their data compression setting unless the partitioning scheme is modified. If the partitioning scheme is changed, all partitions are rebuilt to an uncompressed state. To drop a clustered index and change the partitioning scheme requires the following steps:
  1. Drop the clustered index.
  2. Modify the table by using the

     ALTER TABLE ColumnstoreTable1 REBUILD PARTITION = 1 WITH (

         DATA_COMPRESSION = COLUMNSTORE  
     

     ); ALTER TABLE ColumnstoreTable1 REBUILD PARTITION = ALL WITH (

        DATA_COMPRESSION = COLUMNSTORE  
     

     ); ALTER TABLE ColumnstoreTable1 REBUILD PARTITION = ALL WITH (

        DATA_COMPRESSION = COLUMNSTORE ON PARTITIONS (2, 4)  
     

     );

     2 option that specifies the compression option. To drop a clustered index

     ALTER TABLE REBUILD PARTITION = 1 WITH (

     DATA_COMPRESSION =   
     

     );

     6 is a fast operation, because only the upper levels of clustered indexes are removed. When a clustered index is dropped

     ALTER TABLE REBUILD PARTITION = 1 WITH (

     DATA_COMPRESSION =   
     

     );
     7, SQL Server must rebuild the heap two times, once for step 1 and once for step 2.

How compression affects replication

When you use data compression with replication, be aware of the following considerations:

• When the Snapshot Agent generates the initial schema script, the new schema uses the same compression settings for both the table and its indexes. Compression can't be enabled on just the table and not the index.
• For transactional replication, the article schema option determines what dependent objects and properties have to be scripted. For more information, see sp_addarticle. The Distribution Agent doesn't check for down-level Subscribers when it applies scripts. If the replication of compression is selected, creating the table on down-level Subscribers fails. For a mixed topology, don't enable the replication of compression.
• For merge replication, publication compatibility level overrides the schema options and determines the schema objects that are scripted. For a mixed topology, if it isn't required to support the new compression options, the publication compatibility level should be set to the down-level Subscriber version. If it's required, compress tables on the Subscriber after they have been created.

The following table shows replication settings that control compression during replication.

User intent Replicate partition scheme for a table or index Replicate compression settings Scripting behavior To replicate the partition scheme and enable compression on the Subscriber on the partition. True True Scripts both the partition scheme and the compression settings. To replicate the partition scheme but not compress the data on the Subscriber. True False Scripts out the partition scheme but not the compression settings for the partition. Not to replicate the partition scheme and not compress the data on the Subscriber. False False Doesn't script partition or compression settings. To compress the table on the Subscriber if all the partitions are compressed on the Publisher, but not replicate the partition scheme. False True Checks if all the partitions are enabled for compression.

Scripts out compression at the table level.

Effect on other SQL Server components

Applies to:

Compression occurs in the Database Engine and the data is presented to most of the other components of SQL Server in an uncompressed state. This limits the effects of compression on the other components to the following factors:

How to manage MDF database size in SQL Server?

SQL Server compression is a better way to manage MDF database size. Microsoft SQL Server provides a different type of compression for tables and indexes. In addition, it offers archival compression also to reduce database size. Now, in order to reduce the size of MDF file, a DBA can use the data compression tool.

How many GB is a MDF file?

Total space in hard disk is 136 GB, the .MDF file size is 124 GB, and the log file size is 2 GB. I have only 12 GB free space and while running the shrink command on the .MDF file, logs are growing and consume the free space -- low disk spaces throws the shrinking into a no respond state.

How to reduce the size of MDF file?

Now, in order to reduce the size of MDF file, a DBA can use the data compression tool. Apart from minimizing the physical database size, it reduces a total number of disk I/O and improves the performance of database application.

Does DBCC shrinkfile reduce the size of an MDF?

Have you reviewed existing questions about shrinking MDFs, here on DBA.SE? Yes.Sine the initial size is set as 1.6TB ,the minimum shrink size is also 1.6TB.I want o reduce the initial size so that i can shrink it to a smaller size > inital size. DBCC SHRINKFILE will reduce the size of an MDF, if there's free space available to release.