November 17, 2012 at 12:35 pm
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
November 18, 2012 at 2:59 am
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 (<dbName>) WITH NO_INFOMSGS, ALL_ERRORMSGS
Also, on the mission critical database how far do full and trans log backups go?
Joie Andrew
"Since 1982"
November 18, 2012 at 6:46 am
Joie Andrew (11/18/2012)
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 (<dbName>) WITH NO_INFOMSGS, ALL_ERRORMSGSAlso, 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
November 18, 2012 at 10:06 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 18, 2012 at 12:17 pm
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"
November 18, 2012 at 12:21 pm
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 18, 2012 at 1:08 pm
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" 😉
November 18, 2012 at 3:44 pm
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. 🙂
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
Change is inevitable... Change for the better is not.
November 19, 2012 at 1:17 pm
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).
November 19, 2012 at 1:56 pm
Chrissy321 (11/19/2012)
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).
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
November 20, 2012 at 11:45 am
Awesome responses guys thank you so much, this site rocks 🙂
I've been flat out doing queries and other schema changes and haven't had a chance to run dbcc checkdb yet 🙁 Will try later in week for sure and keep you posted.
Many thanks
B
November 21, 2012 at 3:50 am
Hi
Before any fixes that you think are "essential", make sure your manager agrees the priorities, in particular new work vs schema fixes, backups, reindexing or DBCCs etc
Only do essential fixes initially - if the system has been working for a few years then it can probably work for a few more weeks (even without PKs)!
If you make changes, and something breaks, then some users might think you don't know what you are doing. You also might get blamed for (schema) problems that come to light subsequently.
Before you get into making schema and other changes (but I see that you have already started), make sure you have a way of tracking the changes, eg use some sort of schema source control system.
Hope that helps
Steve
November 21, 2012 at 7:07 am
stephen.sarre (11/21/2012)
HiBefore any fixes that you think are "essential", make sure your manager agrees the priorities, in particular new work vs schema fixes, backups, reindexing or DBCCs etc
Only do essential fixes initially - if the system has been working for a few years then it can probably work for a few more weeks (even without PKs)!
If you make changes, and something breaks, then some users might think you don't know what you are doing. You also might get blamed for (schema) problems that come to light subsequently.
Before you get into making schema and other changes (but I see that you have already started), make sure you have a way of tracking the changes, eg use some sort of schema source control system.
Hope that helps
Steve
I strongly agree with what Steve wrote above. If you operate alone and without a net, you will die when you fall because you will fall even if you're a great DBA that never makes mistrakes. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
November 22, 2012 at 7:19 am
Hi Bugg,
Lot's of great advice from the peeps that know on here. I'd just reinforce the importance of getting a disaster recovery plan in place and making sure all databases and log files are being backed up successfully and in the manner/mode you require for the organizations needs. It's a definite skin saver
Good luck,
Dave.
Dave Morris :alien:
"Measure twice, saw once"
November 23, 2012 at 4:16 am
And if anyone questions why you are being pedantic about backups / corrupt databases etc - get them to read this[/url] - you might want to have a look as well.
This story changed the view of a couple of people I work with.
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply