Issues Minimizing Size of LDF Transaction Log on SQL Server 2000

Greetings,
I'm trying to minimize my 40gb .ldf transaction log. First, I tried shrinking just the transaction log by using both the Shrink Database > Files method and by scripting (DBCC SHRINKFILE) and while the transaction log seemed to go down quite a bit according to the Enterprise Manager statistics, the LDF sitting in our Program Files folder remains at about 37gb.
I then read somewhere that backing up the transaction log can also aid in minimizing its size but this has yet to make any kind of a difference. In addition, I'm also seeing here on the newsgroup that converting the database to simple recovery mode can help keep the LDF to a good size but, according to MS documentation, "there are limitations placed on the recoverability of a database if this recovery model is used." That sounds a little too eerie to me as this particular DB is quite crucial to our operation. Does anyone have any tricks to get a nearly 40gb LDF down to a decent size?
Thanks in advance for any and al information!
- J
The first and most important thing is that you understand what the
transaction log is for and how to use it for backup and recovery purposes.
It seems obvious from several things you have said that you need to do some
reading. Please study the backup and recovery topics in Books Online and
review your backup and recovery strategy before deciding how to proceed.
If you are running under full recovery then you need to make sure your
transaction log is large enough to accommodate all transactions between each
log backup. The correct size for the transaction log will therefore be
determined by how many updates your database has and how often you do
backups. Note that backing up the log will not reduce its size but it will
allow SQL Server to re-use that space. On the information given therefore
there is no reason to believe you need do anything immediately. What makes
you want to reduce the size of the log?
David Portas
|||Dear Jason,
Thank you for posting here.
Before running the DBCC SHRINKFILE to shrink the Transaction Log, we must
first run a BACKUP LOG statement to backup the current Transaction Log. As
mentioned by David, although backing up the log will not directly reduce
its size, it removes the inactive portion of the log and then "DBCC
SHRINKFILE" command will be able to free these spaces. For your reference,
the following actions are required to complete the shrinking of the
transaction log:
1. You must run a BACKUP LOG statement to free up space by removing the
inactive portion of the log.
2. You must run DBCC SHRINKFILE again with the desired target size until
the log file shrinks to the target size.
Below is the related KB article:
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/kb/272318
Additionally, although Simple Recovery mode can keep the LDF file to a
smaller size, there are some limitation s exist. For example, we are unable
to recover to a specific point in time and can only recover to the end of a
backup. This recovery mode is NOT recommended for the database in the
production environment. You can refer to the SQL Server Book Online about
this topic (http://msdn2.microsoft.com/en-us/library/ms189275.aspx)
Hope it helps.
Have a nice day!
Best regards,
Adams Qu, MCSE, MCDBA, MCTS
Microsoft Online Support
Microsoft Global Technical Support Center
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "Jason H." <cupahr@.community.nospam>
| Subject: Issues Minimizing Size of LDF Transaction Log on SQL Server 2000
| Date: Thu, 13 Mar 2008 15:10:51 -0400
| Lines: 69
| Organization: CUPA-HR
| Message-ID: <CB7EEA2C-2E49-43E1-9491-E4082D529223@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: multipart/alternative;
| boundary="--=_NextPart_000_0028_01C8851C.6D2EC7E0"
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Windows Mail 6.0.6000.16480
| X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6000.16545
| X-MS-CommunityGroup-PostID: {CB7EEA2C-2E49-43E1-9491-E4082D529223}
| X-MS-CommunityGroup-MessageCategory:
{E4FCE0A9-75B4-4168-BFF9-16C22D8747EC}
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: mail.cupahr.org 65.23.122.33
| Path: TK2MSFTNGHUB02.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSF TNGP02.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:38532
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Greetings,
| I'm trying to minimize my 40gb .ldf transaction log. First, I tried
shrinking just the transaction log by using both the Shrink Database >
Files method and by scripting (DBCC SHRINKFILE) and while the transaction
log seemed to go down quite a bit according to the Enterprise Manager
statistics, the LDF sitting in our Program Files folder remains at about
37gb.
| I then read somewhere that backing up the transaction log can also aid in
minimizing its size but this has yet to make any kind of a difference. In
addition, I'm also seeing here on the newsgroup that converting the
database to simple recovery mode can help keep the LDF to a good size but,
according to MS documentation, "there are limitations placed on the
recoverability of a database if this recovery model is used." That sounds
a little too eerie to me as this particular DB is quite crucial to our
operation. Does anyone have any tricks to get a nearly 40gb LDF down to a
decent size?
| Thanks in advance for any and al information!
| - J
|
|||Dear Jason,
We wanted to see if the information provided was helpful. Please keep us
posted on your progress and let us know if you have any additional
questions or concerns.
We are looking forward to your response.
Have a nice day!
Best regards,
Adams Qu, MCSE, MCDBA, MCTS
Microsoft Online Support
Microsoft Global Technical Support Center
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| X-Tomcat-ID: 89922039
| References: <CB7EEA2C-2E49-43E1-9491-E4082D529223@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain
| Content-Transfer-Encoding: 7bit
| From: v-adamqu@.online.microsoft.com (Adams Qu [MSFT])
| Organization: Microsoft
| Date: Fri, 14 Mar 2008 09:46:48 GMT
| Subject: RE: Issues Minimizing Size of LDF Transaction Log on SQL Server
2000
| X-Tomcat-NG: microsoft.public.sqlserver.server
| Message-ID: <LixsThbhIHA.360@.TK2MSFTNGHUB02.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| Lines: 99
| Path: TK2MSFTNGHUB02.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:38573
| NNTP-Posting-Host: tk5tomimport2.phx.gbl 10.201.218.20
|
| Dear Jason,
|
| Thank you for posting here.
|
| Before running the DBCC SHRINKFILE to shrink the Transaction Log, we must
| first run a BACKUP LOG statement to backup the current Transaction Log.
As
| mentioned by David, although backing up the log will not directly reduce
| its size, it removes the inactive portion of the log and then "DBCC
| SHRINKFILE" command will be able to free these spaces. For your
reference,
| the following actions are required to complete the shrinking of the
| transaction log:
|
| 1. You must run a BACKUP LOG statement to free up space by removing the
| inactive portion of the log.
| 2. You must run DBCC SHRINKFILE again with the desired target size until
| the log file shrinks to the target size.
|
| Below is the related KB article:
|
| INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
| http://support.microsoft.com/kb/272318
|
| Additionally, although Simple Recovery mode can keep the LDF file to a
| smaller size, there are some limitation s exist. For example, we are
unable
| to recover to a specific point in time and can only recover to the end of
a
| backup. This recovery mode is NOT recommended for the database in the
| production environment. You can refer to the SQL Server Book Online about
| this topic (http://msdn2.microsoft.com/en-us/library/ms189275.aspx)
|
| Hope it helps.
|
| Have a nice day!
|
| Best regards,
|
| Adams Qu, MCSE, MCDBA, MCTS
| Microsoft Online Support
|
| Microsoft Global Technical Support Center
|
| Get Secure! - www.microsoft.com/security
| ================================================== ===
| When responding to posts, please "Reply to Group" via your newsreader so
| that others may learn and benefit from your issue.
| ================================================== ===
| This posting is provided "AS IS" with no warranties, and confers no
rights.
|
| --
| | From: "Jason H." <cupahr@.community.nospam>
| | Subject: Issues Minimizing Size of LDF Transaction Log on SQL Server
2000
| | Date: Thu, 13 Mar 2008 15:10:51 -0400
| | Lines: 69
| | Organization: CUPA-HR
| | Message-ID: <CB7EEA2C-2E49-43E1-9491-E4082D529223@.microsoft.com>
| | MIME-Version: 1.0
| | Content-Type: multipart/alternative;
| | boundary="--=_NextPart_000_0028_01C8851C.6D2EC7E0"
| | X-Priority: 3
| | X-MSMail-Priority: Normal
| | X-Newsreader: Microsoft Windows Mail 6.0.6000.16480
| | X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6000.16545
| | X-MS-CommunityGroup-PostID: {CB7EEA2C-2E49-43E1-9491-E4082D529223}
| | X-MS-CommunityGroup-MessageCategory:
| {E4FCE0A9-75B4-4168-BFF9-16C22D8747EC}
| | Newsgroups: microsoft.public.sqlserver.server
| | NNTP-Posting-Host: mail.cupahr.org 65.23.122.33
| | Path: TK2MSFTNGHUB02.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSF TNGP02.phx.gbl
| | Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:38532
| | X-Tomcat-NG: microsoft.public.sqlserver.server
| |
| | Greetings,
| | I'm trying to minimize my 40gb .ldf transaction log. First, I tried
| shrinking just the transaction log by using both the Shrink Database >
| Files method and by scripting (DBCC SHRINKFILE) and while the transaction
| log seemed to go down quite a bit according to the Enterprise Manager
| statistics, the LDF sitting in our Program Files folder remains at about
| 37gb.
| | I then read somewhere that backing up the transaction log can also aid
in
| minimizing its size but this has yet to make any kind of a difference.
In
| addition, I'm also seeing here on the newsgroup that converting the
| database to simple recovery mode can help keep the LDF to a good size
but,
| according to MS documentation, "there are limitations placed on the
| recoverability of a database if this recovery model is used." That
sounds
| a little too eerie to me as this particular DB is quite crucial to our
| operation. Does anyone have any tricks to get a nearly 40gb LDF down to
a
| decent size?
| | Thanks in advance for any and al information!
| | - J
| |
|
|