How to Tackle SQL Server Error 1105 – Severity 17 State 2?
SQL Server database can encounter many errors and issues with it. Any type of issue with the server is followed by errors and every error belongs to a certain internal issue with the server or database. This blog discusses about SQL Server error 1105, which is associated with the disk space allocation. The “SQL Server Error 1105” is prompted when your transaction needs more space than it is available in transaction log file.
Table of Content
The major issue with this error is when this error occurs, all the current transactions processing are stopped. SQL Server halts all the transaction unless the log is cleared or more space is added to this file for storing more transactions.
Details of SQL Error 1105
Error 1105 Severity Level 17 Message Text Could not allocate space for object '%.*ls' in database '%.*ls' because the '%.*ls' filegroup is full.
This error is related to the transaction log file when the specified filegroup run out of space and there is no space to add the transaction. In order to avoid this error, DBAs take care to balance the transaction log file’s size with respect to the size of database. This size ratio is regulated while creating log file that should be 1/4th-1/2th of the size of database.
Generally, DSSs and data warehouse applications have transaction log files, which are much smaller. Administrators also implement database options like “truncate log on checkpoint” or log-dumping tasks in order to control data in log files.
Note: Corruption in the SQL data files is the main cause of unnecessary storage filled out.
Symptoms of SQL Server Error 1105
When the temporary database (tempdb) or transaction log file is full, you are restricted to perform any query or any other operation in SQL Server. Error 1105 is displayed as below;
How to Resolve This Situation?
In order to gain the space, users can free-up the disk space on the system drive, which comprise of the files in full filegroup, letting files to grow in the group. Users can gain space using data file with its specific database.
Steps to Free Disk Space & Solve Error Code 1105
- Disk space can be freed on the local drive or on another drive. For this, move the data files of filegroup, which have insufficient amount of free disk space to different disk drive.
- Detach the target database using sp_detach_db.
- Then attach the database using sp_attach_db, pointing to the moved files.
By Means of a Data File
Another workaround, which can be done in such situation, is by adding data file to specified database using ADD File clause of ALTER DATABASE statement. Alternatively, the data file can be enlarged using the MODIFY FILE clause by specifying the SIZE and MAXSIZE syntax.
Another Experts recommended Solution to Avoid the Technicalities
As we know that the SQL server database involves plenty of extremely technical operations. However, not all users are that technical. Therefore, they face a plethora of challenges in order to get rid of SQL server error 1105 & several other error codes as well. The key factor of the tool is to repair the data files corruption without running any query.
Experts’ addressed this issue & started taking use of dedicated software that is reliable as well as far better than the traditional methods to get the desired results. One such tool is the SQL Server Recovery Manager which is the first preference of IT experts working on SQL. Now being an expert, it’s quite hectic to manage the entire SQL recovery, migration, and backup alone. Thus, along with beginners, experts also prefer this tool for managing their SQL server for all types errors like error code 1105, error 9002 transactions full, etc.
Download the Trial Version of the Tool for Free
Conclusion
The SQL Server error 1105 can be avoided by managing the size and duration of transactions made with respect to Log file. In case if transactions take up too much memory space, they can be managed by dumping the logs or clearing them in order to continue the flow of transactions. In order to avoid such errors in future, administrators must take care in advance so that the transaction log file memory is managed.