How to delete transaction log file in sql server 2008

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. Expand Databases , right-click it from which to delete the file, and then click Properties. 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. Rui Lima Rui Lima 6, 4 4 gold badges 28 28 silver badges 41 41 bronze badges. Worked like magic! I say this, because some years ago I shrunk a database in a disk where it has too few free space. In the shrink process, the files were getting bigger, and an Out of Space error was thrown.

Result: I lost the database. Luckly was a log database which had lose tolerance. Dragas There is a link to the official documentation in the answer, you may want to consider taking a look at it, at the official documentation. As I said nearly 5 years ago: if anyone has any comments to add for situations when this is NOT an adequate or optimal solution then please comment below Right click on the database name. Normally I've used DBCC before, but I just tried that and it didn't shrink anything, so I tried the GUI and it worked great - freeing up 17 GB in 10 seconds In Full recovery mode this might not work, so you have to either back up the log first, or change to Simple recovery, then shrink the file.

Peter Mortensen By shrinking the database you WILL grow the transaction log file. More Details in these simple, free, videos: sqlservervideos. Here is an exaggeration to demonstrate what is happening and why shrinking is absolutely critical on a periodic basis: Record A is changed 1 million times before a backup is done. What is in the log? If the logs are never shrunk you will never know what the true operating expense of the database is.

Also, you are hogging valuable resources on a SAN, most likely. Shrinking is good maintenance and keeps you in touch with your environment.

Show me someone who thinks you should never shrink and I'll show you someone ignoring their environment. Show 8 more comments. Michael Dalton Michael Dalton 4 4 silver badges 3 3 bronze badges. Johnno Nolan Johnno Nolan Shrink is must less risky, plus it's pretty simple to do. Is there not a risk of uncheckpointed transactions existing in the log? Show 1 more comment. Jonathan Jonathan Setting the recovery mode to simple will not, on its own, magically shrink the transaction log.

Aaron Not on it's own, no. I assumed that the OP would be using their test database, and therefore "the transaction log will very shortly shrink", but you are correct in that it's more of a side effect: Recovery mode of simple will probably make you end up with a shrunken transaction log soon — Jonathan.

The logfile's filegrowth was set to "restricted", and we'd been doing some immense activity on it I understand that it was an unusual situation. In our situation, where we had plenty of disc space, we increased the logfile size, and set logfile filegrowth to "unrestricted" To re-iterate: Do not do this in production.

That's all true, and I get that it was a development-only quick approach. I have on occasion not very often seen the SQL Server not be able to attach the database back to the database when the log file has been deleted.

This leaves you with a useless MDF file. There are several possibilities that can cause the problem. Transactions pending rollback come to mind. If you set up a job to do this every week, you're doing it very, very wrong. Yes, this just happened to us. We wanted to ditch 20G of log file as we'd just backed up the data before moving the database. Community Bot 1 1 1 silver badge. McRobert McRobert 9 9 silver badges 7 7 bronze badges. Rachel Rachel k 60 60 gold badges silver badges bronze badges.

I agree with your answer, except for the , 1 part. Check out his Bad Advice section for more info "Why you should not shrink your data files" Check out Paul's website - he covers these very questions.

The OP specifies a test database but it is a point well worth making for the more general case. Just setting the database to simple won't shrink the log file, in whatever state it's currently in. It just may help prevent it from growing further but it still could.

Majid Ali Majid Ali. This is the way that I clear log files on my dev boxes. It happened with me where the database log file was of 28 GBs. Mahendra Mahendra 4 4 silver badges 7 7 bronze badges. Leo Moore Leo Moore 2, 2 2 gold badges 19 19 silver badges 21 21 bronze badges. Never ever delete the transaction log. Part of your data is in the Log. Delete it and database will become corrupt.

I don't have rep to down vote. Take a backup of the MDB file. Delete or move the renamed log file. Ibrahim Ibrahim 63 1 1 bronze badge. The database automatically picks up the newly created log file.

Shashi Shashi 2, 15 15 silver badges 20 20 bronze badges. 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:.

I am running in simple recovery, why are you telling us to take a DB backup to empty our logs? It makes no sense. Very nice article, helped me delete the unwanted log files on an archive database. This solution should be on stackoverflow and microsoft so more people can find it.

There's lots of non-working solutions for this problem out there. Taking a Full backup does not cause transaction log file truncation. 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.