Deleting or Clearing

Is there a way to delete a database log file without detaching the database?
I don't care about loosing the ability to roll back, etc.
I tried "restricting the growth" setting, but that just makes the database
unusable when it hits the limit. I'd like to be able to restrict a log file
from growing above a specific amount.No, SQL Server need the log file. First you need to consider recovery model.
If you have full, you
need to do regular log backups to empty (not shrink) the log. If you don't w
ant to do regular log
backup (which has a bunch of advantages), then have simple recovery model. O
nce you have sorted that
out, see http://www.karaszi.com/SQLServer/info_dont_shrink.asp for info on h
ow to do the actual
shrink of the log files.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Scott" <sbailey@.mileslumber.com> wrote in message news:uIJc2$ddGHA.3632@.TK2MSFTNGP05.phx.g
bl...
> Is there a way to delete a database log file without detaching the databas
e? I don't care about
> loosing the ability to roll back, etc.
> I tried "restricting the growth" setting, but that just makes the database
unusable when it hits
> the limit. I'd like to be able to restrict a log file from growing above a
specific amount.
>|||The key point here is to use "dbcc shrinkdatabase".
The first solution keeps the backup of data and transaction logs.
The database log file CANNOT be removed, but the size will be reduced.
Depending on your database size, this may be time consuming due to the
backup task.
- Do a full backup on the database.
- Shrink the database using "dbcc shrinkdatabase (dbname)".
Here's another quick trick that I will use when there is not enough space on
the server for doing a full/log backup, while adding a HDD is not possible.
(But, the data in the log database will be removed, and cannot be
recovered!)
-- ** warning ** (copied from BOL)
-- TRUNCATE_ONLY removes the inactive part of the log without making a
backup copy of it and truncates the log.
-- This option frees space. Specifying a backup device is unnecessary
because the log backup is not saved.
-- The changes recorded in the log are not recoverable.
-- For recovery purposes, immediately execute BACKUP DATABASE.
backup log dbname with truncate_only
-- You can then shrink the database.
dbcc shrinkdatabase (dbname)
Martin C K Poon
Senior Analyst Programmer
====================================
"Scott" <sbailey@.mileslumber.com> bl
news:uIJc2$ddGHA.3632@.TK2MSFTNGP05.phx.gbl g...
> Is there a way to delete a database log file without detaching the
database?
> I don't care about loosing the ability to roll back, etc.
> I tried "restricting the growth" setting, but that just makes the database
> unusable when it hits the limit. I'd like to be able to restrict a log
file
> from growing above a specific amount.
>|||so, to your knowledge, this method will reduce a log the maximum amount that
is possible while keeping the database attached?
"Martin C K Poon" < martinpoon__at__graduate__dot__hku__dot_
_hk> wrote in
message news:eUoUCSedGHA.1656@.TK2MSFTNGP02.phx.gbl...
> The key point here is to use "dbcc shrinkdatabase".
> The first solution keeps the backup of data and transaction logs.
> The database log file CANNOT be removed, but the size will be reduced.
> Depending on your database size, this may be time consuming due to the
> backup task.
> - Do a full backup on the database.
> - Shrink the database using "dbcc shrinkdatabase (dbname)".
> Here's another quick trick that I will use when there is not enough space
> on
> the server for doing a full/log backup, while adding a HDD is not
> possible.
> (But, the data in the log database will be removed, and cannot be
> recovered!)
> -- ** warning ** (copied from BOL)
> -- TRUNCATE_ONLY removes the inactive part of the log without making a
> backup copy of it and truncates the log.
> -- This option frees space. Specifying a backup device is unnecessary
> because the log backup is not saved.
> -- The changes recorded in the log are not recoverable.
> -- For recovery purposes, immediately execute BACKUP DATABASE.
> backup log dbname with truncate_only
> -- You can then shrink the database.
> dbcc shrinkdatabase (dbname)
> --
> Martin C K Poon
> Senior Analyst Programmer
> ====================================
> "Scott" <sbailey@.mileslumber.com> bl
> news:uIJc2$ddGHA.3632@.TK2MSFTNGP05.phx.gbl g...
> database?
> file
>|||Both backup (full/log) and "dbcc shrinkdatabase" can be done with the
database online (and without detaching the database).
There is also a database option 'autoshrink' that could be used for
shrinking a database periodically and automcatically by SQL Server. By
default, the 'autoshrink' option is set to OFF in SS2000 (except SS2000
Personal Edition). You will need to implement an appropriate backup
strategy, anyways.
-- To set the autoshrink database option. (When true, the database files are
candidates for automatic periodic shrinking.)
sp_dboption 'dbname', 'autoshrink', 'TRUE/FALSE'
References
- Shrinking the transaction log
http://msdn.microsoft.com/library/d...r />
_1uzr.asp
- Truncating the transaction log
http://msdn.microsoft.com/library/d...r />
_7vaf.asp
Martin C K Poon
Senior Analyst Programmer
====================================
"scott" <sbailey@.mileslumber.com> bl
news:%23E1ZEXgdGHA.1260@.TK2MSFTNGP05.phx.gbl g...
> so, to your knowledge, this method will reduce a log the maximum amount
that
> is possible while keeping the database attached?
>
> "Martin C K Poon" < martinpoon__at__graduate__dot__hku__dot_
_hk> wrote in
> message news:eUoUCSedGHA.1656@.TK2MSFTNGP02.phx.gbl...
space
>sql