Log File Size

Hello there
I've got many solutions in the past from here and from other books about
shrinking database log file.
All the the solutions were good but they didn't solve the maim problem.
It seems that every w i need to detach the databse, delete the log file,
and reattacth it again.
I thought about doing it automaticly by agent at 12:00 pm, check that no one
is connected then.
But for this there were some problems:
1. If one user has forgot to exit the system before he left home this thing
won't work.
2. If after the detach one of the action failed the client won't have system
on the morning
Is there a way to drop every one from the system on some times?
and is there a way to do all the process in transaction so if it fails it
will return to the starting point?Roy
I'm not sure I understand you.
Does your database have FULL recovery mode?
Do you perform a regular BACKUP LOG file?
Are you concerned about a big size of the LOG file?

> 1. If one user has forgot to exit the system before he left home this
thing
> won't work.
> 2. If after the detach one of the action failed the client won't have
system
> on the morning
1. So ,it's okay unless he has not remained an open transaction , i mean he
hit the button and went home
2.What does it mean "one of the action"? An attaching will failed?
"Roy Goldhammer" <roygoldh@.hotmail.com> wrote in message
news:O0c3254MFHA.4028@.tk2msftngp13.phx.gbl...
> Hello there
> I've got many solutions in the past from here and from other books about
> shrinking database log file.
> All the the solutions were good but they didn't solve the maim problem.
> It seems that every w i need to detach the databse, delete the log
file,
> and reattacth it again.
> I thought about doing it automaticly by agent at 12:00 pm, check that no
one
> is connected then.
> But for this there were some problems:
> 1. If one user has forgot to exit the system before he left home this
thing
> won't work.
> 2. If after the detach one of the action failed the client won't have
system
> on the morning
> Is there a way to drop every one from the system on some times?
> and is there a way to do all the process in transaction so if it fails it
> will return to the starting point?
>
>|||RG> It seems that every w i need to detach the databse, delete the
RG> log file, and reattacth it again.
If you need to shrink Log File:
BACKUP LOG DatabaseName WITH TRUNCATE_ONLY
DBCC SHRINKFILE (Database_Log_File_Logical_Name, Desired_Size_In_MB)
Have a fun - your clients can be online all this time. But be warned - if
you are use some maintenance plan that involves log backup as part of
procedure, don't use first statement and add DBCC after transaction log
backup. Also, file shrinking can put some overhead on the system, so it's
best to plan it on non-prodactive time.
With best regards, Alexander Sinitsin. E-mail: al_sin[dog]ukr.net|||HI
WE ARE ALSO IN THE SAME ISSUE... WE ARE USING SQL SERVER2000, THE HDD
CAPACITY IS 20GB MY DATABASE TRANSACTION_LOG FILE ALONE OCCUPIED THE SPACE
NOW I AM RUNNING OUT SPACE. NOW WE NEED TO DELETE THE UNWANTED TRANSACTION
LOG FILES TO GET FREE SPACE.
IF ANY BODY EXPERIENCED THIS PLEASE LET ME KNOW THE PROCEDURE. AND HOW CAN
WE WEE THE DETAILS OF TRANSACTION LOG FILE BY USING SELECT STATEMENT OR
ELSE.
THANKS IN ADVANCE
REGARDS
S KALIYAN
"Roy Goldhammer" <roygoldh@.hotmail.com> wrote in message
news:O0c3254MFHA.4028@.tk2msftngp13.phx.gbl...
> Hello there
> I've got many solutions in the past from here and from other books about
> shrinking database log file.
> All the the solutions were good but they didn't solve the maim problem.
> It seems that every w i need to detach the databse, delete the log
file,
> and reattacth it again.
> I thought about doing it automaticly by agent at 12:00 pm, check that no
one
> is connected then.
> But for this there were some problems:
> 1. If one user has forgot to exit the system before he left home this
thing
> won't work.
> 2. If after the detach one of the action failed the client won't have
system
> on the morning
> Is there a way to drop every one from the system on some times?
> and is there a way to do all the process in transaction so if it fails it
> will return to the starting point?
>
>|||Backing up the log with truncate only, throws away the inactive part of the
log... You will not be able to fully recover until you do a complete
database backup... So either do a normal log backup, followed by the
shrink, OR backup with truncate, shrink and then whole database backup
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
"Roy Goldhammer" <roygoldh@.hotmail.com> wrote in message
news:O0c3254MFHA.4028@.tk2msftngp13.phx.gbl...
> Hello there
> I've got many solutions in the past from here and from other books about
> shrinking database log file.
> All the the solutions were good but they didn't solve the maim problem.
> It seems that every w i need to detach the databse, delete the log
> file,
> and reattacth it again.
> I thought about doing it automaticly by agent at 12:00 pm, check that no
> one
> is connected then.
> But for this there were some problems:
> 1. If one user has forgot to exit the system before he left home this
> thing
> won't work.
> 2. If after the detach one of the action failed the client won't have
> system
> on the morning
> Is there a way to drop every one from the system on some times?
> and is there a way to do all the process in transaction so if it fails it
> will return to the starting point?
>
>|||On Mon, 28 Mar 2005 18:16:22 +0530, S Kaliyan wrote:

>HI
>WE ARE ALSO IN THE SAME ISSUE... WE ARE USING SQL SERVER2000, THE HDD
>CAPACITY IS 20GB MY DATABASE TRANSACTION_LOG FILE ALONE OCCUPIED THE SPACE
>NOW I AM RUNNING OUT SPACE. NOW WE NEED TO DELETE THE UNWANTED TRANSACTION
>LOG FILES TO GET FREE SPACE.
>IF ANY BODY EXPERIENCED THIS PLEASE LET ME KNOW THE PROCEDURE. AND HOW CAN
>WE WEE THE DETAILS OF TRANSACTION LOG FILE BY USING SELECT STATEMENT OR
>ELSE.
>THANKS IN ADVANCE
>REGARDS
>S KALIYAN
Hi S Kayiyan,
First a bit of friendly advise: please don't use the CAPS LOCK key while
writing messages. First because messages in ALL CAPS are much harder to
read, but also because CAPS are often used to denote shouting in Usenet
messages (though they are also often used for SQL Server keywords in
this group). Therefor, ALL CAPS messages are considered impolite.
About your problem: you'll have to understand how recovery modes work.
Full Recovery means that every change ever made to your data will be
kept in either the log file, or a backup of the log file. You can use
this information to restore to a fixed point in time (often 5 seconds
before you accidentally dropped a table or before the harddrive holding
the database started smoking).
If you set your database in full recovery but don't schedule regular
backups of the log file, then it will grow indefinitely.
Simple recovery means that changes are only kept in log as long as they
might be needed for a rollback, i.e. only changes since the start of the
longest running transaction are retained. You don't need to backup the
log file; it won't grow beyond the normal working size.
The downside of simple recovery is that you can only restore to the last
database backup; changes made after that will be lost if you ever need
to restore.
My guess is that you are running in full recovery mode without being
aware of it. Either switching to simple recovery or starting to take log
backups at regular intervals will halt the growth of the log file.
As a one-time repair, you should also look into shrinking the log file
(check out the information on shrinking transaction logs in BOL). After
you've shrunk the log, allow it to grow back to it's normal working
size; don't repeat the shrinking. It will grow back anyway, and the
autogrow of the log file will reduce the performance of your SQL Server.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)