Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2005 » Backups » tran log backup confused 2.5gb log file but...
Add to briefcase
62 posts, Page 2 of 7
««12345»»»

tran log backup confused 2.5gb log file but 70gb log backup size Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
lawson2305
Posted Wednesday, June 27, 2012 3:31 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 19, 2013 3:40 PM
Points: 46, Visits: 182
my second backup finally fixed this.

Log backups now down to 1.6GB for the week.
Post #1322174
lawson2305
Posted Monday, July 16, 2012 11:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 19, 2013 3:40 PM
Points: 46, Visits: 182
ok I just don't get it. They are back up to 66GB again.

I run a tran backup and shrink every day. I append the backup daily except for Saturday I do a overwrite.

The tran backup is now 66 GB and the ldf file is 66GB with 99% available free space.
Post #1330301
Perry Whittle
Posted Monday, July 16, 2012 1:10 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:36 AM
Points: 5,201, Visits: 11,153
lawson2305 (7/16/2012)
ok I just don't get it. They are back up to 66GB again.

I run a tran backup and shrink every day. I append the backup daily except for Saturday I do a overwrite.

The tran backup is now 66 GB and the ldf file is 66GB with 99% available free space.

Have you read the link supplied to you by Gail Shaw?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1330347
Lynn Pettis
Posted Monday, July 16, 2012 1:14 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:11 AM
Points: 21,602, Visits: 27,431
lawson2305 (7/16/2012)
ok I just don't get it. They are back up to 66GB again.

I run a tran backup and shrink every day. I append the backup daily except for Saturday I do a overwrite.

The tran backup is now 66 GB and the ldf file is 66GB with 99% available free space.


And what does the following show for the database in question?

select name, log_reuse_wait_desc from sys.databases;


Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1330351
opc.three
Posted Monday, July 16, 2012 1:23 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:14 PM
Points: 6,706, Visits: 11,738
lawson2305 (7/16/2012)
ok I just don't get it. They are back up to 66GB again.

I run a tran backup and shrink every day. I append the backup daily except for Saturday I do a overwrite.

The tran backup is now 66 GB and the ldf file is 66GB with 99% available free space.

Something is causing your log to bloat. One usual suspect is an index maintenance job. Stop shrinking your tran log every day first of all. As you can see it will simply grow again. The next course of action is to figure out what is causing the log to bloat. Look for index maintenance jobs, large batch processing jobs, rogue apps not closing their transactions, anything that would create a lot of modifications to data or indexes. That can help in troubleshooting. Is your database a publisher in a transactional replication scheme? Also try to narrow down the time frame.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1330358
opc.three
Posted Monday, July 16, 2012 1:26 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:14 PM
Points: 6,706, Visits: 11,738
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

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1330362
lawson2305
Posted Monday, July 16, 2012 1:48 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 19, 2013 3:40 PM
Points: 46, Visits: 182
Lynn Pettis (7/16/2012)
lawson2305 (7/16/2012)
master NOTHING
tempdb ACTIVE_TRANSACTION
model LOG_BACKUP
msdb NOTHING
ReportServer NOTHING
ReportServerTempDB NOTHING
InfinityQS LOG_BACKUP
ActivplantDB LOG_BACKUP
vRangerPro NOTHING



activplantdb is the one in question.
Post #1330373
Lynn Pettis
Posted Monday, July 16, 2012 1:50 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:11 AM
Points: 21,602, Visits: 27,431
Looks to me that you have an active VLF near the end of your t-log file.


Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1330375
lawson2305
Posted Monday, July 16, 2012 1:58 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 19, 2013 3:40 PM
Points: 46, Visits: 182
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.

ActivplantDB ActivplantDB_Log 57 2613000 2012-07-15 04:37:01.497 2012-07-15 04:37:04.110 Log
InfinityQS InfinityQS_Data 65 20000 2012-07-15 03:07:03.667 2012-07-15 03:07:03.687 Data
ActivplantDB ActivplantDB_Log 77 2113000 2012-07-15 03:06:27.590 2012-07-15 03:06:29.703 Log
ActivplantDB ActivplantDB_Log 77 2103000 2012-07-15 03:05:05.537 2012-07-15 03:05:07.640 Log
ActivplantDB ActivplantDB_Log 77 2143000 2012-07-15 03:04:33.510 2012-07-15 03:04:35.653 Log
ActivplantDB ActivplantDB_Log 77 2156000 2012-07-15 03:03:56.730 2012-07-15 03:03:58.887 Log
ActivplantDB ActivplantDB_Log 77 2123000 2012-07-15 03:03:26.450 2012-07-15 03:03:28.573 Log
ActivplantDB ActivplantDB_Log 77 2076000 2012-07-15 03:02:47.090 2012-07-15 03:02:49.167 Log
ActivplantDB ActivplantDB_Log 77 2093000 2012-07-15 03:02:14.900 2012-07-15 03:02:16.993 Log
ActivplantDB ActivplantDB_Log 77 2173000 2012-07-15 03:01:41.990 2012-07-15 03:01:44.163 Log
ActivplantDB ActivplantDB_Log 77 2216000 2012-07-15 03:01:09.400 2012-07-15 03:01:11.617 Log
ActivplantDB ActivplantDB_Log 77 2263000 2012-07-15 03:00:34.743 2012-07-15 03:00:37.007 Log
ActivplantDB ActivplantDB_Log 77 2100000 2012-07-15 03:00:02.610 2012-07-15 03:00:04.710 Log
ActivplantDB ActivplantDB_Log 77 2050000 2012-07-15 02:59:29.383 2012-07-15 02:59:31.433 Log
ActivplantDB ActivplantDB_Log 77 2113000 2012-07-15 02:58:57.050 2012-07-15 02:58:59.163 Log
ActivplantDB ActivplantDB_Log 77 2063000 2012-07-15 02:58:23.783 2012-07-15 02:58:25.847 Log
ActivplantDB ActivplantDB_Log 77 2103000 2012-07-15 02:57:52.220 2012-07-15 02:57:54.323 Log
ActivplantDB ActivplantDB_Log 77 2066000 2012-07-15 02:57:19.997 2012-07-15 02:57:22.063 Log
ActivplantDB ActivplantDB_Log 77 5400000 2012-07-15 02:56:46.123 2012-07-15 02:56:51.523 Log
ActivplantDB ActivplantDB_Log 77 2090000 2012-07-15 02:56:13.207 2012-07-15 02:56:15.297 Log
ActivplantDB ActivplantDB_Log 77 2730000 2012-07-15 02:55:43.020 2012-07-15 02:55:45.750 Log
ActivplantDB ActivplantDB_Log 77 2253000 2012-07-15 02:55:08.697 2012-07-15 02:55:10.950 Log
ActivplantDB ActivplantDB_Log 77 2146000 2012-07-15 02:54:39.087 2012-07-15 02:54:41.233 Log
ActivplantDB ActivplantDB_Log 77 2233000 2012-07-15 02:54:02.653 2012-07-15 02:54:04.887 Log
ActivplantDB ActivplantDB_Log 77 2123000 2012-07-15 02:53:33.233 2012-07-15 02:53:35.357 Log
ActivplantDB ActivplantDB_Log 77 2070000 2012-07-15 02:52:58.993 2012-07-15 02:53:01.063 Log
ActivplantDB ActivplantDB_Log 77 2123000 2012-07-15 02:52:29.823 2012-07-15 02:52:31.947 Log
ActivplantDB ActivplantDB_Log 77 2196000 2012-07-15 02:51:55.123 2012-07-15 02:51:57.320 Log
ActivplantDB ActivplantDB_Log 77 2083000 2012-07-15 02:51:25.660 2012-07-15 02:51:27.743 Log
ActivplantDB ActivplantDB_Log 77 2056000 2012-07-15 02:50:52.013 2012-07-15 02:50:54.070 Log
ActivplantDB ActivplantDB_Log 77 2066000 2012-07-15 02:50:22.440 2012-07-15 02:50:24.507 Log
ActivplantDB ActivplantDB_Log 77 2100000 2012-07-15 02:49:43.860 2012-07-15 02:49:45.960 Log
ActivplantDB ActivplantDB_Log 77 2086000 2012-07-15 02:49:14.200 2012-07-15 02:49:16.287 Log
ActivplantDB ActivplantDB_Log 77 2096000 2012-07-15 02:48:39.527 2012-07-15 02:48:41.623 Log
ActivplantDB ActivplantDB_Log 77 2053000 2012-07-15 02:48:09.857 2012-07-15 02:48:11.910 Log
ActivplantDB ActivplantDB_Log 77 2136000 2012-07-15 02:47:35.267 2012-07-15 02:47:37.403 Log
ActivplantDB ActivplantDB_Log 77 2093000 2012-07-15 02:47:05.830 2012-07-15 02:47:07.923 Log
ActivplantDB ActivplantDB_Log 77 2100000 2012-07-15 02:46:31.590 2012-07-15 02:46:33.690 Log
ActivplantDB ActivplantDB_Log 77 2256000 2012-07-15 02:46:00.807 2012-07-15 02:46:03.063 Log
ActivplantDB ActivplantDB_Log 77 2020000 2012-07-15 02:45:26.150 2012-07-15 02:45:28.170 Log
ActivplantDB ActivplantDB_Log 77 2113000 2012-07-15 02:44:56.277 2012-07-15 02:44:58.390 Log
ActivplantDB ActivplantDB_Log 77 1970000 2012-07-15 02:44:22.310 2012-07-15 02:44:24.280 Log
ActivplantDB ActivplantDB_Log 77 2013000 2012-07-15 02:43:52.280 2012-07-15 02:43:54.293 Log
ActivplantDB ActivplantDB_Log 77 2063000 2012-07-15 02:43:18.767 2012-07-15 02:43:20.830 Log
ActivplantDB ActivplantDB_Log 77 2076000 2012-07-15 02:42:49.153 2012-07-15 02:42:51.230 Log
ActivplantDB ActivplantDB_Log 77 2143000 2012-07-15 02:42:11.820 2012-07-15 02:42:13.963 Log
ActivplantDB ActivplantDB_Log 77 2120000 2012-07-15 02:41:41.750 2012-07-15 02:41:43.870 Log
ActivplantDB ActivplantDB_Log 77 2080000 2012-07-15 02:41:08.710 2012-07-15 02:41:10.790 Log
ActivplantDB ActivplantDB_Log 77 2163000 2012-07-15 02:40:37.530 2012-07-15 02:40:39.693 Log
ActivplantDB ActivplantDB_Log 77 2106000 2012-07-15 02:40:04.197 2012-07-15 02:40:06.303 Log
ActivplantDB ActivplantDB_Log 77 1980000 2012-07-15 02:39:33.197 2012-07-15 02:39:35.177 Log
ActivplantDB ActivplantDB_Log 77 1940000 2012-07-15 02:38:59.990 2012-07-15 02:39:01.930 Log
ActivplantDB ActivplantDB_Log 77 2243000 2012-07-15 02:38:28.923 2012-07-15 02:38:31.167 Log
ActivplantDB ActivplantDB_Log 77 2093000 2012-07-15 02:37:55.567 2012-07-15 02:37:57.660 Log
ActivplantDB ActivplantDB_Log 77 2093000 2012-07-15 02:37:24.347 2012-07-15 02:37:26.440 Log
ActivplantDB ActivplantDB_Log 77 2160000 2012-07-15 02:36:50.920 2012-07-15 02:36:53.080 Log
ActivplantDB ActivplantDB_Log 77 2033000 2012-07-15 02:36:19.880 2012-07-15 02:36:21.913 Log
ActivplantDB ActivplantDB_Log 77 2143000 2012-07-15 02:35:44.293 2012-07-15 02:35:46.437 Log
ActivplantDB ActivplantDB_Log 77 2016000 2012-07-15 02:35:12.060 2012-07-15 02:35:14.077 Log
ActivplantDB ActivplantDB_Log 77 4723000 2012-07-15 02:34:42.247 2012-07-15 02:34:46.970 Log
ActivplantDB ActivplantDB_Log 77 2103000 2012-07-15 02:34:12.287 2012-07-15 02:34:14.390 Log
ActivplantDB ActivplantDB_Log 77 2056000 2012-07-15 02:33:46.690 2012-07-15 02:33:48.747 Log
ActivplantDB ActivplantDB_Log 77 2100000 2012-07-15 02:33:14.507 2012-07-15 02:33:16.607 Log
ActivplantDB ActivplantDB_Log 77 1946000 2012-07-15 02:32:47.597 2012-07-15 02:32:49.543 Log
ActivplantDB ActivplantDB_Log 77 2183000 2012-07-15 02:32:12.060 2012-07-15 02:32:14.243 Log
ActivplantDB ActivplantDB_Log 77 1690000 2012-07-15 02:31:46.053 2012-07-15 02:31:47.743 Log
ActivplantDB ActivplantDB_Log 96 1920000 2012-07-14 02:00:03.723 2012-07-14 02:00:05.643 Log
ActivplantDB ActivplantDB_Log 57 2556000 2012-07-12 10:10:01.630 2012-07-12 10:10:04.187 Log
ActivplantDB ActivplantDB_Log 58 3436000 2012-07-10 09:52:31.153 2012-07-10 09:52:34.590 Log


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.
Post #1330382
opc.three
Posted Monday, July 16, 2012 2:13 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:14 PM
Points: 6,706, Visits: 11,738
lawson2305 (7/16/2012)
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.

<truncated content>

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.

The default trace records each time a file autogrow operation. The default trace is always running by default, but rotates through a fixed number of files so older events will eventually fall off the set of files.

I am happy you caught the pattern. If you need point-in-time recovery there isn't much you can do in terms of reducing the total size of your log backups, but you can manage the size of each one by taking them more frequently while you're doing index maintenance.

Is your 'maintenance task' running a SQL Server Maintenance Plan (MP) doing index rebuilds? One thing I see a lot is people rebuilding ALL their indexes at one time using a MP which is wasteful because it rebuild all indexes regardless of whether they need it or not. This practice is a common source of log bloat.

If you're using an MP consider checking into an index maintenance solution that only rebuilds or reorganizes indexes based on their level of fragmentation, i.e. only when they need it. Here is one I use and recommend often:

SQL Server Index and Statistics Maintenance by Ola Hallengren

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1330392
« Prev Topic | Next Topic »

Add to briefcase
62 posts, Page 2 of 7
««12345»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.