Sql2005 Transaction Become Very Slow
Mar 10, 2008
Hello,
I've migrated an DB from SQL2000 to SQL2005, all it's working, but i've a "strange" problem, during the night, the transactions become very slow (no specific load) and if i restart service or I change the Degree of parallelims or Cost Threshold for parallelims to another value, it's solve the problem.
In the server logs (W2003) and SQL logs I can't see an warning or error...
In the old SQL 2000 server i've no problem...
If anybody can help me .. :-)
View 3 Replies
ADVERTISEMENT
Dec 27, 2005
I just upgraded my SQL 2000 server to SQL2005. I forked out all that money, and now it takes 4~5 seconds for a webpage to load. You can see for yourself. It's pathetic. When I ran SQL2000, i was getting instant results on any webpage. I can't find any tool to optimize the tables or databases. And when I used caused SQL Server to use 100% cpu and 500+MB of ram. I can't have this.Can anyone give me some tips as to why SQL 2005 is so slow?
View 3 Replies
View Related
Oct 10, 2005
Error returned when trying to commit the transaction to a database that is a replication distributor. (sql2005 ctp16)
View 10 Replies
View Related
Feb 19, 2008
Hi,We have shifted one of our data processing to a new instance of SQL2005. For this a new DB was created in SQL2005 and it is not anupgrade of SQL200 DB.The data processing application is a VB6 application that runs a batchprocess to insert about4.5 million records everyday in this SQL 2005 database. I am usingADO(ver 2.6) Connection object to execute T-SQL insert statements toinsert the 4.5 mill. records.This same application used to insert the data in the SQL 2000databaseand used to take about 2.5 hours to do so. After moving it to SQL2005 the performance has really detriorated and it now takes morethan6 hours.Is there any configuration change that I need to do in SQL 2005?Please help.Note: There is also an instance of SQL 2000 running in the sameserver.TIA,Tawfiq
View 3 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
Jan 14, 2008
Hi
I have the following problem (on 2 PC's now)
On 1 pc i installed VS2008 besides VS2005 and SQL 2005 (before i installed VS2008 everything was fine)
On the other is a fresh installed XP machine with only VS2008 SQL2550 and Office2007
Latets service packs are installed
On both machines when i open a table in SQL 2005 with the 'open table' command the show of the table is very slow.
Every row is updated file by field, the grid is not showing, the row and column headers are not 'grey' as normal.
Scrollign is impossible as the screen updates start again that slow.
Even a small table with 10 rows and 15 field behaves this slow. Its not workable.
What can be done to resove this???
Peter
View 11 Replies
View Related
Dec 7, 2007
Hi,I wondered in anyone can help with the following problem that i'mexperiencing, i'll try to provide as much info as possible and anysuggestions would be appreciated.I have just started at an organsiation and there seems to be slowperformance maybe on the san on a 64bit itanium dual core machine. 4CPUs are being showed to sql server, it also has 16gb of RAM. I'llstart with the configuration of the SAN.After speaking to the SAN guy, rather than carve the SAN up intodifferent area's for san Logs/Data etc they have gone for the approachof spreading a Vdisk across as many spindles as possible (All 145 ofthem). So the area that is presented to the SQL Server according thethe SAN guys is a vraid 5 stripe made up of all 145 disks which areall 72gb fibre-channel disks.This storage is not just made available to sql server but also madeavailable to other apps as well that need storage. Having read themanufactres best practice on setting this up there is a valid argumentfor doing this.The bandwidth from the SAN is 2Gb fibre, with each computer that usesthe SAN having 2Gb fibre cards.Clearly, that could act as a bottle-neck. But, there's nothing thatcan be done about it according to the SAN guy.Needless to say, any changes on the SAN are pretty much going to beout of the question as far as he's concerned but i think performanceisn't that good for the type of box they have and the SAN its attachedto.The 2nd thing i'll explain is the setup of the database in question,firstly whoever set it up split the database into 16 different file of4 filegroups so the table that i'm selecting to is in one filegroupsplit over 4 files and the the table selecting from is in anotherfilegroup made up of another 4 files. These are placed on the samephysical disk made up of the SAN LUN with 145 spindles.Anyway when i do a select from a sales table which has various groupbys and then insert the results into a blank table with no indexes itcan take over 2hours for 200k rows which i find very slow.When i look at the sysprocesses table i am getting various waits asfollows :-72427200x0042900PAGEIOLATCH_SH 6:9:219209472427200x00690SLEEP_TASK72427200x00000SOS_SCHEDULER_YIELDThe process seams to be going inbetween a PAGEIOLATCH andSOS_SCHEDULER_YIELD a few times per second.Running the following to get io stalls gives the following :-Select * from sys.dm_io_virtual_file_stats (6,7)Select * from sys.dm_io_virtual_file_stats (6,8)Select * from sys.dm_io_virtual_file_stats (6,9)Select * from sys.dm_io_virtual_file_stats (6,10)gives results like :-67170853985015624218246512844829457222526431245540454412438340307010565449074954240x0000000000000954It worries me that when the process is on the PAGEIOLATCH the waitcan be over 1000. Is it normal for the wait to be this long and whatwould be the best way to prove one way or another if the configurationof the san is causing this kind of performance???Thanks for any suggestions in advanceIan.
View 1 Replies
View Related
Jan 31, 2008
I have an application written in VB6 that creates a ADO connection using the (native SQL2005 clien)t from the client to SQLServer 2005 on Server 2003 configured as a stand alone server. The application works great on XP and has for a number of years.
Now I am attempting to deploy in Vista and using the same code the connection speed CRAWLS. it's in the magnitude of atleast 10 times slower. It eventualy works but the selects and doing a readnext against the resulting record set is at a snails pace.
What am I missing. It's has to be some sort of configuration problem somewhere.
View 2 Replies
View Related
Jul 16, 2007
Hi,
I have run a lot of insert/ delete, update queries on a database in sql 2005 for a couple of months
Is there a way to track when and what are sql transactions that are have been executed?
Thanks in advance for your time and help.
whitze
View 3 Replies
View Related
Oct 20, 2006
Are there any tips/techniques/issues when doing a begin tran and commit between a sql 2000 server/db and a sql 2005 server/db. Should you still use "set xact_abort on"? This will be a recordset of about 1-2000
View 1 Replies
View Related
Oct 19, 2006
Hey all,
Just went through migrating to SQL 2005 as well and we have a need to apply log shipping between SQL 2000 and SQL 2005. Well not shipping per say in the automated fashion that SQL 2005 offers but rather we need to apply SQL 2000 transaction logs to a SQL 2005 instance. Though the database is still in SQL 2000 version 80 and the secondary database needs to be available in read only. (STANDBY MODE)
When I attempt to restore either a FULL backup or transaction log I get the error:
RESTORE DATABASE is terminating abnormally. This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBY
The only way I could restore the FULL was by using the WITH RECOVERY OR NORECOVERY option during the restore process. The issue with this is I need the database in standby by mode in order to continue to append transaction logs.
Anybody have any thoughts how I might be able to get around this?
Thanks
Eric
View 6 Replies
View Related
Dec 19, 2006
HI,
We are migrating SQL2000 to SQL2005, we are using sql2005 jdbc driver 1.0.
I got this error "com.microsoft.sqlserver.jdbc.SQLServerException: Server failed to resume the transaction", when my application calls a stored procedure which is a transaction.
Does anyone know what could be the problem, how to fix it?
Thanks in advance.
View 5 Replies
View Related
Dec 23, 2004
Dear all viewer ,
Thanks to everyone.
We are using -
MSSQL Server 2000 on windows 2000 advanced server.
PB 7.0 for client server front end tools.
Prior to few days our application works fine, rightnow it get slow or not responding after few (4/5) transaction. We don't know wht it is, If anyone has same experience so that is helpfull for us.
Thanks
R.Mall
View 5 Replies
View Related
Sep 21, 2007
Hello,
We have log-shipping set up between a source and 3 destination SS 2000 databases. Two of the destination servers actually perform their log restores across the network from the other secondary server. This allows us to only copy the files once from a remote location. All three servers stay caught up within 15 minutes of each other.
Recently, I added a fourth server to this that has SS 2005 SP2 (X64). I wrote a stored procedure that restores log backups from the same single location as the maintenance plan jobs. The problem that I'm experiencing is that this fourth server is not keeping up with the other three. It seems to take longer to restore the same log backups. The destination servers are all on the same domain. This fourth server was previously part of the same maintenance plan configuration as the others prior to rebuilding it for SS 2005 SP2 (X64). During that time, it stayed caught up with the other servers. There is another database on the new server that I am log-shipping to in the same manner and it stays caught up, though, for the most part, the log backups are smaller. There is a file on the fourth server with a ckp extension for the database in question that doesn't seem to exist for the other databases on this server and the other servers.
Any information on this behavior would be appreciated.
View 1 Replies
View Related
Apr 11, 2006
I have some VB.NET code that starts a transaction and after that executes one by one a lot of queries. Somehow, when I take out the transaction part, my queries are getting executed in around 10 min. With the transaction in place it takes me more than 30 min on one query and then I get timeout.
I have checked sp_lock myprocessid and I've noticed there are a lot of exclusive locks on different objects. Using sp_who I could not see any deadlocks.
I even tried to set the isolation level to Read UNCOMMITED and still have the same problem.
As I said, once I execute my queries without being in a transaction everything works great.
Can you help me to find out the problem?
Thanks,
Laura
View 11 Replies
View Related
Dec 15, 2005
hi
i try to restore a bak file from another sql2005 server to my sql2005 server, but it show the error message as below :
TITLE: Microsoft SQL Server Management Studio Express
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)
------------------------------
ADDITIONAL INFORMATION:
Cannot open backup device 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupackup.bak'. Operating system error 5(error not found).
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3201)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=3201&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
pls some one can help me ???
thanks
chaus
View 62 Replies
View Related
Oct 12, 2007
Can you open/use a database created in SQL2005 in SQL2005 Express?
Thanks for the help!
Max
View 4 Replies
View Related
Jan 4, 2007
Hi
We have Sql2005 x64 bit standard edition server installed in windows 2003 64 bit editio server,
currently due to buisness requirements we need to have sql2005 x64 bit enterprise edition, please let me know how do i do the upgrade or change.
is it possible to retain all our custom settings in the standard edition after changing to enterprise edition.
This has to be done for our production and very critical, please help
Thanks
Samuel I
View 4 Replies
View Related
May 31, 2008
Hi All
I'm getting this when executing the code below. Going from W2K/SQL2k SP4 to XP/SQL2k SP4 over a dial-up link.
If I take away the begin tran and commit it works, but of course, if one statement fails I want a rollback. I'm executing this from a Delphi app, but I get the same from Qry Analyser.
I've tried both with and without the Set XACT . . ., and also tried with Set Implicit_Transactions off.
set XACT_ABORT ON
Begin distributed Tran
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.TRANSACTIONMAIN
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.TRANSACTIONMAIN
set REPFLAG = 0 where REPFLAG = 1 and DONE = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.WBENTRY
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.WBENTRY
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.FIXED
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.FIXED
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.ALTCHARGE
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.ALTCHARGE
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.TSAUDIT
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.TSAUDIT
set REPFLAG = 0 where REPFLAG = 1
COMMIT TRAN
It's got me stumped, so any ideas gratefully received.Thx
View 1 Replies
View Related
Feb 22, 2007
I have a design a SSIS Package for ETL Process. In my package i have to read the data from the tables and then insert into the another table of same structure.
for reading the data i have write the Dynamic TSQL based on some condition and based on that it is using 25 different function to populate the data into different 25 column. Tsql returning correct data and is working fine in Enterprise manager. But in my SSIS package it show me time out ERROR.
I have increase and decrease the time to catch the error but it is still there i have tried to set 0 for commandout Properties.
if i'm using the 0 for commandtime out then i'm getting the Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.
and
Failed to open a fastload rowset for "[dbo].[P@@#$%$%%%]". Check that the object exists in the database.
Please help me it's very urgent.
View 3 Replies
View Related
Feb 6, 2007
I am getting this error :Distributed transaction completed. Either enlist this session in a new
transaction or the NULL transaction. Description:
An unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the error and
where it originated in the code. Exception Details:
System.Data.OleDb.OleDbException: Distributed transaction completed. Either
enlist this session in a new transaction or the NULL transaction.have anybody idea?!
View 1 Replies
View Related
Dec 22, 2006
i have a sequence container in my my sequence container i have a script task for drop the existing tables. This seq. container connected to another seq. container. all these are in for each loop container when i run the package it's work fine for 1st looop but it gives me error for second execution.
Message is like this:
Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.
View 8 Replies
View Related
Jan 8, 2008
Hi,
i am getting this error "Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.".
my transations have been done using LINKED SERVER. when i manually call the store procedure from Server 1 it works but when i call it through Service broker it dosen't work and gives me this error.
Thanks in advance.
View 2 Replies
View Related
Sep 19, 2007
I am unable to install 32-bit SQL Server Integration Services on the server due to something that was left behind by the 64-bit version.
I've uninstalled SQL Server 2005 64-bit and when I try to install the 32-bit version of Integration Services, I get this error: "Failed to install and configure assemblies C:Program Files (x86)Microsoft SQL Server90DTSTasksMicrosoft.SqlServer.MSMQTask.dll in the COM+ catalog. Error: -2146233087 Error message: Unknown error 0x80131501 Error descrition: FATAL: Could not find component 'Microsoft.SqlServer.Dts.Task.MessageQueueTask.ServCompMQTask' we just installed."
I can't seem to figure out how to resolve this problem with the COM+ and I can't remember if Integration Services is required.
Can anybody please advise?
View 1 Replies
View Related
Jul 8, 2007
Hello,
I have a vb program that include a dts package that has been saved to vb with sql2000 dts wizard and works very good.
Now that I upgrade my website to sql2005, this vb dts package doesn't work.
The error I get is:
Microsoft Data Transformation Services (DTS) Package
Invalid STDGMEDIUM structure
(Microsoft Data Transformation Services (DTS) Package (80040066): Invalid STDGMEDIUM structure
) (Microsoft SQL-DMO (ODBC SQLState: 42000) (80004005): [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near ')'.)
I searched in the internet how to make dts package in sql2005 and save it to vb and found no information about it.
What Can I do to get the vb code of the dts package I create in sql2005 or how do i migrage the sql2000 vb dts package code to sql2005?
Thanks,
Kubyustus
View 4 Replies
View Related
Nov 13, 2006
I have some production boxes on Win 2000 32-bits OS and some production servers have been upgraded to Win2003 64-bit OS runing SQL Server 2005. There are also a number of Win2003 32-bit OS running SQL 2005.
The issue is that when linking the 64-bit production servers to the 32-bit boxes running SQL 2005 / Win 2003 OS, the linking seems to succeed, but I am unable to see a number of entries in sys.objects. Typically, these objects are User Stored Procedures.
Moreover, the linking seemed to have worked, but data extraction does not take place between the servers. However, there are no errors. The objects (user stored procs) exist on the 64-bit side, but linking does not actually happen.
Microsoft KB has addressed this in SQL 2000 case in this KB article, but has not suggested a solution for SQL 2005.
Any ideas? Has anyone else encountered this?
Thanks.
View 1 Replies
View Related
Jun 10, 2015
I have Full database backup upto previous day and transaction logfile of Today transaction. my database has crashed. I have restored previous day's Full backup. I have faced difficulty to restore today's transaction from today's transaction log. What are the steps to restore full database back and one day's transaction log file. Note: there is no differential database backup and transaction backup.
View 8 Replies
View Related
Jul 11, 2007
I want to list out the pending transaction for transaction replication by publication.
Help needed.
View 1 Replies
View Related
Nov 14, 2006
I'm receiving the below error when trying to implement Execute SQL Task.
"The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION." This error also happens on COMMIT as well and there is a preceding Execute SQL Task with BEGIN TRANSACTION tranname WITH MARK 'tran'
I know I can change the transaction option property from "supported" to "required" however I want to mark the transaction. I was copying the way Import/Export Wizard does it however I'm unable to figure out why it works and why mine doesn't work.
Anyone know of the reason?
View 1 Replies
View Related
Jul 27, 2015
I created a Calculated measure in cube something like this : ([TransType].[TransTypeHierarchy].[TransTypeCategoryParent].&[SPEND],[Measures].[Transaction Amount]). To get only spend transactions. Now, I want to slice this measure with same hierarchy to find the amount distribution across different transaction types under spend transaction. But this query behaving like the measure doesn't have relation with measure.
you can think this as below query:
WITH
MEMBER SPEND AS ([TransType].[TransTypeHierarchy].[TransTypeCategoryParent].&[SPEND],[Measures].[Transaction Amount])
SELECT NON EMPTY {SPEND} ON 0
,NON EMPTY ([TransType].[TransTypeHierarchy].[TransTypeCategoryParent]) ON 1
FROM [CUBE]
View 6 Replies
View Related
Jun 1, 2007
Hi
How do I make use of begin transaction and commit transaction in SSIS.
As am not able to commit changes due to certain update commands I want to explicitly write begin and commit statements. but when i make use of begin and commit in OLEDB commnad stage it throws an error as follows:
Hresult:0x80004005
descriptionyntax error or access violation.
its definately not an syntax error as i executed it in sql server. also when i use it in execute sql task out side the dataflow container it doesnt throw any error but still this task doesnt serve my purpose of saving/ commiting update chanages in the database.
Thanks,
Prashant
View 3 Replies
View Related
Jun 1, 2006
Hi,
I am having some problem with SSIS transaction. Eventhought I tried to imitate the concept that Jamie presented at http://www.sqlservercentral.com/columnists/jthomson/transactionsinsqlserver2005integrationservices.asp
. My workflow is as followed
*********************************
For Each ADO.Record in Oracle (transaction=not supported)
If (Certain_Field_Value = 'A')
Lookup Data in SQL DB with values from Oracle (transaction=not supported)
DO Sequence A (Start a Transaction , transaction=required)
INSERT/UPDATE some records in SQLDB(transaction=supported)
Finish Sequence A ( transaction should stop here)
UPDATE Oracle DB ( Execute SQLTask, transaction=not supported)
If (Certain_Field_Value = 'B')
Lookup Data in SQL DB with values from Oracle (transaction=not supported)
DO Sequence B (Start a Transaction , transaction = required)
INSERT/UPDATE some records in SQLDB (transaction=supported)
Finish Sequence A ( transaction should stop here)
UPDATE Oracle DB ( Execute SQLTask, transaction=not supported)
If (Certain_Field_Value = 'C')
------------
------------
End ForEach Loop
*************************************
My requirements are that I want separate transaction for each Sequence A, B, C, etc... If Sequence A transaction fails, the other should still be continuing with another transaction.
But I am getting an error regarding the OLEDB Error in next Task (e.g in Certain_Field_Value = 'B') "Lookup Data in SQL DB with values from Oracle ", the error message is ".......Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction. ".
What is it that I am doing wrong?
Regards
KyawAM
View 12 Replies
View Related
Nov 28, 2004
I'm getting this error:
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].
http://support.microsoft.com/kb/839279 this didn't help
any suggestions?
View 3 Replies
View Related
