Unable To Solve Performance Issue In Production Server (ERP)
Jan 2, 2008
Hai,
we have a Dell pe 6600 server running for our ERP Application databases. version of Sql server is 2000 with sp4. OS is windows 2000 with SP 4 .we were running out of space in server and we planned to upgrade the harddisks.
after upgradation, the Performance of the server is very slow. Even zipping of .bak files from one drive to other is slow (which is not related to database). In old setup (before upgradation ) , to zip .bak files (backup files of DB's)(tot Size : 90 GB) use to take 3hrs 40 mins. Now it is taking more than 10 Hrs. There are no errors found in Windows Event viewer and SQL Server.
we logged a call with Dell and finally, dell said that they could not find any errors in raid configuration or harddisks. all the firmware and bios versions are up todate. Application vendor is also saying that, the database is fully optimized. we could not find out the reason for the sluggish performance and we feel that this is something to do with hardware or with raid configuration.
From our analysis,any activity that requires a read or write activity from the harddik is slow.( but server vendor is saying that there is no problem in the hardware.)
Activities analysed (all at zero load on server)
1) backup thru SQL Server - 40 mins (old setup)
backup thru SQL Server - 2 hrs (New setup)
2) zipping of .bak files (from Raid 5 HDD to Raid 1 HDD)
old setup - 3 hrs 40 mins
New setup - 8 hrs 30 mins
3) all the schedulers (both windows and sql server) are dead slow.
4) Maintenance jobs for databases
old setup - 5 - 6 Hrs
New setup - 11 - 12 hrs
after analysing all the above activities ,we came to a conclusion that the problem may not be with sql server.
But we could not trace out the problem till now. since it is the production server we are not able to take downtime that easily. i have given the old set up and new setup configuration below.
Note: the server lies with the same configuration in both old set up and new setup.(only harddisks are upgraded.
Please help me to solve this issue
As of now, we have decided to start from the scrap once again. before that can i get some help ???
old set up:
Raid 1 - 32 x 2 - (two Partitions C and D )
C - OS , D - Batch Files
Raid 5 - 72 x 5 - (two Partitions E and F ) (1 harddisk - Hotspare - 4 hdd will be used for raid)
E - .MDF Files , F - .BAK Files (Backup of database files will be stored here temporarily before moving it to tapes)
Raid 1 - 32 x 2 - (two Partitions G and H )
G - .LDF Files , H - to store the zipped .bak files ( we have a windows sceduler which zip the .bak files in E drive and place the .zip files in H drive)
New Set up:
Raid 1 - 72 x 2 - (two Partitions C: and D )
C - OS , D - Batch Files
Raid 5 - 300 x 5 - (two Partitions E and F ) (1 harddisk - Hotspare)
E - .MDF Files , F - .BAK Files (Backup of database files will be stored here temporarily before moving it to tapes)
Raid 1 - 72 x 2 - (two Partitions G and H )
G - .LDF Files , H - to store the zipped .bak files ( we have a windows sceduler which zip the .bak files in E drive and place the .zip files in H drive)
View 3 Replies
ADVERTISEMENT
Sep 19, 2007
I dont alot about sql server 2005(Express edition). For debugging purposes i want to copy the whole app_data folder(.mdf & .log files) on the production server to another folder on the same machine(or sometimes to a network folder). So when i copy and try to paste this App_data folder to a new location, i get this error message
"cannot copy ASPNETDB: it is being used by another person or program. close any programs that might be using the file and try again."
After reading the above message, i close visual web developer, stop the website in IIS and stop the SQLExpress service on the server and try again but still get the same message.
So how can i make sure that all the programs accessing these database files are closed such that i'm able able to copy them to a different location.
View 2 Replies
View Related
Oct 14, 2014
Was wondering, when performance changes have been made to existing code and deployed to a production environment, should anything be done regarding clearing old cache?
Is there a risk of the new code picking up cold or wrong cached query plans etc?
I know you can't clear the cache for the full system as it will affect everything else. But are the ways to target the changes? I.E run the new code with an OPTION (RECOMPILE) few times to build up new clean plans for it to use?
Or will the update stats see it is using bad plans and then start creating new clean ones over time?
View 5 Replies
View Related
Jan 11, 2007
I created two tables one is based on partition structure and one is non-partition structure.
File Groups= Jan,Feb.....Dec
Partition Functions='20060101','20060201'......'20061201'
I am using RIGHT Range in Partition function.
Then I defined partition scheme on partition function.
I have more than 7,00,000 data in my database.
I checked filegroups and count rows. It works fine.
But When I check the estimation plan time out for query it is same for both partition table and non partition table.
View 1 Replies
View Related
Jan 11, 2007
I created two tables one is based on partition structure and one is non-partition structure.
File Groups= Jan,Feb.....Dec
Partition Functions='20060101','20060201'......'20061201'
I am using RIGHT Range in Partition function.
Then I defined partition scheme on partition function.
I have more than 7,00,000 data in my database.
I checked filegroups and count rows. It works fine.
But When I check the estimation plan time out for query it is same for both partition table and non partition table.
View 1 Replies
View Related
Jun 25, 2004
We rebooted our main SQL Server (SQL2k). It had 5 linked servers setup, 2 on the same network and 3 on another network. After the reboot the linked sql servers on the other network only work some of the time when trying to access them via a linked server. The 2 linked servers on the same network have no problems at all. All the routes are setup properly and are persistant.
Linked servers
SameNetwork1 -- No Problems
SameNetwork2 -- No Problems
ServerOtherNetwork1 -- "sqlserver does not exist or access is denied. [SQLSTATE 42000] (Error 17)."
ServerOtherNetwork2 -- "sqlserver does not exist or access is denied. [SQLSTATE 42000] (Error 17)."
ServerOtherNetwork3 -- "sqlserver does not exist or access is denied. [SQLSTATE 42000] (Error 17)."
All SQL Server 2k Standard with 2 processor licenses and the latest service packs.
All have been setup as TCPIP and the port has been defined in the client network utility using the IP address not the name.
All servers can be "pinged" and you can pull up the physical drive folders from across the networks with no problems.
All linked servers are running windows 2000. The main server is running NT.
After the reboot only server1 was working with the linked server. The other 2 servers were getting either a timeout or "sqlserver does not exist or access is denied. [SQLSTATE 42000] (Error 17)."
I tried to fiddle with the linked servers by changing the passwords and got Server1 and Server2 working over the linked server connection but could not get server3 to work. After about 30 minutes or so I noticed that server 2 was working on and off. It was not consistant about failing or working. I decided to go home for the night. When I came in this morning, only Server2 works. Server1 and 3 do not at all.
I have a job setup to pull transactions from a table on server2 to our main sqlserver. This job was running every 5 minutes and it was failing here and there. I have now setup the job to run every 2 minutes and it has not failed for the past hour. When it does fail it gives the error above (server does not exist).
I really hope this makes sence to someone... lol :o
Thanks.
View 14 Replies
View Related
Jul 9, 2006
Hi Guys,
[VS2005, .NET 2.0 SQL Server Express]
I could really use some help with this one - as in desperately!:
I have a couple of database projects which run fine in Debug Mode from VS2005, but I can't get them to run outside of it.
The problem is that, for some reason, the login to the database file isn't succeeding. I'm getting the following errors:
.NET Project: System.Data.SqlClient.SqlException: Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.
DotNetNuke 4.0.3 Project: ERROR: Could not connect to database specified in connectionString for SqlDataProvider. (I sometimes get a "The Server Version does not match the Database Version" error)
Here are my connection strings for the above two scenarios:
<connectionStrings> <add name="EpgDbConnection" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=C:ProjectsEpgEpg.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True" providerName="System.Data.SqlClient" /></connectionStrings>
<connectionStrings> <add name="SiteSqlServer" connectionString="Data Source=.SQLExpress;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|Database.mdf;" providerName="System.Data.SqlClient" /></connectionStrings>
My assumption is that I need to switch from Integrated Security (is that Windows Security?) to SQL Server Security, but I'm battling to do that effectively.
A pointer to a simple step-by-step guide would be fantastic, but, if there isn't such a thing available, I have a number of specific questions:
Question 1: Why does one need to switch from Integrated Security to SQL Server Security?
If I'm right, I suspect that my connection strings will become:
<connectionStrings> <add name="SiteSqlServer" connectionString="Data Source= (local);user id=gary;password=password;Connect Timeout=30" />
and
<connectionStrings> <add name="EpgDbConnection" connectionString="Data Source=(local); user id=gary;password=password;Connect Timeout=30" />
respectively.
Question 2: Are these strings correct?
(I receive a login failed error)
Question 3: How do I go about changing the Security Setting, using either VS2005 or SQL Server?
(I've modified permissions at a SQL Server Express level from Windows Authentication mode to SQL Server and Windows Authentication mode in SSMS, but the Authentication Method can't seem to be changed at the database file level. View connection string (under database properties), reveals an unalterable "Windows Authentication" Authentication Method.)
Question 4: I've managed to add a user and password at the SQL Server Express level. What roles should the user have?
I'd be very grateful for any help you could give me. I've tried everything I can find or think of and am not getting anywhere!
Thanks very much.
Regards
Gary
View 2 Replies
View Related
Nov 26, 2013
I execute the code below
declare @LastDate datetime
SELECT @LastDate = max([LastUpdate])
FROM [exhibitor].[dbo].[blgBelongs]
WHERE (([Table1]=@module1 OR [Table2]=@module2 )or ([Table2]=@module1 OR [Table1]=@module2 )
AND Exists (SELECT [Table1],[Table1ID] FROM [exhibitor].[dbo].[blgBelongs] WHERE table2=30 and table2ID=@dmn_ID))
Before I see @LastDate , I see this warning
Warning: Null value is eliminated by an aggregate or other SET operation.
View 7 Replies
View Related
Jan 28, 2008
I have this simple full text search query that works perfectly on my own computer using sql server 2005 express, however, on the production server(shared hosting)when I added the first 50+ rows, the full text search works perfect, but as the number of rows increases, the full text search can only see the first50+ rows, but not the new ones. Is there any quick solution for this or it's just a common mistake for developers for not properly indexed columns?Is there a way to re-indexed all rows without loosing data on the live server? search query: SELECT TOP 50 *FROM li_BookmarksWHERE FREETEXT(Keywords,@Keywords)
View 2 Replies
View Related
Aug 1, 2007
I currently have a SQL Server cluster setup with a Primary DB Server SERVER1 and the Standby server SERVER2. SERVER1 has been failing more than normal is the past few weeks and its takes upto 5 mins for SERVER2 realize that SERVER1 is down. I am looking for a better way to implement a backup server on production with minimum downtime. Please adivse..
View 1 Replies
View Related
Feb 18, 2015
Production and development servers are on different domains and they do not trust each other. How do I import data from the table t1 from a database db1 in production and load it into table t1 inside database db1 in development?
View 3 Replies
View Related
Jul 20, 2005
Any help would be greatly appreciated.My problem is that I need to set up a backup SQL Server 2000 machinewhich can be used in case of a failure to my primary. All databases(30 as of now) must be an up to the minute exact copy of productionand include most recent changes in data as well as any structurechanges (Tables, Views, SP's, Triggers, Users . . etc).When I tried this using Transactional Replication, the replicationprocess gets fouled up once I introduce any kind of structure changesto the DB. I've considered the idea of doing periodic backups andrestoring it to my backup SQL server, but this does not give me theconcurrency needed with 0 latency.I've seen articles that recommend using Transaction Replication with'Scheduled Table Refresh', and also doing database dumps to restore onthe backup machine, but I have not been able to find any documentationregarding this to try out. How can I implement this type of backupstrategy in SQL 2000?
View 2 Replies
View Related
Apr 23, 2008
I would like to deploy several reports to production server, Do i need to install reporting services entire software in order to run the reports or is it possible to just have runtime files installed on it to run the reports.
please help, i have almost 100 reports to be deployed on this server which is located in other country.
Thanks for the helpful information.
(i am using SQL server 2005 / reporting services 2005.)
View 6 Replies
View Related
Dec 7, 2001
ok ok, stop laughing. for real, is there any programatic way of doing this? whom ever created this database i inherited (SQL 2000) created the LDF and DATA files on the same drive and in the same folder for that matter. just trying to do a little disaster magament.
thanks
matt
View 1 Replies
View Related
Oct 14, 1998
This feels like a silly question, but I`m going to ask it anyway...
I have limited SQL Server experience, but have run into a wall with a client`s Web/Access combination. I need to upgrade to SQL Server. I have Beta3 installed on a development box and am very happy with it. Is anyone running this thing in a production environment? This isn`t going to be experience huge loads, so I`m tempted. Tell me if I`m crazy for wanting to try it.
View 2 Replies
View Related
Jan 31, 2007
I have a brand new database server with system databases.
I need to copy like four production database from another server to this new server. Can i do restore of the last production backups and restore them on the new server without creating the empty databases on the new server.If any one has better approach i will appreciate
View 3 Replies
View Related
Apr 4, 2008
1.First issue
The port number already allocated was 2059. We have changed that into the default port 1433 in the node. Now we are able to connect the node from the client application. But we are not able to see the configuration manager in any of the environment now.
2. 2nd issue
Cluster Environment
Error while executing the Package The connection details are not loaded in the connection manager tag
The same error with advanced information.
Another error window
R.S.Moorthy
View 1 Replies
View Related
Nov 9, 2007
I have production server 2000. The server gets disconnected sometime by itself and sometime it is working fine.Sometimes it even doesn't get restarted. Is there any problem with service packs and some performance issues.
Can you SQL guru give me best suggestion and how should i proceed.
View 3 Replies
View Related
Jan 17, 2007
Hi,I ran test data on my development machine and it took 1 minute toinsert the data. Ran the same set of data on the server and took 5minutes.Check both database and everything is the same. I even copied theproduction DB on my machine and it was taking still about 1 minute.Look at the fragmentation, and all the numbers are better on the serverthan my development machine so it should be faster.In the application I put some timer and discover that the insert istaking 0.015 ms on the server and 0 on the development. So the problemis on the insert.It is a Web application using ASP.NET.Here are the spec of the computers:Development: P4 HT 3.2GHz 1gig memory running WIN XPServer: Xeon 2.8GHz 1.5Gig memory running WIN 2000 serverAny idea how I to pinpoint the problem? I'm not at the point ofthinking that it can be the hardware, but how to verify that?ThanksFrank
View 2 Replies
View Related
Jul 12, 2007
Hi,
Presently our Organisation is in process of Implementing SOX.
Under Compliance all the User have to be removed from the Production Server.
1.My Question is Do a DBA Should have Admin Privilages on the Production Server.
If yes then what are the Actitvies that a DBA has to perform only if DBA have admin privilages.
Regards
Sufian
View 2 Replies
View Related
Nov 6, 2007
Like most enterprise there is the database administrator (dba) and there are the developers(dev). The dba are conservative while the developers are also exploring their options.
One of the current usage I'm experimenting on is to provide data visualization - image for the data. Like most I needed to "create" the System.Drawing assembly in the database, marking it unsafe.
During my testing, my code had some exception and that brought down SQL Server.
I read that the CLR is better compare to the sp_OAs as well as the extended stored procedures written in C++ because it isolates the execution in a separate app domain and termination is clean - in case of any errors, it should not bring down SQL Server.
Also I read marking assembly unsafe void these benefits of isolation.
Instead of having to manage the situation where it involves code review by the dba and asking the dba to take some risk, is there a technique where all CLR code that runs in the production server does not pose stability issues.
View 1 Replies
View Related
May 27, 2007
How can I apply SP2 on running production server if SP resets all protocols (from TCP/IP enabled on production server to Shared Memory enabled) without disrupting its connections and thereafter its work?
View 1 Replies
View Related
Aug 10, 2007
Howdy;
I've tried this in the 'tools' area, but that didn't work too well. I suspect, I will have to generate a T-SQL code then schedule it as a job. Why I can't just drag and drop with basic desires, is beyond me, but THAT probably does exist.
anyway here is the problem
[this server has many databases, on SQL 2000 sp2]
1. User only wants me to use Monday morning's full backup, which is good in that it doesn't include transaction logs.
2. Restore that data overtop/into Developement db. = good, no data to worry about damaging.
3. User does NOT want me to do this by hand, but schedule it.
ok,
a. must do a RESTORE WITH FILELISTONLY from [?] what ?, master? and if I user the *.bak of the production, it has
a coded date field in the name entry SO, I would, I guess, have to generate all sorts of wonderful code to find the date and build a file name. Why, because using the FROM DISK = 'F:MSSQLBACKUPDBPRODUCTION_yyyyddmm.BAK' is not going to work with a wild card.
Can I do a file lookup using a 'PRODUCTION' prefix into a variable, then use that or should I look for latest file date [remember there are several database backups here], or ????
then. How does one schedule such a T-SQL. Do I save it to some text file, and invoke it using a job scheduler.
any help appreciated.
IS there an easier way.
rik
View 5 Replies
View Related
Jan 27, 2007
Per the subject line :Is SQL Server Express designed for production use ?
View 11 Replies
View Related
Jan 29, 2007
Group,
I have an ASP.NET web application using the default ASPNETDB that I did in VWD 2005, and deployed to a production server (IIS).
I need to make a table addition to the production database (ASPNETDB). I'm trying to use SQL Management Studio to attache the database, but it keeps saying the database is locked by a process. I shut down all the services that would likely be using it, but it's still showing locked. What do I need to shut down to attach the database to make the change?
Or is there a better way to do it?
View 1 Replies
View Related
Jun 4, 2008
Hey everybody!
I'm getting a pretty annoying error on my production server when i want to run an app ..
Error Msg (german): SqlDateTime-Überlauf; muss zwischen 1/1/1753 12:00:00 AM und 12/31/9999 11:59:59 PM liegen.
Funny thing is that on my client development machine i'm not getting an error at all. The DateTimes I use (C# and SQL Server) are dd/mm/yyyy hh:mm:ss formatted. I also don't write to the databse - only read!
Anyone familiar with this issue?
Thanks in advance & best regards!
View 2 Replies
View Related
Sep 17, 2001
I am in a deep shit with this...its my life and survival...
How to configure a linked server for oracle.
My oracle instance name is dbserver.internal.ca.com and how do i query since it gives me an error when i use the above name i had this come up some time early.
what information do i need to create a linked server for orale on sql server either using ole-db or odbc.
One more question is there a way where i can import data from oracle to sql server with having a oracle client installed on the machine where sql server exists.
Thanks,
Tina
View 1 Replies
View Related
Feb 15, 2001
hi,
What is the right procedure for shutdown a production web sqlserver for maintenace purpose. Checking users by sp_who?
Any other monitoring for current connections? lockings?
thanks
ram
View 4 Replies
View Related
Jun 29, 2000
IN ONE OF THE ADVERTISEMENT I SAW, IT SAYS "WE NEED PRODUCTION SUPPORT EXPERIENCE". AND MINIMUM CRITERIA IS, HE/SHE SHOULD BE A SQL SERVER DBA.
My Question is, what do we mean by "PRODUCTION SUPPORT IN SQL SERVER".
Can anyone give a good explantion on that, please?. Thanks in advance.
Srinivasan.
View 1 Replies
View Related
May 17, 2006
I have a 3rd party app which gets the error below on my sql 2000 box. I can't change the app or the db, so I am going to need to tweak sql to make the plane fly. The box is a new dell quad attached to a SAN. Runs on Win 2003 with 16 gig of RAM. 450 concurrent users. Anyone think more memory would help? I have the locks set to default. Are locks more of a memory killer, or CPU?
Error: 1204, Severity: 19, State: 1
The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
View 3 Replies
View Related
Jan 13, 2014
We have SQl Server in the production with dozens databases that reside on a SAN in the RAID 1 configuration with SAS 10K disks. Few databases have a huge overload on the SAN (more writes than reads). As there are some performance issues we are considering new SAN.
Is there any way we can get average IOPs in this server? I tried to use PerMon but without success. I don't know if I can chose "Total" for Logical drives in PerMon for the counters? I read dozens of pages: use sqlio, take this counters, use that, but no straight way to calculate. I know that SAN could have the relevant info for IOPs but our old one doen't provide it.
how can I calculate it (without use these links or use this counters with no calculation procedure in the end)
View 7 Replies
View Related
Jun 24, 2008
Dear All,
i'm planning to drop all the non clustered indexes (as they are not congigured well) on production database, and run the latest script to create fresh non clustered indexes on specific columns.
now my doubts are
1)will the replication affect with dropping and recreating of indexes?
2)query to drop all the non clustered indexes on that database....
can i use the query delete from sysindexes where indid>1
will the query works for me to drop all the non clustered indexes?
3)is it necessary to generate a snapshot again after creating the new indexes? or can i drop and run at subscriber also?
please guide me in this regard
Arnav
Even you learn 1%, Learn it with 100% confidence.
View 3 Replies
View Related
Oct 16, 2007
Hello there,
I have created a report with MySQL using ODBC connection.It works on my localhost server.then when i deployed it on the production server i get error saying.i checked my data source connection to my localhost and both have the same setting.what could be wrong.please help i am very desperate to make this work.thanks.
An error has occurred during report processing.
Cannot create a connection to data source 'Payment'.
For more information about this error navigate to the report server on the local server machine, or enable remote errors
View 2 Replies
View Related
