October 29, 2011 at 6:21 pm
Not sure why but for tempDB querying sys.sysfiles and checking tempdb database Properties - Files
return different file numbers- 4 vs 3? Funny enough but I can see all 4 data files on disk 🙂
May be sql service should be restarted to show correct data files in Properties? Thanks,
October 31, 2011 at 1:49 am
Just to be sure - did you right click on tempdb in the databases list and then select the refresh from the menu? If you didn't I would give that a try to see if after that I see all the files. In any case I'll go with the select statement from sys.database_files
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 31, 2011 at 5:10 am
Yuri55 (10/29/2011)
Not sure why but for tempDB querying sys.sysfiles and checking tempdb database Properties - Filesreturn different file numbers- 4 vs 3? Funny enough but I can see all 4 data files on disk 🙂
May be sql service should be restarted to show correct data files in Properties? Thanks,
can you post details of the following query executed against your SQL Server instance
select name, type_desc, physical_name, size, max_size, growth, is_percent_growth
from sys.master_files where database_id = db_id('tempdb')
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 31, 2011 at 5:31 am
Adi- I certainly tryed Refresh- same 3 files (mdf + 2 ndf)
Perry- I run your query- same result as from sys.sysfiles I mentioned before-
same 3 files and nothing unusual
(sorry, cannot provide details- it's in PCI zone).
Thanaks guys for help, Yuri
October 31, 2011 at 5:45 am
Perry, sorry (early morning in Canada :-))- just to clarify-
sys.sysfiles returns 4 data files fo tempdb,
sys.master_files returns 3 files (same as from Property)
October 31, 2011 at 6:34 am
not a good practice having more than a single LDF file for tempdb.
With your number 4. are you seeing 2mdf and 2ldf files at file system level ?
You could check which file at os level isn't being used be trying to rename ( filemanager )
You'll not be able to rename the ones in use if the instance is up and running.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 31, 2011 at 6:41 am
Yuri55 (10/31/2011)
(sorry, cannot provide details- it's in PCI zone).
wait a minute, so you cant tell me the default logical names of TempDev and TempLog and the default physical filenames of
?:\blah\blah\tempdb.mdf
?:\blah\blah\templog.ldf
with their paths and sizes 😀
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 31, 2011 at 10:10 am
Sorry for delay:
1) output from sys.master_files:
tempdevROWSF:\MSSQL\DATA\tempdb.MDF3717120-1128000
templogLOGF:\MSSQL\DATA\templog.LDF262175-1128000
tempdev1ROWSF:\MSSQL\DATA\tempdev1.ndf3717120-1128000
tempdev2ROWSF:\MSSQL\DATA\tempdev2.ndf3717048-1128000
2)output from sys.sysfiles:
tempdevF:\MSSQL\DATA\tempdb.MDF3717120-112800
templogF:\MSSQL\DATA\templog.LDF262175-112800
tempdev1F:\MSSQL\DATA\tempdev1.ndf3717120-112800
tempdev2F:\MSSQL\DATA\tempdev2.ndf3717048-112800
tempdev3F:\MSSQL\DATA\tempdev3.ndf3717120-112800
Thanks
October 31, 2011 at 3:51 pm
Just to share my experience (make simple test):
1) originally- tempdb has 4 data files. One data file was removed
-sys.sysfiles and sys.database_files return 4 files (old number)
-sys.master_files returns 3 files (new number- same as from SSMS- DB- Properties-Files)
2) sql service was restarted- all views return correct number- 3 data files
Resume- after tempdb file deletion we can rely only on sys.master_files view (and DB Properties)
till sql service will be restarted
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply