Transaction Logs - unrestricted?
-
I have a couple of SQL 2005 databases where the transaction logs are set to unrestricted growth, in fact they are now nearly as large as than the actual databases.
The settings for the transaction logs are:
- Enable autogrowth is checked
- File growth = 10%
- Maximum File Size = Unrestricted File Growth
What will the effect of setting the Maximum File Size to (say) 300MB be? Will I lose data in the tables? Any advice much appreciated!
-
I think you need to specify the file growth in MB instead of percentage as the file is growing enormously.......if you specify the max file size to 300 mb it wudnt grow beyond that size and you cant perform any operations in the db and there is a chance that the db might become suspect as the log file cannot grow anymore..........whereas if the growth is unrestricted then it will grow till it fills the disk space.
[font="Verdana"]- Deepak[/font]
-
If you set a maximum file size, eventually you will run out of space.... unless of course you are doing transaction log backups.
Check books online for "Backing Up and Restoring Databases in SQL Server".
Regards,
Rubes -
Thanks for replies!
As far as I understand this: writes are committed to the transaction log and then to the database. This allows rollbacks of transactions.
Excuse my basic understanding here, but if I stop SQL services and delete the oldest transaction logs what will the effect be?
-
Don't delete the transaction log unless you want to spend some time with an inaccessable database. The transaction log is not a static file that can be deleted at will. Without it, the database won't open.
Truncate the log by backing it up. After the inactive part of the log is truncated, you can shrink the file if you need to. See "truncating the transaction log" and "shrinking the transaction log" in BooksOnLine.
If you don't need to be able to do point-in-time-recovery, consider changing the database recovery model to "simple" so the log truncation will be automatic.
Greg
Greg
-
Hi Greg, the database recovery model is set to simple.
-
As Greg as rightly pointed out "do not delete the log files" then the database will go to suspect state and you cannot access it anymore........better you can truncate the log file and perform shrinking as,
backup log database name with truncate_only
and then proceed wit next step of shrinking
dbcc shrinkfile('log file name','size in mb')
[font="Verdana"]- Deepak[/font]
-
There is not really an oldest transaction log. Transaction logs are on a per database basis. A database may have several transaction logs (in which case all of them are needed), but a transaction log must belong to a single database.
Truncating the transaction log, as Deepak described is the way ahead.
I am on the other hand worried that your transaction log keeps growing so much with simple recovery mode. In simple recovery mode SQL Server recycles the inactive transaction log parts, so there is no need to constantly grow the transaction log. But, the "inactive" part is important. If you have a transaction that you have forgotten about, and it has neither committed or aborted, then any log records after the transaction has started will, even if they belong to a finished transaction, will not be deleted. So if you truncate the log file, and the size of the log file has not reduced, and continues to grow, have a look if you have a transaction running.
Regards,
Andras
Andras Belokosztolszki, MCPD, PhD
GoldenGate Software
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply