August 31, 2005 at 6:31 pm
I am trying to figure out how often and when my data and log files are growing. I have them set at 10% and am not sure if that is the correct number. however, i can't find anything in the SQL Server logs about the file growing. Does anyone know where i can find this inforamation?
Regards, Jim C
September 1, 2005 at 3:48 am
You can use either SQL Profiler or perfmon to see when files are grown.
Regarding 10% being right or wrong, the general advice is to not use autogrowth at all. Instead you should monitor when the files are getting close to full and add space manually at an appopriate time.
September 2, 2005 at 12:37 pm
If your Trans log files are growing rapidly - I would look at backing them up on a regular basis. Also are you defragmenting your indexes and shrinking your database on a regular basis?
September 2, 2005 at 1:05 pm
i just have one suspect database where I shrunk the trans log to 500MB. (it was around 5GB). i came in the next morning and it was back up to 4GB. The only thing i know happened was that DBREINDEX was run on the whole database. I tried running the same command on the same database restored as TEST_database and the log file only jumped to 700 MB.
i am backing the log up every hour and the log file is only using less than 1% of the alloted space. I am going to shrink the log file again, but i want to find out what is causing it to balloon out. I was hoping it was just stored in the SQL Server Logs or something.
Regards, Jim C
September 2, 2005 at 2:36 pm
DBREINDEX will do that, especially on tables that have clustered indexes - you might want to backup the trans log more often when the DBREINDEX runs.
September 4, 2005 at 1:21 am
Jim,
Do the following and your problem will be solved
sp_dboption 'Database_Name','AutoShrink','ON'
go
sp_dboption 'Database_Name','trunc. log on chkpt.','ON'
go
DBCC ShrinkDatabase('Database_Name',10)
go
This way your transaction log will remain smaller in size.
LeoAugust
September 4, 2005 at 7:27 am
Everything I've read says to NOT turn on autoshrink - you can't control WHEN it will shrink and it may pick your busiest time of day to do the shrink.
Jim
September 4, 2005 at 7:44 am
JIM HOLCOMB,
a text from SQL Books Online please go through...It only reduces empty free space of Transact log only. and once you set the "truncate on check point" option on your transaction log is never going to increase beyond your control. It will keep the data in the transact log only until begin..commit tran for each transaction.
Keep this option true only if you have set the simple recovery option to true..
When set to ON, the database files are candidates for periodic shrinking. Both data file and log files can be shrunk automatically by SQL Server. When set to OFF, the database files are not automatically shrunk during periodic checks for unused space. By default, this option is set to ON for all databases when using SQL Server Desktop Edition, and OFF for all other editions, regardless of operating system.
AUTO_SHRINK only reduces the size of the transaction log if the database is set to SIMPLE recovery model or if the log is backed up.
The AUTO_SHRINK option causes files to be shrunk when more than 25 percent of the file contains unused space. The file is shrunk to a size where 25 percent of the file is unused space, or to the size of the file when it was created, whichever is greater.
It is not possible to shrink a read-only database.
Its your choice whether to go for it or not...
Leo August
September 6, 2005 at 10:10 am
Jim - is this a big production database?
My take on this: both Autogrow and Autoshrink might be OK for small / less used databases, but for a large one that is intensively used, it's best to set the size to what you expect and leave it alone. Growing / shrinking constantly will just take time and make the files fragmented on disk.
If you are set to full recovery, then the log will grow when you reindex (logging the reindexing, as designed)
see
http://support.microsoft.com/default.aspx?scid=kb;en-us;315512
September 6, 2005 at 10:48 am
You say:
I tried running the same command on the same database restored as TEST_database and the log file only jumped to 700 MB.
But was that a restore of the database from before you ran DBREINDEX or after? If it was from the after version, then of course it used less space. DBREINDEX or INDEXDEFRAG will cause your log to grow quite huge - but only if the indexed are very fragmented. The less/more fragmented the less/more the log file grows.
I run sp_spaceused every day and that helps me keep track of how my data and log files are growing. You can copy the procedure and tailor it to your specific needs.
-SQLBill
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply