We get the following errors at the same times every night (between about 3AM and 4AM) on our distributor (ss2000). The publisher and subscribers do not log anything unusual during this time period but are busy. The distribution agents time-out with:
Timeout expired (Source: ODBC SQL Server Driver (ODBC); Error number: S1T00)
---------------------------------------------------------------------------------------------------------------
exec sp_MSadd_distribution_history 7, 3, N'The process is running and is waiting for a response from one of the backend connections.', 0x0000000000000000000000000000, 0, 0, 0.00, 0x00, 1, 0x0000000000000000000000000000, 0, 0x01, 0x01
This is driving me crazy as there appears to be little to go on and I don't know what to do next.
When i log in to my server i dont have the clock in the System Tray? Any ideas on how to fix this. I also keep getting a W32time warning in the system event viewer, i dont know if that has anything to do with it.
How does SQL Server respond when the NT Server's clock is set to a time that is earlier than the current time? Someone on my team seems to think that this is a problem for SQL Server, but I am not sure.
I need to convert the given time into 24 hour clock .
I have the two tables out of which one contain time in 12 Hr clock and another contain time in 24 Hr clock and i need to make a join on this colum by converting 12 Hr time in to 24 hr clock time.
Please help if you can! I have setup a job that runs every day on an hourly basis. Every morning I find that it's been disabled. The funny thing is is that the schedule is disabled, but the job is not (you see 'Enabled' in the jobs list in EM, but when you view the schedules tab, it's disabled.) Also, it runs a several times before becoming disabled.
I have a relatively high usage 2005 database whose transaction log grows every day.
Untill now I have been manually shrinking the log files .. but I would like to automate this process.
In the maintenance schedule that runs every night
1 Do a full backup to disk 2. Do a transaction log backup to disk 3. run the following TSQL
USE [MyDB]; GO ALTER DATABASE MyDB SET RECOVERY SIMPLE; GO DBCC SHRINKFILE (MyDB_log, 3000) GO ALTER DATABASE MyDB SET RECOVERY FULL; GO
4. Backup disk files to tape.
however it doesn't work !! the TLOG file does not shrink, even though its the same as the manual job that I run?
There is nothing in the SQL logs .. but in the event log I get
Event Type: Error Event Source: SQLISPackage Event Category: None Event ID: 12291 Date: 13/12/2007 Time: 00:50:10 User: NT AUTHORITYSYSTEM Computer: ALGSQL1 Description: Package "MyDB_FULL_BACKUP" failed. For more information, see Help and Support Center
Some of you might remember me posting a question about importing data from a csv. Well I've managed to do that now by writing a vb.net service.
Now all I need to do is have some kind of method of exporting the data as a report via email every night.
The tables are as follows...
CDetails cNumber ClName FE
Disb_1 cNumber type dateReq payee amount vat total chequeReq
Disb_2 - Disb_20 "Same as Disb_1"
The report structure I need is...
cNumber ClName FE type dateReq payee amount vat total chequeReq
With data from each disb on a new line, i.e. cNumber could be the same for 20 rows! (don't blame me, it's what they want!)
The records should only be shown though when the dateReq is equal to today. I think I can do that with the DateDiff function.
The problem I have is knowing how to set the report up and what to do it in, Reporting services or Business Intelligence. The report only has to be an email, would it even be simpler to write an extra class into my service?
Hi,Sorry for the obscure title but I'm afraid I can't think of a betterway to describe what happened to one of my clerks last night. The guy wasworking late, made a series of changes (accross a number of tables with adependant relationship structure - i.e. a customer linked to an order linkedto an invoice linked to a payments etc.) Now when he came back this morningnone of the work he did last night was still there. I'm by no means asql-pro - but I've managed to make do so far. Here's what I know:1) All of our records in all of our tables have date/time stamped. Once whenthey go in and once when they're changed. So with a little work I can buildup a good picture of what usage our database gets at what times. I've pulledup a query and there is a big fat hole between 8pm-10pm - stuff thathappened before that is still there, stuff that happened after that is stillthere - but all the changes that were put in between then just aren't thereany more. So it's not just insertions but updates as well.2) There is no perceivable break in our identity columns. So despite thefact that I know he entered 7 new customers, I can go back through thecustomers table and look at where those customers should sit visa vie entrytimes - and it's just as though the customers he entered never existed -sql server just keeps incrementing sequentially and there's no break - sothe last customer entered at about 6:50 pm was something like number 11912and the customers entered this morning have numbers that carry on from11913.3) I'm running in a shared environment - the server is hosted by awebhosting company (who shall remain nameless unless I can prove it's theirfault!) based in the US - the server has 100's of other Users databasesrunning on it.I just don't know where to begin diagnosing this sort of problem. But it hasme really running scared. It's not the first time something like this hashappened to me (i've noticed it once before when I had to get a connectionkilled by the people who manage our server because of a long-running querythat seemed to have just got bunged up and was locking a key table) - butthat was just a few records changed by the user who's connection got killed.Nothing like this... but it's pretty scary - I've got no way of knowing thatI'm losing changes like this on a continuous basis. It's my worstnightmare - like a pipe leaking underneath a floor board - and you only findout when the water starts running down the stairs!Has anyone got any ideas? Any starting places? Anyone else had anything likethis happen to them before?ThanksNick
With Vista Ultimate (release), I installed SQL server 2005 Developer edition. I installed SP1, apparently successfully. But my Windows update history shows the SP being installed successfully every night. I have automatic updating turned on (via AD policy).
Has anyone seen this, and does anyone know how to fix it?
There is a database "Foo" sitting on server "A". There is a database "Bar" sitting on server "B". A.Foo publishes a subset of its schema. B.Bar subscribes to A.Foo's publication. The distribution database is on "B" (B.distributor). This a push subscription (transactions are pushed to the subscriber from the distributor). Every day (including the weekend) I get the following alert:
"5/12/2015 3:53:16 AM, Unsubscribed Transactions (Count) on "B" is Warning.
SQL Server instance "B" has 636771 unsubscribed replication transactions received by the Distributor and not received by a Subscriber.
Unsubscribed Transactions (Count): Number of replication transactions received by the Distributor and not received by a Subscriber."
The number of transactions will vary. The alerts will be sent between 1:20 AM (EST) and 3:30 AM (EST). I'm trying to figure out what is causing the backup of transactions. I assume the issue precedes the alerts by 30-minutes or so.
There are no backups occurringNothing is blocking the distributor agent in the subscription databaseJob activity is at a minimum; the few jobs running run throughout the dayThe machine has plenty of resources -- CPU, RAM, etc.The publisher database shows no signs of stress.
of query timeouts in SQL Server Management Studio?
I have increased the timeout setting in many places and still receive a timeout message within 40 seconds for certain update queries that involve large tables and many records. The only workaround is to break up the job into smaller queries but this makes management, unmanageable.
Is there a solid alternative to SQL Server Management Studio?
hi!I am having some timeout issues.I am running sql 2k with 3gig available ram.I did a 600,000 record delete on a table that gets written to by theactive/production application and my application timed out when it wasdoing the delete.why did stored procedures that ran fine before I started the largedelete slow down?Some of the procedures which slowed down were accessing the same tablewhere i was doing the delete.Thanking you in advance!!parez
I am getting Timeout Errors quite often and cannot figure out why. I am using Enterprise Library 2.0 when accessing the database. It is not from any particular function or page either and when I check the database there may only be 2 or 3 connection from my app. Any ideas of what could be causing this? Below is my error and stack.
Error: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Stack: at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at Microsoft.Practices.EnterpriseLibrary.Data.Database.OpenConnection() at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteReader(DbCommand command) at HM.Security.SecurityData.GetAllUsers(Int32 filter) at HM.Admin.Security.SecuritySearch.SetUpAutoFill() in f:InetpubWwwroothomemiSectionsactSearch.aspx.cs:line 74 at HM.Admin.Security.SecuritySearch.Page_Load(Object sender, EventArgs e) in f:InetpubWwwroothomemiSectionsactSearch.aspx.cs:line 26 at System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) at System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) at System.Web.UI.Control.OnLoad(EventArgs e) at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
Hello all, I am managaing an NT infrastructure that supports a web application. The web server is configured to use an ODBC System DSN to access data on an SQL server over a 100MB private segment. When users run a particular query through the web page, they get an ODBC "Timeout Expired" message. Does anyone know a fix for this. I could not find anything that explicitly identified this problem in MS Support. The query runs fine from an Eneteprise Manager T-SQL window both on the server and remotely.
Thanks in advance, Ed Molinari Technical Architect Eamerald Solutions
A timeout issue is occurring resulting in following error. This issue is sporadic and may be related to network. This error occurs on the client: Microsoft OLE DB Provider for SQL Server error `80040e14` OLE/DB provider returned message: Timeout expired "conn_info".asp, line 444
Subject: SQL server job timeouts?We have a job that uses WinHTTP inside a stored procedure. We haveanother SP wrapper that runs this for a couple hundred records.When we add this as a job using SQLAgent it times out half way. Runningit from SQL Query analyser and it completes to the end.Anyway to set the timeouts for jobs?
I've had my SQL server database running for two years now without aproblem.However, just today one of the main tables started returning an error.The table is contained within a database called engineering. I backit up once a week and the file size is up to about 40 MB.The error returned when trying to return data from one table(DbLucent) is:"[Microsoft][ODBC SQL Server Driver]Timeout expired"I can open/query any of the other tables in the database. I can opendesign table for this table. But it won't return any query.I'm debating whether to restore the database from the last backup.Any suggestions would be appreciated. Being located reomotely, Irather not fly back to the city where the server is and work on itthere either.-David
I'm having major problems with SqlClient Data provider connections in my application. I'm using Win 2003 Server with SQL Server 2000 (both on the same machine), and my application is developed in VB.NET.
I'm using the following db connection string: Data Source=(local);Initial Catalog=database;Uid=user;Pwd=password;max pool size=100;
As you can see max pool size is set to 100. The SQL Database has it's MAX_CONNECTIONS property set to >32000.
The symptoms are that at seemingly random times, not nessecarily during peak hours, the application becomes extremely slow and stops responding. The error message that is beeing generated is "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."
After monitoring and debugging, I've found two things.
1) At random times, zero-three times a day, the number of database connections (according to performance monitor) rises from normally 2-8 up to 20-25. After a while, usually 10-60 minutes, it drops back to normal. This is directly related to when the timeout errors occur. It doesn't seem to happen more often during peak load, for some reason. I suspect there is connection leakage in the application, but I've done extensive debugging, and I just can't find any. Also, since the peaks occur randomly and I can't reproduce the error in my development environment, I have no solution to this problem. A few weeks ago, the application ran during heavy load without this problem for 7 days. Before and after that, there have been errors at least every second day. That confuses me even more.
2) The number of connections (monitored in perf mon) never reach above 25. Why? I'm using SQL Server enterprise edition, it's connection limit is over 32000. ADO pool size is 100, and I can't find anything else that would limit the amount of concurrent connections. I'm totally confused. Why does number of connections never rise above 25? Is there a setting hidden somewhere that I just can't find? Also, according to Performance Monitor, I don't get any failed connects, I just get failed commands. Maybe that's a clue? Could the problem some kind of deadlocks in the database that causes timeout?
I realize this entire post is rather confusing. It reflects the state of my mind, I guess, after working around the clock on this issue for a month :)
I have a basic UPDATE that's something along the lines of:
UPDATE LocalTable SET LocalColumn = RemoteColumn FROM LocalTable INNER JOIN server.catalog.dbo.RemoteTable ON (...)
After running in Query Analyzer for ten minutes, I get:
Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'SQLOLEDB' reported an error. Execution terminated by the provider because a resource limit was reached. [OLE/DB provider returned message: Timeout expired] OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ICommandText::Execute returned 0x80040e31: Execution terminated by the provider because a resource limit was reached.].
I checked the linked server settings in EnterpriseManager and both timeout settings are at 0 (no timeout). Any idea how I can remove whatever timeout is causing this?
Anyone know how remote join performance compares to moving remote data over via DTS, running an UPDATE, and dropping the data?
I'm having a problem with SQL Server 2005 not being able to send all of my messages. Occasionally when we batch a large number of messages together, we run into problems because the SMTP server can't process them fast enough, and the message doesn't send due to a timeout. I know I have increase the number of retries, but I'd rather just increase the timeout setting. Does anyone know where I might be able to do this at? The mail admins are also looking at modifying the rules in SMTP so that messages from these servers don't go through the vigor of SPAM and phishing detection. Hopefully that will speed it up, but I'd like to give it a little extra leniency on the SQL side as well. Any suggestions?
I have two problems I need some help with.First, I've just inherited a system and am delving into a few timeoutproblems that are causing problems for the users.Now, if I do a simple select * from the table (which looks to be thecause of the problem at this stage) in QA, I get the results back inless than a second. If I open the table in EM it takes about 10. Isthere a difference in viewing the data this way ? I'm used to EM beingvirtually the same speed. There is only one row. Minor questionreally, just something I'd like to understand if there is adifference.CREATE TABLE [QUERY] ([QUERY_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,[CAT_ID] [numeric](18, 0) NOT NULL ,[QUERY_DESCR] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[USER_NAME] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[USER_ID] [int] NOT NULL ,[IND_EURO] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULLCONSTRAINT [DF_QUERY_IND_EURO] DEFAULT ('N'),[IND_DGCOLUMNS] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL CONSTRAINT [DF_QUERY_IND_DGCOLUMNS] DEFAULT ('N'),[NO_GROUPS] [int] NOT NULL CONSTRAINT [DF_QUERY_NO_GROUPS] DEFAULT(0),[NO_FIELDS] [int] NOT NULL CONSTRAINT [DF_QUERY_NO_FIELDS] DEFAULT(0),[NO_LINES] [int] NOT NULL CONSTRAINT [DF_QUERY_NO_LINES] DEFAULT (0),CONSTRAINT [PK_QUERY] PRIMARY KEY CLUSTERED([QUERY_ID]) WITH FILLFACTOR = 90 ON [PRIMARY] ,CONSTRAINT [FK_QUERY_QUERY_CATEGORY] FOREIGN KEY([CAT_ID]) REFERENCES [QUERY_CATEGORY] ([CAT_ID]) ON DELETE CASCADE ON UPDATE CASCADE) ON [PRIMARY]GOI don't think any re-indexing has been done on this (or the othertables in the db). I was wondering if constant adding/deleting rowscould cause the index to be massive and in need of a good clear out.Any pointers would be appreciated. From what I can tell, there wassome problems trying to get replication to work. I need to dig deeperto see if this is now correct.-------------------------Secondly, there is a another table in the same database.CREATE TABLE [FIELD_DATA] ([ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,[DATA_ID] [numeric](18, 0) NOT NULL ,[FIELD_ID] [numeric](18, 0) NULL ,[FIELD_CODE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[FIELD_VALUE] [numeric](15, 5) NULL ,CONSTRAINT [PK_FIELDDATA] PRIMARY KEY CLUSTERED([ID]) WITH FILLFACTOR = 90 ON [PRIMARY]) ON [PRIMARY]GOIt holds approx 4 million rows. The rest of the tables have minimaldata and about the same amount (consider them the same if you will).Now, another 'copy' of this database is held elsewhere (differentclient data) and this holds 40 million rows. The difference is thatthe first DB is 4.5GB and the second 6.5GB (approx). Does this provemy theory that re-indexing would be a good idea ?ThanksRyan
HiGot a strange problem.For some reason our web client box times out occasionally. Maybe afew times a day.Nothing appears in the logs.What I do know is that the disk queue ramps up to <high>(think top ofthe graph), processor queue jumps up and the tps drops to 0 (naturallyenough!) along with reduced pagefile usage. Usually at this time thetps is between 20 and 300. Running a pair of mirrored 18gb scsi disksfor the whole server (yeah yeah) and a whopping 640mb memory. Oh anda single piii 1 gig. Sql2k standard vanilla, server2k.What server trace events is it worth my catching to try and get abetter Idea of what may be causing this? No major jobs (backups etcseem to be occuring at these times).Cheers
Dear Group,I am tring to use a command that calls the server to fill an adapter, itnever seems to get to the adapter, command and the server either times outor does not respond. The timeout is set at 10 hours. I am using VisualStudio to acces MS SQL - Server.I think I have all the rights and permissions set correctly. Also, I haveused this command to fill other adapters and tables.Does anyone have a suggestion.Jeff Magouirk
im testing some database cleanup scripts that dont seem to be working. im calling stored procedures through a VBScript file to clean out a table in the database at night. there are a couple of tables to clean out (around 12 tables) and i have a sleep in between each call to the stored procedure. the sqlcmd im using is in this format:
so this is called a few times, once for each table, and sleeps for ~10 seconds before the next call to give some lead time. the output i see is below
[SQL Native Client]SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. [SQL Native Client]Login timeout expired [SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
now when i run these SPs manually in SSMSE, it executes fine with no problems. is there some timeout valuee im doing wrong? or am i using the wrong syntax for the osql?
Getting an issue with SQL Server 2005 in my production environment and wanted to see if anyone else has seen similar behavior. First the environment:
SQL Server 2005 on the backend, main site is running on ColdFusion 5 and there are various internal applications written in VB6 and .NET.
Upgraded to SQL Server 2005 about three weeks ago and since then about once a week the server throws a fit and causes all sorts of timeouts. Event Viewer is showing a bunch of same event:
Event ID: 8628 Description: A time out occurred while waiting to optimize the query. Rerun the query.
The events occur a rate of about 10/minute and basically lock up the server. Last time this happened I was not able to open a connection to the SQL Server to even analyze the issue. All connections timed out... Had to shutdown and restart the server to recover.
The first time this happened I saw a lot of waittypes RESOURCE_SEMAPHORE_QUERY_COMPILE in the processes table and thought that this might be related to the known bug with 2005 that has to do with implicit conversions between Ascii and Unicode. This might be the case since I know we have a lot of NVARCHAR columns and most searches are using ASCII text so the result looks something like this:
SELECT cols FROM table WHERE nvarchar_column = 'Something'
There is a published article regarding this issue here:
I'm having an issue with what appears to be SQL Server 2005 deciding to randomly ignore new connections.
I currently have two virtual servers - one running just SQL Server 2005, the other running Reporting Services, Windows Sharepoint Services and Team Foundation Server.
For 3 weeks, it was all working perfectly, then on Wednesday night the server (and both Virtual Servers) was rebooted after installing the latest updates for Windows. Since then, I've had this issue.
It will work fine for a while, then it'll start throwing loads of Errors and Warnings into the Event Log, all along the lines of unable to connect to the database. The Reporting Services Configuration utility throws up the same problem. Then randomly, it'll start working again.
If anyone has any ideas, they would be much appreciated as this is driving me crazy!
I was given a list of user comments from an old application and told to match it up with our current system. After all the easy methods I was still left with 95% of the records unmatched. The only way I had left was to try and match up the names. Given that people could have the same name I added a count to make sure it only found one record. This query has been running for over an hour now. How do I tell if it's actually running, or if it locked up? Also here is the query I'm using, does anyone have any feedback on it? Is this poorly written, is my logic wrong? Did I put the server in a loop? UPDATE user_table_1SET id = s.idFROM user_table_2 AS sWHERE RTRIM(s.first_name) = RTRIM(user_table_1.first_name) AND RTRIM(s.last_name) = RTRIM(user_table_1.last_name) AND (SELECT COUNT (*) FROM user_table_2 AS cs WHERE RTRIM(user_table_1.first_name) = RTRIM(cs.first_name) AND RTRIM(user_table_1.last_name) = RTRIM(cs.last_name)) = 1
I have a database that I am using as an archive for emails and am storing them in varbinary(max) data types. The database works fine for inserts and retrieval but I cannot delete large sets of records ( > 30K) without it timing out. I assume this is because SQL server isn't simply releasing the handle to the blob but is instead doing a lot of work reclaiming the space. Is there any flag I can set or approach I can use to resolve this issue?
I was considering moving the blobs into a seperate simple table and putting async triggers on the primary to delete the blobs, will this work?
Any ideas are appreciated. Besides the "store files in the file system" idea. - Christopher.
