Solved

sp_attach_single_file_db

Posted on 2002-03-06
5
314 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: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 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

770 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