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 |
|
steelkilt
Constraint Violating Yak Guru
255 Posts |
 Posted - 2002-11-18 : 09:46:34
|
| I've decided to use sp_detach, sp_attach to remove my unwieldy transaction log and replace it with a new, smaller one. There are many fine links here at SQLTEAM explaining how to do this. Being irrationally fearful about the outcome of this procedure, however, I wanted to run my simple plan by you all to make sure I'm not missing anything. I read the databasejournal.com article (http://www.databasejournal.com/features/mssql/article.php/1460151) that explains how to do this, but I just need a bit of clarification for the ATTACH operation.Steps to detach a db, then reattach it on same server. Goal is to begin fresh with new transaction log:1. Detach db:use masterEXEC sp_detach_db 'production_data' 2. reattach db:use masterEXEC sp_attach_single_file_db @dbname = 'production_data', @physname = 'E:\mssql70db\production_data\production_data.mdf'Questions:**Re: ATTACH: I assume I must use the "single_file" SPROC since all I want to reattach is the MDF, correct? The databasejournal article is not clear on this.**re: size of new transaction log: the person who set up this DB originally set the transaction log space allocation at 250MB, thus causing the current problem. Do I need to pass the ATTACH sproc anything in the way of a size threshold for the new LDF? I don't want the newly-attached DB to automatically adopt the old 250mb setting, thus defeating the purpose of this exercise.Thanks. Edited by - steelkilt on 11/18/2002 09:49:39 |
|
|
rfnoteboom
Starting Member
4 Posts |
Posted - 2002-11-18 : 09:58:44
|
| From my (limited) experience with re-attaching databases, I have concluded that it's necessary to actually delete the .ldf, otherwise the re-attached database will automatically use the old log file.OTOH, a simple ALTER DATABASE MODIFY FILE should be enough to remove the file size limitations (see BOL). |
 |
|
|
rihardh
Constraint Violating Yak Guru
307 Posts |
Posted - 2002-11-18 : 10:08:56
|
| You're right on both terms. You can set up a smaller start size for the log, but don't forget to set the "filegrowth" option.Reattaching a DB in this way is no big deal, BUT, there can always go "something" wrong.Since this is a production DB, I would suggest that you "try before buy"... |
|
|
|
royv
Constraint Violating Yak Guru
455 Posts |
Posted - 2002-11-18 : 10:37:44
|
| If you use sp_detach_db, chances are you do not need sp_attach_single_file_db. You can use sp_attach_db, the only thing is you have to rename the old log file so that the procedure won't pick it up. Before you do any of this, TAKE A FULL BACKUP! I can't emphasize how important this is.***************************************Death must absolutely come to enemies of the code! |
|
|
|
|
|
|
|
|
Detaching production db today; fear setting in