Everyone knows, I hope, that it is usually a best practice to have your database log file on a different drive from the data file. But what if you have a situation, where originally, the database was created with both the log and the data file being on the same drive? How do you move the log file to a new location? Here is what you do:

Step 1: Make sure nobody is using the database at the moment. (This whole procedure is better done off business hours).

Step 2: Do a full database backup. (This is not an absolutely required step, but is ALWAYS a good idea before you do something that is somewhat dangerous).

Step 3: After you backup the database, I would shrink the log file so when you move it, it doesn't take forever. (This is also not a required step, and shouldn't be really done if your log file is not huge). If you do decide to perform this step, here is the syntax: DBCC Shrinkfile ('yourDB','1');    Note: the second parameter here is the size in kilobytes to which you want to shrink your log.

Step 4: Detach your database by using this syntax:

USE master
GO
EXEC sp_detach_db @dbname=N'yourDB';
GO

Step 4: However you want, copy the actual yourDB_log.ldf file to whatever location you want. Say for this example, we'll move it to the root of the D drive.

Step 5: Attach your database, and specify the new file locations.

USE master
GO
EXEC sp_attach_db @dbname = N'yourDB',
@filename1 = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\yourDB_Data.mdf',
@filename2 = N'D:\yourDB_log.ldf';
GO

So I was doing a lecture on sql server query optimization at UCLA yesterday, and one of the topics I was covering was statiscs. As we know, statistics is information that the sql server collects about the distribution of data in columns and indexes, and they are used by the query optimizer to determine the best execution plan. If the database option to update statistics automatically is on, which is the default, statistics will be updated as soon as the need is discovered. In general, if your table has over 500 rows in it, and at least 500 plus 20% of the rowcount rows have been modified, statistics are out of date. Therefore, when that happens, and a user does a select, sql server will first update the statistics to get the best execution plan, and then work on returning your query results. While that sounds good in theory, in practice, it kills your query performance because wating for statistics to update when you're trying to get data returned to your application is very costly. I talked about being proactive in updating statistics with good maintenance plans, etc. I also talked about the possibility of turning on the option of updating statistics asynchronously on the database. What that will do, is alow your query to return data using the old execution plan, while the statistics are updating on the different thread.

As I was discussing these issues, a great question came up. Is updating statistics sufficient to make sure that our stored procedures use the latest and greatest execution plan? The answer is, not necessarily. Before SQL Server 2005, the execution plan for a stored procedure was saved during initial compile, and if you changed your table structure or added new indexes to a table after that compile, you would have to make sure to recompile your stored procedure in order for it to generate a new and better execution plan. Since the 2005 version, there has been a slight improvement to when the optimization of your procedure's execution plan occurs. Now, the optimization happens automatically when the first time a stored procedure is executed right after SQL Server services are restarted. It will also happen automatically if an underlying table used by the stored procedure changes. However, if you add a new index from which a stored procedure might benefit, and you do not want to wait till the SQL Server is restarted for your stored procedure to reap the benefit of your new index. In this case, you might want to force the stored procedure to recompile the next time it executes, and for that you can use the sp_recompile system stored procedure.

I have two presentations coming at Cal State Fullerton Code Camp on January 25th.
One is an overview of animation techniques in WPF and Silverlight. This is one of my better presentations. It provides a good overview of animation capabilities of WPF. Find out more details here.
The other is an overview of the new cool capabilities of SQL server 2008 one can benefit from immediately. This is intended for developers and DBAs. Find out more details here.

I will be a proctor for a 3:00 hour training session on Silverlight 2.0 this January 15th at QuickStart Facilities in Irvine from 6:00PM to 9:00PM. The session was full in less than an hour after announcement! This is a great MSDN event since everyone will be provided with a virtual machine to get hands on experience developing code for Silverlight. I am working with Microsoft and other OC.NET user group folks to see if we can repeat the session sooner than later. Lynn Langit of Microsoft is working hard to arrange at least two more presentations in Irvine and San Diego within the next two months. If possible, I will be arranging earlier sessions here in Orange County. Additional details can be found here.

The shoemaker's children go barefoot. That’s certainly been our case here at HarborObjects. While in business for 20 years and a bunch of great knowledgeable engineers on staff, our very own blog engine is the last one to get a new skin. We simply figured why not start a fresh one with the New Year. This is hopefully the start of many useful posts by the guys to help others cut down their debugging and development time. Migrating all the old posts onto this engine is the next item for me to attack!