SQL Server Performance Forum – Threads Archive

Need help on internal SQL server batch cmds

Hi, Recently i found my database very slow. The insert operation often timeout, after a few days, and, in a regular basis. Below is what I found from the SQL profiler. Could somebody helps me on what do those batch commands do ? Cos I found that these batch commands executes periodically, and some times it took a very long time to execute, and during these interval, my insert operation timeout. Configuration:
SQL Server 2000, FULL Recovery Mode, Autogrow enabled. Thanks ! ====
use [master] exec sp_helpreplicationdboption exec sp_MSdbuseraccess N’db’, N’%’ DECLARE @retval int EXEC @retval = master.dbo.xp_MSADEnabled IF (@retval = 0) SELECT 1 ELSE SELECT 0 set noexec off set parseonly off
master.dbo.xp_regread N’HKEY_LOCAL_MACHINE’, N’SoftwareMicrosoftMSSQLServerSQLServerSCP’, N’objectGUID’ set noexec off set parseonly off select name, DATABASEPROPERTY(name, N’IsDetached’), (case when DATABASEPROPERTY(name, N’IsShutdown’) is null then -1 else DATABASEPROPERTY(name, N’IsShutdown’) end), DATABASEPROPERTY(name, N’IsSuspect’), DATABASEPROPERTY(name, N’IsOffline’), DATABASEPROPERTY(name, N’IsInLoad’), (case when DATABASEPROPERTY(name, N’IsInRecovery’) is null then -1 else DATABASEPROPERTY(name, N’IsInRecovery’) end), (case when DATABASEPROPERTY(name, N’IsNotRecovered’) is null then -1 else DATABASEPROPERTY(name, N’IsNotRecovered’) end), DATABASEPROPERTY(name, N’IsEmergencyMode’), DATABASEPROPERTY(name, N’IsInStandBy’), has_dbaccess(name), status, category, status2 from master.dbo.sysdatabases select sum(convert(float,size)) * (8192.0/1024.0) from dbo.sysfiles exec sp_spaceused DBCC SQLPERF(LOGSPACE) use [master] select max(backup_finish_date) from msdb..backupset where type = ‘D’ and database_name = N’LogDB’ set noexec off set parseonly off select max(backup_finish_date) from msdb..backupset where type = ‘L’ and database_name = N’LogDB’ set noexec off set parseonly off select p.plan_name from msdb..sysdbmaintplans p, msdb..sysdbmaintplan_databases d where (d.database_name = ‘All Databases’ or d.database_name = ‘All User Databases’ or d.database_name = N’MasLogDBMode1′) and (p.plan_id = d.plan_id) set noexec off set parseonly off select collation_name(value) from master.dbo.syscurconfigs where (config=1124) set noexec off set parseonly off SELECT 1 FROM msdb..log_shipping_databases where database_name =N’LogDB’ set noexec off set parseonly off select p.source_server, d.source_database from msdb..log_shipping_plans p, msdb..log_shipping_plan_databases d where (d.destination_database = N’LogDB’) and (p.plan_id = d.plan_id) set noexec off set parseonly off xp_instance_regread N’HKEY_LOCAL_MACHINE’,N’SoftwareMicrosoftMSSQLServerMSSQLServer’,N’DefaultData’ set noexec off set parseonly off select filename from dbo.sysfiles where fileid = 1 use [model] select convert(int, ceiling((sum(convert(float,size)) * 8192/1024) / 1024)) from dbo.sysfiles where groupid = 1 SELECT groupid, groupname, FILEGROUPPROPERTY( f.groupname, N’IsReadOnly’ ), FILEGROUPPROPERTY( f.groupname, N’IsPrimaryFG’ ), FILEGROUPPROPERTY( f.groupname, N’IsDefault’ ) FROM dbo.sysfilegroups SELECT o.fileid, o.name, o.filename, o.groupid, o.size, o.maxsize, o.growth, o.status FROM dbo.sysfiles o WHERE o.groupid = (SELECT u.groupid FROM dbo.sysfilegroups u WHERE u.groupname = N’PRIMARY’) and (o.status & 0x40) = 0 select FileProperty( file_name(1), N’IsPrimaryFile’ ) use [master]
xp_instance_regread N’HKEY_LOCAL_MACHINE’,N’SoftwareMicrosoftMSSQLServerMSSQLServer’,N’DefaultLog’

Have you checked the execution plan for the running queries?
And also apply optimum indexes to speed up the query execution.
ALso allocation dynamic memory to SQL server. What are those internal batch commands you’re concerned about, I don’t see any particular issue with them. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Hi ya, it looks like some sort of monitoring software like HP OpenView or BMC Patrol or something like that? Cheers
Twan

This looks like a some monitoring / diagnostic / alerting software configured on the SQL server. The commands are generating data about backup status and size of the database. I am also not seeing any performance issues with these commands.
/* SKChandra */

Go to Services and see if you have a MOM agent running on this server. If not, ask the systems group if they have an agentless MOM agent running (or something similar). I doubt it’s HP OpenView. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

I checked with the system guy. They are running some SQL server health monitoring program (not HPOV). Um…sometimes these batch commands took a long time to execute, causing my application insert to fail. So to confirm, those batch commands will not lock the DB while executing ?

I dont see anything that would cause excess locking
How long does it take to execute? I think your application insert failures are some other problem

Hi wycklk, Those commands are issued by Enterprise Manager when you or one of your admins views a database. They are getting the information needed to fill out the database property sheet (when you right-click the database and pick "Properties…"). I don’t know why these take too much time. Autoclose being set on your databases is one possibility. (Google for "sql server autoclose option"). Cheers
skissane

]]>