Tuesday, May 17, 2016

"Operating system error 112(There is not enough space on the disk.) encountered." Error When Starting SQL Server Service After Changing The Initial Size or Location of [tempdb] Data File.

You've changed the location or the initial size of the data file of the [tempdb], then you restarted SQL Server services, but that failed with errors in the Event Viewer like the following:

The operating system returned error 38(Reached the end of the file.) to SQL Server during a read at offset 0000000000000000 in file '[some path]\tempDB.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

and

[some path]\tempDB.mdf: Operating system error 112(There is not enough space on the disk.) encountered.


Solution:

  1. Go to SQL Server Configuration Manager and change the SQL Server services account to [NT AUTHORITY\NetworkService].
  2. Make sure you are login into windows using a machine administrator.
  3. Run CMD as administrator.
  4. Execute the following command:
     net start mssqlserver /f
    this will start SQL Server services with the minimal requirement and configuration, and in single user mode that allows just one administrator to login.
  5. Immediately after that run the following command:
    osql -E
    you will connect to SQL Server using the current windows user (which should be machine admin), through CMD, and using command line.
  6. Now change the initial size to a size you think it is available on where the tempdb data file is located. For example, the following will change the initial size of the tempdb data file to 1GB:
    alter database tempdb modify file ( name=tempdev ,size=1000MB);
    go
  7. If the above point finished successfully, go back to SQL Server Configuration Manager and change back the account that runs SQL Server service to the one you want, then try to start the service again.

No comments:

Post a Comment