I want to run Profiler Traces, Perfmon Checks and whatever else to see what hits the server the hardest in order to work out where I can make performance improvements. We don't have any massive problems other than more timeouts than I would expect from web users no-and-then when they do pretty simple free-text searches. I just want to see if I can lighten the load on the server in general.
Where does a DBA get the most ROI? I don't want to drift into the realm of diminishing returns where I am making changes that have no noticeable impact.
What should I start measuring first and how do you measure it? (E.g. if I should monitor disk writes in Profiler, how many writes are too high for a statement? 100? If so, is one 100-write statement per hour OK but one 100-write statement per second is not OK?)
If there is already an article for this, my apologies. I'd be grateful if you can point me to it.
Is http://www.sql-server-performance.com/ a sufficient one-stop-shop for this question? If so, have you found some articles there to be more useful than others?
can anybody help me that how can we see sql server Health check with t-sql.Because i am trying to make script for this Basically i need these info.. 1.Current Date 2.Current time 3.Session,id 4.Session name 5.all locks 6.cpu utilization 7.how much disk space is used inside the database 8.how much space is free inside the database 9.Date read 10 Date write 11.how can we see process information with t-sql or with stored procedure
and i have solved 6 problems from this list i made queries for this . will u plz help me thaxxx
Hi Everybody, Can anybody tell me how to get the number of commands delivered per minute in case of Merge Replication with Publisher and subscribers. This way, we can be sure that even if there is a latency (due to high volume transaction processing), replication is in good shape and things will catch up soon. Also if there are any other similar measures which can be monitored to make sure that replication is going on fine, it would be great
Please let me know If anyone has got information on same.
What all are the command , or what for should one look out to confirm if nothing is wrong with the DB & its the application that is creating a prob.. I mean command for checking the health of the DB.
Recently I've installed Microsoft SQL Server Health and History Tool Version 2
and The SQLH2 Reports are a set of reports that provide different views on the data
that the tool collects. But unfortunately I can be able to see Data on Performone counters
Report. I can not find optional component SQLH2 Performance Collector is a stand-
alone service of Microsoft Windows that collects and stores performance counter data from
selected servers.
The Deployment guide tells :
Performance Collector is optional, and is available for download from the Microsoft Download Center (search for SQL Server with keyword SQLH2): http://www.microsoft.com/downloads/search.aspx
Hallo,MSDB.dbo.BackupSet logs all backupsis it possible find Status of Backup (Success / Failed)?Table "BackupSet" hasn't any information about it.I need log and notify all backups with status(It must be a windows service (c#)Thanks
Our database crashed this morning and went into recovery mode.how I can track the progress of the recovery to determine how long it might take?The error log shows that it started up all the databases, then shows the recovery messages fr the msdb database, then shows that sql server is ready for client connections. I don't see any messages about my database recovery or the number of transactions to roll forward or background. If i run the sys.sp_readerrorlog and search for my database name, the only line returned is the starting up database message.
I do expect the database to take a while to recovery as it is about 8TB, there is plenty of free disk space about 3TB.The database started recovery while a transaction log backup was running so that backup failed,the last transaction log backup was taken 2 hours before recovery started. The last full backup completed about 5 days ago. The transaction log backup occurs every 2 hours and is typically around 16GB.
I want to know through applicaiton how to check whether the Server named"Myserver" is Exists with Database named "MyDatabase" with Tables named ('Table1','Table2','Table3').
For any Database like (MS Access, Oracle, MS SQL-Server,MySQL)..
Hi, I have implemented health monitoring for my web-site, using the SQL provider. Health monitoring works fine when the website is run from VS2005, using the built in web server, all the expected events are inserted into the aspnet database. However when I deploy the site onto IIS, no events are ever inserted into the database. I would appreciate some help figuring out why this is happening! The code that implements the health monitoring in my web.config file is:1 <healthMonitoring 2 enabled="true" 3 heartbeatInterval="0"> 4 <bufferModes> 5 6 <remove name="Analysis"/> 7 8 <add name="Analysis" 9 maxBufferSize="10" 10 maxFlushSize="2" 11 urgentFlushThreshold="2" 12 regularFlushInterval="00:00:02" 13 urgentFlushInterval="00:00:01" 14 maxBufferThreads="1"/> 15 16 </bufferModes> 17 18 <providers> 19 20 <remove name ="SqlWebEventProvider"/> 21 22 <add name="SqlWebEventProvider" type="System.Web.Management.SqlWebEventProvider, 23 System.Web,Version=2.0.0.0,Culture=neutral,PublicKeyToken=b03f5f7f11d50a3a" 24 25 connectionStringName="SQL_ASPNET" 26 maxEventDetailsLength="1073741823" 27 buffer="true" 28 bufferMode="Analysis" 29 30 /> 31 32 </providers> 33 34 <eventMappings> 35 36 <remove name ="All Events"/> 37 <add name="All Events" 38 type="System.Web.Management.WebBaseEvent, System.Web,Version=2.0.0.0,Culture=neutral,PublicKeyToken=b03f5f7f11d50a3a"/> 39 40 </eventMappings> 41 42 <profiles> 43 44 <remove name="Default"/> 45 <add name="Default" 46 minInstances="1" 47 maxLimit="Infinite" 48 minInterval="00:10:00" 49 /> 50 </profiles> 51 52 <rules> 53 54 <add name="All Events" 55 eventName="All Events" 56 provider="SqlWebEventProvider" 57 profile="Default" 58 minInterval="00:00:01" 59 minInstances="1" /> 60 61 </rules> 62 63 </healthMonitoring> 64
I'm starting to collect and develop some scripts that will tell me the health and welfare of my MSSQL 2k server. I have a few for blocks, db size, who is on and what they are currently running.
I was wondering if you guys could share some of the scripts you guys use to watch the health of your servers.
I'm starting to collect and develop some scripts that will tell me the health and welfare of my MSSQL 2k server. I have a few for blocks, db size, who is on and what they are currently running. I was wondering if you guys could share some of the scripts you guys use to watch the health of your servers.
Hey all I am new here and fairly new to SQL server. And yes i have a question, that maybe dumb. My boss wants me to reporting to him the health of the DB each week. So here are a few questions. 1. What really defines the "health" of a DB? What elements should I be reporting to him? 2. Is there any utilities out there that would be helpful or can SQL provide enough info? 3. I am using 2005 standard, so I have the shipped reports in summary page. But is this enough? and can I customize them? Sorry I am having a hard time even asking the questions cause I am not really sure what to ask. Any help or pointing down t he correct path would be greatly appreciated. Thanks, jason
I've been running the Ola Hallengren maintenance script for the last five months without missing a beat. Today I find an error stating the UserDatabase Integrity check job failed last night. This is running on SQL Server 2014 BI edition w/64 Gigs.
I ran a DBCC CHECKDB on each database manually and all worked until I tried it on the biggest one that is about 18 gbs. It just keeps running and I eventually stopped it so I'm guessing it is memory, but doesn't make sense considering it has 64 gbs. I have it set to 64/4 max / min. Again, this was never an issue until last night.I've been looking up all morning, but not seeing much on this error "The operating system returned error 1453"?
I installed SQL Server 2005 Enterprise, then SP1 and then SP2, Maintenance Plan worked. But if I installed SQL Server 2005 Enterprise and then SP2 directly (skipping SP1), the Check Database Integrity in Maintenance Plan was not working. The error message is as follows:
Executed as user: DomainSqlServiceAccount. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:56:21 AM Could not load package "Maintenance PlansTest Plan" because of error 0xC0014062. Description: The LoadFromSQLServer method has encountered OLE DB error code 0x80004005 (Login failed for user ''. The user is not associated with a trusted SQL Server connection.). The SQL statement that was issued has failed. Source: Started: 10:56:21 AM Finished: 10:56:21 AM Elapsed: 0.047 seconds. The package could not be loaded. The step failed.
Other tasks like Rebuild Index seem to be fine. SP2 is supposed to be inclusive. Does anyone have any ideas on why this is happening.
Help Please!!! I am killing myself already...... the code is like this; Read the code, and the error.... this is bull, because every page in the site, including base page, and opther classes in the same assembly as this class use the exact same connection string call and open. And it always works!!!! However, when going thorgyugh health monitoring, i get the error: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
public class GBMSQLEventProvider : SqlWebEventProvider { private GBMErrorLog elog; private System.Data.SqlClient.SqlConnection db = null;
public override void Initialize(string name, NameValueCollection config) { elog = new GBMErrorLog(); elog.Write("GBMSQLEventProvider Process Initialize"); db = (SqlConnection)GBMDB.Open("HealthMonitoring"); <------ THIS LINE CAUSES ERROR base.Initialize(name, config); }
ERROR: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.Source Error:
Line 30: elog = new GBMErrorLog(); Line 31: elog.Write("GBMSQLEventProvider Process Initialize"); Line 32: db = (SqlConnection)GBMDB.Open("HealthMonitoring"); Line 33: Line 34: base.Initialize(name, config);Source File: C:GCMProjectsGBMFrameworkGBM.Web.UIEventsBufferedProvider.cs Line: 32 Stack Trace: Regards, and thank you in advance, Jonathan
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.
yes,I have an error, like 'The database file may be corrupted. Run the repair utility to check the database file. [ Database name = SDMMC Storage Cardwinpos_2005WINPOS2005.sdf ]' .I develope a program for Pocket Pcs and this program's database sometimes corrupt.what can i do?please help me
I am administering several SQL Servers running SQL Server 2005 SP2 Build 3042. I have a common maintenance plan that runs on each of the servers. The maintenance plan runs fine on all the servers except for one. On the one server the Database Integrity check fails with the following error:
Check Database integrity on Local server connection Databases: <list of databases> Include indexes Task start: 2008-02-21T00:05:42. Task end: 2008-02-21T00:05:46. Failed0) Alter failed for Server €˜XYZ€™
I created a test maintenance plan to just do the integrity check and selected one database only and this also failed with the same error message. I ran this test maintenance plan and configured it for each of the databases in question and it failed each time. If I run the DBCC manually against the databases they all report fine.
I read some of the post that talked about the €œAllow Updates€? being set incorrectly but that does not apply to my problem since my configured and run values are set to 0.
How do I check when my database was last used ? I have two old databases that I think is not in use, but I want to check to make sure before I delete them or take them "offline".
I am performing a number of queries on a database and inputting the results of those queries into a target database.
These queries are performed inside a number of data flows.
I would like to do two things:
1) Check that the target database can be seen before entering the dataflows. If not, wait X minutes and then retry.
2) If, for some reason, SSIS loses connectivity to the target database during the process, I would like to, is possible, roll back any current transactions and start over.
Unfortunately my knowledge of SSIS is very limited, so if anyone could lend a hand I would appreciate it.
There are some databases in a SQL Server. How to check, 1. User database numbers 2. Backup plan 3. Configuration 4. User database size 5. Nightly process
I was wondering if it was possible to check what server database you are currently connected to, using T-SQL, when executing commands in Query Analyzer. Even though you choose the server and database when connection, sometimes by habit you may connect to the wrong server & DB, and execute an .SQL file. Is it possible to put a line of T-SQL at the beginning that performs this pseudo-code, as a safety feature:-- While in Query Analyzer with a certain .SQL file open:If current server <> 'TheCorrectServer' and current DB <> 'TheCorrectDB' then cancel this .SQL file execution.
Hi everyone, I am looking for a way to check to see if a database is a snapshot or not. You can for example check different properties of a database by running the following:
I want to query my databases to find out if Auto Shrink is set. I know the status column in sysdatabases holds this value as a bit setting but I don't know how can I check this value using a query.
im trying to set up maintenance plan for the check database integrity...
In sql 2000 you get a nice little log in SQL Logs DBCC CHECKDB (WSS_Search_db3) executed by xxx found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 1 seconds.
But in SQL 2005 Im not getting a nice log of this but getting it against some system database and not the database i selected
Date1/22/2008 5:19:43 PM LogSQL Server (Current - 1/22/2008 5:19:00 PM) Sourcespid84 Message DBCC CHECKDB (mssqlsystemresource) WITH no_infomsgs executed by XXX found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 1 seconds.
Anyone got this to work? Trying to get the same message i got for SQL 2000 or at least so i can confirm it ran.
Hi everybody,Is there way to check what were the last modifys to specified database??I mean the last insertion, deletion ........... .. and in which tables.Thanks for advance--"Imagination is more important than knolwege" (Albert Einshtein)Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forum...eneral/200507/1
helloWhat query shoul I send to SQL serwer ( in transact SQL language ) to checkdoes some database exist on serwer ? It similar to problem "does some tableexist in database" - resolve to it is query:use db_silnikIF EXISTS (SELECT * FROM prad)PRINT 'table exist'but what is the query to check does some database exist on serwer ?best regardsAdam
Hello,I am trying to check the integrity of SQL database. The 'problem' withSQL server is that it's fixing the DB automatically after restore so Ineed a way to do it offline, much like using eseutil on Exchangedatabases.Thanks in advance.
