| M | My database log is full at once, what should I do?

wetboy

The log for database 'cnepa' is full. Please back up the database's transaction log to free up some log space.
Every two days
Do not need log files
Or let the SQL database be compressed every two hours

What should I do

jiu2shiai

Limit log increase:

According to the book, once the database is operated, the log may record N entries.

苹果小刀

Can also be emptied or dumped!

jccg1124

lz can back up log files once a day and then compress them. These can be made to run automatically.
If you don't really need a log file, you can change the database's failure recovery model to simple, and change the file growth mode to M instead of percentage.

4319231

Limit hard drive growth; expand hard drive capacity

660158

Don't log just like "jccg1124" to change the database's failure recovery model to simple

deadlyshadow

Back it up, you can clear the log

rcloud

if exists (select * from dbo.sysobjects where id = object_id (N '[dbo]. [p_backupdb]') and OBJECTPROPERTY (id, N'IsProcedure ') = 1)
drop procedure [dbo]. [p_backupdb]
GO
create proc p_backupdb
@dbname sysname = '', --The name of the database to be backed up, if not specified, the current database is backed up
@bkpath nvarchar (260) = '', --The directory where the backup files are stored.If not specified, the default SQL backup directory is used.
@bkfname nvarchar (260) = '', --Backup file name,\DBNAME\can be used for the database name,\DATE\for the date, and\TIME\for the time.
@bktype nvarchar (10) = 'DB',-backup type: 'DB' backup database, 'DF' differential backup, 'LOG' log backup
@appendfile bit = 1-Append / overwrite backup file
as
 declare @sql varchar (8000)
 if isnull (@dbname, '') = '' set @ dbname = db_name ()
 if isnull (@bkpath, '') = '' set @ bkpath = dbo.f_getdbpath (null)
 if isnull (@bkfname, '') = '' set @ bkfname = '\DBNAME\_\DATE\_\TIME\.BAK'
 set @ bkfname = replace (replace (replace (@ bkfname, '\DBNAME\', @ dbname)
  , '\DATE\', convert (varchar, getdate (), 112))
  , '\TIME\', replace (convert (varchar, getdate (), 108), ':', ''))
 set @ sql = 'backup' + case @bktype when 'LOG' then 'log' else 'database' end + @ dbname
  + 'to disk =' '' + @ bkpath + @ bkfname
  + '' 'with' + case @bktype when 'DF' then 'DIFFERENTIAL,' else '' end
  + case @appendfile when 1 then 'NOINIT' else 'INIT' end
 print @sql
 exec (@sql)
go


--Back up the current database
exec p_backupdb @ bkpath = 'c:\', @ bkfname = 'db_\DATE\_db.bak'
-Differential backup of the current database
exec p_backupdb @ bkpath = 'c:\', @ bkfname = 'db_\DATE\_df.bak', @ bktype = 'DF'
--Back up the current database log
exec p_backupdb @ bkpath = 'c:\', @ bkfname = 'db_\DATE\_log.bak', @ bktype = 'LOG'

rcloud

if exists (select * from dbo.sysobjects where id = object_id (N '[dbo]. [p_backupdb]') and OBJECTPROPERTY (id, N'IsProcedure ') = 1)
drop procedure [dbo]. [p_backupdb]
GO
create proc p_backupdb
@dbname sysname = '', --The name of the database to be backed up, if not specified, the current database is backed up
@bkpath nvarchar (260) = '', --The directory where the backup files are stored.If not specified, the default SQL backup directory is used.
@bkfname nvarchar (260) = '', --Backup file name,\DBNAME\can be used for the database name,\DATE\for the date, and\TIME\for the time.
@bktype nvarchar (10) = 'DB',-backup type: 'DB' backup database, 'DF' differential backup, 'LOG' log backup
@appendfile bit = 1-Append / overwrite backup file
as
 declare @sql varchar (8000)
 if isnull (@dbname, '') = '' set @ dbname = db_name ()
 if isnull (@bkpath, '') = '' set @ bkpath = dbo.f_getdbpath (null)
 if isnull (@bkfname, '') = '' set @ bkfname = '\DBNAME\_\DATE\_\TIME\.BAK'
 set @ bkfname = replace (replace (replace (@ bkfname, '\DBNAME\', @ dbname)
  , '\DATE\', convert (varchar, getdate (), 112))
  , '\TIME\', replace (convert (varchar, getdate (), 108), ':', ''))
 set @ sql = 'backup' + case @bktype when 'LOG' then 'log' else 'database' end + @ dbname
  + 'to disk =' '' + @ bkpath + @ bkfname
  + '' 'with' + case @bktype when 'DF' then 'DIFFERENTIAL,' else '' end
  + case @appendfile when 1 then 'NOINIT' else 'INIT' end
 print @sql
 exec (@sql)
go

lovetifa

Clear, it will be cleared at intervals.