none
Contradicting Results from different system tables in SQL server RRS feed

  • Question

  • Hi All

    I am getting different result for no. of tempdb files. Can some one explain why and how to get correct output

    1. Select  *FROM sysfiles
    2. Select    *FROMsys.database_files
    3. Select    *fromsys.master_fileswhere database_id = 2
    4. select       *fromsys.sysaltfileswheredbid=orderby fileid
    5. check database properties from GUI
    6. Physically checked tempdb files on disk

    ------------OUTPUT--------------

    • 1 Set of ouput from : sysfiles and sys.database_files – Showing 3 files (2 data and 1 log file)
    • 2nd Set of output from : sys.master_files and sys.sysaltfiles – Showing 7 files (5 data and 1 log file)
    • 3<sup style="font-family:'Courier New';">rd</sup> set of output from database properties (2 data and 1 log file)
    • 4<sup style="font-family:'Courier New';">th</sup> Set of output physically checked files 5 files (3 data file and 2 log file)

    I:\MSSQL\LOGS\templog.ldf

    I:\MSSQL\DATA\tempdb.mdf

    I:\MSSQL\DATA\tempdb2.ndf

    J:\MSSQL\DATA\tempdev1.ndf

    J:\MSSQL\LOGS\templog2.ldf

    Screen shot:



    Thanks Saurabh Sinha Blog Twitter LinkedIn Gallery Facebook Please click the Mark as answer button and vote as helpful if this reply solves your problem


    Tuesday, January 12, 2016 6:37 AM

Answers

  • Saurabh,

    I am not sure what could be reason but dont use Old views to get system information. I am referring to

    sysfiles and sysaltfiles. Just use sys.database_files or sys.master_files

    Dont query them. Now sys.database_files is database specific so did you made sure you selected tempdb when querying it. Or what is output of below

    use tempdb
    go
    select * from sys.database_files
    go
    select * from sys.master_files where database_id=2


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Wiki Articles

    MVP


    Tuesday, January 12, 2016 7:33 AM
    Moderator
  • Saurabh,

    The view sys.master_files is something new and is updated asynchronously. It is not yodates immediately. The sys.master_files tell you about any tempdb data file which was there on  your  system or it shows the number of tempdb files with which your server will start. While sys.database_files shows currently used tempdb files. Its quite possible that not all tempdb data files came online.

    Like you said sysfiles and sys_database_files are same only diff is former is deprecated. While sysaltfiles and sys.master_files is same and difference being former is deprecated.

    Read MS comment in this connect item. This is not directly related to what you have asked .

    Now can you read the errorlog look for any error meesage did any of the files did not come online.

    About GUI I am not sure


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Wiki Articles

    MVP

    Tuesday, January 12, 2016 10:34 AM
    Moderator
  • sys.master_files is how you have defined that SQL Server will create tempdb *at startup*. Since you started your SQL Server, somebody might have executed SQL commands to remove or add tempdb files.

    tempdb.sys.database_files is what SQL Server is *currently using*.

    When you re-start your SQL Server, SQL Server will re-create tempdb based on sys.master_files. If there is a file on disk which isn't specified in sys.master_files, then that file will be left as is on startup; SQL Server will not try to tidy up after itself.

    The GUI (SSMS) picked the values from sys.master_files prior to 2012. As of 2012, the GUI picks the values from tempdb.sys.database_files.


    Tibor Karaszi, SQL Server MVP | <a href="http://www.karaszi.com/sqlserver/default.asp"> web</a> | <a href="http://sqlblog.com/blogs/tibor_karaszi">blog</a>

    Tuesday, January 12, 2016 2:41 PM
    Moderator

All replies

  • Saurabh,

    I am not sure what could be reason but dont use Old views to get system information. I am referring to

    sysfiles and sysaltfiles. Just use sys.database_files or sys.master_files

    Dont query them. Now sys.database_files is database specific so did you made sure you selected tempdb when querying it. Or what is output of below

    use tempdb
    go
    select * from sys.database_files
    go
    select * from sys.master_files where database_id=2


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Wiki Articles

    MVP


    Tuesday, January 12, 2016 7:33 AM
    Moderator
  • Hi Shanky

    1. I am using tempdb while querying

    2. Output of old table (sysfiles) and new (sys.database_files) is same

    3. Output of old table (sysaltfiles) and new (sys.master_files) is same

    And output of point 2 and 3 are different where as output of sys.database_files or sys.master_files should be same, but here its not.

    4. GUI is also showing different output then any of query

    5. Finally when i check physically , i can see only 5 files which again different form all above. 

    I:\MSSQL\LOGS\templog.ldf

    I:\MSSQL\DATA\tempdb.mdf

    I:\MSSQL\DATA\tempdb2.ndf

    J:\MSSQL\DATA\tempdev1.ndf

    J:\MSSQL\LOGS\templog2.ldf

    Note: Its stand alone machine with 1 Sql server installed and all files are in use (i cant rename any of these)



    Thanks Saurabh Sinha Blog Twitter LinkedIn Gallery Facebook

    Please click the Mark as answer button and vote as helpful if this reply solves your problem


    Tuesday, January 12, 2016 8:05 AM
  • Saurabh,

    The view sys.master_files is something new and is updated asynchronously. It is not yodates immediately. The sys.master_files tell you about any tempdb data file which was there on  your  system or it shows the number of tempdb files with which your server will start. While sys.database_files shows currently used tempdb files. Its quite possible that not all tempdb data files came online.

    Like you said sysfiles and sys_database_files are same only diff is former is deprecated. While sysaltfiles and sys.master_files is same and difference being former is deprecated.

    Read MS comment in this connect item. This is not directly related to what you have asked .

    Now can you read the errorlog look for any error meesage did any of the files did not come online.

    About GUI I am not sure


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Wiki Articles

    MVP

    Tuesday, January 12, 2016 10:34 AM
    Moderator
  • sys.master_files is how you have defined that SQL Server will create tempdb *at startup*. Since you started your SQL Server, somebody might have executed SQL commands to remove or add tempdb files.

    tempdb.sys.database_files is what SQL Server is *currently using*.

    When you re-start your SQL Server, SQL Server will re-create tempdb based on sys.master_files. If there is a file on disk which isn't specified in sys.master_files, then that file will be left as is on startup; SQL Server will not try to tidy up after itself.

    The GUI (SSMS) picked the values from sys.master_files prior to 2012. As of 2012, the GUI picks the values from tempdb.sys.database_files.


    Tibor Karaszi, SQL Server MVP | <a href="http://www.karaszi.com/sqlserver/default.asp"> web</a> | <a href="http://sqlblog.com/blogs/tibor_karaszi">blog</a>

    Tuesday, January 12, 2016 2:41 PM
    Moderator
  • Thanks Shanky / Tibork

    This doesn't clarify completely but i can live with this explanation :-)

    I didn't find much in logs. Will get back to you if i get any new logs.

    I summarize below, what you guyz shared with me for later reference. 

    1. The view sys.master_files is something new and is updated asynchronously. It doesn't updates immediately. 
    2. When you re-start your SQL Server, SQL Server will re-create tempdb based on sys.master_files. 
    3. The sys.master_files tell you about any tempdb data file which was there on  your  system (the number of tempdb files) with which your server have started. 
    4. While sys.database_files shows currently used tempdb files. Its quite possible that not all tempdb data files came online.
    5. You can read the errorlog look for any error meesage did any of the files did not come online.
    6. After you started sql server somebody might have executed SQL commands to remove or add tempdb files.


    Thanks Saurabh Sinha

    Blog Twitter LinkedIn Gallery Facebook

    Please click the Mark as answer button and vote as helpful if this reply solves your problem


    Thursday, January 14, 2016 5:17 AM
  • But if all the tempdb datafiles come online sys.mastre_files must be same as sys.database_files. If not than there seems some contradiction.

    I also have feeling that its better o rely on sys.database_files because sys.master_files being newly introduced seems erratic to me. Although I dont have proof for it from my side


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Wiki Articles

    MVP

    Thursday, January 14, 2016 7:15 AM
    Moderator
  • I have doubts regarding the asynchronicity mentioned in point 1 above. I'd like to see a repro or something else that elaborate on this. To the best of my knowledge, there is no asynchronicity involved for sys.master_file. Nor is it new. I'll elaborate more on this at the end of this post.

    I agree with point 2.

    As for point 3, it depends on how you look at it. Say that you tell SQL Server (sys.master_files) to create 5 tempdb files. One of them fails (a disk which doesn't exist, for instance). Sys.master_files will still have the "instruction" to create 5 tempdb files, and will attempt to do so at next startup. But the information will not correspond with reality, even immediately after startup, since one of these files failed.

    I agree with point 4, 5 and 6.

    A background on sys.master_files: Back in the days, SQL Server 7.0, we didn't have this formation. All we had was sysfiles (the precursor to sys.database_files). Imagine that you now lose the mdf file for a database and want to produce a backup of the transaction log for that database. You can't. Why? SQL Server has no idea where the ldf file is (or the files, since we can have more than one ldf file). So in 2000, MS decided to also reflect the information in sysfiles into a table in the master database, called master..sysaltfiles. The purpose of this was to give you ability to produce a log backup even if you lost the mdf file for your database - the information of where the databases' ldf file is can be found in the master database. In SQL Server 2005, MS re-architectured the system tables and hid them from us. We were given views for backward compatibility with the old system tables, but these will go away in the future. And we were given the "proper" way to access this information: "catalog views". The catalog views for sysfiles is sys.database_files and the catalog view for master..sysaltfiles is sys.master_files.


    Tibor Karaszi, SQL Server MVP | <a href="http://www.karaszi.com/sqlserver/default.asp"> web</a> | <a href="http://sqlblog.com/blogs/tibor_karaszi">blog</a>

    Thursday, January 14, 2016 9:16 AM
    Moderator
  • I have doubts regarding the asynchronicity mentioned in point 1 above. I'd like to see a repro or something else that elaborate on this. To the best of my knowledge, there is no asynchronicity involved for sys.master_file. Nor is it new. I'll elaborate more on this at the end of this post.

    I agree with point 2.

    As for point 3, it depends on how you look at it. Say that you tell SQL Server (sys.master_files) to create 5 tempdb files. One of them fails (a disk which doesn't exist, for instance). Sys.master_files will still have the "instruction" to create 5 tempdb files, and will attempt to do so at next startup. But the information will not correspond with reality, even immediately after startup, since one of these files failed.

    I agree with point 4, 5 and 6.

    A background on sys.master_files: Back in the days, SQL Server 7.0, we didn't have this formation. All we had was sysfiles (the precursor to sys.database_files). Imagine that you now lose the mdf file for a database and want to produce a backup of the transaction log for that database. You can't. Why? SQL Server has no idea where the ldf file is (or the files, since we can have more than one ldf file). So in 2000, MS decided to also reflect the information in sysfiles into a table in the master database, called master..sysaltfiles. The purpose of this was to give you ability to produce a log backup even if you lost the mdf file for your database - the information of where the databases' ldf file is can be found in the master database. In SQL Server 2005, MS re-architectured the system tables and hid them from us. We were given views for backward compatibility with the old system tables, but these will go away in the future. And we were given the "proper" way to access this information: "catalog views". The catalog views for sysfiles is sys.database_files and the catalog view for master..sysaltfiles is sys.master_files.


    Tibor Karaszi, SQL Server MVP | <a href="http://www.karaszi.com/sqlserver/default.asp"> web</a> | <a href="http://sqlblog.com/blogs/tibor_karaszi">blog</a>

    While the explanation is Awesome Tibor....I belive sys.master_files is not synchronoulsy updated. And if you read the comment given by Microsoft Engineer in Connect item I posted in my Second reply it says that. Well I still not tried doing this but I guess we can add some files to tempdb and see how sys.database_files and sys.master_files behave

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Wiki Articles

    MVP

    Thursday, January 14, 2016 9:31 AM
    Moderator
  • Thanks Shanky :-)

    Regarding whether sys.master files is updated asynchronously or synchronously: Personally, I don't feel we should get too in deep on this. I have doubts that it matters much for this scenario. The thread you posted is interesting. The Peter person from MS started by saying:

    "The updating of sys.master_files occurs in an asynchronous manner from the actual grow/shrink of the file."

    However in a more recent comment, the same Peter (I assume) say:

    "The fact that one is out of sync is a little odd because both system tables are updated in the same transactions usually. "

    There's a contradiction, and also vagueness ("usually") in here. Also, there's a question regarding this potential asynchronicity is only when grow occurs for a non-tempdb database, or whether it also applied for various operations for tempdb. 

    Bottomline: show us the source code, and we will be able to say. :-)


    Tibor Karaszi, SQL Server MVP | <a href="http://www.karaszi.com/sqlserver/default.asp"> web</a> | <a href="http://sqlblog.com/blogs/tibor_karaszi">blog</a>

    Thursday, January 14, 2016 9:52 AM
    Moderator