June 29, 2009 at 4:36 am
i have a db with transaction log initial size as 40 and it is increasing to 100mb then again it gets reseted to initial size 40mb. pls let me know what is the reason to gets reset. Pls Help me out
June 29, 2009 at 4:50 am
If this is SQL Server 2000, if you have set DB Maintenance plan for optimizations.. Please check whether the option
‘Remove unused space from Database Files’ is enabled. If so, please uncheck it.
This would remove any unused space from the database, thereby reducing the size of the data files.
June 29, 2009 at 4:57 am
Thank you but i am using sql2005.
June 29, 2009 at 5:28 am
Either you have a maintenance plan option that's doing a shrink or you have autoshrink on or there's a manual shrink job somewhere.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 29, 2009 at 5:41 am
Thank you Shaw, We kept autoshrink as false and we do not manually autoshrink anywhere else.
June 29, 2009 at 5:48 am
Kingsleen Solomon Doss (6/29/2009)
Thank you Shaw, We kept autoshrink as false and we do not manually autoshrink anywhere else.
If your log file is decreasing in size, you have a shrink database or shrink file somewhere. There are no other operations that reduce the size of the transaction log.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 29, 2009 at 6:00 am
sorry we are shrinking database and another thing we updated service pack 3 in sql2005. after applying this pack only it gets reseted
June 29, 2009 at 6:12 am
Kingsleen Solomon Doss (6/29/2009)
sorry we are shrinking database
There's your answer then. A shrink database will shrink the log.
Why are you shrinking your database though? Databases tend to grow as more data gets put in them. It's in their nature.
Shrinking causes massive fragmentation and will just result in the data file growing again next time data gets added. When that happens, the entire system will slow down as the file is expanded. Also repeated shrinks and grows will cause fragmentation at the file-system level, which is hard to fix.
See - http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 29, 2009 at 6:24 am
But Before applying sql2005 service pack 3 its normally working fine the log file never decreases. then how come happens now
June 29, 2009 at 6:31 am
Could be anything, but Shrinkdatabase shrinks both data and log files. Data file shrink's the bigger problem, but log file shrink can cause issues too.
Bottom line, if you don't want the file to reduce in size, don't run shrink.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply