Showing posts with label transaction log. Show all posts
Showing posts with label transaction log. Show all posts

Wednesday, August 30, 2017

Error: Tranaction log full due to replication

I had this error in one of the databases and I was trying different possible steps to fix it.

I tried to take a transactional log back up - no luck
I tried to change the recovery model to simple - no luck
The database does not have replication but it does have CDC enabled. So I tried to disable the CDC I got another error related to metadata
I tried to change the db owner to sa - no luck still error
Then the following helped me to get rid of the error that was saying replication

EXEC sp_repldone @xactid = NULL, @xact_sgno = NULL, @numtrans = 0, @time = 0, @reset = 1;

I tried to change the db owner to sa after running the above - success
I tried to disable the CDC - success
I expanded the log file - success

This is how I was able to resolve the issue.

Friday, September 16, 2011

Shrink the transaction log file

The steps needed to shrink the transaction log file
Scenario: The database is in FULL RECOVERY mode and the transaction log file was growing to more than 2GB. We were taking the transaction logbackups in every 1 hour inteval. However the transaction log file still grew because at one point someone just set the recovery mode to FULL and there was no transaction log backups set up. The following steps were taken to reduce the siz of the log file.

--check the log file name
select * from sys.database_files

--Truncate the log by changing the database recovery model to SIMPLE. ( checkpoint occurs-frees up VLFs)  

ALTER DATABASE ARCHIVEhcfsdatabase
 SET RECOVERY SIMPLE;
 GO

-- Shrink the truncated log file the smalles size possible
DBCC SHRINKFILE (ARCHIVEhcfsdatabase_Log,0, TRUNCATEONLY);
GO

--reset the log file size to 64MB

ALTER DATABASE archivehcfsdatabase
Modify FIle(Name = 'ARCHIVEhcfsdatabase_Log',
,Size = 1024
)
 -- Reset the database recovery model.
ALTER  DATABASE ARCHIVEhcfsdatabase
SET RECOVERY FULL;

GO

--Do a full Back up - Until a full back up is done the database is not set to full recovery mode
--