July 31, 2012 at 8:34 am
I wonder if you can standardize this and ship it through something like CodePlex maybe as a 3rd party application.
I use a maintenance DB on all instances too, mostly to log/manage stuff like index and statistics rebuild, backup, partitions and a few diagnosis procedures that I found helpful in the past. A lot of the procedures are actually taken from different online resources (for example I use the classic index rebuild script by Hallengren[/url], which am sure more than half of SQL Server admins use it too). Does anyone have a DBA/Utility database they can share?
/>L
-----------------
... Then again, I could be totally wrong! Check the answer.
Check out posting guidelines here for faster more precise answers[/url].
I believe in Codd
... and Thinknook is my Chamber of Understanding
July 31, 2012 at 9:10 am
I have been wanting to create a nice clean package to post, but we're in the middle of a major project and haven't had any time to come up for air. :w00t:
It is on my goal list, though. 😀
July 31, 2012 at 9:19 am
If you haven't built a DBA database...
Why haven't you?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 31, 2012 at 9:23 am
I've never heard of this. Probably because I'm an accidental DBA. I think it is a brilliant idea! I'm going to be watching this thread with great interest to see what should go into a DBA database. Thank you, Steve, for bringing this to my attention.
Kindest Regards, Rod Connect with me on LinkedIn.
July 31, 2012 at 9:36 am
I'm not a true DBA admin. I work with the SQL tables and such. I would like to learn how to set this up. Can someone send me to a site/book that would tell me what needs to be collected and how to get this information. It sounds like a good idea for me to implement on my system.
I will look a the sites and information posted previously in this thread.
July 31, 2012 at 9:38 am
As the Data Administrator I do not have a DBA Database. I travel more in the Metadata Database to insure that the internal data models are within standard, and to determine how we are modeling certain entities/tables and interfaces. It is very handy. A job runs each day and uses the SMO to monitor changes in the metadata across multiple database servers.
Both the data within and the application are highly protected and available to only a few. Having this information helps me look at how we are and are not integrating data across systems as well as identifying potentially redundant data.
Our DBA has a DBA warehouse of sorts.
Not all gray hairs are Dinosaurs!
July 31, 2012 at 9:50 am
cksid (7/31/2012)
I'm not a true DBA admin. I work with the SQL tables and such. I would like to learn how to set this up. Can someone send me to a site/book that would tell me what needs to be collected and how to get this information. It sounds like a good idea for me to implement on my system.I will look a the sites and information posted previously in this thread.
I second that.
Kindest Regards, Rod Connect with me on LinkedIn.
July 31, 2012 at 10:55 am
Yup, I think eventually most DBA's come to the same conclusion about needing one on their own. Though I imagine the structure and purpose varies wildly between implementation, ever evolving as they are. I've seen a DBA use master for the same purpose, but it always made more sense to me to have a dedicated dba db and or monitoring/repository server.
July 31, 2012 at 11:03 am
Yes. Used for storing performance history, job monitoring, various maintenance procs (our log shipping, index rebuild jobs, etc). Never leave home without it.
August 1, 2012 at 6:19 am
Yes, I use a DBA database for a number of solutions (one of those is Ola's solution for re-indexing). A list of the databases gives an idea of the data I collect:
cdc_capture_log (nice when the cdc capture is done on the log shipped database)
DBGrowthRate (also aggregated on a central server)
IndexInfo (for user_seeks etc.)
recommendations (for missing indexes)
ReindexMessages (where I log Ola's re-index actions)
SpaceUsed (like DBGrowthRate, but for tables)
August 1, 2012 at 6:46 am
Yep, even as an accidental DBA I've got one on every one of our servers. In my case it's very basic compared to what others have described, but from a SysAdmin point of view it's invaluable for server monitoring (which is what caused me to create them initially). Having an identical DB on every server, with an identical table containing identical data makes monitoring very easy, since I can point to any SQL server, run a single query, and know that the result should always be the same.
It also come in very handy when dealing with client servers where you have no real control (or sole control) of the data being stored on there. It means you have a DB that is completely separate from the clients data, and whatever you do with it doesn't matter.
August 2, 2012 at 6:32 am
I have started to implment a DBA (or "Admin") database within the last year. I am an accidental DBA, having started in application development, then moving to database development, which has now morphed into admin work as well. I went the DBA db route because I wanted to automate a bunch of tasks that weren't being done on a regular basis (index maintenance), or had jobs that weren't being monitored (backups). I have also integrated procedure and application execution and error logging as well; I have tables to store the error and execution data, and have been integrating standard procedures to handle the logging into the other databases and their procedures.
As you can see, I haven't done much in terms of performance monitoring, but that's because I don't know what to monitor, or exactly how to implement it. Unfortunately, as DBA is only a part-time hat, I don't spend much time actually looking at performance until someone complains about something "running slowly". So, if anyone has any tips, links, or advice on implementing proper performance monitoring, I'm all ears (or eyes, really).
As for what I do have, I have procedures to:
- Create backups (db and trans log), and maintain a pre-set number of those backups (I delete files outside of the database's retention window).
- Regularly scheduled maintenance tasks: index maintenance, clearing out old job and backup histories, integrity checks, logging missing indexes, logging database file size.
- Execution and Error logging: the tables are centrally located, and I use certificate-signed procedures to pass from the application database through a central stored procedure then into the logs.
- Scripts that will generate "inventories" of the databases on server. I use these to create reports showing what objects we have in each database, the SQL Agent jobs, Replication, etc. etc. etc.
Like I said, if anyone has any thoughts or suggestions on additional tasks to include, or any info on proper performance monitoring, and how to maintain that data without having it grow too large, that would be fantastic.
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply