Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2004
    Location
    Germany
    Posts
    5

    Question Unanswered: How to alter the transaction logfile size in MSDE 2000?

    Hi there,

    I recently saw that the transaction log files of user dbs grow undefinitely in SQL Server 2000 - one of our customers had a 11 GB log file which totally slowed down the server.
    Another customer of ours uses one of my applications logging all actions in a MSDE database file and I fear that the corresponding transaction log file will grow and block the system too - is there any way that I could shrink and set the max size of the transaction log file through SQL?

    I already know the command "SHRINK FILE ('filename')" but I haven't found a SQL command to set the max size.

    Thank you for any hints!

    Sascha

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    To set the growth, UOM for increment, increment value, allocated size, and max sizeeeee, you need to use ALTER DATABASE. However, the claim that the size of the log file slows down the server or even "block" it is somewhat strange. I've never heard of the size of the log file having such detremental effect on the system. Anyone has other opinion?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Dec 2004
    Location
    Germany
    Posts
    5
    hi rdjabarov,

    thank you for your comment. How should the ALTER DATABASE command look like? It's not a part of the standard SQL command, that's why I haven't found any documentation in standard SQL books.
    The server slowed down because there were only 180 kb free space on the drive and windows needs some space for swapping, running other programs and other stuff.
    I'll try to see if I can get some info on ALTER DATABASE with MS SQL Server.

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Now THAT (!!!) is a different story! In other words, your db is on the verge of becoming Suspect. BTW, your OS doesn't need any more room for swap file, unless you're bogging down the memory as well.

    BOL (ALTER DATABASE syntax):

    MODIFY FILE
    Specifies the given file that should be modified, including the FILENAME, SIZE, FILEGROWTH, and MAXSIZE options.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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