Wiki source code of Datenbankmanagement

Last modified by Sabrina V. on 2025/01/24 07:12

version	line-number	content
1.1	1	{{aagon.priorisierung}}
	2	160
	3	{{/aagon.priorisierung}}
	4
	5
	6	{{aagon.floatingbox/}}
	7
16.1	8	Data is constantly changing in the course of day-to-day work: new data can be added, existing data can change (e.g. grow and take up more space or change location) or be deleted. Storing data in the database is demanding and requires constant maintenance.
1.1	9
16.1	10	Even if the database is working properly, there are bound to be problems over time, such as indexes becoming fragmented, logs taking up a lot of space, or the database itself simply becoming too large. The result is often a drop in performance, longer load times when retrieving data, or unpredictable behaviour of the database. In short, the database system no longer works as it should, or cannot be started at all until more space is created.
1.1	11
16.1	12	To counteract this, you should always take the time to maintain your existing database system. Below are some recommendations and hints on how to maintain your ACMP database and some useful countermeasures you can take.
1.1	13
	14	{{aagon.infobox}}
16.1	15	The following examples are based on working with Microsoft SQL Server Management Studio. We recommend that you install the application that allows you to connect to a server to perform database maintenance. The Studio does not need to be installed on the server running the SQL Server instance!
1.1	16	{{/aagon.infobox}}
	17
16.1	18	= Countermeasures: Reduce the size of the database and files =
1.1	19
16.1	20	Shrinking a database is usually done when there is little or no space left on the disk where the files are stored. Only the data can be shortened by free space. This means that partially used areas cannot be completely freed up (the theoretical minimum is the space required for the data).
1.1	21
16.1	22	The purpose of reducing the size is therefore to free up space in the database. Either the database or the files it contains can be shrunk.
1.1	23
	24	{{aagon.warnungsbox}}
16.1	25	It is essential that you make a database backup before you reduce the size of the database or individual files. If you have not yet made a backup, you must do so urgently. You alone are responsible for ensuring that the database can be restored in the event of an error. Read here how to create an [[ACMP backup >>doc:.Vollständiges ACMP Backup durchführen.WebHome]] and how to [[restore>>doc:.Vollständiges ACMP Backup einspielen.WebHome]] it.
1.1	26	{{/aagon.warnungsbox}}
	27
16.1	28	== Shrink database ==
1.1	29
	30	{{aagon.infobox}}
16.1	31	When shrinking a database, make sure that applications such as ACMP or others are not accessing the database. If this is not the case, the shrink will not be optimal, will be very slow or will not be performed at all. So make sure that the relevant services do not access the ACMP database. Use the Activity Monitor to check for possible processes and stop them.
1.1	32	{{/aagon.infobox}}
	33
16.1	34	The size of a database is reduced by reducing the size of the database files. This applies to the areas of the database files that are completely free (see also [[Defragmenting the Indexes>>doc:\|\|anchor="HIndizesDefragmentierung"]]). If you only want to reduce the size of individual database files, use the [[//Reduce files//>>doc:\|\|anchor="HDatenbankverkleinern"]] option.
1.1	35
16.1	36	After you have closed all relevant applications, processes and sessions, go to the Microsoft SQL Server Management Studio left-hand navigation and select the database you want to shrink. Right-click to open the context menu and click //Tasks// > //Contract// > //Database//.
1.1	37
	38	{{figure}}
7.1	39	[[image:66_Datenbank_Datenbank verkleinern_690.png\|\|data-xwiki-image-style-alignment="center"]]
1.1	40
	41	{{figureCaption}}
16.1	42	Shrink database
1.1	43	{{/figureCaption}}
	44	{{/figure}}
	45
16.1	46
	47	In the window that opens, you can see the current information (for example, the selected database and its size). In the Database size section, you can see the current allocated and free space. Check the Shrink action box if you want to reorganise the files before freeing up unused space. Add a percentage to this action to indicate the maximum amount of space available for the files after they have been shrunk.
1.1	48
	49	{{aagon.infobox}}
16.1	50	This option can have a negative impact on performance.
1.1	51	{{/aagon.infobox}}
	52
	53	{{aagon.infobox}}
16.1	54	Recommended: Do not make the database file too small and leave a possible buffer for any further actions. To ensure that the work process is not interrupted when the database is brought back online, you should always include some reserve space (e.g. 200 MB). The following applies The more space you have to free up, the longer an action will take!
1.1	55	{{/aagon.infobox}}
	56
16.1	57	Click //OK// and the database reduction will be performed.
1.1	58
16.1	59	== Reducing the size of files ==
1.1	60
16.1	61	If you do not want to reduce the size of an entire database directly, but only selected database files and/or groups of files, you can make such changes separately. This can be useful if, for example, you can identify which of your database files are very large and you want to reduce their size. In this case, the size of the database will be reduced by reducing the size of individual files or groups of files.
1.1	62
	63	{{aagon.infobox}}
16.1	64	Please note that this procedure does not free up any allocated database space. To do this, you must include all the database files, which can be done using the [[Shrink database>>doc:\|\|anchor="HDatenbankverkleinern"]] option.
1.1	65	{{/aagon.infobox}}
	66
16.1	67	In Microsoft SQL Server Management Studio, open the database in which you want to shrink files. Right-click to open the context menu and select //Tasks// > //Shrink// > //Files//.
1.1	68
	69	{{figure}}
7.1	70	[[image:66_Datenbank_Datei verkleinern_690.png\|\|data-xwiki-image-style-alignment="center"]]
1.1	71
	72	{{figureCaption}}
16.1	73	Reducing the size of files
1.1	74	{{/figureCaption}}
	75	{{/figure}}
	76
16.1	77	In the //Database// row you can check that you have selected the correct database. In this case you should see the name of the ACMP database. In the pictures above this is called "ACMP". You can now select the type under the //Database Files and File Groups// heading.
1.1	78
	79	{{aagon.infobox}}
16.1	80	Depending on the file type selected, the user interface may vary slightly as not all fields are always available.
1.1	81	{{/aagon.infobox}}
	82
16.1	83	Select the file type and subsequent configurations. Review the current allocated and available disk space to check for any changes.
1.1	84
16.1	85	Under the Reduction action, select how you want to manage the space:
1.1	86
16.1	87	\|Option\|Explanation
	88	\|Release unused storage space\|With this action, you release the unused storage space.
	89	\|Reorganise pages before releasing unused storage space\|Here you can reorganise the pages before releasing unused storage space and reduce the size of the files by entering a value. The value to be entered here must be based on the minimum value and cannot be less than this.
1.1	90
	91	{{aagon.infobox}}
16.1	92	MSSQL manages a database file in //extents//. Extents are blocks of memory consisting of eight pages. A page is an area of 8KB. This is the smallest unit of memory that SQL Server can store. Therefore, an extent consists of 64KB.
1.1	93	{{/aagon.infobox}}
	94
16.1	95	\|Option\|Explanation
	96	\|Clear file by migrating its dates to other files in the same file group\|If you select this option, all data from the specified file will be moved to other files in the same file group. The clear file can then be deleted.
1.1	97
16.1	98	Click //OK// to reduce the size of the file.
1.1	99
	100
16.1	101	= Indexes Defragmentation =
1.1	102
16.1	103	Fragmentation is the scattered storage of logically related blocks of data from a file system on a hard disk. Fragmentation of indexes is caused by the constant changing of data (insert/modify/delete), which can create gaps in the database file. An index sorts the data logically, so the order is maintained even when fragmentation occurs. Over time, however, the order can become out of sync, which is why a database takes up more space and access becomes slower. To solve this problem effectively, a regular maintenance script can provide a remedy.
1.1	104
16.1	105	To do this, you must first open Microsoft SQL Server Management Studio and connect to the server by logging in with your user credentials.
1.1	106
	107	{{aagon.warnungsbox}}
16.1	108	Make sure that your user account has the appropriate rights to perform a defragmentation.
1.1	109	{{/aagon.warnungsbox}}
	110
16.1	111	Click //Connect// and the Management Studio will open in the background. Navigate to the Object Explorer on the left hand side of the menu bar and open the ACMP database.
1.1	112
	113	{{aagon.infobox}}
16.1	114	Make sure you select the correct database from the drop down box to avoid making changes to the wrong database.
1.1	115	{{/aagon.infobox}}
	116
16.1	117	Executions Right click on the ACMP Database and select //New Query// from the context menu that opens.
1.1	118
	119	{{figure}}
7.1	120	[[image:66_Datenbank_Aufruf einer neuen Abfrage_478.png\|\|data-xwiki-image-style-alignment="center"]]
1.1	121
	122	{{figureCaption}}
16.1	123	Insert new query
1.1	124	{{/figureCaption}}
	125	{{/figure}}
	126
16.1	127	An editor for the new query opens in the main view of the Management Studio. Insert the following SQL script:
1.1	128
	129	##DECLARE @database NVARCHAR(100)= DB_NAME()
	130	EXEC ap_index_defrag 0,0##
	131	##DBCC SHRINKDATABASE (@database)##
	132	##EXEC ap_index_defrag 0,0##
	133
	134	{{figure}}
7.1	135	[[image:66_Datenbank_Ausführung einer neuen Abfrage_837.png\|\|data-xwiki-image-style-alignment="center"]]
1.1	136
	137	{{figureCaption}}
16.1	138	Execute query SQL script in query
1.1	139	{{/figureCaption}}
	140	{{/figure}}
	141
	142	{{aagon.infobox}}
16.1	143	Make sure that you have selected the correct entry from the available databases (here: ACMP), so that you can run the query on the correct database.
1.1	144	{{/aagon.infobox}}
	145
16.1	146	Then click on //Executions//. Two tabs (Results and Messages) will open at the bottom of the Editor, displaying messages after each execution (whether successful or not). Close Microsoft SQL Server Management Studio when you are finished.
1.1	147
16.1	148	The command rebuilds the indexes of the ACMP database and also performs a reduction of the database..
1.1	149
	150	{{aagon.warnungsbox}}
16.1	151	Running the above SQL script in combination with [[backup>>doc:.Vollständiges ACMP Backup durchführen.WebHome]] should be done regularly (e.g. once a month) to avoid possible database problems.
1.1	152	{{/aagon.warnungsbox}}
	153
16.1	154	= ACMP database too large due to log files =
1.1	155
16.1	156	The ACMP database can also fill up quickly and become too large because of the log files.
1.1	157
16.1	158	The log files are important for two reasons:
1.1	159
16.1	160	Firstly, they ensure that the data remains consistent when you make changes. This means that the changes are either made in full or not at all (e.g. in the event of an error or power failure). The latter means that the provisional changes are rolled back until an error occurs, with the relevant information about what was rolled back in the event of an error stored in the log file. The rollback is done transparently in the background. If the log file fills up, SQL Server will stop starting.
1.1	161
16.1	162	On the other hand, log files are also used for rollforward, which is the reverse function of rollback. Roll-forwards are performed when you import a full backup of the database and then want to restore the latest changes since the backup was created. This is only possible in the //Full Recovery// model.
1.1	163
16.1	164	The log files are located under //MSSQL\Data//. However, the database and the log file should each be on a separate disk, which not only separates them but also improves performance. The files are saved as MDF or LDF files, where the MDF files contain the actual data and are therefore often larger than the LDF file. The LDF file is a support file that stores information about the transaction logs. Check both the location and the size of the files.
1.1	165
16.1	166	If you have not set any or incomplete settings for the log files, you can change these in the properties of the ACMP Database. To do this, first exit the ACMP server service.
1.1	167
16.1	168	Then open the Microsoft SQL Server Management Studio, log in using the login information and then select the ACMP Database in the navigation on the left. Execute a right-click on the ACMP Database and click on Properties in the context menu that opens. The database properties for the database open. Select the Options menu item. The first column in the view is the sort order. The first column in the view is the sort order. Select the //Latin1_General_CI_AS// collation that we recommend (see also the graphic below). This collation is used for both Unicode and non-Unicode. In the second position//, //define a //Recovery Model//. The [[Recovery Model>>https://learn.microsoft.com/de-de/previous-versions/sql/sql-server-2008-r2/ms189275(v=sql.105)?redirectedfrom=MSDN) ]] is an option with which you can define how a los is handled:
1.1	169
16.1	170	\|(% style="width:136px" %)Recovery Model\|Explanation
	171	\|(% style="width:136px" %)Full\|All database changes are logged. There are no time or size restrictions. Storage and backup requirements are significantly increased.
	172	\|(% style="width:136px" %)Bulk logged\|As with the full recovery model, a log backup is created, but this model uses slightly less storage and keeps the backup smaller. It can be used, for example, for an import with a large number of records.
	173	\|(% style="width:136px" %)Simple\|The log memory is automatically freed as this model can only write to files up to a certain size. The oldest data is deleted when the size is reached. This model is often used for the ACMP database.
1.1	174
16.1	175	If "Full" or "Bulk logged" is set in the drop-down field, change the entry to "Simple".
1.1	176
	177	{{figure}}
7.1	178	[[image:66_Datenbank_Datenbankeigenschaften_698.png\|\|data-xwiki-image-style-alignment="center"]]
1.1	179
	180	{{figureCaption}}
16.1	181	Customize recovery model
1.1	182	{{/figureCaption}}
	183	{{/figure}}
	184
16.1	185	Then click //OK// and the window will close.
1.1	186
16.1	187	Then reduce the size of your database. To do this, follow the steps in the section [[Shrinking the database>>https://learn.microsoft.com/de-de/previous-versions/sql/sql-server-2008-r2/ms189275(v=sql.105)?redirectedfrom=MSDN) ]].