New DBA job, done some digging on Database and have some concerns!

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » New DBA job, done some digging on Database...

15 posts, Page 1 of 2

1 2 »»

New DBA job, done some digging on Database and have some concerns!

Rate Topic

Display Mode

Topic Options

Author

Message

bugg

Posted Saturday, November 17, 2012 12:35 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 13, 2013 1:04 AM
Points: 123, Visits: 401

I've recently started a Job at company working as the sole DBA. I come from a .net developer background and have done a load of SQL BI work in the past (SSRS, SSIS), but have never really done any DBA oriented tasks such as performance monitoring, DR, HA etc except for maybe doing SQL jobs, backups and user/role related changes.

After doing some checks on the current database I have spotted some issues that I think need immediate attention.

I restored a copy of the DB, I checked when last DBCC Check was done and it was 3-4 years ago, when the DB was created more or less. Tried to run it and after a while of churning error-ed .I don't have the error message now but will post it at some point in another thread.

Master, msdb, model db are also not being backed up.

50 indexes need rebuilding as their fragmentation is greater then 40.%

Anyway things started to get a little interesting when on my 2nd day I was asked to make some small schema updates. I noticed some of the proposed new column names for the table had similar names to other tables and were int types, clearly foreign keys but the developer hadn't asked for them to be setup as foreign keys. Then I spotted the table that needed changing didn't have a primary key this lead me to look at the other tables and came up with this:

In total there are 251 user tables.

57 tables don’t have primary keys

Only 10 tables have foreign keys

Now I know that foreign keys aren't absolutely essential but i think for data integrity they should be used and i believe that every table should have a primary key.

This database is mission critical and some how has chugged along with little to no attention.

No naming conventions have been adhered to and the whole thing seems a bit mess, a dumping ground for data.

Normalization seems to be ignored in quite a few places.

I am worried that I have taken on an overwhelming task of sorting this all out. Being new to the DBA world i'm bricking myself and don't even know where do begin.

Maybe I'm just being over concerned , but in this situation what would the pro's do first?

Thanks

Post #1385992

Joie Andrew

Posted Sunday, November 18, 2012 2:59 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 544, Visits: 1,018

First and foremost before even considering any changes to the current schema if it were me I would work to identify the current environment a little more. Specifically the potential data corruption.

I would get the results of that DBCC CHECKDB run as soon as you can. Run the following on your system/user databases and post the results if any errors are reported:

DBCC CHECKDB (&lt;dbName&gt;) WITH NO_INFOMSGS, ALL_ERRORMSGS

Also, on the mission critical database how far do full and trans log backups go?

Joie Andrew
"Since 1982"

Post #1386033

bugg

Posted Sunday, November 18, 2012 6:46 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 13, 2013 1:04 AM
Points: 123, Visits: 401

Joie Andrew (11/18/2012)

DBCC CHECKDB (&lt;dbName&gt;) WITH NO_INFOMSGS, ALL_ERRORMSGS

Also, on the mission critical database how far do full and trans log backups go?

Thanks Joie, will try run this in the week. What performance impact will it have on a production DB? The DB is up 24/7 with users connected at all times.

I will double check how far back the backups go, what is the ideal length of time?

thanks

Post #1386041

GilaMonster

Posted Sunday, November 18, 2012 10:06 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:47 AM
Points: 37,698, Visits: 29,960

bugg (11/18/2012)

What performance impact will it have on a production DB? The DB is up 24/7 with users connected at all times.

Somewhere between none and horrid. It's an IO and CPU intensive process.

You can restore a backup on another server and run it there, but you need to run it to completion, now and on a regular basis.

I will double check how far back the backups go, what is the ideal length of time?

Far enough back to support business requirements for retrieving lost/deleted data and far enough that should you encounter corruption restoring a clean backup is always an option

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1386065

Joie Andrew

Posted Sunday, November 18, 2012 12:17 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 544, Visits: 1,018

Yeah, exactly what Gail said. You should have a backup strategy for all your databases in production. How far back your backups should be based on your requirements for how far back you should keep data. Since constency checks have not been regularly run on your databases I asked because if you have database corruption you may have to go back to a database that is quite old and restore all the transaction log backups in order to fix the corruption and ensure no data loss.

Joie Andrew
"Since 1982"

Post #1386084

GilaMonster

Posted Sunday, November 18, 2012 12:21 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:47 AM
Points: 37,698, Visits: 29,960

Joie Andrew (11/18/2012)

you may have to go back to a database that is quite old and restore all the transaction log backups in order to fix the corruption and ensure no data loss.

I've never yet seen a case where someone discovers corruption ages after the fact and actually has all the backups necessary.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1386087

Perry Whittle

Posted Sunday, November 18, 2012 1:08 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:36 AM
Points: 5,201, Visits: 11,153

bugg (11/18/2012)

What performance impact will it have on a production DB? The DB is up 24/7 with users connected at all times.

I will double check how far back the backups go, what is the ideal length of time?

thanks

For an immediate run for your production system you may use the following DBCC command, it's less intensive than a full check and recommended for production systems. You will at some point though, need to run a full check and as already suggested you could do this on a restored copy of the database on an offline server.

DBCC CHECKDB ('YourDB') WITH  ALL_ERRORMSGS, PHYSICAL_ONLY

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" Wink

Post #1386093

Jeff Moden

Posted Sunday, November 18, 2012 3:44 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:57 PM
Points: 32,906, Visits: 26,790

bugg (11/17/2012)

Maybe I'm just being over concerned , but in this situation what would the pro's do first?

I'm with the others that have said so.

My first priority would be to protect the data as much as possible. That means making sure that backups and log backups (if appropriate) are being taken correctly. I'd do this even before a check for corruption just in case there is no corruption.

My second priority would be to check for corruption and, if it existed and depending on what was available, fix it using one method or another (and there are several odd ones available like getting data from a covering index if available). The, restart the log chain from some point after the corruption was repaired.

My third step would be to check disk space and make sure they're not going to outgrow it anytime soon based on the size of the existing databases. I guess my recommendation would be that the have enough room left equal to at least half the storage requirements of all the MDFs and LDFs combined.

Once the steps above have been accomplished, I'd then take a baseline of the servers just to see what's up and to measure improvements by.

Then, the real "fun" can begin. My next step would be to find out all the logins and users that have DBO and SA privs so I can start to whittle them down to nothing as I work on the other problems that I'd find.

Don't get discouraged in doing any of this and don't waste your time calling people "idiots" and the like. Take on the attitude of "Forgive them for they know not what they do", teach them better ways, and everyone help each other to make things better for the database and the apps that touch it. It'll also take a very strong sense of learning which battles to fight because its more important that this all turn out to be a team effort than a fight between the DBA and the rest of the world. Don't lead... get behind them and push like hell! LET them see it your way instead of jamming it down their throats. A choking person doesn't listen very well. Smile

Heh... as Granny would say, "Get the bucket. Get the stool. Get the knife. Get the potatoes. Then start peelin' one potato at a time."

--Jeff Moden

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1386109

Chrissy321

Posted Monday, November 19, 2012 1:17 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 5:03 PM
Points: 551, Visits: 2,986

I might want to prepare a report for management detailing the state of the database and recommended actions. I would be careful here not to be judgemental, just the facts. I wouldn't throw the previous DBA under the bus or suggest there was a management failure. You should cya in case something goes wrong and the blame goes to the new dba(you).

Post #1386556

GSquared

Posted Monday, November 19, 2012 1:56 PM

SSCoach

Group: General Forum Members
Last Login: Yesterday @ 1:55 PM
Points: 15,442, Visits: 9,571

Chrissy321 (11/19/2012)

Excellent recommendation.

Something along the lines of, "In doing due diligence on the databases I am taking responsibility for, I found a fair amount of 'low-hanging fruit' where processes and administrative practices can be improved." Go on from there, in a positive manner, not a blamethrower thing.

Even if something bad (like serious corruption) is found, see if you can solve it first, and present it that way. "I found that there was an amount [be specific] of data corruption, and took the following steps to correct it. It is now handled, and preventative steps have been taken to prevent further corruption from occuring." That kind of thing.

If you find a no-situation (no corruption, recoverable backups, that kind of thing), "Preventative measures are being taken against future data loss situations, based on industry best-practices. While no data corruption has been found, improved preventative measures have been put in place to keep the data clean." Don't push "The prior incompetent didn't have DBCC CHECKDB running regularly." Instead focus on positive attention to details.

This avoids blamethrowing, while creating a positive environment for yourself and others.

On the technical priorities, I would definitely make sure backups are being done correctly as a top priority, and DBCC CHECKDB all-the-way.

I also recommend some monitoring tools, like Confio Ignite (there's a free version) for performance issues, and Red Gate SQL Monitor (not free). These will make your life as a DBA much, much easier.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Post #1386570

« Prev Topic | Next Topic »

15 posts, Page 1 of 2

1 2 »»

Permissions