How to delete or shrink a transaction log file.
-
The Trans Log of on eof my dbs has ballooned to over 8 GB!
What is the most direct method for removing and or shrinking the log file?
I do NOT need the transaction log data at all. It is a development db.
Reading the documentation just seems to turn me in circles, and no matter what directions I follow the log file never gets any smaller.
Joel
Takauma
-
You can truncate the transaction log and then shrink it.
Backup log <you db name> with No_log
Dbcc shrinkfile(<log file name>
You can set up recovery mode to simple if you don't want too big a trasaction log.
-
You can also use the option backup log dbname with truncate_only
then dbcc shrinkfile(logfilename,targetsize)
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
Hi,
dbcc
shrinkdatabase ({your_db}, truncateonly) always works for me.
Paul
-
After you shrink your databases why not change recovery mode from full to simple as you say you don't need them in a development database.
-
hi,as every 1 rightly stated u truncate the log and then perform dbcc shrinkfile...also change the recovery model to simple .... coz it will truncate the log file in that model....hence the log file wont grow......
[font="Verdana"]- Deepak[/font]
-
Thanks every 1. One more question:
Is there a T-SQl Command(s) that will change a db's "recovery model" to "simple?"
I am finding scattered documentation on the subject, but so far no directions on how to make the change.
Takauma
-
use master
go
alter database DBName
set recovery simple
or else you can also change the recovery model by
goto object explorer in Management studio---right-click the db---properties---opotions---recovery model........
[font="Verdana"]- Deepak[/font]
-
Ok,
I have used Backup log ... with no_log, then used dbcc shrinkfile(...). My transaction log did clear, however whenever I do more transactions on the DB the transaction log is filling back up.
I have the simple recovery model on this db with 'Automatically grow file', 'By Percent' and Unrestricted file growth' checked(the default settings I believe). Do I need to change these settings for my transaction log to stop growing? And what would you suggest these settings be?
I am getting confused with everyone saying "coz it will truncate the log file in that model....hence the log file wont grow." And mine is growing.
Thanks in advance!
Ken
-
Ken -
The growth of your Tlog is normal/correct behavior as you load/manipulate data within the database - the Tlog will grow to accommodate the largest single transaction. If you are manipulating a bunch of data within the database (e.g. a large update or select into) you can expect your tlog to grow and fill during the course of the operation, once the operation is complete the tlog will then remain at the same physical size but be largely empty...
If your growth is caused by loading a large amount of data in a single transaction you may want to take a look at using a bulk loader (e.g. bcp, SSIS, DTS) that can take advantage of the "bulk-logged" recovery model/option or limit the number of rows you commit in a single transaction to limit the growth of your tlog.
Joe
-
If none of the above solutions work for you, you can delete the log file by detaching the database, renaming the log file and then reattaching the database without the log file. This will create a new log file; I think it will be the same size as the log file when the database was originally created. Once the database has re attached you can delete the old log file.
Gethyn Elliswww.gethynellis.com
-
I have a similar problem where the database is already in "simple" mode but the log file blows out to 17Gb over a period of a couple of weeks.
The quick & nasty fix was to have a weekly 'Shrink' job in place but will investigate & implement a better solution sometime soon.
(SQL2K-b2187)
A lack of planning on your part does not constitute an emergency on mine.
-
The "blow out" to a much larger tlog size is usually due to a large transaction of some kind, whether a large data load or logged operation that consumes a lot of log space while it is running. Check and see how much of the space within the tlog is in use - it's not unusual to see a "large" tlog file that is mostly empty when running a database in simple mode.
Yes, you can continue to shrink the log every so often in the interest of saving disk space but I would probably try to figure out what it is that is that keeps causing the growth of the tlog - the reason that the tlog is growing like that is that some operation requires that much space - all you're doing by shrinking the log file is causing the system to have to reallocate that same space the next time around, better to understand why you need that much space in the log in the first place and maybe taking a look at that particular process if disk space is at such a premium.
Joe
-
Joe,
Agreed.
That is what I was saying before. I need to investigate the cause as to why this is happening - the shrink is just a quick & nasty to keep things running along without consuming all the disk space until I can get to it.
A lack of planning on your part does not constitute an emergency on mine.
-
Yes you should find the root cause of the growth and attempt to kill it. In the interim why not do frequent transaction log backups and just delete backups when a day old - or whenever you do your full backups? Assuming you have trunc log at checkpoint turned on, this should make the system commit transactions to the db file and lessen growth of the trans log.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply