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

March 7, 2007

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.

This Post Has 5 Comments

  1. David Gardiner

    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. Amar

    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!

  3. robfarley

    Yeah – it’s not perfect.

  4. owie

    is there any way we could get the accurate sizes?

  5. robfarley

    Accurate size – Do you mean size on disk, or amount of file used?

    Rob

Leave a Reply

LobsterPot Blogs

Blog posts by Rob Farley and other LobsterPot Solutions team members.