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

7889638

Run it regularly
dbcc shrinkdatabase (dbname)
backup log dbname with truncate_only

wetboy

What I don't understand is that my current log file is still the same size
But the program can run without reporting too large a log file

lisongfox

1. Clear the log
DUMP TRANSACTION library name WITH NO_LOG
2. Truncate the transaction log:
BACKUP LOG database name WITH NO_LOG
3. Shrink the database file (if it is not compressed, the database file will not be reduced)
Enterprise Manager--right click on the database you want to compact--all tasks--shrink the database--shrink the file
-Select the log file-Select Shrink to XXM in the shrink mode.Here is a minimum number of M that is allowed to shrink.
-Select data file-select shrink to XXM in the shrink mode, here will give a minimum number of M to allow shrinking, enter this number directly, confirm it
Can also be done with SQL statements
-Shrink the database
CC DBCC SHRINKDATABASE (Customer Information)
-Shrink the specified data file, 1 is the file number, you can query through this statement: select * from sysfiles
DBCC SHRINKFILE (1)
4. In order to minimize the log file (if it is SQL 7.0, this step can only be performed in the query analyzer)
A. Detach the database:
Enterprise Manager--Server--Database--Right-click--Separate Database
B. Delete the LOG file in my computer
C. Additional database:
Enterprise Manager--Server--Database--Right-click--Add Database
This method will generate a new LOG with a size of only over 500K
用 Or use code:
The following example detaches pubs and then appends a file from pubs to the current server.
A. Separation
E X E C sp_detach_db @dbname = 'pubs'
 B. Delete log files
C. Reattach
X E X E C sp_attach_single_file_db @dbname = 'pubs',
Phy @physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'
5.In order to automatically shrink in the future, make the following settings:
Enterprise Manager--Server--Right-click Database--Properties--Options--Select "Auto Shrink"
--SQL statement setting method:
E X E C sp_dboption 'database name', 'autoshrink', 'TRUE'
6. If you don't want it to grow too large in the future
Enterprise Manager--Server--Right-click database--Properties--Transaction log
-Limit file growth to xM (x is the maximum data file size you allow)
--SQL statement setting method:
Alter database database name modify file (name = logical file name, maxsize = 20)
pay attention:
按 Please follow the steps, not the previous steps, please do not do the next steps
否则 Otherwise it may damage your database.
It is generally not recommended to do steps 4 and 6
Step 4 is not safe and may damage the database or lose data
Step 6 If the log reaches the upper limit, subsequent database processing will fail, and it can only be recovered after the log is cleared.

In addition, a simpler method is provided. I have tried it a lot and recommend it to everyone.
Easier way:
   1. Right build database properties window-failover model-set to simple
   2. Right build database all tasks-shrink the database
   3. Right build database properties window-failure recovery model-set to bulk logging

xuwei29088

up

frozenray

Landlord
You have to back up the transaction before you can compress it, and the backup is compressed once, until the log file is 1M (minimum).
At this point you can do the daily log file compression job.

tpjd2006

BACKUP LOG databasename WITH TRUNCATE_ONLY
Ranch
DBCC SHRINKDATABASE (databasename, 10)

Can be shrunk with the top

dukang520

learned

jccg1124

Is the lz problem solved?
Build a test library yourself, and then try all the functions in the Enterprise Manager, you will learn a lot. It will be easier to use sql statements after knowing more, this is my personal experience

xiaomo

mark

沙白二世

Learn + Collect