How to Check Database File and Log Sizes Using SQL.

I thought a bit as to what would be a nice simple first post for my blog when, as luck would have it, I was inspired by my job.

We had noticed a database growing a bit faster than it should have so I decided I'd like to know a couple things:

• What was the growth rate of the .MDF file per day,
• What was the growth rate of the .LDF file per day,
• What was the growth rate of each table per day.

So to answer those questions we can turn to sys.sysfiles and the columns it provides. The ones that interested me at the point in time were the 'name' and 'size' columns (you can look at the link and see what other columns might be helpful for your particular situation).



Now for the simple code snippet:

SELECT name AS DB_Name,
              filename AS DB_FileName,
              (size*8)/1024 AS Size_In_MB,
              GETDATE() AS Time
FROM My_DB.sys.sysfiles
WHERE name = 'My_DB_Name'

Seeing as how this will only provide you with the state of the DB at runtime you can create a job to run this, say every hour or so, and store the info in a table somewhere to be pulled and analysed later on. Also in order to get the same data for the logfile, just change the 'where' clause to the logfile's name. Also if you were wondering about the '(size*8)/1024' math, it's because the size is stored in 8-KB pages, so had to convert to flat KB, then I converted this to MB.

Hey, didn't I say I also wanted to know the stats of the tables in the DB? Yes, I did (click here).

I didn't want to post the code here as it wasn't mine, but I can say it worked out pretty well for my needs. The same deal applies to the above: set up a job to run this every however often and plug it into a table so you can scrutinize later.

P.S. Please remember I am by no means an expert, so any and all comments are absolutely welcomed. If there is a better way to do this kind of stuff, do share in the comments below. :)