aaron weiker on sql

SQLProgrammer.org

Just wanted to let everyone know here that I just put up a new website dedicated to SQL Server and .NET called SQLProgrammer.org. Currently on this website I have a list of Frequently Asked Questions, Links to external resources, and forums.

Sponsor

Posted Sunday, January 23, 2005 11:51 AM by aweiker | 51 Comments

Filed under: SQL

Transaction Log 101 (What you don't want to know, but should)

If you haven't had a chance to dig into the transaction log yet, allow me to get your feet wet. The following is a basic regurgitation of what I feel is important to know when dealing with the transaction log. Feel free to point out things that you feel I should have addressed and in my next rendition I will be sure to add them. If you think that something doesn't make sense or is inaccurate, please let me know so that I don't spread misconceptions and bad karma.

Every transaction that modifies a database is logged in the transaction log. This includes inserts, updates, deletes, and create/alter/drop tables. The transaction log stores a before and after copy of the data that was modified. When a transaction completes it is not guaranteed to be written to disk, instead it just means that the transaction has been completely logged to the transaction log. In order for data to be written to disk a checkpoint must occur. A checkpoint occurs where there are enough dirty pages in memory to reach the recovery interval. The recovery interval is the length in time in which it would take to roll all of the transactions forward which have not been written to the data file yet. By default SQL Server 2000 automatically manages the recovery interval and uses a small amount of time. When a checkpoint occurs it will write all of the dirty pages in memory to disk that are not currently taking part in an active transaction. On some systems you may incur a performance penalty if this happens too frequently.

If left unattended the transaction log can grow to be quite large, this is especially true on systems in which there are lots of writes. The ideal way to manage the transaction log is to make regular full backups as well as transaction log backups in between the full backups. During the backup the transaction log will get truncated except for all of the active portions (have not been written to the data file). It is also possibly to manually truncate the transaction log using BACKUP LOG [DATABASE NAME] WITH TRUNCATE_ONLY. When this happens you will only be able to restore from the last full backup. This is similar to using the Simple recovery model.

The transaction log has three modes of operation, Full, Bulk-Logged, and Simple. These are referred to as Recovery Models. When using Full Recovery you have the ability to restore the database to any point in time. This is because all transactions are logged to the transaction log. This also means that you need to make sure you perform regular backups. If you perform a lot of high volume statements then it may be beneficial for you to use the Bulk-Logged Recovery model. Using this model it won't log full details about the bulk operations. You will want to make sure you perform regular backups in order to minimize possible loss of data in case of system failure. The final option is Simple Recovery. Using this model once a checkpoint occurs the data that has been committed is removed from the transaction log. Using this option only allows us to restore up to the latest full backup.

References:

Transaction Log Architecture (BOL)
SQL Server 2000: Performance Tuning (Microsoft Press) - Chapter 2: Architecture Fundamentals
SQL Server 2000: Resource Kit (Microsoft Press) - Chapter 9: Storage Engine Enhancements

Sponsor

Posted Wednesday, March 31, 2004 10:34 AM by aweiker | 67 Comments

Filed under: SQL

Reporting Services Question

I was recently asked a few questions on how to do aggregate functions using Reporting Services (Reporting Services Documentation). While answering these questions I have had a chance to really dig into the report designer and am much more comfortable and excited about what you can do. I'm hoping to put some examples online that I have done on how to do certain things, but in the meantime I will try to at least put the questions I get asked here. Also, if you have questions feel free to leave them in the feedback and I will do my best to get you the most accurate answer, or redirect you to somewhere that has the answer already.

Sponsor

Posted Wednesday, March 31, 2004 10:24 AM by aweiker | 0 Comments

Filed under: SQL

Making a .NET assembly always appear in Visual Studio.NET

To display your assembly in the Add Reference dialog box, you can add a registry key, such as the following, which points to the location of the assembly
[HKEY_CURRENT_USER\SOFTWARE\Microsoft\.NETFramework\AssemblyFolders\MyAssemblies]@="C:\\MyAssemblies"

NOTE: You can create the this registry entry under the HKEY_LOCAL_MACHINE hive. This will change the setting for all of the users on the system. If you create this registry entry under HKEY_CURRENT_USER, this entry will affect the setting for only the current user.

Sponsor

Posted Wednesday, March 24, 2004 1:48 PM by aweiker | 50 Comments

Filed under: FAQ, Programming

Password Generator

I came across this web page and find it extremely useful any time that I need to get a random password made. I also have some code that I use for generating random passwords as well which I have posted in this article.

Sponsor

Posted Monday, March 22, 2004 9:56 AM by aweiker | 58 Comments

Filed under: FAQ

New Category

I just added a new category named “FAQ”. This is where I'm going to be puting things that I see asked over and over again. I'm also going to update the historical posts with this category where they apply. So for you people reading this in an agregator and it says that an old post is now updated, sorry. I just want things to be easily found.

Also, if you have things you want put in here, just leave me feedback or contact me.

Sponsor

Posted Friday, March 19, 2004 10:46 AM by aweiker | 46 Comments

Running AntiVirus software on SQL Server

I've seen this asked a few dozen times so I thougth I would put a link here so that I remember where I put it.

INF: Consideration for a Virus Scanner on a Computer That Is Running SQL Server

Sponsor

Posted Friday, March 19, 2004 10:18 AM by aweiker | 1 Comments

Filed under: SQL, FAQ

Links for working with the Transaction Log

Here are some good references on how to take care of your Transaction Log

Sponsor

Posted Wednesday, March 17, 2004 8:12 PM by aweiker | 0 Comments

Filed under: SQL, FAQ

Email notification for failed jobs

As you may or may not know, you can set up SQL Server Agent to email you whenever a job fails. This however requires SQL Mail to be properly working. Again, as you have probably experience this is a lot easier said than done. What I have ended up doing is using XPSMTP for sending email notifications to me whenever a job fails. This does require a little bit of extra work in the job definition. You must create a new step to be run only if that job step fails, this however isn't much of a problem if you don't have a lot of jobs defined. Below is the procedure that I use for sending out the email. This procedure requires you to send in the job_id, which you will need to pull out of the msdb.dbo.sysjobs[job_id]. For more information about the system tables Dave's last post.

/*
 * BE SURE TO CHANGE THE MAIL SERVER
 * AND EMAIL ADDRESSES TO THE ADDRESSES
 * YOU WANT THE NOTIFICATIONS TO BE
 * DELIVERED TO.
 *
 * IF YOU DO NOT CHANGE THE ADDRESSES
 * YOU WILL END UP TELLING ME ALL OF YOUR
 * DEEPEST DARKEST SECRETS!!!! ;)
 * 
 */

CREATE PROCEDURE dbo.sp_SendJobAlert
 @job_id UNIQUEIDENTIFIER
AS

DECLARE @FROM NVARCHAR(4000),
  @FROM_NAME NVARCHAR(4000),
  @TO NVARCHAR(4000),
  @Subject VARCHAR(4000),
  @Message VARCHAR(4000),
  @priority NVARCHAR(10),
  @rc INT

SELECT TOP 1
  @subject = '[' + hist.server + '] JOB FAILED: ' + hist.step_name,
  @message = hist.message
FROM msdb.dbo.sysjobhistory hist
WHERE job_id = @job_id AND step_id <> 0 AND run_status = 0
ORDER BY instance_id DESC

SELECT @FROM = N'sql@aaronweiker.com',
  @FROM_NAME = N'SQL Server',
  @TO = N'alerts@aaronweiker.com',
  @priority = N'HIGH'

EXEC @rc = master.dbo.xp_smtp_sendmail
  @FROM = @FROM,
  @TO = @TO,
  @priority = @priority,
  @subject = @subject,
  @message = @message,
  @server = N'mail.aaronweiker.com


RETURN @rc

Sponsor

Posted Tuesday, March 16, 2004 10:42 AM by aweiker | 49 Comments

Filed under: SQL

Subquery Phobia

I have this habit of never using Sub-Queries in any of my SQL Statements. I'm not sure why I started this, but it is one of those things I avoid at all costs.

Is this fear justified? Or do you think I'm making a big deal out of something that I don't need to?

Do you use subqueries? If so when and why?

Also keep in mind that I'm usually working with procedures that need to respond in subsecond times. This means anything that takes longer than 500ms is usually going to get re-written until it only takes 10-20ms.

Sponsor

Posted Friday, March 12, 2004 11:46 AM by aweiker | 73 Comments

Filed under: SQL

How do you learn?

I enjoy learning new things and as such I constantly become aware of how much I don't know. The more I learn the dumber I feel. (OK, maybe that's a slight exaggeration, but you get the point). Typically the way I learn is through reading books and some magazines. On of my all time favorite books for SQL is Inside SQL Server.

My question to all of you is, how do you learn? Is there anything I can do that would help you?

One of the reasons I wanted a blog here was to provide a way to give back to the community and share some of the knowledge I have gained. So here's your chance, what do you want to see more information on?

Sponsor

Posted Thursday, March 11, 2004 11:16 PM by aweiker | 0 Comments

Filed under: SQL

Thought of the day

Just finished reading a post that Dave sent to the aspnet-database list and I began pondering the following question.

When was the last time you programmed to an interface instead of an object?

So tell me, what's your answer?

Sponsor

Posted Tuesday, March 09, 2004 6:37 PM by aweiker | 54 Comments

Filed under: Programming

Bugs, why won't they just go away!!

One of the goals we have as programmers is to write code that does not have any bugs. However; since none of us are perfect, we are going to fail. Does this mean we should stop trying? Of course not.

So how do we go about trying to reach this impossible goal? The quickest and easiest way I can know of is to make your code as readable as possible, not just to yourself but more importantly to the person that is going to be coming back to fix the bugs.

So how do I make my code more readable? You must first establish what your coding standard is, and then you need to practice the art of refactoring. Combine this with smart testing and some forethought and you will be on your way to easier, more efficient code writing.

Sponsor

Posted Monday, March 08, 2004 8:04 AM by aweiker | 48 Comments

Filed under: Programming

Am I secure enough?

It is my firm belief that any server needs to be properly secured. But how do you check to see if you have done all of the right things? You shouldn't just hope that you have done everything, instead you need to be proactive. First of all, get the Microsoft Baseline Security Analyzer (MBSA). Running this is your first real sanity check. From there you need to make sure that you have a good firewall in place and someone managing that firewall that knows how that firewall works. A firewall is no good if it is not properly managed and kept up to date. Now, there are probably a lot of other things that you can and should do, but I'm not going to go into those in depth right now. The bottom line is that if you are not 100% confident in what you have, ask for help. There are a lot of companies that are willing to help you properly secure your network and all you have to do is give them a call. Some of them will even help you properly plan your network without charging a dime as they just want you to buy their hardware and/or software. Do your research though, don't just go with someone because they cost the least. Remember the old adage, you get what you pay for. Security is not one of those things you should skimp on. Having a secure network is like having Johnnie Cochran on retainer.

Sponsor

Posted Sunday, March 07, 2004 6:57 PM by aweiker | 161 Comments

Filed under: SQL

Setting up Least Privileges in SQL Server

This has been moved to sqlprogrammer.org.

Sponsor

Posted Sunday, March 07, 2004 6:44 PM by aweiker | 51 Comments

Filed under: SQL, FAQ