Database Compression
In the world of today, IT service providers constantly face problems of storage space. Under these circumstances, administrators and users turn to database shrinking and compression. By this practice, limited storage space can be used efficiently. There are many ways to achieve this. There is software that accomplishes the same task and there are various commands in different servers that can do the same job.
For SQL 2008 R2 databases ‘DBCC SHRINKDATABASE’ can be used. All you have to do is, state the name of the database and state the target percentage compression.
Mentioned above is the exact syntax for compression. For users of SQL Server 2000, this task is quite simplistic. Select the server group and then expand a server. Then, specify the amount of required free space under the option of ‘Max free space after shrinking’. After that, select the option ‘Move pages to start after shrinking’. Moreover, the database can be shrunk according to a set schedule using “Schedule” and manual shrinking of the files can be done selecting the option of ‘Shrink Files’.
DB2 9 is an application enhancement and database management tool which is designed for UNIX, Linux and Window servers. It has a feature of data compression that is worth mentioning. Through this utility, data in a table can be compressed and data row compression can be implemented easily. Also, compression ratio for existing compression can also be calculated and more effective utilization of the compression can be achieved.
Tempdb database can be reduced using various methods. SQL Server Management studio is recommended for performing the tasks described.
For method one, SQL server has to be stopped. ‘sqlserr -c –f’ has to be typed and the server restarted. After this, connect to SQL server and run the following commands:
After executing, these commands, size of Tempdb.mdf has to be verified and the server needs a restart for changes to take place.
Individual Tempdb files can also be shrunk individually using the ‘DBCC SHRINKFILE’ command. It can be used on a single file hence it is a more flexible command. For the command to execute successfully there shouldn’t be any other activity on the Tempdb and the SQL server must be in single user mode. Next, the query analyzer must be connected to the SQL server and the following syntax must be used:
’dbcc shrinkfile (temdev, ‘desired size mentioned in megabytes’)
‘dbcc shrinkfile (templog, ‘desired size mentioned in megabytes’)
The first command is related to reducing the primary data file while the second command targets the log file, shrinking it to a certain size, however, database cannot be smaller than the model database.
Associated with database compression are many problems. Compression can cause loss of important data and index fragmentation. Auto-shrink and scheduled shrink jobs can pose a serious threat to your database since it is an unsupervised method of compressing your database. However the upside is efficient usage of database and recovered speed of execution. Advantages like these cannot be overlooked.
