SQL MSDB growing - 'QueryStats'

Question

SQL MSDB growing - 'QueryStats'

Lanky Doodle 241

Hi

I have a very large QueryStats table and not sure the right approach to clear it down.

It's approaching 100GB of "totalSpace'! 'pages' are high as well.

Thanks

Seeya Xi-MSFT 16,756 Reputation points

2022-07-07T06:45:20.48+00:00

Hi @Lanky Doodle ,

As Erland said, there is not a table named QueryStats in msdb system table. The msdb database is used by SQL Server Agent for scheduling alerts and jobs and by other features such as SQL Server Management Studio, Service Broker and Database Mail. We need to know what this table is for so that we can figure out a better way.

Best regards,
Seeya

1 answer

Your answer

Seeya Xi-MSFT 16,756 Reputation points

2022-07-07T06:45:20.48+00:00

Hi @Lanky Doodle ,

As Erland said, there is not a table named QueryStats in msdb system table. The msdb database is used by SQL Server Agent for scheduling alerts and jobs and by other features such as SQL Server Management Studio, Service Broker and Database Mail. We need to know what this table is for so that we can figure out a better way.

Best regards,
Seeya

Answer 1

Erland Sommarskog 134.7K MVP Volunteer Moderator

That does not sound like standard table for msdb, so this must be something you are loading yourself. Or you have some third-party tool that stores data into this table. So the first thing may be to find out how data gets into this table.

Lanky Doodle 241 Reputation points

2022-07-07T12:24:01.32+00:00

Thanks for the replies.

For clarity (apologies for not mentioning) I am querying sys.indexes, following this https://www.dbaservices.com.au/how-to-fix-very-large-msdb-database/
Lanky Doodle 241 Reputation points

2022-07-07T12:24:40.473+00:00

Everything else from that query is showing OK.
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2022-07-07T12:39:22.017+00:00

Yeah, we don't question that you have accurate information about the table size. But we can't tell you how to deal with that table, because we don't know what you have it there in the first place.

Of course you can use say "DROP TABLE QueryStats" and wait to see if something starts failing.

(From the name, I would guess that you have a job that saves the contexts of sys.dm_exec_query_stats and related DMVs according to some schedule.)

Share via

SQL MSDB growing - 'QueryStats'

1 answer

Your answer