December 21, 2003 at 10:03 pm
dear friends,
I have a database with 2 logfiles.(both online). In order to issue alter database remove file filename , the file must be empty. how to ensure that? how to move the active transaction in one logfile to another so that first one can be deleted. kindly help
thanks in advance
Anil Kumar
December 22, 2003 at 1:23 am
As far as I remember I used to do this by shrinking the second log file and then remove.
Prakash Heda
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
December 22, 2003 at 2:14 am
thanks,
but can I shrink second file so that it will not occupy any space? what will happen to the log data in the shrinked file? suppose logfile size is 20 mb and used space is 12mb, can i shrink it below 12 mb?
December 23, 2003 at 2:58 pm
Anil and Prakash,
Is this a online production db ? If so what recovery model is set (full, Simple, etc) You wish to shrink THEN remove the Second .LDF correct ? There are several ways to do this but it could have disasterous effects (pardon the spelling) on replication and transactional backups if you needed to restore in the event of a failure. I would during a "defined" maintenance period set the db to DBO use only. Backup the DB (all all it entails) then Run a Backup Database <dbname> With NO_LOG and then attempt the shrink. Then remove the 2nd LDF File.
Jim Babington
Jbabington
Jbabington@hotmail.com
December 23, 2003 at 6:15 pm
DBCC SHRINKFILE ('x2_Log', EMPTYFILE)
ALTER DATABASE x REMOVE FILE x2_Log
Keep in mind that you cannot remove primary log file.
November 25, 2005 at 10:22 pm
dbcc shrinkfile emtyfile
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply