If SQL Server is insufficient because the disk is available, it cannot complete the recovery of the database, then SQL Server 2000 returns an error 1105 and sets STATUS columns in sysdatabases. You can see that there should be 1105 error message in the application log of SQL Server Error Log and OS: SQL Server transaction logs may be filled, which will block the database operation, including Update, Delete, Insert, and Checkpoint. The transaction log fills the 1105 error: Can't Allocate Space for Object Syslogs in Database DBNAME BECAUSE The LogSegment is Full. If You Ran Out of Space in syslogs, dump the transactions log. OtherWise Use alter Database or sp_extendsegment to increase the size of the segment. This phenomenon may appear in any database, including Master and Tempdb. Some unforeseen factors may consume log space. For example: a large transaction, especially like bulk data update, insert or delete. A uncommitted transaction. Check the bandwidth required for checkpoint handler cuts. The result of the threshold above the various conditions of the above conditions is exceeded. The marking transaction for publishing is not read by log read programs. The following is the steps of repairing steps and shrink log: 1. Run the following command at the command prompt: SQLSERVER -F -M Remarks: -m switch Single user mode starts SQL Server. In single user mode, only one connection can be successfully established. Note that any other clients or services may use that connection before you set up the connection via the SQL Server query analyzer. 2. Reset the status of the database. SP_RESETSTATUS 'DATABASE_NAME' The following is the result set: Database'Database_name'Status RESET!
WARNING: You Must Reboot SQL Server PRIOR To Accessing this Database!
3. Add a data file or log file to the database with ALTER DATABASE: Use master
Go
Create Database DB_Name on
(
Name = dbname_dat1,
Filename = 'd: /msql/data/dbname_dat1.ndf',
Size = 1000MB,
Filegrowth = 50MB
)
GO - Change the database to add a 2GB size new data file
Alter Database DB_NAME
Add file
(
Name = dbname_dat2,
Filename = 'f: /msql/data/dbname_dat2.ndf',
Size = 2000MB,
Filegrowth = 50MB
)
Go
- Change the database to add a 1GB size new log file
Alter Database DB_NAME
Add log file
(Name = db_name_log2,
FileName = 'f: /msql/data/db_name_log2.ldf',
Size = 1000MB,
FileGrowth = 20MB),
Go
4. Stop and restart SQL Server: SQL Server should be able to complete the database recovery with the additional space provided by the new data file or log file. 5. Release the disk space and re-run the recovery operation, and constrict the log in the steps below. SP_RESETSTATUS Turn off the quo flag of the database, but it is unable to maintain other options for the database. To fundarately solve such problems, you can configure SQLServer 2000: a below. If you don't need to restore to the specified point in time, you can configure the database's recovery mode as simple, so Update, Delete, SELECT is not Logs will be recorded, and the log will not increase.
Use master
Go ALTER DATABASE DB_NAME SET Recovery Simpleb. If your recovery mode is all, you must configure the log field contraction:
Use master
. GO sp_dboption 'databasename', '.. Trunc log on chkpt', true sp_dboption 'databasename', 'autoshrink', truec shrink by daily backup log: BACKUP DATABASE DATABASE_NAME TO BACKUP_DEVICES BACKUP LOG DATABASE_NAME TO LOG_DEVICES OR BACKUP LOG DATABASE_NAME with TRUNCATE_ONLY
** Check the capacity of the log: DBCC SQLPERF (Logspace) This time is not contracted!
d. After the backup database is completed every day, restart the MS SQLServer Service. Use database_name Go DBCC ShrinkFile (2, TruncateOn "
** Check the capacity of the log: DBCC SQLPERF (Logspace) This time the log has shrunk!
e Manual rapid contractions log:. / * run below script, you will shrink you database log filesimmediately, in my experience, you need to run the script for 3 or4 minutes before stopping it manually * / use databasenamedbcc shrinkfile (2, notruncate) dbcc Shrinkfile (2, truncateonly Create Table T1 (Char1 Char (4000)) godeclare @i tentSelect @ i = 0WHile (1 = 1) Begin While (@i <100) Begin Insert Into T1 Values ('a') Select @i = @ I 1 endtruncate table t1backup log youdatabaseename with truncate_onlyendgo
Note You can use sp_resetstatus only under your main support provider or if there is a problem with troubleshooting recommendations. Otherwise, the database may be damaged. Since the process modifies the system table, the system administrator must enable system table updates before running sp_resetstatus. To enable updates, use the following procedure: Use Master Go SP_Configure 'Allow Updates', 1 Go Reconfigure with Override Go process, disable system table updates: sp_configure 'allow updates', 0 Go Reconfigure with override Go Only system administrators Can perform sp_resetstatus. After performing this process, close the SQL Server immediately. Please refer to:
Http://support.microsoft.com/default.aspx?scid=kb;zh-cn; 317375
Http://support.microsoft.com/default.aspx?scid=kb;zh-cn;307775
