Clear SQL server logs

1. Open the query analyzer, enter the command dump transaction database name with no_log2, Open Enterprise Manager -- Right-click the database you want to compress -- all tasks -- contract database -- contract file -- Select log file -- select shrink :, here we will provide a minimum number of M that can be reduced. Enter this number directly and click OK. 3. Right-click the database and choose Properties> Options> fault recovery> model> simple model. 4. Choose "Enterprise Manager"> "server"> "right-click" Database ">" properties ">" options ">" auto contract"

 

 

==================================

1: logs overflow caused by small transactions. The system can start normally.

Solution:

Expand the Database Log space:
Alter database name on device name = Quantity (MB)

Sp_logdevice Database Name, device name

Clear logs
Dump transaction database name with no_log (no_truncate)

2: log overflow caused by large transactions. The system cannot be started normally or the database cannot be recovered for a long time.

Solution:

Forcibly clear logs.
If you cannot recover the database or have a recent backup, you can forcibly clear the logs. The consequences of using this method may completely damage the database. Perform the following steps:

I. Start SQL Server in-V mode (logs are not detected)

Ⅱ modify the database status to-32768 (blocking)

Update sysdatabases set status =-32768 where name = Database Name

Iii. Authorize sybase_ts_role (sybase_ts_role is the special administrator permission of SQL Server. This role is not required in daily database management)

Sp_role "grant", "sybase_ts_role", SA

Set role "sybase_ts_role"

IV clear logs

DBCC rebuild_log (Database Name, 1, 1)

After completing the preceding steps, restart SQL Server. If the database can be started normally, the database will be restored. If the database cannot be started, you can only create a new database.

========================================================== ======================================

Compressed logs

1: truncate transaction logs:
Backup log database name with no_log

2: Clear logs
Dump transaction database name with no_log

Again:
Enterprise Manager -- Right-click the database you want to compress -- all tasks -- contract database -- contract file -- Select log file -- select to contract to xxm in the contract mode, here we will provide a minimum number of M that can be reduced. Enter this number directly and click OK.

3: delete log
1: Detach Database Enterprise Manager-> server-> database-> right-click-> detach Database
2: delete log files
3: attach the Database Enterprise Manager-> server-> database-> right-click-> attach Database
This method generates a new log with a size of more than 500 kb.
Then set the database to automatically contract
Or useCode:
The following example separates pubs and attaches a file in pubs to the current server.

Exec sp_detach_db @ dbname = 'pubs'
Exec sp_attach_single_file_db @ dbname = 'pubs ',
@ Physname = 'C: \ Program Files \ Microsoft SQL Server \ MSSQL \ data \ pubs. MDF'

4: If you want to prevent it from increasing 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 settings:
Alter database name Modify file (name = logical file name, maxsize = 20)

5. Set to auto contract

Enterprise Manager -- server -- Right-click Database -- Property -- option -- select" auto contract "