managing log file

I'm unfortunately not a DBA who is forced into being a DBA. I'm trying to
figure out why the log file for my database is almost 300MB when the data
file is only 31MB. Shrinking it does no good. I back the database up, but
that doesn't allow for making the log file any smaller either. Can someone
give me a brief explanation as to how to effectively manage my log file and
make it smaller? If it is historically keeping every single modification
ever made, I don't need it to do that past the point where I'm backing it
up.
Thanks,
Jamesyou'll need a log-backup (not a full) to allow the log to remove completed
transactions..
jobi
"James" <capricorn@.nospam.com> wrote in message
news:#BAaYK#hDHA.884@.TK2MSFTNGP10.phx.gbl...
> I'm unfortunately not a DBA who is forced into being a DBA. I'm trying to
> figure out why the log file for my database is almost 300MB when the data
> file is only 31MB. Shrinking it does no good. I back the database up,
but
> that doesn't allow for making the log file any smaller either. Can
someone
> give me a brief explanation as to how to effectively manage my log file
and
> make it smaller? If it is historically keeping every single modification
> ever made, I don't need it to do that past the point where I'm backing it
> up.
> Thanks,
> James
>|||The log file is not emptied on database backup. Make sure that you do regular log backups or
possibly put the db in simple recovery mode.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"James" <capricorn@.nospam.com> wrote in message news:%23BAaYK%23hDHA.884@.TK2MSFTNGP10.phx.gbl...
> I'm unfortunately not a DBA who is forced into being a DBA. I'm trying to
> figure out why the log file for my database is almost 300MB when the data
> file is only 31MB. Shrinking it does no good. I back the database up, but
> that doesn't allow for making the log file any smaller either. Can someone
> give me a brief explanation as to how to effectively manage my log file and
> make it smaller? If it is historically keeping every single modification
> ever made, I don't need it to do that past the point where I'm backing it
> up.
> Thanks,
> James
>|||In addition to other post refer to below mentioned urls.
http://www.support.microsoft.com/?id=256650 INF: How to
Shrink the SQL
Server 7.0 Tran Log
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.mssqlserver.com/faq/logs-shrinklog.asp Shrink
File
http://www.support.microsoft.com/?id=315512
Considerations for Autogrow and AutoShrink
http://www.support.microsoft.com/?id=272318 INF:
Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
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.mssqlserver.com/faq/logs-shrinklog.asp Shrink
File
http://www.support.microsoft.com/?id=315512
Considerations for Autogrow and AutoShrink
- Vishal|||Ok, I went in EM and right clicked the database, selected All Tasks, Backup
Database, and selected Transaction Log and executed the backup. Then I went
into All Tasks, Shrink Database. I clicked the Files button and selected
the Log File. It tells me that Space used is 30MB and its current size is
230MB. I used the "Compress pages and then truncate free space from the
file" and this had very little impact. The file is still 230MB reporting
only 30MB used. I then did the general "Shrink Database" selecting "Move
pages to the beginning of the file before shrinking", but this again yields
Space Allocated: 252MB, space free: 190MB (75%). How can I get the
Transaction Log to shrink to a size closer to the size of its contents?
Thanks,
James
"jobi" <jobi@.reply2.group> wrote in message
news:bldttb$n4h$1@.reader08.wxs.nl...
> you'll need a log-backup (not a full) to allow the log to remove completed
> transactions..
> jobi
> "James" <capricorn@.nospam.com> wrote in message
> news:#BAaYK#hDHA.884@.TK2MSFTNGP10.phx.gbl...
> > I'm unfortunately not a DBA who is forced into being a DBA. I'm trying
to
> > figure out why the log file for my database is almost 300MB when the
data
> > file is only 31MB. Shrinking it does no good. I back the database up,
> but
> > that doesn't allow for making the log file any smaller either. Can
> someone
> > give me a brief explanation as to how to effectively manage my log file
> and
> > make it smaller? If it is historically keeping every single
modification
> > ever made, I don't need it to do that past the point where I'm backing
it
> > up.
> >
> > Thanks,
> >
> > James
> >
> >
>|||there's a kb that clears out the mistics of transactionlogshrinking.
hope this link stil works :
http://support.microsoft.com/support/kb/articles/q256/6/50.asp?id=256650&SD
jobi
"James" <capricorn@.nospam.com> wrote in message
news:#tdzkHDiDHA.1300@.TK2MSFTNGP10.phx.gbl...
> Ok, I went in EM and right clicked the database, selected All Tasks,
Backup
> Database, and selected Transaction Log and executed the backup. Then I
went
> into All Tasks, Shrink Database. I clicked the Files button and selected
> the Log File. It tells me that Space used is 30MB and its current size is
> 230MB. I used the "Compress pages and then truncate free space from the
> file" and this had very little impact. The file is still 230MB reporting
> only 30MB used. I then did the general "Shrink Database" selecting "Move
> pages to the beginning of the file before shrinking", but this again
yields
> Space Allocated: 252MB, space free: 190MB (75%). How can I get the
> Transaction Log to shrink to a size closer to the size of its contents?
> Thanks,
> James
>
> "jobi" <jobi@.reply2.group> wrote in message
> news:bldttb$n4h$1@.reader08.wxs.nl...
> > you'll need a log-backup (not a full) to allow the log to remove
completed
> > transactions..
> >
> > jobi
> > "James" <capricorn@.nospam.com> wrote in message
> > news:#BAaYK#hDHA.884@.TK2MSFTNGP10.phx.gbl...
> > > I'm unfortunately not a DBA who is forced into being a DBA. I'm
trying
> to
> > > figure out why the log file for my database is almost 300MB when the
> data
> > > file is only 31MB. Shrinking it does no good. I back the database
up,
> > but
> > > that doesn't allow for making the log file any smaller either. Can
> > someone
> > > give me a brief explanation as to how to effectively manage my log
file
> > and
> > > make it smaller? If it is historically keeping every single
> modification
> > > ever made, I don't need it to do that past the point where I'm backing
> it
> > > up.
> > >
> > > Thanks,
> > >
> > > James
> > >
> > >
> >
> >
>|||Hi James,
What is the version of SQL Server?
In SQL Server 7.0, there are some common reasons why a transaction log
might not shrink when you use the DBCC SHRINKFILE or DBCC SHRINKDATABASE
command. The SQL Server Books Online topics "DBCC SHRINKFILE" and "DBCC
SHRINKDATABASE" provide detailed information, but a brief summary follows.
For additional information regarding shrinking the SQL Server 7.0
transaction log, please refer to the following article below:
256650 INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/?id=256650
In SQL Server 2000, shrinking the log in is no longer a deferred operation.
A shrink operation attempts to shrink the file immediately. However, in
some circumstances it may be necessary to perform additional actions before
the log file is shrunk to the desired size.
For additional information regarding shrinking the Transaction Log in SQL
Server 2000, please refer to the following article below:
272318 INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC
http://support.microsoft.com/?id=272318
Please let me know if this solves your problem or if you would like further
assistance.
Thanks for using MSDN newsgroup.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.