How to Empty and Delete SQL Server Log FileThere'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.The following introduced ways will be applied to SQL Server 2014/2012 to delete log file with. Way 1: Delete SQL Server Log File in SQL Server Management Studio Step 1: Shrink Server log file1. 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.
Step 2: Delete SQL Server log file1. 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. Way 2: Delete SQL Server Log File with Transact-SQLIf you are familiar with Transact-SQL, follow this way to work for SQL Server database or log file deletion.
Step 1: Empty SQL log file1. 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;GODBCC SHRINKFILE (DataFile1, 8);GOThis example uses DBCC SHRINKFILE to shrink the size of a data file named DataFile1 in the UserDB database to 8 MB. Step 2: Delete SQL Server log file1.
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;GOALTER DATABASE AdventureWorks2012REMOVE FILE test1dat4;GOComparing 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:.
I created a database to test some ideas on with initial filesizes of 50MB and 512MB for the data and the log files respectively. The database was set to Simple recovery mode.
After doing to some work the data file was 150MB and the log file was +-414MB. Repeated font='Courier New'DBCC shrinkfile(2,0)/font commands brought it down to 127.8MB with 127.4MB free.
HowardW (3/29/2010)You cannot shrink a log file below the initial size it was created with.I believe the steps you'll need to take are:1) Detach the database2) Rename/delete the old log file3) Re-attach the mdf file, removing the original log file from the database details list (It will error if it's still in the list)SQL will then automatically create a log file in the default location of the default initial size which you can then move/resize.Do not do this - unless you are willing to have your database corrupted. It may work, but it is not guaranteed to work all the time.You can shrink the file to a size less than the initial size by specifying the size you want. The reason it doesn't shrink lower is because it cannot shrink the smaller than the active VLF. You can find that information by using DBCC LOGINFO in the database you are working with.In simple recovery model, you can issue CHECKPOINT several times to roll to the next VLF. You may need to actually write to the database and then issue the checkpoint to roll over. Once you have rolled over to the beginning of the log file - then you can shrink.In full recovery model, you need to backup the log to get it to rollover to the beginning.Review the link in my signature on managing transaction log files for additional information.BTW - what is the purpose of shrinking?
How To Resize Log File In Sql Server
Thanks Jeffrey. The point in shrinking the log file was that I didn't want to waste space with a large backup for a database that was just to test something.
However, my interest in not being able to shrink the log file then turned to one of curiosity and learning.Thanks for your answer.If anyone else is interested, it appears that font='Courier New'DBCC LOGINFO/font 'displays information about the number, sizes, and status of the virtual log files internally to the transaction log. It is an undocumented command and provides you information only.' .For more information regarding Virtual Log Files in SQL Server 2008, please see the MSDN documentation. SQL will then automatically create a log file in the default location of the default initial size which you can then move/resize.Do not do this - unless you are willing to have your database corrupted. It may work, but it is not guaranteed to work all the time.I wouldn't suggest it for production databases, but in which circumstances would it lead to corruption?When detatching a database (assuming it happened cleanly and didn't error), I was under the impression that transactions are rolled back/commited prior to detatching, so it's already consistent and the log is not required to roll forward/back transactions when it's next onlined?.
SQL will then automatically create a log file in the default location of the default initial size which you can then move/resize.Do not do this - unless you are willing to have your database corrupted. It may work, but it is not guaranteed to work all the time.I wouldn't suggest it for production databases, but in which circumstances would it lead to corruption?When detatching a database (assuming it happened cleanly and didn't error), I was under the impression that transactions are rolled back/commited prior to detatching, so it's already consistent and the log is not required to roll forward/back transactions when it's next onlined?The thing is - even with a clean shutdown there is no guarantee that the database will come back online without a log file. You are relying on the fact that there is nothing in the log file that would have to be rolled forward or back - but you can't guarantee that.What would happen if you had a long running transaction that was in process when you performed the shutdown? When the system comes back up - it can't roll that back and you now have a problem. Jeffrey Williams-493691 (3/30/2010)The thing is - even with a clean shutdown there is no guarantee that the database will come back online without a log file. You are relying on the fact that there is nothing in the log file that would have to be rolled forward or back - but you can't guarantee that.What would happen if you had a long running transaction that was in process when you performed the shutdown?
When the system comes back up - it can't roll that back and you now have a problem.Remember that the database was detached (not just shutdown) cleanly - so there could not have been any active connections at that time. I am pretty sure that still does not guarantee recoverability in all possible cases - but still. SQL will then automatically create a log file in the default location of the default initial size which you can then move/resize.Do not do this - unless you are willing to have your database corrupted. It may work, but it is not guaranteed to work all the time.I wouldn't suggest it for production databases, but in which circumstances would it lead to corruption?When detatching a database (assuming it happened cleanly and didn't error), I was under the impression that transactions are rolled back/commited prior to detatching, so it's already consistent and the log is not required to roll forward/back transactions when it's next onlined?The thing is - even with a clean shutdown there is no guarantee that the database will come back online without a log file.
You are relying on the fact that there is nothing in the log file that would have to be rolled forward or back - but you can't guarantee that.What would happen if you had a long running transaction that was in process when you performed the shutdown? When the system comes back up - it can't roll that back and you now have a problem.I see this as an absolute last ditch effort that should be avoided.Shrinking the log file is not a reason to go to this extreme - IMO.I had a jr try this once while I was out on vacation - same exact method. The database would not start. I had to come in off of vacation to get the database back online and shrink the log file. Shrinking the log file was successful and was successful in that scenario to below the original size as well. At which point I regrew the log file to defragment the VLFs.Typically there is a transaction in the database or the database thinks that a transaction is still ongoing. You need to find out what is causing it to be held like that and then shrink the log file after fixing that issue.
Perry Whittle (3/30/2010)as Jeffrey said DBCC LOGINFO and check for anything with a status of 2 near the bottom of the output. SQL server will not truncate or shrink (they are both different actions) past this point. Cycle the VLF's by checkpoint or multiple log backups, that's the approach i normally use!Agreed - but the point I was trying to make is that even detaching/offlining/clean shutdown does not guarantee that there are no transactions that have to be rolled back. If you detach the database and force all connections to close before detaching, there could have been an open transaction that was terminated. Is that transaction guaranteed to be fully rolled back before the database is detached?Either way - the risk is too high for me that it.might.
not work, so my advice is to not take the chance. Deleting a log file should only be a last resort, and only if the log file has already been damaged to a point where you can't recover anyways.