I would like to capture about 20 rows from the sysperfinfo table every
30 secs on a production server. I am thinking of ways I can reduce the
disk (not network) I/O overhead of this process. Instead of reading
the table from a local SQL Agent job and writing to a local table, I am
wondering if I should create the job to capture the data from a remote
(less critical) server. The servers are connecting via a gigabit line
and are in the same server room on the same switch. This way the
production server would be required to do the reads but the other
server could take care of the I/O of the writes to the capture table.
Also, this job would run from 7am to 7pm non-stop. A waitfor delay of
30 seconds would control the twice a min scheduling. Running this on
the remote server would free up a few SQL Agent CPU cycles as well.
This would be one less job for the production server to worry about.
Thanks
I have been doing some testing with SQL Server 2000 using a packet sniffer, and have found that it is sending bytes with a value of 0x00 between each "valid" character. For example, if it was going to send "hello" over the network, it would be transmitted as "h.e.l.l.o".
Can anybody suggest why this might be. It happens regardless of what client is used - ADO.NET, osql.exe, etc. Is it something to do with the encoding used?
Everything does work fine and the data is received intact, but if these seemingly redundant bytes could be removed then it would increase performance by 100%.
I've been asked to put together an estimation for the performance impact that replication would have on our database server during a particular operation. I know that this depends on a lot of different factors, including:
* Number of articles being replicated * Types of articles being replicated * Number of DML transactions that would result in delivery of replicated data
When I execute this statement through ASP.NET select cntr_value FROM sysperfinfo I get this error, System.Data.SqlClient.SqlException: Invalid object name 'sysperfinfo'. Any ideas why?
I'm running SQL 2000 (SP3) on top of Windows NT 4.0 (SP6).
I have a couple of processes that look at the sysperfinfo table every day to collect some performance/metric type information. It works for all my other servers (mostly SQL 2K/Win2K) except for this one.
Why is the table on this one server empty?
I do have one other SQL 2K/Win NT server. The sysperfinfo table on that one is fine.
the only other clue/difference is that the server which has the empty table is a named instance install of SQL 2K.
I have DB monitoring jobs which use Sysperfinfo to monitor some of the counters. On One SQL 2K Server since few days the select on sysperfinfo returns 0.
Do you think I need to start any service from the OS side to enable this?
I'm using a performance monitor counter with an alert to catch merge replication conflicts. As you might have seen in my earlier psting this alert keeps running even if all conflicts have been resolved. After almost 2 days searching I found that in the table master..sysperfinfo there all the PM counters are stored still has the value 4 for conflicts/sec. When I manually create a new conflict this value becomes 5 which is nonsense since it's not the number of conflicts per second but the total number of conflicts since I started the server. If I check in PM it shows the correct value, whih of course is zero 99% of the time. Has anybody ever experienced the same kind of problem??
Or does anybody know a way of resetting the values in sysperfinfo. Even after allowing direct updates to systemtables I still can't modify the table.
hi,Is it over head to use SqlTransaction(begin, commit, rollback) for a single transaction.am not using application block or enterprise library.only a single insert statement.
As part of dealing with a locking problem I am fine tuning a stored procedure that updates a table. The application updates a row by changing every single column except for the primary key, whether 1 or all of the columns have been modified. Although easier to code, this strikes me as taking a sledge hammer to a nut.
Could anyone tell me if there is any benefit in attempting to break this down. That is, code the stored procedure so that only those columns being changed are modified. I am thinking this might reduce dramatically the overhead of writing to the transaction log and making the changes to the actual row.If the benefit is non existant (or insignificant) because of the way Sql Server updates a row it will obviously be a waste of time to generate dynamic sql.
when I run a package from a command window using dtexec, the job immediately says success. DTExec: The package execution returned DTSER_SUCCESS (0). Started: 3:37:41 PM Finished: 3:37:43 PM Elapsed: 2.719 seconds
However the Job is still in th agent and the status is executing. The implications of this are not good. Is this how the sql server agent job task is supposed to work by design.
Does anyone have any benchmarks for the amount of overhead caused by autoshrink of the log and having autostats enabled? We have a customer that insists that turning off these options was necessary to eliminate a performance problem they were having (Query timeouts), but we are not convinced that these two options would have generated enough overhead to have been the root cause (they also rebuilt all their indexes and made some other unspecified changes that more likely solved the problem).
We are hestitant to have them continue with these options disabled because then we need to rely on them to keep the log file shrunk and the statistics updated and because of the data changes during the day, would prefer to have stats updated automatically rather than on a fixed schedule that may not be as appropriate.
Anyway, if anyone has any feedback on overhead generated and potential performance implications of having either of these options enabled, it would be greatly appreciated.
We are having some issues with temporary tables (with # prefixes) within Stored Procedures.
When running a profile trace on them, the stored proc quite happily creates the temp # table (in fact several of them) but whenever it hits the first statement inserting data into one of them (and it doesn't matter which one), there is a 5-6 second delay.
By commenting out one and moving to the next piece of code, the same thing happens.
Following which, the rest of the Stored Prco runs fine and subsequent inserts into the # temp tables also run efficiently.
Is the stored proc getting recompiled perhaps ?
Any advice woul;d be appreciated.
we are running SQL Server 7.0, dont know whether that helps ?
CDC is creating additional tables under System tables.
What is the performance overhead on the database by creating these tables?
I am going to access the CDC records through one ETL tool. Once read the data I am going to delete the records.
If frequency of changes are more once reading the data there may be few records will be added to the CDC. Is CDC is going to truncate the tables or only read records?
I am a developer, and I have a disagreement with my DBA. He has convinced management, that SQL 2005 FullText Index is so much overhead on production, that it should NEVER be used under any circumstances. We have a Cold Fusion site, and somehow he convinced management that a bunch of Cold Fusion developers can create a more efficient full text indexing method than by using SQL 2005 Full Text Index. So now we have to come up with a method for doing this in Cold Fusion.
Is there any statistical data that could possible support or refute his statements? Thanks
We just moved source server to newer, bigger box ... Windows 2003 and Active Directory ... Snapshot agent worked but distribution failed ... Same login as on older machine, login is sysadm, used DCOMCNFG to allow ability to launch process ... What are we missing?
I would appreciate any help here at all. I am pulling my hair out!
I am unable to start the snapshot agent or log reader agent from within SQL management studio. it fails on executing step 2 with unknown username or bad password.
I have checked all account details and they are correctly using my domain admin account. I can connect to SQL using teh same account and it also has sysadmin permissions.
If i copy the step 2 paramters and start from the cmd prompt (again logged in using the same domain account) they both start fine.
I am testing peer to peer replication in our environment. I simulated a three node peer to peer topology and a local distributor.
For some wierd reason I cannot get the Log Reader Agent and snapshot agent to start. The domain account under which SQL Server Agent runs has administrator previlage on the box. I also use a domain account for SQL Server Service. (none of the passwords changed).
This is the error I am getting - "Executed as user: abc. A required privilege is not held by the client. The step failed"
While I was out of the office the Lan Team moved one of my SQL Server2000 servers to a new network domain. Since then the maintenance jobhas not ran.The error log for the SQL Agents has the message listed in the subjectline. I have not found any useful articles on the MS SQL Serversite. Anyone know what might be wrong and how to fix it.HTH -- Mark D Powell --
Hi. I have installed an SQL 2005 Failover Cluster on a Two Node Active Passive Windows 2003 Cluster.If i am trying to failover from the Active node to the passive Node,The Sql Server,Sql Server Agent and SQL Analysis resources fail,However if i reset the passowrd in the services tab of the above three services on the pasive node,the resources come online. Below is the brief of my setup 1.I have two Active directory domain controllers running Windows 2003 R2 Standard edition with SP2. 2.i have installed a Windows 2003 Two Node Active Passive Cluster as NODE1 and NODE2. 3.The Domain account used to install WIndows 2003 A/P Cluster is Clusteradmin.This account is the member of Administartors on the Domain as well as the Local Admin on NODE1 and NODE2. 4.SQL 2005 with SP2 is installed on both the Nodes as SQL 2005 Failover Cluster.The account used to install SQL 2005 is sqadmin.This account is the member of Administartors on the domain and the member of Local Admins on NODE1 and NODE2. 5.SLQ 2005 has four domain groups for 4 SQL Services.The Services are SQL Server,SQL Server Agent,SLQ Anaylisis Server and Full text Search. 6.Each of these servcie has a seperate service account created for them.All these service accounts areb the members of domain admin and the member of Local Admin on NODE1 and NODE2. 7.Each of these servcies is running under these servcie accounts in the Servcies tab in NODE1 and NODE2. 8.If i fail the resources from NODE1 to NODE2 ,The SQL Server,SQL Server Agent and SQL Anaylisis resources are failing.on going to the service tab of NODE 2 I reset the password for these services,the services come online. 10.The Cluster resources and MSDTC Resources are Failing over successfully.They are coming online successfully. 11,I have a problem with the SQL Server Resources,even if i failback to NODE1 from NODE2,the same resources are failing again.
Is there a difference with sql replication between sql server 2005 64bit and sql server 2005 32bit? Both are on a Windows 2003 server. One is 32 bit and the other one is 64 bit. The first time I set up sql replication (test environment), it was on a 32 bit sql server. This worked fine. The second time I wanted to set up replication (live environment), it was on a 64 bit sql server. This didn't worked fine.
I can't call the sqlcesa30.dll file on the 64bit server with IIS. So I called the dll via a remote IIS server. This worked but gave me the following error when calling the following URL: http://domain/PDASYNC/sqlcesa30.dll?diag
SQL Server Mobile Server Agent Diagnostics 2007/07/31 14:26:55
General Information
Item Value
Server Name domain
URL /PDASYNC/sqlcesa30.dll
Authentication Type Anonymous
Server Port 80
HTTPS off
Server Software Microsoft-IIS/6.0
Replication Allowed
RDA Allowed
Logging Level 0
Impersonation and Access Tests
Action Status ErrorCode
Impersonate User SUCCESS 0x0
ReadWriteDeleteMessageFile FAILURE 80070003
SQL Server Mobile Modules Test
Module Status ErrorCode Version
SQLCERP30.DLL SUCCESS 0x0 3.0.5207.0
SQLCESA30.DLL SUCCESS 0x0 Unknown
Reconciler Test
Reconciler Status ErrorCode
9.0 Database Reconciler SUCCESS 0x0
8.0 Database Reconciler FAILURE 0x8007007E
SQL Server Module Versions
Module Version
sqloledb.dll 2000.86.1830.0
9.0 replrec.dll 2005.90.2047.0
9.0 replprov.dll 2005.90.2047.0
9.0 msgprox.dll 2005.90.2047.0
8.0 replrec.dll 2000.80.760.0
8.0 replprov.dll 2000.80.760.0
8.0 msgprox.dll 2000.80.760.0
After seeing this I looked up what ReadWriteDeleteMessageFile could mean. It appeared to be some NTFS acces problem. But when I checked the security settings, it all seemed to be ok.
Has anyone any idea what else to check? Or is sure that I must have done something wrong?
Sql server agent is running under a domain account that is a member of administrators and domain users amongst others, and the package is executed as the service account. Connecting to servers on the same domain works and when I run it from the msdb package store in ssis (ssis runs under the network service account ...) it will connect to the pop server as well. Permissions, fiddly proxies .. the answer's out there somewhere
The SQL Server Agent (MSSQLSERVER) service on server started and then stopped. If i Manually start the services also SQL Server Agent services are stopping automatically.Though I selected Auto Restart SQL Server Agent if it stops unexpectedly, The services are stopping automatically.TCP/IP protocol was enabled for port 1433.Are there any setup I missed?
Hello everyone !!! I need to do a process that run every day to update some data, but my sql server version (express 2005) does not have job agent service. Does anyone can give me a suggestion of what to do? Thank you!
Hi there, was wondering if any can tell me why the following jobs keeps failing: INSERT INTO TblActivityBookingArchive SELECT * FROM TblActivityBooking WHERE (expiryDate <= GETDATE()) it retuens that it is successful when there is no data to archive from TblActivityBooking, it's when there is data to archive that it returns that the job has failed. Hope you can help Thanks Mel
I am unable to start SQL Server Agent. When I try, I get this message: "An Error 1053 - (The service did not respond to the stort or control request in a timely fashion) occurred while performing this service operation on the SqlServerAgent service."
Is it possible to schedule a job to automatically send e-mails based on criteria in a database table, maybe using CDONTS? I don't want to use SQLMail, since my administrator has not set that up. Also, I don't want to write the code in an ASP page for the following reason: If the page is not being viewed at the time I want the e-mail to be sent, it won't work. Besides, it's not efficient to have to read this code for no reason every time a page is loaded in the hopes someone loads it at the correct time. Any suggestions?
We received the error messages below (in the SQLAgent Log) when trying to execute a SQL Job manually. These errors do not appear if the job is executed automatically, and consequently runs smoothly. Only when we try to kick off the job manually does it fail and report these errors.
3/20/01 4:27:16 AM - ! [000] Password verification of the 'SQLAgentCmdExec' proxy account failed (reason: A required privilege is not held by the client) 3/20/01 4:27:16 AM - ! [000] Password verification of the 'SQLAgentCmdExec' proxy account failed (reason: A required privilege is not held by the client)
If anyone has any insight into this issue it would be greatly appreciated.
My sql server agent is not starting and whenever I try to start a job it gives me an error saying that job csnnot be started as sql server agent is not running...and it open a black window(with title bar d:mssql7insqlagent.exe)..can anyone tell me the reason for that and how do I get around this problem.
Attempted to start the SQL Server Agent in Services and got this error message: 'Error 2140: An Internal Windows NT error occured'. Does anyone know what I need to do or fix? Any help would be greatly appreciated. Thanks.
