Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
can anyone help me out what exactly the problemIt looks like you call stack exceeds maximum allowable level. It means that you run a stored procedure that executes another stored procedure that executes yet another... and so on.
Hi:
Is there a limit on the number of subscribers for Transactional replication?. My question is how many maximum number of subscribers can we have in transactional replication?. How do I retrieve this information throug T-SQL (any DMVs or system tables?). Please let me know.
I am looking to know for both SQL 2005 as well as SQL 2000 and also if anyone can tell me if anything has changed in SQL 2005 for transactional replication that would be great.
Can anyone please let me know?.
Thank you
AK
There is no hard-coded limit, just what your hardware and workload can spare. we've tested 500 concurrent, but the hardware and workload will be much different that what you have, and is no indication or guarantee that you can achieve the same.|||Hi there,You can check the following article for new replication features and improvements.
http://msdn2.microsoft.com/en-us/library/ms170959.aspx
One of the performance enhancement feature is multi-stream subscription for tran replication.
Regards,
Gary
Does AS 2005 have a limit to the number of partitions it can support?
I am sure some one out there must have asked this before, but I just can't seem to be able to find an answer in the docs or online.
Regards,
Alex
Hi,
In standard edition: maximum 3 non-writeback partitions per MeasureGroup.
In the other editions (enterprise, developer, evaluation): the limit is 2^32-1, so it's up to how much the server and tools can handle.
Adrian Dumitrascu.
|||Adrian,Thanks a lot. It would be interesting to know at what point the number of partitions begins to affect performance.
Regards,
Alex|||
1) Technically, there is no limit. On Project REAL we are running about 1300 partitions on our Store Inventory MG. However, you will feel the difference at that level. SSMS is slow at making connections (5-10 min) and once you drill down and expand the partition list you again feel a multi-minute delay (although less). That being said, once it is open, navigating and working with the objects is about the same – and queries are not impacted; nor are executing XMLA scripts in the background. It appears to mostly be an issue with SSMS.
2) You should be partitioning based on your query load; not necessarily based on row count. While, in general, the recommendation is to keep partitions to no larger than 20M rows per partition, I’ve seen system which went larger and still got good responses because of other factors, e.g. well tuned aggregations based on the type of queries issued against long-term historic data.
|||Dave,Thanks a lot. Just wondering when the number of partitions grows to this level does it affect cube processing in any way? Have you had a situation in Project REAL that you needed to reprocess the cube?
Regards,
Alex
oopsss sorry... oopsss sorry...We're using Conversation Timers to implement a retry mechanism for outbound web service calls. The idea is to use Service Broker to queue up a number of web service requests. If a web service call fails to contact it's target server, we would like to reschedule that request to retry after a few minutes delay. It appears that when we have a few thousand Conversation Timers queued up, SQL Server (June CTP) will crash, or at least cause a failover to it's mirror partner.
TIA -- Keith.
There is no hard limit on the number of conversations. However, conversations timers do require system resources (memory, processor time, tempdb space, etc) and if you push more than your hardware can handle, the system might become unresponsive and this can cause mirroring to failover.
In order to decide whether this is the case or something else is happening, I would need more info.
- Is it really a crash or just a failover? Does the SQL LOG folder contain any dumps?
- What kind of hardware is this happening on: architecture, number and speed of procs, RAM available?
- When the failover occurs, any entry appear in the ERRLROG or in the system EventViewer, on the pricnipal or on the mirror?
Thanks,
~ Remus
We are running an load test which is attempting to benchmark the maximum capability of the system. The h/w configuration:
2 x HP DL380's running the SQL 2005 June CTP on Windows Server 2003 Standard Edition SP1. The servers are using synchronous Database Mirroring with a 3rd machine acting as a Witness. Each DL380 has 2 x 3.4GHz Xeon's and 4GB RAM. Each DL380 also has a RAID5 SCSI array attached.
@.@.VERSION = Microsoft SQL Server 2005 - 9.00.1187.07 (Intel X86) May 24 2005 18:22:46 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
There are also 2 x HP DL360's running Windows Server 2003 Web Edition SP1. Each DL360 has 2 x 3.4GHz Xeon's with lots of RAM. These are being used as load-balanced web application front ends, running ASP.NET 2.0. There are also 3 .NET 2.0 based windows services running against the database.
There are no indications of a crash: no dumps in the SQL LOG folder. The starting PRIMARY server has the following log entries at the time of failover:
09/09/2005 21:25:36,Logon,Unknown,Login failed for user 'XXXUser'. [CLIENT: 10.0.10.14]
09/09/2005 21:25:36,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 16.
09/09/2005 21:25:36,spid27s,Unknown,Bypassing recovery for database 'XXX' because it is marked as a mirror database<c/> which cannot be recovered. This is an informational message only. No user action is required.
09/09/2005 21:25:35,spid27s,Unknown,Starting up database 'XXX'.
09/09/2005 21:25:34,Logon,Unknown,Login failed for user 'XXXUser'. [CLIENT: 10.0.10.15]
09/09/2005 21:25:34,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 16.
09/09/2005 21:25:34,spid17s,Unknown,Database mirroring is inactive for database 'XXX'. This is an informational message only. No user action is required.
09/09/2005 21:20:17,Backup,Unknown,Log was backed up. Database: XXX<c/> creation date(time): 2005/09/02(23:31:06)<c/> first LSN: 2489:314:1<c/> last LSN: 2492:2360:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'D:\SQL_Data\MSSQL.1\MSSQL\Backup\tlogs\XXX_backup_200509092120.TRN'}). This is an informational message only. No user action is required.
The error messages for failed logons continue to repeat. The starting SECONDARY server has the following log entries at the time of failover:
09/09/2005 21:49:37,spid15s,Unknown,Database mirroring is inactive for database 'XXX'. This is an informational message only. No user action is required.
09/09/2005 21:25:39,spid15s,Unknown,Database mirroring is active with database 'XXX' as the principal copy. This is an informational message only. No user action is required.
09/09/2005 21:25:35,spid15s,Unknown,Recovery is writing a checkpoint in database 'XXX' (5). This is an informational message only. No user action is required.
09/09/2005 21:25:33,spid15s,Unknown,Database mirroring is inactive for database 'XXX'. This is an informational message only. No user action is required.
09/09/2005 15:51:17,spid15s,Unknown,Database mirroring is active with database 'XXX' as the mirror copy. This is an informational message only. No user action is required.
This is an order processing application using 4 service broker queues all in the same database. One queue is being used by the two ASP.NET 2.0 front-ends for Order Entry. The three .NET 2.0 services each process the remaining three queues. Each of the services is multi-threaded with 4, 16, and 16 threads respectively. Each thread will wait on it's respective queue for an available request.
The system overall runs as expected when no conversation timers are injected into the queues. The application front-ends run at nearly 100% CPU utilization (as expected) and the database servers seems to run at around 20% CPU without extensive memory pressure.
Simulating one of our failure conditions causes the program logic to use the conversation timers in one of the queues as part of the retry mechanism. The failover and logon problems start after only a few minutes of running our load test. I would estimate that less than 500 orders have been processed at that time.
-Keith.
|||How big are the conversation timer intervals?If I understand correctly, you say that when there are about 500 conversation timers 'armed' for a timeout of X, the system will become unresponsive (user logins are denied, mirroring timeouts occur triggering failover etc).
we'd better take this offline, I will probably have more questions for you and some of the details probably you don't want them posted on a public forum. Can you send me at r e m u s r @. m i c r o s o f t . c o m a mail address where I can contac yout?
I will post back the conclusion here.
Thanks,
~ Remus
I am upgrading from Access, where you can only have 10 fields in a primary key or unique index. Is this also the limit in SQL Server? If not, what is the limit?
Thanks for any help on this.
From Books Online, Topic: 'Create Index'
"Up to 16 columns can be combined into a single composite index key."
