By Hally | Last Updated
There's no doubt that log file will become larger and larger with the growth of SQL Server database file. However, it will influence the running speed of computer and occupy more and more space. This is not what we expect to see. So sometimes, it is necessary to shrink even delete SQL Server log file.
The following introduced ways will be applied to SQL Server 2014/2012 to delete log file with SQL Server Management Studio or Transact – SQL.
1. Login in SQL Server Management Studio. In Object Explorer, expand that instance that is connected to SQL Server.
2. Unfold Databases and then right-click the database that you want to shrink.
3. Turn to Tasks - Shrink, and then click Files.
4. Select the file type and file name.
Generally, log file will be shrunk after you click OK now. But if you want to set it in more detailed, options below can be selected.
Option 1: Select Release unused space check box.
Option 2: Select Reorganize files before releasing unused space check box.
If this is selected, the Shrink file to value must be specified. By default, the option is cleared.
Option 3: Enter the maximum percentage of free space to be left in the database file after the database has been shrunk. The value can be between 0 and 99. This option is only available when Reorganize files before releasing unused space is enabled.
But if we prepare to delete data, the primary data file cannot be made smaller than the size of the primary file in the model database.
Option 4: Select the Empty file by migrating the data to other files in the same filegroup check box.
5. Click OK.
1. In Object Explorer, make instance connected to SQL Server Database Engine and then expand that instance.
2. Expand Databases, right-click it from which to delete the file, and then click Properties.
3. Select the Files page. In the Database files grid, select the file to delete and then click Remove.
4. Click OK.
If you are familiar with Transact-SQL, follow this way to work for SQL Server database or log file deletion.
1. Connect to the Database Engine.
2. From the Standard bar, click New Query.
3. Copy and paste the following example into the query window and click Execute.
USE UserDB;
GO
DBCC SHRINKFILE (DataFile1, 8);
GO
This example uses DBCC SHRINKFILE to shrink the size of a data file named DataFile1 in the UserDB database to 8 MB.
1. Connect to the Database Engine.
2. From the Standard bar, click New Query.
3. Copy and paste the following example into the query window and click Execute. This example removes the file test1dat4.
USE master;
GO
ALTER DATABASE AdventureWorks2012
REMOVE FILE test1dat4;
GO
Comparing Way 1 to Way 2, the first way is undoubtedly easier for SQL Server new users. But command can work fast. However, it will be easier to controll failure rate when deleting SQL Server file using SQL Server Management Studio. In a word, they can shrink and then delete database file or log file. The difference is just the implementation.
Related Articles:
