attaching and detaching db

[Guest_imported]

I am trying to move the transaction log from one drive to another because it is full. I have tried the following script:

sp_detach_ db 'database'
go
sp_attach_db 'database', 'c:\msql7\data\datafile.mdf',
'f:\mssql7\data\logfile.ldf'

but, I get an error message stating that the script can't execute because the database is currently in use. Does anyone know a better way of doing this? Thanx!

 

[TomSark]

First of all, how are you doing your backups? Do you need the transaction log or can you truncate it. You can't move a log. Databases are required to have a transaction log, whether you need one or not.

You could truncate the log, you could set the 'truncate log on checkpoint option', you could shrink the log, you could back up the log thereby shrinking it, etc. But, you cannot move the log.

If your recovery needs don't require that you use a log, in that your only need to restore from a database backup, say, made every night at off hours, then maybe you don't need to have a log. But, if you need to have the ability to restore your database up to a certain point in time, then you will need to regularly backup the log for potential recovery purposes.

I would strongly suggest you read books-online and learn about database backup and recovery issues.

Thanks,

Tom

 

[Guest_imported]

Tom, I appreciate your response, but your are incorrect about moving db files. You can move them. Please see:

http://support.microsoft.com/support/kb/articles/Q224/0/71.ASP

for more information on this. I just need to to turn off sql agent. Thanx!

 

[Guest_imported]

From books online:

This example detaches the pubs database with skipchecks set to true.

EXEC sp_detach_db 'pubs', 'true'

This example attaches two files from pubs to the current server.

EXEC sp_attach_db @dbname = N'pubs',
@filename1 = N'c:\mssql7\data\pubs.mdf',
@filename2 = N'c:\mssql7\data\pubs_log.ldf'


So, just change 'pubs' to whatever you call your database and set the location of where your database files are.

This works always. But when it says "the database is in use" you might be accessing the database somehow. Maybe you are browsing in it with the Enterprise Manager or something else.

 

[calahans]

What I have done before, is created an empty shell of a database an attach the files. I followed books on line and it worked.

Cal

 

[TomSark]

Sorry, I keep misunderstanding people's questions.. lack of sleep I guess..

Anyways, you cannot detach a database if someone else is connected to it. You could always put the database in single user mode before attempting this as well..

Tom