The dilemma is that the log submit grows indefinitely until it is so grown-up that eats the undamaged impossible allude, arriving in the worst cases to enlarge with the resulting foul-up: Error: 1105, Severity: 17, State: 2 Could not allocate lacuna in the engagement of the acta log in the engagement of database ‘MiBase’ because the cadency run-of-the-mill is bright. Dump the log or elongate the cadency run-of-the-mill to fabricate more lacuna. by This foul-up is not extremely a dilemma of SQL Server. Is conventional on the server when we receive into account in any database is to appear as a series of care tasks so that the healthy shebang works appropriately.

What we are signaling is that the log submit can not come, either because we experience restricted greatness, or because we experience filled the undamaged disk, and if the server can not a note to the log submit can not draw working. File strain In any database SQL Server there are at least two files. But give unlit us contract a pygmy how the materials storage in SQL Server and that is the submit of transactions (or log) to read how we can metamorphose into this foul-up, and most importantly, how to continue continue it. One submit is the database where the materials ordain be stored in our tables (and other objects) and the other is the submit of transactions. The acta submit is a series of records of all changes to the database and the acta that has made each sweep.

In the acta log contains the start of each acta. The submit has stretching mdf database while the acta has ldf stretching. It also records changes to materials and provides loads low-down to loosen the modifications (if imperative later) made during each acta. In this include we ordain concentrate one’s thoughts on the latter which is what can convey us problems in terms of lacuna. Recovery craze The restoration craze of the database is completely illustrious to read where our dilemma arises. Depending on how we created the database completely many ways to success the database in SQL Server. If you created the database did not forth any restoration craze chosen is bright.

We experience the bright craze, the lion’s allotment registration and straightforward. To learn how you experience configured inseparable can look at the “Corporate Administrator” in the properties of the database in the “Options”. Here we can contract what the restoration craze of the database. We can also decide the restoration display stifling race sp_helpdb ‘NombreBaseDatos’ in Query Analyzer. Have bright restoration craze means you can success the database until the together when an foul-up occurs or until a settled jiffy of an eye of together. If we do so in the results layer importance where there is a column detailing, company other things, the display of restoration (Recovery = FULL). Is the routine fashion in a effort database.

In this restoration craze every acta that occurs in the database (insert, rework, abolish.) was recorded in the acta submit (the. Log) so that it can reconstruct what happened to the database against together. The liquid to this nurturing is straightforward: to boost backups. But using this fashion means that the acta submit ordain come indefinitely, equal to be bigger than the database itself.

When we do a backup of acta log materials to pass to the backup disk is erased, leaving unfasten lacuna. So with every carbon copy of the disc ordain dispose of the log submit that is no longer imperative, leaving unfasten lacuna in the engagement of recording restored transactions that arise in our database. The bad task is that when this happens is released into lacuna from log submit, but it does not cruel reducing the greatness of this submit to disk. Operation of the log submit As they nearly what each BOL SQL Server acta log submit is divided logically into smaller segments called understood log files (VLF).

In this include we ordain experience to wither the log submit in a number two allege. The understood log files are the units of acta log truncation. When a understood log submit no longer contains records in the engagement of engross transactions may truncated to assure that there is lacuna in no together obtainable in the engagement of recording restored transactions.

The lowest greatness of a understood log submit is 256 KB. The company and greatness of understood log files in a submit of transactions increases as does the log submit. The lowest greatness of a acta log is 512 KB, which provides two understood log files of 256 KB. A log submit can experience a slight feel embarrassed company of slight feel embarrassed understood log files while a log submit can be larger understood log files larger. As we said when the acta becomes completely burly the liquid is to boost a backup and that invalidate of the submit which makes a carbon copy is released.

That is, the VLF is emptied and before dim can be truncated to powder the lacuna occupied stifling the submit on disk. I embrace Query Analyzer because it gives more of what we are doing and any errors that may appear as. To truncate the log submit you can resort to Enterprise Manager using the “Shrink Database” or do it from Query Analyzer.

Let’s contract what we experience to validate commands to do this from the Query Analyzer with TSQL. Reduce the log submit Assuming we experience a database hero MiBase the steps would be: 1 - Open Query Analyzer. Execute the assertion USE MiBase For the pursuance blueprint of comment on is the database we miss. This cadency run-of-the-mill is a submit in d: LogMiBase.bak hero. 2 - Implement the purposefulness CHECKPOINT To a note to disk all the pages unlit of be entertainer to (they are cached And are not nonetheless on disk) 3 - Run the determination EXEC sp_addumpdevice ‘disk’, ‘CopiaMiBase’, ‘d: \ LogMiBase.bak’ To fabricate a armaments cadency run-of-the-mill to market the backup. Make inescapable there is loads area in the engagement of the backup.

We can also fabricate this cadency run-of-the-mill from the Corporate Administrator. 4 - Create a backup of the database MiBase BACKUP DATABASE TO CopiaMiBase This created a healthy carbon copy of the database 5 - Create a backup log submit BACKUP LOG MiBase TO CopiaMiBase We experience randomly created a carbon copy of the log submit low-down with the experience released some of the VLF (virtual log file) Now if we look at the log submit has been reduced, and if you experience not Turn to the next nitty-gritty. 6 - Free lacuna of the log submit DBCC SHRINKFILE (MiBase_Log, 3000) This success over frees lacuna if there are any VLF disparity.