Voice of the DBA

Tag: administration

  • SQL Server Needs Tempdb

    I saw a post recently where someone noted they had moved tempdb like this:

    USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'c:\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'c:\templog.ldf')

    This gives you the message the tempdb has been moved, and it will take effect on restart. If you restart, however, you’ll often find the service doesn’t restart and you get a “network error” when you try to connect. This is because the service is down.

    If you check the error log, you might see this:

    2013-07-05 13:20:48.65 spid9s      Clearing tempdb database.

    2013-07-05 13:20:48.65 spid9s      Error: 5123, Severity: 16, State: 1.

    2013-07-05 13:20:48.65 spid9s      CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file ‘c:\tempdb.mdf’.

    2013-07-05 13:20:48.65 spid9s      Error: 17204, Severity: 16, State: 1.

    2013-07-05 13:20:48.65 spid9s      FCB::Open failed: Could not open file c:\tempdb.mdf for file number 1.  OS error: 2(The system cannot find the file specified.).

    2013-07-05 13:20:48.65 spid9s      Error: 5120, Severity: 16, State: 101.

    2013-07-05 13:20:48.65 spid9s      Unable to open the physical file "c:\tempdb.mdf". Operating system error 2: "2(The system cannot find the file specified.)".

    2013-07-05 13:20:48.65 spid9s      Error: 1802, Severity: 16, State: 4.

    2013-07-05 13:20:48.65 spid9s      CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

    You might also see an operating system error 5 (access is denied) if the files exist. In all likelihood, the problem is security for your service account. The SQL Server service account shouldn’t have rights to all folders and files on the system. If it does, you’re doing something wrong.

    I had thought (incorrectly) that SQL might start, but be in a read-only state without tempdb. However Gail Shaw pointed out this was incorrect, and when I tested this, she was right. SQL Server won’t start.

    What can you do?

    A few options here.

    • The brute force approach
    • The more elegant approach

    The more elegant approach is specified in Books Online, in Move System Databases. In the failure recovery procedure, you start SQL Server with Trace Flag 3608, issue the alter commands, and then restart the instance without the trace flag.

    The brute force approach, which I have tested, is to move the tempdb files to this location (they didn’t exist in my situation). You’ll need admin permissions to do this. You can then change the service account to one with permissions to see the files (like an admin), and restart the instance. From there, connect, and issue the ALTER DATABASE commands as shown above, with the correct path.

    If you need help configuring permissions, use this article.

  • T-SQL Tuesday #44–Second Chances

    tsqltuesdayIt’s T-SQL Tuesday time again. This is the monthly blog party, started by Adam Machanic. If you’d like to participate, write a post on the topic and publish it on the second Tuesday of the month. If you’d like to host, contact Adam.

    This month the topic is hosted by Bradley Ball and his subject is Second Chances.

    Second Chances

    We all make mistakes. I ask the question of people in some of my talks, and note that I make mistakes all the time. I do, and while most of them are fairly small and easily recovered from, some aren’t. Some are easy to brush off, and some are very, very embarrassing.

    In my career, when I think about the big mistakes I’ve made, I’ve got a few choices. There have been times I’ve deleted all the data in a table.

    secondchance

    There have been times I’ve restarted a SQL Server without telling anyone. There have been “fixes” to the system I’ve made without running through the proper channels. However there is one item that stands out for a few reasons.

    SQL Slammer

    I’d let patching slip. We had a large SQL Server environment (hundreds of Standard/Enterprise versions, thousands of MSDE versions) and while we’d been trying to stay patched, it was a hassle and we let some things slip in the fall of 2002.

    I’d gone to the mountains for the weekend to ski with my family. We returned late on a Sunday night to find numerous voice mail messages at home and on my cell phone. I was actually called again while I was checking messages on the way home from a friend at work that said I needed to come in. After dropping off my wife and kids, I headed to the office.

    Our analysis of the worm showed that it was wrecking havoc on our network. It constantly bottlenecked the network, and we had shut everything down. For a 5,000 person company, with a central network presence and hundred of software developers, this was not ideal. We were up most of the night, waiting on someone from Microsoft to fly in and help us rebuild the patch that Microsoft had released. Since we’d installed many instances of MSDE in non-standard locations, the patches wouldn’t work.

    Our network was down Mon and Tues, but we learned some valuable lessons, like email and file shares weren’t so critical to the business that we couldn’t function for a day. We also learned that allowing developers to ignore patches was a bad idea as the majority of the delays after Mon afternoon were due to MSDE instances.

    My lesson? Myself and the other DBA received a stern talking to from our boss about patching. We had delayed some of the patches for a few months, but there wasn’t a good excuse for getting almost six months behind. Microsoft had been sending lots of patches, but we didn’t have a good reason for not getting them installed quarterly.

    I still don’t like the every other month patches that MS releases as cumulative updates, and I don’t recommend them, but I have learned the security updates are worth getting installed ASAP.

  • SQL Server Should Work for Us

    failI ran across a post the other day from someone that was trying to find out why their maintenance plan failed. This person had received a failure notice from SQL Agent, which is good. We should all be aware of failed jobs from some sort of monitoring system. Like any good DBA, this person checked the job history, saw an error, couldn’t figure it out and posted a question at SQLServerCentral, looking for help. That’s a good plan for most anyone 😉

    Experienced DBAs know that to debug this issue, you need to look at the maintenance plan log, which has more details. The job history contains a minimal amount of information and usually doesn’t help. If you examine the maintenance plan log, it’s usually easy to determine which part of the plan failed since the plans are fairly simple constructs. The really exceptional DBAs don’t use maintenance plans and instead would rely on some sort of tool or well known script instead to handle their maintenance.

    However why do we need to go to the maintenance plan’s log? SQL Server includes the job history. It includes maintenance plans. Why doesn’t the job understand there is a maintenance plan, read it’s log, and return the information? Or give us a button on the job history that loads up the maintenance plan log? That’s a simple thing to do, and isn’t the job of software to make tasks easier?

    This is one of those places where SQL Server feels a bit immature and unrefined. I understand the complexity of the entire product and the limited resources that are devoted to enhancing and growing the product. However, where are the resources that make SQL Server easier for the average and accidental DBAs to use? Those are the majority of the people using the platform.

    SQL Server led the industry in producing tools that made it easy to manage and use. Other platforms are quickly catching up, however, and if SQL Server can’t continue to improve its toolset, in addition to its features, people will consider other platforms. The cost of SQL Server has risen, but so has the revenue. Do us, and yourself, a favor, Microsoft. Put a team of 50 people to work on usability and improving the tooling. It will be a great investment for the future.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.

  • Losing Data

    We hate losing data as professionals. We should not make the simple mistakes that make it easier for this to happen.
    We hate losing data as professionals. We should not make the simple mistakes that make it easier for this to happen.

    Most of us that work as data professionals hate the idea of losing data. When the developer calls and says his test database is gone and backups were never set up, we may shrug our shoulders and offer to help next time, but we feel bad. We will try everything we can do to recover the data, usually going out of our way to give it our best effort.

    We will lose data. There will be situations that are out of our control, and we have to accept that. However we should try not to make the easy mistakes ourselves that might cause data loss. I ran across a short piece on Five Sure Ways to Lose Data and I agree with the items, but I think there are a few more things we should watch out for.

    One of the easiest mistakes to make to forget is to set up backups. Too often we implement new databases under time pressure, dealing with software that is dropped in our laps at the last minute. Security permissions are never documented and during the frustration of just getting something deployed, we may forget to set up a backup system, intending to do it next week.

    Don’t do that. Get backups set up immediately. It’s quick, it’s easy, and you should have some automated process or script ready. As soon as you complete backups, invite yourself to a meeting to set up monitoring in the next day or two. That’s one of the other easy things to fix: ensuring your backup schemes are working by monitoring your servers. Your monitoring should include alerts for DBCC checks and high severity errors in addition to backups at a minimum. Automating this, or using a tool, are the best things you can do.

    There are lots of other things we might ignore that can cause data loss, but if you get your backups working, you should be able to recover from most any situation.

    Steve Jones

    Voice of the DBA Podcasts

    The podcasts will return tomorrow.