Home Forums SQL Server 2005 Backups tran log backup confused 2.5gb log file but 70gb log backup size RE: tran log backup confused 2.5gb log file but 70gb log backup size

  • opc.three (7/16/2012)


    I assume you have autogrow enabled on your log file. Check the default trace to see when it grew, that can help establishing a timeframe of when the log bloat started, and hopefully mostly occurred:

    Reviewing AutoGrow events from the default trace by Aaron Bertrand

    ok from this script I do see a pattern.

    I'm guessing this script tells me everytime the file grows.

    ActivplantDBActivplantDB_Log5726130002012-07-15 04:37:01.4972012-07-15 04:37:04.110Log

    InfinityQSInfinityQS_Data65200002012-07-15 03:07:03.6672012-07-15 03:07:03.687Data

    ActivplantDBActivplantDB_Log7721130002012-07-15 03:06:27.5902012-07-15 03:06:29.703Log

    ActivplantDBActivplantDB_Log7721030002012-07-15 03:05:05.5372012-07-15 03:05:07.640Log

    ActivplantDBActivplantDB_Log7721430002012-07-15 03:04:33.5102012-07-15 03:04:35.653Log

    ActivplantDBActivplantDB_Log7721560002012-07-15 03:03:56.7302012-07-15 03:03:58.887Log

    ActivplantDBActivplantDB_Log7721230002012-07-15 03:03:26.4502012-07-15 03:03:28.573Log

    ActivplantDBActivplantDB_Log7720760002012-07-15 03:02:47.0902012-07-15 03:02:49.167Log

    ActivplantDBActivplantDB_Log7720930002012-07-15 03:02:14.9002012-07-15 03:02:16.993Log

    ActivplantDBActivplantDB_Log7721730002012-07-15 03:01:41.9902012-07-15 03:01:44.163Log

    ActivplantDBActivplantDB_Log7722160002012-07-15 03:01:09.4002012-07-15 03:01:11.617Log

    ActivplantDBActivplantDB_Log7722630002012-07-15 03:00:34.7432012-07-15 03:00:37.007Log

    ActivplantDBActivplantDB_Log7721000002012-07-15 03:00:02.6102012-07-15 03:00:04.710Log

    ActivplantDBActivplantDB_Log7720500002012-07-15 02:59:29.3832012-07-15 02:59:31.433Log

    ActivplantDBActivplantDB_Log7721130002012-07-15 02:58:57.0502012-07-15 02:58:59.163Log

    ActivplantDBActivplantDB_Log7720630002012-07-15 02:58:23.7832012-07-15 02:58:25.847Log

    ActivplantDBActivplantDB_Log7721030002012-07-15 02:57:52.2202012-07-15 02:57:54.323Log

    ActivplantDBActivplantDB_Log7720660002012-07-15 02:57:19.9972012-07-15 02:57:22.063Log

    ActivplantDBActivplantDB_Log7754000002012-07-15 02:56:46.1232012-07-15 02:56:51.523Log

    ActivplantDBActivplantDB_Log7720900002012-07-15 02:56:13.2072012-07-15 02:56:15.297Log

    ActivplantDBActivplantDB_Log7727300002012-07-15 02:55:43.0202012-07-15 02:55:45.750Log

    ActivplantDBActivplantDB_Log7722530002012-07-15 02:55:08.6972012-07-15 02:55:10.950Log

    ActivplantDBActivplantDB_Log7721460002012-07-15 02:54:39.0872012-07-15 02:54:41.233Log

    ActivplantDBActivplantDB_Log7722330002012-07-15 02:54:02.6532012-07-15 02:54:04.887Log

    ActivplantDBActivplantDB_Log7721230002012-07-15 02:53:33.2332012-07-15 02:53:35.357Log

    ActivplantDBActivplantDB_Log7720700002012-07-15 02:52:58.9932012-07-15 02:53:01.063Log

    ActivplantDBActivplantDB_Log7721230002012-07-15 02:52:29.8232012-07-15 02:52:31.947Log

    ActivplantDBActivplantDB_Log7721960002012-07-15 02:51:55.1232012-07-15 02:51:57.320Log

    ActivplantDBActivplantDB_Log7720830002012-07-15 02:51:25.6602012-07-15 02:51:27.743Log

    ActivplantDBActivplantDB_Log7720560002012-07-15 02:50:52.0132012-07-15 02:50:54.070Log

    ActivplantDBActivplantDB_Log7720660002012-07-15 02:50:22.4402012-07-15 02:50:24.507Log

    ActivplantDBActivplantDB_Log7721000002012-07-15 02:49:43.8602012-07-15 02:49:45.960Log

    ActivplantDBActivplantDB_Log7720860002012-07-15 02:49:14.2002012-07-15 02:49:16.287Log

    ActivplantDBActivplantDB_Log7720960002012-07-15 02:48:39.5272012-07-15 02:48:41.623Log

    ActivplantDBActivplantDB_Log7720530002012-07-15 02:48:09.8572012-07-15 02:48:11.910Log

    ActivplantDBActivplantDB_Log7721360002012-07-15 02:47:35.2672012-07-15 02:47:37.403Log

    ActivplantDBActivplantDB_Log7720930002012-07-15 02:47:05.8302012-07-15 02:47:07.923Log

    ActivplantDBActivplantDB_Log7721000002012-07-15 02:46:31.5902012-07-15 02:46:33.690Log

    ActivplantDBActivplantDB_Log7722560002012-07-15 02:46:00.8072012-07-15 02:46:03.063Log

    ActivplantDBActivplantDB_Log7720200002012-07-15 02:45:26.1502012-07-15 02:45:28.170Log

    ActivplantDBActivplantDB_Log7721130002012-07-15 02:44:56.2772012-07-15 02:44:58.390Log

    ActivplantDBActivplantDB_Log7719700002012-07-15 02:44:22.3102012-07-15 02:44:24.280Log

    ActivplantDBActivplantDB_Log7720130002012-07-15 02:43:52.2802012-07-15 02:43:54.293Log

    ActivplantDBActivplantDB_Log7720630002012-07-15 02:43:18.7672012-07-15 02:43:20.830Log

    ActivplantDBActivplantDB_Log7720760002012-07-15 02:42:49.1532012-07-15 02:42:51.230Log

    ActivplantDBActivplantDB_Log7721430002012-07-15 02:42:11.8202012-07-15 02:42:13.963Log

    ActivplantDBActivplantDB_Log7721200002012-07-15 02:41:41.7502012-07-15 02:41:43.870Log

    ActivplantDBActivplantDB_Log7720800002012-07-15 02:41:08.7102012-07-15 02:41:10.790Log

    ActivplantDBActivplantDB_Log7721630002012-07-15 02:40:37.5302012-07-15 02:40:39.693Log

    ActivplantDBActivplantDB_Log7721060002012-07-15 02:40:04.1972012-07-15 02:40:06.303Log

    ActivplantDBActivplantDB_Log7719800002012-07-15 02:39:33.1972012-07-15 02:39:35.177Log

    ActivplantDBActivplantDB_Log7719400002012-07-15 02:38:59.9902012-07-15 02:39:01.930Log

    ActivplantDBActivplantDB_Log7722430002012-07-15 02:38:28.9232012-07-15 02:38:31.167Log

    ActivplantDBActivplantDB_Log7720930002012-07-15 02:37:55.5672012-07-15 02:37:57.660Log

    ActivplantDBActivplantDB_Log7720930002012-07-15 02:37:24.3472012-07-15 02:37:26.440Log

    ActivplantDBActivplantDB_Log7721600002012-07-15 02:36:50.9202012-07-15 02:36:53.080Log

    ActivplantDBActivplantDB_Log7720330002012-07-15 02:36:19.8802012-07-15 02:36:21.913Log

    ActivplantDBActivplantDB_Log7721430002012-07-15 02:35:44.2932012-07-15 02:35:46.437Log

    ActivplantDBActivplantDB_Log7720160002012-07-15 02:35:12.0602012-07-15 02:35:14.077Log

    ActivplantDBActivplantDB_Log7747230002012-07-15 02:34:42.2472012-07-15 02:34:46.970Log

    ActivplantDBActivplantDB_Log7721030002012-07-15 02:34:12.2872012-07-15 02:34:14.390Log

    ActivplantDBActivplantDB_Log7720560002012-07-15 02:33:46.6902012-07-15 02:33:48.747Log

    ActivplantDBActivplantDB_Log7721000002012-07-15 02:33:14.5072012-07-15 02:33:16.607Log

    ActivplantDBActivplantDB_Log7719460002012-07-15 02:32:47.5972012-07-15 02:32:49.543Log

    ActivplantDBActivplantDB_Log7721830002012-07-15 02:32:12.0602012-07-15 02:32:14.243Log

    ActivplantDBActivplantDB_Log7716900002012-07-15 02:31:46.0532012-07-15 02:31:47.743Log

    ActivplantDBActivplantDB_Log9619200002012-07-14 02:00:03.7232012-07-14 02:00:05.643Log

    ActivplantDBActivplantDB_Log5725560002012-07-12 10:10:01.6302012-07-12 10:10:04.187Log

    ActivplantDBActivplantDB_Log5834360002012-07-10 09:52:31.1532012-07-10 09:52:34.590Log

    I'm guessing what was running at process 77 is my issue. I do run maintenance task at this time I will review the history.