• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 376
  • Last Modified:


Hi experts,

I understand that 'sp_attach_single_file_db' can automatically create a new log file, if it can't find one. But, what I have observed is it always creating the log file in the same physical location as of the data file.

Is there any way that we can specify the location (path) fo this log file to be created. I have tried the 'Default log' setting available under Database settings in Server properties. But that does not help.

1 Solution
Scott PletcherSenior DBACommented:
I don't think you can create a new log and change its disk location in the same step, but you can do it separately.  After the "EXEC sp_attach_single_file_db" creates the new log, you could do the following to move it:

1) EXEC sp_detach_db 'dbname'
2) move the log file (.LDF) to its new location
sp_attach_db 'dbname', 'c:\...\....MDF', 'd:\...\....LDF' --if needed, add other files
ykchakriAuthor Commented:
Thank you Scott,

Yes, I thought of that as a workaround. But, I am concerned that if that increases the downtime of my database. What I am doing is :
Deataching the database
moving the datafile
attaching the database with sp_attach_single_file_db

But, If I detach it again and re-attach it, it is increasing the downtime. Especially the move of the log file, Because I have to move it from one drive to another.
What will be the intial size of the log file after attaching it with sp_attach_single_file_db. My test created a log file 500KB, which is faster to move. But, is there any possibilty that it creates a bigger log file that that?
Scott PletcherSenior DBACommented:
No, the new log should almost always be very small (the only ways I see it being significantly larger is [a] your model db has an unusally large log size or [b] there is an integrity problem with the original problem).

Therefore, the move of a brand new log to a new drive should be very fast since it will be a very small file.  I don't think you'd be increasing downtime more than a minute, if that much.
David ToddSenior DBACommented:

The log file that will be created on using sp_attach etc will be the either the default one when the database was created or the one that Model is currently set to.

And as Scott says, it is likely to be quite small.

In fact this is also a technique for truncating huge log files.

delete log file

IF you want limited down time. You could do the following

reapply transaction logs.

if you have dbsource and dbtarget and you are concerned about downtime of both then
Restore to a 3rd database.
Dettach temp db
Dettach targetdb
rename targetDb files (a few seconds)
rename tempdb files (a few seconds)
reattch TargetDb

Downtime is seconds.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now