I have a Clustered MS SQL 2005 server that has a database that was migrated several years ago.
Made a lot of record changes in the database today and our log file grew to 3.8 GB. So I was trying to backup the database and shrink the log file:
print 'Backing up Database'
backup database plat to PLATBACKUP WITH NOFORMAT, INIT, REWIND
print 'backup log plat with no_log'
backup log plat to Platlog with NOFORMAT, INIT, REWIND
print 'shrink log file'
dbcc shrinkfile(plat_log,1)
backup log plat with no_log
I got this errors:
1. The log was not truncated because records at the beginning of the log are pending replication. Ensure the Log Reader Agent is running or use sp_repldone to mark transactions as distributed.
2. Cannot shrink log file 2 (plat_Log) because all logical log files are in use.
A look on EE suggested seting recovery mode to simple so I ran this script:
/* Compress plat Database */
ALTER DATABASE [plat]
SET RECOVERY SIMPLE;
GO
/* Database File */
use [plat] DBCC SHRINKFILE (N'plat')
GO
/* Log File */
use [plat] DBCC SHRINKFILE (N'plat_log')
GO
ALTER DATABASE [plat]
SET RECOVERY FULL;
GO
I ended up getting these errors: Cannot shrink log file 2 (plat_Log) because all logical log files are in use.
So after more research I ran this script
When trying work arounds
use plat
EXEC sp_dboption 'plat', 'published', 'true'
GO
sp_dboption 'plat'
GO
EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time= 0, @reset = 1
GO
EXEC sp_dboption 'plat', 'published', 'false'
GO
I got these errors:
The Distributor has not been installed correctly. Could not enable database for publishing.
Unable to execute procedure. The database is not published. Execute the procedure in a database that is published for replication
The database is not setup to replicate logs. I am not sur what I need to do get past this.
Any help will be greatly appreciated.
Start Free Trial
