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
Munshi Verma/Pinal Dave,
Hi..
Please correct me… Is it good practise to use BACKUP LOG WITH TRUNCATE_ONLY? I have also given the problem am currenly facing. Any help is appreciated.
Currently, the production database is 85Gb but the log file grows really huge to about 200GB in a week time.
Everytime I use the below steps:
1.backup log with no_log
2.shrink the log file. Right click the correct database and choose shrink file ->chosse the log -> ok
Please give me a good solution for my above problem. As well let me know a way to automate the truncation of the log file.
Thanks,
Janani
Hi
i have set up a databse mirroring.
i will like to know if i use any above option do i need to stop mirroring at the time of operation.
Thank you for your helpfull sulution
but only i make small notation in syntax by example :
USE sherbydb
GO
DBCC SHRINKFILE(‘sherbydb_log’, 1)
BACKUP LOG sherbydb WITH TRUNCATE_ONLY
DBCC SHRINKFILE (‘sherbydb_log’, 1)
thnk you for your successfull solution
but i will add an example as a help for qutation locations:
USE mydb
GO
DBCC SHRINKFILE(‘mydb_log’, 1)
BACKUP LOG mydb WITH TRUNCATE_ONLY
DBCC SHRINKFILE (‘mydb_log’, 1)
Hello,
After i run this statments, i take the message tha the folder is incorrect.
The log file is in logical size now, but when i try to open a table in the database i teke the message “the system cannot find the file specified”
Please help me
Thanks George
Also, my database name is EP14SQL, so i use the command “USE EP14SQL”. But in my sql server i have many databeses, and i see that i take this message to all tables at all databeses.
All .mdf and .ldf files have modyfied date the current and i hope not to damage all databases.
Please i need your help immediatly
Thanks again
Sorry for my extented posts.
I restarted the machine and all look to work correct!!!
I guess the problem was that when i run the statments, i was out of disk space and there was a problem with the file system.
Thanks guy’s
Hi Guys,
I am a bit confuse in Truncate and shrinking……when we say truncating the log file what does it mean?
As per my understanding shrining means – to compress the the log file size which is left empty. e.g. if in 100 gig log file 40gig is used and 60 gig is unused then we can shrink it upto 60 gig. Now wts truncate here and wts the + and – points of truncate as shrinking???
* truncate and shrinking(sorry type mistake)
Hi,
I am working as a DBA, for supporting Development team, My Question is:
I am handling different database. I Created Developer role and give it to all the developers. and another Role “Admin” created and give it to Team leads. My requirement is if the TL change any database objects, is there any way to capture the changes? like auto generated mail to my mail box? that mail should tell that, from Machine A, the object changed on particular Database….like that?
I will appriciate, if any one give the solution…
Thanks in Advance.
Hello, I am looking for a sql query that will return the location of the logfiles, as a string. Could you please help?
Hi,
What are the correct steps to DAILY backup, with Full recovery, without loosing the transactionlog chain?
This is what I want to do.
Full Backup Database (Already heaving the first backup)
Daily:
Transaction Bakcup (23.00 hours)
Full database Backup (23.05 hours)
And than:
USE mydb
GO
DBCC SHRINKFILE(’mydb_log’, 1)
BACKUP LOG mydb WITH TRUNCATE_ONLY
DBCC SHRINKFILE (’mydb_log’, 1)
If i need to recover to a poin in time I also need to make an additional backup of the active Transactions.
Is this sufficient or do I have a kink in the chain?
Regards,
@DBAtor
Sorry:
You can set up an WMI alert(SQL Server 2005) that will send an email to you when database object has changed.
@DBAtor.
sorry: This will help. just modify it
CREATE TRIGGER [ddlDatabaseTriggerLog]
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
SET NOCOUNT ON;
DECLARE @data XML;
DECLARE @schema sysname;
DECLARE @object sysname;
DECLARE @eventType sysname;
SET @data = EVENTDATA();
SET @eventType = @data.value(‘(/EVENT_INSTANCE/EventType)[1]’, ‘sysname’);
SET @schema = @data.value(‘(/EVENT_INSTANCE/SchemaName)[1]’, ‘sysname’);
SET @object = @data.value(‘(/EVENT_INSTANCE/ObjectName)[1]’, ‘sysname’)
IF @object IS NOT NULL
PRINT ‘ ‘ + @eventType + ‘ – ‘ + @schema + ‘.’ + @object;
ELSE
PRINT ‘ ‘ + @eventType + ‘ – ‘ + @schema;
IF @eventType IS NULL
PRINT CONVERT(nvarchar(max), @data);
EXEC msdb.dbo.sp_send_dbmail
@recipients = ‘youremail@yourdomai.com’,
@body = ‘somebody has made a modification.’,
@subject = ‘MODIFICATION MADE’
END;
ENABLE TRIGGER [ddlDatabaseTriggerLog] ON DATABASE
GO
Hi Pinal,
I am having issue with Replication. I have Created Replication was working great. When hard drive fail I lost distribution database. Now I am trying to Recreate Replication It will not allow me to Create Replication. I have remove replication. I tried to delete distribution database it says It is alredy using replication.
When I query from master database using
select * from sys.objects I can see distribution database in list.
Please Help my on this issue.
Thanks
Rakesh
Hi,
I’ve a peculiar problem with the SQL Server installation on one of our servers.
The SQL Server Agent is not visible in the Management Studio on one of our Servers. Also, the following error is getting displayed when we log in to the server :
Could not find stored procedure ‘master.dbo.xp_MSplatform’.
Any idea what could be the problem and how to resolve this ?
Thanks in Advance
Girinath
Hi,
My database is in full recovery and i regeraly take the backups
nw i Want to shrink my log file.I want to knw wether is it necessary for me to execute
Backup log dbname with truncate_only
or directly execute
Dbcc shrinkfile()
So what is the difference between the Backup with truncateonly and shrinkfile
Please help me understand. I am taking a full backup once a day, and transaction logs once every hour. Why is my LDF file not retaining a “normal” size? It continues to grow. I do not want to break the chain and use truncate only, though I have done this and it fixes the problem. I would very much like to understand the underlying problem.
thank you in advance
@LauraV.
You did not mention which version of SQL Server you are using, Either 2000 or 2005, ( would be easy for us to narrow our answer to questions)
When ever you take transactional log backup, no matter if you scheduled it or if you take it manually, SQL Server by default will empty transactional log. Meaning after the transactional backup ( Be Careful NOT FULL BACKUP) SQL Server will remove inactive transactions from logfile.
Which means your transaction file is empty after you take transactional log backup, Empty doesnot mean your logfile became small, NO. the size of the log file will still be the same but it will be empty, all you have to do is shrink logfile, run below command,
use database_name
Dbcc shrinkfile (logfilename, 1)
and it will shrink the log file to its minimum size possible,
In SQL Server 2000:
Right click database name -> all task -> backup -> in the dialog box, select transactional backup, and click on options ( at the top left side of the box) you will see “remove inactive entries from Transactional log”.
This means when you take transactional Log backup, SQL Server is removing all inactive entries from SQL Server log file. This is default setting.
In SQL Server 2005:
Right Click database name->task ->Backup in the Dialog box, select backup type Transactional backup, and click options tab (at left side up) and under Transaction log section you will see “Truncate the Transaction log”.
Which mean when SQL Server 2005 performs transactional backup it truncates logfile. This is also default setting.
NO matter if you use SQL Server 2000 or SQL Server 2005, SQL Server by default (can be changed) will truncate log after performing Transactional backup.
So simple solution will be shrink log file after you take transactional log backup. You dont have to do it manually You can send this as a response to this transactional bacup job, when ever this job succeed, response to that would be run this script
use databasename
dbcc shrinkfile ( logfile_name, 1)
That might work.
I will take one more minute to explain what is this active and inactive transaction in the logfile . Please correct me if I am wrong.
This is how SQL Server works,
1. When a transaction comes to SQL Server, it first comes to transactional log buffer, and then it is hardened to disk ( log file, .ldf ) and then it is written to data file ( .mdf). Then we say the transaction is committed or it is inactive, because the transaction performed all the actions that it should.
On the other hand, when a transaction comes to SQL Server and it is entered into log buffer and also in transactional log, but not yet entered in data file, its still in the process, then we will say this transaction as active transaction.
One important point to think is, all the inactive transactions in the log file ( transactions which completed their tasks and are entered in data file) are also present in data file, and SQL Server is smart enough to think, inactive transactions are already in data file and also, they have been backed up( by transactional log backup) hence it thinks, this is the time to get rid of this data and it removes all the inactive transactions.
But for Active transactions, which are either incomplete or could not complete because of disaster ( sudden power failure….) will be stored in transactional log and will be called acive transactions untill they are entered in data file.
This will be little bit confusing…
Read more about this in books online.
Hope this helps.
Imran.
Thanx Pinal Dave ….
Thanks for your code for SQL SERVER – Shrinking Truncate Log File …
I tried it and its working fine