Tuesday, December 27, 2011

How to Resolve MSSQL Server Error 1105

A SQL server database administrator can get error 1105, when temporary database becomes full. At this situation, database administrator is unable to do any operation on the database and get below error message:

Can't allocate space for object syslogs in database because the logsegment is full. If you ran out of space in syslogs, dump the transaction log. Otherwise use ALTER DATABASE or sp_extendsegment to increase the size of the segment.”
Cause: As stated above, this error message appears when the size of temporary database is full or running out of order.

Work Around for Error 1105: To solve this issue, firstly you should move the temporary database on to a different device so that is no longer exist on the master database and expand the size of temporary database. For this, follow the given below steps:

  • Stop & Restart the MSSQL server database, this will temporarily fix the problem.
  • Move the temporary database to a different device.
  • Again Stop & Restart MSSQL server database.

Hint: Generally, the size of temporary database of SQL server is around 20 percent of the size of your longest MSSQL server database. If the size of tempdb exceed by more the 20 percent then problem will be arises.

Detail Information: By default, temporary database of SQL server is placed on the master device. A database administrator can move the location of temporary database by two methods. One is “move temporary database by using SQL enterprise manager” and another “move temporary database by using ISQL”.

No comments:

Post a Comment