[VB / SQLServer] Vage foutmelding in SQL

[PdeHoog]

We zijn bezig met de ontwikkeling van een applicatie in VB6 die met behulp van ADO verbinding maakt met een SQLServer 7.0-database.

In de ontwikkelomgeving hebben we nergens last van, maar in productie krijgen we constant een foutmelding. Heeft iemand deze foutmelding wel eens voorbij zien komen?

"Cannot create new transaction because capacity was exceeded."

Microsoft geeft twee hits, waarvan er een van toepassing is op Access en verder niets nuttigs bied. De ander geeft aan dat het in een groot insert-transactie zou kunnen zitten. Dat kan wel kloppen....de plekken dat het fout gaat betreffen inderdaad procedures waarvandaan een insert-statement een grote hoeveelheid keer wordt aangeroepen. De code ziet er echter goed uit.

Ik ben nu benieuwd of er meer mensen met dit probleem zijn of die het hebben gezien en mij misschien kunnen vertellen wat ze er aan hebben gedaan?

Overigens, het probleem is in de ontwikkelomgeving totaal niet na te spelen. We hebben ons best gedaan, maar het lukt niet.

[Goodielover]

Vanuit mijn Oracle achtergrond zou ik zeggen: te kleine redo/log files.
Ik hoorde van collega's hier dat zij ook bij SQLServer dit probleem hadden, maar dat ging toen nog om erg kleine hoeveelheden data. Een commit tussendoor loste het op, maar fraai is dat natuurlijk niet. Die grootte van de logfile was toen niet het probleem.

[PdeHoog]

SQL staat zo ingesteld dat het log automatisch groeit. Elke nacht wordt deze bovendien gebackuped en getruncated.

We hebben nu nog als oplossing om na elke grote transactie gelijk maar een truncate uit te voeren op het log. We zijn aan het onderzoeken of dat wil helpen.

[Verwijderd]

eeeuh . .transacties kleiner maken ??

[Verwijderd]

Domme vraag wellicht, maar is er wel een service pack op die productieserver geinstalleerd? SQLServer7 zit op dit moment op Servicepack 4.

Wat is het nesting level van de transactions in je stored proc? Ga je via een recursieve loop transactions aanroepen of heb je nested triggers die de boel verkloten?

[PdeHoog]

Op dinsdag 28 mei 2002 13:40 schreef Otis het volgende:
Domme vraag wellicht, maar is er wel een service pack op die productieserver geinstalleerd? SQLServer7 zit op dit moment op Servicepack 4.

Goed idee. Ik zal dat gelijk eens nakijken morgenochtend!

Wat is het nesting level van de transactions in je stored proc? Ga je via een recursieve loop transactions aanroepen of heb je nested triggers die de boel verkloten?

De transactions worden vanuit VB aangeroepen. Het betreft hier ADO-transactions waarbij er gelooped wordt vanuit een grid. We zijn aan het kijken of we hier de transaction gewoon weg kunnen laten.

[Verwijderd]

Uit MSDN ( http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql7/html/storageeng.asp, onderaan ):

Transaction Log
A Microsoft SQL Server database has at least one data file and one transaction log file. Data and transaction log information is never mixed on the same file, and individual files are used by only one database.

SQL Server uses the transaction log of each database to recover transactions. The transaction log is a serial record of all modifications that have occurred in the database, and which transaction performed each modification. The log records the start of each transaction, the changes made to the data, and enough information to undo the modifications, if necessary. The log grows continuously as logged operations occur in the database. For some large operations, such as CREATE INDEX, the log records the fact that the operation took place. The log records the allocation and deallocation of pages, and the commit or rollback of each transaction. This allows SQL Server either to restore or to back out of each transaction:

A transaction is rolled back when SQL Server backs out of an incomplete transaction. SQL Server removes from the database all modifications that followed the BEGIN TRANSACTION statement. If SQL Server encounters log records that indicate a CREATE INDEX was performed, SQL Server reverses the statement. These operations are reversed from most recent to oldest.
A transaction is rolled forward when a transaction log is restored. SQL Server copies to the database the image of the data that followed every modification or reruns statements such as CREATE INDEX. These actions are applied in the same sequence in which they originally occurred. At the end of this process, the database is in the same state as it was at the time the log was backed up.
At a checkpoint, SQL Server ensures that all log records and all database pages that have been modified are written to disk. During the recovery process of each database that occurs when SQL Server is restarted, a transaction must be rolled forward only when it is not known whether all the data modifications in the transaction were actually written from the SQL Server buffer cache to disk. Because a checkpoint forces all modified pages to disk, it represents the point at which the startup recovery must start rolling transactions forward. All pages that were modified before the checkpoint are guaranteed to be on disk, so there is no need to roll forward any transaction that was completed before the checkpoint.

Each log file is divided logically into smaller segments called virtual log files. Virtual log files are the unit of truncation for the transaction log. When a virtual log file no longer contains log records for active transactions, it can be truncated and the space becomes available to log new transactions.

The smallest size for a virtual log file is 256 KB. The minimum size for a transaction log is 512 KB, which provides two virtual log files of 256 KB each. The number and size of the virtual log files in a transaction log increase as the size of the log file increases. A small log file might have a small number of small virtual log files (for example, a 5 MB log file comprised of 5 virtual log files of 1 MB each). A very large log file has larger virtual log files (for example, a 500 MB log file comprised of 10 virtual log files of 50 MB each).

SQL Server avoids having many small virtual log files. When SQL Server recovers a database, it must read each virtual log file header. Each log file header costs one page I/O. The more virtual log files, the longer the time required for a database to start. Approximately 50 to 100 log files is acceptable; 1,000 log files may be too many. The number of virtual log files grows much more slowly than their respective sizes. If a log file grows in small increments, it tends to have many small virtual log files. If a log file grows in larger increments, SQL Server creates fewer larger virtual log files. For example, if the transaction log grows by 1 MB increments, the virtual log files are smaller and more numerous than those corresponding to a transaction log that grows at 50 MB increments.

By default, a log file automatically grows (autogrow) unless you specified otherwise when you created it. The log grows by the increment set at creation time. The increment can be a percentage of the file or a specific MB/GB value. Every time the autogrow feature activates, more virtual files are created. To avoid creating too many virtual files by preventing frequent activation of the autogrow feature, set the autogrow increment to a reasonable size. For example, if the log is 200 MB and grows to 400 MB daily, set the autogrow increment to 50 or 100 MB rather than to 1 MB.

The autogrow feature also stops the transaction log to allow for the addition of space. As a rough estimate, you can add 3 MB per second. As records are written to the log, the end of the log grows, moving from one virtual log file to the next. If there is more than one physical log file for a database, the end of the log grows, moving through each virtual log file in each physical file before it circles back to the first virtual log file in the first physical file. Only when all existing log files are full will the log begin to grow automatically.

The log can also be set to shrink automatically (autoshrink). By default, autoshrink is on for the SQL Server Desktop Edition and off for the Standard and Enterprise editions.

The autoshrink feature does not shrink logs smaller than their original sizes set by the database administrator. If the autoshrink feature is on, a calculation is performed every 30 minutes to determine how much of the log has been used. If the log size exceeds the original size, the log is marked for shrinkage, assuming available free space. The actual shrinking of the log takes place with the next log truncation or BACKUP TRANSACTION LOG statement. The shrinking of a log incurs a performance cost.

Recommendations:

- Set the original size of the transaction log to a reasonable size to avoid constant activation of the autogrow feature, which creates new virtual files and stops logging activity as space is added
- Set the autogrow percent to a reasonable but small enough size to avoid frequent activation of the autogrow feature and to prevent stopping the log activity for too long a duration
-Use manual shrinking rather than automatic shrinking

HTH