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
Thanks for your code
It is working perfectly
Thanks pinal for code
It is working fine
i am ahmad from jordan.
i need ask you that if i have machine installed sql server 2000 database
if i want to take a backup from database then i want repair it on another machine.can i do?if yes how i can do it
thanks and regard..
1. backup your database from machA.
2. Copy it to machB.
3. Restore the backup by select “file”, type in the new database name, and change the path if neccessary.
4. Now you can repair it on machB.
After finish repairing it, you can detach and replace the database on machA.
Thanks for this tip. I used it today and it worked for me. I had a log file that was 10GB causing significant problems on my production server. Now I have reduced it to 30MB. This was very helpful. Thanks
Hello,
I saw your code and is very helpful, what I assume or understood from the code is, we are taking backup of the log file and then shriniking it with truncating_only option ( I think it realease the space to operating system, I assume I am not sure).
Is there any way, where we can truncate and shrink the log file with out taking backup.
Like I did some transactions for that I dont need a log backup. I want to reduce the size of log file, shrinking would be the option. But without taking backup can we shrink the file.
I tried it many times, the size of the log file did reduced initially later when I tried it to reduce more, the usage space is increasing and the free space is reducing the total space remaining same as previous, meaning its logging every operation. Which I dont want it to do.
Is there any way to do this !
regarding my previous questions, reducing the size of log file with out taking backup.
Can we use this :
dump transaction Db_name with no_log
I know Dump is for older version and is synonym to back_up as explained in this thread.
So does this code still takes backup, I am sure this time it will not log, because I gave no_log option.
but can we execute this statement when we dont have space on the disk, Lets say no disk space, if this code does takes backup then this is not what I am looking at ?
Please clarify !
My database (sys files) getting rename and i could not open my database and it also not porformed backup database action…………// please tell me what i do
regards
Thanks. It really works great and running~!!!
Thanks again man~
Pinal,
Excellent tip thanks a lot!
I had a log that was 17GB in size now its ok
Best regards and keep up the good work!
Praveen,
I tried the detach method of shrinking a log file and it did not work. When I reattach it errors stating that the log file can’t be found.
Thank
Dave
Hi Pinal,
I had this problem and it took me hours trying to figure out how to reduced the size of the file and now just by copying and pasting your command and running it just did it.
Your approach is 200% better than the article I was trying to follow on MS KB.
Problem SOLVED!
Thanks again Pinal. You are a legend.
Regards,
Peter
Hi Dave O,
Please understand that the DETACH and ATTACH methods are not meant to truncate log files.
What Praveen told was just a workaround to get rid of a transaction log file altogether which has grown too large and is no more required because if you don’t specify a transaction log file while ATTACHING a database, one is automatically created for that database and you can delete that unwanted transaction log file which grew in size.
DETACH and ATTACH method is ideally used to move your files (data and transaction log files, i.e. .mdf and .ldf files) around on a server. For example, if you want to move data and transaction log files of a database that are located on say X:\Data\ and X:\Log\ folders respectively, to a different drive and location, say Y:\Data\ and Y:\Log\ respectively, then you can DETACH the appropriate database and copy its .mdf and .ldf files to the target locations as shown above and then simply ATTACH the database, specifying the correct path of the files, that is, your target file locations.
In order to truncate the transaction log files, I suggest you use the code that Pinal has already displayed in his previous blogs.
I am pasting the code for your reference:
USE DatabaseName
GO
DBCC SHRINKFILE(, 1)
BACKUP LOG WITH TRUNCATE_ONLY
DBCC SHRINKFILE(, 1)
I also absolutely agree with Pinal on having frequent transaction log backups, as the transaction log gets truncated automatically after every transaction log backup happens.
Should you still have any confusion regarding truncating the transaction logs, you may write to me or Pinal Dave.
Cheers!
Munshi Verma
Hi Praveen,
How are you doing? Hmm! Nice to see you here.
Well let me answer your question that you asked on August 7, 2007, as I see no explanation to this question of yours.
Your question was:
…..! Can we Shrink LOG file while using DML commands by users !
If yes Why , If no Why ..!
Thanx in advance
————————————————————————–
Answer to your question is:
Yes, We can shrink the transaction log files while DML commands are being executed by users on the associated database because the DML commands would affect the active portion of the transaction log file and the inactive portion can still be truncated and shrunk.
Note that we can’t truncate the active portion of a transaction log file.
Cheers!
Munshi Verma
Hi, Munshi Verma.
Thanks for your reply.
Execuse me all i was not in touch with blog for long time , cought in some production work.
BR
Praveen
Use the following statement for finding file id number in sql 2000
use master
SELECT * FROM sysdatabases
TRUNCATE_ONLY is a backup Log statement. It performs a checkpoint to manually force the transaction log to be truncated. This truncates the log by discarding everything from the log. This option frees space but risk possible data loss. After the log is truncated by using NO_LOG or TRUNCATE_ONLY in a backup log statement, the changes recorded in the truncation portion of the log are not recoverable until the next full database backup.
Therefore for recovery purpose, after using either of this options, immediately take a full database backup. Never use TRUNCATE_ONLY to manually truncate the transaction log(especially production DB in full recovery mode) because these breaks the log chain. Until the next full DB backup, your database is not protected from failure.
Penal:
I was little confused and my result seems to be same for reducing log and data space. There is little secret running the dbcc shrinkdatabase script.
Truncating did not help me.
backup log ‘databasename’ with truncate_only (did not help much)
Below is the correct way to get rid of all data and log file full issue. Now my available free space is same as file capacity.
This works for me and reduced my log file to its full capacity.
dbcc shrinkdatabase (N’database name’, 1, truncateonly)
dbcc shrinkdatabase (N’database name’, 2, truncateonly)
dbcc shrinkdatabase (N’database name’, 3, truncateonly)
dbcc shrinkdatabase (N’database name’, 4, truncateonly)
Best solution:
sp_helpdb gives you fieldid in a database, thats how I got those 1, 2, 3, 4, 5 etc.
* Find how many data & log fileid your database carry.
* Run one by one as my above dbcc shrinkdatabase script.
* Remember 1, 2, 3, 4, 5 is depend how many file consists of the database.
This site is a life saver for me. Keep up the good work Mr. Penal.
-sikander
Thank you VERY much. I have been all over looking for a simple solution, and have run a number of scripts and wizards to no avail. I appreciate your easy and EFFECTIVE script!
hi
I am deepak Sharma
i am facing log full space full problem
My ldf file size is 90 GB and my datadase size is 20 gb. I want to delete my old ldf file while i am taking backup
is that possible or not ?
Is that any tecnique to delete old ldf file after taking full database backup through wizard?
How can i overcome this problem ?
Hi Sharma,
The size of your log file tells me that your DB is in full recovery mode and you are not taking a transaction log back.. or you are running a long transaction.
To get out this situation,
take a full backup of your db, take a transaction log back.
Transaction log backup(dumps) removes transaction that has commited from the log file. Once you have taken a transaction log back, Shrink your log file.
NOTE: You can not delete your log file.