SQL log requirements

dmoretti's Avatar

dmoretti

14 Sep, 2015 05:00 PM

We recently moved our environment to from SQL Server 2008 to SQL Server 2014 and have noticed our log files jumped tremendously in size. Today, based on the size of the log - 81GB - users were unable to log-in and subsequently unable to access the system. Our DBA increased the space for the logs but upon successful log-in of only a handful of users the log file jumped to 100GB. We're wondering if a setting was lost in the migration. What are the recommended settings for logging in a Microsoft SQL Server 2014 environment for Cascade 7.14.2?

  1. Support Staff 1 Posted by Tim on 14 Sep, 2015 05:59 PM

    Tim's Avatar

    Hey there,

    We don't have any specific recommendations for transaction log settings. Out of curiosity, can you or your DBA run a query like this:

    select
           convert(varchar(20), db_name(dbid)) as DatabaseName,
           datediff(mi, last_batch, getdate()) as TransactionDuration,
           convert(varchar(30), hostname) as HostName,
           cmd as Command,
           spid
    from master..sysprocesses
    where open_tran <> 0 and last_batch < dateadd(hh, -1, getdate())
    order by 1 asc, 2 desc
    
    and provide us with the results? From what you're describing, it's possible that there are one or more transactions that are not getting removed. This will hopefully give us an idea of what those may be.

    Thanks

  2. 2 Posted by dmoretti on 14 Sep, 2015 06:49 PM

    dmoretti's Avatar

    Our DBA ran the query but the results are all vmware specific. None of the results were related to the cascade DB.

    David Moretti
    Director of Web Services
    Office of Marketing & Communications
    Queensborough Community College
    222-05 56th Avenue, A-316
    Bayside, NY 11364
    (718) 281-5353

  3. Support Staff 3 Posted by Tim on 14 Sep, 2015 08:49 PM

    Tim's Avatar

    Interesting. You mentioned that the logs seem to fill up when users log into Cascade. If that query is executed again while that's happening, do the results begin to show any items specific to Cascade? If so, can you include that information here?

  4. 4 Posted by dmoretti on 14 Sep, 2015 09:07 PM

    dmoretti's Avatar

    I will ask him to run it again tomorrow when we have more expected users logging into the system.

    David Moretti
    Director of Web Services
    Office of Marketing & Communications
    Queensborough Community College
    222-05 56th Avenue, A-316
    Bayside, NY 11364
    (718) 281-5353

  5. 5 Posted by Ryan Griffith on 23 Sep, 2015 10:12 PM

    Ryan Griffith's Avatar

    Hi David,

    Just wanted to follow up to see if you were able to run the query while more users are logged in.

    Please let us know if you have any questions.

    Thanks!

  6. 6 Posted by dmoretti on 29 Sep, 2015 02:23 PM

    dmoretti's Avatar

    Sorry Ryan,

    The DBA and I have been on separate projects so we haven’t coordinated a redo of the query while more users are logged in. I’m trying to arrange for this later in the week when we know many different users have to log in to update their committee pages.

    David Moretti
    Director of Web Services
    Office of Marketing & Communications
    Queensborough Community College
    222-05 56th Avenue, A-316
    Bayside, NY 11364
    (718) 281-5353

  7. 7 Posted by Ryan Griffith on 29 Sep, 2015 06:17 PM

    Ryan Griffith's Avatar

    Not a problem at all, David. Please keep us posted on your findings and let us know if you have any questions.

    Thanks!

  8. Ryan Griffith closed this discussion on 02 Nov, 2015 08:07 PM.

Comments are currently closed for this discussion. You can start a new one.

Keyboard shortcuts

Generic

? Show this help
ESC Blurs the current field

Comment Form

r Focus the comment reply box
^ + ↩ Submit the comment

You can use Command ⌘ instead of Control ^ on Mac