January 28, 2005 at 2:00 pm
I've just installed SQL Server 2000 Ent SP 3 with MS03-031 on a new W2K server SP4 box. I usually add the sql service account to the local admins group for the duration of the install and then remove it once the install is done. This allows the sql service account to be the owner of all the appropriate directories. This time when I removed the service account from the local admin group and restarted sql the sql service would not start. I looked in the error log and got the following error:
2005-01-28 13:35:22.33 spid5 Clearing tempdb database.
2005-01-28 13:35:22.36 spid5 Encountered an unexpected error while checking the sector size for file 'D:\MSSQL\MSSQL\Data\tempdb.mdf'. Check the SQL Server error log for more information.
2005-01-28 13:35:22.39 spid5 Error: 823, Severity: 24, State: 6
2005-01-28 13:35:22.39 spid5 I/O error 38(Reached the end of the file.) detected during read at offset 0000000000000000 in file 'D:\MSSQL\MSSQL\Data\tempdb.mdf'..
2005-01-28 13:35:22.41 spid5 WARNING: problem activating all tempdb files. See previous errors. Restart server with -f to correct the situation.
Putting the SQL service account back in the local admins account allowed the service to start up error free.
If I move the tempdb to another physical drive AND remove the SQL Service account from the local admins the server also starts normally.
At first it seemed clear that there was an NTFS rights problem. I checked and the sql service account was inheriting full control on the data directory. I thought that maybe there was a problem with the way it was inheriting it so I granted the rights explicitly but the problem persisted.
Can anyone shed some light on this at all?
Thanks in advance,
Noel
January 28, 2005 at 2:21 pm
Where do you have the model database ? The tempdb gets created from the model database. I recently ran into this error when i was trying to move the model database to a new drive after a new install.
January 28, 2005 at 2:38 pm
Thanks for the reply. All system databases including the model are in the default data directory (i.e. The same one as tempdb).
February 2, 2005 at 4:38 pm
perhaps the sql service account does not have log on as service rights in the local system policy?
hth
Cody Pack
MCSE Windows 2000
MCDBA SQL Server 2000
February 3, 2005 at 10:01 am
Thanks for your response. As it turns out the SQL Service account needed "List Folder Contents" to the root of the drive where the data files are located.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply