Page 1 of 2 1 2 LastLast
Showing results 1 to 10 of 18

Thread: SQL problem with zenOn 6.21 - huge project-log file

  1. #1
    Join Date
    30.05.2007
    Location
    Salzburg
    Posts
    879

    Default SQL problem with zenOn 6.21 - huge project-log file

    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)
    Last edited by herberto : 14th December 2009 at 12:42 Reason: Not needed anymore: "re-adjust the Recovery model to "full" ("Vollständig")"

  2. #2
    Join Date
    30.05.2007
    Location
    Salzburg
    Posts
    879

    Default Re: SQL problem with zenOn 6.21 - huge project-log file

    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)
    Attached Files Attached Files

  3. #3
    Join Date
    30.05.2007
    Location
    Salzburg
    Posts
    879

    Ausrufezeichen Re: SQL problem with zenOn 6.21 - huge project-log file

    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.

  4. #4

    Default Re: SQL problem with zenOn 6.21 - huge project-log file

    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
    Last edited by markclemens : 20th October 2008 at 12:42

  5. #5

    Default Re: SQL problem with zenOn 6.21 - huge project-log file

    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.

  6. #6
    hansvanzijp Gast

    Default Re: SQL problem with zenOn 6.21 - huge project-log file

    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

  7. #7

    Default Re: SQL problem with zenOn 6.21 - huge project-log file

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

  8. #8

    Default Re: SQL problem with zenOn 6.21 - huge project-log file

    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)

  9. #9
    hansvanzijp Gast

    Frage Re: SQL problem with zenOn 6.21 - huge project-log file

    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?

  10. #10

    Default Re: SQL problem with zenOn 6.21 - huge project-log file

    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

Similar Threads

  1. Replies: 2
    Last Post: 9th June 2009, 12:53

Posting Rules

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •