Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 370
  • Last Modified:

sp_attach_single_file_db

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.

0
ykchakri
Asked:
ykchakri
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
3) EXEC
sp_attach_db 'dbname', 'c:\...\....MDF', 'd:\...\....LDF' --if needed, add other files
0
 
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?
0
 
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.
0
 
David ToddSenior DBACommented:
Hi,

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.

Dettach
delete log file
reattach

Regards
  David
0
 
simonsabinCommented:
IF you want limited down time. You could do the following

Backup
Restore
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.

0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

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