Table monitoring & Table informations in SQL-Server databases

If you are a database admin or developer you always should know and take care about, where tables are stored, how much space they consume and how data grows in the tables. One of the golden rules in SQL-Server database development is not to use the PRIMARY file group as the default location for user-tables in your projects.

Better create a new custom file group, and set it as the default file group. From my opinion, this is the point, where a good database design starts: “With the implementation of additional and customized file groups”. In this article you will find a fast example how to do that. I had to mention file group separation first, before we can move on, because this was actually my motivation to write that query and this blog post.

If you never think about it, or don’t even have an idea where the tables in the database are located, how big they are and how much rows your tables contains. You better take care of it…NOW!

Therefor i wrote a quick management query, especially aligned for that case.  If  you run that query in your database, you will get an overview of the current situation in the database. The result provides you the necessary informational output as you see in the screenshot below.

common table informations
Example result of the user table information query


Now you can decide about the next steps what to do (moving tables to a another file group) or talk to your developers about the necessary changes to stay online and keep your database in a healthy and in a performant state.

Management query to gather table information

Creating a additional file group

Yeah… that’s all for today. Hope you like this post  and help’s you managing your databases in the daily business. Stay tuned – Comments are welcome!

Leave a Reply

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

Proudly powered by WordPress | Theme: Baskerville 2 by Anders Noren.

Up ↑

%d bloggers like this: