Archive | SQL Server 2000 RSS feed for this section

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

SQL 2000 Server Wide Configurations

Pinal Dave asks a question about server-wide configuration values regarding 2005 and 2008, but I thought I would add the ones for 2000 in case noone else did. Hopefully I got the pertinent info.

SQL Server 2000 Sys Configures

SQL Server 2000 Sys Configures

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

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

Ebooks, Twitter, and Contacts

I was checking out a new contact I made on Twitter, which I was convinced I should use by Brent Ozar, and Marlon’s post on a free eBook made me think back to some other great finds that I’ve seen on Red Gate. If someone thinks of any others please feel free to post.

Best of, Vol. 1

Best of Vol. 5 and 6
You can also download 3 good ebooks from Brad @ Red Gate.. Brad’s Sure Guide to SQL Server 2008, Brad McGehee’s DBA Best Practices, How to Become an Exceptional DBA.

“Dissecting SQL Server Execution Plans”
by Grant Fritchey

I’m sure I”m missing some links that I had, but I’ll update if I find them. I hope noone at Red Gate has a problem with me posting them like that because they should drive people to try the products.

Comments { 4 } Posted on February 14, 2009 in Education, SQL Server 2000

SqlSecurityHandler is invoked

I had an admin come to me this morning regarding a SQL Server box that had suddenly gone down. The SQL Service and SQL Agent where both stopped. When I looked at the SQL Server error log I noticed the following error: “SqlSecurityHandler is invoked. Potential buffer overrun detected – server is terminating.” I went to the trusty google box and didn’t find a ton of useful info, but I did finally come across a KB from Microsoft.

This SQL Server is handling a third-party database whose developers have decided it’s ok to use “SELECT *” in many of their queries. They have also opted to not use stored procedures alone. Both of which I think are bad ideas. They have deadlocks that pop up all of the time, and I see many holes in their reasoning but there’s not a ton I can do to change any of that. They’ve never tested under the load that they are getting with us, so I believe they are learning the hard way that their product might not be as scalable as they thought.

Anyway, I’ve run many a trace against the box through perfmon, profiler and DBCC TRACEON without incidence, but it turns out that it’s possible to crash the server when the following criteria are met:

“The SQL Server process may end unexpectedly when you turn on trace flag -T1204 and a profiler trace is capturing the Lock:DeadLock Chain event in SQL Server 2000 SP4”. We were able to determine that the vendor had been running a profiler trace capturing that event during the time that this happened.  The server was patched up to 2040, but obviously this hotfix is supposed to fix this issue. I decided this would be a good time to go ahead and bring it up to hotfix 2273 given the problems that we’ve had. That means I chose to bypass 2050 and go with 2273. That means I chose QFE over GDR. I’ve included a link to an explanation that helped me understand those two items. Normally, it would be recommended that you stay with the GDR unless you need a specific fix above that hotfix. At least that’s the way I understood it.

Cumulative list of SQL Server 2000 hotfixes


Microsoft Security Bulletin MS08-040

Comments { 0 } Posted on February 12, 2009 in SQL Server 2000