Archive | SQL Server 2005 RSS feed for this section

Execute SQL Task too big

I ran into an issue last night where copying selected text into the Execute SQL Task within SSIS would get truncated at a certain point. I really believe that I’ve successfully pasted larger text without an issue before, but I haven’t tested that theory as of yet.

One fix is to save the file as .sql and browse to it from within the Execute SQL task. Once I tried this I noticed that my last few lines were finally there. If you know of other ways around this please feel free to share.

Comments { 2 } Posted on March 10, 2009 in SQL Server 2005, SQLServerPedia Syndication, SSIS

Install SQL Server from network

Whenever it comes time to install SQL Server to a server it’s usually not a big deal to walk back to the server with the disc or share my drive across the network. I ran into a different situation this weekend when I needed to install several versions on a server while on a vpn connection from home. I can’t switch the discs when needed because I’m over 30  minutes away so I needed to come up with a better way.

The problem arises when I have to deal with a .iso file. I needed a way to get the contents out of the iso and into a folder that I could use for a network install. After a small amount of searching I found that I had a few options. The option from Microsoft (Virtual CD Rom) didn’t play nice with the server permissions. I finally got ISOBuster to work for my needs.

Once I extracted the .iso file to a folder for each of the cd/dvds then it was a very easy process. I’m sure there are multiple ways to do this, but given my situation of being remote and only having the .iso files the solution I found seemed to do the trick.

Comments { 0 } Posted on March 8, 2009 in SQL Server 2000, SQL Server 2005

Tempdb needs a doctor

In the past, we’ve run into issues with contention and the -T1118 flag, hotfix 2040, and multiple tempdb files that led me to go ahead and put this info out there if nothing more than for my future reference.

I ran into a machine today that had the tempdb files in the wrong location. They were also the wrong size and there were not enough of them.

So that presents three problems that had to be solved. The first problem I decided to fix was to get rid of the current files that were sized inappropriately and in the wrong location. No need to double my work.

I’m sure it can just as easily be done by script, but I went into the GUI and deleted each tempdb data file that I didn’t want anymore for the time being. You will get an error message about deleting the file and restarting SQL Server. Once you’ve done that you are left with only one data file and one temp file, even though they may be in the wrong location and the wrong size.

Let’s go ahead and tackle the size issue first. After we’ve just restarted SQL Server lets do something like this:

dbcc shrinkfile (‘tempdev’,1024,TRUNCATEONLY)

Now it’s time to go ahead and move the files to the place we want them:

USE master
MODIFY FILE (NAME = tempdev, FILENAME = ‘I:\tempdb.mdf’)
MODIFY FILE (NAME = templog, FILENAME = ‘I:\templog.ldf’)

Now it’s time to add the additional tempdb data files:
USE [master]
ALTER DATABASE [tempdb] ADD FILE ( NAME = N’tempdev2′,
FILENAME = N’I:\tempdb2.ndf’ , SIZE = 1048576KB , FILEGROWTH = 204800KB )
ALTER DATABASE [tempdb] ADD FILE ( NAME = N’tempdev3′,
FILENAME = N’I:\tempdb3.ndf’ , SIZE = 1048576KB , FILEGROWTH = 204800KB )
ALTER DATABASE [tempdb] ADD FILE ( NAME = N’tempdev4′,
FILENAME = N’I:\tempdb4.ndf’ , SIZE = 1048576KB , FILEGROWTH = 204800KB )
ALTER DATABASE [tempdb] ADD FILE ( NAME = N’tempdev5′,
FILENAME = N’I:\tempdb5.ndf’ , SIZE = 1048576KB , FILEGROWTH = 204800KB )
ALTER DATABASE [tempdb] ADD FILE ( NAME = N’tempdev6′,
FILENAME = N’I:\tempdb6.ndf’ , SIZE = 1048576KB , FILEGROWTH = 204800KB )
ALTER DATABASE [tempdb] ADD FILE ( NAME = N’tempdev7′,
FILENAME = N’I:\tempdb7.ndf’ , SIZE = 1048576KB , FILEGROWTH = 204800KB )
ALTER DATABASE [tempdb] ADD FILE ( NAME = N’tempdev8′,
FILENAME = N’I:\tempdb8.ndf’ , SIZE = 1048576KB , FILEGROWTH = 204800KB )

This will create 8 files (for our # of processors) that are 1024MB in size and autogrow at 200MB. At this point it probably wouldn’t hurt to restart the SQL Server. We had a few small issues but I’m not sure they were related.

The links that helped me with this are as follows:

Comments { 2 } Posted on February 18, 2009 in SQL Server 2000, SQL Server 2005, SQLServerPedia Syndication

Which SQL Server version?

From my understanding, it is only necessary for me to discuss SQL Server versions later than SQL Server 2000. I include SQL Server 2000 because the truth is that many people are still using this product. I know that the shop I work in has mostly SQL Server 2000 instances and a couple of SQL Server 2005 so that is where I’m coming from. Some of the consideration may be out of your control because your shop may not have a SQL Server 2000 server. I hope to present the pros and cons that go with the order in which you choose to begin your journey. I also hope that others will add their opinion to back me up or correct me.

SQL Server 2008
If you aren’t working with a certain SQL Server version at your workplace then I would think that going with SQL Server 2008 would be your best option. It’s the latest and greatest and you will give yourself the most shelf-life for your effort. SQL Server 2008 was released in August of 2008 and you can see a build list here. One negative I noticed is that some of the books (MCTS Self-Paced Training Kit (Exam 70-432): Microsoft® SQL Server® 2008 Implementation and Maintenance (PRO-Certification) are still being finalized for this version at the time of this writing. At the bare minimum, I would recommend becoming familiar with this version and go grab the Express Edition.

SQL Server 2005
You still may want to look at this version first if you are breaking into the field. There are tons of resources for SQL Server 2005 and you can focus on laying the ground work for a successful base to your knowledge. SQL Server 2008 does have new features, but I am not sure they are make or break for the beginner DBA. Personally, I have only gone as far as touching SQL Server 2005 and 2000. I am in the process of beginning to test SQL Server 2008, but since it’s not deployed in my work environment I feel that I gain more by focusing on maintaining and upgrading from 2000 to 2005. The amount of resources available to someone starting out with 2005 will be far greater than 2008 at the time of this writing, but there are many variables to add up when making your decision and I can’t account for all of them. One negative aspect that I saw last night would be finding a development version of SQL Server 2005 to start your test lab with. Amazon did not have any copies in stock so you would have to find a reseller if you wanted to use more advanced features that weren’t available in the Express Edition. SQL Server 2005 is up to SP3 so you are past the initial growing pains that come with a product without a service pack. You can grab the Express Edition here.

SQL Server 2000
Support for this product is only available through extended support. If you are currently in a shop that primarily uses SQL Server 2000 then you first step should be learning how to help maintain what is already there. If you are going to be working with a product then it does not make sense to me to only prepare for the future. This is the situation that I inherited so I can speak more clearly on it than the others. When this package fell into my lap we were only using SQL Server 2000. Even though the product life cycle is basically expired, the product still has to be managed by someone. Given the current economy, there’s a pretty good chance that budgets are frozen and upgrades to SQL Server 2005 may not be possible. This does not mean you should not prepare for the future, but it does mean you better become an expert on what your shop is using right now if you want to be the “go-to-guy” where you are at. There are tons of resources out there for SQL Server 2000, and that has helped me a great deal when it came to becomming the person who wants to answer the questions. If your shop is currently using SQL Server 2000 then you should have access to the necessary materials to build a good test lab to begin the learning process. I made the choice to go ahead and go with the 2005 certification track since that is the direction we are going, but I still needed to know how to do the same things in 2000. Having a certification of MCITP Database Administrator in SQL Server 2005 helps give those around me the confidence that I am able to facilitate an upgrade from SQL Server 2000 to 2005.

The SQL Server version you choose to go with definitely depends on variables that only you know. Whether or not you actually get to touch the SQL Servers at your workplace also play a huge role in what kind of decision you make. I would still download an Express Edition copy of 2005 and 2008 in order to get your feet wet, but you have to focus on what you feel will give you the edge where you work or where you would like to work. It all goes back to establishing what your goal is and then taking action to make it happen. If you want to become a SQL DBA where you work then you have to know if there’s even a possibility of that happening before you expend too much energy going down that path. Pick a target and set some goals to try and hit it. If you never set them then you’ll miss it every time. And by all means, if you want different results then you have to change what you are doing.

Comments { 0 } Posted on February 17, 2009 in SQL Server 2000, SQL Server 2005, SQL Server 2008