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
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.
|||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.|||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.
>
> Yes, check Books Online.
>
> No, that's for DBCC SHRINKDATABASE 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 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...[vbcol=seagreen]
> 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:
|||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.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment