What does the shrink database maintenance task do?
Shrinking data files recovers space by moving pages of data from the end of the file to unoccupied space closer to the front of the file. To eliminate the fragmentation, consider rebuilding the indexes on the file after shrinking. This task executes the DBCC SHRINKDATABASE statement.
What is shrinking of database?
The Shrink Database task reduces the size of SQL Server database data and log files. Shrinking data files recovers space by moving pages of data from the end of the file to unoccupied space closer to the front of the file.
How to Test SQL server maintenance plan shrink Database Task?
To test the task, we locate it under the “Maintenance Plans” , right-click on it and select “Execute”: The task is successfully executed and if we had a database with a visible amount of unused, free storage space, we could see that the files’ sizes were reduced.
Is there a way to shrink the database?
Overall, the Shrink Database Task can be used to reduce the database size by shrinking its unused space. This task is recommended to use only in case of a real necessity as it is a resource-consuming task and causes index fragmentation.
Can a maintenance plan be run against any database?
No maintenance tasks are run against the SQL Server system databases. Generate a maintenance plan that runs maintenance tasks against only those databases that are selected. At least one database in the list must be selected if this option is chosen. Maintenance plans only run against databases set to compatibility level 80 or higher.
How to shrink a transaction log file maintenance plan?
In this section, we’ll list detail steps to Shrink a Transaction Log File Maintenance Plan in SQL Server. Login to SQL Server. From Object Explorer > Management > Maintenance Plan Wizard. Click “Next” to start “Maintenance Plan Wizard”. Specify a meaning name for your Maintenance Plan as you prefer.