|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
physical shrink of transaction log - sql 2000I have a log file in my sql 2000 database that is over 1gb in physical size.
The recovery model is "simple". The transaction log is not backed up. Implicit/explicit checkpoints are supposed to truncate inactive portions of the log. After, I issued DBCC SHRINKDATABSE and SHINKFILE commands (no errors during command) , the physical log still remians the same (over 1GB). Does anyone have suggestions on what to do next? Thanks, Tom With simple recovery model, you shouldn't need to maintain the log unless
there are processes that prevent it from being truncated. One such example is an open transaction that wasn't closed for a while, maybe as a result of a bug. To see if that's the case, run DBCC OPENTRAN. Another case that might prevent the log from being truncated is if you have transaction log replication. At any rate, I suggest that you look for a kb article in the support site on log truncation. Look for the keywords transaction log, shrink. Show quote "tbrauch" wrote: > I have a log file in my sql 2000 database that is over 1gb in physical size. > The recovery model is "simple". The transaction log is not backed up. > Implicit/explicit checkpoints are supposed to truncate inactive portions of > the log. After, I issued DBCC SHRINKDATABSE and SHINKFILE commands (no > errors during command) , the physical log still remians the same (over 1GB). > > Does anyone have suggestions on what to do next? > > Thanks, > Tom tbrauch wrote:
> I have a log file in my sql 2000 database that is over 1gb in The log file can still grow very large even when using simple recovery. > physical size. The recovery model is "simple". The transaction log > is not backed up. Implicit/explicit checkpoints are supposed to > truncate inactive portions of the log. After, I issued DBCC > SHRINKDATABSE and SHINKFILE commands (no errors during command) , the > physical log still remians the same (over 1GB). > > Does anyone have suggestions on what to do next? > > Thanks, > Tom All it needs is a very large transaction to do this. However, the log should auto-truncate in simple recovery. That won't reduce the file size, but it will mark committed transactions as free space. The DBCC SHRINKFILE run on the log file itself should return the log file to a more manageable size. Can you post the DBCC SHRINKFILE code you ran? I tried DBCC SHRINKDATABASE (<databasename>) which said command succeeded
and DBCC SHRINKFILE (<Logname>) . . .which said 1ROW AFFECTED and COMMAND SUCCEEDED. Statistics were all 134896 for cuurentsize, minimum size, used pages, and estimated pages. Physical size still > 1GB. Thanks, Tom Show quote "David Gugick" wrote: > tbrauch wrote: > > I have a log file in my sql 2000 database that is over 1gb in > > physical size. The recovery model is "simple". The transaction log > > is not backed up. Implicit/explicit checkpoints are supposed to > > truncate inactive portions of the log. After, I issued DBCC > > SHRINKDATABSE and SHINKFILE commands (no errors during command) , the > > physical log still remians the same (over 1GB). > > > > Does anyone have suggestions on what to do next? > > > > Thanks, > > Tom > > The log file can still grow very large even when using simple recovery. > All it needs is a very large transaction to do this. However, the log > should auto-truncate in simple recovery. That won't reduce the file > size, but it will mark committed transactions as free space. The DBCC > SHRINKFILE run on the log file itself should return the log file to a > more manageable size. > > Can you post the DBCC SHRINKFILE code you ran? > > > -- > David Gugick > Quest Software > www.imceda.com > www.quest.com > > tbrauch wrote:
> I tried DBCC SHRINKDATABASE (<databasename>) which said command Try specifying the target size for the log file when running DBCC > succeeded and > DBCC SHRINKFILE (<Logname>) . . .which said 1ROW AFFECTED and COMMAND > SUCCEEDED. Statistics were all 134896 for cuurentsize, minimum size, > used pages, and estimated pages. > > Physical size still > 1GB. > > Thanks, > Tom SHRINKFILE. You need to familiar yourself with the concept of virtual log files. Info and links here
http://www.karaszi.com/SQLServer/info_dont_shrink.asp In general, you tend to do DBCC SHRINKFILE, BACKU LOG, DBCC SHRINKFILE, BACKUP LOG etc. until you get the logfile to the size you desire. -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ "tbrauch" <tbra***@discussions.microsoft.com> wrote in message news:0B1B78A8-C9B7-43AC-A60D-7285BEC79B9C@microsoft.com... >I tried DBCC SHRINKDATABASE (<databasename>) which said command succeeded > and > DBCC SHRINKFILE (<Logname>) . . .which said 1ROW AFFECTED and COMMAND > SUCCEEDED. Statistics were all 134896 for cuurentsize, minimum size, used > pages, and estimated pages. > > Physical size still > 1GB. > > Thanks, > Tom > > > "David Gugick" wrote: > >> tbrauch wrote: >> > I have a log file in my sql 2000 database that is over 1gb in >> > physical size. The recovery model is "simple". The transaction log >> > is not backed up. Implicit/explicit checkpoints are supposed to >> > truncate inactive portions of the log. After, I issued DBCC >> > SHRINKDATABSE and SHINKFILE commands (no errors during command) , the >> > physical log still remians the same (over 1GB). >> > >> > Does anyone have suggestions on what to do next? >> > >> > Thanks, >> > Tom >> >> The log file can still grow very large even when using simple recovery. >> All it needs is a very large transaction to do this. However, the log >> should auto-truncate in simple recovery. That won't reduce the file >> size, but it will mark committed transactions as free space. The DBCC >> SHRINKFILE run on the log file itself should return the log file to a >> more manageable size. >> >> Can you post the DBCC SHRINKFILE code you ran? >> >> >> -- >> David Gugick >> Quest Software >> www.imceda.com >> www.quest.com >> >>  |
|||||||||||||||||||||||
Other interesting topics