Database Health
Feb 28, 2006What 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.
Thanks,
ADVERTISEMENT
Database Server Health Check
May 9, 2007I 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?
Health Monitoring And IIS
Feb 5, 2007Hi,
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
Thank you in adance!
Jon
Health Checks On SQL
Jul 15, 2004I'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.
Thanks,
DMW
Health Checks
Jul 15, 2004I'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.
Thanks,
DMW
Reporting On Health Of Db??
Jan 17, 2007Hey 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
SQL Server Health And History
Oct 5, 2004Anyone had time to play with this yet? Wanna know if it was worth playing around with.
View 2 Replies View RelatedHow To See Health Check In Sql Server
Apr 9, 2008Hi,
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
jagpal singh
Trusted Connection - Health Monitoring - Please :-)
Apr 10, 2006Help 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
Microsoft Cluster Server Health Check.
Jun 29, 2005Hello,
View 2 Replies View RelatedMicrosoft SQL Server Health And History Tool Version 2
Sep 15, 2004Recently 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
But it does not give result
Where can I find the missing component ?
Thanks
Oleg
:(
SQL Server 2000: Replication Latency Health Check
May 25, 2004Hi 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.
SQL 2012 :: Approximate Hours Per Month To Monitor And Maintain Health Of Databases?
Jul 8, 2014Based 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.
SQL2005 Cannot Backup A Restored SQL2000 Database With Unknow Database Full-text Catalog Database
Nov 15, 2007
We replicate a SQL2000 database (DataBaseA) to a SQL2000 database (DataBaseB) by using the Restore function and hasn't change its logical name but only the physical data path and file name. It is running fine for a year. We use the same way to migrate the DataBaseB to a new SQL2005 server with the Restore function and the daily operation is running perfect. However, when we do the Backup of DatabaseB in the SQL2005, it just prompt the error message
System.Data.SqlClient.SqlError: The backup of full-text catalog 'DataBaseA' is not permitted because it is not online. Check errorlog file for the reason that full-text catalog became offline and bring it online. Or BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data. (Microsoft.SqlServer.Smo)
Please note we left the DataBaseA in the old SQL2000 server.
Please help on how we can delete the Full-text catalog from DatabaseB so we can do a backup
Many Thanks
Replication :: Syncing Of Database From Local Host Database To Online Database Automatically After Some Interval
Oct 14, 2015I have database on localhost and i want to show this data on my website. I want to create a database online and want to sync with Local Host. Can it be possible syncing data automatically after some interval?
View 6 Replies View RelatedThe Database File May Be Corrupted. Run The Repair Utility To Check The Database File. [ Database Name = SDMMC Storage Cardwinp
Oct 9, 2007yes,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
View 4 Replies View RelatedCoping Database Objects From One Database To Another Blank Database.
Aug 18, 2005I want to create a duplicate database in sql 2000 using asp.net from a webform
I created a database using CREATE DATABASE .......
But how to copy tables, views, stored procedures to newly created
database from old using asp.net from webform
Is there any another method to create a duplicate database with another name
from existing database on same server ?
An Attempt To Attach An Auto-named Database For File (file Location).../Database.mdf Failed. A Database With The Same Name Exists, Or Specified File Cannot Be Opened, Or It Is Located On UNC Share.
Sep 2, 2007Greetings, I have just arrived back into the country (NZ) and back into ASP.NET.
I am having trouble with the following:An attempt to attach an auto-named database for file (file location).../Database.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
It has only begun since i decided i wanted to use IIS, I realise VWD comes with its own localhost, but since it is only temporary, i wanted a permanent shortcut on my desktop to link to my intranet page.
Anyone have any ideas why i am getting the above error? have searched many places on the internet and not getting any closer.
Cheers ~ J
The Backup Set Holds A Backup Of A Database Other Than Existing Database. Restore Database Is Terminating Abnormally
Apr 9, 2008
I have a problem when i restore my .DAT_BAK file. I am getting error like "The backup set holds a backup of a database other than existing database. Restore Database is terminating abnormally".
I tried by using
RESTORE DATABASE <DATABASENAME>
FROM DISK = 'D:DATAMYTEST.DAT_BAK'
WITH MOVE 'VZAI_DATA' TO D:PROGRAM FILES..MSSQLTEST.MDF',
MOVE 'VZAI_LOG' TO D:PROGRAM FILES..MSSQLTEST.LDF',
REPLACE
And also i tried like
RESTORE DATABASE <DATABASENAME>
FROM DISK = 'D:DATAMYTEST.DAT_BAK'
WITH REPLACE
When i use like this,
RESTORE FILELISTONLY FROM DISK = 'D:DATAMYTEST.DAT_BAK'. I am able to get the output as LogicalName, PhysicalName, Type, FileGroupName, Size, etc.
Can i anyone please help me out?
Thanks in Advance,
Anand Rajagopal
Cant Create New Database / CREATE DATABASE Permission Denied In Database Master (error 262)
Oct 2, 2007
I am using SQL express and Visual web developer on windows Vista.
When I try to create a new database the following message appears.
CREATE DATABASE permission denied in database master (error 262)
I log on to my computer as an administrator.
Help appreciated
Prontonet
NORTHWIND Database Was Re-created From A Different Database:How Can I Change The Entry In Sysdatabases For Database 'NORTHWIND'?
Jan 14, 2008Hi all,
From the http://msdn.microsoft.com/en-us/library/bb384469.aspx (Walkthrough: Creating Stored Procedures for the Northwind Customers Table, I copied the following sql code:
--UpdateSPforNWcustomersTable.sql--
USE NORTHWIND
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'SelectCustomers' AND user_name(uid) = 'dbo')
DROP PROCEDURE dbo.[SelectCustomers]
GO
CREATE PROCEDURE dbo.[SelectCustomers]
AS
SET NOCOUNT ON;
SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM dbo.Customers
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'InsertCustomers' AND user_name(uid) = 'dbo')
DROP PROCEDURE dbo.InsertCustomers
GO
CREATE PROCEDURE dbo.InsertCustomers
(
@CustomerID nchar(5),
@CompanyName nvarchar(40),
@ContactName nvarchar(30),
@ContactTitle nvarchar(30),
@Address nvarchar(60),
@City nvarchar(15),
@Region nvarchar(15),
@PostalCode nvarchar(10),
@Country nvarchar(15),
@Phone nvarchar(24),
@Fax nvarchar(24)
)
AS
SET NOCOUNT OFF;
INSERT INTO [dbo].[Customers] ([CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax]) VALUES (@CustomerID, @CompanyName, @ContactName, @ContactTitle, @Address, @City, @Region, @PostalCode, @Country, @Phone, @Fax);
SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM Customers WHERE (CustomerID = @CustomerID)
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'UpdateCustomers' AND user_name(uid) = 'dbo')
DROP PROCEDURE dbo.UpdateCustomers
GO
CREATE PROCEDURE dbo.UpdateCustomers
(
@CustomerID nchar(5),
@CompanyName nvarchar(40),
@ContactName nvarchar(30),
@ContactTitle nvarchar(30),
@Address nvarchar(60),
@City nvarchar(15),
@Region nvarchar(15),
@PostalCode nvarchar(10),
@Country nvarchar(15),
@Phone nvarchar(24),
@Fax nvarchar(24),
@Original_CustomerID nchar(5)
)
AS
SET NOCOUNT OFF;
UPDATE [dbo].[Customers] SET [CustomerID] = @CustomerID, [CompanyName] = @CompanyName, [ContactName] = @ContactName, [ContactTitle] = @ContactTitle, [Address] = @Address, [City] = @City, [Region] = @Region, [PostalCode] = @PostalCode, [Country] = @Country, [Phone] = @Phone, [Fax] = @Fax WHERE (([CustomerID] = @Original_CustomerID));
SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM Customers WHERE (CustomerID = @CustomerID)
GO
====================================================================================
I executed the above code in my SQL Server Management Studio Express (SSMSE) and I got the following error messages:
Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'NORTHWIND'. No entry found with that name.
Make sure that the name is entered correctly.
===============================================================================================================
I know I recreated the NORTHWIND Database from a different Database before and I did not do anything for the entry in sysdatabases. How can I change the entry in sysdatabases for database 'NORTHWIND' now? Please help and advise.
Thanks in advance,
Scott Chang
Copy Database One Database To Onther Database
Mar 27, 2007hii want to copy one database table to onther database table using script?my database is ms-sql server 2000
View 4 Replies View RelatedSQL Server 2005 Express: The Database Principal Owns A Schema In The Database, And Can Not Be Dropped.
Jan 11, 2006I recently added a new user to my database. Now I want to delete that user, but I keep getting the error above. What do I need to do to delete my recently added user?
View 4 Replies View RelatedDatabase Create ODBC Connections To Access Database Directly And Update Data?
Sep 10, 2012We have a SQL database that uses Active Directory with Windows Authentication. Can users that are members of the Active Directory group that has read/write access to the SQL database create ODBC connections to access the database directly and update the data? They dont have individual logins on the server. They are only members of the Active Directory group that has a login?
View 1 Replies View RelatedSQL Server 2008 :: How To Update The Database Physical File Location In Master Database
Mar 8, 2015I had to to relocate the database log file and I issued an Alter database command but by mistake I put a space in the file name as below. The space is at the beginning file name. Now I am unable get the database loaded to SQL Server. The database has 2 replications configured, so deleting and re-attaching the database means the replication needs to be re-configured. Is there an alternative way to issue a command to update the database FILENAME ? Not sure if this can be edited in master database (sys files).
ALTER DATABASE [User_DB]
MODIFY FILE (NAME = User_DB_log, FILENAME = 'I:SQLLogs User_DB_log.ldf')
GO
I Have A Database On A Network Drive That I Use For Archiving Purposes, But When The Server Is Rebooted The Database Becomes Suspect.
Mar 31, 2007I created the db with the attached script and I am able to access ituntil I reboot the server. I've tried enabling flag 1807 via the SQLserver service and the startup parameters of the instance. In allcases the database always come up suspect after a reboot. There wasone instance where I was able to recover, but I am not sure how thathappened.Does anyone have an idea of how I can reboot the server without thedatabase becomming suspect?USE MASTERGODBCC TRACEON(1807)GO--DBCC TRACEOFF(1807)--DBCC TRACESTATUS(1807)GOCREATE DATABASE ReadyNAS ON( NAME = ReadyNAS_Data,FILENAME = '\NAS1NASDiskSQL ServerReadyNASReadyNAS_Data.mdf',SIZE = 100MB,MAXSIZE = 20GB,FILEGROWTH = 20MB)LOG ON ( NAME = ReadyNAS_Log,FILENAME = '\NAS1NASDiskSQL ServerReadyNASReadyNAS_Log.ldf',SIZE = 20MB,MAXSIZE = 100MB,FILEGROWTH = 10MB)
View 5 Replies View RelatedDatabase Explorer In VB Express:AdventureWorks.mdf-Files Do Not Match The Primary File Of The Database
Nov 5, 2007Hi all,
I downloaded and ran AdventureWorks.msi into my SQL Server Management Studio Express (SSMSE) one year ago.But I did not know how to attach it to my SSMSE then. Last week, I deleted it from the "Add or Remove" of Control Panel and I downloaded the new AdventureWork.msi and installed it my SSMSE. Today, I tried to use the Database Explorer of VB 2005 Express for the first Stored Procedure programming. I clicked on AdventureWorks.mdf and I got the following error: One or more files do not match the primary of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupt and should be restored from a backup. Cannot open user default database. Login failed. Login failed for user 'CENADe1enxshc'. Log file 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataAdventureWorks_Data_log.ldf' does not match the primary file. It may be from a different database of the log may have been rebuilt previously. Please help and advise me how to correct this problem.
Thanks,
Scott Chang
2005 Database Will Be Shown In Management Sudio Express Or App_Data/Database Explorer?
Feb 13, 2008 I have installed 2 SQL Server 2005 Express sample databases from 2 books, ASPnet 2.0 and ADOnet 2.0. The ASPNETDB.MDF was shown in App_Data and Database Explorer, but not in the SQL Server Management Studio Express. The AdoStepBy Step database created by a ConfigDB.exe was displayed in the Management Studio, but not in the App_Data, or Database Explorer.
Is this the way SQL Server 2005 runs the 2005 databases for SQL Server 2005 Express only? Or also in SQL Server 2005?
TIA,
Jeffrey
SQL Security :: How To Create Database Specifications On Newly Created Database Automatically For Audit
Jul 15, 2015I am setting up SQL audit on sql servers in my environment based on requirement. I want to create database specifications ASAP database created. I tried DDL trigger but Audit doesn't support triggers. So I created audit specifications on model database. the only problem with this is every specification created on new database with same name.database specification name includes newly created database name or other methods to create database specifications on newly created databases.
View 6 Replies View RelatedIntegration Services :: Import Varying Number Of Tables Each Time From One Database To Different Database
Sep 9, 2015I am new to SSIS. I have been struggling with this for the past one week. I have a weird task. I need to import several tables from one database to a different server with a new database name. We need to do this at the end of every year. The main problem here is that the number of tables varies every year. You may not have all the tables as last year or may have more tables. So I need to create a dynamic task that takes care of this every year without changing the package.
I have performed the following tasks **
1. Create a new dynamic database. ( I have used Execute SQL Task to do this) 2. Copy all the table structures ( I have used Execute SQL Task to do this)
3. Import Data. This is the main problem. I was trying to create a dynamic connection string with variables as suggested in several forums but I finally came to know that this cannot be done if the table structures are different as the metadata cannot be refreshed at runtime.
4. The final step to create a process to validate the data (the count from each table for both source and destination. I think this can be done with Sql task.
What is the best method to do this? My DBA does not like “Transfer SQL Objects Task” or “transfer Database Task”. I would like to create this as a dynamic process.
Migrating From Sql2000 MSDE Database To Sql2005 Express - Attach Database Errors
Apr 23, 2006I have a medical records system, SoapWare v4.90, that uses MSDE (SQL2000) databases. Due to the 2gb limitation, I am trying to migrate over to SQL 2005 (Standard or Express) which I have heard works fine. The SoapWare has a datamanager that allows me to log in to the MSDE instance, detach the SoapWare databases from msde (as well as do backups, etc) which I can confirm are detached.
Then I log back into a SQL2005 database instance using the datamanager and try to attach the database. This is what their pictured instructions demonstrate. However, I get the following error:
Database 'sw_charts' cannot be upgraded because it is read-only or has read-only files. Make the database or files writeable, and rerun recovery.
Of course, some of the entries will be read only, since doctors have to sign off the charts and are not allowed to subsequently change them. But I should still be able to switch over to sql 2005?!?!?!?
Or... is there a way to attach the databases to SQLExpress manually?
Help pls?
The Log File For Database 'Home_alone' Is Full. Back Up The Transaction Log For The Database To Free Up Some Log Space.
Feb 4, 2008
Hi all
Iam working in Prodcution ENV,Please help how make space
The log file for database 'Home_alone' is full. Back up the transaction log for the database to free up some log space.
Question: Can I Synchronize The Mobile Device Which Has A SQL Server CE Database With The Access Database On The Desktop?
Sep 26, 2006Dear All,
i have a question abt winCE 4.2 and SQL server CE.
i am using VB.net of Visual Studio 2005
My platform is using a PDA with winCE 4.2 and SQL server CE. The Host program is using dbf files on desktop side.
I got a problem of how to sync / read the sql CE data from a windows application.
so, i wanna ask,
1. any method to access the data from winCE data by windows application? or can i convert the sdf file to windows readable files? or any others?
2. Can i use a MDB to sync with SQL server CE?
can i synchronize the mobile device which has a SQL Server CE database with the Access database on the desktop?
last question,
3. is that windows CE .net 4.2 not support pocket access (cdb) anymore?
please help me out