SQLServer Error: 802, 701, 733
in [Server Configuration]
|
Prev: Missing Object xp_cmdshell
Next: Batch File ???
From: Bernd Maierhofer on 3 Aug 2010 02:54 SQL Server 2008 on Win2008 R2 x64 Server, 8 GB RAM - running virtualized on VMWare ES-Server Every sunday some maintainance jobs are scheduled to run. Rebuilding indices and statistics fail with messages: [298] SQLServer Error: 701, There is insufficient system memory in resource pool 'internal' to run this query. [SQLSTATE 42000] .... [298] SQLServer Error: 802, There is insufficient memory available in the buffer pool. [SQLSTATE 42000] (SaveAllSchedules) After this the server is no longer reachable: [298] SQLServer Error: 773, SQL Server Network Interfaces: The requested security package does not exist [SQLSTATE 08001] .... [298] SQLServer Error: 773, A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. [SQLSTATE 08001] A reboot of the server solves the situation, however the maintainance tasks are not completed. Any idea, what�s going on? Thanks for any assistance, Bernd Maierhofer www.nutritional-software.at
From: Erland Sommarskog on 3 Aug 2010 15:10 Bernd Maierhofer (bernd.maierhofer(a)dato.at) writes: > SQL Server 2008 on Win2008 R2 x64 Server, 8 GB RAM - running virtualized > on VMWare ES-Server > > Every sunday some maintainance jobs are scheduled to run. Rebuilding > indices and statistics fail with messages: > > [298] SQLServer Error: 701, There is insufficient system memory in > resource pool 'internal' to run this query. [SQLSTATE 42000] > ... > [298] SQLServer Error: 802, There is insufficient memory available in the > buffer pool. [SQLSTATE 42000] (SaveAllSchedules) > > > After this the server is no longer reachable: > [298] SQLServer Error: 773, SQL Server Network Interfaces: The requested > security package does not exist [SQLSTATE 08001] > ... > [298] SQLServer Error: 773, A network-related or instance-specific error > has occurred while establishing a connection to SQL Server. Server is > not found or not accessible. Check if instance name is correct and if > SQL Server is configured to allow remote connections. For more > information see SQL Server > Books Online. [SQLSTATE 08001] > > A reboot of the server solves the situation, however the maintainance > tasks are not completed. I don't have much idea of what is going on, but what if you reboot the server *before* the maintenance job? That may give a clue of whether the maintenance job itself is causing the problem, or if it something that adds up with the sins of the past week. If I am to make a guess, you have a memory leak because of an extended stored procedure, OPENXML or similar. What is in those maintenance job up the job that fails? -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Bernd Maierhofer on 4 Aug 2010 01:45 Rebuiding indices and statistics. The DB is abeout 30 GB having 2 Tables containing most of the data as well as most indices. Is it possible that the rebuild of the indices is done in one transaction, and separating it into smaller peaces would succeed? But in the past this was rather a problem with space and not with memory. Maybe a service allocationg memory without releasing? tx Bernd "Erland Sommarskog" <esquel(a)sommarskog.se> schrieb im Newsbeitrag news:Xns9DC9D75ABAE88Yazorman(a)127.0.0.1... > Bernd Maierhofer (bernd.maierhofer(a)dato.at) writes: >> SQL Server 2008 on Win2008 R2 x64 Server, 8 GB RAM - running virtualized >> on VMWare ES-Server >> >> Every sunday some maintainance jobs are scheduled to run. Rebuilding >> indices and statistics fail with messages: >> >> [298] SQLServer Error: 701, There is insufficient system memory in >> resource pool 'internal' to run this query. [SQLSTATE 42000] >> ... >> [298] SQLServer Error: 802, There is insufficient memory available in the >> buffer pool. [SQLSTATE 42000] (SaveAllSchedules) >> >> >> After this the server is no longer reachable: >> [298] SQLServer Error: 773, SQL Server Network Interfaces: The requested >> security package does not exist [SQLSTATE 08001] >> ... >> [298] SQLServer Error: 773, A network-related or instance-specific error >> has occurred while establishing a connection to SQL Server. Server is >> not found or not accessible. Check if instance name is correct and if >> SQL Server is configured to allow remote connections. For more >> information see SQL Server >> Books Online. [SQLSTATE 08001] >> >> A reboot of the server solves the situation, however the maintainance >> tasks are not completed. > > I don't have much idea of what is going on, but what if you reboot the > server *before* the maintenance job? That may give a clue of whether > the maintenance job itself is causing the problem, or if it something that > adds up with the sins of the past week. > > If I am to make a guess, you have a memory leak because of an extended > stored procedure, OPENXML or similar. > > What is in those maintenance job up the job that fails? > > > -- > Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se > > Links for SQL Server Books Online: > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000: > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >
From: Bernd Maierhofer on 4 Aug 2010 02:14 Maybe this http://support.microsoft.com/kb/982854/en-us is the cause. I�m investigating. B. "Bernd Maierhofer" <bernd.maierhofer(a)dato.at> schrieb im Newsbeitrag news:O8ws7g5MLHA.1624(a)TK2MSFTNGP06.phx.gbl... > Rebuiding indices and statistics. > > The DB is abeout 30 GB having 2 Tables containing most of the data as well > as most indices. > > Is it possible that the rebuild of the indices is done in one transaction, > and separating it into smaller peaces would succeed? But in the past this > was rather a problem with space and not with memory. > > Maybe a service allocationg memory without releasing? > > tx Bernd > > "Erland Sommarskog" <esquel(a)sommarskog.se> schrieb im Newsbeitrag > news:Xns9DC9D75ABAE88Yazorman(a)127.0.0.1... >> Bernd Maierhofer (bernd.maierhofer(a)dato.at) writes: >>> SQL Server 2008 on Win2008 R2 x64 Server, 8 GB RAM - running virtualized >>> on VMWare ES-Server >>> >>> Every sunday some maintainance jobs are scheduled to run. Rebuilding >>> indices and statistics fail with messages: >>> >>> [298] SQLServer Error: 701, There is insufficient system memory in >>> resource pool 'internal' to run this query. [SQLSTATE 42000] >>> ... >>> [298] SQLServer Error: 802, There is insufficient memory available in >>> the >>> buffer pool. [SQLSTATE 42000] (SaveAllSchedules) >>> >>> >>> After this the server is no longer reachable: >>> [298] SQLServer Error: 773, SQL Server Network Interfaces: The requested >>> security package does not exist [SQLSTATE 08001] >>> ... >>> [298] SQLServer Error: 773, A network-related or instance-specific error >>> has occurred while establishing a connection to SQL Server. Server is >>> not found or not accessible. Check if instance name is correct and if >>> SQL Server is configured to allow remote connections. For more >>> information see SQL Server >>> Books Online. [SQLSTATE 08001] >>> >>> A reboot of the server solves the situation, however the maintainance >>> tasks are not completed. >> >> I don't have much idea of what is going on, but what if you reboot the >> server *before* the maintenance job? That may give a clue of whether >> the maintenance job itself is causing the problem, or if it something >> that >> adds up with the sins of the past week. >> >> If I am to make a guess, you have a memory leak because of an extended >> stored procedure, OPENXML or similar. >> >> What is in those maintenance job up the job that fails? >> >> >> -- >> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se >> >> Links for SQL Server Books Online: >> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx >> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx >> SQL 2000: >> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >> > >
From: Erland Sommarskog on 4 Aug 2010 15:39
Bernd Maierhofer (bernd.maierhofer(a)dato.at) writes: > Maybe this > http://support.microsoft.com/kb/982854/en-us > > is the cause. I�m investigating. Does not seem to be related to indexing, but if there are fulltext queries running simultaneous, the reindexing could be the victim. > Is it possible that the rebuild of the indices is done in one transaction, > and separating it into smaller peaces would succeed? But in the past this > was rather a problem with space and not with memory. No, this is very unlikely. Your database is 30 GB which is fairly modest. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx |