January 5, 2009 at 4:18 am
Hi,
I try to shrink log file to 10 GB by using DBCC Shrink scripts like below;
EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1 BACKUP LOG SC2 with truncate_only DBCC SHRINKFILE (SC2_Log, 10000)
But i am getting following error:
Msg 18757, Level 16, State 1, Procedure sp_repldone, Line 1
Unable to execute procedure. The database is not published. Execute the procedure
in a database that is published for replication.
Cannot shrink log file 2 (SC2_Log) because all logical log files are in use.
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Thanks
January 5, 2009 at 5:30 am
Hi there,
You have two choices here:
1. Backup the log file and it will then allow you to shrink it.
2. Backup the database, change the recovery model to 'simple', shrink the log, revert recovery model to 'full' and backup databases again. This is all in aid of protection data, if your not bethered, scip the backup sections.
Good luck 🙂
Adam Zacks-------------------------------------------Be Nice, Or Leave
January 5, 2009 at 5:36 am
thinknight (1/5/2009)
Unable to execute procedure. The database is not published. Execute the procedurein a database that is published for replication.
Cannot shrink log file 2 (SC2_Log) because all logical log files are in use.
Is this database part of a replication set? If so DONT EXECUTE MY SUGGESTION UNTIL I CAN VERIFY ITS OK REPLICATION.
Log backups and then shrinks are always ok.
Adam Zacks-------------------------------------------Be Nice, Or Leave
January 5, 2009 at 5:41 am
thinknight (1/5/2009)I try to shrink log file to 10 GB
How big was your Log at creation time?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 5, 2009 at 5:51 am
You can also check if any opent transactions are there or not?
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
January 5, 2009 at 6:43 am
I presume that this is a production dump of the database to a staging or development environment, if not be very wary of truncating the log. Perform a full backup and then log backups to file so that you have something to fall back on should you need it.
You'll need to publish the database (local or through your distributor) in order to execute the sp_repldone statement (I would remove all of the publication articles also), once you've done that you should find the log easily shrinkable, and you can unpublish the database.
January 6, 2009 at 9:31 am
Hi. To shrink database logfiles I use this:
DECLARE @vdatafile VARCHAR(50)
DECLARE @vlogfile VARCHAR(50)
DECLARE @vdatabase VARCHAR(50)
SELECT @vdatafile = RTRIM(NAME) FROM SYSFILES WHERE GROUPID = 1
SELECT @vlogfile = RTRIM(NAME) FROM SYSFILES WHERE GROUPID = 0
SELECT @vdatabase = RTRIM(DB_NAME(DBID)) FROM MASTER..SYSPROCESSES WHERE SPID=@@SPID
BACKUP LOG @vdatabase WITH TRUNCATE_ONLY
DBCC SHRINKFILE(@vdatafile,10)
DBCC SHRINKFILE(@vlogfile,10)
DBCC SHRINKDATABASE(@vdatabase , 10, TRUNCATEONLY)
GO
Run it in management studio or query analyzer with the database you want to shrink selected.
Regards.
January 27, 2009 at 3:45 am
Many thanks for this. Works a treat.
January 27, 2009 at 4:01 am
gilk (1/6/2009)
BACKUP LOG @vdatabase WITH TRUNCATE_ONLYDBCC SHRINKFILE(@vdatafile,10)
Anyone who decides to use this should note two things.
1) The backup log with truncate will break the log chain. Hence an immediate full backup is required as point-in-time restores will not be possible until a full backup is done. On a production database with a 0 data loss requirement, that can cause very big problems if the DB should fail any time after this.
2) The shrink of the data file and subsequent shrink of the entire database has fragmented every single index in the database, possibly to levels of 90% or so. An index rebuild on every index is recommended as soon as possible as performance will suffer until that is done.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply