Impact of disabling DBQL & Listing all the DB/Users under Query/Access logg...

Database
l_k
Enthusiast

Impact of disabling DBQL & Listing all the DB/Users under Query/Access logg...

Hi All,

I need to clean up the table DBQLOGTBL in DBC database.Currently it's size is almost 850 GB of permanent space.
If i do the clean up of DBQLOGTBL directly ,then DBC is getting blocked and some of our Business ETLs getting failed..

So,i would like to disable the Query/Access logging temporarily before doing this and then will enable the logging..

I need to know ,

1) Is there any option to find out what are all the DBs/Users are Capturing query/Access logg?
2) If i disable the logging, any impact will be there apart from capturing information in DBC tables?
3) How to disable logging for all DBs/Users?

Thanks in advance..

Lavakumar
1 REPLY
Enthusiast

Re: Impact of disabling DBQL & Listing all the DB/Users under Query/Access logg...

1) Check the system view: DBC.DBQLRules
2) If you disable logging you will lose what ever data would have been logged during the maintenance of the DBQL tables.
3) Would depend on how you enabled logging for the users or accounts in question. See the SQL Reference: Data Definition Statements manual section on END QUERY LOGGING for details.

If you enabled logging at the account string level for all users then it should be as simple as using DBQLRules view to build END QUERY LOGGING ON ACCOUNT = ; statements and then running them.

Just be sure to have a copy of the exact rules you had in place before revoking them so you can re-enable them after your maintenance window has completed. Going forward it would be best to move the DBQL data on a more regular basis (daily or weekly). If you fill accidentally DBC you will have a bigger problem on your hands.