hello,
i am getting a problem
suddenly the DB in Sql2000 has slowed down drastically
and when i have checked the logs the error is
Supersocket info:[spn register]:error 1355
pls. help me
pavan
Hi,
Recently we have migrated our application from MSDE 2000 to SQL Server Express 2005(SP 1). This has significantly reduced the performance of our Windows.NET application which is developed using C#.
For example : While logging in to the application two databases are being attached.
Time taken in MSDE: 16 secs
Time taken in SQL Server Express 2005 : 58 secs
Also note performance is degraded for normal screens where data is retrived from database using inline queries.
Questions:
1) Is there any special(optimum) configuration(installation parameters) while installing the SQL Server Express 2005 setup?
2) Is there any query optimization to be done w.r.t SQL Server Express 2005 ?
We highly appreciate any help towards resolving the above problem.
Regards,
Sasi
Hi,
We are processing 60,00, 000 rows(2 GB file) available in a flat file and loading them in to a database tables using OLEDB Destination components. In the data pipeline of an SSIS package we have 1 flat file source reader, 7 look up components(full cache mode), 1 multicast component and 2 OLE DB destinations with fast load option.
We have observed that first 10,00, 000 rows are processed and loaded in to target tables in just 4 minutes time. The second set of 10,00, 000 rows are processed in 15 minutes time. After this for processing each 1,00,000 rows SSIS is taking approximately 8 - 10 minutes time. We are not able to identify the reasons for the unexpected behaviour of SSIS.
We thought that as the input file size is 2 GB SSIS is not able to manage and slowing down over time of execution. We did split the big input file in to 60 small 37 MB (approx) size files. Then we modified the package by adding For-Each loop task to process all the 60 small files and load them in to database server sequentially. Even in this approach also we have identified data loading has slowed down drastically after processing 13 files.
In order to verify is there any problem with reading source file or transformation, we have replaced OLEDB destinations component with Flat File destinations. With Flat file destination the time taken for processing rows is very constant. For every 8 minutes package is able to process 10,00,000 rows and write them in to the destination files. So, there is no problem with the with either Look up components or flat file source reader.
We are sure that target database server is in same state/condition from the starting to the end of package execution. The client box in which we are running the package is having 1 GB RAM. During package execution time the CPU usage is at 30 % and PF usage is 580 MB. SP1 is also installed on both Client and Server.
Does any one have clue what is causing slow down of data load over the time of package execution?
I'm running into an issue when developing a clr project. I open the project to make changes and apparently it hoses up the sql server that I'm connecting to. I'm opening the CLR Project from a mapped drive on another computer. I can't figure out why just opening up the project causes the slow down. Any ideas.
Thanks,
Josh
We have a process here where a ~45Gb is copied from DB1 to DB2 (both on the same server) using the Export Data wizard and choosing the "Transfer objects and data between SQL Server 7.0 databases" - All the defaults are left as is.
Under 6.5 this method took around 24 - 26 hours, now with SQL 7.0 (SP3) it takes about 16 hours. That's great execpt towards the end of the copy it seems to be hogging most of the server resources - although the processors aren't mazed out.
Users of the other databases are having queries take 5+ mins when they normally come back in < 5 seconds. As soon as the copy finishes there query time return to 'normal'.
Anyone any ideas on where to start looking or how to analyse this problem.
Thanks in advance.
Rob Elmes
hi guys,
the following test script works fine and displays a list of cars from the fairly small database, but if I specify the sort order in the querystring, the page takes ages to display and usually times out. Can someone look over it please and tell me where I can fine-tune it for performance or redundant code?
thanks
M
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<%
Dim oRS,oConn,myOrder,strSQL
Set oRS = Server.CreateObject("ADODB.Recordset")
Set oConn = Server.CreateObject("ADODB.Connection")
'next, a couple of test lines to prevent timeout (seems to have no effect)
oConn.CommandTimeout = 0
Server.ScriptTimeout = 0
Set strOrder = Request.QueryString("Order")
oConn.ConnectionString = "Provider=MSDASQL;DRIVER=SQL Server;SERVER=address;UID=userID;PWD=password;DATA BASE=name"
oConn.Open
%>
</head>
<body>
<%
strSQL = "Select make,model,price from vehicles where cat = 'car' AND active = 'yes'"
if strOrder <> "" then
strSQL = strSQL & " ORDER BY " & strOrder
end if
oRS.Open strSQL, oConn, 2, 3
oRS.moveFirst
Do while not oRS.eof
make = oRS("make")
model = oRS("model")
price = oRS("price")
%>
<%=make%> <%=model%> <%=price%><BR>
<%
oRS.MoveNext
loop
oRS.close
set oRS= nothing
oConn.close
set oConn=nothing
%>
</body>
</html>
I've been doing some experiments with speeding up copying tables ofapproximately 1 million rows between databases using BCP and BULK INSERT.I noticed that the total time for removing the indexes (non-clustered) andthen recreating them after the BULK INSERT was significantly less than justdoing the BULK INSERT with the indexes left there, even though I specifiedTABLOCK.I would have expected SQL Server not to update the index until the insertcompleted (given the table lock) and so removing the indexes would have noeffect. Can anyone explain why removing the indexes should speed it up?This is on SQL Server 7.CheersDave
View 2 Replies View RelatedWe're having problems with our SQL 2000 SP3 standard server (on Win 2k3). Our quasi-data-warehouse application does data processing on feeds of approximately 7 million records. Once the data is loaded in the queries against that data and updates against large tables will often wait with PAGEIOLATCH_SH contention. To give an example, over 7 million rows I was testing something out in development and issued: UPDATE <Table> SET <VarcharColumn> = null, which took forever (over an hour) with the PAGEIOLATCH problem....meanwhile someone else using another database was completely blocked from making an update during that time. It seems like something is very wrong.
The server has 4 drives spinning at 15k rpm with some sort of a high end raid controller, so I'm sure it's not a slow i/o subsystem.
Has anyone experienced this behavior before? Is this an issue that's resolved in 2000 SP4?
Thanks in advance,
-Mark
Hello there,
I have a SQL Server 2000 with a dozen of databases. The databases are rather small (all sum up to 10 GB).
The entire server gets extremelly slow from time to time (lasting a few days when it happens and suddently coming back to normality). A profiler trace doesn't show anything strange (besides a lot of SQL Agent entries).
I pretty much tried to isolate every single application that makes use of the databases in that server and see if it was the cause of the problem, but I couldn't find any correlation.
I know the computer where this server runs is quite fragmented. Is there any way I could make sure this is the cause of my performance issues?
I don't know if this might help, but once the server simply went down for some 3 hours, and I wasn't able to bring it up in any way. It eventually started working again by itself. The only thing I did in the meantime was to run DBCC CHECKDB a few times, always getting the response "No errors found on the database".
Any hint on that?
Background
SQL Server 2005 Standard 9.0.1399 64bit
Windows 2003 64-bit
8gb RAM
RAID-1 70gb HD 15K SCSI (Log Files, OS)
RAID-10 1.08TB HD 10K SCSI (Data Files)
Runs aproximately _Total 800 Transaction/Second
We deliver aproximately 70-80 million ad views / day
8 Clustered Windows 2003 32-bit OS IIS Servers running Asp.net 2.0 websites
All 8 servers talking to the one SQL server via a private network (server backbone).
In SQL Server Profiler, I see the following SQL statements with durations of 2000 - 7000:
select top 1 keywordID, keyword, hits, photo, feed from dbo.XXXX where hits > 0 order by hits
and
UPDATE XXXX SET hits=1906342 WHERE keywordID = 7;
Where the hits number is incremented by one each time that is selcted for that keyword ID.
Sometimes these happen so frequently the server stops accepting new connectinos, and I have to restart the SQL server or reboot.
Any ideas on why this is happening?
Regards,
Joe
Hello:
Interestingly enough, I haven't come across this before. I have a SQL stored procedure which takes four parameters; periodstartdate (datetime), periodenddate (end time), hsgradyearstart (int), hsgradyearend (int)
[dbo].[CalculateActivityTotal]
-- Add the parameters for the stored procedure here
@periodstartdate datetime = '2007-01-01',
@periodenddate datetime = '2007-01-08',
@hsgradyearstart int = 1900,
@hsgradyearend int = 2007
AS...
If I run the stored procedure and pass the parameters using EXEC or
sp_executesql "CalculateActivityTotal '2008-01-04 12:00:00', '2008-01-11 12:00:00', 1900, 2008"
the stored proc takes well over ten minutes to run (it does a bunch of aggregation). If I modify the stored procedure to take no parameters, however, and I hardcode the dates in the stored proc using declare and set then it runs in 13 seconds. What could be causing my problem and how I can I go about resolving this? I need to pass the parameters via reporting server. Thanks!
Hello,
We are using SQl as the DB for our application. Now we lost direct acess to the DB, but we can acess through the application.
How we will get the direct acess back? The network side is ok.
We don't know how it happened. can anybody help me to resolve this?
The server being used is a Intel Xeon E5310 Clovertown 1.6GHz 2 x 4MB L2 Cache Socket 771 80W Quad-Core 2U Passive Processor.
The problem is that this server is slowing down everytime about 1000 users log into a forum which the server is running. I think that the server should be able to handle this many users with no problems but I am not sure if that is the case.. The problem is probably something to do with the SQL of the server I am guessing. The server is not mine but I want to help the owner of the server as well as the users who are trying to access this forum but cant because of this server issue. If I was able to get the SQL would I be able to fix this problem? I doubt you need this but the server url is www.smashboards.com
I am fairly new to servers and have never really set one up myself yet. Forgive me for my lack of knowledge about them.
-Thank you!
Hello.
Im trying to insert data in a sql-table using a SP.
But it wont work!
here's my connectionstring (established in Page_Load()):
sqlconCon = new SqlConnection("server=localhost;database=dbJonas;Trusted_Connection=Yes");
and it executed like this (in a OnServerClick-eventhandler):
SqlCommand sqlcmdInsertNewsArticle = new SqlCommand(insertCmd, sqlconCon);
...
filling with parameters
...
sqlcmdInsertNewsArticle.Connection.Open();
The error I get point to the Connection.Open()-line and says :
System.Data.SqlClient.SqlException: SQL Server does not exist or access denied.
does anyone know what I may have done wrong?
I have other connections to the server that works fine (Select-statements though)
anyone? thankful for tips...
Is it possible to access a file (for example, to write in a text file)with transact-sql?.I've create a stored procedure and I'd like to write in a log filesome traces depends on the result of the statements executed in thisstored procedure.Thanks
View 1 Replies View Relatedif (!Page.IsPostBack)
{ if (Session["users"] != null && (Session[flag"] == "true"))
{
String IP = Request.ServerVariables["remote_host"].ToString();
String Datee = DateTime.Now.Date.ToString();
OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source="
+ HttpContext.Current.Server.MapPath("~/App_Data/result.mdb"));
OleDbCommand cmd = new OleDbCommand("Insert Into KullaniciSayisi (IP,Datee) Values (IPDatee)", con);
cmd.Parameters.AddWithValue("IP", IP);
cmd.Parameters.AddWithValue("Datee", Datee);
con.Open();
intresultt = cmd.ExecuteNonQuery();
con.Close();
Session["flag"] = "false";
}
}
I want to convert this code to SQL sever . However I can not do it? Can you help me?
I am using Vista Business with Visual Web Developer and Sql Server Express 2005. I want to be able to access the data from two tables in a query. Both tables are attached to the application.
To refer to the two databases I have used the following
DATABASE.OWNER.TABLE (specifically TaskMgr.dbo.TaskTable)
For both databases I get the error "Invalid object name"
What am I missing?
Thanks
hi
i was reading book about asp.net and i found example for a dataacess class but i didn't understand this part
Private m_FieldData As New NameObjectCollection
Private _m_ConnectionString As String
Private m_dbConnection As SqlConnection
Private Sub AddParameters( _
ByVal objCommand As SqlCommand, _
ByVal objValues() As Object)
Dim objValue As Object
Dim I As Integer
Dim objParameter As SqlParameter
objCommand.Parameters.Clear()
SqlCommandBuilder.DeriveParameters(objCommand)
I = 0
For Each objParameter In objCommand.Parameters
If objParameter.Direction = ParameterDirection.Input _
Or objParameter.Direction = _
ParameterDirection.InputOutput Then
objValue = objValues(I)
objParameter.Value = objValue
I = I + 1
End If
Next
End Sub
Private Sub AddFieldParameters _
(ByVal objCommand As SqlCommand)
Dim objParameter As SqlParameter
objCommand.Parameters.Clear()
SqlCommandBuilder.DeriveParameters(objCommand)
For Each objParameter In objCommand.Parameters
objParameter.Value = _
_FieldData.Item(objParameter.ParameterName. _
Substring(1))
Next
End Sub
Public Function ExecDataReader _
(ByVal strStoredProc As String, _
ByVal ParamArray objValues() As Object) _
As SqlDataReader
Dim objCommand As SqlCommand
Dim objReader As SqlDataReader
objCommand = New SqlCommand
objCommand.CommandText = strStoredProc
objCommand.CommandType = CommandType.StoredProcedure
objCommand.Connection = dbConnection
Try
objCommand.Connection.Open()
If (objValues.Length = 0) Then
AddFieldParameters(objCommand)
Else
AddParameters(objCommand, objValues)
End If
objReader = objCommand. _
ExecuteReader(CommandBehavior.CloseConnection)
Catch ex As Exception
If objCommand.Connection.State.Open Then
objCommand.Connection.Close()
End If
End Try
Return objReader
End Function
can anybody help me what the author want to do
Using Web Matrix I made and tested a query using the Sql wizard acessing a SQL server table and it worked fine.
Web Matrix created the following connection string, in the function to create the query :
"server='(local)'; trusted_connection=true; database='AVAL_360'"
But when the function created to execute the same query,my application then the following error showed up.
Login failed for user
What is the matter ?
Can someone help me ?
Thanks in advance
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
1) I have two PC. PC A and PC B.
2) I have one Access database in PC A in the folder of C:TEMP
3) I have a website done using a visual studio in PC B.
Question is how do I connect the website in PC B to the Acess database (C:TEMPdata.mdb) in PC A??
Thanks in advance. really need some help cos I have really no idea to that, i'm really new.
If possible,give me in steps
Hello,I am unable to access SQL Server 2005 (Express Edition) when i am creating wesites on localhost.But the same database can be accessed without any problem when websites are created on local file system.When creating the website on localhost and trying to access databse the following error is displayed: "
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."Anyone plz help me out of this? Thanks.Ashu.
HI all,
I currently have an issue trying to access my database in SQLexpress2005. Whenever i try to start the service it errors out. I looked at the error in event viewer and it says says
"The log scan number passed out to log scan in database 'master' is not valid. This error my indicate data corruption or that the ldf does not match the mdf....."
How can i fix this problem seing as though i can not access studio manager to restore the db?
Brad
hi. i got a question regarding the permission on share folder.
i wonder i am able to do this as below..
if on runtime, after i export the data to xmlfile and i want it to auto save into the SHARE Folder in sql server. can i do that ?
** after press a button, auto export data to xml file, then auto save the xml file to share folder.
Hi,
We have a MS Access .adp app which is having some security problems. We have a windows global group which is a user - which is added to a role. The developers are having problems with unqualified sql statements trying to find user.table instead of dbo.table. It seems we are not able to specify a default schema for the global 'user' - the option is greyed out and a sql statement fails. MS says:
Default Schemas
A default schema is used to resolve the names of securables that are referred to without their fully qualified names. In SQL Server 2000, name resolution first checks the schema owned by the calling database user, followed by the schema owned by dbo.
In SQL Server 2005, each user has a default schema. The default schema can be set and changed by using the DEFAULT_SCHEMA option of CREATE USER or ALTER USER. If DEFAULT_SCHEMA is left undefined, the database user will have dbo as its default schema.
Is the default truly undefined in a global group user? We are not totally locked in to the global group solution, but it does make admin easier for us.
Thanks a bunch!
Hello,
I mistakenly deleted my source table data.
Now i want to again migrate the data from MS Aceess table to the SQL server table.
Please tell me
As far as i remember I used some export or import command.
Thank You
please dont send me link...i have to do it now so no time to read
Hi
We have a demo version of our SQL2000 application that uses MSDE. This has always worked sucessfully in the past.
However, at the most recent installation however there is a problem which I cannot seem to fix.
MDSE was installed on Windows 2000 using the Local Admin account.
When the user logs on to the PC, SQL will not start gives error message '5 Access Denied'.
I have given the local user read/write permission to the Data directory and mdf/ldf files that it contains.
I have tried starting SQL specifiying it to log on using the Local Admin account but it makes no difference.
Does anyone know what else can I do?
Thank you
Mark
Hi,
I'm using ComponentArt's Callback grids with Manual Paging.
The CA example grid uses Access:(http://www.componentart.com/webui/demos/demos_control-specific/grid/programming/manual_paging/WebForm1.aspx)
That SQL syntax produced is invalid in SQL Server 2005.
Example:
"SELECT TOP " & Grid1.PageSize & " * FROM (SELECT TOP " & ((Grid1.CurrentPageIndex + 1) * Grid1.PageSize) & " * FROM Posts ORDER BY " & sSortColumn & " " & sSortOrderRev & ", " & sKeyColumn & " " & sSortOrderRev & ") ORDER BY " & sSortColumn & " " & sSortOrder & ", " & sKeyColumn & " " & sSortOrder
So...This is what I have (simplified), and it appears return incorrect rows on the last few pages:
SELECT top 15 * FROM Posts where & sFilterString & " and Postid in (SELECT TOP " & ((Grid1.CurrentPageIndex + 1) * Grid1.PageSize) & " Postid FROM Posts where " & sFilterString & " ORDER BY " & sSortColumn & " " & sSortOrder & ") " & " ORDER BY " & sSortColumn & " " & sSortOrderRev
What other approaches has anyone used besides the "ID in (...)"?The examples I have included show the available variables: sort asc and desc, current page, number of rows on a page, etc.
Hi everybody,
I have a query slower in SQL Server 2005 than in SQL Server 2000. I have a database in SQL 2000, I put it on the same server, but with SQL 2005 and the query take 5 seconds instead of 0 seconds. The DB compatibility is SQL Server 2000 (I tried with 2005 and result is the same). Execution plan seems right and I tried to change some DB options without results. It is weird, when I remove left join on MandatsEx, it take 2 seconds. The view currentEmployeeLevelHistoric returns 45 000 rows and mandatsex has 0 rows.
Here is the sample:
Select ls.EmployeNiveau.pk_EmployeNiveauID as NoNiveau,
IsNull(nullif(ls.Traduction.Description, ''), ls.TraductionDefaut.Description) + ' (' +
ls.currentLevelHIstoric.NoNiveau + ')' As Nom,
ls.currentEmployeeLevelHistoric.DebAssign As DateAssignationDebut,
ls.EmployeNiveau.assignmentReason As AssignmentReason,
ls.currentEmployeeLevelHistoric.FinAssign As DateAssignationFin,
ls.MandatsEx.noDossier, ls.MandatsEx.pk_MandatID,
'' As Period, ls.currentEmployeeLevelHistoric.NiveauPrincipal,
ls.currentEmployeeLevelHistoric.pk_emplNiveauHisto_Id,
ls.EmployeNiveau.fk_NiveauID_Niveaux, ls.EmployeNiveau.No_Ent_leg,
IsNull(nullif(TradPere.Description, ''), TradDefautPere.Description) + ' (' + NiveauPere.NoNiveau
+ ')' As NomSup,
ls.EmployeNiveau.No_Ent_leg + ls.EmployeNiveau.no_divisio + ls.EmployeNiveau.no_sec_eco +
ls.EmployeNiveau.no_etabli + ls.EmployeNiveau.no_mat as employeeScope,
case when ls.mandatExternalisation.fk_mandatID_MandatsEx is null then 2 else 1 end as
ContractCategory
From ls.currentEmployeeLevelHistoric
Inner Join ls.EmployeNiveau on
ls.currentEmployeeLevelHistoric.pk_EmployeNiveauID = ls.EmployeNiveau.pk_EmployeNiveauID
Inner join ls.currentLevelHistoric On
ls.EmployeNiveau.fk_NiveauID_Niveaux = ls.currentLevelHistoric.fk_niveauID_niveaux
Left Outer Join ls.TraductionDefaut On
ls.currentLevelHIstoric.fk_TraductionID_TraductionDefaut = ls.TraductionDefaut.pk_NoTraduction
Left Outer Join ls.Traduction On
ls.TraductionDefaut.pk_NoTraduction = ls.Traduction.No_Traduction and
ls.Traduction.Langue = 1
Left Join ls.MandatsEx on
ls.EmployeNiveau.fk_MandatID_MandatsEx = ls.MandatsEx.pk_MandatID
Left Join ls.mandatExternalisation on
ls.MandatsEx.pk_MandatID = ls.mandatExternalisation.fk_mandatID_MandatsEx
left Join ls.Niveaux as NiveauPere on
NiveauPere.niveauID = ls.currentLevelHIstoric.supId
Left Outer Join ls.TraductionDefaut As TradDefautPere On
NiveauPere.fk_TraductionID_TraductionDefaut = TradDefautPere.pk_NoTraduction
Left Outer Join ls.Traduction As TradPere On
TradDefautPere.pk_NoTraduction = TradPere.No_Traduction and
TradPere.Langue = 1
Where ls.EmployeNiveau.fk_EmploID_Emplos = 345158 and (convert(varchar,
ls.currentEmployeeLevelHistoric.DebAssign, 112) <= '20060802' and
(ls.currentEmployeeLevelHistoric.FinAssign is null or
convert(varchar, ls.currentEmployeeLevelHistoric.FinAssign, 112) >= '20060802'))
Thank you
Steve Gadoury
I am using the following query to export data from sql server to ms access in export data wizard:
SELECT * FROM myView where myID = 123
Order by varcharColumnName1,varcharColumnName2 ,intColumnName3
This query will fetch about 7, 00,000 records.
SQL server 2005 shows the correct order, but Data in access table shows Incorrect data.
Please give me the solutions.