Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
lfmn
Posting Yak Master
141 Posts |
 Posted - 2002-06-11 : 09:42:51
|
| I currently work on the most poorly funded "data warehouse" project in America. Because of space limits, I have to watch my backups like a hawk. Can anyone tell me if any of the following affect transaction log size? I checked BOL and some of my reference manuals, but can't find anything.DBCC CHECKDB (assuming I use one of the repair options)DBCC DBREINDEXUDPATE STATISTICSDBCC SHRINKDATABASE Thanks for your helpP.S. They've promised additional disk space so many times I feel like I'm Charlie Brown and they are Lucy pulling the football away at the last second!SQL is useful if you don't know cursors :-) |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-06-11 : 09:50:30
|
| If disk space is a big issue for you then you need to consider the distinction between logical size and physical size. A BACKUP LOG statement will checkpoint the transaction log, potentially reducing its logical size, but only when followed by (not by itself) a DBCC SHRINKDATABASE or DBCC SHRINKFILE will the physical size of the log be reduced. I don't believe the other three statements below will affect the transaction log in any way.setBasedIsTheTruepath<O> |
 |
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-06-11 : 10:01:09
|
| It is not clear whether you are asking about the size of your database log file or the size of the log backup device. Also, you didn't say what version of SQL Server you are running. I believe it goes like this:DBCC CHECKDB -- will fill up tempdb log (and might cause it to grow) But since tempdb log is truncated automatically, it probably isn't too much of a concern for youDBCC DBREINDEX -- SQL7 should not have any affect on the log/backup size (tempdb log might fill up, but see above). On SQL2k all will depend on the recovery mode. If you are in Full Recovery mode, the size of the log file in your database will grow like mad. The size of the backup device will be about the same as the size of the used portion of the log file. If you are in Bulk-logged mode, the log file will not grow, but the backup device will get huge (SQL Server has to dump out all the "dirty" pages). UPDATE STATISTICS -- won't do much to your log sizeDBCC SHRINKDATABASE -- will fill up tempdb log (but again, due to nature of tempdb it's not too much of a concern)Edited by - izaltsman on 06/11/2002 10:03:45Edited by - izaltsman on 06/11/2002 10:14:01 |
|
|
|
lfmn
Posting Yak Master
141 Posts |
Posted - 2002-06-11 : 14:05:45
|
| Thanks for the input.SQL is useful if you don't know cursors :-) |
|
|
|
|
|
|
transaction log question