The SQL Server does not recognize new tempdb log file location

dbaSQL
dbaSQL used Ask the Experts™
on
SQL Server v2014.  I have attempted to relocate the tempdb files from C to another drive.  The ALTER statements performed fine, and the restart completed without error as well.  But now that I am trying to adjust the size of the file on the new drive, the system tells me there is not enough space -- because it still thinks they are on the C drive.

This is the error I receive when I attempt to ALTER the size of the tempdb log file:

     Msg 5149, Level 16, State 3, Line 41
     MODIFY FILE encountered operating system error 112(There is not enough space on the disk.) while attempting to expand the physical file
      'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\templog.ldf'.

Yet at the very same time, sp_helpfile says the files are:
   sp_helpfile results for tempdb


I have relocated the tempdb  countless times before, but I have never run into this problem.  Are there any Experts who can assist?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
have you restarted the sql services yet?

Author

Commented:
Yes.  Twice.  My steps for relocating the tempdb are below -- have used them countless times without error.  Typically only one restart is necessary, but my adjustment of the log file failed due to insufficient space on the C drive.  I checked the file location and confirmed they are  on T, but still restarted again -- without impact.

-- 1. relocate the files
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, ....................
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, ...............
-- 2. restart service
-- 3.  adjust existing files
-- 4.  add new files

Author

Commented:
Even in the gui, the tempdb files are on the new drive that I ALTERed them to, yet my attempts to alter them are failing due to space on the C drive.

Anybody?
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
In the ALTER did you specify the FILENAME parameter?

ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'd:\new\path\to\data\file\...' )
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'd:\new\path\to\log\file\...' )

Did you verify that the path is valid?

For tempdb you shouldn't have to copy the existing files, but you will need to delete the old files (on the c: drive) yourself.  Go ahead and delete them: if you can't, then they are currently in use by SQL and you haven't changed the tempdb file path(s).

Author

Commented:
Hi Scott.  Yes, the path is valid -- this is my 1st statement verbatim:

ALTER DATABASE tempdb
MODIFY FILE (
      NAME = tempdev,
      FILENAME = 'T:\MSSQL\TempDB\Data\tempdb.mdf',
      size = 1MB
      );
ALTER DATABASE tempdb
MODIFY FILE (
      NAME = templog,
      FILENAME = 'T:\MSSQL\TempDB\Log\templog.ldf',
      size = 1MB
      );

That completed without error.  Then I restarted the service and attempted these two statements:


ALTER DATABASE tempdb
MODIFY FILE (
      NAME = tempdev,
      size = 8192MB,
      filegrowth = 256MB
      );
ALTER DATABASE tempdb
MODIFY FILE (
      NAME = templog,
      size = 8192MB,
      filegrowth = 128MB
      );


The 1st one completed successfully and the log failed with this error:
Msg 5149, Level 16, State 3, Line 41
     MODIFY FILE encountered operating system error 112(There is not enough space on the disk.) while attempting to expand the physical file
      'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\templog.ldf'.



I did it twice now -- same exact error -- but again, sp_helpfile and a simple select from sys.master_files shows that the files ARE on the T drive.  When I go into the directory structure in file explorer, they are NOT there.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
I wouldn't include a size when you want to change the file location.  They're effectively contradictory options and the size is smaller than the current file, which could also cause an issue.

ALTER DATABASE tempdb
MODIFY FILE (
      NAME = tempdev,
      FILENAME = 'T:\MSSQL\TempDB\Data\tempdb.mdf'
      );
ALTER DATABASE tempdb
MODIFY FILE (
      NAME = templog,
      FILENAME = 'T:\MSSQL\TempDB\Log\templog.ldf'
      );

Author

Commented:
Ok.  Getting closer.  I've checked sys.databases and create_date is NOT today.  It is 2019-07-16 15:23:33.010.  

I've gone into the configuration mgr twice now -- the first time I chose to restart.  The 2nd time I stopped then started -- both actions took a little longer than normal, but neither errored out.  I even checked the service before doing so -- as I always do, just to be sure I'm on the right instance.  In the SQL Server Configuration Manager, SQL Server Services, double click SQL Server (MSSQLSERVER) to open up the properties box, and in the Service tab it lists exactly the service name and hostname that I am looking for.

So I'm at a bit of a loss.  I have taken all of the actions to restart... but no restart.
Does the user under which the SQL Server service is running have full access to the folder 'T:\MSSQL\TempDB\ and its subfolders?
Top Expert 2016

Commented:
stop sql server services
copy/move the files to the new location (could be a permissions issue does the service account have full control on the new location?)
start sql server services

https://docs.microsoft.com/en-us/sql/relational-databases/databases/move-system-databases?view=sql-server-2017

Author

Commented:
Again, I went into the configuration manager and restarted the service twice - yet the sys.database.create_date for tempdb does not reflect this restart.  Clearly this is why the Tempdb files are still on the C  drive, but I don't know where the problem is.  I have checked all server properties and logs and I do not see any failure with those service restarts.  There is no named instance either.  

The service is running under NT Service\MSSQLSERVER, so I believe it is possible that the new drive may require an account w/explicit privileges, but IF that is the case, I would expect access errors when I ran the first ALTER on the database file locations.  

I think the problem is not about where the tempdb files are anymore, but why I cannot seem to restart my service.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
CHECKPOINT all dbs, wait a few secs, then force down SQL by issuing a:

SHUTDOWN

command.  That should shut it down.  If not, you can really force it with:

SHUTDOWN WITH NOWAIT

Then restart it by whatever method you prefer.
If the create_date does not reflect the restart date then it means you did not restart the right service...

Could you please tell what returns
SELECT @@servername, @@servicename

or whether it returns values you've used to restart the SQL Server service?
To check the folder rights you may create a new database in the same location.

Author

Commented:
I have to wait on the next attempt to restart (or shutdown) -- will send an update as soon as I can.

Author

Commented:
It was not a permission problem with the SQL service account.  It was a problem with the actual drives -- newly allocated but not permissioned properly.  There were no access denial or permission related errors anywhere in SQL or Windows logs, but the server team said they took ownership of the directories and applied system permissions as the new owner --- afterward we restarted the service and the files were in T as expected.

All is well.  Thank you each for looking this way.
Commented:
Just a problem on the back end with the directory permissions.
And are you sure we did not propose to check the permissions?
Top Expert 2016

Commented:

Author

Commented:
>>Permissions and access were mentioned in my comment
>>And are you sure we did not propose to check the permissions?
Yes, David and pcelba, but the permissions were not for the sql service account.  It was specific to the drive configuration.  Regardless, I have adjusted the recognition for each of you that helped me.  I am very grateful for the input.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial