Hi to all experts,
I have a server with about 50 databases and the server bandwidth usage is 15-30 Mbps.
I need to know which databse is consuming the bandwidth.
Can you tell me what is the best way (if any) to monitor bandwidth usage per database?
Hi!
Please give me an idea how to monitor database size.
Thank you
Hi,
I logged some of the parameters of my SQL server using the Performance monitor into a log file - smn5.log & the log settings in smn5.pml. I started the log and the log file (smn5.log) started growing in size indicating that it was collecting data.
I then went to Options button and said Save. After this in the File menu, I selected Export Log and saved it in a .CSV file, expecting it to contain the Logged data. However, it contains only the Log settings as shown below :
--------------------------------
Reported on L&T1362
Log File C:MSSQLLOGsmn5
Interval: 15.000 seconds
Object,Computer
SQLServer-Log,ERMINTRUDE
SQLServer-Procedure Cache,ERMINTRUDE
SQLServer-Locks,ERMINTRUDE
SQLServer,ERMINTRUDE
SQLServer-Log,FLORENCE
SQLServer-Procedure Cache,FLORENCE
SQLServer-Locks,FLORENCE
SQLServer,FLORENCE
---------------------------------
Could some one please tell me how to gather and view the logged information ? smn5.log contains 10MB of data - the perf. monitor shows that.
Thanks
Satish
im trying to measure the amount of CPU used in a backup and restore, using the SQL Server Databases: Backup Restore counter in Perfmon.
I can select The counter, but it does not let me select an instance database to monitor.
see image:
I have 3 instances on my machine, 2 sql 2005 and 1 sql 2008. why do these instances not appear? i dont know how to set up this monitoring.
if i type LOCALHOST or 127.0.0.1 in the "Select counters from computer" text box i can see all databases under my sql 2005 instances, but not the ones under the 2008 instances. I have disabled all sql server 2005 services and can confirm none of the sql 2008 instance databases are showing up.
this is a huge problem, especially for when i start stress testing sql server 2008 in preperation for upgrading.
anyone any ideas on why i cannot see sql server 2008 instances in perfmon? I have tried from a remote pc also with the same results.
Based on the description below on average how many hours a month would it take to monitor and maintain the MSSQL Server databases?
Description of IT infrastructure.All Windows Servers and MSSQL Servers are up to date on patches and best practices.
Corporate site with 3 remote sites.
All remote sites have one DC and one MSSQL Server.
The corporate site has one MSSQL Server.
Replication is performed between the remote MSSQL databases and the corporate office MSSQL database.
There is no in-house DBA. All DBA services will have to be outsourced. I am trying to determine what is reasonable in budgeting for time involved for this service.
There is one project written in MS Access using Visual Basic for Applications (VBA) with the backend residing on these database.
The question is on average approximately how many hours a month would it take to monitor and maintain the health of the MSSQL Servers database by a MSSQL DBA. The DBA will not have to create any user reporting, queries, etc. Just maintain the existing MSSQL Servers database.
How to restrict resources usage based on individual Databases in resource governor?
We have many databases in one instance; I would like to restrict resource usage to each database respectively.
I created 2 pools as pool_login, pool_DBNAME, and 2 workload groups as GroupLogin,GroupDBNAME, and also the classifier function.After setup above, I use following statement to check what sessions are in each group .
Even if there are spids which are accessing database DBNAME, I can’t see that they fall into the group GroupDBNAME and pool pool_DBNAME.
SELECT s.group_id, CAST(g.name as nvarchar(20)), s.session_id, s.login_time, CAST(s.host_name as nvarchar(20)), CAST(s.program_name AS nvarchar(20))
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_resource_governor_workload_groups g
ON g.group_id = s.group_id
ORDER BY g.name
GO
Following is the code to create pool, group,classifier function:
USE master
GO;
-- Create a resource pool pool_login.
CREATE RESOURCE POOL pool_login
WITH
[Code] ....
-- Create a workload group to use this pool.
CREATE WORKLOAD GROUP GroupLogin
USING pool_login;
GO
CREATE WORKLOAD GROUP GroupDBNAME
USING pool_DBNAME;
[code]....
-- Register the classifier function with Resource Governor.
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.rgclassifier_v1);
GO
If I'm on a remote machine, meaning a computer not in the WSFC cluster, and I open SSMS 2014, point it to a SQL Instance, and open activity monitor:
1. I get all the panes and charts except % Processor Time.
2. Then, if I authenticate to the cluster's domain by mapping a drive with valid domain credentials, I'm free to put performance counters in the Perfmon - - - but SQL Activity Monitor shuts down with“The Activity Monitor is unable to execute queries against server SQL-V01INSTANCE1..Activity monitor for this instance will be placed into a paused state.Use the context menu in the overview pane to resume the activity monitor.
Additional information: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))(Mscorlib)”
3. Of course, the Activity monitor can't be resumed via the context menu. Removing counters and closing the perfmon do not work. I dropped the mapped drive and rebooted the machine. That brought back 95% of the information in the Activity monitor.
4. Further experimentation showed that any mapping of drive shares present on the SQL Server to the computer running SSMS cut off functionality of the 'overview' pane in the remote machine's SQL Activity monitor -- the monitor that had been trying to watch the server offering the shares.
I have scenerio that find out the Bandwitdh size between clint and server.
I wanted find out howmuch size of data recieveing from server at a time.
Any advice regarding this.
Thanks,
Ravi
I am building a SQL server on a Windows 2000 Server box. It has a public IP and we have 4 T1 lines. After I install SQL 2000 it will run for about 30 minutes and then it will take up all the bandwidth on the network to such a degree that my users are unable to access the internet. I have been troubleshooting this over the last couple of days. This morning I reinstalled SQL with just the default DB's and after 45 mins it brought the network down again. Any help on this would be greatly appreciated.
View 3 Replies View RelatedHi there SQL Team
I hope I’ve posted this in the right section.
We have a customer, call them customerA
CustomerA has just employed a new Chief Information Officer.
The New CIO would like to make a few changes to the current way we have things right now.
Bare with me as I explain our environment.
We have The following Servers
Application Server[FSS (Dual DC Xeon 2.33GHz CPU, 4GB RAM, 3x72GB hdd RAID]
Database Server [FSS (Dual DC Xeon 2.33GHz CPU, 4GB RAM, 3x72GB hdd RAID)]
Both the above servers sit in a fully fault tolerant environment with daily backups power generators etc etc etc.1000MB network everything work 100%
Now my question:
The new CIO would like to move the DB server out of this environment to a remote building downtown, and have the application server connect to this remote db server.
Is this advisable?
What are the required connection speeds, so that the end user does not have to wait for a response?
What is the recommended response time IIS should give?
Please also note that we are in South Africa and broadband is VERY expensive / unreliable.
The CIO would like to install a 2Mbit radio link from this building to the ISP I’m not sure on the cost, but do know that this type of link is very unstable,
Their failover will be a 512kb fixed line or diginet line. Cost about ZAR 8000.00 per month
The application is extremely data / graphs intensive as it contain property information.
I think they average about 16000 visitors daily, not a lot.
If anyone can give me any input i would appreciated.
Thanks
HiI have two SQL2000 servers in different sites, once a day approximately1M of data in the form of a large update is required to be transferedbetween the 2. We have use of a 2M pipe between the servers but thereis no quality of service, the other users on the pipe are traders sothere must be no interruption in the quality of their bandwidth at anytime.Is there any way of throttling back the data transfer between the twoservers to restrict its bandwidth use. Obviously we want to retain themax bandwidth on our local network.The pipe is administered by a seperate company so we do not have adminaccess to their gateways, routers etc.. so a solution which we canimplement on our database servers would be the easiest.I am not sure if this is the right newsgroup for this but anyinformation would be greatThanksMark
View 1 Replies View RelatedDoes anyone know a wayto compress data between two database connections over "low bandwidth" lines in order to speedup datatransfer?
Used connections are Oracle<->SQL2000 and SQL2000<->SQL2000.
Hi
I am evaluating the possibility of replicating a database over a network to our HQ from the control site (one way). The original database is on SQL server.
The database is likely to grow to many terabytes so we would be using transactional replication.
The table to be replicated recieves about 500 records per second.
The table will probably consist of a record key (8 byte int), site ID (4 byte int), reading (8-byte float), and timestamp (8 byte timestamp). All up, 28 bytes + whatever overhead exists.
MINOR DETAIL:
The HQ's copy should be preferably no more than 1 hour behind the control site's. This would be a long-term setup that would last for many years. Our link is currently about 2 MB/s, and is fairly reliable.
QUESTIONS:
I'm guessing that (bytes/record)*(records/second) won't be the whole story. Does anyone have an estimate of the average data efficiency factor for transactional replication?
How would I go about calculating how much bandwidth would be needed? Is there a formulae hiding somewhere?
Thanks in advance.
Simon.
All:
One of the new tests that we are running have to do with load testing an application with a constrained network pipe. I like this. One of my beefs has been related to stored procedures that return bloated result sets. This new set of tests potentially gives me some more amunition to use when I review stored procedures. A piece that I would like to produce as a result of this has to do with establishing an output bandwidth standard for our database servers. I have a few biased ideas but I would like to know if any of you have any similar pre-existing standards along this line. Any help?
Dave
I have a client program that writes to sql server database 10 records per second . i want to compute the CPU usage and the memory usage for the whole program or CPU usage,memory usage for the insert statement in the program .
Can anybody help me with this?
Hello, When I am seeing SQL Server 2005 Management studio Server Dashboard> I am seeing my(USERS) databases and msdb database usage is very small % of in CPU Usage(%), Logical IO Performed (%) Usage pie chart.
90% of Total cpu usage is showing for Adhoc Queries. what excatly this means in Dashboard? if application uses more than it would have shown in Database level or not?
sicerely this dashboard is good, if any one is watching daily, please advice their experiences here.
Thanks in advance. Hail SQL Server!
I have setup transactional replication everything on one box. later(two or three weeks later), Replication monitor is show red X Under my publishers (publications is disconnected). this is SQL2005.
Everyone known how to fix this problem?
Thanks,
Frank
providing a query for fetching the data for CPU Usage, Memory usage, blocking and all details ...
I want to create a job which will run on a Node every 15 min and store data in a table for each instance...
DMV is not giving more stuff and xtended events not sure if i can store that data into a table?
Hi All,
I am setting up P2P replication for a high latency environment (although at 100MBit per sec, the bandwidth is not a serious issue ).
I have noticed that at high load, the bandwidth between Distribution and Subscriber servers maxes out at 1MBit per second (our network bandwidth is 100MBit per second).
This causes transactions to 'back up' and when the load reduces they 'catch up' and synchronise.
Does anyone know where this limitation of 1Mbs is being enforced and what is the way round it?
I have seen the -SubscriptionStreams NN parameter, but this is not applicable in P2P replication.
Any advice would be appreciated.
Thanks
Aasim
Dear Readers,Is it possible, like in Access, to link to tables in other SQL databases that are on the same server? I have a query that I originally had in Access that queered from multiply databases. It did this by having those other tables in the other databases linked to the database that had the query.
Hi, Today, I heard about Sql Profiler, and I seems to be a tool to monitor, analyze, and fine tune sql server. Is there a tool like this for SQL Server Express 2005? thanks,John
View 1 Replies View RelatedHullo. I have a SQL SCHEDULER Job that keeps hanging.
1) How do I monitor this? I would like to have another Sql Job monitor the first one hourly, and send me an email if it is hanging. Isn't there a system table that tells me a job status?
1a) Once I find the job, how can I "Stop it" automatically?
2) Is there a way to have the Job stop itself it it runs for more than 20 minutes?
Thanks.
~Le
Hi ..
i have a task to monitor the sql jobs..
i wish to do with this with C# or asp.net application.
please help me to finish this..
Hi all,
Can anyone please tell me whether there are any ways we can check whether a connection that was opened to a SQL Server database is closed. Is there a tool we can use?.
Thx
Hakl
Would the Performance Monitor be the best tool
to use if you wanted to find out how many transactions
are being process a day?
How do I access performance monitor (system monitor).I am using Windows 2000 professional, SQL server 2000. Do I need to set up analysis service or something? I am not seeing system monitor on my box? am I missing something?
View 5 Replies View RelatedHello all,
I want to know whether any performance monitoring tools/procedure available in market for SQL Server other than Performance monitor, Profiler, Query Ananlzer of SQL Server.
thank you in advance.
regards,
Nana
Hi guys!
Please tell me where is mistake?
I have win.nt workst. 4.0 and SQL Server 6.5 Client Configuration Utility installed.
I open Performace monitor and try to add to chart sql server which already have been registered in SQL Enterprise Manager.
And I have error message: Computer name not found.
Why??????
Thank you.Sev
Until this week, I had full functionality of SQL Monitor`s capabilities to monitor SQL Server. Now, when I select our Production Server and attempt to `Add to Chart` I am unable to view/select any SQL Server... entries in the `Object` dropdown list box. When I click the down arrow with the Development server selected I DO see all of the SQL Server selections. This inability to `see` the SQL Server functions in the Performance Monitor for the Production server exists regardless of whether I attempt to view them from a Win NT 4.0 Workstation or at the Server`s console. Again, I had full functionality from both locations last week. The logins have not changed. What has changed?
ANY help would be GREATLY appreciated. I need to monitor this system and I`m running blind at the monent.
TIA
Mike Gaudet
Visages, Inc.
Hello,
I do all my monitoring locally for disk space, locks, blocking, I've 10 production servers, We need to centralised the monitoring server so from one server all the monitors can be done. Does anyone has any ideas how memory, cpus consumption, disk space, all alerts, locks, blocking, log space and job completition monitoring can be handled.
Any idea or guidance is appreciated
Thanks
Roma
Help ????????
I have a winnt and sql server 6.5 when i open up sql performace monitor it doesn't show any thing it comes blank when i go to reintialize the sqlserver object is not there i need to know how can i fix it
its urgent
girimanjesh
I'm trying to record all the queries running on a specific database to a file.
Does anybody know of free or commercial utility that can do that?
Hi,
How do most of you monitor your SQL servers (i.e. performance, cpu utilization, job failures...)? Can you recommend any tools that are not too costly?
Thanks.
Mary