6 Replies Latest reply on Apr 24, 2009 5:24 AM by karenpeacock

    Size of log file

    gramsay Specialist

      Our DBA has just asked if the transaction log file needs to be so large. It is 34GB.

      What's the best way to manage this file?

        • 1. Re: Size of log file
          ahe Expert

          Hello Graham,

           

          I'm not familiar with the Service Desk database, but 34 GB is to big for a transaction log file. It seems something of database configuration must be changed.

           

          Which SQL Server version/service pack level do you use?

           

          There exist some documents how to maintain/optimize the databases...

           

          ServicePortal Performance - Database Tuning

          SQL Tuning and Performance

          Installing Microsoft SQL Server 2005 for use with Management Suite 8.8 (search for maintenance plan in this doc or other documents like "Installing Microsoft SQL Server... in the BKM's)

           

          Regards

          Axel

          1 of 1 people found this helpful
          • 2. Re: Size of log file
            phoffmann SupportEmployee

            Reading the documents Axel linked would be a good start. In particular the "Installing SQL 2000 / 2005 for use with LANDesk 8.8"

             

            Most likely cause -- whoever set up the database overlooked the point in the white paper about "change the revovery model from FULL to SIMPLE". (I suspect it's an easy enough thing to overlook, since in most apps it would make sense to have a full log).

             

            LANDesk is a VERY highly transactional database - and if you manage a lot of nodes, the # of transactions will go up accordingly. Yet at the same time, the details of the transactions aren't particularly relevant (95% of "something bad happened" scenarios can be fixed with a simple SYNC scan) -- so a "simple" recovery model is prefectly fine for most cases.

             

            Here's something that may put a bit of perspective on it.

             

             

            If you're running any kind of a business critical application, you should take a backup every night and you might run full recovery mode such that, if a disk crash occurs at lunch time, you can go back to yesterday night's backup and use the transaction logs to get you back to where you were at the point of failure.

             

            Benefit: you haven't lost the morning's transactions.

            Cost: you log a day's worth of transactions and you need to have strict procedures in place. After you've taken your daily back, the transactions log has served its purpose and you throw it away.

             

             

            By selecting simple recovery, you accept the risk that your database will crash, you'll have to go back to your last backup and you will have lost all transactions since the last backup.

             

            We have traditionally not considered the LDMS database critical enough that you couldn't afford to lose a day's (or 2 days' or 1 week's...) transactions. But there are no absolutes here - for instance when using Asset Manager. If you've spent all morning typing in asset data, how would you like being asked to please type it all in again because we've had a crash while you were away having lunch? On the other hand - as indicated above - most data *CAN* be restored by a simple inventory scan ...

             

             

             

            If you have folks who understand databases, who know what they're doing, who have procedures in place to take daily backups, they will be grown up enough to understand the costs and benefits and procedural implications of the various recovery modes and we should not be telling them how to manage their databases.

             

            A decent rule of thumb has always been "if you need to ask what simple / full recovery means, then you use simple; otherwise, use your own judgement".

             

            Hope that this answers your question / explains what's going on.

             

            Paul Hoffmann

            LANDesk EMEA Technical lLead

            • 3. Re: Size of log file
              gramsay Specialist

              Thanks both of you. I'll pass the info on to our server team.

              • 4. Re: Size of log file
                phoffmann SupportEmployee

                Ah - I just noticed that this is for Service Desk ... not LANDesk. My apologies, it's still early morning (for my brain at any rate).

                 

                I'll get one of the Service Desk folks to put a more educated answer in... just to be on the safe side .

                 

                Paul Hoffmann

                LANDesk EMEA Technical Lead

                • 5. Re: Size of log file
                  Stu McNeill Employee

                  From a Service Desk point of view there isn't anything extra to add, thanks Paul!

                   

                  Normally we'd expect a nightly backup to trigger the logs to be reset.

                  • 6. Re: Size of log file
                    karenpeacock SupportEmployee

                    I might be wrong here, but I think that if you have automated large imports going on and are using full recovery mode, you could consider swapping to another recovery mode before they start and swapping back after they finish.  The reason being that in theory if you hit a problem you should be able to just run the import again rather than having to use the sql log file to recover to.  I think there's a recovery mode called bulk logged for this purpose - more info in sql books online or microsoft kb if you are interested.

                     

                    Thanks

                    Karen