- \r\n
- You cannot shrink a database while a backup is in progress, and you cannot take a backup while the database is being shrunk.<\/li>\r\n
- A database cannot be shrunk to a size smaller than its original (initial) size. For example, if a database was initially 10 MB and later grew to 2 GB, the minimum shrink size will still be 10 MB.<\/li>\r\n
- Ensure sufficient free space is available on the disk before performing the shrink operation.<\/li>\r\n<\/ul>\r\n
Set Database Recovery Model to Simple (Recommended)<\/strong><\/p>\r\n
Before changing the Recovery Model to Simple, ensure you understand the impact on transaction log backups and recovery requirements. If the database is switched back to Full recovery mode after shrinking, a new Full Database Backup should be taken before resuming transaction log backups.<\/p>\r\n
\r\nSteps to Set Recovery Model to Simple:<\/p>\r\n
- \r\n
- Open SQL Server Management Studio (SSMS).<\/li>\r\n
- Right-click the database and select Properties.<\/li>\r\n
- Go to Options.<\/li>\r\n
- Set Recovery model to Simple.<\/li>\r\n
- Click OK.<\/li>\r\n<\/ol>\r\n
Note:<\/p>\r\n
After switching the database back to Full Recovery mode, take a new Full Database Backup before resuming transaction log backups, as the previous log backup chain is no longer valid .<\/p>\r\n
<\/p>\r\n<\/div>\r\n
Method 1: Using SQL Server Management Studio<\/strong><\/p>\r\n
- \r\n
- Right-click on the Database name >> Tasks >> Shrink >> Database.
![\"Database\"](\"https:\/\/www.webhosting.uk.com\/kb\/wp-content\/uploads\/2016\/12\/1.png\")
<\/li>\r\n - Then Click OK.
![\"Ok\"](\"https:\/\/www.webhosting.uk.com\/kb\/wp-content\/uploads\/2016\/12\/2.png\")
<\/li>\r\n<\/ol>\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\nMethod 2: Using Transact SQL Command<\/strong><\/p>\r\n
- \r\n
- Open SQL Server Management Studio.<\/li>\r\n
- Connect to the Database Engine.<\/li>\r\n
- From the Standard bar, click New Query.
![\"New](\"https:\/\/www.webhosting.uk.com\/kb\/wp-content\/uploads\/2016\/12\/3.png\")
<\/li>\r\n - Type the following SQL command into the query window and click Execute.\r\n
DBCC SHRINKDATABASE (DatabaseName, 'target percent') --<\/pre>\r\nExample:
\r\nDBCC SHRINKDATABASE (test, 10);
GO<\/pre>\r\nThis command shrinks the database named test to leave 10% free space.<\/p>\r\n
![\"shrink](\"https:\/\/www.webhosting.uk.com\/kb\/wp-content\/uploads\/2016\/12\/4.png\")
<\/li>\r\n<\/ol>\r\nThat\u2019s it! By following these steps, you can safely shrink the SQL Server database size.<\/p>\r\n
Running SQL Server in production?<\/strong>
For better performance and full control, consider using our VPS hosting optimised for database workloads<\/a>.<\/div>\r\nIf you found this article helpful, you will definitely appreciate our support services.<\/p>\r\n
- Right-click on the Database name >> Tasks >> Shrink >> Database.