aaron weiker on sql

SQLProgrammer.org

aweiker — Sun, 23 Jan 2005 16:51:00 GMT

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.

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

aweiker — Wed, 31 Mar 2004 14:34:00 GMT

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

Reporting Services Question

aweiker — Wed, 31 Mar 2004 14:24:00 GMT

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.

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

aweiker — Wed, 24 Mar 2004 17:48:00 GMT

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.

Password Generator

aweiker — Mon, 22 Mar 2004 13:56:00 GMT

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.

New Category

aweiker — Fri, 19 Mar 2004 14:46:00 GMT

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.

Running AntiVirus software on SQL Server

aweiker — Fri, 19 Mar 2004 14:18:00 GMT

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

Links for working with the Transaction Log

aweiker — Thu, 18 Mar 2004 00:12:00 GMT

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

Email notification for failed jobs

aweiker — Tue, 16 Mar 2004 14:42:00 GMT

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

Subquery Phobia

aweiker — Fri, 12 Mar 2004 16:46:00 GMT

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.

How do you learn?

aweiker — Fri, 12 Mar 2004 04:16:00 GMT

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?

Thought of the day

aweiker — Tue, 09 Mar 2004 23:37:00 GMT

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?

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

aweiker — Mon, 08 Mar 2004 13:04:00 GMT

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.

Am I secure enough?

aweiker — Sun, 07 Mar 2004 23:57:00 GMT

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.

Setting up Least Privileges in SQL Server

aweiker — Sun, 07 Mar 2004 23:44:00 GMT

This has been moved to sqlprogrammer.org.