Can't shrink log file

I have an SQL Server 2000 database where the data file is 120 MB and the log
file is 330 MB. For some reason I am now unable to shrink the log file,
which has never been a problem before. I am doing this in Enterprise
Manager, selecting Shrink Database then selecting the log file and the
"compress pages and truncate free space" option. It says that the entire 330
MB is being used and won't shrink it. How can I fix this? Is it OK to close
down SQL Server and just delete the log file?
GlennDon't delete the file. Check out http://www.karaszi.com/SQLServer/in...ink
.asp where you
find some information about shrinking log file.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Glenn Alcott" <galcott@.nyc.rr.com> wrote in message news:47616ac1$0$2353$4c368faf@.roadrunne
r.com...
>I have an SQL Server 2000 database where the data file is 120 MB and the lo
g
> file is 330 MB. For some reason I am now unable to shrink the log file,
> which has never been a problem before. I am doing this in Enterprise
> Manager, selecting Shrink Database then selecting the log file and the
> "compress pages and truncate free space" option. It says that the entire 3
30
> MB is being used and won't shrink it. How can I fix this? Is it OK to clos
e
> down SQL Server and just delete the log file?
> Glenn
>|||You most likely have an open transaction preventing the log from reusing the
space. Run DBCC OPENTRAN() in that db to see. If not you probably have the
recovery set to FULL but are not doing log backups.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Glenn Alcott" <galcott@.nyc.rr.com> wrote in message
news:47616ac1$0$2353$4c368faf@.roadrunner
.com...
>I have an SQL Server 2000 database where the data file is 120 MB and the
>log
> file is 330 MB. For some reason I am now unable to shrink the log file,
> which has never been a problem before. I am doing this in Enterprise
> Manager, selecting Shrink Database then selecting the log file and the
> "compress pages and truncate free space" option. It says that the entire
> 330
> MB is being used and won't shrink it. How can I fix this? Is it OK to
> close
> down SQL Server and just delete the log file?
> Glenn
>|||Glenn,
You can always shrink the log using t-sql as well.
First you need to backup the log but in this case its for the purpose of
reducing the size and not actually saving the log.
BACKUP LOG WITH TRUNCATE_ONLY
then you need to use the dbcc shrink file command
DBCC SHRINKFILE (LogicalFilename, size you would like to reduce it to);
http://msdn2.microsoft.com/en-us/library/ms189493.aspx
that should take care of it.
--
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
"Glenn Alcott" <galcott@.nyc.rr.com> wrote in message
news:47616ac1$0$2353$4c368faf@.roadrunner
.com...
>I have an SQL Server 2000 database where the data file is 120 MB and the
>log
> file is 330 MB. For some reason I am now unable to shrink the log file,
> which has never been a problem before. I am doing this in Enterprise
> Manager, selecting Shrink Database then selecting the log file and the
> "compress pages and truncate free space" option. It says that the entire
> 330
> MB is being used and won't shrink it. How can I fix this? Is it OK to
> close
> down SQL Server and just delete the log file?
> Glenn
>|||Try to Dump Tran Databasename with no_log command then selecting Shrink
Database
"Glenn Alcott" <galcott@.nyc.rr.com> wrote in message
news:47616ac1$0$2353$4c368faf@.roadrunner
.com...
> I have an SQL Server 2000 database where the data file is 120 MB and the
> log
> file is 330 MB. For some reason I am now unable to shrink the log file,
> which has never been a problem before. I am doing this in Enterprise
> Manager, selecting Shrink Database then selecting the log file and the
> "compress pages and truncate free space" option. It says that the entire
> 330
> MB is being used and won't shrink it. How can I fix this? Is it OK to
> close
> down SQL Server and just delete the log file?
> Glenn
>

Can't shrink log file

I have an SQL Server 2000 database where the data file is 120 MB and the log
file is 330 MB. For some reason I am now unable to shrink the log file,
which has never been a problem before. I am doing this in Enterprise
Manager, selecting Shrink Database then selecting the log file and the
"compress pages and truncate free space" option. It says that the entire 330
MB is being used and won't shrink it. How can I fix this? Is it OK to close
down SQL Server and just delete the log file?
Glenn
You most likely have an open transaction preventing the log from reusing the
space. Run DBCC OPENTRAN() in that db to see. If not you probably have the
recovery set to FULL but are not doing log backups.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Glenn Alcott" <galcott@.nyc.rr.com> wrote in message
news:47616ac1$0$2353$4c368faf@.roadrunner.com...
>I have an SQL Server 2000 database where the data file is 120 MB and the
>log
> file is 330 MB. For some reason I am now unable to shrink the log file,
> which has never been a problem before. I am doing this in Enterprise
> Manager, selecting Shrink Database then selecting the log file and the
> "compress pages and truncate free space" option. It says that the entire
> 330
> MB is being used and won't shrink it. How can I fix this? Is it OK to
> close
> down SQL Server and just delete the log file?
> Glenn
>
|||Glenn,
You can always shrink the log using t-sql as well.
First you need to backup the log but in this case its for the purpose of
reducing the size and not actually saving the log.
BACKUP LOG WITH TRUNCATE_ONLY
then you need to use the dbcc shrink file command
DBCC SHRINKFILE (LogicalFilename, size you would like to reduce it to);
http://msdn2.microsoft.com/en-us/library/ms189493.aspx
that should take care of it.
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
"Glenn Alcott" <galcott@.nyc.rr.com> wrote in message
news:47616ac1$0$2353$4c368faf@.roadrunner.com...
>I have an SQL Server 2000 database where the data file is 120 MB and the
>log
> file is 330 MB. For some reason I am now unable to shrink the log file,
> which has never been a problem before. I am doing this in Enterprise
> Manager, selecting Shrink Database then selecting the log file and the
> "compress pages and truncate free space" option. It says that the entire
> 330
> MB is being used and won't shrink it. How can I fix this? Is it OK to
> close
> down SQL Server and just delete the log file?
> Glenn
>
|||Try to Dump Tran Databasename with no_log command then selecting Shrink
Database
"Glenn Alcott" <galcott@.nyc.rr.com> wrote in message
news:47616ac1$0$2353$4c368faf@.roadrunner.com...
> I have an SQL Server 2000 database where the data file is 120 MB and the
> log
> file is 330 MB. For some reason I am now unable to shrink the log file,
> which has never been a problem before. I am doing this in Enterprise
> Manager, selecting Shrink Database then selecting the log file and the
> "compress pages and truncate free space" option. It says that the entire
> 330
> MB is being used and won't shrink it. How can I fix this? Is it OK to
> close
> down SQL Server and just delete the log file?
> Glenn
>

Can't remove inactive entries from transaction log

My database is set to Full Recovery model. I can shrink my transaction log
by executing a logfile backup, but when I execute a Full or Incremental
backup, this option is disabled. I don't understand why. With the execution
of a successful backup, entries in the log prior to the point of backup
become irrelevant! I might need subsequent logfile transactions in the event
of a failure, but I won't need the logfile prior to the point of backup. So
why can't I shrink the logfile as part and parcel of the backup operation?
Must I always execute a separate logfile backup prior to my Full /
Incremental backup for the sole purpose of shrinking the logfile? I don't
get it.
Thanks for your advice!
- Joe Geretz -
Joseph
If I understood you correctly you have to run LOG file backup with INIT
option to clear all entries prior the FULL backup database.
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:Ol669krcEHA.2384@.TK2MSFTNGP09.phx.gbl...
> My database is set to Full Recovery model. I can shrink my transaction log
> by executing a logfile backup, but when I execute a Full or Incremental
> backup, this option is disabled. I don't understand why. With the
execution
> of a successful backup, entries in the log prior to the point of backup
> become irrelevant! I might need subsequent logfile transactions in the
event
> of a failure, but I won't need the logfile prior to the point of backup.
So
> why can't I shrink the logfile as part and parcel of the backup operation?
> Must I always execute a separate logfile backup prior to my Full /
> Incremental backup for the sole purpose of shrinking the logfile? I don't
> get it.
> Thanks for your advice!
> - Joe Geretz -
>
|||Joe,
A full database backup or differential backup DO NOT truncate the log ( as
you have discovered.) This is by design... Here is a sample backup plan
Full1 Full2
log1 log2 log3 log4 log5,,,, etc
If we die are the log 5 backup, we can restore from FULL2, then restore all
of the logs and we are good to go... But what if the FULL2 backup tape is
missing or corrupted? You can begin by restoring the FULL1 backup, then
log1, log2, log3, log4. etc and still get up-to-the-minute recover without
loss of data... Log 4 contains ALL OF THE CHANGES since log3, Even when a
full or differential database backup was done between 3 and 4. The full,
and differential backups do NOT truncate the log SPECIFICALLY to give us the
opportunity to restore from a grandparent ( FULL1 ) or even older full or
differential backup and not lose data...
If you do not need the log backups, and wish to keep the log small, you
might wish to consider using the Simple recovery model for the database...
This means you will only be able to recover to the last full or differential
backup however.
Hope this helps..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:Ol669krcEHA.2384@.TK2MSFTNGP09.phx.gbl...
> My database is set to Full Recovery model. I can shrink my transaction log
> by executing a logfile backup, but when I execute a Full or Incremental
> backup, this option is disabled. I don't understand why. With the
execution
> of a successful backup, entries in the log prior to the point of backup
> become irrelevant! I might need subsequent logfile transactions in the
event
> of a failure, but I won't need the logfile prior to the point of backup.
So
> why can't I shrink the logfile as part and parcel of the backup operation?
> Must I always execute a separate logfile backup prior to my Full /
> Incremental backup for the sole purpose of shrinking the logfile? I don't
> get it.
> Thanks for your advice!
> - Joe Geretz -
>
|||Wayne
Why not just clean up with INIT option the LOG file after each FULL backup?
Why to blow the LOG file?
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eqD5jF9cEHA.3944@.tk2msftngp13.phx.gbl...
> Joe,
> A full database backup or differential backup DO NOT truncate the log ( as
> you have discovered.) This is by design... Here is a sample backup plan
> Full1 Full2
> log1 log2 log3 log4 log5,,,, etc
> If we die are the log 5 backup, we can restore from FULL2, then restore
all
> of the logs and we are good to go... But what if the FULL2 backup tape is
> missing or corrupted? You can begin by restoring the FULL1 backup, then
> log1, log2, log3, log4. etc and still get up-to-the-minute recover without
> loss of data... Log 4 contains ALL OF THE CHANGES since log3, Even when a
> full or differential database backup was done between 3 and 4. The full,
> and differential backups do NOT truncate the log SPECIFICALLY to give us
the
> opportunity to restore from a grandparent ( FULL1 ) or even older full or
> differential backup and not lose data...
> If you do not need the log backups, and wish to keep the log small, you
> might wish to consider using the Simple recovery model for the database...
> This means you will only be able to recover to the last full or
differential[vbcol=seagreen]
> backup however.
> Hope this helps..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Joseph Geretz" <jgeretz@.nospam.com> wrote in message
> news:Ol669krcEHA.2384@.TK2MSFTNGP09.phx.gbl...
log[vbcol=seagreen]
> execution
> event
> So
operation?[vbcol=seagreen]
don't
>

Can't remove inactive entries from transaction log

My database is set to Full Recovery model. I can shrink my transaction log
by executing a logfile backup, but when I execute a Full or Incremental
backup, this option is disabled. I don't understand why. With the execution
of a successful backup, entries in the log prior to the point of backup
become irrelevant! I might need subsequent logfile transactions in the event
of a failure, but I won't need the logfile prior to the point of backup. So
why can't I shrink the logfile as part and parcel of the backup operation?
Must I always execute a separate logfile backup prior to my Full /
Incremental backup for the sole purpose of shrinking the logfile? I don't
get it.
Thanks for your advice!
- Joe Geretz -Joseph
If I understood you correctly you have to run LOG file backup with INIT
option to clear all entries prior the FULL backup database.
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:Ol669krcEHA.2384@.TK2MSFTNGP09.phx.gbl...
> My database is set to Full Recovery model. I can shrink my transaction log
> by executing a logfile backup, but when I execute a Full or Incremental
> backup, this option is disabled. I don't understand why. With the
execution
> of a successful backup, entries in the log prior to the point of backup
> become irrelevant! I might need subsequent logfile transactions in the
event
> of a failure, but I won't need the logfile prior to the point of backup.
So
> why can't I shrink the logfile as part and parcel of the backup operation?
> Must I always execute a separate logfile backup prior to my Full /
> Incremental backup for the sole purpose of shrinking the logfile? I don't
> get it.
> Thanks for your advice!
> - Joe Geretz -
>|||Joe,
A full database backup or differential backup DO NOT truncate the log ( as
you have discovered.) This is by design... Here is a sample backup plan
Full1 Full2
log1 log2 log3 log4 log5,,,, etc
If we die are the log 5 backup, we can restore from FULL2, then restore all
of the logs and we are good to go... But what if the FULL2 backup tape is
missing or corrupted? You can begin by restoring the FULL1 backup, then
log1, log2, log3, log4. etc and still get up-to-the-minute recover without
loss of data... Log 4 contains ALL OF THE CHANGES since log3, Even when a
full or differential database backup was done between 3 and 4. The full,
and differential backups do NOT truncate the log SPECIFICALLY to give us the
opportunity to restore from a grandparent ( FULL1 ) or even older full or
differential backup and not lose data...
If you do not need the log backups, and wish to keep the log small, you
might wish to consider using the Simple recovery model for the database...
This means you will only be able to recover to the last full or differential
backup however.
Hope this helps..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:Ol669krcEHA.2384@.TK2MSFTNGP09.phx.gbl...
> My database is set to Full Recovery model. I can shrink my transaction log
> by executing a logfile backup, but when I execute a Full or Incremental
> backup, this option is disabled. I don't understand why. With the
execution
> of a successful backup, entries in the log prior to the point of backup
> become irrelevant! I might need subsequent logfile transactions in the
event
> of a failure, but I won't need the logfile prior to the point of backup.
So
> why can't I shrink the logfile as part and parcel of the backup operation?
> Must I always execute a separate logfile backup prior to my Full /
> Incremental backup for the sole purpose of shrinking the logfile? I don't
> get it.
> Thanks for your advice!
> - Joe Geretz -
>|||Wayne
Why not just clean up with INIT option the LOG file after each FULL backup?
Why to blow the LOG file?
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eqD5jF9cEHA.3944@.tk2msftngp13.phx.gbl...
> Joe,
> A full database backup or differential backup DO NOT truncate the log ( as
> you have discovered.) This is by design... Here is a sample backup plan
> Full1 Full2
> log1 log2 log3 log4 log5,,,, etc
> If we die are the log 5 backup, we can restore from FULL2, then restore
all
> of the logs and we are good to go... But what if the FULL2 backup tape is
> missing or corrupted? You can begin by restoring the FULL1 backup, then
> log1, log2, log3, log4. etc and still get up-to-the-minute recover without
> loss of data... Log 4 contains ALL OF THE CHANGES since log3, Even when a
> full or differential database backup was done between 3 and 4. The full,
> and differential backups do NOT truncate the log SPECIFICALLY to give us
the
> opportunity to restore from a grandparent ( FULL1 ) or even older full or
> differential backup and not lose data...
> If you do not need the log backups, and wish to keep the log small, you
> might wish to consider using the Simple recovery model for the database...
> This means you will only be able to recover to the last full or
differential
> backup however.
> Hope this helps..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Joseph Geretz" <jgeretz@.nospam.com> wrote in message
> news:Ol669krcEHA.2384@.TK2MSFTNGP09.phx.gbl...
> > My database is set to Full Recovery model. I can shrink my transaction
log
> > by executing a logfile backup, but when I execute a Full or Incremental
> > backup, this option is disabled. I don't understand why. With the
> execution
> > of a successful backup, entries in the log prior to the point of backup
> > become irrelevant! I might need subsequent logfile transactions in the
> event
> > of a failure, but I won't need the logfile prior to the point of backup.
> So
> > why can't I shrink the logfile as part and parcel of the backup
operation?
> > Must I always execute a separate logfile backup prior to my Full /
> > Incremental backup for the sole purpose of shrinking the logfile? I
don't
> > get it.
> >
> > Thanks for your advice!
> >
> > - Joe Geretz -
> >
> >
>

Can't remove inactive entries from transaction log

My database is set to Full Recovery model. I can shrink my transaction log
by executing a logfile backup, but when I execute a Full or Incremental
backup, this option is disabled. I don't understand why. With the execution
of a successful backup, entries in the log prior to the point of backup
become irrelevant! I might need subsequent logfile transactions in the event
of a failure, but I won't need the logfile prior to the point of backup. So
why can't I shrink the logfile as part and parcel of the backup operation?
Must I always execute a separate logfile backup prior to my Full /
Incremental backup for the sole purpose of shrinking the logfile? I don't
get it.
Thanks for your advice!
- Joe Geretz -Joseph
If I understood you correctly you have to run LOG file backup with INIT
option to clear all entries prior the FULL backup database.
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:Ol669krcEHA.2384@.TK2MSFTNGP09.phx.gbl...
> My database is set to Full Recovery model. I can shrink my transaction log
> by executing a logfile backup, but when I execute a Full or Incremental
> backup, this option is disabled. I don't understand why. With the
execution
> of a successful backup, entries in the log prior to the point of backup
> become irrelevant! I might need subsequent logfile transactions in the
event
> of a failure, but I won't need the logfile prior to the point of backup.
So
> why can't I shrink the logfile as part and parcel of the backup operation?
> Must I always execute a separate logfile backup prior to my Full /
> Incremental backup for the sole purpose of shrinking the logfile? I don't
> get it.
> Thanks for your advice!
> - Joe Geretz -
>|||Joe,
A full database backup or differential backup DO NOT truncate the log ( as
you have discovered.) This is by design... Here is a sample backup plan
Full1 Full2
log1 log2 log3 log4 log5,,,, etc
If we die are the log 5 backup, we can restore from FULL2, then restore all
of the logs and we are good to go... But what if the FULL2 backup tape is
missing or corrupted? You can begin by restoring the FULL1 backup, then
log1, log2, log3, log4. etc and still get up-to-the-minute recover without
loss of data... Log 4 contains ALL OF THE CHANGES since log3, Even when a
full or differential database backup was done between 3 and 4. The full,
and differential backups do NOT truncate the log SPECIFICALLY to give us the
opportunity to restore from a grandparent ( FULL1 ) or even older full or
differential backup and not lose data...
If you do not need the log backups, and wish to keep the log small, you
might wish to consider using the Simple recovery model for the database...
This means you will only be able to recover to the last full or differential
backup however.
Hope this helps..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:Ol669krcEHA.2384@.TK2MSFTNGP09.phx.gbl...
> My database is set to Full Recovery model. I can shrink my transaction log
> by executing a logfile backup, but when I execute a Full or Incremental
> backup, this option is disabled. I don't understand why. With the
execution
> of a successful backup, entries in the log prior to the point of backup
> become irrelevant! I might need subsequent logfile transactions in the
event
> of a failure, but I won't need the logfile prior to the point of backup.
So
> why can't I shrink the logfile as part and parcel of the backup operation?
> Must I always execute a separate logfile backup prior to my Full /
> Incremental backup for the sole purpose of shrinking the logfile? I don't
> get it.
> Thanks for your advice!
> - Joe Geretz -
>|||Wayne
Why not just clean up with INIT option the LOG file after each FULL backup?
Why to blow the LOG file?
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eqD5jF9cEHA.3944@.tk2msftngp13.phx.gbl...
> Joe,
> A full database backup or differential backup DO NOT truncate the log ( as
> you have discovered.) This is by design... Here is a sample backup plan
> Full1 Full2
> log1 log2 log3 log4 log5,,,, etc
> If we die are the log 5 backup, we can restore from FULL2, then restore
all
> of the logs and we are good to go... But what if the FULL2 backup tape is
> missing or corrupted? You can begin by restoring the FULL1 backup, then
> log1, log2, log3, log4. etc and still get up-to-the-minute recover without
> loss of data... Log 4 contains ALL OF THE CHANGES since log3, Even when a
> full or differential database backup was done between 3 and 4. The full,
> and differential backups do NOT truncate the log SPECIFICALLY to give us
the
> opportunity to restore from a grandparent ( FULL1 ) or even older full or
> differential backup and not lose data...
> If you do not need the log backups, and wish to keep the log small, you
> might wish to consider using the Simple recovery model for the database...
> This means you will only be able to recover to the last full or
differential
> backup however.
> Hope this helps..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Joseph Geretz" <jgeretz@.nospam.com> wrote in message
> news:Ol669krcEHA.2384@.TK2MSFTNGP09.phx.gbl...
log[vbcol=seagreen]
> execution
> event
> So
operation?[vbcol=seagreen]
don't[vbcol=seagreen]
>