May 9, 2008 at 5:09 am
I broke into cold sweat last night, when, towards the end of a production outage I attempted to move a tempdb data file to a different LUN:
ALTER DATABASE tempdb MODIFY FILE ...
The (logical) move went successfully, and I shut down the SQL startup service, in order to move the physical file to its new location. I then attempted to start the instance back up and was greeted by a message that in effect said that the service was not coming back up! 🙁
I asked myself "What would MacGyver have done in this situation?" and that (and some prayers) helped calm me down. After some google search I was able to find a great link that helped me move the tempdb file back to its original location and successfully start the instance:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2226699&SiteID=1
If anyone needs any more details on how I did this, pls let me know.
The question remains though: What caused this to happen???
Both my data and log file for tempdb are now on the same LUN, and I will need to move them apart at some point down the road. I've only managed to buy myself some more time.
I got the following errors in the Event Viewer logs when attempting the failed restart:
SYSTEM LOG
Event Type:Error
Event Source:Service Control Manager
Event Category:None
Event ID:7024
Date:5/8/2008
Time:10:41:22 PM
User:N/A
Computer:serverName
Description:
The SQL Server (INSNAME) service terminated with service-specific error 1814 (0x716).
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
APPLICATION LOG
Event Type:Error
Event Source:MSSQL$INSNAME
Event Category:(2)
Event ID:17204
Date:5/8/2008
Time:10:41:22 PM
User:N/A
Computer:serverName
Description:
FCB::Open failed: Could not open file G:\SQL\MSSQL$INSNAME\Data for file number 1. OS error: 5(Access is denied.).
Event Type:Error
Event Source:MSSQL$INSNAME
Event Category:(2)
Event ID:17207
Date:5/8/2008
Time:10:41:22 PM
User:N/A
Computer:serverName
Description:
FCB::Open: Operating system error 5(Access is denied.) occurred while creating or opening file 'G:\SQL\MSSQL$INSNAME\Data'. Diagnose and correct the operating system error, and retry the operation.
Event Type:Error
Event Source:MSSQL$INSNAME
Event Category:(2)
Event ID:5123
Date:5/8/2008
Time:10:41:22 PM
User:N/A
Computer:serverName
Description:
CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'G:\SQL\MSSQL$INSNAME\Data'.
These access-denied errors make no sense. I'm logged in as the domain account under which the SQL services are running, and that account is admin on the machine!
Has anyone else seen this? Anyone know what's going on?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 9, 2008 at 8:05 am
I had to move tempdb files last week. I don't remember the exact error but I couldn't get SQL to start. Finally I figured out that the right service didn't have permission on the data or log files. I modified the file permissions and SQL started no problem.
May 9, 2008 at 8:47 am
Thanks for the reply.
In my case, the account I am using is sysadmin on the machine and instance, so I cannot fathom it not having adequate permissions locally.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 9, 2008 at 8:56 am
check the NTFS permissions on the drive and the files. should be full control for the sql service account. i think you also have to change a few things inside the registry if you move tempdb files from the default.
what we always do is add a new file on a separate drive and the default files are shrunk and restricted to only a few MB in size
May 9, 2008 at 9:02 am
Microsoft best practices dictate that tempdb data files be made of equal size, on no autogrowth, in a separate physical device from other files. Also there should be (roughly) as many data files as CPUs on the system.
Not sizing your data files equally and with the same characteristics, you are not taking advantage of some of the built-in optimizations that come with that.
Thanks though, I will check for the NTFS permissions.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 9, 2008 at 1:45 pm
Marios,
I think your mistake was physically moving the tempdb files manually. Just ensure the new directory structure exists (actually if they don't alter database will fail), run alter database and stop start sql. SQL rebuilds tempdb in new location, you just delete the old files.
---------------------------------------------------------------------
May 9, 2008 at 2:04 pm
george sibbald (5/9/2008)
Marios,I think your mistake was physically moving the tempdb files manually. Just ensure the new directory structure exists (actually if they don't alter database will fail), run alter database and stop start sql. SQL rebuilds tempdb in new location, you just delete the old files.
Thanks, that must be it! I must have done this at least 10 times before, but hadn't done it in a while...
I should have consulted my documentation before starting, but was too over-confident I knew how to do it... 🙂
Thanks again!
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply