SQL Server Error 9002 Error and Resolution
SQL Server Error
The SQL server log files are stored in the database. But when we try to do our shrink operation, it returns error 9002. So with a direct shrink operation, we can’t resolve it.
And what should we do about it?
First, run the script below ;
First, run the script below ;
1 | dbcc sqlperf ( logspace ) |
As shown above, we can access log size and percentage slices of databases. Right-click on the database, and then click Properties on the shortcut menu. From this window, click on the proparties tab and enter DB settings.
On the left side of the screen that comes to us on this “select a page” section is located in the header of our DB settings. We will use this section “options” is the section.
After clicking on the Options tab, on the right side is the area where we can edit the properties of this tab. In this area, we need to draw our DB in the “recovery model” field into simple mode.
Simple Mode : transaction logs are not nearly kept. Log backup In the event of a failure after the last full/differential backup, there is no chance of a return through the logs.
It can be preferred in systems where data loss is not very important and where CRUD operations occur less frequently.
You can use the full or Differential backup feature to create a full backup.
In the property section of the recovery model to simple mode ‘after pulling the OK button.
Then right-click on our database and select Shrink tab from the Task tab, as shown on the screen below and click on the Files section.
After this, we see the screen below. On the shrink File screen, select File Type as log and press the OK button.
After all the operations are complete, we call new query and run our script again. Then we first look at the difference between the results and see the result of our healing.
9002 Error and Resolution
16
Finally, we will do the operation of our DB’ınızın full mode ‘to take the model of the recovery. This is done by doing the same simple mode.
Right-click our DB and select full from the recovery model and click OK.
The Most Reliable Recovery Model.
Full mode : all transactions are logged. Naturally supports all types of backups.
Point in time recovery is supported.
If you have a full or differential backup, you can use the following command:
When log backups are not taken for a long time, the swelling in the logs causes the system to slow down. You can use the log Backup feature to restore a database from a Backup that has not been backed up before.
Video production and creative agency in Cyprus.