Large log file

Hi:
I have a 10 GB log file that has remained exactly the same size even when
database was only 2GB. Now database is 9GB. I noticed that log file was not
alloed to grow in Enterprise Manager and that our recovery mode was full.
My Questions are
My SQL server recovery is set to full. What should I backup before I
statrt. I plan on backing the full directory before I start.
And if I switch the recovery mode to simple would it automatically delete or
shrink the log file. Our Server utilization is at constant 100% since SP4
(SQL 2000). Before it was 50% to 70% at most.
I am newbie to SQL but find it fascinating. I am also not fmiliar with Query
Analyzer.
Thanks
JM
JBM,
1. Backup the transaction log.
2. Perform a DBCC SHRINKFILE on the transaction log file
If you set the recovery mode to SIMPLE and clear the log you'll loose the
ability to recover those transaction since the last transaction log backup
in case of a failure. With the first option you'll still have that ability.
HTH
Jerry
"JBM" <JBM@.nowhere.com> wrote in message
news:exlhNIexFHA.2792@.tk2msftngp13.phx.gbl...
> Hi:
> I have a 10 GB log file that has remained exactly the same size even when
> database was only 2GB. Now database is 9GB. I noticed that log file was
> not alloed to grow in Enterprise Manager and that our recovery mode was
> full.
> My Questions are
> My SQL server recovery is set to full. What should I backup before I
> statrt. I plan on backing the full directory before I start.
> And if I switch the recovery mode to simple would it automatically delete
> or shrink the log file. Our Server utilization is at constant 100% since
> SP4 (SQL 2000). Before it was 50% to 70% at most.
> I am newbie to SQL but find it fascinating. I am also not fmiliar with
> Query Analyzer.
>
> Thanks
> JM
>
|||Hi,
If your database is not production then change the recovery model to simple
using below script:-
ALTER DATABASE <DBNAME> SET RECOVERY SIMPLE
Now use the below command to see if the log file is free
DBCC SQLPERF(LOGSPACE)
If the log is free then issue the below command to shrink the physical file.
DBCC SHRINKFILE('Logical_ldf_file_name',Truncateonly')
Since the LDF file is huge instead of giving Truncateonly option you can
shrink the file in small sizes say 1000 MB on each execution.
In this case replace Truncateonly with 1000.
Thanks
Hari
SQL Server MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OmJv$QexFHA.2540@.TK2MSFTNGP09.phx.gbl...
> JBM,
> 1. Backup the transaction log.
> 2. Perform a DBCC SHRINKFILE on the transaction log file
> If you set the recovery mode to SIMPLE and clear the log you'll loose the
> ability to recover those transaction since the last transaction log backup
> in case of a failure. With the first option you'll still have that
> ability.
> HTH
> Jerry
> "JBM" <JBM@.nowhere.com> wrote in message
> news:exlhNIexFHA.2792@.tk2msftngp13.phx.gbl...
>
|||Hi Jerry:
Many thanks for responding.
I am a newbie. Could you please tell me the exeact syntax.
DBCC Shrinkfile abcd_log.ldf - Should I include the ( ) or not.
I would also like to know why i should not trucate the file and get rid of
dead transaction. Should I clear log once a week, once a day. What is an
ideal schedule. Does shrink get rid of dead (committed) transactions.
Thanks
JBM
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OmJv$QexFHA.2540@.TK2MSFTNGP09.phx.gbl...
> JBM,
> 1. Backup the transaction log.
> 2. Perform a DBCC SHRINKFILE on the transaction log file
> If you set the recovery mode to SIMPLE and clear the log you'll loose the
> ability to recover those transaction since the last transaction log backup
> in case of a failure. With the first option you'll still have that
> ability.
> HTH
> Jerry
> "JBM" <JBM@.nowhere.com> wrote in message
> news:exlhNIexFHA.2792@.tk2msftngp13.phx.gbl...
>
|||Harry:
My database is production. We run our business on it. What other
alternatives I have to regularly delete (truncate) dead (committed)
transactions.
Thanks
JBM
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OwirhyexFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Hi,
> If your database is not production then change the recovery model to
> simple using below script:-
> ALTER DATABASE <DBNAME> SET RECOVERY SIMPLE
> Now use the below command to see if the log file is free
> DBCC SQLPERF(LOGSPACE)
> If the log is free then issue the below command to shrink the physical
> file.
> DBCC SHRINKFILE('Logical_ldf_file_name',Truncateonly')
> Since the LDF file is huge instead of giving Truncateonly option you can
> shrink the file in small sizes say 1000 MB on each execution.
> In this case replace Truncateonly with 1000.
>
> Thanks
> Hari
> SQL Server MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:OmJv$QexFHA.2540@.TK2MSFTNGP09.phx.gbl...
>
|||> I am a newbie. Could you please tell me the exeact syntax.
> DBCC Shrinkfile abcd_log.ldf - Should I include the ( ) or not.
The command is documented in Books Online.

> I would also like to know why i should not trucate the file and get rid of dead transaction.
> Should I clear log once a week, once a day. What is an ideal schedule. Does shrink get rid of dead
> (committed) transactions.
The question you should ask yourself is whether you want to do both database backups and transaction
log backup or only database backups. If only database backups, set the database to simple recovery
model and SQL Server will empty the log for you. If full recovery model, the log is emptied each
time you do a transaction log backup. So, start by reading about backup and restore and recovery
models in Books Online and it will all be clear for you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"JBM" <JBM@.nowhere.com> wrote in message news:%23DLChvkxFHA.664@.tk2msftngp13.phx.gbl...
> Hi Jerry:
> Many thanks for responding.
> I am a newbie. Could you please tell me the exeact syntax.
> DBCC Shrinkfile abcd_log.ldf - Should I include the ( ) or not.
> I would also like to know why i should not trucate the file and get rid of dead transaction.
> Should I clear log once a week, once a day. What is an ideal schedule. Does shrink get rid of dead
> (committed) transactions.
> Thanks
> JBM
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:OmJv$QexFHA.2540@.TK2MSFTNGP09.phx.gbl...
>