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
i need to delete my records in a table as well as reduce my .logfile size how?
plz give me replay@@
Hi Pinal,
I want to monitor log files of all DBs. If any of the log file is grown above 80%, then it should shrink. Please provide solution. Any script?
Hello Pinal,
I want to truncate my sharepoint config database and WSS_Logging database logs the size of sharepoint_config database is growing at a pace of ~10GB every week. I have scheduled a weekly full backup. Current .ldf file size is 113GB.
I am using SQL server 2012 with Always On High Availability feature. I am not able to convert the recovery mode from Full to Simple.
Please suggest in my case to reduce the log file what I need to do.
Thanks & Regards,
Paresh Jain
1. Are you taking regular transaction log backups?
2. Is the secondary replica synchronized?
What do you in for below?
Select log_reuse_wait_desc from sys.databases
1. Are you taking regular transaction log backups?
I am taking weekly full backup using maintenance plan in sql management studio. No, separate backup for transaction log backups has been schedule.
2. Is the secondary replica synchronized?
While I raised this query on my secondary replica it was showing Not-Synchronizing, but later I had to go to individual database under my Always On replica. and had to click Resume Data movement. Wanted to understand after failure is it normal that we will have to individually click on Resume Data Movement? Could you suggest.
After I clicked on resume data I took log backup and shrink the log file, the log file size was reduced drastically.
You are not taking t-log backups and still you are able to get the space back? I don’t believe so. I can bet my money that log backup is taken on other secondary replica.
Hello Pinal,
No, I said I am taking full database backup weekly, not taking transaction log separately. And the file size got reduced as I took manual transaction log backup due to oversizing/growing size of my sharepoint config database log file. Manual backup on both primary and secondary replica helped me to reduce my file size.
Now 2 queries:
1. Is it mandate to take a transaction log backup apart from weekly full backup to keep my transaction log in control.
2. Wanted to understand after failure over is it normal that on each database, we will have to individually click on Resume Data Movement, for secondary replica (primary replica got a roll of secondary after failover) to function in synchronizing mode? Could you suggest.
@Paresh
1. Yes. You MUST take transaction log backups to keep LDF size under control. This is irrespective of availability group.
2. If movement is suspended then resume is needed.
Hi,
I m trying to use the following code, but showing me the error . i m using sql 2008r2
‘truncate_only’ is not a recognized BACKUP option.
use dbname
go
dbcc shrinkfile (xyz_log, 1)
backup log dbname with truncate_only
dbcc shrinkfile (xyz_log, 1)
go
Please solve my query, it’s very urgent for me and my client.
use dbname
go
dbcc shrinkfile (xyz_log, 1)
dbcc shrinkfile (xyz_log, 1)
go
that will work without he backup. you can do the back up before
Thanks a lot .
Hello Team,
We have a SQL database server and the disk space becomes full due to the transaction log file which is huge as 750 GB. And the remaining space on the disk is around 20 GB, and I’m pretty sure this gonna be full at some particular point of time.
To avoid any future interruption on the transaction’s which are happening on daily basis, I need to shrink the file which will be relatively very small when done using the SQL server management studio. As to my knowledge this should be the procedure (select database -> Task -> Shrink -> Log and select the desired option in next window and click OK), please correct me if there is any alteration needed.
At this point, I have 2 questions
i) What will be the total time needed to complete the task (Log file data size is 750 GB).
ii) While this process is ongoing will there be any transaction mismatch/lost or affect anything from user side. In parallel is there any impact gonna happen due to this shrinking.
However, If the process is gonna take for several hours, can we make a partition/split this file to 6 or 7 sub-files depending on the size and time elapsed?
Kind Regards,
Thomas John
Hi,
I am getting below error,can anybody help me out
Cannot shrink log file 2 (TestLog) because total number of logical log files cannot be fewer than 2
i am unable to fix