November 30, 2005 at 3:13 pm
I am running SQL 2000 SP4.
I was changing the security for the SQL service account on a test machine and found myself with a tempdb with files in a location that I did not know were there ( I did not install this server or instance of SQL).
The point I'm at right now is that the following two statement return a different set of files.
SP_HELP_DB 'tempdb'
select * from sysaltfiles where dbid = 2
It appears that SQL tries to use the files returned from the sysaltfiles on startup to determine how to build tempdb. Some of these files I added via EM myself today, but after a SQL stop and restart they do not show up EM anymore but are still listed in sysaltfiles.
There are two old log files that point to the root of C: also listed in sysaltfiles and I do not want these files anymore. SQL will not have permissions to use C: and it is failing on startup to create these files. I tried to delete these files using following command but get an error:
alter database tempdb remove file 'tempdbLogUpg'
EM doesn't list the files in DB properties.
Why does EM not match sysaltfiles and what is the proper way to change and list tempdb file structure? I can't seem to delete the log files and am contemplating manually deleting them. Plus I'm thouroghly confused about why EM doesn't show me proper files and locations for TEMPDB.
December 1, 2005 at 3:52 am
Jane,
Sounds like your server is in a bit of a confused state. Try the following: http://support.microsoft.com/default.aspx?scid=kb;en-us;224071#XSLTH3207121122120121120120 then see if sp_helpfile, sp_helpdb 'tempdb' and sysaltfiles return the same information.
hope this helps.
ll
December 1, 2005 at 8:27 am
I looked at the reference and I couldn't get that to work either. I had an idea that maybe the problem was privileges still - that because the file path wasn't available to SQL account it was having trouble changing FROM it.
What I figured was happening is that EM and the SPs quit listing information after they got to the first record in sysaltfiles that SQL did not have security to validate. If the Alter Database commands use the same list that was being displayed by SP_HelpDB then that is why they kept saying the logical file I was trying to delete didn't exist. I put the SQL service account back in local admins, restarted SQL, and sp_help_db and sp_help_files both started returning the right information. I was then able to use alter database to remove the log files I did not want. I removed the service account from local administrators again and restarted. Everything looks right now.
Thanks for your help. It got me thinking down a different path and helped me resolve the issue.
December 1, 2005 at 9:29 am
glad you got it figured out.
may i ask why you are simply removing the SQL service account from local admins? also are you using a local or domain windows account?
please note that stripping any rights from the service account will revert it back to the minimum permissions applied when first assigned as the SQL service account, possibly making network locations, registry keys and local file system locations unaccesible by the service account, potentially causing problems if your databases are located in non default locations. remember that the supported MS method for changing the SQL service account is within Enterprise Manager (EM), EM then sets the appropriate permissions on that account to run SQL, this is the limited credentials that the SQL server service account can have. this may be of use in the future http://support.microsoft.com/default.aspx?scid=kb;en-us;283811&sd=tech
ll
December 1, 2005 at 9:48 am
For security reasons I am removing non neccessary permissions from the service accounts.
The article you point to is a very good reference to find all the information about rights. It seems there are several articles that list rights required by the service accounts and the local accounts (such as network service) none of which seem to list everything but this one comes as close to any I've seen.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply