June 12, 2008 at 9:26 am
I double checked and the profile is enabled. The script to set the jobs to write to the event log worked (one of the dummy job now is set to write to teh application log) but I am still not seeing the deadlock in the logs though.
June 12, 2008 at 10:10 am
Nice article. We are going through an upgrade to 2005 and it's nice to see what your doing being confirmed by your peers.
I would also add some more notes on hardware set-up. I'm a little weak in this area and like to see more on the initial set-up so I know I'm going to get the most out of the server.
For instance, I didn't know about volume alignment and found the following article very helpful:
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/pdpliobp.mspx
June 12, 2008 at 10:45 am
Great article! I may have missed these but, a couple other items that come to mind:
- Security, who has access and who should or shouldn't have access?
- Service accounts being used.
- Auditing of schema changes.
- Do any tables need to have auditing setup?
- Under backups, are transaction log backups not being done that should be?
- Linked Servers.
- Index usage statistics
June 12, 2008 at 10:50 am
Try a different alert, I'll be honest I have been lucky enough not to come across any deadlocks in SQL Server 2005 databases I've supported - things have been looking up, the deadlock event was set up and taken from a SQL Server 2000 script.
USE [msdb]
GO
/****** Object: Alert [Deadlock] Script Date: 06/12/2008 17:46:22 ******/
EXEC msdb.dbo.sp_add_alert @name=N'Deadlock',
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]',
@performance_condition=N'SQLServer:Locks|Number of Deadlocks/sec|Database|>|1',
@job_id=N'00000000-0000-0000-0000-000000000000'
June 12, 2008 at 10:50 am
Carlo Mitchell (6/12/2008)
Great article! I may have missed these but, a couple other items that come to mind:- Security, who has access and who should or shouldn't have access?
-
Ack! Security! Another one of those areas I have to read articles on....:w00t:
June 12, 2008 at 11:53 am
SSWUG.com asked a similar question. Since I'm primarily security focused, that was most prominent on my mind. Here's the list I sent them in an email:
Membership of the local Administrators group.
Quick vetting of anyone in the local Administrators group.
What service accounts the services are running under and what rights they have on the server.
Quick vetting of who has the passwords to these service accounts.
Disk allocation and used/free space.
All logins to the SQL Server.
All logins assigned to the various server roles.
All owners of the individual databases.
Quick vetting of anyone that's a member of the sysadmin fixed server role.
Quick vetting of anyone who has a password to a SQL Server login that's a member of the sysadmin fixed server role.
Backup strategy on each database.
Any information on successful testing of restores.
Applications which use the SQL Server.
How they connect in.
Documentation on the actual rights they need.
Comparison of what rights they were given.
How are the database files distributed?
Is the RAID configuration sufficient?
K. Brian Kelley
@kbriankelley
June 12, 2008 at 11:55 am
Hi Carolyn,
I still can't get the deadlock to show up in the log. I added the new alert that you mentioned and I added the notify operator as well to the new alert.
Is there something else I can check or do to get the message to show up in the log. I already ran the script that was mentioned in the link in the your original article.
Thanks
MP
June 12, 2008 at 6:50 pm
MP (6/12/2008)
Hi Carolyn,I still can't get the deadlock to show up in the log. I added the new alert that you mentioned and I added the notify operator as well to the new alert.
Is there something else I can check or do to get the message to show up in the log. I already ran the script that was mentioned in the link in the your original article.
Thanks
MP
I like this kind of articles. Wish to have a checking script to automate some of the tasks and generate a report.
Nice to see another way to catch a deadlock. I have a related post and got a few good answers recently. http://www.sqlservercentral.com/Forums/Topic514877-146-1.aspx
June 13, 2008 at 3:11 am
Right click on the properties of the event and look at history. Has the alert ever been triggered? The history will tell you. If it has fired the failure to send you an email is where the problem is, if it hasn't fired you need to try another alert or look at adding trace flags as per the link in the last post.
June 13, 2008 at 9:56 am
Thanks for the article. I've recently inherited a number of SQL Servers, so have had to give this some thought.
Here's a few additional items...
Has a recent DBCC CHECKDB been run on every database? If not, do so ASAP.
Examine the current logs (SQL Server, Windows) on the server. Any problem areas ?
See what features are enabled under Surface Area Configuration
Find out what client libraries are in use. If IP, does SQL Server listen on a non-default port (For security, hopefully, yes!) From my experience, use of an incorrect port is the cause of a majority of client connectivity issues
February 5, 2009 at 2:02 pm
Great article but my problem is this, "I'm new SQL and just loaded SQL 2005 and what/how do I do next?" I can read the article but it doesn't show "How to the steps." Screen shot or a book with picture would be great help.
February 5, 2009 at 2:21 pm
frank.morales (2/5/2009)
Great article but my problem is this, "I'm new SQL and just loaded SQL 2005 and what/how do I do next?" I can read the article but it doesn't show "How to the steps." Screen shot or a book with picture would be great help.
If you use this article and discussion as a starting point, you can take each point the author brings up and research them one at a time. It'll be a great introduction to database administration. Kind of like treating this article as if it were the table of contents for a book on being a DBA.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply