Book review – DBA Survivor: Become a Rock Star DBA

I recently read through Thomas LaRock’s book “DBA Survivor: Become a Rock Star DBA”. It was a nice read that walked the reader through the basics of being a DBA.

I have mixed opinions about this book though. Some of the first chapters are about starting a new DBA job and what you should be doing. This was rather convenient for me because at the time I was starting a new job. LaRock gives a list of things you should first do and check. This is great in theory but my first day I was rushed into a meeting and tossed onto a project. Before I could get to number one on his list, I was already knee deep in fixing jobs, answering SQL questions, and sitting in meetings.

The beginning of the book talks about getting a DBA job, who to talk to, what to read, etc. Lots of good advice for people starting out in the industry. It then moves on to what to do when you first get your first DBA job. Again, I think this is great in theory but I don’t know anyone who got their first DBA job through an interview. Everyone usually starts somewhere else – QA (my personal experience), developer, sysadmin, or something other related field. I did enjoy this part of the book though. It was able to fill in some holes for me.

The next part talks about office basics and what to do and not to do. If anyone has worked in an office, they’ve probably encountered most of what he is talking about here. Eat lunch with people, don’t talk about people, be nice, and most common sense stuff.

The next couple chapters is where it starts to get good. We’re talking about the raids, the HA’s, the SLA, and all the interesting stuff about database life. There are some great examples of what you should use and where. This is the perfect juicy stuff that would satisfy a newb but could also reteach an old timer something he/she hasn’t been exposed to in awhile. Chapter 6 is the best section by far. This is where the author gets into troubleshooting. I reread this section more than once.

The book then goes in and out of sounding like a support book for people having trouble in an office –  “Be nice”, “Work/Life Balance”, “Job Security”, and so on. I think these are important in any job but I didn’t particularly care for these sections. Especially the sections on watching what you eat. Not all DBAs are big fat dudes.

hmmmm clustered or non clustered indexes...

The last part of the book focuses on training. I thought this part was ok because I never really tried to persuade my boss to send me for training. I’m not against training but I probably wouldn’t spend a lot of time trying to get the money for it either (although I have to admit, PASS Summit is pretty cool).

Overall I thought this book was a good quick read and perfect for people new to the IT field and new to the database industry. If I was a professor in college, I would definitely recommend this book to new graduates or even people thinking about becoming a DBA. I give it 2.5 sandwiches out of 5.

 

Share

Backing up all DBs using sp_msforeachdb

This will be the first in a series of post on a couple of my new favorite undocumented commands. I’m talking about sp_msforeachdb and sp_msforeachtable. These guys are awesome.

Recently I needed to backup all the databases on a SQL 2000 server. I could have created a maintenance plan and just used it as a one-off but I figured I’d get a little creative. I used sp_msforeachdb to back each DB up.

Check it out:

1
2
3
4
5
6
7
8
9
10
11
12
13
EXEC Sp_msforeachdb
' IF DB_ID(''?'') > 4
 Begin
BACKUP DATABASE [?] TO  DISK = N''\\server\Backups\?.BAK'' WITH NOFORMAT,
 INIT,  NAME = N''?-Full Database Backup'',SKIP, NOREWIND, NOUNLOAD
declare @backupSetId as int
 select @backupSetId = position from msdb..backupset where database_name=N''?''
and backup_set_id=(select max(backup_set_id)
from msdb..backupset  where database_name=N''?'' )
if @backupSetId is null begin raiserror(N''Verify failed. Backup information for database ''''?'''' not found.'', 16, 1)
end
RESTORE VERIFYONLY FROM  DISK = N''\\server\Backups\?.BAK''
WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND End '
Share

Managing your SQL Server logs

“I am getting an error. Something’s broke. My application works fine. It’s the database.”

We’ve heard that guy more than once. So what do you do? I usually open up my box of SQLs and check out the logs.

This is usually a pretty simple process. Open the management folder, open the SQL Server Logs folder, double click your log. It’s a nice place to start.

Now what happens when you have 500,000 records in there? It takes FOREVER!

Luckily SQL 2011 (Denali) has a feature in there to stop the whole log from loading. If you aren’t on SQL 2011 (Denali) then you have to sit there and wait…and wait…and wait…

I recently found this beauty – EXEC MASTER.sys.Sp_cycle_errorlog

This boots your current log to second place and creates a new one. Now you have a nice clean fresh log to fill up.

So what happens to the logs? Well the SQL default is to keeps 6. Almost every time you open up that folder, you see Current + 6 old ones. After 6, they are recycled.

You are able to configure the amount of logs you keep too, between 6 and 99. Just right click on the ‘SQL Server Logs’ –> Configure.

SQL creates a new log every time the server is restarted. For most people, this is way too far apart. On my servers, I set up a weekly job that run this command. That way I know I have 1 week worth of data in each of my logs.

Share

Fixing Orphaned Users

One my favorite commands when I was started my career was Sp_changeuserslogin. I had to move a lot of databases around and I orphaned a lot of users. This command really helped me find what users were orphaned and reconnect them with the appropriate database.

When should you use this command?

This command is very useful when you have a user in your database and a login on your server that should be connected (or mapped) but are not.

To find out if you have an oprhaned users run:

EXEC Sp_change_users_login ‘report’

This will give you a list of orphaned users in your DB.

If you find you have some orphaned users you can run this command to fix them:

EXEC Sp_change_users_login
‘auto_fix’,
‘YourUser’

It’s a handy tool to use but BOL warns avoiding it because it will be going away soon. They suggest using ALTER USER instead.

ALTER USER YourUser WITH LOGIN = ‘YourUser’

Share

Query to find Duplicate records in a column

We are going to start SQL sandwiches with a tasty little snack. I have had this asked in an interview before. “What is an easy way to find duplicate records in one table?”

The answer is quite easy yet it can stump anyone who hasn’t done it before.

It’s a simple select statement that you can use for multiple columns.

1
2
3
4
5
SELECT column1,
COUNT(column1)
FROM table1
GROUP BY column1
HAVING COUNT(column1) > 1

You can add other columns in there if you are looking for a record that has multiple columns that are the same.

 

 

Share

Welcome to SQL Sandwiches!

Hi,

My name is Adam and I am a DBA. Welcome to my kitchen of delicious SQL tips, tricks, and whatever else I can offer you. This blog is for me – to document my experiences with SQL, for you – maybe you can learn my mistakes or view something from a different angle, for the community – the more useful information out there, the better (sometimes), and for Ween – because they are awesome.

Like most DBAs, I didn’t always dream of working in the world of data. One day I was handed a terribly overdue project and was working nights. The next thing I know I am restoring backups, then looking at stored procedures, then the next – I am a certified DBA. Maybe not that fast but you get the idea.

So feel free to leave comments if you find something helpful, if you find mistakes in my work, or just to say Ween is awesome.

Enjoy!

 

Share