Solved

sp_attach_single_file_db

Posted on 2002-03-06
5
300 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
5 Comments
 
LVL 69

Expert Comment

by:ScottPletcher
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:
ScottPletcher earned 50 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

914 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now