Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Mini-Me


Mini-Me

Author
Message
GabyYYZ
GabyYYZ
Say Hey Kid
Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)

Group: General Forum Members
Points: 673 Visits: 2332
99% of the scripts we use are in house or customized from sites like SQLServerCentral.com

That being said, there are a couple third party tools that are worth their weight in gold such as ones that compare databases on a QA/Development Server with their eventual incarnations in production. Any script involving heavily used remote logins, I'd always be hesitant about.

That being said, there's a cool feature about SQL 2008 Management Studio I read about at http://www.sqlskills.com/BLOGS/KIMBERLY/post/SQL-Server-2008-Central-Management-Servers-have-you-seen-these.aspx where for example, you can right click on a group folder, such as where you have a group of QA Servers bundled up, and query that entire group. For example, right click on the folder, select new query, and a query window opens up. Any query done in that window is run against the entire set of servers in that grouping. This type of integration could provide DBA's more power to create even more powerful customized scripts.

Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein

emmchild
emmchild
Valued Member
Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

Group: General Forum Members
Points: 72 Visits: 408
The queries help out alot. It's nice to know that a certain situation is in need of attention before it becomes a big mess. For instance there are times when a service gets hung so an email alert gets sent so the service can be restarted. These types of queries are invaluable when operating in a high volume environment.



paul_congdon
paul_congdon
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 10
How much "checking" I build into my custom systems depends on time and money available to do them. I haven't taken the SQL Agent route, typically I have a Windows service that periodically processes various multi-step business transactions. As part of the cycle of jobs-to-do, I usually place my "checks" code at the end of each processing cycle. My service always uses stored procedures and I usually build a few that will cull my data looking for issues that need to be brought to someone's attention. When something is found, I send off an email to the appropriate personnel (I usually maintain in the database a list of emails tied to types of activities).

Transaction Status
I should also note that I create transaction tables that stores the various transactions within my system. As part of these tables I determine the main steps to complete the transaction and create a status date/time column for each step as part of the same transaction row definition. These columns are defaulted to NULL. I also create a Status Notes column that can contain textual status info. This defaults to "Waiting to be processed". When the process completes a step, it runs a sProc that applies the current date/time to the appropriate status date column and updates the Status Notes column with the results of that step. If a process encounters an error, part of the error handling applies the error info in the Status Notes.

Notifications based upon type of activity
Sometimes the issue is support-related and I'll send it to system support folks. Typically these are business transactions that could not complete and need some human eyes to help it along. Having the Status Notes column greatly assists these folks when they analyze the transaction tables.
Sometimes the issue is business-related, such as manufacturing jobs running late and I need to notify the appropriate business unit manager.

Use of SQL Agent
Many of my clients would not allow their SQL Servers to send email as part of their internal security policies. Some even go so far as to disable SQL Agent. I have to create systems within the confines given to me and if it's a 4' x 6' jail cell, so be it.
Nicole Garris
Nicole Garris
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 143
We review our SQL Servers every morning looking for "Corrupted" next to the database name. We also have alerts set up to e-mail us. We were having some problems with our storage system and found out that a database can become corrupted with no alert being triggered, especially if there's been no activity against it. So we added scheduled jobs to run CheckDB against every database just before we come in to work. In my opinion this is a must, especially for databases that aren't necessarily accessed every minute or every hour.
Brian Kukowski
Brian Kukowski
Mr or Mrs. 500
Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)

Group: General Forum Members
Points: 508 Visits: 1535
Great topic. Something I give a lot of thought to, and always looking for new ideas. In many ways, I think my responsibilities consist in large part of the following:
1. Prevent bad stuff from happening (Protect)
2. If it happens, become aware of it quickly (Detect)
3. Resolve the issues accurately and efficiently (React)

My company has a pretty diverse environment (SQL versions between 6.5 and 2005, multiple domains, both physical and virtual servers). That, in addition to having a very limited budget for DBA tools, has forced me to use a variety of methods. Three main ones come to mind, in decreasing order of automation:

1. SQL Agent jobs for emailing me about really bad conditions (ex: SQL errors with a high severity, xaction log about to run out of space) in real time. I have a job that runs early in the morning to do nothing but send me an email, to verify that both SQL Agent and our email infrastucture are functioning properly.

2. Custom SQL scripts using Red Gate's SQL Multi-Script tool to catch conditions that while important, can usually wait a few hours (ex: disk space getting low, missing backups, SQL Error log entries that the SQL Agent jobs don't report on). I run these scripts a few times throughout the day. Doesn't take too much time...

3. Server-side traces to catch things like unauthorized use of the sa account, poor-performing queries.

It would be great to be notified about everything automatically when it happens. That said, I still think there's many instances where a bit of at least somewhat-manual checks are warranted. The manual checks can act as an audit of the automated checks.

Brian Kukowski
Cade Roux
Cade Roux
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 Visits: 491
Yes, typically, I would have jobs on the various servers in Agent and they would normally email (we used SQLAnswersMail on SQL Server 2000 because it has nice HTML formatting and allows you to a lot of flexibility).

The jobs would email the appropriate people when they detected problems. Some would be daily, weekly or monthly. The emails were always designed to go to the people who would fix the problem and be sent on behalf of the people who could answer any questions (sometimes these are the same person!)). Preferably the bad data was a link to a web based application's maintenance page where the problem could be corrected.

I always liked to ensure that the addresses for the recipients were either a mailing list or stored in a table (or sometimes the email address was extracted from the system - like the person who was the rep for the customer) so that the job never needed to be changed for simple job assignment changes. In cases wherre the rep for a customer had to do something, there was a fallback so that if the rep's email address was NULL (or the rep was terminated), it would go to the department head (and then, if that wasn't available, to a DBA). So even the jobs were anticipating problems with the integrity of their email configuration, to some extent.
Cade Roux
Cade Roux
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 Visits: 491
And another thing - being proactive, I would never do this stuff twice. If I had to investigate a problem (and couldn't easily prevent it with a CONSTRAINT or something), at the very least, I would set up a job to email me about it - even putting the investigatory SQL I would need to figure it out in the email. The next time that job detected the problem, I would ramp it up into a full-blown system (based on that SQL) to identify who should be fixing these problems and give them as much information as they needed to get it fixed.
David Korb
David Korb
SSC-Enthusiastic
SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)

Group: General Forum Members
Points: 145 Visits: 311
We maintain a database called the Data Quality Engine (DQE) into which an operator can insert various kinds of tests that target data either on our SQL Server or Oracle systems. Most tests are run in a daily job and those results that fall outside certain boundaries are added to an email alert.
Naked Ape
Naked Ape
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 703
We have a fairly large Access application with a SQL2000 back-end (about 120 users) which is one of our core systems - local government to manage properties, rates, and all sorts of applications (building consents, resource consents, etc.).

It's quite a configurable application, which is great in allowing us to configure it for all sorts of processes, but it's not so good at enforcing data integrity or ensuring users follow proper processes.

We have created a wide variety of audit scripts (I think we're up to about 200 now) that check all sorts of data entry, relationships, etc. Each can be scheduled to run daily/weekly/monthly, on any given day, fire emails back to the users to notify them to fix the data, and escalate the issues if they are left unresolved.

I'm sure there are plenty of tools around to do this, but we just created a simple DTS package with a couple of control tables and a series of scripts. Saves us heaps of time and helps identify repeat offenders, who might need further training, or just a damn good thrashing.

Chris
Mad Hacker
Mad Hacker
Right there with Babe
Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)

Group: General Forum Members
Points: 746 Visits: 414
I regularly write SQL queries to check for certain conditions in my data. We've developed a system used to automate the calculation of Teacher Bonuses that are based on Student Assessment Scores. The system basically is dependent on the right data being in the right place at the right time, which doesn't always happen. Therefore, we use a number of SQL queries to identify conditions such as mismatched data or missing critical data. Although the development of the queries consumed a lot of time, the queries continue to pay great dividends when used in a proactive manner.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search