How could I reloaction primary transaction log file to another disk.

Hey, All,
Recently, we have installed a new raid disk on server, I want to migrate
transaction log to new disk for improving performance, is this possible and
how can I do it?
Thanks
YifeiOne method is to detach the database, move the log file and attach the
database files from the new location. See sp_detach_db and sp_attach_db in
the Books Online for details.
Hope this helps.
Dan Guzman
SQL Server MVP
"Yifei" <yjiang@.sdg.aust.com> wrote in message
news:%23hbx8Pk3DHA.1908@.TK2MSFTNGP10.phx.gbl...

quote:

---

> Hey, All,
> Recently, we have installed a new raid disk on server, I want to migrate
> transaction log to new disk for improving performance, is this possible

---

and

quote:

---

> how can I do it?
> Thanks
> Yifei
>

---

|||Hey, Dan,
I think I need to clarify that I only need change transaction log file
location and keep the database file as is.
I have tried detach and attach, it seems not work at this situation.
Any other ideas would be appreciated.
Thanks
Yifei
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:%23jPQCkk3DHA.2144@.TK2MSFTNGP10.phx.gbl...

quote:

---

> One method is to detach the database, move the log file and attach the
> database files from the new location. See sp_detach_db and sp_attach_db

---

in

quote:

---

> the Books Online for details.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Yifei" <yjiang@.sdg.aust.com> wrote in message
> news:%23hbx8Pk3DHA.1908@.TK2MSFTNGP10.phx.gbl...
> and
>

---

|||Dan is right,,, sp_attach/detach will work..
Do an sp_detachdb dbname,
then copy the log file to the new destination, and remove the orig.
then sp_attachdb dbname, datafilename, logfilename
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Yifei" <yjiang@.sdg.aust.com> wrote in message
news:eKtOf8k3DHA.556@.TK2MSFTNGP11.phx.gbl...

quote:

---

> Hey, Dan,
> I think I need to clarify that I only need change transaction log file
> location and keep the database file as is.
> I have tried detach and attach, it seems not work at this situation.
> Any other ideas would be appreciated.
> Thanks
> Yifei
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:%23jPQCkk3DHA.2144@.TK2MSFTNGP10.phx.gbl...
> in
migrate[QUOTE]
possible[QUOTE]
>

---

|||Like Wayne said, you can leave the data file(s) in the original location.
However, you should specify all database data and files when attaching the
database regardless of whether or not they were moved.
Hope this helps.
Dan Guzman
SQL Server MVP
"Yifei" <yjiang@.sdg.aust.com> wrote in message
news:eKtOf8k3DHA.556@.TK2MSFTNGP11.phx.gbl...

quote:

---

> Hey, Dan,
> I think I need to clarify that I only need change transaction log file
> location and keep the database file as is.
> I have tried detach and attach, it seems not work at this situation.
> Any other ideas would be appreciated.
> Thanks
> Yifei
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:%23jPQCkk3DHA.2144@.TK2MSFTNGP10.phx.gbl...
> in
migrate[QUOTE]
possible[QUOTE]
>

---

|||Thanks Dan and Wayne, It works.
Yifei
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:uI5jQ1o3DHA.360@.TK2MSFTNGP12.phx.gbl...

quote:

---

> Dan is right,,, sp_attach/detach will work..
> Do an sp_detachdb dbname,
> then copy the log file to the new destination, and remove the orig.
> then sp_attachdb dbname, datafilename, logfilename
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "Yifei" <yjiang@.sdg.aust.com> wrote in message
> news:eKtOf8k3DHA.556@.TK2MSFTNGP11.phx.gbl...
sp_attach_db[QUOTE]
> migrate
> possible
>

---

How could I reloaction primary transaction log file to another disk.

Hey, All,
Recently, we have installed a new raid disk on server, I want to migrate
transaction log to new disk for improving performance, is this possible and
how can I do it?
Thanks
YifeiOne method is to detach the database, move the log file and attach the
database files from the new location. See sp_detach_db and sp_attach_db in
the Books Online for details.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Yifei" <yjiang@.sdg.aust.com> wrote in message
news:%23hbx8Pk3DHA.1908@.TK2MSFTNGP10.phx.gbl...
> Hey, All,
> Recently, we have installed a new raid disk on server, I want to migrate
> transaction log to new disk for improving performance, is this possible
and
> how can I do it?
> Thanks
> Yifei
>|||Hey, Dan,
I think I need to clarify that I only need change transaction log file
location and keep the database file as is.
I have tried detach and attach, it seems not work at this situation.
Any other ideas would be appreciated.
Thanks
Yifei
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:%23jPQCkk3DHA.2144@.TK2MSFTNGP10.phx.gbl...
> One method is to detach the database, move the log file and attach the
> database files from the new location. See sp_detach_db and sp_attach_db
in
> the Books Online for details.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Yifei" <yjiang@.sdg.aust.com> wrote in message
> news:%23hbx8Pk3DHA.1908@.TK2MSFTNGP10.phx.gbl...
> > Hey, All,
> >
> > Recently, we have installed a new raid disk on server, I want to migrate
> > transaction log to new disk for improving performance, is this possible
> and
> > how can I do it?
> >
> > Thanks
> >
> > Yifei
> >
> >
>|||Dan is right,,, sp_attach/detach will work..
Do an sp_detachdb dbname,
then copy the log file to the new destination, and remove the orig.
then sp_attachdb dbname, datafilename, logfilename
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Yifei" <yjiang@.sdg.aust.com> wrote in message
news:eKtOf8k3DHA.556@.TK2MSFTNGP11.phx.gbl...
> Hey, Dan,
> I think I need to clarify that I only need change transaction log file
> location and keep the database file as is.
> I have tried detach and attach, it seems not work at this situation.
> Any other ideas would be appreciated.
> Thanks
> Yifei
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:%23jPQCkk3DHA.2144@.TK2MSFTNGP10.phx.gbl...
> > One method is to detach the database, move the log file and attach the
> > database files from the new location. See sp_detach_db and sp_attach_db
> in
> > the Books Online for details.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Yifei" <yjiang@.sdg.aust.com> wrote in message
> > news:%23hbx8Pk3DHA.1908@.TK2MSFTNGP10.phx.gbl...
> > > Hey, All,
> > >
> > > Recently, we have installed a new raid disk on server, I want to
migrate
> > > transaction log to new disk for improving performance, is this
possible
> > and
> > > how can I do it?
> > >
> > > Thanks
> > >
> > > Yifei
> > >
> > >
> >
> >
>|||Like Wayne said, you can leave the data file(s) in the original location.
However, you should specify all database data and files when attaching the
database regardless of whether or not they were moved.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Yifei" <yjiang@.sdg.aust.com> wrote in message
news:eKtOf8k3DHA.556@.TK2MSFTNGP11.phx.gbl...
> Hey, Dan,
> I think I need to clarify that I only need change transaction log file
> location and keep the database file as is.
> I have tried detach and attach, it seems not work at this situation.
> Any other ideas would be appreciated.
> Thanks
> Yifei
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:%23jPQCkk3DHA.2144@.TK2MSFTNGP10.phx.gbl...
> > One method is to detach the database, move the log file and attach the
> > database files from the new location. See sp_detach_db and sp_attach_db
> in
> > the Books Online for details.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Yifei" <yjiang@.sdg.aust.com> wrote in message
> > news:%23hbx8Pk3DHA.1908@.TK2MSFTNGP10.phx.gbl...
> > > Hey, All,
> > >
> > > Recently, we have installed a new raid disk on server, I want to
migrate
> > > transaction log to new disk for improving performance, is this
possible
> > and
> > > how can I do it?
> > >
> > > Thanks
> > >
> > > Yifei
> > >
> > >
> >
> >
>|||Thanks Dan and Wayne, It works.
Yifei
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:uI5jQ1o3DHA.360@.TK2MSFTNGP12.phx.gbl...
> Dan is right,,, sp_attach/detach will work..
> Do an sp_detachdb dbname,
> then copy the log file to the new destination, and remove the orig.
> then sp_attachdb dbname, datafilename, logfilename
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "Yifei" <yjiang@.sdg.aust.com> wrote in message
> news:eKtOf8k3DHA.556@.TK2MSFTNGP11.phx.gbl...
> > Hey, Dan,
> >
> > I think I need to clarify that I only need change transaction log file
> > location and keep the database file as is.
> >
> > I have tried detach and attach, it seems not work at this situation.
> >
> > Any other ideas would be appreciated.
> >
> > Thanks
> >
> > Yifei
> >
> > "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> > news:%23jPQCkk3DHA.2144@.TK2MSFTNGP10.phx.gbl...
> > > One method is to detach the database, move the log file and attach the
> > > database files from the new location. See sp_detach_db and
sp_attach_db
> > in
> > > the Books Online for details.
> > >
> > > --
> > > Hope this helps.
> > >
> > > Dan Guzman
> > > SQL Server MVP
> > >
> > > "Yifei" <yjiang@.sdg.aust.com> wrote in message
> > > news:%23hbx8Pk3DHA.1908@.TK2MSFTNGP10.phx.gbl...
> > > > Hey, All,
> > > >
> > > > Recently, we have installed a new raid disk on server, I want to
> migrate
> > > > transaction log to new disk for improving performance, is this
> possible
> > > and
> > > > how can I do it?
> > > >
> > > > Thanks
> > > >
> > > > Yifei
> > > >
> > > >
> > >
> > >
> >
> >
>sql

How can you tell what the primary key of a new row will be?

I need to insert a row into a table in SQL Server 2000. The primary
key for the row is an identity type, so it auto-numbers for me without
needing to put in the value in the insert statement.

My problem, is that after i insert a row, i need to insert another row
in a different table that references the first row. To do that i need
to know the primary key for the original row.

How can i tell what the primary key was? In Oracle, you would check
the sequence before the original insert. Is there a similar feature
in SQL Server? And how would you use it?

(I'm using C# ADO)

- PaulOn 7 Jan 2004 17:26:13 -0800, prempel@.paradata.com (Paul) wrote:

>I need to insert a row into a table in SQL Server 2000. The primary
>key for the row is an identity type, so it auto-numbers for me without
>needing to put in the value in the insert statement.
>My problem, is that after i insert a row, i need to insert another row
>in a different table that references the first row. To do that i need
>to know the primary key for the original row.
>How can i tell what the primary key was? In Oracle, you would check
>the sequence before the original insert. Is there a similar feature
>in SQL Server? And how would you use it?
>(I'm using C# ADO)
>- Paul

In SQL Server, you check the identity value immediately following the INSERT.
The old way to do this was to check the @.@.IDENTITY variable, but that's
unrelibale if a trigger also inserts a row into another table, so the new,
preferred way to do it is the SCOPE_IDENTITY function.|||Paul wrote:

> I need to insert a row into a table in SQL Server 2000. The primary
> key for the row is an identity type, so it auto-numbers for me without
> needing to put in the value in the insert statement.
> My problem, is that after i insert a row, i need to insert another row
> in a different table that references the first row. To do that i need
> to know the primary key for the original row.
> How can i tell what the primary key was? In Oracle, you would check
> the sequence before the original insert. Is there a similar feature
> in SQL Server? And how would you use it?
> (I'm using C# ADO)
> - Paul

I'll leave it to someone that knows more about SQL Server than I to
answer your question. But what you suggest for Oracle doesn't work in
Oracle. Well unless you are in a single-user environment. The solution
in Oracle would be to use the RETURNING clause of the INSERT statement
as in:

DECLARE
x emp.empno%TYPE;
BEGIN
INSERT INTO emp
(empno, ename)
VALUES
(seq_emp.NEXTVAL, 'Morgan')
RETURNING empno
INTO x;

dbms_output.put_line(x);
END;
/

--
Daniel Morgan
http://www.outreach.washington.edu/...oad/oad_crs.asp
http://www.outreach.washington.edu/...aoa/aoa_crs.asp
damorgan@.x.washington.edu
(replace 'x' with a 'u' to reply)

How can we modify the files path for the database in database engine?

Hi, all experts here,

Thank you very much for your kind attention.

I am trying to modify the files path (primary file, log file) of databases, but it looks like I am not able to mofidy their files path directly from the database property dialogue? Would please any experts here give me some ideas on what else can I try to figure it out? Thanks a lot in advance and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

I don't think it's possible to modify file paths when the database is active (read only)

I'd suggest DETACH, RE-ATTACH with different file path

should take < 1 minute

|||

You can not just change the path. You have to either detach and attach the database or backup and restore the database. Detach and attach will be faster and easier with less chance of data loss.

Both options are offline procedures.

|||

Hi, all,

Thanks a lot for your kind advices.

With best regards,

Yours sincerely,

|||Hi Helen,

You cant change the filepath when the database is in online. When sqlserver starts it will check for the path of database (from sysaltfiles tables) and it will start accessing the files. Once the database is online it will lock the file (you cant even copy the physical file through OS).

You can detach the db and move the physical file to the path you needed and the attach the db. It will work fine.

The above description is only for user dbs.|||

Hi, Sagar,

Thanks a lot for your very kind advices and help.

With best regards,

Yours sincerely,

How can we modify the files path for the database in database engine?

Hi, all experts here,

Thank you very much for your kind attention.

I am trying to modify the files path (primary file, log file) of databases, but it looks like I am not able to mofidy their files path directly from the database property dialogue? Would please any experts here give me some ideas on what else can I try to figure it out? Thanks a lot in advance and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

I don't think it's possible to modify file paths when the database is active (read only)

I'd suggest DETACH, RE-ATTACH with different file path

should take < 1 minute

|||

You can not just change the path. You have to either detach and attach the database or backup and restore the database. Detach and attach will be faster and easier with less chance of data loss.

Both options are offline procedures.

|||

Hi, all,

Thanks a lot for your kind advices.

With best regards,

Yours sincerely,

|||Hi Helen,

You cant change the filepath when the database is in online. When sqlserver starts it will check for the path of database (from sysaltfiles tables) and it will start accessing the files. Once the database is online it will lock the file (you cant even copy the physical file through OS).

You can detach the db and move the physical file to the path you needed and the attach the db. It will work fine.

The above description is only for user dbs.|||

Hi, Sagar,

Thanks a lot for your very kind advices and help.

With best regards,

Yours sincerely,

How Can I use this code for Primary Key autogeneration

Hi

DECLARE @.MyValue varchar(10)
SELECT @.MyValue = (SELECT RIGHT(YEAR(GETDATE()),1)+
REPLACE(STR(MONTH(GETDATE()),2),' ','0')+
REPLACE(STR(PlaceID,2),' ','0')+
'00001'
FROM Provinces WHERE PlaceName='Kinshasa' )
SELECT @.MyValue

I written a code like this to generate (example: 5080100001 - 5 Year, 08 Month, 01 PlaceID, remaining digit should be automatic increment for the current month) unique number for everymonth. According to my imagination it will increment automatically for each month. Now I want to assign this value to my primary key field. How it's possible in SQL Server 2000.

Thanks in advance
Jose

Here is one way.
Note: this contains undocumented/supported trick. Use at your own risk.

create table seed(i int)
insert seed values(0)
go
create proc getval
as
begin
set nocount on
declare @.i int
update seed
set @.i=i=i+1
select convert(char(4),getdate(),12)+right(1000000+@.i,6) as
end
go
create function dbo.pkgen()
returns char(10)
as
begin
return(select i from openquery(sqlserver_name,'exec tempdb..getval;commit')x)
end
go

create table t(pk char(10) primary key default dbo.pkgen(),i int)
go

insert t(i) values(10)
insert t(i) values(20)
insert t(i) values(30)
select * from t
go