May 17, 2005 at 3:26 pm
hi
I have a database back file, when i restore the file and check the database size i see that sql server has allocated 40 MB to datafile and 450 MB to log file.. Log file size seems to be huge relative to datafile.. is this due to uncomitted transactions during the database backup?
Thanks
THNQdigital
May 17, 2005 at 4:47 pm
This can be due to many things. Infrequent (non-existant?) log backups, large transactions (DTS?), etc. Essentially the cure is usually to backup the log file, which will then allow the log to shrink. Of course actually getting it to shrink can be complicated because it might have to fill the current segment before wrapping back to the beginning of the file and allowing the "top" part to be shrunk off.
See BOL "DBCC SHRINKFILE", "Truncating the Transaction Log", etc. for more technical details.
May 17, 2005 at 11:14 pm
The transaction log grow mostly because there is no proper Tlog backup or the recovery model is set to full and there is a reindexing or any bulk- insert activities happening
Amit Lohia
May 18, 2005 at 10:59 am
The log will not shrink on its own with a transaction log backup. It will truncate. The difference is that SQL has ALLOCATED 450MB to your transaction log because at some point it needed that much space. Then, some action was taken to truncate the log, probably a transaction log backup. Now, you have a 450MB transaction log file with maybe 45MB of transactions (probably less). In order to SHRINK the file, that is, to reduce the allocation, you must do something to tell SQL that you wish to do this. In Enterprise Manager, you can select the database, right click, All Tasks, Shrink Database. Or, and this is probably the better way... DBCC SHRINKFILE from Query Analyzer allows you to shrink a specific file to a specific size (if possible). Check Books Online for "shrink database" for more details.
Steve
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply