February 11, 2020 at 2:24 am
Thanks for the assistance in advanced.... i am trying to create my temdb with 4 data files and one log file. with the size being 100GB for each file
when i run
use tempdb
select * from sys.master_files where database_id=2 this returns 19+ files that i can NOT seem to get ride of... i have rebooted many times and still those files seem to show up.
Any suggestions are more than welcome and i have ran the below code and still not much luck the files are created but when i look in the properties of tempdb they are not there...
ALTER DATABASE [tempdb]
MODIFY FILE (NAME= N'Tempdb_LOG', NEWNAME= N'Tempdb_Log', FILENAME= N'H:\MSSQL_TempLogs\Tempdb_log.ldf', SIZE= 50000MB, FILEGROWTH= 10000MB);
ALTER DATABASE [tempdb]
MODIFY FILE (NAME= N'Tempdb1', NEWNAME= N'Tempdb_Data1', FILENAME= N'H:\MSSQL_TempData\Tempdb_data1.mdf', SIZE= 100000MB, FILEGROWTH= 10000MB);
ALTER DATABASE [tempdb]
ADD FILE (NAME= N'TempdbData2', FILENAME= N'H:\MSSQL_TempData\Tempdb_data2.ndf', SIZE= 100000MB, FILEGROWTH= 10000MB);
ALTER DATABASE [tempdb]
ADD FILE (NAME= N'TempdbData3', FILENAME= N'H:\MSSQL_TempData\Tempdb_data3.ndf', SIZE= 100000MB, FILEGROWTH= 10000MB);
ALTER DATABASE [tempdb]
ADD FILE (NAME= N'TempdbData4', FILENAME= N'H:\MSSQL_TempData\Tempdb_data4.ndf', SIZE= 100000MB, FILEGROWTH= 10000MB);
Thanks
DHeath
DHeath
February 11, 2020 at 9:51 am
Don't modify the mdf file of tempdb, I have seen it do some very crazy things when the MDF file has been modified.
Revert the mdf file back to the original settings Logicalname of "tempdev", filename of "<whateverpath>\tempdb.mdf"
Then you may need to start in single master only mode as the other files will want to be created, so you would then want to go and remove the files so that it thinks it only has the mdf and ldf.
Then add in the 3 NDF's only
Restart in normal mode and all should be fine.
This has happened to me a number of times on servers I have inherited.
February 11, 2020 at 1:53 pm
Antony.Green, thanks for the reply...
I have never had to "revert" backward are there any special commands or anything to the sort? As you mention you said single master mode i am assuming you mean single user mode. When you say go and remove the files from where they are NOT in the physical location you see in the attachment so will going into single user mode clear that up?
I appreciate your assistance just need a bit more clarity.
DHeath
DHeath
February 11, 2020 at 2:30 pm
reverting backwards is the same as reverting forwards, just back to the original setting.
The <VALUE> field needs replacing with whatever sys.database_files, sys.master_files thinks the name of the tempdb mdf file current is called.
ALTER DATABASE [tempdb]
MODIFY FILE (NAME= N'<VALUE>', NEWNAME= N'tempdev', FILENAME= N'H:\MSSQL_TempData\tempdb.mdf', SIZE= 100000MB, FILEGROWTH= 10000MB);
You could try single user (/m) switch, but that will want to create all the files tempdb thinks it has, so it may crash as it needs space for all the files to create. The /f switch for minimal mode may be the better option so it starts with minimal config, I always call this master only mode, someone probably called it that before and it stuck.
You then need to issue a number of ALTER DATABASE tempdb REMOVE FILE commands so that the tempdb is back to normal 1 MDF, 1 LDF. Then add in the 3 required NDF's. Restart without any switches.
Once you fixed the MDF file though you may/maynot need to do all the switching etc as it may revert to its last good config, again as I say I have seen it do very strange things if the MDF has been modified from default.
February 11, 2020 at 2:49 pm
Mr. Green, thanks again and much appreciated
altho i must ask...how does the sys.master_files clean up itself...is that something SQL will do or something i need to do after i revert? I was hoping i can manually get rid of that but it doesn't seem like that will be the case. Thanks Again
DHeath
DHeath
February 11, 2020 at 3:16 pm
It should sort itself out when it you do all the needed "remove file" "add file" "alter file" commands. You cant modify the system tables anymore (not that you should of back in 2000/SQL7) so you need to do it based on the commands issued.
February 11, 2020 at 4:13 pm
Thank you SIR...... much appreciated
DHeath
DHeath
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply