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
Reference : Pinal Dave (http://www.SQLAuthority.com), BOL
pinaldave
Thanks for your code for SQL SERVER - Shrinking Truncate Log File - Log Full
I tried it and it worked!!!
I wish there was something as plain and simple in Books online.
Albin
SQL Server 2005 Books Online:
“[TRUNCATE_ONLY]
This option will be removed in a future version of SQL Server. Avoid using it in new development work, and plan to modify applications that currently use it.
…
We recommend that you never use NO_LOG or TRUNCATE_ONLY to manually truncate the transaction log…”
Yeah Its True JAHA …all situations depends;
The NO_LOG or TRUNCATE_ONLY options truncate the transaction log. When you use these options, you might not receive a full database backup!!
I feel that in OLTP envirnment where DML commands are running most of the time .
it creats a lock on .LDF and doesnt allow physical update on LDF ..is it true champ then what we can do such situation .
As you mentioned above in the article that use syntax
USE DatabaseName
GO
DBCC SHRINKFILE(, 1)
BACKUP LOG WITH TRUNCATE_ONLY
DBCC SHRINKFILE(, 1)
, 1 refers to MB if i am not wrong …my question is
1) can we set the minimum size for transaction log
2) If we can ,then we need to mention the minimum size rather then 1 .if i am not wrong .
3) if we simply mention the DBCC SHRINKFILE (ABC_LOG)without mentioning the Size then how much will it reduse .
4) I found sometime command dosent work it comes with error ..
1 refers to the identification (ID) number of the file to be shrunk. To obtain a file ID, use the FILE_ID function or search sys.database_files in the current database.
SELECT file_id, name FROM sys.database_files
If target_size is specified, DBCC SHRINKFILE tries to shrink the file to the specified size. If not specified, DBCC SHRINKFILE reduces the size to the default file size.
1 refers to the identification (ID) number of the file to be shrunk. To obtain a file ID, use the FILE_ID function or search sys.database_files in the current database.
i.e.
SELECT file_id, name FROM sys.database_files
If target_size is specified, DBCC SHRINKFILE tries to shrink the file to the specified size. If not specified, DBCC SHRINKFILE reduces the size to the default file size.
Thanx SAM
…..! Can we Shrink LOG file while using DML commands by users !
If yes Why , If no Why ..!
Thanx in advance
If primary transaction log file is exceed in large volume then is it good to restrict size , is there any wrong impact if we restrict
the size.
Very good question Nirav,
If log are left to un-restricted growth they will grow bigger than hard disk space available it will crash SQL Server completely.
The best way to handle SQL Server logs are to have un-restricted grown with frequent back up of log.
This will also add security to your system when it fails you can restore using logs (conditionally you have full backup to begin with).
Kind Regards,
Pinal Dave (http://www.SQLAuthority.com)
…..! Can we Shrink LOG file while using DML commands by users !
If yes Why , If no Why ..!
Thanx in advance
Pinal ,
If my Log Grows bigger then Disk space , will my database comes in SUSPECT MODE ..or crashes the SQL server completly .
Scenrio :
SQL install on C: and Log files on D: ….in this condition also can crash my SQL INSTANCE …
Praveen
Hi Praveen,
If your lLog Grows bigger then Disk space too much it will crash the SQL Server.
Regards,
Pinal Dave (http://www.SQLAuthority.com)
Thanx Pinal Dave ….
In both the snerios as i mentioned a previous post !!
And offcourse i would like to thanx for this Shairpoint where we can discuss our views Thanx once again !
How to truncate log file in SQL Server 2005
SQL Server 2005 is quite different from SQL Server 2000. To truncate log file is one thing which is different from SQL Server 2000. In SQL Server 2000, you just use Shrink to whatever file size you like. In SQL Server 2005, sometime I cannot shrink the log file at all.
Here I want to describe some tricks to truncate log file for a database in SQL Server 2005. The work environment is MS SQL Server Management Studio.
I. Shrink the log file size at the right time
I found out this trick:
Immediately after I use the SSIS package or Import the data to the database ( highlight the database->Tasks->Import data … ), or Export the data from the database ( highlight the database->Tasks->Export data … ), I can shrink the log file to the desired size, for example, 1MB. That is, highlight the database->Tasks->Shrink->Files
set the file size, say, 1MB.
Then, click OK and you are done.
II. Eliminate the log file completely
Sometimes we just do not need the big log file. For example, I have 40GB log file. I am sure I do not need this log file and want to get rid of it completely to free up the hard drive space. The logic is
a. Detach the database
b. Rename the log file
c. Attach the database without the log file
d. Delete the log file
Let’s say, the database name is testDev. In the SQL Server Management Studio,
Highlight the database-> Tasks->Detach..-> Click OK
Go to log file folder -> rename the testDev_log.ldf to be like testDev_log-aa.ldf,
Highlight Databases->Attach…-> Click Add -> add the database testDev, highlight the log file and click the ‘Remove’ button. This means you only attach testDev.mdf
After this is done, you can verify the contents of the attached database and then delete the log file.
Hi Praveen Barath,
Good Explanation. Very good understanding of subject.
Regards,
Pinal
My pleasure Chief .
Hi Pinal,
I was wondering if there is a way to shrink all log files of all databases. I’m trying to do a script to acomplish this task and so far I cannot make it work.
The problem is that you have to issue a Use DatabaseName before using the shrinkfile(Filename_log) statement. I tried using the Exec and the dynamic command execution but it doesn’t seem to work. I appreciate any help you could give me and keep up the good work!
Regards.
Thank you.
We recently migrated a server to a new PC image. We reinstalled the SQL server to the same versions of the original PC. After this migration our TempDB has started to grow tremendously fast.
We have implemented a purge of the TempDB to prevent the server from crashing.
Is there a way to see what is making the Tempdb grow?
Is the SQL Server log files can help us to restore our database in original situtation if it crashesh.
IF Yes
HOW?? (In SQL 2000 and 2005)
ELSE IF No
THEN WHAT IS THE USE OF LOG FILES???
END IF
Hi Prashant Pandey,
Please search your questions at http://search.SQLAuthority.com
You will find many of your questions already answered.
Kind Regards,
Pinal Dave
Thanks.. Pinal
Hi
This is Dhirendra , n have the problem in SQL Server 2000 as Log file is full while in my Databse Table not much more data.
Thanks
Hi My name is Jitendra Verma,please tell me the procedure of reducing the log file size.
[...] 22, 2007 by pinaldave About a year ago, I wrote SQL SERVER - Shrinking Truncate Log File - Log Full. I was just going through some of the earlier posts and comments on this blog and one particular [...]
[...] SQL SERVER - Shrinking Truncate Log File - Log Full(Script) [...]
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.
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.
[...] 21, 2008 by pinaldave Imran Mohammed continues to help community of SQL Server with his very enthusiastic writing and deep understanding [...]
Thanx Pinal Dave ….
Thanks for your code for SQL SERVER - Shrinking Truncate Log File …
I tried it and its working fine
Hi Pinal,
I keep on follow your blogs - really very good expla.
1. can you tell me what measures I need to take on server level to perform my application will run fast - .NET 2.0 web application?
2. I want to write a script to create a database - parameterized script.
Regards
Seema Reddy Akkala
Hi Pinal,
Pls - notice we are using sql server 2005 - .NET 2.0 web application
I am setting up a maintenance plan to provide daily backups for a set of new databases in SQL Server 2005. I obviously want to keep the log files from groing. Will “Full” backups of each database accomplish this? Do I need to set up a Full Backup and a Transaction Log backup?
Thanks,
Frank
Most of these scripts have a serious (but easy to fix) flaw - they set the size of the log file to 1 megabyte!
Emeka (#43 above) has already pointed out the DANGER of using the TRUNCATE_ONLY option - do not use that option if you require full-time availability and no data loss. It can be made to work - Kimberly Tripp recommends taking a full backup, truncating the log, and then taking another full backup. But, you have to be able to afford the downtime.
If you are going to shrink your log, NEVER shrink it to 1 megabyte! You want to shrink it to where it should be. Otherwise, you get a very inefficiant log file with lots of internal fragmentation.
And where should it be? In a system with proper backups of the log file running successfully and with no long running transactions, the log file IS ALREADY THE SIZE IT SHOULD BE.
As was stated above by Emeka (#46 above), if a log file keeps growing continuously, you have another problem which needs to be fixed, before you shrink the log file.
When you do shrink a log file that got out of control, don’t try to set it to 1 single megabyte!!! Set it to 2 or 3 times the size of the database, if you have no other clues as to the proper size.
David
Hi Pinal…
We have an option with one of our client like disk space alert, when the usage os disk reaches 90-95% it wil create an alert and send an email to all DBA.
All the time Critical suituation is due to growth of LOG files only, rarely on MDF files. Is there any query to find which file MDF/LDF is growing or which is adapting more space.
Is there any way to find the file in short time period because we have to resolve it as quick as posible.
Regards,
Senthil kumar Rajendran.
Hi,
I have a question.
I am going to delete a huge chunk of data for which my log files will be increasing a lot.
I want to know is there is any kind of dml statements which will not log any data into the log files.
Or is there is any way that the to truncate the log file before i start deleting.
I will be deleting large chunk of data and then i will have to shrink the database as shrink file did not work that efficiently.
Also is there is any way while i delete the tables can i disable all the constraints of that table. My deletion is done dynamically or using the ad-hoc query. So for that reason my contraints gives problem.
I want something like this..
disable table name constaraints.
delete table
enable constarain of that table.
Thanks in advance.
Palin, thanks for the info to shrink SS2000 log files. Backups failed for a while and the log files grew. Now the backups are working again but I needed to reset the log file and your instructions were very helpful!
Pinal says:
>>If log are left to un-restricted growth they will grow bigger
>>than hard disk space available it will crash SQL Server >>completely.
Is this really true even with recent versions of SQL such as 2008?
It’s hard to believe that SQL treats running out of disk space so ungracefully.
regards,
Lee
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!
This commands help me to shrink log file quickly, thanks
No one has put the light on the script which can run on all databases on the server.
EX: If I have 100 Databases and want to run the command DBCC Shrinkfile on all the DBs at one go…Any suggestions/solution appreciated.
Thanks in Advance
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!
Hi all;
Been reading all the comments and would like to “shrink” my log file down to a respectable size and get rid of all the “white” space.
Can I run these commands below if there has not been a backup on either the db’s or logs in quite a while?
Also what implications are there using the NO_LOG option?
Thanks in advance, Steve
USE DatabaseName
GO
DBCC SHRINKFILE(, 1)
BACKUP LOG WITH TRUNCATE_ONLY
DBCC SHRINKFILE(, 1)
Hi,
The above query to truncate transaction log is most useful fpr me. Thank you very much
Hi Pinal,
I am new to this forum. We have a DB crash down yesterday due to increasing log file ran over the disk space. We have restored the DB from the backup taken on mirrored server.
We want to reduce the size of log file. Should we use shrink file comand? It would be helpful if you reply with the commands to be run over the SQL server 2000
Thanks in Advance