detach / attach to reduce log file size

Hi, i was wondering if someone could help me. When one detaches a database
via sp_detach_db, all the transactions in the ldf log file are commited to
the mdf file right? After this is done, i delete the old log file and then
do a:
EXEC sp_attach_db 'pubs',
'd:\Data\pubs.mdf'
But how does it know where to put the new ldf log file? Does it have a
memory as to where the original log file was placed? What if i want to place
it somewhere else? And is it possible to attach the database and give it a
new name and location for the log files?
Any help appreciated!
cheers johnThis is not the correct procedure to reduce the size of a transaction log
file. You should use DBCC SHRINKFILE for this purpose.
The location of database data and log files are recorded in the database's
sysfiles table. When you attach a database, attach tries to find the
unspecified files using this table. In the special case that a file is
missing and it is a log file and the database has only one log file, a new
log file will be created. The new log will be created in the log file
default location when the original location is unavailable.
To move your log file (after shrinking, if desired), detach the database,
move the log file and attach specifying all files.
Hope this helps.
Dan Guzman
SQL Server MVP
"john r" <johnr@.trailer.com> wrote in message
news:Oj3bvHN7FHA.2012@.TK2MSFTNGP14.phx.gbl...
> Hi, i was wondering if someone could help me. When one detaches a database
> via sp_detach_db, all the transactions in the ldf log file are commited to
> the mdf file right? After this is done, i delete the old log file and then
> do a:
> EXEC sp_attach_db 'pubs',
> 'd:\Data\pubs.mdf'
> But how does it know where to put the new ldf log file? Does it have a
> memory as to where the original log file was placed? What if i want to
> place it somewhere else? And is it possible to attach the database and
> give it a new name and location for the log files?
> Any help appreciated!
> cheers john
>