Changes for page Datenbankmanagement

Summary

• Page properties (1 modified, 0 added, 0 removed)

Details

Page properties

Content

...	...	@@ -9,7 +9,7 @@
9	9
10	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.
11	11
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.
	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.
13	13
14	14	{{aagon.infobox}}
15	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!
...	...	@@ -22,13 +22,13 @@
22	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.
23	23
24	24	{{aagon.warnungsbox}}
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.
	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.
26	26	{{/aagon.warnungsbox}}
27	27
28	28	== Shrink database ==
29	29
30	30	{{aagon.infobox}}
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.
	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.
32	32	{{/aagon.infobox}}
33	33
34	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="HIndexesDefragmentation"]]). If you only want to reduce the size of individual database files, use the [[//Reduce files//>>doc:||anchor="HReducingthesizeoffiles"]] option.
...	...	@@ -36,7 +36,7 @@
36	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//.
37	37
38	38	{{figure}}
39		-[[image:66_Datenbank_Datenbank verkleinern_690.png||data-xwiki-image-style-alignment="center"]]
	39	+[[image:66_Datenbank_Datenbank verkleinern_690.png||data-cmp-info="10" data-xwiki-image-style-alignment="center"]]
40	40
41	41	{{figureCaption}}
42	42	Shrink database
...	...	@@ -67,7 +67,7 @@
67	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//.
68	68
69	69	{{figure}}
70		-[[image:66_Datenbank_Datei verkleinern_690.png||data-xwiki-image-style-alignment="center"]]
	70	+[[image:66_Datenbank_Datei verkleinern_690.png||data-cmp-info="10" data-xwiki-image-style-alignment="center"]]
71	71
72	72	{{figureCaption}}
73	73	Reducing the size of files
...	...	@@ -74,7 +74,7 @@
74	74	{{/figureCaption}}
75	75	{{/figure}}
76	76
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.
	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.
78	78
79	79	{{aagon.infobox}}
80	80	Depending on the file type selected, the user interface may vary slightly as not all fields are always available.
...	...	@@ -108,16 +108,16 @@
108	108	Make sure that your user account has the appropriate rights to perform a defragmentation.
109	109	{{/aagon.warnungsbox}}
110	110
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.
	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.
112	112
113	113	{{aagon.infobox}}
114	114	Make sure you select the correct database from the drop down box to avoid making changes to the wrong database.
115	115	{{/aagon.infobox}}
116	116
117		-Executions Right click on the ACMP Database and select //New Query// from the context menu that opens.
	117	+Executions Right click on the acmp Database and select //New Query// from the context menu that opens.
118	118
119	119	{{figure}}
120		-[[image:66_Datenbank_Aufruf einer neuen Abfrage_478.png||data-xwiki-image-style-alignment="center"]]
	120	+[[image:66_Datenbank_Aufruf einer neuen Abfrage_478.png||data-cmp-info="10" data-xwiki-image-style-alignment="center"]]
121	121
122	122	{{figureCaption}}
123	123	Insert new query
...	...	@@ -132,7 +132,7 @@
132	132	##EXEC ap_index_defrag 0,0##
133	133
134	134	{{figure}}
135		-[[image:66_Datenbank_Ausführung einer neuen Abfrage_837.png||data-xwiki-image-style-alignment="center"]]
	135	+[[image:66_Datenbank_Ausführung einer neuen Abfrage_837.png||data-cmp-info="10" data-xwiki-image-style-alignment="center"]]
136	136
137	137	{{figureCaption}}
138	138	Execute query SQL script in query
...	...	@@ -140,20 +140,20 @@
140	140	{{/figure}}
141	141
142	142	{{aagon.infobox}}
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.
	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.
144	144	{{/aagon.infobox}}
145	145
146	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.
147	147
148		-The command rebuilds the indexes of the ACMP database and also performs a reduction of the database..
	148	+The command rebuilds the indexes of the acmp database and also performs a reduction of the database..
149	149
150	150	{{aagon.warnungsbox}}
151	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.
152	152	{{/aagon.warnungsbox}}
153	153
154		-= ACMP database too large due to log files =
	154	+= acmp database too large due to log files =
155	155
156		-The ACMP database can also fill up quickly and become too large because of the log files.
	156	+The acmp database can also fill up quickly and become too large because of the log files.
157	157
158	158	The log files are important for two reasons:
159	159
...	...	@@ -163,19 +163,19 @@
163	163
164	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.
165	165
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.
	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.
167	167
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. 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:
	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. 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:
169	169
170	170	|(% style="width:136px" %)**Recovery Model**|**Explanation**
171	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	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.
	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.
174	174
175	175	If "Full" or "Bulk logged" is set in the drop-down field, change the entry to "Simple".
176	176
177	177	{{figure}}
178		-[[image:66_Datenbank_Datenbankeigenschaften_698.png||data-xwiki-image-style-alignment="center"]]
	178	+[[image:66_Datenbank_Datenbankeigenschaften_698.png||data-cmp-info="10" data-xwiki-image-style-alignment="center"]]
179	179
180	180	{{figureCaption}}
181	181	Customize recovery model