December 22, 2008 at 10:25 pm
Hi.
I have taken a database backup from production and restored it in Dev server.The database size is of 160 GB.So i planned to shrink database file.i used the following statement
DBCC ShrinkFile('ChangeDM',1)
While executing the statement,I am getting the error "Cannot shrink log file 4 (ChangeDM_Log1) because all logical log files are in use".
I have used the Open tran Statement to view the open transaction in Database.
I get the following Results
Replicated Transaction Information:
Oldest distributed LSN : (2120809:18651:1)
Oldest non-distributed LSN : (2120809:18920:2)
Replication is configured in Production only and not in my Development server.
Please Help me to resolve the issue
December 22, 2008 at 10:41 pm
[font="Verdana"]Shirnking databases is not normally advised, if you have the resources. But since, this is a development database...you might want to try this code. Don't use it on production 🙂
USE MASTER
GO
--SET DATABASE IN SINGLE USER MODE AND ROLLBACK ANY UNCOMITTED TRANSACTIONS IMMEDIATELY.
ALTER DATABASE CHANGEDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE CHANGEDB
GO
--REMOVE INACTIVE LOG PORTION AND TRUNCATE WITHOUT BACKUP
BACKUP LOG CHANGEDB WITH TRUNCATE_ONLY
GO
--SHRINK LOG TO 100MB
DBCC SHRINKFILE ('CHANGEDB_LOG1',100)
GO
--SHRINK DATAFILE TO WHATEVER SPACE IS FREE
DBCC SHRINKFILE('CHANGEDB_DATA', NOTRUNCATE)
GO
DBCC SHRINKFILE('CHANGEDB_DATA', TRUNCATEONLY)
GO
ALTER DATABASE CHANGEDB SET MULTI_USER WITH NO_WAIT
GO
[/font]
December 23, 2008 at 1:07 am
Bilichi (12/22/2008)
Replication is configured in Production only and not in my Development server.
Since that's a backup of a database that's replicated, there's replication set up in it as well. Create a publication, drop that publication and then disable publishing on that database.
What's the recovery model? Since it's a dev database you can set it to simple, then you don't need to worry about managing the log at all.
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 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply