Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
harshal_in
Aged Yak Warrior
633 Posts |
 Posted - 2004-04-05 : 01:44:47
|
| Hi all,I have to give support to this new client of ours and the system out there was a real mess.The log files for all four databases were ranging from 5 to 9 GB's, no maintanance no nothing was done since the setup of the system. I have truncated the logs after taking proper backups since it all started after they compalined a DTS was not working, which was due to no disk space available ;).Now I have to shift the log files to a seperate disk.I know it can be done by detaching the database and attaching it back, I was wondering if we can do it without bringing the system offline?Anycomments welcome.Thankyou.regards,harshal.He is a fool for five minutes who asks , but who does not ask remains a fool for life! |
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2004-04-05 : 02:26:52
|
| The only way I know is to re-attach it...I'd love to be told otherwise though...DavidM"If you are not my family or friend, then I will discriminate against you" |
 |
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2004-04-05 : 03:10:19
|
You must have looked so smart there  Just a few ramblings...if the data and the log files are on the same partition/disk as the operating system, I think you'll get better performance by moving the data files out onto to the new disk, and keep the transaction logs where they are. And I don't think this can be done without bringing the database offline, either. Also with regular transaction log backups, the logs won't grow as much and you will see immediate benefits from running DBCC SHRINKFILE periodically, especially before and after a major DTS task. So you might not need a lot of space after all, which means you can afford to keep a little more space for the data files. Of course, you will have to cater to log expansion during BULK INSERT or DTS tasks.OS |
|
|
|
harshal_in
Aged Yak Warrior
633 Posts |
Posted - 2004-04-05 : 04:28:08
|
quote:
Originally posted by mohdowais
You must have looked so smart there
 quote:
Also with regular transaction log backups, the logs won't grow as much and you will see immediate benefits from running DBCC SHRINKFILE periodically, especially before and after a major DTS task. So you might not need a lot of space after all, which means you can afford to keep a little more space for the data files. Of course, you will have to cater to log expansion during BULK INSERT or DTS tasks.
the database files and operating system files are on different partition.I have already started the jobs to take transaction log backups and shrinking the files periodically.He is a fool for five minutes who asks , but who does not ask remains a fool for life!<N>http://www.sqldude.4t.com |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-04-05 : 08:07:41
|
| Well, I agree with everyone on having to bring the system down. I'm sure you already have this, but this is one link I keep in my favorites for moving databases around.http://support.microsoft.com/default.aspx?scid=kb;en-us;224071&Product=sqlWe've had to move systems around a LOT since I've been here though, so this has been an invaluable reference. It's not something I just want to do from memory and hope I'm right, especially with the system databases, which I like to have in the same place on every server.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
harshal_in
Aged Yak Warrior
633 Posts |
Posted - 2004-04-06 : 00:50:13
|
| Thankyou everyone.He is a fool for five minutes who asks , but who does not ask remains a fool for life!<N>http://www.sqldude.4t.com |
|
|
|
|
|
|
|
|
moving transaction log file to another disk