May 28, 2008 at 5:57 pm
Hi,
Is it possible to move LOG without bringing down the database ?
I've tried to add second log file, then truncate log (BACKUP LOG WITH TRUNCATE_ONLY), then emptying the primary log file (DBCC SHRINKFILE (2,EMPTYFILE)), and then removing primary log file but... DBCC SHRINKFILE didn't shrink log to 0, it left 128 pages. And when trying to remove primary file I've go message 'You cannot remove primary log file'.
Do you know any method to accomplish this task ? On-line, no sp_detach/sp_attach please
Regards,
Slawek
May 28, 2008 at 8:02 pm
Can't do it. Need to take the db offline to move the log file.
May 29, 2008 at 3:17 am
That's what I was afraid of 🙁
Thanks.
Slawek
June 11, 2008 at 4:28 pm
HI, No need to attach/detach. Shrink the primary log file (the first log file) to the size you want and can. Then use:
Alter Database DBNAME MODIFY FILE (Name = Logical_File_Name, FileName = 'new Complete Path').
The path would be changed in catalog views immediately but the actual log file would be physically moved to the new location at the next restart of SQL Server service.
January 15, 2009 at 1:59 pm
Excellent. Thank you for taking the time to post this solution.
January 15, 2009 at 3:11 pm
Don't get it. That STILL needs to get the db OFFLINE!
* Noel
January 16, 2009 at 1:07 am
Suresh Kumar Maganti (6/11/2008)
The path would be changed in catalog views immediately but the actual log file would be physically moved to the new location at the next restart of SQL Server service.
Be careful following that advice. If you do that, the next time SQL starts your database will go suspect because SQL can't find the log file. It's only tempDB where SQL automatically moves the files for you. (Or, more correctly, recreates them in the new place)
The proper series of steps to move a file using Alter Database is:
1) Run the alter database and set the file's new location.
2) Take the database offline
3) Go to the filesystem, find the file and move it to the location that you specified in the alter database
4) Bring the database back online.
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 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply