Compressed database and access compressed Database

Compressed database and access compressed Database
Zookeeper

1. display the disk space reserved and used by the entire database.

EXEC SP_SPACEUSED

2. display the database file list

SELECT * FROM SYSFILES

3. Set the database recovery mode to simple

Alter database DNName set recovery simple with NO_WAIT
GO
Alter database DNName set recovery simple -- SIMPLE mode
GO

Alter database DNName set recovery full with NO_WAIT

GO

Alter database DNName set recovery full -- restore to FULL Mode

GO

4. Compress database files

-- Log

Dbcc shrinkfile (n'dnname _ log', 11, TRUNCATEONLY) -- automatically ignores "percentage of available space required in database files after database contraction ", so "11" can be another value.

Simple Method

Dbcc shrinkfile (0)

Dbcc shrinkfile (1)

0, 1 indicates the database file number, which is queried through sysfiles.

Parameter description:

TRUNCATEONLY: releases all available space at the end of the file to the operating system, but does not move any page inside the file. The data file is only shrunk to the most recently allocated zone. If it is specified with TRUNCATEONLYTarget_percent.

It can increase the compression speed.

However, the compression rate is not high.

5. compress the database

Simple Method

Dbcc shrinkdatabase (0)

Dbcc shrinkdatabase (0, 1, TRUNCATEONLY)

0: indicates the current database.

Add