February 21, 2011 at 8:15 am
From my last post, I see many DBA prefers using T-SQL over SMS gui do daily jobs like maintenance, trouble shooting, performance etc?
Can anyone tell me what are the most common used T-SQL DBA uses in daily jobs?
Or a resource or a summary article about this?
Thank you.
February 21, 2011 at 8:37 am
The most common uses are backup management, and index management.
The maintenance plan backups are okay, but they lack a few bits of flexibility, with regards to doing things like test-restores to make sure the backup is really valid.
The index defrag/rebuild in the management plans is way too robotic. It's better to work out real rules for index maintenance, like basing the action taken on the fragmentation, deciding whether it needs defragmentation in the first place based on single-row vs range lookups, and modifying fill factors based on speed of fragmentation.
There are other things that can be managed with scripts in maintenance jobs, but those are the two most common and most important.
- 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
February 21, 2011 at 8:55 am
Thank you, what other T-SQLs for everything DBA needs to do for daily jobs?
February 21, 2011 at 9:36 am
I've got a number of scripts I use daily and have them stored safely in a SSMS Solution and version control. Some of them are job specific but these tend to be the generic ones include.
-DMV queries to recover cached query execution plans.
-Scripts to investigation transaction replication errors.
-DMV script to get currently executing queries
-Fix orphaned users
-Litespeed restore/backup
-Customised Logshipping scripts for investigation and correction
-Customised Index creation script with SORT_IN_TEMPDB=ON and ONLINE=ON
There's probably more 🙂
February 21, 2011 at 10:24 am
Browse through the "Scripts" area for plenty of useful ideas.
February 21, 2011 at 10:52 am
It really depends on your job and your environment. Like Gus already said, index management and backups are the most common ones, but different situations are going to require you to spend time in different areas. For example, my previous company had a very large development and testing environment that we had to refresh from production constantly. It meant that lots of time was spent in writing or modifying or automating database restores, so the people responsible for those systems were running restore operations more than the average DBA does. I spent most of my time working on performance tuning and database design, so I was running queries against DMVs or writing DDL code. Basically, you have to study what you need to know for the job at hand.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply