The Microsoft SQL Server database management system today is one of the most flexible and powerful. All data of the databases serviced by it are stored in mdf-files (Master Database File). With intensive use of the database (multiple insertions and deletions of table rows), the container file becomes fragmented. Its volume begins to greatly exceed the real volume of data stored in the database. If necessary, you can compress the mdf file using SQL Server.
Necessary
- - Microsoft SQL Server running on the local computer;
- - SQL Server Management Studio.
Instructions
Step 1
Connect to the database server. In SQL Server Management Studio, select the "Connect Object Explorer …" item in the File section of the main menu. The Connect to Server dialog will be displayed. The same dialog is displayed automatically after starting SQL Server Management Studio. Select the Database Engine entry from the Server type drop-down list. In the Server name text box, enter the local computer name. In the Authentication list, make the current item corresponding to the type of authentication supported by the local SQL server. If you select SQL Server Authentication, enter valid credentials in the User name and Password fields. Click the Connect button.
Step 2
Begin the process of adding an existing database. Select the Databases item in the Object Explorer pane. Right click on it. Select "Attach …" from the context menu.
Step 3
Select the mdf file to attach. In the Attach Databases window dialog, click the "Add …" button. In the Select the file folder tree of the Locate Database Files dialog, find and expand the directory with the mdf file. Highlight it and click OK.
Step 4
Add the new database contained in the mdf file. In the Attach Databases window, check that the path is specified correctly. Select the only item in the Databases to attach list. In the Database details group of controls, delete the item corresponding to the log file, if it is not found (the message Not found is displayed in the Message field). To do this, click the Remove button. Click OK.
Step 5
Start compressing mdf files. In the Object Explorer window, find the item corresponding to the newly added database. Highlight it. Click on it with the right mouse button. In the context menus, select the Tasks, Shrink, Files items.
Step 6
Compress the mdf file. In the Shrink File window, select the Release unused space option. Click OK and wait for the operation to complete.
Step 7
Take the previously attached database out of the control of the server. In the Object Explorer window, click on the item corresponding to the database added in the fourth step. In the context menus, select the Tasks and Detach items. Click the OK button in the window that appears. After that, the mdf file can be used at your own discretion.
