How to remove log file from sql database

Select the Files page. In the Database files grid, select the file to delete and then click Remove. Copy and paste the following example into the query window and click Execute. This example removes the file test1dat4. But command can work fast. In a word, they can shrink and then delete database file or log file. The difference is just the implementation. For Windows.

FileID is log file IDs for our database. Status indicates is VLF reusable or not possible values: 0 - yes, 2 -no. Now when we will insert data into the TestTable and monitor how log files are growing:. Note, that we can remove only the secondary log files. Removing the primary log file is not allowed by SQL Server. Each database has only one primary log file and the first log file which is created in the database creation script is considered the primary.

If we try to remove the second log file:. We can remove the transaction log file only when it's empty, therefore we first need to empty it. To do that, we should back up the transaction log. Since our "TestDB" database is newly created and there are no full backups, we need to issue a full database backup for the TestDB database, after which we can issue a transaction log backup:.

The transaction log backup truncates the log file there are some exceptions, which are out of scope of this tip. Log truncation deletes inactive virtual log files from the start of the logical log and releases space in the log file.

However, truncation does not reduce the size of a physical log file. It only frees space in it, which can be reused. However when we check log the information again, we will see that the logical log file still exists:. If we do not delete these temporary secondary log files over time, log file count will increase.

We select the Log option from the File type section as shown below. We choose the file we want to shrink from the file name. In this way we are able to transfer the data in the log file to the other log files. I receive an error saying the primary data faile cannot be removed. Related Articles. How to rename a SQL Server database. How to determine SQL Server database transaction log usage. Understanding how SQL Server stores data in data files.

Copy a SQL Server database with just the objects and no data. How to stop and start SQL Server services. Popular Articles. Rolling up multiple rows into a single row and column for SQL Server data. How to tell what SQL Server versions you are running. Resolving could not open a connection to SQL Server errors.