How to reduce MS SQL file size
MS SQL database files will pre-allocate some spaces on Windows file system. Sometimes, you may want to re-claim the free space inside the SQL log/data files by reduce the file size. You may use the command below:
If you think the database log is not useful, you can simply discard it by:
DBCC SHRINKDATABASE(@dbName, 10, TRUNCATEONLY)
The above command will generate alert at SQL server 2008. You can also switch from Full Recovery Mode to Simple Recover Mode, and then back to Full Recovery Mode in order to discard the logs.
Moreover, you should note that you may need to allow some free space inside the SQL files. Otherwise, the log/data file will run out of space. If you are lucky that you have specify auto-grow, the log/data file will grow. The downside is it will affect the server performance during grow on peak hour. An unschedule grow will also increase the file fragmentation from OS file system point of view. It is better for DBA to review the free space of SQL files regularly and perform file grow manually on non-peak hours.
USE [DBNAME]
DBCC SHRINKFILE (N'DBNAME_Log' , 700) -- Shrink the file to 700MB
DBCC SHRINKFILE (N'DBNAME_Log' , 700) -- Shrink the file to 700MB
The above command will ask the SQL server to shrink the file to a target of 700MB. If there is more than 700MB data inside the log, of course, the SQL server cannot shrink the file to your target.
If you think the database log is not useful, you can simply discard it by:
DBCC SHRINKDATABASE(@dbName, 10, TRUNCATEONLY)
The above command will generate alert at SQL server 2008. You can also switch from Full Recovery Mode to Simple Recover Mode, and then back to Full Recovery Mode in order to discard the logs.
Moreover, you should note that you may need to allow some free space inside the SQL files. Otherwise, the log/data file will run out of space. If you are lucky that you have specify auto-grow, the log/data file will grow. The downside is it will affect the server performance during grow on peak hour. An unschedule grow will also increase the file fragmentation from OS file system point of view. It is better for DBA to review the free space of SQL files regularly and perform file grow manually on non-peak hours.
Comments