Hello there!

I thought today I would share with you the documentation header that I use for my T-SQL scripts. The header has evolved from the days of just the name of the script, the author and the date. Through my years of experience I have made the header more descriptive and always looking to improve it. The header has saved me lots of time from searching through my T-SQL code to see if a particular stored procedure is called, user defined function, etc. Here is the documentation header I use:

/*
**********************************************************************
**
** Object Name: < Object Name>
** Object Type: < Stored Procedure, User Defined Function, etc. >
** File Name: < Name of the file.. normally followed by .sql >
** Database Name: < The name of the database the object will reside in >
** Author: < Name of the person who wrote the T-SQL script >
** Creation Date: < Date the T-SQL script was created with ANSI date >
** Description: < Detailed description of what the script does >
** Special Note: < Any special remarks about the script >
** Modified Tables: < List tables that the data is modified by this script >
** Called Procedures < List procedures that this script calls >
** Called Jobs: < List SQL Agent jobs that this script calls >
** Called UDFs: < List user defined functions that this script calls >
** Calling Procedures: < List procedures that call this script >
** Calling Jobs: < List SQL Agent jobs that call this script >
** Calling UDFs: < List User Defined Functions that call this script >
** Return Statuses: < List return status that this script returns >
**
**********************************************************************
*/

If you would like to suggest adding something more to the document header please comment!

Hello and welcome back!

Today I am going to cover how easily it is to verify the version number, service pack and edition that is installed on SQL Server 2005. When you run the following T-SQL statement it will give you the Product Version, Product Level and the Edition.

Step 1 - Connect to a valid SQL Server 2005 server.

Step 2 - After connecting, open up a query window. It does not matter what database is specified when opening up the query window.

Step 3 - Type and execute the following T-SQL statement:

SELECT SERVERPROPERTY(‘productversion’) AS [Product Version], SERVERPROPERTY (‘productlevel’) AS [Product Level], SERVERPROPERTY (‘edition’) AS [Edition]

This will give you the detailed information you are looking for. If you have another way of identifying this information please post your solution!

Hello once again… today I am covering how to remove transactions that are marked for replication in the transaction log of a restored database. Now I have seen this occur in SQL Server 2000 so many times that I have created a T-SQL script that I execute step by step to ‘unmark’ these transactions so that I am able to shrink the log of the restored database.

Here is the scenario… there is a production database that is involved in replication and the developers want to restore the backup of this database to a development server to develop against. This database will no longer participate in replication while residing on the development server. Once the production database gets restored to the development server the database is still ‘marked’ for replication as well as the transactions that were in the log at the time of the full database backup. Now in order to conserve space on the development server the log needs to be shrunken down. When a DBCC ShrinkDatabase or DBCC ShrinkFile is executed on the database the transaction log does not truncate and the log does not shrink. This is what we do to fix this…

Step 1 - Make sure that the Recovery Model for the restored database is set to ‘Full’.

Step 2 - Make sure in the master.dbo.sysdatabases table (on the development server) that the category column for the restored database is set to 1 (the database is marked for replication).

Step 3 - Run the following T-SQL script in the restored database:

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1

This will ‘unmark’ the transactions (for replication) in the log

Step 4 - Backup the Log

BACKUP LOG <database name> WITH NO_LOG

Step 5 - Shrink the log

USE <database name> DBCC SHRINKFILE (N’<database name>_log’)

The log will now be down to a size reasonable size. To keep your log small on the development server, set the Recovery Model to ‘Simple’. Let me know how this process works for you!

Welcome back to squirrel’s blog on “everything” SQL Server. I will be covering little nuances with Database Mirroring here. Database Mirroring is a very cool feature in SQL Server 2005 when you get it setup and configured properly. I went through the growing pains of installing, configuring and troubleshooting this process.

Before I installed it on my production database servers I used an application called VM Workstation - http://www.vmware.com. With this cool software I was able to create the production SQL Servers (here you will need the Windows and SQL Server ISOs) but in a virtual form. My production database servers have Windows 2003 Server and SQL Server 2005. Once I got 3 (Principal server, Witness server, Mirror server) virtual machines\servers setup I went ahead and installed and configured Database Mirroring. Import thing to note… you will need to install SQL Server 2005 Service Pack 1 or later for database mirroring to work. Database Mirroring is disabled by default.

When setting up the servers to participate in the mirroring process it is import to fully qualify the server name e.g. mycompname.mydomainname in order for Database Mirroring to see the servers. I tried it using the IP Addresses but Database Mirroring did not like that. Also, fortunate for me all of my servers were in the same domain. If anyone has experience going across different domains please share your experience. Last thing to note on Database Mirroring… when using a Witness server if there is a ‘hick up’ in the network the Witness will view that as a failure in the communication between the Principal and Mirror servers and will automatically “fail” it over to the mirror server. For this reason, I have left the Witness server out of the configuration and just have the Principal server and the Mirror server. If there is a production problem then you can fail it over manually.

For more information on Database Mirroring please see BOL.

Hello..

Welcome to my blog… this blog is dedicated to providing information that is not found in your standard SQL Server books. Through my years of experience I have collected numerous tips and tricks to help get through those rough times when trying to get a project pushed into production. This blog will discuss tips and tricks from SQL Server 7.0 through SQL Server 2005.

As I provide these tips and tricks I will point out which version(s) of SQL Server they will work on. I invite you the reader to comment and perhaps donate tips and tricks from your own experience. I like to call it… SQL Server Tips and Tricks from the field.