UPDATE: Please follow link for SQL SERVER – SHRINKFILE and TRUNCATE Log File in SQL Server 2008.
Sometime, it looks impossible to shrink the Truncated Log file. Following code always shrinks the Truncated Log File to minimum size possible.
USE DatabaseName
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO
[Update: Please note, there are much more to this subject, read my more recent blogs. This breaks the chain of the logs and in future you will not be able to restore point in time. If you have followed this advise, you are recommended to take full back up right after above query.]
UPDATE: Please follow link for SQL SERVER – SHRINKFILE and TRUNCATE Log File in SQL Server 2008.
Reference : Pinal Dave (http://www.SQLAuthority.com), BOL
314 Comments. Leave new
Hi John,
First check the free space available in mdf file and accordingly shrink the file to set the new size. For example a file named file1 of size 95 GB, has 15 GB free space then shrink with followinf command:
DBCC SHRINKFILE (file1, 80000)
Make sure that this process should be run in off-working hours because it could bloack user’s processes.
Regards,
Pinal Dave
Hi Pinal,
I am happy to your reply.
I will check and reply you later
Thank you ..
Hi Pinal!
Thanks you very much. It really works on me.
Regards..
Hi Pinal,
I used without SHRINKFILE without target size (if missing, it take default size.. so i missed this parameter)
DBCC SHRINKFILE (file1)
It takes long time (above 5 hours) and still the query is working.. how long it need to complete its process ?
Is it provide any problem ?
What shall i do now ?
Thanks in advance
Hello John,
It seems you are shrinking the data file. Shrinking a data file while the database is in use may block or hang the processes.
If data file has a lot of free space % then use SHRINKFILE with TRUNCATE_ONLY. It would instantly release the free space to OS.
Regards,
Pinal Dave
Thanks pinal ..
Hi
Thanks pinal
Sometimes i was unable to shrink log from database now it’s working fine
Regards
Jayant
The WITH TRUNCATE_ONLY doesnt work anymore. What should I use instead?
Hi
Thanks a lot
Its working
I tried with many solutions provided by various websites.
But its working!!!!!!!!!
Thanking U Again
following only works
USE MyDB
GO
DBCC SHRINKFILE(‘MyDB_log’, 1)
BACKUP LOG MyDB_log WITH TRUNCATE_ONLY
DBCC SHRINKFILE(‘MyDB_log’, 1
after I do below steps
all u have to do is doing this steps and u will shrink ur database form enterprise manager and query analyzer
1. open enterprise manager.
2. right click on the database u wanna shrink it.
3. click on properties.
4. from the data properties go to options.
5. in the middle u will see recovery model make it “simple” then click on “ok” and try to shrink the database.
it works 100%
I was getting error that all log files are in use. refer link
Lot of thanks to everyone & Dave
Thank you for leaving this up. Its 11:45 pm on a Saturday night and I’m dialed into my server with an outrageously large log file. This saved my bacon, or at least kept me from having to go on site.
Hi I have a Problem with attach/detach
I detached my database then I am going to attach the database , buy it’s giving some error , the error is as follows..,
Error 823:I/O error 38(Reached the end of the file.) detected during read at offset 0*000002ac680000 in file ‘S:MSSQLDataActivateMBBCards_log.ldf’.
Please Provide the answer
I am curious when is the proper time to the run:
DBCC SHRINKFILE(, 1)
and
BACKUP LOG WITH TRUNCATE_ONLY
In relation to the database backups.
Before/after a full backup?
Before/after a log backup?
Or doesn’t it matter?
Thanks,
~Peter
Hello Sahasra,
There is some read-write error in the log (.ldf) file. Restore the database without log file using sp_attach_single_file_db that would create new log file. Use this method as last resort as the older log file is removed and recovery is not performed that can turn your database into inconsistent state.
Regards,
Pinal Dave
When is the best time to run:
DBCC SHRINKFILE(, 1)
and
BACKUP LOG WITH TRUNCATE_ONLY
in relation to backups?
Before/after a full backup?
Before/after a log backup?
or doesn’t it matter?
Thanks,
~Peter
Hello Peter,
If you are keeping the transaction log backup then you should never execute “BACKUP LOG WITH TRUNCATE_ONLY”.
You should use SHRINKFILE command after taking the full and transaction log backup.
Regards,
Pinal Dave
opps i didn’t think my first post worked…sorry for the double post….
Hello Pinal ,
Thanks for your reply , but I dont have the database , I dettached database, how can i run the query..
Thanks & Regards
Sahasra
hello Pinal
Can You provide the entair script Plz..
BACKUP LOG WITH TRUNCATE_ONLY — Will it change the LSN Number
Of course it will change the LSN number that is used to identify the next backup log file while restoring. The MinLSN number also decide the inactive part of a transaction log file.
Regards,
Pinal Dave
Thank you pinal….
it is working fine.
Hi
I am in deep trouble one of my colleague deleted the .mdf file of db in sql 2005, Now there is no object can be seen in db. I want to take tail log backup for my safe side. Can I recreate .mdf file whereas .ldf file is still available on the disk. I can not recreate the .mdf file then how can I take tail log backup ?
If I can recreate .mdf file, give me step to recreate the same.
Hi Brijesh,
Unfortunately log file contain only transaction not real data so you can’t create MDF file from log. there is only option available for you to restore your database with latest backup.