Results 1 to 2 of 2
  1. #1
    Platinum Lounger
    Join Date
    Feb 2002
    A Magic Forest in Deepest, Darkest Kent
    Thanked 1 Time in 1 Post

    Memory usage (SQL)

    I have a SQL database running my CRM system, this runs happily along on a cluster and network load balanced set of servers. To assit in performance I have a web service that moves the incremental changes to a hot spare every 15 minutes on a different SQL server which acts as my Report datasource. There are very few transactions that take place except when reports are run.

    I have various maintenance scripts in place where I shrink transaction logs, delete excess records not required in the Reporting area. I have been watching this "reporting database" carefully for a few days as I am preparing for a disc upgrade and got a warning over the weekend statng that the drive was running out of space!! In a matter of 2 days the database grew by 1 Gb and exceeded the size of the original live database <img src=/S/nuts.gif border=0 alt=nuts width=15 height=15>. I have rectified the issue now and reduced log files etc.

    The question is: When a SQL/ Access database appends files to a table does the database "assign" a certain amount of memory space exceeding the recordset's actual size similar to what Access does, requiring Compact and Reair/ DBCC Shrinkdatabase?

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Evergreen, CO, USA
    Thanked 65 Times in 64 Posts

    Re: Memory usage (SQL)

    Hi Jerry, you don't indicate which version of SQL Server you are using, but certain types of transactions do cause the log file to grow like topsy. I recently had a situation where a replicated database decided to start growing the log file at a pretty rapid pace, and the user started complaining about performance when doing certain types of transactions. It turned out the default increase of 10% in size for the log file was trying to get 7GB - the log file had grown to 70GB in a matter of a couple of weeks. I did double backups, and then truncated the log file completely, and it now seems to behaving normally. All this was on 2005, but I've also seen similar behavior with 2000. To answer your question, the log file grows in a linear fashion always at the end, and the MDF file grows in a similar fashion. SQL Server 2000 uses an 8K page so that is the minimal growth. And your maintenance tasks should be doing periodic backups of both the log file and the database file, and doing a compact and repair at least once a week. Unfortunately the default installation for both 2000 and 2005 take the simple recovery approach which can lead to growth in huge amounts. Hope this helps.

Posting Permissions

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