Re: massive log file
From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 02/25/05
- Next message: Wes: "SQL ( more than one Database)"
- Messages sorted by: [ date ] [ thread ]
Date: Sat, 26 Feb 2005 00:15:40 +0100
On Fri, 25 Feb 2005 14:01:35 -0800, grumy wrote:
>the transaction log on a 10Mb database has shot up to 1G!,
>how can I reduce this and prevent this happening in the
>future.................
Hi grumy,
It depends on whether you need point-in-time recovery or not.
If you need it:
* To reduce it, backup the log, then use DBCC SHRINKFILE to shrink it.
* To prevent it from happening again, schedule regular log backups.
If you don't need it:
* To reduce it, run "BACKUP LOG databasename WITH TRUNCATE_ONLY", then
use DBCC SHRINKFILE to shrink it.
* To prevent it from happening again, change the recovery model of your
database from "Full" or "Bulk-Logged" to "Simple".
If it still happens again, check for vary long-running transactions or
for transactions that insert, update and/or delete large numbers of
rows. Reduce the running time of the former and try to split the latter
in seperate transactions that each affect only a subset of the rows
(i.e. 100 transactions to delete 10,000 rows instead of one transaction
to delete 1,000,000 rows).
Best, Hugo
-- (Remove _NO_ and _SPAM_ to get my e-mail address)
- Next message: Wes: "SQL ( more than one Database)"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
