December 22, 2009 at 3:34 pm
I have an existing database with the following settings. Data: Initial size: 296mb Log: Initial Size: 11735mb I am sure the log size was created in error. The Model database is set to 20mb.
How do I reduce the initial log size without effecting the database (which is a 24/7 production database)
December 22, 2009 at 3:39 pm
donna.harrison (12/22/2009)
I have an existing database with the following settings. Data: Initial size: 296mb Log: Initial Size: 11735mb I am sure the log size was created in error. The Model database is set to 20mb.How do I reduce the initial log size without effecting the database (which is a 24/7 production database)
There will be an impact if you adjust the log size.
Quite possibly, you are seeing that as the initial size - but the log has grown to that size. If it has grown to that size, there is a runaway process that is the culprit in your system. It would be better to find that, then fix it, and then adjust your log file size during a scheduled maintenance window.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 22, 2009 at 3:46 pm
Impact of shrinking log file can be minimal if you do it immediately after a log backup or when the log has a small active portion. Still good practice to do it at a quite time though
shrinking a data file is always very intrusive so avoid it.
Is the database in full, bulk-logged or simple recovery mode? If full or bulk-logged is the log being backed up?
---------------------------------------------------------------------
December 22, 2009 at 3:49 pm
you are right.. the actual log file is 12,016,128kb
.. Can you provide insight how to find this "run away process" to fix?
(I'm new to managing SQL)
December 22, 2009 at 3:51 pm
The database is in full mode and the log file is being backed up every 30minutes with a full back up each day.. The log file still remains at 12,016,128kb after backup
December 22, 2009 at 3:53 pm
run
dbcc sqlperf(logspace)
and post result for relevant database
whilst in database run
dbcc opentran
and post result
---------------------------------------------------------------------
January 11, 2010 at 12:34 pm
Sorry it took so long to answer .. I have run the commands and received the following
Name Log Size MB Log Space Used % Status
Worksight 11734.49 0.3390196 0
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
January 12, 2010 at 3:41 am
Donna, log file is 11Gb but is actually hardly used. You do not have a problem per se, certainly not an ongoing runaway transaction or anything like that. At some point in the past something caused the log to grow to that size, and as a log backup does NOT reduce the physical size of the log file on disk it has remained at that size.
Monitor the size of your log backups for a week and see what the biggest one is. Use that as a guide to the real required size for your transactio log file (add a reasonable fudge factor and don't be mean with it).
then do a ONE OFF shrink of the log file using dbcc shrinkfile to that size at a quite time. do not repeatedly shrink it, get it to a required size and leave it there.
If overall disk space is not a problem don't concern yourself too much with the size of the log file. Also ensure the growth factor is a sensible value.
---------------------------------------------------------------------
January 12, 2010 at 9:52 am
Look for a scheduled job that performs a reindex operation on that database. I would bet that the log file is growing to that size during this maintenance.
Also, look for any load processes where lots of data can be loaded during a transaction.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply