?
Solved

sp_attach_single_file_db

Posted on 2002-03-06
5
Medium Priority
?
353 Views
Last Modified: 2008-01-09
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
Comment
Question by:ykchakri
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 6845208
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
 
LVL 2

Author Comment

by:ykchakri
ID: 6845266
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
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 200 total points
ID: 6845584
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
 
LVL 35

Expert Comment

by:David Todd
ID: 6846288
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
 
LVL 7

Expert Comment

by:simonsabin
ID: 6846778
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 real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question