Databases

Msg 5030, Level 16, State 12, Line 1

I like to be preventive or proactive in SQL server maintenance (or PM for short). As part of the PM I have in place I run database integrity checks weekly. If there are issues when running the check(s) I get an email with enough details such as database name and server to make things a little easier when investigating further. I ran into an error the other day I wanted to share what I found and how I fixed it.

Problem

This is an example of the email I received this morning:

Daily DBCC CHECKDB Error: TestDB on server Server_Name 

My initial reaction was “Oh, no!” (I actually used a few other more colorful adjectives I won’t repeat)

The first thing I did was to copy the backup from last night to a test server and ran CHECKDB to see what I could see and make sure we did not have any corruption. After running the check and finding no issues I started to investigate further. I know running CHECKDB can be resource intensive (one reason, if you can, to off load that maintenance task to a “non-production” server). I wanted to make sure there weren’t any jobs running at the same time. Finding none, I wanted to see what the actual error was. Not finding anything related to the error in the logs I opened a query window and ran the following command and received the following results.

Msg5030-Level16-State12-Line1

Now I think it is important to note here the server in question is still being configured/tested before deploying to production so I was able to run CHECKDB on the server directly (and since I confirmed no issues were present already).

Solution

It appears there is a bug for this particular issue. Bug #: 489609 (SQLBUDT)

It seems if you get this error you have potentially one of two problems.

  • At least one other connection is using the database against which you run the DBCC CHECK command.
  • The database contains at least one file group that is marked as read-only.

I was fairly sure the database did not have a “read-only” file group but I checked just to make sure because this project has too many cooks in the kitchen (too many people with sysadmin access). I confirmed no read-only file groups were present.

sp_helpfile_info

I know there are other connections (several in fact) to this database. So we found out what caused the error. Now, how do we fix it?

We can either off load this task to another server. If we do that, we will need to build out a process of either copying a backup to another server or we can back up the database to a different server, restore and run CHECKDB.

Another option is to create a snapshot. Run CHECKDB against the snapshot we create and then drop the snapshot when finished.

Before_DBSnapShots

The code below is an example of how to go about doing this.

createsnapshot

After_DBSnapShots

runCheck

CHECKDB_results

dropSnapshot

Conclusion

No matter how much preventive maintenance you do on your servers/databases corruption will happen at some point. Running integrity checks is one way to help combat corruption and be aware of it as close to the time it occurred as possible. The sooner you know the more options you will have to remedy the issue. To read more about this error and how to work around it see this article. http://support.microsoft.com/kb/928518

Cheers!

Advertisements

I’ll be presenting at PASS DBA VC next month

Goals

I have been slowly trying to increase my speaking within the SQL community beyond our local user group CACTUSS. Recently, I submitted some sessions to 24HOP and SQLRally. I did not make the speakers list at either event but that is O.K. It’s impossible to make the list if you don’t submit and the sessions and speakers for both events are absolutely awesome. You should make a serious effort to attend one or both these events.

Determination

Some would think not making the list to those events would discourage from submitting to other events. Not me. It actually made me focus more on the abstracts and the content. I looked at a few sessions I had started but never fully completed, finished them and more recently I submitted sessions to both SQL Saturday 107 – Houston and SQL Saturday 134 – Johannesburg, Gauteng, South Africa.

Drive

I will be a making the drive to Houston regardless if my session it accepted since it is just down the road. I would love to go in person to Johannesburg, but if my session is accepted I will have to present via live meeting and a perfect segue into the next paragraph.

Presenting at PASS DBA VC

On Wednesday March 28th, @ 12 Noon (Easter) I will be presenting at the PASS DBA Virtual Chapter.

Work Smarter Not Harder – Standardize Your Environment with Best Practices

Are all your servers standardized across your environment? Do you manually click the next button when installing SQL Server? Do you manually create and configuring your routine maintenance? Are your SQL Agent Jobs owned by other DBAs in your group? Do you know when jobs fail? Or discover them accidentally? Do you have a standardized way to deploy updates?

Conclusion

Remember, if you are interested in speaking and have the desire there are many different venues to share your passion. Seek them out and do not be discouraged if you don’t make the list the first time.

So set your calendar for 3/28/2012 at 12 noon eastern and stop by. You can look me up at SQLRally or SQLSaturday 107 or maybe at an event near you soon.

Cheers!

What is in a name?

I just deployed multiple databases for a new application the other day. Like a lot of software deployments these days they are done with installation packages. While this is nice of those that do the installation I personally don’t like them unless I am advised (in advance preferably) what they are doing besides installing databases. Call me a control freak and I will not deny it especially when it comes to the databases I am responsible for, but I like to know what is happening on my servers.

File extensions

Anyway, after running the installation being the control freak I am I started to look at the database that were installed. While reviewing the initial size, the names, if they had additional file groups for indexes or used the primary and other basic information about them. One of the things that I noticed right off the bat was several of the databases had additional file groups and data files associated with them. That’s a good practice and something I like to do as an added layer for disaster preparedness, especially if the projection is for them to grow to be very large databases and according to the project these are.

What I noticed was there were three files like so.

Database_name.mdf

Database_name_Index.mdf

Database_name_log.ldf

Naming convention

You might think that to be a major problem causing issues right away. Since mdf files are usually associated as the primary data file and the primary file group the contains the system information (objects) . The industry standard for secondary data files and additional file groups is usually ndf extension and ldf is for the transaction logs of the database.

Confusion

While it is not a best practice or industry standard SQL Server does not care one single bit. The file extensions are just naming conventions. If you really wanted to you could have your data file with an extension of .ldf or the transaction log with an mdf file extension.

The bigger issue this can cause is when other DBAs on the team have to perform maintenance or trouble shoot an issue and I am not around. It has the potential to be confusing

Conclusion

As I mentioned this is not a show stopper and the database will hum along just fine but I will be mentioning this issue during our next meeting and provide options on how to fix this sooner rather than later and hopefully avoiding any issues when not using industry standards and best practices is confusion.

Moving TempDB data and log file locations

I have a test database server, at the time the server was setup it had minimal drives and space on those drives was small. It was setup on the stingy side because it is a testing server. I knew I would have problems with it if I was going to do any work on the majority of my databases due to the size limitations.

Recently, I had additional drives and space added so I wanted to move some things around a bit. This is a short post on how to move data and log files for your database. We are going to be talking about the TempDB today but this code and method works for all your databases.

Find File and Name Location

Before we move any files around we need to make sure we know where they are. We have standard configurations but some of the servers were setup prior to the establishment of standards, so the data files can be almost anywhere. I know, it’s not the best environment but as servers are modified I try and get things closer to our standards.

Open a query window in SQL Server Management Studio (SSMS) and type or copy the following code into the window and execute.

USE [tempdb]
GO

EXEC sp_helpfile

Your results will be like this. Notice the files are located on the D:\TempDB drive and folder.

findFileNamesAndLocations

Move The Files

Now that we know where the files are we will move them to the T:\TempDB drive by executing the following command.

MODIFY FILE ( NAME = logical_file_name, FILENAME = ‘ new_path/os_file_name ‘ )

This is the code I used to my the files.

ALTER_DATABASE_MODIFY_FILE

For this demo and to keep things simple I moved the log file as well. Ideally, you want to place the log file with other log files on their own drive separated from data files.

Restart SQL Server

Now, we want to restart SQL Server

restart SQL Server

Now we want to confirm the files are where we want them to be.

USE [tempdb]
GO

EXEC sp_helpfile

ALTER_DATABASE_MODIFY_FILE_results2

OK, so the files are now moved to where we wanted them, our work here is done.

As you can see this is very handy and a really good thing to know, especially if you cover other projects that have difficulty managing their disc space. Not that something like that would go on in my shop or yours for that matter. J

If you want to read up more on ALTER Database T-SQL, here is the link to MSDN

http://msdn.microsoft.com/en-us/library/bb522469.aspx

Cheers!

Getting Organized

When you have over 170 servers your team is responsible for monitoring, some having multiple instances with hundreds of databases you have to get organized quickly or the hundreds of emails daily will eat you up and spit you out real quick.

Before I started I asked about some of the monitoring tools we use. You know what the biggest tool I was advised to get to know was? Guesses? Outlook. That’s right. Outlook.

Every day (night actually) we have reports sent to us information about our servers. The information can be used to track changes on the server. If a user is added without our knowledge (like that would ever happen) we will know. That’s a lot of information to watch right? The good thing is I am the primary only on some the servers and the backup on others. I am still waiting on a definitive list of what is mine. Until then I have been spending all my downtime (ha, what’s that) creating rules in Outlook to filter out the 130+ daily emails from the critical issue emails.

I am getting organized by creating folders. The folders are by version of SQL Server (2000, 2005, and 2008). Yes we still have 2000 servers around. Some will live for a long, long time due to regulations but that is another post all together. Under the version folders I have the other DBAs on the team and under their folders I have the servers they are responsible for. I there are probably flaws in this process but I will worry about that when (if) the time comes. I have to start somewhere and this is working for now.

How does this help get organized you ask? Well, I really only need to look at my folder(s) and servers to review the daily issues. Oh, I forgot, I mentioned critical issues before. They have their own folders in the root. That way I can see Fatal Errors and Insufficient Resource email alerts separate from the daily monitoring being done.

This method may not (will not) work for all. But it is getting my inbox down to a more mangagable mess and that reduces my stress. So, if you are not organized and you don’t think this will work for you. I suggest you find something that does and reduce your stress level 10 fold.

Cheers!

SQL Server tools

I know of a DBA who will be starting a new job in the near future. He is making a list and checking it twice and reading SQLRockstars’s (Thomas Larock) ( twitter | blog ) Book for advice. Expanding on that note he is seeking thoughts and opinions from the community on tools of the DBA trade.

His anxiety levels are at boiling point and he is seeking ways to help reduce his stress. He has investigated the new environment seeking information on the tools used and how he might become familiar with ones being used. Come to find out, no tools are available for use. This is a known issue at the new job and they are seeking to improve the situation. He now has an opportunity to provide input on some the tools that will be investigated and potentially purchased.

  • Time frame is 4-6 weeks (maybe less).
  • Tools must have an evaluation copy for download.
  • Tools should allow for managing performance, availability, quality.
  • Tools must run on SQL Server 2000, 2005, 2008.

My friend thanks you all for any and all input. He can be reached through me via twitter, this blog, or directly via email.

Cheers!

Loving what you do, doing what you love

I realized long ago, if you don’t love what you do then you’re doing the wrong thing. Find something you love to do and do it. Not to get too philosophical but, life is too short to not like (love) what you do

I love being a Database Administrator! It is exciting, challenging, and stressful (at times) but I always have fun and I love it. I try to “learn something new everyday”. That is one of the good things about being a DBA. I basically get paid to play all day long!

Since I am a Development DBA (the other being a Production DBA) I know what I don’t know and that is the Production side of database maintenance and such. While I do have hands on knowledge of and experience with creating maintenance plans and even have experience with trouble shooting. The experience I do have is limited and not part of my daily routine.

I have decided to move from my comfort zone and pursue more knowledge and learning on the Production DBA side. This may sound kinda corny but that’s what I want to do. I realize my comfy 9-5 (actually 7:30-4:30) Developer DBA job is nothing like a 24/7/365 Production DBA job but that is O.K. I am ready and feel I can and want to take on the responsibility.

I started with switching some of the blogs I follow to some more related to the things I know I need to work on.

I purchased “Professional SQL Server 2008 Internals and Troubleshooting”, by several well known SQL Server Experts and MVPs like Christian Bolton ( blog | twitter), Justin Langford ( blog | twitter), Brent Ozar ( blog | twitter), James Rowland-Jones ( blog | twitter), Jonathan Kehayias ( blog | twitter), Cindy Gross ( blog | twitter), and Steven Wort ( blog | twitter)

I figured if I can absorb even a small portion of the collective minds that had a part in this book I can’t go wrong and will be that much better at my job.

I also made a list of things I need to learn more about. Of course this list is just a start and I will most likely expand on it as I go.

  • Best Practices for SQL Server setup
  • Disaster Planning
  • Backup and Recovery,
  • Troubleshooting,
  • Index Tuning
  • Memory
  • Security
  • Storage

As usual anything I pick up along the way while reading this I’ll pass along, within reason of course. After all if you really want to learn the information you should buy their book and read their blogs too.

Cheers!