log file size

Hi,
Unfortunately, we are a small company and don't have a 'DBA' so I'm stuck wi
th any SQL server admin. My question concerns the size of the log file. I
have a small database where the .MDF file is around 8MB but the .LDF file
is around 180MB. I can onl
y figure that there is record of every transaction since day one. In our s
ituation, I am only concerned with transactions that persist for the duratio
n of one logical programatic update. We're never going to rollback to any t
ime in the past, etc.
Is there some way to tell sql server to limit the amount of info saved in th
e log file.
Thanks,
johnJohn
If you 'don't carry about your data' you can put the database in SIMPLE
recovery mode, otherwise perfom BACKUP LOG or/and DBCC SHRINKFILE
For more details please refer to the BOL
"JohnR" <anonymous@.discussions.microsoft.com> wrote in message
news:10A26988-292E-4A2D-A306-EAB6DFF4E6C7@.microsoft.com...
> Hi,
> Unfortunately, we are a small company and don't have a 'DBA' so I'm stuck
with any SQL server admin. My question concerns the size of the log file.
I have a small database where the .MDF file is around 8MB but the .LDF
file is around 180MB. I can only figure that there is record of every
transaction since day one. In our situation, I am only concerned with
transactions that persist for the duration of one logical programatic
update. We're never going to rollback to any time in the past, etc.
> Is there some way to tell sql server to limit the amount of info saved in
the log file.
> Thanks,
> john|||Hi,
Is there some way to tell sql server to limit the amount of info saved in
the log file.
No.
But you could restrict the log file size using Enterprise manager ,
Database, property, transaction log ....
To restrict the Transaction log file growth and if you do not require any
rollbacks, set the database with SIMPLE recovery model. In SIMPLE recovery
model the log file will be cleraed as soon as commit is done.
How to set the recvery to simple
--
ALTER database <dbname> set recovery SIMPLE
How to reduce the current transaction log file size
----
Execute the below script:-
backup log <dbname with with truncate_only
go
dbcc shrinkfile('logical_ldf_name',truncateon
ly)
How to check the log size of a db and usage
---
dbcc SQLPERF(LOGSPACE)
Thanks
Hari
MCDBA
"JohnR" <anonymous@.discussions.microsoft.com> wrote in message
news:10A26988-292E-4A2D-A306-EAB6DFF4E6C7@.microsoft.com...
> Hi,
> Unfortunately, we are a small company and don't have a 'DBA' so I'm stuck
with any SQL server admin. My question concerns the size of the log file.
I have a small database where the .MDF file is around 8MB but the .LDF
file is around 180MB. I can only figure that there is record of every
transaction since day one. In our situation, I am only concerned with
transactions that persist for the duration of one logical programatic
update. We're never going to rollback to any time in the past, etc.
> Is there some way to tell sql server to limit the amount of info saved in
the log file.
> Thanks,
> john|||Thanks, that did the trick.|||And , if you are NOT going to put the database in simple recovery mode, you
will have to back up the log on a regular basis or the log will become full,
and no users will be able to insert, update or delete
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"JohnR" <anonymous@.discussions.microsoft.com> wrote in message
news:10A26988-292E-4A2D-A306-EAB6DFF4E6C7@.microsoft.com...
> Hi,
> Unfortunately, we are a small company and don't have a 'DBA' so I'm stuck
with any SQL server admin. My question concerns the size of the log file.
I have a small database where the .MDF file is around 8MB but the .LDF
file is around 180MB. I can only figure that there is record of every
transaction since day one. In our situation, I am only concerned with
transactions that persist for the duration of one logical programatic
update. We're never going to rollback to any time in the past, etc.
> Is there some way to tell sql server to limit the amount of info saved in
the log file.
> Thanks,
> john