sys.database_files and sys.master_files (rather than sys.sysfiles)

Whenever there are changes, people can be slow to embrace them. One I’ve come across recently is that looking in sysfiles is no longer the best way of getting information about your database files. sys.database_files will tell you a lot more about them, and give you much nicer ways of filtering them. So for example, if you want a list of the log files for your database, try:

select * from sys.database_files where type = 1

And sys.master_files will list them for the whole system. So it becomes really easy to look at the state of things from within T-SQL.

For more information on these, check out http://msdn2.microsoft.com/en-us/library/ms174397.aspx and http://msdn2.microsoft.com/en-us/library/ms186782.aspx.

Now go and make yourself a Vista Sidebar gadget (or use PowerGadgets to do it really easily) to show you the size of your log files (or whatever). Have it update every hour, sitting alongside those gadgets which tell you how nice the Adelaide weather is and show you pictures of the blue skies in Australia.

5 thoughts on “sys.database_files and sys.master_files (rather than sys.sysfiles)”

  1. After trying a few weather services, I’m currently using WeatherBug – who seem to be a bit more accurate than the others. eg. their numbers seem a lot closer to what the Bureau publish. Some of the others always seem to be 3-4 degrees out, which is annoying.

    Their Vista Sidebar gadget is pretty nice too.

    -dave

  2. Rob Farley,

    I really like what sys.master_files has to offer alas the information in it not correct. Most people online complained about how it has the wrong Size data. But another misleading information in it is the max_size column. It makes no differentiation between Auto-growth enabled and Auto-growth disabled. It shows “-1” for both situations!

    Test your scripts on at least 2 servers if you are going to build some monitoring job using these seemingly unreliable system views. What a loss!

Leave a Reply

Your email address will not be published. Required fields are marked *