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.
This blog is created to add all the information and experiences that I have learnt while working on MS sql server as a DBA. The blog is also updated frequently with the real world problem that I encounter hands on at work and the resolutions to resolve them. I hope this blog will be of some use to you and you will revisit. Thank you for stopping by and you are welcome to leave comments.
Showing posts with label transaction log. Show all posts
Showing posts with label transaction log. Show all posts
Wednesday, August 30, 2017
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--
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--
Subscribe to:
Posts (Atom)