Sqlerver database/transaction log full solution

1. Simple method

1. Right-click database & rarr; attributes & rarr; options & rarr; fault recovery model & rarr; set to simple & rarr; OK;
2. Right-click database & rarr; all tasks & rarr; contract database & rarr; OK;
3. Right-click database & rarr; attribute & rarr; option & rarr; fault recovery model & rarr; set to large-capacity log record & rarr; OK.

2. Complex methods

1. Clear logs
Dump transaction database name WITH NO_LOG
2. Truncate transaction logs
Backup log database name WITH NO_LOG
3. Compress database files (if not compressed, the database files will not be reduced)
Enterprise Manager -- right-click the database you want to compress -- all tasks -- contract database -- contract file
-- Select Log File -- select to shrink to XXM in the contraction mode. Here, a minimum number of MB allowed to be shrunk is displayed. Enter this number directly and click OK.
-- Select data file -- select to shrink to XXM in the contraction mode. Here, a minimum number of MB allowed to be shrunk is displayed. Enter this number directly and click OK.
You can also use SQL statements to complete
-- Shrink database
Dbcc shrinkdatabase (customer profile)
-- Shrink the specified data file,

1 is the file number. You can use this statement to query: select * from sysfiles dbcc shrinkfile (1)

Solution to full transaction logs

If a large transaction is committed to fill up the entire logstore, no matter how you truncate the log, there are three solutions:

1. Restart the database (the most stupid but most effective), but there will certainly be REDO and UNDO recovery
It takes a long time to recover.
2. Add space for database logs, but sufficient space is required.
3. Find the ID of the SESSION for executing the large transaction and KILL it, but it will also be rolled back, and no
You can KILL it.
 
**************************************** *******
For the 1st methods, can I directly change the status in sysdatabases to-32768, then enter the database dump tran dbname with truncate_only, and then restart to set the status to 0. In this way, you do not have to wait for redo and undo time. This method may sometimes cause database data inconsistency.
 
**************************************** *******
The Adaptive Server can be started without any hardware faults, but the application system cannot operate. In many cases, the application system database logs are too slow, generally, you can use dump tran dbname with truncate_only to solve the problem, but sometimes the log is full, as shown in the errorlog file segment below:

The code is as follows:

Copy code

Server Database 'sybsystemprocs 'is now online. Server Recovering database 'testdb '. Server Redo pass: 6000 records done (11%); 47493 records left. Server Redo pass: 48000 records done (89%); 5493 records left. Server Redo pass of recovery has processed 787 committed and 44 aborted transactions. Server Error: 1105, Severity: 17, State: 3 Server Can't allocate space for object 'syslogs' in database 'testdb' because 'logsegment' segment is full/has no free extents. if you ran out of space in syslogs, dump the transaction log. otherwise, use alter database or sp_extendsegment to increase size of the segment. Server Error: 3475, Severity: 21, State: 7 Server There is no space available in SYSLOGS for process 1 to log a record for which space has been reserved. This process will retry at intervals of one minute. The internal error number is-4. Kernel shutdownproc: shutting down SQL Server! Server SQL Server shutdown by request.

The business system database cannot be started normally. To solve this problem, follow these steps:

Step 1: Enable allow updates to system tables so that users with the system administrator role can change the system tables and create and modify the stored procedures of the system tables, the system table includes all the tables provided by Sybase in the master database, all the tables starting with "sys" in the user database, and the tables whose ID value is less than or equal to 100 in the sysobjects table. Incorrect changes to the system table will cause database damage and data loss. When modifying the system table, you must use begin transaction to protect the database from errors that may damage the database, immediately disable allow updates to system tables after modification.

The code is as follows:	Copy code
1>; sp_configure "allow update", 1

Step 2: each database in the Adaptive Server has a corresponding row in sysdatabases. After The Adaptive Server is installed, the master database, model database, sybsystemprocs, and tempdb databases will have corresponding entries in sysdatabases, if the audit function has been installed, the sybsecurity database also has corresponding entries. Modify the sysdatabases table, change the testdb status to-32768, and restart the Adaptive Server after the Adaptive Server is disabled.

The code is as follows:	Copy code
1>; update sysdatabases set status =-32768 where name = "testdb" 1>; shutdown

Step 3: because the transaction log is full, you cannot use the conventional method to dump the transaction log. If the dump transaction or dump transaction with truncate_only command is used, and the command fails due to insufficient log space, you can use the special dump transaction option with no_log, which can truncate transaction logs without recording the transaction event. All dump tran with no_log will be reported in The Adaptive Server error log. These messages include the user ID that executes this command, messages indicating success or failure, no_log is the only dump option for generating error log messages. However, this option (including with truncate_only) does not provide any way to recover the transactions committed since the last routine dump.

The code is as follows:	Copy code
1>; use testdb 1>; dump tran testdb with no_log

Step 4: modify the sysdatabases table, restore testdb to 0, and disable allow updates to system tables.

The code is as follows:	Copy code
1>; use master 1>; update sysdatabases set status = 0 where name = "testdb" 1>; sp_configure "allow update", 0