Background
In the company where I work, a situation arose when it became necessary to cut the base, i.e. delete all documents and movements before a certain date. At this point, the database weighed almost 350GB, and it was very difficult to work with it. The performance of 1C also suffered and routine operations with the database took a very long time to complete.
The decision was made - we cut off the base! After a lot of testing of standard folding treatments, I was disappointed in them. I spent more than two weeks alone with the computer, cutting off the test base. Such deadlines are simply unacceptable in a production database, especially since users work in the database 6 days a week. Somehow it was necessary to get out of the deadlock situation. We decided to outsource the task. But we were not happy here either, the cost of the work was high and it took a lot of time to complete.
IN general question remained unresolved and returned to solving the problem on their own.
I had to resort to extreme measures, I disconnected from all other tasks and took up the task of collapsing the base.
And soon the issue was resolved - they cut the base in 2 days, reducing it by 70%.
Trimming a UT database with a capacity of 350 GB in 2 days.
I decided to perform the task using this algorithm:
Create a copy of the working database. In the copy, form the balances for the required date, delete all documents and movements before this date. Next, from the working database, transfer all new documents (created since the copy was created) to new base. Let users into the new database.
The task was divided into 5 subtasks:
1. Create an exchange plan so that after trimming the database, all new documents can be transferred to the new database.
2. Form the balances on the roll-up date.
3.Delete all documents and movements before the date of reconciliation.
4.Upload documents from the current database to the new database.
5.Replace the working base with a new (cut) base.
The preparatory stage took about two weeks: creating an exchange plan, searching for tools to create balances and clear registers, testing mechanisms.
Database collapsing process.
1. Formation of balances on the roll-up date.
The remainder was formed using standard processing “ConvolutionBase.epf”.
To save time, the balances were formed in the working database in work time. Because the balances are formed by the documents “Adjusting register entries” and processing establishes inactive movements in the registers, then this procedure is harmless for the database.
In processing, I set a limit on the number of lines in one document to 50,000.
I did it slowly, and within 1-2 days everything was formed (about 400 documents turned out).
Note:
- if on the roll-up date there are documents “Adjustment of register entries” not related to the base roll-up, then it is better to move them to a later date so as not to accidentally mix them up later.
- when forming balances, it is better to view all registers, because It happens that you do not need initial balances for all registers. Sometimes there are situations when the register is no longer used anywhere. This will ultimately save you time.
2. Create full plan exchange for unloading into an identical configuration (can be done in parallel with the formation of residues).
3. During non-working hours, clear the objects registered in the exchange plan and make a copy of the database with regulations disabled.
4.Deleting documents and clearing registers.
This operation is the slowest and to speed up this operation you need to use direct SQL queries. Similar tools can be found on Infostart. There is one more point: if we delete data in this way, the objects will be deleted quickly, but without control of referential integrity. Accordingly, lines like “Object not found...” will appear in our documents and other documents.
My task was to leave these documents unchecked for informational purposes.
For this purpose, a processing was written that removed from processing and transferred to the roll-up date all documents involved in movements in register adjustment documents.
After which, using processing to delete documents and movements through SQL queries, delete everything before the roll-up date (document journals, documents, accumulation registers, information registers, etc.).
Note:
- according to information registers, only periodic ones need to be cleared. If the register is not periodic, then it will most likely be cleared completely.
6. Activate document movements for adjusting register entries by processing “Base Convolution.epf”. In this case, the processing of these documents transfers to the previous date (this is convenient for reconciling balances in two databases).
8.Check the correctness of entering balances and correct them (you can involve accounting staff for this purpose)
9. Transfer documents from the working database to the new database. This procedure can be done immediately after creating a copy. Those. If many documents are created in the database every day, then it is better to transfer documents in parallel so as not to waste additional time on this.
10.Allow users to the new database. To do this, it is better to rename the databases on the 1c server so that the transition is painless for users. Those. old database we call it whatever we want and rename the new (collapsed) database to the old one.
Note!
As you know, when objects are directly deleted, referential integrity is not checked.
Accordingly, after deleting objects, objects that refer to non-existent objects remain in the current database (“Object not found”).
My goal was not to have these objects in a collapsed database. But broken links can negatively affect various types of exchanges between databases and other processes.
That's why I moved them to the collapsed base, but after I collapsed the base.
1. Made exchange rules that transfer documents (only number, date in unposted form).
2. Transferred all the documents that were found in the documents for entering initial balances.
This procedure can be done after rolling, but it is advisable to prepare the tools in advance.
Database collapse is a necessary procedure that a huge number of 1C software users encounter. In the process of work, the database becomes overgrown with a large number of documents that are of no value in a given period of time. In order to exclude them from the working field, but at the same time preserve them for the possibility of referring to them in the future, they are minimized. In this material we will answer the question of what convolution is and how it is done correctly this operation.
What is the convolution for?
Work with 1C information databases in many companies is carried out over a long period of time, which in turn leads to an increase in the size of the database. Its growth is even faster in large organizations that carry out a large number of transactions. In addition, often the same database serves several organizations. In this case, its size can reach large values. In some cases, the database can reach 4-5 gigabytes in size in just a few years of operation.
This state of affairs leads to some difficulties, such as:
- Slowing down the speed of working with the program;
- Increased time to create a database backup;
- Accumulation of a large number of old documents that can interfere with the user’s work with the program.
There is also a critical volume of the database, at which it becomes impossible to work with it at all, since the program simply will not open. For file database the critical volume is 12 gigabytes.
In order to avoid all the problems listed above, you need to collapse the 1c database. The essence of this procedure is to reduce the work volume by archiving and arranging past documents, as well as removing information that is unnecessary.
How is convolution done?
To roll up the database, you need to select the accounting subsection called “Administration”. Then, on the action panel you should find the “Service” group. In it you will see a button called “Other commands...”. You just need to click on it, after which it will appear on the screen special form, which will display a list of sequential operations. The fact is that in order for 1C database convolution to be performed correctly, this procedure should be carried out in several stages. After successfully completing each stage, the corresponding item in the list is marked in green.
Convolution of the 1c database is carried out in six steps:
- Creating a backup;
- Operation setup;
- Formation of residues;
- View transaction;
- Examination;
- Control of old documents.
Let's look at each item separately in more detail.
Creating a Backup
Before performing any operations on the database, you should perform a backup to preserve information in case of unforeseen circumstances. Convolution information base is no exception, which is why its first step is creating a backup copy.
In order to start the procedure, check the appropriate box. Below it there are two fields:
- Administrator - ITS username;
- Password for using ITS services.
Below you will be asked to select the directory where the backup will be saved.
After this, the program will give you warnings that for successful backup the program must be restarted. As a result, after some time, the duration of which depends on the size of the database, the backup copy will be created, the menu item will highlight green, and you can move on to the next operation.
Settings
This stage is the most important and decisive, it should be approached most carefully. You will need to configure the procedure being carried out. It includes defining the following parameters:
- The period at the beginning of which the remains will be curtailed;
- The number and names of organizations in relation to which the database will be rolled up;
- Date prohibited from changing data.
It is recommended to carry out the procedure immediately for all organizations served by this database. However, if this is unacceptable for some reason, you can minimize the 1C database for certain organizations. In this case, if the need arises, the roll-up for the remaining organizations can be done separately later. It is also recommended to roll up to the beginning of last year; this is the most effective option.
Formation of residues
The formation of residues is an operation of detailed settings planned operation. In this menu, if necessary, you need to determine those registers that should be trimmed. They are grouped into three categories:
- Accounting registers;
- Cumulative registers;
- Information registers.
View transaction
This menu item allows the user to examine prepared documents so that, if necessary, they can be returned back before deletion. In addition, here you can open any document and make changes to it.
Examination
This stage allows you to make reports on accounting, accumulation registers and information registers. You can also get all the information on accounting and tax accounting. On the right side of the screen there is a button that allows you to configure a standard report.
Deleting old documents
The name of this item speaks for itself. Here you can delete all old documents that you no longer need. Unlike all other information, these documents are permanently deleted.
To avoid deleting documents that you may need in the future, you should pay special attention to the previous two points
Conclusion
Database collapse is the most important procedure, which greatly simplifies work in the 1C program. In this material, we examined in detail how to collapse a 1c database. The procedure is carried out in several stages and will not cause difficulties for a trained user.
First, a little theory.
The 1C:Enterprise 8 program can be operated in two modes:
- file(the database is located on the user’s computer or local network),
- client-server(the database is located on a separate server).
This article is relevant only for the first option. In the case of file hosting, the program has a significant limitation - the size of one database table cannot exceed 4 GB. As soon as the size of any database table (and in the case of the 1C: Accounting 3.0 configuration, the heaviest table is the accounting register) exceeds 4 GB, the database will stop starting, and when approaching this threshold you will experience a noticeable decrease in program performance (in other words, "brakes" will appear).
So, folding allows you to reduce the size of database tables (primarily the accounting register). The base folding operation is as follows:
- the date on which the database needs to be collapsed is determined (as a rule, this is the beginning of the year),
- the program determines the balances for each accounting account on this date and enters these balances by creating documents Operation,
- all movements in all system registers before the specified date are canceled (except for those selected when setting up convolution),
- documents entered before this date are deleted (if the movements of these documents are completely cleared) or marked for deletion,
- documents entered after the specified date remain in their original form in the system.
This operation allows you to significantly reduce the size of the accounting register in the program.
How to make a convolution
In chapter Administration open convolution processing:
The following window appears:
Set the sign Create backup copy , indicate the user data under which you want to make a copy, and the directory where to save the copy. Click Further.In the window that appears, select Yes:
We encounter a platform error.
When restarting, the situation repeats. Let's make a copy manually. Let's go to the configurator:
Select the path to save the copy and give it a name:
A message appears:
We return to convolution processing. We remove the sign of creating a copy. Click Further:
On the next screen you need to select the year at the beginning of which the rollup is made. It is also advisable to set the sign Set ban date so that nothing will be entered into the database before 2016. From the list of organizations, you can select only a few to collapse by, or you can collapse the database across all organizations:
On the next screen we see how the program plans to process the database tables (the system suggests collapsing some tables, leaving others unchanged). For example, many information registers and accumulation registers related to VAT will remain unchanged. This is not critical for us; the priority is to reconcile the accounting register.
In the next window the program displays a list of documents Operation, which will be created to enter the initial balances as of December 31, 2015 for all accounting accounts that have balances. Let's move on.
The next screen displays a summary table with an analysis of the correctness of the initial balances at the beginning of 2016. Correctness is checked by comparison with opening balances at the moment before the convolution. In the above screenshot we see that there are no deviations.
Just in case, let’s also check the reconciliation table for information and accumulation registers:
On the next screen, the program warns that the procedure for deleting documents before 2016 has begun. We agree
After deleting the documents, a message appears indicating that the rollup was successful.
Draw your attention: Due to the fact that movements in the accumulation registers regarding VAT and in many information registers remained unchanged, the documents that made movements in such registers also remained in the system.
Don’t let this confuse you - our goal was to “simplify” the accounting register, and we achieved it.
If necessary, you can go through a full (and free) course on the website.
Published 01/13/2017 09:22 Views: 6503In January, many people have a desire to start everything from scratch, and I very often hear from clients and readers the question: “Can we create a new 1C database, leave all mistakes in the past and start keeping records correctly? Is there some kind of automatic ability to “cut” the 1C database at the beginning of the year?” Such a desire is quite understandable and understandable, because someone received a database in not very good condition from a previous accountant, and someone simply studied and made many mistakes, but now they have the necessary knowledge, skills and the desire to work correctly, but they don’t want to drag constantly carrying the burden of past mistakes. In the program 1C: Enterprise Accounting 8 edition 3.0 there is a function such as information base roll-up, which allows you to automatically generate balances at the beginning of the selected period and delete old documents.
Why is 1C database convolution convenient? If you simply create a new empty database and start manually entering balances for all accounts, you will spend quite a lot of time on this work, because you will need to transfer not only the balance amounts, but also the data of all directories necessary for the work (counterparties, individuals, employees, nomenclature, etc.). When reconciling, the program itself will automatically generate account balances, which you can adjust if necessary, all elements of the directories will remain in the database, and old documents will be deleted. If you also want to “clean up” the directories, you can manually remove unnecessary elements. This will take much less time than entering all the necessary elements into a new database.
An important nuance: after the reconciliation, documents before the selected date in your current database will be deleted, but you definitely need to save this information in another database, because it may still be needed for further work. Usually, I create a second identical database (in the list of databases in the name you can indicate that one of the databases belongs to the period, for example, before 2017, and the other from 2017), and then you can collapse one of them, in which Accounting will be carried out for the current year. The second database simply serves as an archive of data from past years, which can be accessed at any time.
After we have dealt with the copies of the database and checked the data on the SALT, we go to the “Administration” section and select the “Information Base Reduction” item.
First of all, the program will again prompt you to make a copy of the database. If you already have a copy, then you can skip this item (uncheck the box). If you don’t have a copy yet, don’t miss it under any circumstances!
Then you need to indicate the year at the beginning of which we will form the balances. As is correct, this is the current year, but once in my practice I encountered a request to leave the data for the last three years in the program during the roll-up (at that time, the records in the database had been kept for more than 5 years). If there are several organizations in the database, then we note whether the rollup will be carried out for all or only for the selected ones.
At the next stage, it is necessary to indicate the data from which registers we will collapse. If you do not have any special wishes, then all settings can be left unchanged.
Then we see on the screen a list of transactions that were automatically generated to reflect balances at the beginning of the selected period. It is possible to open each operation for viewing and, if necessary, edit the data. But it will be possible to carefully study the generated transactions even after the convolution is completed.
At the next stage, the data is verified: account balances before and after reconciliation are compared. Ideally, the amounts should match. If any discrepancies arise, be sure to print out the report generated by the program and look in detail at each account for which differences arose.
At the last stage of the rollup, old documents are deleted; this process can take quite some time. for a long time, especially if records in the program have been maintained for many years.
The program will then report the successful completion of the convolution.
Of course, convolution is not a “magic pill” and will not quickly solve all problems if the records in the database have not been kept very carefully for a long time. After the reconciliation, you may need to adjust the balances or figure out why not all documents were automatically deleted. In this case you will have to spend a certain amount of time to bring the database to an optimal state: work with documents for entering balances and analyze why the program could not delete some old documents. The only thing I want to ask you is to make copies of the information base! And if the state of the database is very neglected or the volume of entered documents is large, then it is better to seek help from experienced specialists to perform the rollup.
During the process of collapsing an information database, the following actions are performed in it:
- documents are generated for entering balances on the date when the reconciliation is carried out;
- documents and movements in registers that were in the program up to and including the roll-up date are deleted.
Collapsing the information database allows you to:
- speed up the system;
- reduce the size of 1C IBD.
ATTENTION! Before you begin collapsing the information base, you must:
- perform a preliminary test rollup on a copy of the working 1C IDB;
- If the test roll-up is completed successfully, only then can you begin to roll-up the working information database, having first backed it up.
To collapse the information database, processing for the configuration (revision 1.6), which is on disk, is used. For edition 2.0 this processing is not suitable because it does not contain the “Adjustment of register entries” document. You can order on our website.
Order a package
Step-by-step instructions for rolling up a base
- First you need to create a backup copy of the information database. Then log into the database using the mode 1C: Enterprise, and check whether all documents have been completed at the end of the month on the roll-up date.
- Next, you need to make a Turnover Balance Sheet report for the period for which you plan to roll up the base.
- Using the “Open” command (File menu), load external processing Base rollup 2.0.
- The database is collapsed after clicking the corresponding button on the bottom panel, during which objects are deleted for the period specified in the processing. It should be noted that the process may take quite a long time to complete. After the 1C IDB rollup has been carried out, it is under no circumstances possible to correct and repost documents for the rolled up period.
It is also impossible not to say that if there were errors during the convolution process, due to which the removal of objects was interrupted or not completed completely, this may lead to the fact that the totals for some registers will be disabled. This fact, in turn, makes it impossible to obtain totals for some registers. You can view the list of registers for which totals are disabled on the “Registers with disabled totals” tab. Here, if necessary, you can include results.
If the convolution process was interrupted for any reason, then the list of documents for entering balances can always be restored using the “Fill out the list” button on the “Documents for entering balances” tab, specifying the date interval. - The last stage is deleting marked objects. It can be carried out if all the above-described convolution steps have been successfully completed.
- In the “Operations” menu, select the “Delete marked objects” command.
- The upper part of the window contains a dialog with a list of objects detected in 1C IBD and marked for deletion. The user can open and view any of them. Using flags, the user can also determine which objects need to be checked for deletion. The “Control” button will help you determine whether there are links to objects to be deleted. Having carefully checked the objects to be deleted and found no errors, you need to start the process of deleting objects by clicking the “Delete” button.
At the end of the database rollup, you need to make sure that everything went correctly. To do this, open the Turnover Balance Sheet and compare it with the one that was saved before the roll-up. If they are identical, then the base folding was performed correctly.
Loading...
