Moving a database from one machine to another and the lack of speed

Dear All,

We have a procedure that takes12 minutes to run on the first server but
that same procedure now takes 3 hours to run on the second server using the
same data. Does anyone have any suggestions why this is happening and how
to make the procedure faster on the second server

Thanks in advance.

Jeff Magouirk"Jeff Magouirk" <magouirkj@.njc.org> wrote in message
news:113bhrh7krjtjad@.corp.supernews.com...
> Dear All,
> We have a procedure that takes12 minutes to run on the first server but
> that same procedure now takes 3 hours to run on the second server using
> the same data. Does anyone have any suggestions why this is happening and
> how to make the procedure faster on the second server
> Thanks in advance.
> Jeff Magouirk
>

No idea - the usual advice if you see something like that is to a) update
statistics, and b) use Profiler to identify what's taking so much time. You
can also capture the execution plan in Profiler (or use Query Analyzer,
which is a bit more user-friendly), and compare it against the plan on the
original server.

If that doesn't help, you'll need to give more details about your
environment - are they the same MSSQL version? The same hardware? Do you
have the same database schema (including indexes etc.)? Are the database
files exactly the same size? Are they using the same recovery model? Is the
data the same (or nearly so)? Is everything slow, or only certain
procedures? Etc.

Simon

Moving a database

I would like to move my arc serve backup from the original datastore to
an SQL server. is there a procedure anywhere for this ?Are you saying that you have backup of a SQL Server database performed with
ArcServe and you want to
restore that to a SQL server? If so, check the ArcServe documentation. Or do
you want to create a
SQL server backup file of that ArcServe backup? If so, I would imagine that
ArcServe should have
such a utility (if they are a serious player).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"scuba19" <scuba19@.discussions.microsoft.com> wrote in message
news:90EE8394-F9AD-4B2B-951F-4931905F66A1@.microsoft.com...
>I would like to move my arc serve backup from the original datastore to
> an SQL server. is there a procedure anywhere for this ?

Moving a database

I would like to move my arc serve backup from the original datastore to
an SQL server. is there a procedure anywhere for this ?More information needed than this.|||Are you saying that you have backup of a SQL Server database performed with ArcServe and you want to
restore that to a SQL server? If so, check the ArcServe documentation. Or do you want to create a
SQL server backup file of that ArcServe backup? If so, I would imagine that ArcServe should have
such a utility (if they are a serious player).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"scuba19" <scuba19@.discussions.microsoft.com> wrote in message
news:90EE8394-F9AD-4B2B-951F-4931905F66A1@.microsoft.com...
>I would like to move my arc serve backup from the original datastore to
> an SQL server. is there a procedure anywhere for this ?|||Arc Serve has a database containing the information of the datastore
including indexes references etc. This is needed if a restore is going to
be done. I would like this database that arcserve creates to be put into
an SQL database, instead of residing on my local drive.
"unc27932@.yahoo.com" wrote:
> More information needed than this.
>

Moving a database

I would like to move my arc serve backup from the original datastore to
an SQL server. is there a procedure anywhere for this ?
Are you saying that you have backup of a SQL Server database performed with ArcServe and you want to
restore that to a SQL server? If so, check the ArcServe documentation. Or do you want to create a
SQL server backup file of that ArcServe backup? If so, I would imagine that ArcServe should have
such a utility (if they are a serious player).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"scuba19" <scuba19@.discussions.microsoft.com> wrote in message
news:90EE8394-F9AD-4B2B-951F-4931905F66A1@.microsoft.com...
>I would like to move my arc serve backup from the original datastore to
> an SQL server. is there a procedure anywhere for this ?

Moving .ldf to new drive, doesn't work gives "Cannot associate fil

The procedure you described should work fine. But I suspect your .ldf file is
not really from that database.
Linchi
"caperneus@.gmail.com" wrote:

> I am trying to move a log file to a non-RAID drive. I have detached
> the database and made a copy of the .ldf file. I have taken this copy
> of the .ldf file and placed it where I want it to be. I would like to
> keep the .mdf file where it is located on the RAID drive, but for some
> reason I can only reattach if the .ldf file is in the same location as
> the .mdf file.
> I have then run:
> USE MASTER
> GO
> EXEC sp_attach_db @.dbname = N'Corporate',
> @.filename1 = N'F:\Web\MSSQL$WEB\Data\Corporate_Data.mdf',
> @.filename2 = N'E:\Web\Corporate\Corporate_Log.ldf'
> And it returns:
> Server: Msg 5173, Level 16, State 2, Line 1
> Cannot associate files with different databases.
> Is this possible?
>
Instead of detached and attached try backup and restore ...
BACKUP DATABASE Corporate
TO DISK = 'E:\backup_corporate.bak'
WITH INIT
Drop the database, and delete the mdf and ldf file (or just back it up)
Then the Restore:
RESTORE DATABASE Corporate
FROM DISK = 'E:\backup_corporate.bak'
WITH RECOVERY,
MOVE '[datafilelogicalname]' TO 'F:\Web\MSSQL$WEB\Data\Corporate_Data.mdf',
MOVE '[loglogicalname]' TO 'E:\Web\Corporate\Corporate_Log.ldf'
Hope that works.
Lucas
<caperneus@.gmail.com> wrote in message
news:1181852163.020692.243720@.a26g2000pre.googlegr oups.com...
> On Jun 14, 2:44 pm, Linchi Shea <LinchiS...@.discussions.microsoft.com>
> wrote:
> It is really from that database. I can attach it back with no problems
> if the files are in the same directory.
>

MoveFirst re-runs procedure

Okay this has had me stumped for a while... I've now sussed that myRecSet.MoveFirst can re-run the query!! Eh?

My SQL is "EXEC sp_Proc 'YYYYMM'" This proc will create records in a table if they don't exist, and return a msg. If the records already exist it returns a diff msg. Simple!

I create the recordset in the fashion of myRS = myDB.Execute (mySQL) though I've tried myRs.Open mySql, myDB, myRSType (with various recordset types) with the same result.

I run the query and prior to doing a MoveFirst the 1 line recordset has the expected result (create). When I do a MoveFirst the recordset has the 'existing' message.

Now I know the MoveFirst is not actually required (but it's in a lot of our old systems), but I can't see why this should happen. I've got a big dent in me head now from bashing it against the desk. Any comments or ideas would be most welcome before I throttle the soft toy on my desk!Have you tried this with pubs or northwind databases in sql server ? Post your code and sp.

moved stock minus in item table to stock in itemmoment table

helo all...,

i want to make procedure like:
example

i have table:
item (itemid,itemname,stock)
orderdetail(no_order,itemid,quantity)
itemmoment(itemid,itemname,stock)
item table
itemid itemname stock
c1 coconut 2
p1 peanut 2

orderdetail table
no_order itemid quantity
1 c1 5

itemmoment table
itemid itemname stock
c1 coconut 0
p1 peanut 0

when customer paid, his quantity in orderdetail decrease stock in item table..
so stock in item table became:
itemid itemname stock
c1 coconut -3
p1 peanut 2

it's not good, because stock may not minus...
so i want to move -3 to itemmoment table..
so stock in item table became:
itemid itemname stock
c1 coconut 0
p1 peanut 2

and in itemmoment table became:
itemid itemname stock
c1 coconut 3
p1 peanut 0

my store procedure like:
ALTER PROCEDURE [dbo].[orders]
(
@.no_order as integer,
@.itemid AS varchar(50),
@.quantity AS INT
)
AS
BEGIN
BEGIN TRANSACTION

DECLARE @.currentStock AS INT

SET @.currentStock = (SELECT [Stok] FROM [item] WHERE [itemid] = @.itemid)

UPDATE [item]
SET
[Stock] = @.currentStock - @.quantity
WHERE
[itemid] = @.itemid

COMMIT TRANSACTION
END

it's only decrease stock with quantity. i want move stock minus from item to itemmoment..
can anyone add code to my store procedure?

plss.. helpp.
thxx...

Here's how you would tackle the issue.

Declare @.AvailableQtySelect @.AvailableQty =Count(*)FROM ItemWhere ItemId = @.ItemIdIF @.AvailableQty >= @.QuantityBegin-- We have enough quantityUpdate ItemSet Stock = Stock - @.QuantityWhere ItemId = @.ItemIdEndElseBegin-- We have less quantityUpdate ItemSet Stock = 0Where ItemId = @.ItemIdInsert into ItemMoment (itemid,itemname, stock )Select @.ItemId, ItemName, (@.Quantity - @.AvailableQty )From ItemWhere Itemid = @.ItemIdEnd

|||

thx ndinakar...

it's one problem in ur store procedure, but i have fix it.

Select @.AvailableQty =Count(*)FROM ItemWhere ItemId = @.ItemId

change to

set @.AvailableQty = (select stock FROM item Where itemid = @.itemid) 

it have working...

ok, thx...

Move Sql 7 databases to SQL 2000 server on different network

What would be the best/easiest procedure to move a few SQL 7 databases to a
SQL 2000 server that is running on a different network?It's easy enough to back them up, copy the backup files to
the SQL2000 server, and restore there.
If you have reliable network connectivity, you could skip
the copy step and restore directly from the backup files
on the remote server by specifying the files' UNC names.
You could also backup to the remote server, and then
restore from the backup files locally on the other server.
Linchi
>--Original Message--
>What would be the best/easiest procedure to move a few
SQL 7 databases to a
>SQL 2000 server that is running on a different network?
>
>.
>|||| What would be the best/easiest procedure to move a few SQL 7 databases to
a
| SQL 2000 server that is running on a different network?
--
I would backup the SQL 7 database, ship the backup file to the SQL 2000
server and restore it there.
Hope this help,s
--
Eric Cárdenas
SQL Server support|||>--Original Message--
>| What would be the best/easiest procedure to move a few
SQL 7 databases to
>a
>| SQL 2000 server that is running on a different network?
>--
>I would backup the SQL 7 database, ship the backup file
to the SQL 2000
>server and restore it there.
Make sure that both servers are using the same settings
for Locale, Collation and case sensitivity.
Dave.|||> Make sure that both servers are using the same settings
> for Locale, Collation and case sensitivity.
Just to expand a bit:
They don't *have* to be on the same collation setting as in SQL2K, you can have different collations
between different databases. However, that might cause problems if the code isn't written to handle
this, so I agree that this is a good idea.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Dave" <anonymous@.discussions.microsoft.com> wrote in message
news:06fe01c3c3b9$aa8a1cd0$a501280a@.phx.gbl...
> >--Original Message--
> >| What would be the best/easiest procedure to move a few
> SQL 7 databases to
> >a
> >| SQL 2000 server that is running on a different network?
> >--
> >I would backup the SQL 7 database, ship the backup file
> to the SQL 2000
> >server and restore it there.
> Make sure that both servers are using the same settings
> for Locale, Collation and case sensitivity.
> Dave.