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.

Published Wed, Mar 7 2007 10:25 by Rob Farley
Filed under: , ,

Comments

Tuesday, March 06, 2007 8:41 PM by David Gardiner

# re: sys.database_files and sys.master_files (rather than sys.sysfiles)

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

Tuesday, November 11, 2008 6:40 PM by Amar

# re: sys.database_files and sys.master_files (rather than sys.sysfiles)

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!

Tuesday, November 11, 2008 9:10 PM by Rob Farley

# re: sys.database_files and sys.master_files (rather than sys.sysfiles)

Yeah - it's not perfect.

Friday, December 12, 2008 4:57 AM by owie

# re: sys.database_files and sys.master_files (rather than sys.sysfiles)

is there any way we could get the accurate sizes?

Friday, December 12, 2008 8:28 PM by Rob Farley

# re: sys.database_files and sys.master_files (rather than sys.sysfiles)

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

Rob

Leave a Comment

(required) 
(required) 
(optional)
(required)