how to use dbcc shrinkfile to shrink 180GB Transaction Log

I have a sql server 2000 database configured on Full Recovery Model. The
transaction log is growing quite big (~180GB currently) and I have been
looking for solution to shrink this down.
I have been reading on using the dbcc shrinkfile command on this forum and
have attempted to shrink the transaction log by issuing the dbcc shrinkfile
(database name) but the transaction log did not go down (verified with the
dbcc sqlperf(logspace) command when I did this. The log size did not change
at all. Could someone explain to me why this is not working?
I read on some discussions that a transaction log backup is necessary. In
my case, is it really necessary to backup log before performing the dbcc
shrinkfile command? Will it work without a backup? Also, could someone
explain to me the the meaning of 'target size' in the dbcc shrinkfile
(dbname, target-size) command? Is it referring to the final size after
shrinking? or Is it referring to percent free space to retain after shrinking?
Thanks,
emPlease verify the commands I am going to write about before
running them yourself.
There is an undocumented and unsupported command DBCC LOGINFO
that you should search more about before running it on a database. I am
not sure if there's any real risk of causing problems to your database by
running this command. Search the net before running it.
Do DBCC LOGINFO(DatabaseName) and scroll down to the bottom
of the records. There's a column called Status, if they have a value of 2
then they are active VLOG (Virtual Log files) and shrinking would not occur
because shrinking a log file or a tlog file needs to be done from the end of
the file.
You would issue BACKUP LOG DatabaseName that would make those active
VLOG into inactive. I believe BACKUP LOG would issue a
CHECKPOINT command.
> explain to me the the meaning of 'target size' in the dbcc shrinkfile
> (dbname, target-size) command? Is it referring to the final size after
> shrinking?
Yes, check Books Online.
> or Is it referring to percent free space to retain after shrinking?
No, that's for DBCC SHRINKDATABASE command
> I read on some discussions that a transaction log backup is necessary. In
> my case, is it really necessary to backup log before performing the dbcc
> shrinkfile command?
Yes, if a scheduled transaction logs are not being done then you would need
to manually (or a scheduled script) run BACKUP LOG in order to truncate
the tlog file. Truncating it does not shrink it. It justs marks virtual log
files to
inactive and they get re-used instead of claiming new physical space.
Please verify everything I've said here in Books Online and do further
research on the net. I can't be 100% sure that everything I said is 100%
accurate. Sorry.|||You need to specify the Log file name not the database name to shrink the
log. BooksOnLine has pretty good documentation on DBCC SHRINK FILE that I
suggest you read. But these will get you started as well:
http://www.support.microsoft.com/?id=272318 Shrinking Log in SQL Server
2000 with DBCC SHRINKFILE
http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
http://www.nigelrivett.net/TransactionLogFileGrows_1.html Log File issues
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=873235 How to stop the log file from
growing
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"emgeneral" <emgeneral@.discussions.microsoft.com> wrote in message
news:50A2B8C1-A449-4065-B5E8-ED3279647730@.microsoft.com...
>I have a sql server 2000 database configured on Full Recovery Model. The
> transaction log is growing quite big (~180GB currently) and I have been
> looking for solution to shrink this down.
> I have been reading on using the dbcc shrinkfile command on this forum and
> have attempted to shrink the transaction log by issuing the dbcc
> shrinkfile
> (database name) but the transaction log did not go down (verified with the
> dbcc sqlperf(logspace) command when I did this. The log size did not
> change
> at all. Could someone explain to me why this is not working?
> I read on some discussions that a transaction log backup is necessary. In
> my case, is it really necessary to backup log before performing the dbcc
> shrinkfile command? Will it work without a backup? Also, could someone
> explain to me the the meaning of 'target size' in the dbcc shrinkfile
> (dbname, target-size) command? Is it referring to the final size after
> shrinking? or Is it referring to percent free space to retain after
> shrinking?
> Thanks,
> em
>|||I always use the backup log, then use SQL Enterprise manager to shrink
it down:
Step 1:
backup log DBNAME with truncate_only
Step 2:
Right click on DB within Enterprise Manager > Shrink Database > Files
Button > Select log file > Specify size to shrink it to (I usually
select the minimum allowed size).
Step 3:
Backup your transaction logs with a maintenance plan
Step 4:
Have auto-shrink option checked.
timcl|||Hi Serge,
Thanks for clarifying this me; I understood why the size didn't go down when
I issued dbcc shrinkfile command, that is, because all the virtual logs have
a status of 2 (active).
RECAP: to shrink transaction log
1) BACKUP LOG <databasename> truncateonly -- this would turn all active
vlogs from a status of 2 to 0 (serve as a checkpoint).
2) DBCC SHRINKFILE (tlogname, size) -- this would shrink the tlog to the
desired size
Question: Is there a formula to determine a target size to shrink tlog to?
I have a 180GB file size and I am uncertain as to how much I am allowed to
shrink to? What is going to happen if I set a TargetSize to a size that
cannot be shrunk? There are explainations from the MSDN online, however, not
clear to me to understand fully. Could you explain this?
Is there any affect if I perform this during business hours (other than
performance)?
--em
"serge" wrote:
> Please verify the commands I am going to write about before
> running them yourself.
> There is an undocumented and unsupported command DBCC LOGINFO
> that you should search more about before running it on a database. I am
> not sure if there's any real risk of causing problems to your database by
> running this command. Search the net before running it.
> Do DBCC LOGINFO(DatabaseName) and scroll down to the bottom
> of the records. There's a column called Status, if they have a value of 2
> then they are active VLOG (Virtual Log files) and shrinking would not occur
> because shrinking a log file or a tlog file needs to be done from the end of
> the file.
> You would issue BACKUP LOG DatabaseName that would make those active
> VLOG into inactive. I believe BACKUP LOG would issue a
> CHECKPOINT command.
> > explain to me the the meaning of 'target size' in the dbcc shrinkfile
> > (dbname, target-size) command? Is it referring to the final size after
> > shrinking?
> Yes, check Books Online.
> > or Is it referring to percent free space to retain after shrinking?
> No, that's for DBCC SHRINKDATABASE command
>
> > I read on some discussions that a transaction log backup is necessary. In
> > my case, is it really necessary to backup log before performing the dbcc
> > shrinkfile command?
> Yes, if a scheduled transaction logs are not being done then you would need
> to manually (or a scheduled script) run BACKUP LOG in order to truncate
> the tlog file. Truncating it does not shrink it. It justs marks virtual log
> files to
> inactive and they get re-used instead of claiming new physical space.
> Please verify everything I've said here in Books Online and do further
> research on the net. I can't be 100% sure that everything I said is 100%
> accurate. Sorry.
>|||Thanks for the help!
I tried the links but most of the links don't work. Could you verify this
for me?
em
"Andrew J. Kelly" wrote:
> You need to specify the Log file name not the database name to shrink the
> log. BooksOnLine has pretty good documentation on DBCC SHRINK FILE that I
> suggest you read. But these will get you started as well:
> http://www.support.microsoft.com/?id=272318 Shrinking Log in SQL Server
> 2000 with DBCC SHRINKFILE
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
> considerations
> http://www.nigelrivett.net/TransactionLogFileGrows_1.html Log File issues
> http://www.support.microsoft.com/?id=317375 Log File Grows too big
> http://www.support.microsoft.com/?id=110139 Log file filling up
> http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
> and AutoShrink
> http://www.support.microsoft.com/?id=873235 How to stop the log file from
> growing
>
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "emgeneral" <emgeneral@.discussions.microsoft.com> wrote in message
> news:50A2B8C1-A449-4065-B5E8-ED3279647730@.microsoft.com...
> >I have a sql server 2000 database configured on Full Recovery Model. The
> > transaction log is growing quite big (~180GB currently) and I have been
> > looking for solution to shrink this down.
> >
> > I have been reading on using the dbcc shrinkfile command on this forum and
> > have attempted to shrink the transaction log by issuing the dbcc
> > shrinkfile
> > (database name) but the transaction log did not go down (verified with the
> > dbcc sqlperf(logspace) command when I did this. The log size did not
> > change
> > at all. Could someone explain to me why this is not working?
> >
> > I read on some discussions that a transaction log backup is necessary. In
> > my case, is it really necessary to backup log before performing the dbcc
> > shrinkfile command? Will it work without a backup? Also, could someone
> > explain to me the the meaning of 'target size' in the dbcc shrinkfile
> > (dbname, target-size) command? Is it referring to the final size after
> > shrinking? or Is it referring to percent free space to retain after
> > shrinking?
> >
> > Thanks,
> > em
> >
>|||I clicked on each link in your reply and each one worked for me. If you
google the KB # you should find them as well.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"emgeneral" <emgeneral@.discussions.microsoft.com> wrote in message
news:71B7286F-E9F9-4E04-83C6-BE5CF0E14E4F@.microsoft.com...
> Thanks for the help!
> I tried the links but most of the links don't work. Could you verify this
> for me?
> em
>
> "Andrew J. Kelly" wrote:
>> You need to specify the Log file name not the database name to shrink the
>> log. BooksOnLine has pretty good documentation on DBCC SHRINK FILE that I
>> suggest you read. But these will get you started as well:
>> http://www.support.microsoft.com/?id=272318 Shrinking Log in SQL Server
>> 2000 with DBCC SHRINKFILE
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
>> considerations
>> http://www.nigelrivett.net/TransactionLogFileGrows_1.html Log File
>> issues
>> http://www.support.microsoft.com/?id=317375 Log File Grows too big
>> http://www.support.microsoft.com/?id=110139 Log file filling up
>> http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
>> and AutoShrink
>> http://www.support.microsoft.com/?id=873235 How to stop the log file
>> from
>> growing
>>
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "emgeneral" <emgeneral@.discussions.microsoft.com> wrote in message
>> news:50A2B8C1-A449-4065-B5E8-ED3279647730@.microsoft.com...
>> >I have a sql server 2000 database configured on Full Recovery Model.
>> >The
>> > transaction log is growing quite big (~180GB currently) and I have been
>> > looking for solution to shrink this down.
>> >
>> > I have been reading on using the dbcc shrinkfile command on this forum
>> > and
>> > have attempted to shrink the transaction log by issuing the dbcc
>> > shrinkfile
>> > (database name) but the transaction log did not go down (verified with
>> > the
>> > dbcc sqlperf(logspace) command when I did this. The log size did not
>> > change
>> > at all. Could someone explain to me why this is not working?
>> >
>> > I read on some discussions that a transaction log backup is necessary.
>> > In
>> > my case, is it really necessary to backup log before performing the
>> > dbcc
>> > shrinkfile command? Will it work without a backup? Also, could
>> > someone
>> > explain to me the the meaning of 'target size' in the dbcc shrinkfile
>> > (dbname, target-size) command? Is it referring to the final size after
>> > shrinking? or Is it referring to percent free space to retain after
>> > shrinking?
>> >
>> > Thanks,
>> > em
>> >
>>|||On Thu, 21 Feb 2008 14:31:10 -0500, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:
>I clicked on each link in your reply and each one worked for me. If you
>google the KB # you should find them as well.
They did not open for me when the question was first asked, but they
are opening now. I suspect server problems.
Roy Harvey
Beacon Falls, CT|||> Have auto-shrink option checked.
No, do not have the auto-shrink option checked.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp|||> 2) DBCC SHRINKFILE (tlogname, size) -- this would shrink the tlog to the
> desired size
> Question: Is there a formula to determine a target size to shrink tlog to?
> I have a 180GB file size and I am uncertain as to how much I am allowed to
> shrink to? What is going to happen if I set a TargetSize to a size that
> cannot be shrunk? There are explainations from the MSDN online, however,
> not
> clear to me to understand fully. Could you explain this?
I am sure some of the links provided by Andrew J. Kelly explain this
stuff very well. But to answer you what I know is you're supposed
to start with a small size for the tlog file and then have a scheduled
tlog backup happening on a regular basis and let it grow for a few days
to get an idea how much your tlog is growing to. For a 180GB database
I have right now, the tlog is 4GB at this moment.
> Is there any affect if I perform this during business hours (other than
> performance)?
Yes, definitely. Specially shrinking a huge file of that size. I believe all
or
most DBCC commands are I/O intensive, SHRINKFILE and
SHRINKDATABASE are for sure very I/O intensive.
Do not run it during business hours, trying to shrink a log that size, I
won't be
surprised if it took a very long time. I don't know 10-20+ hours maybe?
I don't recall where i got this from, but this is what I've used lately
to quickly shrink the tlog files. There's no way I would want to shrink a
180GB tlog file without the following statements.
alter database MyDatabaseName
set recovery simple
dbcc shrinkfile (MyDatabaseName_Log, 1)
alter database MyDatabaseName
set recovery full
Please take the time to read the articles provided by
Andrew J. Kelly and do a bit more research on the stuff
I said. I know I'll be reading those links in the next few
days.|||I tried the link this morning and it is finally working. Thanks.
"Roy Harvey (SQL Server MVP)" <roy_harvey@.snet.net> wrote in message
news:pikrr35th7gneuiv2oo5171rkujk4hrdc5@.4ax.com...
> On Thu, 21 Feb 2008 14:31:10 -0500, "Andrew J. Kelly"
> <sqlmvpnooospam@.shadhawk.com> wrote:
>>I clicked on each link in your reply and each one worked for me. If you
>>google the KB # you should find them as well.
> They did not open for me when the question was first asked, but they
> are opening now. I suspect server problems.
> Roy Harvey
> Beacon Falls, CT|||What is the benefit of changing the recovery model from full to simple
before issueing the dbcc shrinkfile command?
FYI -- The links by Andrew J. Kelly have not been working till this morning.
I'll catch up with the reading but please let me know just in case I have
problem with accessing the links later on the day...
Thanks,
em
"serge" <sergea@.nospam.ehmail.com> wrote in message
news:F3858C8B-CDC1-4175-972E-ACE51790DA54@.microsoft.com...
>> 2) DBCC SHRINKFILE (tlogname, size) -- this would shrink the tlog to the
>> desired size
>> Question: Is there a formula to determine a target size to shrink tlog
>> to?
>> I have a 180GB file size and I am uncertain as to how much I am allowed
>> to
>> shrink to? What is going to happen if I set a TargetSize to a size that
>> cannot be shrunk? There are explainations from the MSDN online, however,
>> not
>> clear to me to understand fully. Could you explain this?
> I am sure some of the links provided by Andrew J. Kelly explain this
> stuff very well. But to answer you what I know is you're supposed
> to start with a small size for the tlog file and then have a scheduled
> tlog backup happening on a regular basis and let it grow for a few days
> to get an idea how much your tlog is growing to. For a 180GB database
> I have right now, the tlog is 4GB at this moment.
>
>> Is there any affect if I perform this during business hours (other than
>> performance)?
> Yes, definitely. Specially shrinking a huge file of that size. I believe
> all or
> most DBCC commands are I/O intensive, SHRINKFILE and
> SHRINKDATABASE are for sure very I/O intensive.
> Do not run it during business hours, trying to shrink a log that size, I
> won't be
> surprised if it took a very long time. I don't know 10-20+ hours maybe?
> I don't recall where i got this from, but this is what I've used lately
> to quickly shrink the tlog files. There's no way I would want to shrink a
> 180GB tlog file without the following statements.
> alter database MyDatabaseName
> set recovery simple
> dbcc shrinkfile (MyDatabaseName_Log, 1)
> alter database MyDatabaseName
> set recovery full
>
> Please take the time to read the articles provided by
> Andrew J. Kelly and do a bit more research on the stuff
> I said. I know I'll be reading those links in the next few
> days.
>
>|||> What is the benefit of changing the recovery model from full to simple
> before issueing the dbcc shrinkfile command?
It's actually in SQL Books Online:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/e02b2318-bee9-4d84-a61f-2fddcf268c9f.htm
DBCC SHRINKFILE (Transact-SQL)
B. Shrinking a log file to a specified target size
The following example shrinks the log file in the AdventureWorks database to
1 MB. To allow the DBCC SHRINKFILE command to shrink the file, the file is
first truncated by setting the database recovery model to SIMPLE.
USE AdventureWorks;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AdventureWorks
SET RECOVERY FULL;
GO
Make sure you understand switching to the Simple RecoveryModel you would be
breaking the log chain. Read a bit morein Books Online before doing so.Also
read thisSwitching from the Simple Recovery Model
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/2b59bdfe-7d8c-4322-84b8-f5127ddca5fa.htm|||Oh the oldest sql issue in the book.
The log file grows until you do a log file backup.
So use dbcc shrinkfile and then add to your current backups and log
backup...
Regards,
Tom Bizannes
Sydney, Australia