Reduce log file size
in [Programming SQL]
|
From: DavidC on 12 Aug 2010 20:38 I cannot reduce the size of my log file which has grown to 43GB. I tried using DBCC SHRINKDATABASE but got the following message and not sure how to shrink it. Any help is appreciated. DBCC SHRINKDATABASE: File ID 1 of database ID 10 was skipped because the file does not have enough free space to reclaim. Cannot shrink log file 2 (MCFICore_log) because of minimum log space required. -- David
From: John Bell on 13 Aug 2010 07:12 On Thu, 12 Aug 2010 17:38:03 -0700, DavidC <dlchase(a)lifetimeinc.com> wrote: >I cannot reduce the size of my log file which has grown to 43GB. I tried >using DBCC SHRINKDATABASE but got the following message and not sure how to >shrink it. Any help is appreciated. > >DBCC SHRINKDATABASE: File ID 1 of database ID 10 was skipped because the >file does not have enough free space to reclaim. >Cannot shrink log file 2 (MCFICore_log) because of minimum log space required. Hi Under normal circumstances you shouldn't want to shrink the log file, with the correct backup regime the log file should stay at a stable size that covers normal usage. You should certainly not use SHRINKDATABASE as this will also try to shrink the data file which can seriously affect performance. http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(930)-data-file-shrink-does-not-affect-performance.aspx Also look at http://www.karaszi.com/SQLServer/info_dont_shrink.asp http://www.sqlskills.com/blogs/paul/post/backup-log-with-no_log-use-abuse-and-undocumented-trace-flags-to-stop-it.aspx John
From: DavidC on 13 Aug 2010 08:40 "John Bell" wrote: > On Thu, 12 Aug 2010 17:38:03 -0700, DavidC <dlchase(a)lifetimeinc.com> > wrote: > > >I cannot reduce the size of my log file which has grown to 43GB. I tried > >using DBCC SHRINKDATABASE but got the following message and not sure how to > >shrink it. Any help is appreciated. > > > >DBCC SHRINKDATABASE: File ID 1 of database ID 10 was skipped because the > >file does not have enough free space to reclaim. > >Cannot shrink log file 2 (MCFICore_log) because of minimum log space required. > > > Hi > > Under normal circumstances you shouldn't want to shrink the log file, > with the correct backup regime the log file should stay at a stable > size that covers normal usage. You should certainly not use > SHRINKDATABASE as this will also try to shrink the data file which can > seriously affect performance. > http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(930)-data-file-shrink-does-not-affect-performance.aspx > > Also look at > > http://www.karaszi.com/SQLServer/info_dont_shrink.asp > http://www.sqlskills.com/blogs/paul/post/backup-log-with-no_log-use-abuse-and-undocumented-trace-flags-to-stop-it.aspx > > John > . > Please bear with me as I am not a DBA, but a developer. We run a full backup every night at 1:00AM so I am unsure of what you mean by " correct backup regime". Maybe some background will help. We are developing a conversion via stored procedures to move data and redesign the current database (both in 2008). We use a series of TRUNCATE TABLE commands before doing so (this may be a cause of the large log file). I am open to any suggestions as once the log file gets down to a workable size (and that is still a problem) I would like to keep it there. Thanks. David
From: Andrew J. Kelly on 13 Aug 2010 09:12 Unless you are in SIMPLE recovery mode you must do regular (hourly, every 15 minutes etc.) transaction log backups in addition to the FULL backup in order to reuse the space in the log file. If you are in FULL recovery mode and the transactions in the log file have not been backed up you cannot reuse that portion and the log file keeps growing. -- Andrew J. Kelly SQL MVP Solid Quality Mentors "DavidC" <dlchase(a)lifetimeinc.com> wrote in message news:47845817-48B9-4E2C-A048-5705342DA237(a)microsoft.com... > "John Bell" wrote: > >> On Thu, 12 Aug 2010 17:38:03 -0700, DavidC <dlchase(a)lifetimeinc.com> >> wrote: >> >> >I cannot reduce the size of my log file which has grown to 43GB. I >> >tried >> >using DBCC SHRINKDATABASE but got the following message and not sure how >> >to >> >shrink it. Any help is appreciated. >> > >> >DBCC SHRINKDATABASE: File ID 1 of database ID 10 was skipped because the >> >file does not have enough free space to reclaim. >> >Cannot shrink log file 2 (MCFICore_log) because of minimum log space >> >required. >> >> >> Hi >> >> Under normal circumstances you shouldn't want to shrink the log file, >> with the correct backup regime the log file should stay at a stable >> size that covers normal usage. You should certainly not use >> SHRINKDATABASE as this will also try to shrink the data file which can >> seriously affect performance. >> http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(930)-data-file-shrink-does-not-affect-performance.aspx >> >> Also look at >> >> http://www.karaszi.com/SQLServer/info_dont_shrink.asp >> http://www.sqlskills.com/blogs/paul/post/backup-log-with-no_log-use-abuse-and-undocumented-trace-flags-to-stop-it.aspx >> >> John >> . >> > > Please bear with me as I am not a DBA, but a developer. We run a full > backup every night at 1:00AM so I am unsure of what you mean by " correct > backup regime". Maybe some background will help. We are developing a > conversion via stored procedures to move data and redesign the current > database (both in 2008). We use a series of TRUNCATE TABLE commands > before > doing so (this may be a cause of the large log file). I am open to any > suggestions as once the log file gets down to a workable size (and that is > still a problem) I would like to keep it there. Thanks. > > David
From: DavidC on 13 Aug 2010 11:03 OK, that worked. I was suprised as I had the log file setup as "restricted" to no more that 2GB. Thanks. -- David "Andrew J. Kelly" wrote: > Unless you are in SIMPLE recovery mode you must do regular (hourly, every 15 > minutes etc.) transaction log backups in addition to the FULL backup in > order to reuse the space in the log file. If you are in FULL recovery mode > and the transactions in the log file have not been backed up you cannot > reuse that portion and the log file keeps growing. > > -- > > Andrew J. Kelly SQL MVP > Solid Quality Mentors > > "DavidC" <dlchase(a)lifetimeinc.com> wrote in message > news:47845817-48B9-4E2C-A048-5705342DA237(a)microsoft.com... > > "John Bell" wrote: > > > >> On Thu, 12 Aug 2010 17:38:03 -0700, DavidC <dlchase(a)lifetimeinc.com> > >> wrote: > >> > >> >I cannot reduce the size of my log file which has grown to 43GB. I > >> >tried > >> >using DBCC SHRINKDATABASE but got the following message and not sure how > >> >to > >> >shrink it. Any help is appreciated. > >> > > >> >DBCC SHRINKDATABASE: File ID 1 of database ID 10 was skipped because the > >> >file does not have enough free space to reclaim. > >> >Cannot shrink log file 2 (MCFICore_log) because of minimum log space > >> >required. > >> > >> > >> Hi > >> > >> Under normal circumstances you shouldn't want to shrink the log file, > >> with the correct backup regime the log file should stay at a stable > >> size that covers normal usage. You should certainly not use > >> SHRINKDATABASE as this will also try to shrink the data file which can > >> seriously affect performance. > >> http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(930)-data-file-shrink-does-not-affect-performance.aspx > >> > >> Also look at > >> > >> http://www.karaszi.com/SQLServer/info_dont_shrink.asp > >> http://www.sqlskills.com/blogs/paul/post/backup-log-with-no_log-use-abuse-and-undocumented-trace-flags-to-stop-it.aspx > >> > >> John > >> . > >> > > > > Please bear with me as I am not a DBA, but a developer. We run a full > > backup every night at 1:00AM so I am unsure of what you mean by " correct > > backup regime". Maybe some background will help. We are developing a > > conversion via stored procedures to move data and redesign the current > > database (both in 2008). We use a series of TRUNCATE TABLE commands > > before > > doing so (this may be a cause of the large log file). I am open to any > > suggestions as once the log file gets down to a workable size (and that is > > still a problem) I would like to keep it there. Thanks. > > > > David > > . >
|
Pages: 1 Prev: import file problem Next: Creating indexes increasing size of datafile |