May 21, 2009 at 10:12 am
How do your either completely truncate the log and reclaim the disk space, or keep the log files from growing too large?
May 21, 2009 at 10:20 am
Are you backing the log file up on a regular basis?
May 21, 2009 at 10:32 am
doug turner (5/21/2009)
How do your either completely truncate the log and reclaim the disk space, or keep the log files from growing too large?
1)Taking Log Backups at regular intervals.: After that, u can check log_reuse_wait_descr column in sys.databases to see if there is anything there that is preventing the log from truncating when logbackups r run.
2)Use DBCC SQLPERF(LogSpace) to see what percentage of log is full.
3)U can use Shrinkfile to shrink ur log file later on to a size depending on ur server or activities happening.
May 21, 2009 at 10:45 am
doug turner (5/21/2009)
How do your either completely truncate the log and reclaim the disk space, or keep the log files from growing too large?
Ask yourself whether you REALLY need a log file to back up. If daily backups of your data are sufficient then simply set the database to simple recovery mode under [Options] in the database properties. Then run a
DBCC SHRINKDATABASE(dbname)
And your log file will never grow again. On the other hand if you DO need to backup your database more often than once a day write back and people can help you out with that :-).
May 21, 2009 at 10:54 am
"And your log file will never grow again"
Not actuallly correct. A log file can absolutely auto grow even when utilizing the SIMPLE recovery model. One "long" running transaction is all it takes. It still needs to be sized appropriately. Not as large as you would keep it if you were in FULL or BULK LOGGED.
May 21, 2009 at 11:03 am
SQLDBA is correct. One long transaction will grow a log file, or fail because of a lack of log space even in Simple mode.
You need to size for your peak transaction load between backups (full mode) or checkpoints (simple)
May 21, 2009 at 11:38 am
Thanks for the replies, folks. I've watched this log file grow from 1.4Gb to to 1.9Gb in about 2 hrs. So, if I change it to Simple mode, it will only write to the logs when I do a backup?
May 21, 2009 at 11:47 am
doug turner (5/21/2009)
Thanks for the replies, folks. I've watched this log file grow from 1.4Gb to to 1.9Gb in about 2 hrs. So, if I change it to Simple mode, it will only write to the logs when I do a backup?
If u change it to simple, every time a transaction is commited SQL will issue an checkpoint. That will free up the space in transaction log file so it will not keep on growing exponentially.
Everytime a transaction is run or activity happens in ur SQL Server, that action is written in ur Log file regardless of the recovery model(Simple, Bulk logged or Full)
May 21, 2009 at 11:56 am
I've gone into options and changed it to Simple. I have been running dbcc shrinkfile, truncateonly pretty much back to back since my last post. Windows Explorer is now showing the file size to be 2.5Gb. It's grown that much in that short a time.
May 21, 2009 at 12:00 pm
Is this a highly transactional system?
May 21, 2009 at 12:00 pm
Like SQLDBA had said before, there r transactions in ur database that need that kind of log space to perform actions. Maybe u have long running queries in ur server.
May 21, 2009 at 12:03 pm
Yes, it is. It is a high transactional server. Now up to 3.5Gb
May 21, 2009 at 12:14 pm
Well then you need to size the log accordingly. Shrinking it down to the smallest value is only going to result in auto growths.
Keep in mind too that "long running queries" is relative. If you shrink the log file down to 1MB for example, odds are ANY transaction is going to make it grow. how much it grows is based upon how often it is autogrowing and by what increments you have ti set to autogrow.
If this is a highly transactional system...... do you need point in time recovery (log backups)? Granted I do not know the nature of the system, but it being classified as highly transactional would be a red flag for me on whether or not it should be running in SIMPLE recovery mode.
May 21, 2009 at 12:15 pm
Keep in mind too that these autogrowths are hurting server performance. Auto growth should be a last resort.
How large is the database (mdf)?
May 21, 2009 at 12:20 pm
SQLDBA (5/21/2009)
Keep in mind too that these autogrowths are hurting server performance. Auto growth should be a last resort.How large is the database (mdf)?
unrestricted growth is turned on for the database and the log.
Current database size is 580Mb, log size is 3.8Gb
Viewing 15 posts - 1 through 15 (of 37 total)
You must be logged in to reply to this topic. Login to reply