July 14, 2005 at 2:01 pm
Hi
I use SQL Server 2000
I use Full recuperation mode.
My DB size is 458 MB (344 in use, 114 available)
My log size is 18 MB
I do Full backup every hour starting at 8:00 am until 21:00
I do transaction backup every 10 minutes.
The problem is that the size of the transaction log backup is strange, as follows:
Time Size(KB) filename
....
05:52 78.336 myDB_tlog_200507140552.TRN
06:02 78.336 myDB_tlog_200507140602.TRN
06:12 78.336 myDB_tlog_200507140612.TRN
06:22 78.336 myDB_tlog_200507140622.TRN
06:32 11.776 myDB_tlog_200507140632.TRN
06:42 11.776 myDB_tlog_200507140642.TRN
06:52 78.336 myDB_tlog_200507140652.TRN
07:03 359.117.312 myDB_tlog_200507140703.TRN Note
07:12 133.072.384 myDB_tlog_200507140712.TRN Note
07:22 291.328 myDB_tlog_200507140722.TRN
07:32 291.328 myDB_tlog_200507140732.TRN
07:42 291.328 myDB_tlog_200507140742.TRN
07:52 356.864 myDB_tlog_200507140752.TRN
08:02 356.864 myDB_tlog_200507140802.TRN
08:12 356.864 myDB_tlog_200507140812.TRN
08:22 357.888 myDB_tlog_200507140822.TRN
08:32 422.400 myDB_tlog_200507140832.TRN
08:42 10.663.424 myDB_tlog_200507140842.TRN Note
08:52 437.760 myDB_tlog_200507140852.TRN
09:02 437.760 myDB_tlog_200507140902.TRN
09:12 699.904 myDB_tlog_200507140912.TRN
09:22 503.296 myDB_tlog_200507140922.TRN
09:32 568.832 myDB_tlog_200507140932.TRN
09:42 568.832 myDB_tlog_200507140942.TRN
09:52 569.856 myDB_tlog_200507140952.TRN
10:02 372.224 myDB_tlog_200507141002.TRN
10:12 10.442.240 myDB_tlog_200507141012.TRN Note
10:22 413.184 myDB_tlog_200507141022.TRN
10:32 545.280 myDB_tlog_200507141032.TRN
10:42 479.744 myDB_tlog_200507141042.TRN
10:52 414.208 myDB_tlog_200507141052.TRN
11:02 479.744 myDB_tlog_200507141102.TRN
11:12 413.184 myDB_tlog_200507141112.TRN
11:22 414.208 myDB_tlog_200507141122.TRN
11:32 546.304 myDB_tlog_200507141132.TRN
11:42 1.093.120 myDB_tlog_200507141142.TRN
11:52 303.616 myDB_tlog_200507141152.TRN
12:02 436.736 myDB_tlog_200507141202.TRN
12:12 369.152 myDB_tlog_200507141212.TRN
12:22 239.104 myDB_tlog_200507141222.TRN
12:32 172.544 myDB_tlog_200507141232.TRN
12:42 107.008 myDB_tlog_200507141242.TRN
12:52 172.544 myDB_tlog_200507141252.TRN
13:02 239.104 myDB_tlog_200507141302.TRN
13:12 9.269.760 myDB_tlog_200507141312.TRN Note
13:22 158.208 myDB_tlog_200507141322.TRN
13:32 158.208 myDB_tlog_200507141332.TRN
13:42 92.672 myDB_tlog_200507141342.TRN
13:52 158.208 myDB_tlog_200507141352.TRN
14:02 92.672 myDB_tlog_200507141402.TRN
14:12 92.672 myDB_tlog_200507141412.TRN
14:22 92.672 myDB_tlog_200507141422.TRN
14:32 289.280 myDB_tlog_200507141432.TRN
14:42 16.926.208 myDB_tlog_200507141442.TRN Note
14:52 280.064 myDB_tlog_200507141452.TRN
15:02 411.136 myDB_tlog_200507141502.TRN
Any ideas?
I want to know what is happening, and how to fix it.
Thanks in advance.
July 15, 2005 at 2:47 am
Hi
In my experience the size of a tran log directly corresponds with the amount of data written. So a large log means large amounts of data written. (an index defrag is a logged operation!)
Do you notice this each day? Are you running any index defrag jobs or some other daily maint routines? Are there any users creating copies of large tables?
JP
July 15, 2005 at 5:35 am
Hi JP de Jong
Thanks for the answer, but:
-NO large amounts of data are written.
(this changes even occurs when no user is connected)
-I do not make any index defrag.
-Each day I notice this.
-NO users are creating copies of large tables
-There are NO other daily maint routines.
-Except for one done at 1:00 DB Optimization.
Also, Auto Stats are Off.
I hope it helps in founding the reason my log is so changing.
Thanks in advance
July 15, 2005 at 5:38 am
There must be some kind of process writing data I guess. If you cretae a sql profiler trace we you can maybe find out what's going on.
JP
July 15, 2005 at 1:38 pm
Hi JP
I have cretae a sql profiler trace, but nothing appears to populate the database so big, in order to increase the log size.
Looking a the log size (the big one) with notepad I See lots of blanks in it.
I don't know what else to do.
The log changes in size from one moment to another, and there is no pattern to follow.
Any Ideas?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply