Transaction Log Error

Last Post 29 May 2008 10:44 PM by SQLUSA. 7 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
kmcnet
New Member
New Member

--
28 May 2008 09:43 AM
Hello everyone and thanks for your help in advance. I am encountering an error with a SQL Server 2000 database. When trying to write through a web application, I receive the error Transaction Log is full. In looking at the database,the transaction log (LDF) file is nearly 4 times that of the MDF file. Because of this, I am running out of disk space. I have read some articles regarding this topic, but can't seem to find one definitive source as to how to rectifify this problem (for example, do I need to TRUNCATE the log or does backing up the databse fix this issue?). I currently have the database allowing unrestricted growth to the transaction log, but don't know the ramifications of selecting a smaller size. Or do I simply need to add more disk space? Any help on this topic would be greatly appreciated. Thanks.
kmcnet
New Member
New Member

--
28 May 2008 12:22 PM
Thanks for the response. I guess I am confused. Within the Management Snap-in, I highlight the database and select "back up". I am assuming this backs up everything including the transaction log. However, this does not seem to reduce the size of the transaction log after the back up. Am I missing something in the process? Or do I need to use Transact SQL in order to back up only the transaction log? Then keep a copy of the log separately and truncate it?
nosepicker
New Member
New Member

--
28 May 2008 01:57 PM
Yes, performing a backup like you described (a "complete" or "full" backup) will backup everything, including the transaction log. But that will not clear out the transactions from the log. Performing a transaction log backup will backup the transactions and clear them out from the log. You don't need T-SQL to do that - you should have that option available to you in Enterprise Manager if the database is in full or bulk-logged recovery mode. If it's in simple recovery mode, you won't have the option to perform a transaction log backup.

And FYI, doing a transaction log backup won't shrink the size of the physical log file. You'll need to perform a shrink file operation to do that (which can be done either via Enterprise Manager or a T-SQL command).
kmcnet
New Member
New Member

--
29 May 2008 10:39 AM
Thanks for the response, however, I must be doing something wrong. Within enterprise Manager, I highlighted "All Tasks" and selected "Backup Database". Once in that section, I selected "Transaction Log". Under Options, I made sure "Remove inactive entries from transaction log" was checked. I then selected "OK". The back ran for approximately 18 hours, far longer than a trypical full backup operation. Once done, I then highlighted the database and selected "Shrink Database", which seems to have been performed successfully. However, neither the LDF or MDF changed in size. I am working under the assumption that this would actually shrink the physical size of the files and free up some space. Any idea what the issue is?
kmcnet
New Member
New Member

--
29 May 2008 05:42 PM
Already tried that. didn't work. Am I correct in assuming that once I do the shrink operation, the file size should appear smaller?
kmcnet
New Member
New Member

--
29 May 2008 05:49 PM
I selected "Shrink Database" and took the default which is "Compress pages and then truncate free space from the file". Do I need to actually specify the file size and what is a good size to use?
SQLUSA
New Member
New Member

--
29 May 2008 10:44 PM

CONTENT REMOVED
kmcnet
New Member
New Member

--
30 May 2008 03:39 PM
Looks like I had an application locking the database. Once I stopped it, the resizing worked well. Thanks for the help.


Acceptable Use Policy
---