April 3, 2009 at 1:24 am
Hi
I have an application with SQL Server 2005 as the database.
Recently i'm getting an error " transaction log of the [mydatabase] is full ".
I searched for this error and got many results and views regarding the same.I'm confident that i have closed every connections properly in my application.So i doubts what can be the cause for the same.
Can anyone suggest me a solution for the same.
Regards
Nicsam
April 3, 2009 at 2:01 am
Hello,
Is this issue on a Production or Test/Dev DB?
If it is Production then I assume you are regularly backing up the Transaction Log. This is what allows “old” Transactions to be removed from the Log and therefore free-up space.
If it is Test/Dev then you could consider setting the Recovery Mode to Simple, and that would fix your problem.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
April 3, 2009 at 2:19 am
Although you have your database in Recovery Model Simple, you can get this kind of error.
You must check your proccess:
- Include transanctions where you can.
- Make transactions smaller
And, in any cases, we have to use the CHECKPOINT command, to force the SQL to write to disk caches and free the log.
For example, in one case we have to delete a table, the table had 1.7 Million records.
If we made,
DELETE FROM ourTable, this needed 1,7GB of log.
But if we made
DECLARE @Filas INTEGER
DECLARE @Error INTEGER
SET ROWCOUNT to 10000
SET @Filas = 10000
WHILE @FILAS = 10000
BEGIN
-- Initialize transaction
BEGIN TRAN
-- Delete the rows you want
DELETE FROM ourTable
-- Get the return code, and the number of rows deleted
SELECT @Error = @@ERRORm @Filas = @@ROWCOUNT
-- Check if there is an error
IF @Error <> 0
BEGIN
-- In Error, Rollback transaction
ROLLBACK TRAN
RETURN
END
-- If no error, Commit transaction and continue with next block
COMMIT TRAN
END
In this case, we only needed 100 Mb to complete the delete.
April 3, 2009 at 5:14 am
webqatesting (4/3/2009)
HiI have an application with SQL Server 2005 as the database.
Recently i'm getting an error " transaction log of the [mydatabase] is full ".
I searched for this error and got many results and views regarding the same.I'm confident that i have closed every connections properly in my application.So i doubts what can be the cause for the same.
Can anyone suggest me a solution for the same.
Regards
Nicsam
what is the recovery model for the database concerned
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 3, 2009 at 6:41 am
webqatesting (4/3/2009)
HiI have an application with SQL Server 2005 as the database.
Recently i'm getting an error " transaction log of the [mydatabase] is full ".
First have you got enough space on the disk where this tlog is residing?
What is the size of the log file at present?
How are you incrementing your log file? Can you post them please?
What operations did you perform before you go this error? Have you had any bulk load processes against this database?
I searched for this error and got many results and views regarding the same.I'm confident that i have closed every connections properly in my application.So i doubts what can be the cause for the same.
Can anyone suggest me a solution for the same.
Yes, there are many reasons why a tlog might be full. So, you can't just google it unless you know what the problem is?
As Grant mentioned, what recovery model it is in? If full, then are you taking frequent log backups?
Please post the details as requested.
April 5, 2009 at 10:55 pm
Hi
Unfortunately we are not sure about the recovery model and all.
Can i have a question is this error can occur due to any coding issue in the application end rather than the Database end.
April 5, 2009 at 11:27 pm
no man this is not cause of application problem....this only what your transactional log file full and now there is not enough space...if you want to check then do some extra space in pc and check it will starts to work...but you need to set proper backup plan or if this is for only testing purpose then you can set it to simple recovery model...
>> goto database properties in option you can choose recovery model....
>> take backup of transactional log file
>> truncate transactional log file
these are three ways ...choose whatever you think ok
Raj Acharya
April 6, 2009 at 3:27 am
Krishna Potlakayala (4/3/2009)
As Grant mentioned, what recovery model it is in?
Grant who??
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 6, 2009 at 3:36 am
webqatesting (4/5/2009)
HiUnfortunately we are not sure about the recovery model and all.
Ok, now i'm a little worried :unsure:
right click the database and check the options on the database properties dialog or run the following query through management studio and check the results for your database name and recovery model
select [name] as 'Database Name', recovery_model_desc as 'Recovery Model'
from sys.databases where database_id > 4
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 6, 2009 at 4:38 am
Perry Whittle (4/6/2009)
Krishna Potlakayala (4/3/2009)
As Grant mentioned, what recovery model it is in?Grant who??
Oops..I get confused with the pics...sorry Perry:-D, next time i should focus on the names
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply