SQL problem with zenOn 6.21 - huge project-log file

[herberto]

The project-log file (SQL Server Database Transaction file) in the SQL directory of the project constantly grows and requires several GigaBytes on the harddisk

Before executing the shrink action (either with the SQL-Manager Software or via the batch file) it is recommended to create a backup of the database!!

Under very specific circumstances it can happen that the shrink action (especially doing it via the batch file) leads to a corrupt Log-File and thus the Database itself can not be read anymore.

Due to some reasons the SQL-Log-File of a database can reach indeed a very huge size (this behaviour belongs to default settings of the MS SQL Server).

However, it is possible to shrink this file (Note: do not delete this Log-File!! Otherwise important links can get lost and the project itself would be corrupt).

On the zenOn DVD there exists a SQL-Manager Software which can handle this issue (see Additional_Software\MSSQL_Express\ -> SQLServer2005_SSMSEE.msi)


Please follow the steps below after you have installed the software mentioned above:

1. create a project-backup (just for safety)
2. start the program SQL Server Management Studio Express
3. connect to the SQL Server (probably SQL Server (ZENON_DEV))
4. select the database (GUID) which should be shrinked (i.e. 0b1b379e-7a6d-43b6-9aa6-39cc5065c984)
5. right-click at this database and select properties ("Eigenschaften")
6. go to options ("Optionen") and at the recovery model ("Wiederherstellungsmodel") choose simple ("einfach") and close this dialog
7. right-click at the database again, choose tasks -> shrink database -> files ("Aufgabe -> Datenbank verkleinern -> Dateien")
8. select at datatype protocol (Project_Log will be automatically choosen at filename)
9. after pressing OK the Log-File will be shrinked (this process needs about 2-3 seconds -> depends on the size)

[herberto]

Additionally to the posted solution above, there exists another procedure to handle this issue.

In the attachment you can find a batch-file (SQL Helper.zip) which also can shrink the Project.ldf file (via SQL commands). Thus an installation of the der SQL-Manger Software would not be necessary.

Before executing the batch file it is necessary to modify them (i.e. with the use of notepad):

shrinklog.bat:

-> Default: "ZENON_DEV"
-> If the zenOn SQL Instance was given a different name as the "ZENON_DEV" it has to be replaced



shrinklog.sql:

USE [7bc49c84-feb6-4ba7-890d-da2ec247239b]
-> enter the GUID of the the project here

BACKUP LOG [7bc49c84-feb6-4ba7-890d-da2ec247239b] WITH NO_LOG
-> enter the GUID of the the project here



Output.log:
-> This file will contain the actions (logs) of the shrink-action


Note: To find out the which GUID the project has -> (Project -> General -> GUID)

[herberto]

Before executing the shrink action (either with the SQL-Manager Software or via the batch file) is recommended to create a backup of the database!!

Under certain circumstances it can happen that the shrink action (especially doing it via the batch file) leads to a corrupt Log-File and thus the Database itself can not be read anymore.

[markclemens]

there also exists a utility with a graphical userinterface for shrinking existing zenon databases in both the MSDE (Sql Server 2000) and the SQL Server 2005 express.

Again, mind the recommendations regarding project backups in the other posts in this thread.

please see for the latest version of this utility, this post:

http://www.copadata.com/forums/showp...47&postcount=8

[markclemens]

in order to prevent the SQL LOG file from growing again, after the database has been shrunken, you can set the recovery model of the project (sql) database from "full" to "simple", using e.g. the SQL Server 2005 Management Studio Express (installable from the zenon DVD)

With zenon 6.22 SP1, the recovery model for new databases (zenon projects) will also be set to "simple" until Microsoft has provided a resolution for this issue.

Setting the recovery model to "simple" does not affect the project itself. Also still enough recovery information is stored, to get the SQL database in a running state should the sql server be terminated unexpectedly.

With the recovery model set to "simple" the SQL LOG file (*.ldf ) will not get much larger as the database .mdf file itself.

[hansvanzijp]

Hello Mark,

Is it possible to extend the db_maintenance tool so that the recovery model can be changed with that tool? Because there is a direct link with the zenon projects it would be easier to do it with the tool then using the GUID's in SQL manager.

Kind regards,

Hans van Zijp
SigmaControl

[markclemens]

I think this would not be a problem. I will forward the suggestion!

[markclemens]

Please find below the updated db_maintenance program, which will also set the recovery model of the shrinked database to simple.

DB_Maintenance.zip

Remember to also make a backup of the zenon project, after this operation!

(should you restore a previous zenon backup from before the shrink action, the recovery model would be set to "full" again)

[hansvanzijp]

Hello Mark,

Thanks but....
Date and time of the executable are the same as the previous version.
Is this really the version which sets the recovery model?

[markclemens]

Hans,

I don't know about the date & time, but this is the correct db_maintenance which also sets the recovery model to simple

(I just downloaded and tested the file from the forum)

groeten uit Wenen,
Mark