{"id":1000,"date":"2011-09-27T20:23:20","date_gmt":"2011-09-27T20:23:20","guid":{"rendered":"http:\/\/craftydba.com\/?p=1000"},"modified":"2017-10-15T16:51:52","modified_gmt":"2017-10-15T16:51:52","slug":"dba-basics-introduction","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=1000","title":{"rendered":"DBA – Introduction"},"content":{"rendered":"

Today, I am going to start writing another topic thread for Database Administration (DBA<\/a>). I do alot of DBA work among many other things at my current job. I am going to cheat a little by copying a list from a renown SQL Server MVP named Paul Randal<\/a>. This list was published <\/a>in a TechNet Magazine article in November, 2010.<\/p>\n

To be a succesful database administrator, you should start working your way through the following tasks (secrets) to manage choas at your organization.<\/p>\n

\r\n10. Take Inventory\r\n9. Standardize Configurations\r\n8. Understand the I\/O Subsystem\r\n7. Create a Customized Maintenance Plan\r\n6. Ensure the Security of Your System\r\n5. Get on Good Terms with Your Developers\r\n4. Develop a Comprehensive Disaster Recovery Strategy\r\n3. Take and Test Regular Backups\r\n2. Monitor and Maintain Performance\r\n1. Know Where to Find Information\r\n<\/pre>\n<\/p>\n
In the coming weeks, I am going to write articles on how I choose to execute some of these tasks.<\/p>\n
For today, I want to start the discusion with a quick performance tool.  There are many times in which I want to figure out what is causing issues with a database.  I go into SQL Server Management Studio to pull up the activity monitor but nothing comes back since the CPU usage is extremely high.  <\/p>\n
What do you do when that happens?
\n<\/span><\/p>\n
There is a nice system stored procedures called sp_who <\/a>and sp_who2 that will return information on blocking and CPU usage; However, on a large database that has hundreds of processes running (SPID’s), it is very difficult to track down the exact entries you are looking for.<\/p>\n
I wrote a user defined stored procedure called usp_who2 which I installed in the msdb database.  Basically, this procedure creates a table in the tempdb given the users login.  Since I am logged in under the dba account, that is the name it will use for the table.  <\/p>\n
The output of sp_who2 is piped to this table.  A simple SELECT statement with a WHERE clause can narrow down the process that your are looking for in seconds.  Please note, spids under 50 are usually system processes.<\/p>\n
The following stored procedure is for your usage.  Like many shops, I have a couple SQL Server 2000 systems.  Microsoft’s transition from 2000 to 2005 made changes to this system stored procedure.  Below are the two variants.<\/p>\n
SQL SERVER 2000 <\/a>
\nSQL SERVER 2005+<\/a> <\/p>\n
A sample call to the procedure with verbose on tells you the name of the temporary table.<\/p>\n
\r\n-- Call proc with verbose on\r\nmsdb.dbo.usp_who2 1\r\n\r\n-- Check the following table for output\r\n[usp_who2] - please select from table [tempdb].[dbo].[dba_login] for your output.\r\n\r\n-- Track down the issue by user computer\r\nselect * from [tempdb].[dbo].[dba_login] a where a.host_name LIKE '%the_user_computer%'\r\n<\/pre>\n<\/p>\n
The table created by this procedure stays in the system until tempdb is recreated upon server startup.  I found this procedure very handy when trashing on the database system does not allow the activity monitor to load data.<\/p>\n","protected":false},"excerpt":{"rendered":"
Today, I am going to start writing another topic thread for Database Administration (DBA). I do alot of DBA work among many other things at my current job. I am going to cheat a little by copying a list from a renown SQL Server MVP named Paul Randal. This list was published in a TechNet Magazine article in November, 2010. To be a succesful database administrator, you should start working your way through the following tasks (secrets) to manage choas at your organization. 10. Take Inventory 9. Standardize Configurations 8.…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[117,12,15,118,28],"class_list":["post-1000","post","type-post","status-publish","format-standard","hentry","category-db-admin","tag-database-administrator","tag-free-code","tag-john-f-miner-iii","tag-sp_who2","tag-sql-server"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/1000","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1000"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/1000\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1000"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1000"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1000"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}