How to compress .mdf file in SQL2000

**Kaufman** · 14-02-2010

Hi all,

I am using SQL 2000 in my office. I have lots of data in SQL, I want to compress the output data that is on mdf format in SQL , but not succeed. I also usedsome third party tool, but it will create a problem with SQL. Can anybody suggest me how to compress .mdf file in SQL2000. Please help. Thanks in advance.

**Modifier** · 14-02-2010

First of all you have to find your largest tables in the database, generally the largest number of tables, if not clear, in the Query Analyzer query by:

Code:

DBCC SHOWCONTIG

then sp_spaceused Table To query the reserved values and data values can differ by more seem redundant in the table. Check and reply.

**MindSpace** · 14-02-2010

You also need to copy of your transfer of data from old table to the new sheet;

Code:

SELECT * INTO new FROM old table table

If you have the data are more likely to be compressed you will see the corresponding database mdf and ldf file size in the surge, nothing in the replication tables. This command can not complete copy of the database node structure, require you to re-new table the old table and the corresponding primary key, automatic increase in value, index, the default value, etc. Change. Check and reply.

**opaper** · 14-02-2010

You also updated the look of SQL document by this command:

Code:

DBCC UPDATEUSAGE (0)

Remove the old table, and then change the name of the new table the old table name, which can go to Enterprise Manager where operation. Shrinking mdf file, and most want to achieve a step,

Code:

DBCC SHRINKDATABASE ( 'Library Name')
DBCC shrinkfile (2,0)
DBCC UPDATEUSAGE (0)

2 is use for to shrink the file fileid, corresponding to sysfiles in fileid and 0 is used for shrink to the original size of the. check and reply.

**Reegan** · 14-02-2010

You should noted down that the Scarlet Letter is a contraction of your database mdf file, the time may be a bit long; blue word is shrinking ldf files; purple character is to update the look, non-important. You should be able to see changes in the database mdf file size differences, did not say dozens of M, I shrink the database from 6G to 1G is about to execute the query again, some of the data is obviously much quicker. Best of luck.