Post reply

Transaction Log Mystery

  • June 5, 2006 at 4:43 am

    #60806

    Hi All,

    Not sure if this is the correct forum but...

    I have a SQL2K (SP4) Server.  There is one principal database, which is around 6.6Gb to 6.8Gb.  The transaction log is usually around 1.1-1.3Gb.  There is a backup of the transaction log (and the DB) evey night.

    Last night the size of the Transaction Log had grown dramatically (to 6.7Gb).  The principal access to the database is via a website, as far as we can guage there has not been a dramatic increase in web traffic.

    When I try to open the SQL Server Log current Log, my Enterprise Manager hangs.  The size of the Current Log is currently over 20Mb.

    Can anyone give me any suggestion as to where I should look to see what may have happened to the Transaction Log.

    I will be implementing a more frequent Log Backup strategy shortly but am a bit confused aas to why this may have happened.

    Thanks

     

    S

  • June 5, 2006 at 5:17 am

    #641621

    Did you see any errors in the windows event log?  You can also ty using somthing else to review the errorlogs, I have had the  same issues opening it via the Enterprise manager.


    Stacey W. A. Gregerson

  • June 6, 2006 at 6:20 am

    #641928

    Can you use 'Remote Desktop Connection' to connect to the server to view SQL logs?  Can you go directly to the server itself (I realise this may not be physically possible)?

    If the SQL logs show nothing strange.........

    Has anyone imported/exported a number of records?  This can make the transaction log grow wildly, particularly if the user exports to Excel for some reason.......

    Are there any strange/unusual addresses in the web traffic?   Is this database commercially sensitive?    Is the database functionally correctly? That is: has someone has broken into your database? Could someone have broken through your firewall?

    Can your database be queried by inserting partial data and wildcards into screens?  We had a database where one user put in such odd queries that the vendors (and no one else!) had not envisaged.  These queries translated into series of 'nested ifs' that regularly ground the transaction log until it swallowed all the available space on the server which then crashed.  This user denied any wrong doing but it was funny how the problem stopped when he retired........................

    Madame Artois

  • June 6, 2006 at 6:29 am

    #641932

    Just in case you can get to the server

    The SQL server logs are in

    <Drive letter>\MSSQL\Log\ErrorLog1 to ErrorLog6.  The current log is just called ErrorLog.  All can be opened with Notepad

     

    Madame Artois

  • June 6, 2006 at 6:47 am

    #641944

    Thanks,

     

    we haven't spoktted anything exceptional in the logs.

    Although there are far too many potential areas for security breeches here, we don't appear to have been victom to anything.  The data seems to be as we expected.

    The overnight backups and dumps all took place quite happily last night.  I shall keep an eye on what may happen ansd see if I can spot a pattern.

     

    all the best

     

    S

  • June 6, 2006 at 6:54 am

    #641948

    Did you do any database maintenance, such as reindexing (DBCC DBREINDEX) or index defragging (DBCC INDEXDEFRAG)?

    That will cause your tlog to grow to huge sizes (reindexing can cause your tlog to grow to 1.5 times the size of the database).

    -SQLBill

  • June 6, 2006 at 7:10 am

    #641959

    You also might want to consider flushing the error logs daily and increasing the amount of error logs being kept.  This will possibly help in you being able to open the log file in EM.  Thanks.

    Chris

  • June 6, 2006 at 7:41 am

    #641980

    I know the data may be as you expect;when someone breaks in (or tries to) they don't usually remove everything, just copy it (like a credit card).

    Does this database contain personal data?  If so, I suggest you look at the last time the files were accessed.

    Just 'cos we are paranoid, it does not mean that they are not after us!

     

    Madame Artois

  • June 6, 2006 at 10:27 am

    #642079

    Just a quickie on the errorlogs ...

    If you run the command sp_cycle_errorlog from QA, this will move all existing errorlogs down by one archive number, and put a new one as your current one. You may want to schedule this to run periodically, maybe once a month or even more frequently if you accumulate log messages quickly. That way you shouldn't find individual logs getting so big.

  • June 7, 2006 at 2:00 am

    #642201

    Thanks - that is a useful tip on the error log.  I will be making sure we do that in future.

    Thanks for all your suggestions, I will continue to monitor the situation.

     

    S

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply