| Author |
Topic |
|
sql-lover
Yak Posting Veteran
99 Posts |
 Posted - 2012-07-19 : 09:35:18
|
| Something weird happened to me last night, while doing maintenance to the servers at my new job.Our databases were configured with Data and Tlog on same drive. Yeah, I know, really bad for performance.So I informed that and during the scheduled downtime, I changed that. I altered the Tlog file location via ALTER DATABASE command. Then I took the database offline, again with ALTER DATABASE command. And finally moving the Tlog file manually to different drive and then putting the database online again.Today, I noticed the Tlog files are back to initial location, mixed with Data files again. I should mention that after above tasks, I turn the SQL service off and later on, so my Windows Admin could do some patching. That's the only additional MS-SQL activity I did after mine.Why the Tlog files are back to original location? Am I missing something?Thanks in advance. |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-07-19 : 09:51:02
|
| backup databasedetach databasemove log filesattach database with move==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
sql-lover
Yak Posting Veteran
99 Posts |
Posted - 2012-07-19 : 10:09:49
|
quote:
Originally posted by nigelrivett
backup databasedetach databasemove log filesattach database with move==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy.
Forgot to mention, it is on the same server and same MS-SQL instance. So backup and attach make no sense here.But thanks for reply. My question is still open tough... |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-07-19 : 10:35:07
|
quote:
Forgot to mention, it is on the same server and same MS-SQL instance. So backup and attach make no sense here.
Sure they do. Make a backup to protect against any accidental loss while detaching. Detach the database so you can freely move the files, and then attach them using the new locations.The other option is to RESTORE that database and specify WITH REPLACE, MOVE options to move the log files to the new location. |
|
|
|
sql-lover
Yak Posting Veteran
99 Posts |
Posted - 2012-07-19 : 10:43:19
|
quote:
Originally posted by robvolk
quote:
Forgot to mention, it is on the same server and same MS-SQL instance. So backup and attach make no sense here.
Sure they do. Make a backup to protect against any accidental loss while detaching. Detach the database so you can freely move the files, and then attach them using the new locations.The other option is to RESTORE that database and specify WITH REPLACE, MOVE options to move the log files to the new location.
I do appreciate you take the time and replied, but from my point of view and experience, it makes no sense.The backups are in place and verified, so you are right about that, but attaching and de-attaching several big databases just to move the Tlog files? is a very aggressive command. On same server and same MS-SQL instance, makes no sense. There are even several features and/or settings that we can loss if we do attach instead of ALTER DATABASE.ALTER DATABASE leaves the database on the MS-SQL instance while allowing the file move.I've seen very bad corruption or issues when re-attaching databases. That is being minimized when keeping the database there, offline.Restoring via backup using MOVE is time consuming and over-killing when we are only trying to relocate the Tlog file on same SQL instance and server. For moving Tlog files, same server and instance, it is my opinion that ALTER DATABASE is the way to go; granted, some people may disagree. But my post is not about challenging that method but knowing what went wrong or what step I missed using ALTER DATABASE, if I missed one. I am looking for any possible MS bug or missed step on the task.Going back to my initial question or topic, I would like to know possible reasons why the move and path got reset after restarting the SQL service. I validated it myself. The database(s) were using new path before turning the SQL service off. |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-07-19 : 11:44:56
|
quote:
Originally posted by sql-lover
quote:
Originally posted by nigelrivett
backup databasedetach databasemove log filesattach database with moveWas the database using the new path or had yoou just updated a value that was displayed back to you until the next restart?Did yoou set the database offline before running the command?And on-line afterwards?Hope you are taking backups.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy.
Forgot to mention, it is on the same server and same MS-SQL instance. So backup and attach make no sense here.But thanks for reply. My question is still open tough...
Yes they do. Both of them.You shuold do a backup just in case things go wrong.You have to take down the database to do the log move anyway.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-07-19 : 11:57:58
|
| Looking at your original post I see you took it offline after the alter.I suspect that means the alter was never committed and new log files were created in the old location when it was brought online again.I also suspect that if you had more than one log or database file you would have ended up with a corrupt database.What you are doing is actually more complicated than a detach and attach (and more fragile as you have seen) but each to his own.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2012-07-19 : 12:18:41
|
"from my point of view and experience, it makes no sense"With a little more experience, your point of view is sure to change. |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2012-07-19 : 14:03:55
|
quote:
Originally posted by nigelrivett
Looking at your original post I see you took it offline after the alter.I suspect that means the alter was never committed and new log files were created in the old location when it was brought online again.I also suspect that if you had more than one log or database file you would have ended up with a corrupt database.What you are doing is actually more complicated than a detach and attach (and more fragile as you have seen) but each to his own.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy.
If you take a database offline - move the log file, then bring the database back online it will not recreate the log file if it wasn't put in the right location. It will not bring that database online and probably will put it into suspect status.I believe the procedure that the OP followed is the Microsoft recommended procedure for moving database files. It works just as advertised and going the extra length of detaching/attaching can actually cause issues (e.g. reset permissions on the files).What I would be wondering is if the OP 'copied' the files to the new location and just forgot to delete them from the old location. A way to validate is to look in sys.master_files for the actual file locations and verify they are in the new locations. If not, then I would have to guess that the alter command to move the files either wasn't done - or wasn't successful. |
|
|
|
sql-lover
Yak Posting Veteran
99 Posts |
Posted - 2012-07-25 : 00:21:11
|
quote:
Originally posted by nigelrivett
Looking at your original post I see you took it offline after the alter.I suspect that means the alter was never committed and new log files were created in the old location when it was brought online again.I also suspect that if you had more than one log or database file you would have ended up with a corrupt database.What you are doing is actually more complicated than a detach and attach (and more fragile as you have seen) but each to his own.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy.
You may be right. I think I did not follow MS procedure and did the ALTER prior putting the database offline. Interesting enough tough, I tested again on my own PC without any issues at all. I even rebooted after doing it. The Tlog remains on new place .Also, I cut and paste. I checked sys.master after the move, everything went fine.I may never know for sure, but I think it was because I did the ALTER before the offline, which is not the MS process I think. What puzzles me is that on my own PC, it works even doing the ALTER 1st.I respectfully disagree on your comment that is riskier than attach and deattach, especially on same SQL instance and working on big databases. More chances of ending with a database that refuses to attach than one that can't be brought online after a simple ALTER, assuming the files were moved to right path. But ... not the topic here anyway ... |
|
|
|
sql-lover
Yak Posting Veteran
99 Posts |
Posted - 2012-08-02 : 14:08:36
|
| Just a follow up, for those who read this thread.I actually solved this mystery. The reason why the Tlog whent back to the original path is or was because permissions issues, on the database folder.Last night, I was helping during the planned downtime. I tried to do the same with the only difference that we did not restart the whole server or MS-SQL instance (we had before, because some Windows patches) So I used ALTER command and I got a "permission denied" error.It looks like the Data and Tlog folder or the drive, I don't know yet, are not properly set for the MS-SQL instance or service.Had to use attach/de-attach and because obvious reasons, that worked.Also, for those interested and if you are a member of SQL Server Central, you can check this article [url]http://www.sqlservercentral.com/articles/Administration/65896/[/url]which explains the pros and cons of using attach, when moving files on same server. |
|
|
|
|
Tlog files back to original location, after move