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
Ok SQL 2008 this will not work on. For that you need to modify this slightly
use DB1
alter database DB1 set recovery simple
alter database DB1 set recovery full
dbcc shrinkfile(‘DB1_Log’,1)
dbcc shrinkfile(‘DB1_Data’,1)
Have fun
What would be best way to implement this theory on SQL 2008 DB? I am looking for something that would assist me with compressing my dbs as well as truncating the log files.
Rod
Why are we setting it to simple then to full, I am sorry I did not understand why DB1_Data and DB1_log has to be shrink, shouldn’t it only be log file that should shrink??? Thanks!
My database name is “VTT Breda” andi want to truncate the transaction log.
but while using the query
USE VTT Breda
GO
DBCC SHRINKFILEVTT Breda, 1)
BACKUP LOG VTT Breda WITH TRUNCATE_ONLY
DBCC SHRINKFILEVTT Breda, 1)
GO
some error is being shown due to the space in the db name.
Could you please help me in truncating the transaction log.
Hi Preema,
I am oracle dba now working on MSSQL 2000,2005
we take the downtime for truncate the log and use following way for truncating producation database log.
use master
go
checkpoint
go
dump transaction ReportServer with no_log
go
use ReportServer
go
dbcc shrinkfile (2)
go
We Uninstall MSSQL 2000 Standard edition
Now we reinstall MSSQL 2000 Standard edition
trying to attach old databases it display error .
Could not find row in sysindexes for database ID 7, object ID 9, index ID -1. Run DBCC CHECKTABLE on sysindexes.
We need Help.
Thanks alot it work like charm
Hello Pinal,
Just wanted to know, what to do in this scenario.
This statement does not work with SQL Server 2008.
It works fine with SQL 2000 / SQL 2005.
” BACKUP LOG WITH TRUNCATE_ONLY ”
So, with SQL Server 2008, do we have a replacement statement(s) that could be used to accomplish the same task.
Also, FYI the Log file has been set to 10 % Auto growth with unrestricted growth. At times, the log file grows as large as 80-85 GB, whereas the actual Database is barely 6-7 GB.
Any ideas on how we can set SQL Server to Automatically TRUNCATE the LOG file once it exceeds certain size.
Can DBCC SHRINKFILE (,transactionlogfile, percentvalue) be used ??
Thanks in advance. Regards,
Aashish Vaghela.
Hi Ashish,
The truncate_only option has been removed in SQL Server 2008.
The only workaround in SQL Server 2008 is :
1) Change the database recovery model to simple.
2) shrink the log file using DBCC SHRINKFILE
3) revert back the recovery model to FULL.
For your other question you can set up SQL Server agent alert which fires when log file size exceeds a particular value and in the response part of the alert u can fire a job that takes transaction log backup and shrinks log file size.
Best regards,
Ashwani
For SQL 2008 you have to follow below way to trunacate log file –
USE TestDB
GO
— Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE TestDB
SET RECOVERY SIMPLE;
GO
— Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (TestDB_Log, 1);
GO
— Reset the database recovery model.
ALTER DATABASE TestDB
SET RECOVERY FULL;
GO
Regards,
Nilesh Shah
Hello everyone!
NEED AN URGENT HELP!
My DB recovery model is set to simple.
But my logs are getting this message very often and my customer needs an explanation to this.
“BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated.
All my servers are Express Edition. So, they have max 4 GB limitation. Is 4 GB limitation include log file size?
If yes, I may need to run script given by you (after taking full backup).
Please let me know…
Hi Ramesh,
4GB limitation is for your data only, it doesn’t include your transaction log file…. so don’t worry
Hi Dudes,
Very Good Morning.
i want track all insert, update and delete activity on one table using trigger.
what ever user done on that table i want save that activity in another table.
any buddy tell me how can i track all activity in sql server.
thanks in advance
satish
Hi Satish,
Herewith, I wanted to share you one link which will show you small example about auditing with Trigger.
if you are using SQL Server 2008, there is one more efficient way to do so:
Hi pinal sir,
I am looking a database of website.many users logged into website and feeds some entry and data.
can i shrink log file(ldf), while user are connected to website,or they will lose some data when i will shrink log file.
pls guide me
Hi Hariom,
There will not be any issues of data loss but it is not a good practice to shrink log file manually now and then.
sir,
my log file having 38 gb size after shrink it is 34 gb.
what i should do now,
can u provide me script by which log file will shrink automatically after a partikular time.
@Hariom,
take a backup of your log and than try to shrink the log fie again. It may help…..
Hello Ashwani & Nilesh,
Thanks both of you !
I’ve tried your suggestions & they have worked for me.
Our .NET based application uses SQL Server 2005-based
Database & has a LOG file too. This baby grows tremendously
over a period of time. We either have to TRUNCATE it manually or use SQL Agent to create a JOB that will truncate it periodically.
Today I noticed that the LOG file (.LDF) was more than 900MB
in size & was slowing down the application to a lot of extent.
Truncating it didn’t help. So I used DBCC SHRINKFILE (LogFileName, 10) & it worked successfully. The 900+ MB Log was reduced to 10MB in a matter of few seconds.
Thanks once again …!
Regards,
Aashish Vaghela.
Sys.Admin.
Oh ! forgot to ask you all (including Pinal) a question.
Is there a way to monitor the LOG file & its growth.
So that once this LOG file crosses certain size limit,
a TRIGGER gets invoked & performs TRUNCATE or SHRINKDATABASE or SHRINKFILE operations .. ???
Thanks in advance .. !
Regards,
Aashish Vaghela
Sys.Admin.
hi pinal,
one database log file frequently full , i want write script log file full alert. please give soluiton to me//
Thanks
jernas
Is there a way to shrink all files present in a volume in a single transacation ?
Hey there,
Quick question:
To truncate a huge log file on SQL 2008…as backup log with truncate_only is not working anymore..Can i use this method instead:
1. Alter database…set recovery model to Simple.
2. Shrink the log file by right clicking on the database under option of tasks..and release the space to operating system.
3. Alter database…set recovery model to Full.
And, one more thing after doing this, Do i need a full backup of the database to restore it to point in time recovery in case of any disaster…or i still can use my old set of backups chain??
Does this method have any harm to use…?