Slow Database Restore
Jan 9, 2008
Has anyone else experienced this?
Database Restore takes much longer on Windows 2003 64-bit than on 32-bit...
Is this simply the Service Pack level or does it have to do with the 64-bit/32-bit issue?
We have a Development/QA/Production environment setup in this manner:
DEV - fast restores - (about 2 hours)
OS: Windows Server 2003 R2 Service Pack 2
DB: SQL 2000 Service Pack 4 (32-bit)
QA - slow restores - (about 10 hours)
OS: Windows Server 2003 x64 Service Pack 1
DB: SQL 2000 Service Pack 4 (32-bit)
Production - slow restores - (about 10 hours)OS: Windows Server 2003 x64 Service Pack 1
DB: SQL 2000 Service Pack 4 (32-bit)
Thank you for your time in advance!!!
Adminicrater
View 6 Replies
ADVERTISEMENT
Jan 22, 2008
Hi
I have configured a cluster SQL 2005 (active/passive) and added 4 LUNS to that cluster the cluster works without problems.
However I am having issues backing and restoring on 2 drives. I can complete the operation ( a resotre of 100MB ) DB in less than a minute if i use the 2 drives but it takes about an hour if i use the other 2 drives. The wait type is ansynchronous_IOcompletion and backupthread in dm_exec_requests.
I have detached the drives and reformatted it but stil lsame issues any help
Anup
View 4 Replies
View Related
Feb 1, 2008
I have a table that has appx 3.2 million rows. see sp_help
Name Owner Type Created_datetime
-------------------------------------------------------------------
TB_SAAI014_BPD dbo user table 2005-08-10 11:33:23.893
Column_name Type Comp Lngth Prec Scale Nullable
------------------------------------------------------------------------
RowID int no 4 10 0 no
SPHInstID int no 4 10 0 no
BPDInstID int no 4 10 0 no
BMUID varchar no 11 no
InfoImblCfw numeric no 9 12 2 no
BMUPrdNonDel numeric no 9 12 2 no
PrdFPN numeric no 9 13 3 no
PrdBMUBalSrvVol numeric no 9 13 3 no
PrdInfoImblVol numeric no 9 13 3 no
PrdExpdMtrVol numeric no 9 13 3 no
BMUMtrVol numeric no 9 13 3 no
PrdBMUNonDelBidVol numeric no 9 13 3 no
PrdBMUNonDelOfrVol numeric no 9 13 3 no
TranLossFctr numeric no 9 15 7 no
TranLossMtpl numeric no 9 15 7 no
TradUnitName varchar no 30 no
TotTrdUnitMtrVol numeric no 9 13 3 no
BMUAppBalSrvVol numeric no 9 13 3 no
DTCreated datetime no 8 yes
DTUpdated datetime no 8 yes
Identity Seed Inc Not Repl
-----------------------------------------
RowID 0 1 0
RowGUIDcol
-----------------------------
No rowguidcol column defined.
Data Located on File Group
==========================
PRIMARY
Index Name Decsription Keys
----------------------------------------------------------------------------
idx_SPH_BPD clustered, unique located on PRIMARY SPHInstID, BPDInstID
This table has 1 clustered index based on its own unique record ID and that of its parent table record
I have an import process that adds appx 980 rows of data to this table and numerous rows to several other tables as part of a transaction and it ran in about 15 seconds.
However we suffered a server failure and it had to be rebuilt (Svr2k3), SQL 2000 re-installed (with default options) and the data base restored.
The same transaction is now taking 8 to 9 minutes.
I tracked it down to this particular table. Just doing a count(*) takes over 5 minutes. Select * where ID = 1 takes over 5 mins. Also, whenever the table is accessed you can hear the server thrashing the disks. Other tables, although smaller do not seem to be suffering from this masive performance drop..
I've tried droping and recreating the index. I have even created a copy of the table, with index, and still get the same issue with speed.
DBCC CHECKTABLE returns the following but takes 6 and a half minutes
DBCC results for 'TB_SAAI014_BPD'.
There are 3168460 rows in 72011 pages for object 'TB_SAAI014_BPD'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
No errors are shown
A DBCC CHECKTABLE on another table with 230 thousand rows, run at the same time only took 10 seconds
Can anyone please point me in the direction of things to check, try or repair.
Any help greatfully recieved.
Jinx1966
View 10 Replies
View Related
Apr 27, 2007
I have seen this before. A 2000 restore fails, leaving the database thinking it is being restored but the restore job failed and errors when it is restarted. EM is clueless. I believe there is a proc to reset some flag. Can you share it with me???
Thanks!
View 4 Replies
View Related
Nov 19, 1999
11/19
Trying to keep out sysadmins & sa during/between database RESTORE
Configuration:
WINNT Server Enterprise 4.0 w/SP5
SQL Server 7 Enterprise & SP1
2 SQL Servers:
Production Server
Standby server
I Backup (full backup) databases to disk on primary server (logical backup devices are physicaly located on a Standby server (dedicated gigabit NIC in each server for this process). Transaction logs are applied to the Standby server throughout the day.
Problem:
How to keep out "sa" and sysadmins from a database while I'm restoring (or between restores) to a standby server?
The database being restored cannot be in use during a restore.
If a DBA forgets that this process is happening, the statement fails (RESTORE)for the database they happen to be in at the time of the restore.
Example restore statement:
Standby Server -
RESTORE DATABASE databasename FROM database_dd WITH DBO_ONLY, REPLACE, STANDBY = 'g:Mssql7FromPrimaryDatabaseName_undo.ldf'
I could restrict Domain sysadmin access and change sa password. I could also put the database in "Single user" mode, however this could become problem if my process disconnects and then someone else connects - then my process is locked out. What I'm really looking for is to lock out all activity for a database that is in "standby mode" except for RESTORE processes.
Any ideas??
Wade
wadej@vailresorts.com
View 1 Replies
View Related
Aug 23, 2000
I have a database that's 2.5GB but only has about 17MB of actual data. I've setup a standby server that I load my dumps into. The load takes about 10 miuntes. The dump takes about a minute and a half (which also seems slow to me for that small amount of data). I don't expect that it should take that long to load 8800 pages into a database. The standby server is the same hardware as the production server (sinlge 500MHz Xeon, 2GB RAM, RAID 5). The server has only a single RAID 5 array to store all the OS, and all the SQL data however, I still don't thinkit should take thta long to load. Let me know what you think.
--Buddy
View 1 Replies
View Related
Oct 5, 2004
I have a access database, the data store in another server. This noon, one of our user is runing the access database too slow. Open the database and search the data, etc. It took a long time to come out, Any body has experience on it, why, we had etrust install on each user machine, is that cause this too slow? Thanks in advance.
View 1 Replies
View Related
Jul 31, 2007
dear friends,
i have dropped many objects and recreated in a database.suddenly my database became very slow. so please any one of friends give solution.
View 9 Replies
View Related
Sep 28, 2007
HI Friends,
My database performing slow.
In which area i will take care.
Can u any body plz explaine me step by step plz
Thsx
siva
Meti BEST OF THE BEST
View 2 Replies
View Related
Nov 23, 2007
I have designed a 22 table database in sql server that is to act as a backup/alternate access to the data we have stored in an ADABAS database. I've also written a vb.net console program that will take data from ADABAS through a broker connection (one row at a time), checks the sql server database to see if that information is already stored, and then either performs an insert or an update.
I can write the rows from ADABAS to a text file (not using the broker), at the rate of about 1.3 million rows in 1.3 hours. Data can be imported (I'm not sure how this import is done, possibly via a CSV file. SELECTS/UPDATES are not done, just INSERTS) at about 1 million or so an hour. But when I do the update, receiving information via the broker from ADABAS to the VB program (with it's SELECT, then UPDATE/INSERT), I'm only doing about 20-25 thousand rows an hour.
I ran a trace using the SQL Server Analyzer on the database while running the update program, and then ran Profiler using the generated workload. It created a few indices, but I just restarted the update program (I'm still developing, so I delete all rows from all tables each time I rerun the update), but I haven't seen that it's really any faster.
I have a rather large set of data to transfer over, and this 20-25 thousand row time is not nearly fast enough.
Any help will be appreciated.
Thanks,
View 1 Replies
View Related
Jul 20, 2005
We just installed SQL Server version 800.194 on a dual processorserver equipped with a gigabyte of RAM, running Windows 2000 Serveroperating system. We set up a few databases with (so far) very tinytables.When I am working locally (i.e. on the server itself) with QueryAnalyzer, even the simplest operation is incredibly slow. If I bringup Windows Task Manager looking at the Processes pane (Query Analyzershows up as "isqlw.exe"), and use "View/Select Columns..." to choose"I/O Writes" and "I/O Write Bytes", then I observe that doing aselect* on a table with a single row results in over 500 "I/O Writes"and 170,000 "I/O Write Bytes" of data written. It requires 15 secondsto return the single row of information.Even clicking on the Change Database Listbox results in hundreds ofwrite operations!However, when I am working remotely with Query Analyzer, the select*works perfectly normally. Neither "I/O Writes" nor "I/O Write Bytes"are recorded.I figure maybe there is some sort of security logging turned on thatrecords everything you do...Whatever is going on here, how do you turn it off?Tom
View 1 Replies
View Related
Jul 20, 2005
I have a database that had worked under msde 1.0 until reached the 2GBof dimension and the dB was blocked. To make possible the work i haddeleted old data from some table. The database restart, but theanswers from server become very slow. So i decided to pass to SqlServer 2000 without success. May i perform a check of this database?if is a indexs problem there is a way to rebuild them?Thanks in advanceAndy Wet
View 1 Replies
View Related
Sep 15, 2007
I just found out the response time of open a connection or execute a SQL command over VPN is very slow. It takes around 150ms for each round trip. If the same program run on LAN, it takes less than 1ms. I understand that VPN may have encryption and thus have a bit delay. However, if the delay happens whethever I make a SQL call, it will be unacceptable. Is there anything that I have missed out? If the delay occurs once only, it will be still great. (I think this is the point of connection pooling. Right?) However, it's really bad if the delay occurs each time I call SQL. Please help!
View 5 Replies
View Related
Feb 12, 2008
I have a portal site that has many iframes loading various pages. One of the iframes requires data from a database that has a slow connection and right now there is nothing we can do about the slow connection and is something we have to live with.
What seems to be happening though is that even though each page is loading seperatly in an iframe, when the page loads with the slow connection, it seems to hold up processing on the server for the other frames until the connection has been established with the server. It can be something like 10 seconds. I am guessing trying to establish the connection is holding up the worker process on IIS???
So I am trying to find a workaround bearing in mind there is nothing we can do about the slow connection for the time being? Does anyone have any suggestions? One I am thinking of is forcing this frame to load last so at least the other frames are not being held up. Another is maybe to use a seperate thread, but does anyone have any idea on this?
Thanks in advance
View 1 Replies
View Related
Aug 2, 2007
I'm querying a small SQL2005 database and finding that the query can sometimes complete in under a second and then 5 minutes later the same query can take 15 minutes to complete.
The query I'm running is very simple as follows:
select TOP 26 * from vSearchListOpportunityItem WHERE OpIt_OpportunityId=2495 ORDER BY Prod_Name, OpIt_OpportunityItemId
The view it is pulling data from only contains only 1890 lines, which in turn pulls data from 3 tables with 821, 2560, and 1957 lines of data. In other words it's small. I have noticed that if I try and open the smallest of these tables while on a 'go slow' period it also takes around 15 minutes to return the data.
The database was originally on SQL 2000. It is the only database on this powerful quad core server.
The SQL Server CPU usage never goes above 40%, and always has free memory.
No sign of locks.
I can't figure out why such a small database is going so slow with such a simple query. Any ideas?
View 8 Replies
View Related
Feb 28, 2007
I am developing a mobile 5.0 application. I use mobile Sql as the database in the pda.
In the program, i use dataset.xsd to create the table and tableAdapter, but the performance is very slow for just access the data from the database. It takes about 4200ms for just
this.userAdapter = new PDA_USERTableAdapter();
MBDB2DataSet.PDA_USERDataTable ut = userAdapter.GetUser();
the "new PDA_USERTableAdapter()" is very fast.But...
The userAdapter.GetUser() will only return about 20 rows, each rows only contains 5 field .But it cost 4200 ms for this line.
The sql statement in userAdapter.GetUser() is
SELECT User, PASSWORD, TYPE, USER_ID, Supervisor_ID
FROM PDA_USER
WHERE (TYPE = 5)
ORDER BY User
Please Help, Urgent!!!!!
p.s (The total rows in the PDA_USER table is only 30 rows)
Thank you very much
View 3 Replies
View Related
May 9, 2012
I'm taking a database(read-only) backup from one server and restoring it on other server. As soon as restore is done it is bringing database into single-user read-only mode.
why it is bringing the database into single user mode ?
View 1 Replies
View Related
Nov 15, 2006
Hello,i am in great trouble. I want to revert back to original state ofdatabase before i performed restore database on my sql server 2KDatabase. Accidently i didn't take backup of my Database and i didrestore, so is there any way to get the original state back of myDatabase?Any suggestion will be highly appriciated.Regards,S. Domadia.
View 2 Replies
View Related
Dec 26, 2003
Hi i have a sql server instance on my system and it is linking into an oracle database on another server. When i run queries against this other server...it takes forever...
However, when i use access, and link the table and run the same query against the oracle database...it runs immediatly.
I am very confused as to why there would be such a performance difference and why sql server would run so slow.
I am wondering if it has something to do with the way i configured the linked server. there are several options that I didn't know what they meant.
collation compatible (not selected)
Data access (selected)
RPC (not selected)
RPC Out (not selected)
collation name
connection timeout
query timeout
View 1 Replies
View Related
Apr 18, 2002
I upgraded from 6.5 to 7.0 SP3. Now when I save (write) an invoice it takes about 10-12 seconds, at 6.5 it was 1-3 seconds. SQL Server and my Materials App are the only thing running on this box. This is the only area that has gotten slower everything else works great. I have 3 users saving invoices and about 15 people total using the system at one time. It's a compaq DL580 loaded with memory, database is 2,195MB in size. Same 6.5 client to access system as before. Should I rebuild/reindex the database? Is there something from the old 6.5 version I need to remove?? Thanks in advance!!!
View 1 Replies
View Related
May 29, 2008
Hi guys,
I am asking this question on behalf of a friend. I have little knowledge of SQL 2005 but my friend is quite knowledgeable, although this is the first time he is dealing with large database for a client. So here's the story.
His client has a database containing 1.5 million books. Now he is setting up a website which will enable users to search books. Searching by ISBN is no problem as it only takes 1 seconds. The problem is, searching by Title takes more than 20seconds, which is unacceptable. My friend has only done smaller database and he just recently thought of implementing indexing and now looking for other ideas.
Each row contains book details such as Title, Author1, Author2, Author3, Publisher, Publication Date, ISBN, etc.
Can anyone who are more experienced in doing large database share with me some design ideas? His client is aiming for 8seconds or less.
Thanks in advance!
View 14 Replies
View Related
Apr 30, 2007
Hi,
My database was previously running on sql2000 with 2 gigs of RAM and 2 x 2.8ghz XEON processors, and was running pretty decently.
I've now upgraded to SQL2005, 8 gigs of RAM, and 1 Intel 5130 2ghz processor (supposed to be more CPU power than previously)
The problem is its now running very SLOW.
I have run a trace and I'm finding queries that used to take 50,000 reads are now taking 1.4 million reads (25x more) The system runs for a decent amount of time but then SLOWS down massively for awhile. I can't find any cause yet.
What could be causing this ? What steps can I take towards resolving this? I have ran Tara's isp_ALTER_INDEX to try and help, I'm not sure what else to do.
Any suggestions are GREATLY appreciated..
Thanks very much,
mike123
View 7 Replies
View Related
Feb 10, 2007
I've noticed that after the database have been idle for some time, it takes up to 10 seconds to get it started when something needs to access it. In the event viewer it says that the database <name> have been started.
Obviously, there is some idle timeout setting.
I saw an option in the database properties that is called "Auto Close" which is set to true. I assume this is what i'm looking for. Can someone confirm that? (it could take some time to test myself...)
But what i'm actually wondering is:
1. Is it possible to adjust how long it would wait before timing out?
2. What advantages does closing the database bring? Does it free up (a noticeable amount of) ressources? Or is it only that it's unlocking the files, so that it's possible to copy the database source files?
View 4 Replies
View Related
May 7, 1999
Anybody figure a way around this:
In a development server you take a backup and you want to move the entire database to a production server. The production server does not contain the database, users, or logins in master.
When you restore the backup to production in 7.0, the users get moved to DBO because the logins do not exist and then you cannot delete them.
We are having to drop the users from the database on development, back up the database and restore it to production, then recreate the users on production.
This is bogus and did not happen in 6.5 because of the aliases.
Anybody come up with a better way?
View 1 Replies
View Related
Jul 3, 2007
I am fairly new to VS 2005 and SQL Server CE. I have developed a Desktop Windows application using VB 2005 and the SQL Compact Edition Database. This application will sell to users via web download.
When they download a service patch, or updated version, I wish for them to retain the data in their present database. In other words, fill the new database with the old data. The new table structures will remain intact except for added columns at the end.
Question is how to save the old data and update the new database with it. Is there an easy way to do this, or do I need to write a module to save a database copy, and update the new database with content at install time?
View 6 Replies
View Related
Oct 23, 2007
Hi all,
This managed application was written to run on a Symbol 3090 Win CE 5.0 scanning device. We are using the symbol provided classes to access the scanning interface, and SQL Compact database on the device to collect the scanned data, and then using merge replication to synchronize scanned data when the device is docked. The problem we have experienced seems to be releated to the performance when inserting and updating records in the database.
We have tested some randomly generated 1000 records and inserting/updatating into a database. At first the time to commit a record increases when the database is flushing into the memory (The flush interval in the connection string property is 10 seconds by default). and then as the database size grows increasing the time to commit every single record which is causing the application to perform slowly as they scan items into the database. However, the device program memory remains consistant as they are scan items. From our tests, I found the time to execute either a update/insert command on 2MB sqlMobile database (upto 10000 records, depending on the size of the columns) is taking nearly 2 to 2 and half seconds to complete. Below is the only code I am executing,
If Not sqlObj.UpdateItem(1061022, itemNo, 1) Then
sqlObj.InsertResultSet(1061022, itemNo, itemObj.Style, itemObj.Color, itemObj.Size, itemObj.Description, 0, 1)
End If
For the notes, I am using prepared updated command and resultset.insert methods to perform update and insert commands into the database.
Any help on this issue is highly appreciated.
Thanks
Ravi.
View 1 Replies
View Related
Apr 9, 2008
I have a problem when i restore my .DAT_BAK file. I am getting error like "The backup set holds a backup of a database other than existing database. Restore Database is terminating abnormally".
I tried by using
RESTORE DATABASE <DATABASENAME>
FROM DISK = 'D:DATAMYTEST.DAT_BAK'
WITH MOVE 'VZAI_DATA' TO D:PROGRAM FILES..MSSQLTEST.MDF',
MOVE 'VZAI_LOG' TO D:PROGRAM FILES..MSSQLTEST.LDF',
REPLACE
And also i tried like
RESTORE DATABASE <DATABASENAME>
FROM DISK = 'D:DATAMYTEST.DAT_BAK'
WITH REPLACE
When i use like this,
RESTORE FILELISTONLY FROM DISK = 'D:DATAMYTEST.DAT_BAK'. I am able to get the output as LogicalName, PhysicalName, Type, FileGroupName, Size, etc.
Can i anyone please help me out?
Thanks in Advance,
Anand Rajagopal
View 8 Replies
View Related
Dec 19, 2007
Hello All,
I’m looking for a solution to timeouts that occur when I’m executing a stored procedure from my web application. Most of the SPs will run from 3 to 15 minutes, and, unfortunately, modifying/optimizing them isn’t an option at the moment. I tried setting the CommandTimeout to 0 with no luck. Unless, I didn’t use it properly. Here’s my code: 1
2 try
3 {
4 string dbConn = ConfigurationManager.ConnectionStrings["ConStringNTMTLDEV"].ToString();
5 OleDbConnection connection = new OleDbConnection(dbConn);
6
7 lbl_SearchResult.Text = dbConn;
8
9 //OleDbDataAdapter adapter = new OleDbDataAdapter();
10 OleDbCommand cmd = new OleDbCommand("SP_CallHistoryLookUp", connection);
11 cmd.CommandType = CommandType.StoredProcedure;
12
13 cmd.Parameters.Add(new OleDbParameter("@phoneNumber", "1234567890"));
14 cmd.Parameters.Add(new OleDbParameter("@email", "123@123.com"));
15 cmd.Parameters.Add(new OleDbParameter("@WebUser", "123"));
16 connection.Open();
17 cmd.CommandTimeout = 0;
18 cmd.ExecuteNonQuery();
19 cmd.Dispose();
20 connection.Close();
21 }catch(OleDbException ex)
22 {
23 lbl_SearchResult.Text += "<br/> Something went wrong </br>";
24 lbl_SearchResult.Text += ex.Message.ToString();
25 }
26
27
28
Is it possible to launch a stored procedure and close the connection without waiting for a result?Would the stored procedure still run on the SQL server? I’m using MSSQL 7. Would you have any examples that would solve this problem? Thank you for your help.
R.
View 5 Replies
View Related
Jul 29, 2014
Sometime during the night last night some user account permissions were "lost". Am I right to think that restoring the master database would be the way to go? We have a 2 node 2012 cluster and I stop the cluster resource and start the db in single user mode from the active node. Somehow the sharepoint farm is still trying to connect so I can't get logged in single user. What method could I use to stop users from connecting when I don't have access to the sharepoint farm.
View 8 Replies
View Related
Apr 26, 2007
i have a development database that has updates and changes to a production database. rather than go through individually and alter all relevant tables and stored procedures, id like to back up the database on the development side and restore it on the production side as the production database. is there a way to restore the database on the production server but preserve all the security settings (ie logins and such)? i noticed on our development server, that if i try to restore the database with my development database, it overwrites the users and/or if the user is the same on both, it removes the login name for that user.
View 3 Replies
View Related
Aug 10, 2007
Hi,
I was wondering how I could restore a bkp file into a database with a different name. For example, I made a backup of T1.mdf and i want to restore t1.bkp to T2.mdf.
I am using VB.Net to do the backup and restore.
Is there any way of doing it?
Thanks!
View 21 Replies
View Related
Aug 19, 2015
I have a client that has POS software called Restaurant Pro Express (RPE) from [URL] ...
Their old POS computer had a hardware failure, but I was able to attach the hard-drive to another computer and recover the data. RPE uses a MSSQL database system. However, my client doesn't seem to make backups very often
- the last one is dated January 5, 2015. I was able to copy the C:Program FilesMicrosoft SQL Server folder over which contained the instance as well as all the data files - and has up-to-date information. The instance in the recovered Microsoft SQL Server folder was called MSSQL.1. I installed the RPE software on their new computer, and it too now has an instance called MSSQL10_50.PCAMERICA. The new computer is using MSSQL 2008 R2, while I believe the old computer would have been using MSSQL 2005.
I am no DBA expert, especially when it comes to MSSQL. Is is possible to 'restore' the database from the 'raw' .mdf and .ldf files of the old computer to the new computer / database instance? If so, how should I proceed?
View 3 Replies
View Related
Jun 6, 2000
I had a major disk failure, and the only SQL Server files I was able to recover were master.dat, msdb.dat,msdblog.dat, two text files and two database devices. My question, is it possible in SQL 6.5 to restore a database from just the database device, and if so, how? Thank you.
MH
View 1 Replies
View Related
