Re: SQL Server Monitoring with CF
Hi John, what you could do ist to write some sql-statements that retrieve exactly the values you need. In general I use the SQL-Profiler to record the statements I need. Therefore I open the Enterprise Manager and Display the properties of a certain database while having the SQL-Profiler recording every SQL-Statement. From the resulted Statements I extract the ones important to me. For example retrieving every Table or view from a certain database: Select Name from SysObjects Where XType = 'U' It's a little complicated to find the statements you need, but much easier than writing the statements yourself searching the SQL-Server help. You then could run a scheduled task on the cf-server in order to monitor the activity on the server. (But not too often, since it could hurt performance). BTW: Here two statements that returns the size of a certain database: Use [Databasename] 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 Go SELECT fileid, name, filename, size, growth, status, maxsize FROM dbo.sysfiles WHERE (status 0x40) 0 Go But you need to have access to the master database in order to retrieve this information. If you need any further information, just contact me off the list. cfgreetings name=Gert Franz location=Switzerland email=[EMAIL PROTECTED] function=Railo Core Developer John Lucania schrieb: I want to monitor the sql server connected to CFMX. What I want is: - the total sizes the sql server drives - free sizes the drives - size of each database in cfmail. Any ideas? Please advise. jl ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:230808 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: SQL Server Monitoring with CF
Hi JL, you could also use some or a special stored proc: cfquery name=GetDatabases datasource=DatasourceWithSaRights sp_databases /cfquery !--- This is a global stored procedure which returns you exactly what you need --- cfdump var=#GetDatabases# Returns the databasename, the size and remarks of the database cu Gert Franz Technical Support [EMAIL PROTECTED] www.railo.ch John Lucania schrieb: Gert, Thank you for the tips. I have 40 databases. Is there a way to use 'loop'? If not, it seems I need to repeat the query for each database. Please advise. Thank you, jl -- Forwarded message -- From: Gert Franz [EMAIL PROTECTED] Date: Jan 31, 2006 3:45 AM Subject: Re: SQL Server Monitoring with CF To: CF-Talk cf-talk@houseoffusion.com Hi John, what you could do ist to write some sql-statements that retrieve exactly the values you need. In general I use the SQL-Profiler to record the statements I need. Therefore I open the Enterprise Manager and Display the properties of a certain database while having the SQL-Profiler recording every SQL-Statement. From the resulted Statements I extract the ones important to me. For example retrieving every Table or view from a certain database: Select Name from SysObjects Where XType = 'U' It's a little complicated to find the statements you need, but much easier than writing the statements yourself searching the SQL-Server help. You then could run a scheduled task on the cf-server in order to monitor the activity on the server. (But not too often, since it could hurt performance). BTW: Here two statements that returns the size of a certain database: Use [Databasename] 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 Go SELECT fileid, name, filename, size, growth, status, maxsize FROM dbo.sysfiles WHERE (status 0x40) 0 Go But you need to have access to the master database in order to retrieve this information. If you need any further information, just contact me off the list. cfgreetings name=Gert Franz location=Switzerland email=[EMAIL PROTECTED] function=Railo Core Developer John Lucania schrieb: I want to monitor the sql server connected to CFMX. What I want is: - the total sizes the sql server drives - free sizes the drives - size of each database in cfmail. Any ideas? Please advise. jl ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:230833 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
SQL Server Monitoring with CF
I want to monitor the sql server connected to CFMX. What I want is: - the total sizes the sql server drives - free sizes the drives - size of each database in cfmail. Any ideas? Please advise. jl ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:230729 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL Server Monitoring with CF
Hi John, You can monitor SQL db sizes from Windows Performance Monitor and schedule an alert to trigger when the db size goes over a value. The alert can run an external script (a batch file for example). Perhaps you could have the script create a file that a scheduled CF task subsequently looks to see if it has been created and create a cfmail if found? Jenny -Original Message- From: John Lucania [mailto:[EMAIL PROTECTED] Sent: 30 January 2006 14:58 To: CF-Talk Subject: SQL Server Monitoring with CF I want to monitor the sql server connected to CFMX. What I want is: - the total sizes the sql server drives - free sizes the drives - size of each database in cfmail. Any ideas? Please advise. jl ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:230751 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
