A DBAs List of Little Things

by Jason Brimhall
Categories: Meme Monday, News, Professional, SSC
Tags: , ,
Comments: 1 Comment
Published on: March 5, 2012

Today is Meme Monday.  Today we get to talk about all of the little things a DBA does.  Thomas LaRock started things off with his list – here.

I want to just add to the list he started.

  1. SAN Admin
  2. Server Admin
  3. Technical Writer
  4. Project Manager
  5. Automate the process to Migrate Data from Production to Dev/Test/QA
  6. Automate the process to Migrate Data from Production to Reporting Servers
  7. Export and Email/FTP/Transfer Data to customers
  8. Attend Sales meetings
  9. Predict customer requests before the request is made
  10. Create standards pertaining to the database environment
  11. Document
  12. Document
  13. Document
  14. Domain Admin
  15. Administer excel spreadsheets
  16. Administer Cognos, Crystal Reports, and Access (or any other tool that may provide a reporting interface for the database)
  17. Create Data warehouse
  18. Improve your skills

I really like the bullet item “therapist” from Tom’s list.  There is a lot of truth to that.

Are my Linked Servers Being Used?

by Jason Brimhall
Categories: News, Professional, Scripts, SQLServerPedia Syndication, SSC
Tags: , ,
Comments: No Comments
Published on: March 5, 2012

This is a follow up to an article published on 3/1/2012.  That article showed how to find what linked servers were created on your instance of SQL Server.  You can read it here.

This article came about due to a request to find if any stored procedures are using any of the linked servers.  In addition to finding if any stored procedures may be using a linked server was the need to find the name of that procedure.  The request evolved to also include finding any SQL Agent jobs that may be using the linked server.

In response to that request, I had the idea to adapt a query I had recently written.  I will be posting that in the near future.

Query

DECLARE @VName VARCHAR(256)
DECLARE Findlinked CURSOR
LOCAL STATIC FORWARD_ONLY READ_ONLY
     FOR
SELECT name AS name
	FROM sys.servers
	WHERE is_linked = 1
 
OPEN Findlinked;
FETCH NEXT FROM Findlinked INTO @VName;
 
WHILE @@FETCH_STATUS = 0
BEGIN
	SELECT OBJECT_NAME(OBJECT_ID)
		FROM sys.sql_modules
		WHERE Definition LIKE '%'+@VName +'%'
		AND OBJECTPROPERTY(OBJECT_ID, 'IsProcedure') = 1 ;
 
	FETCH NEXT FROM Findlinked INTO @VName;
END
CLOSE Findlinked
 
OPEN Findlinked;
FETCH NEXT FROM Findlinked INTO @VName;
 
WHILE @@FETCH_STATUS = 0
BEGIN
	SELECT j.name AS JobName,js.command
		FROM msdb.dbo.sysjobsteps js
			INNER JOIN msdb.dbo.sysjobs j
				ON j.job_id = js.job_id
		WHERE js.command LIKE '%'+@VName +'%'
	FETCH NEXT FROM Findlinked INTO @VName;
END
 
CLOSE Findlinked
DEALLOCATE Findlinked

Don’t blast me just yet due to the use of a loop (cursor).  In a case such as what has just been presented, a cursor is a legitimate tool.  I need to find all stored procedures that contain the text I specify.  In this case, I am searching for each of the linked servers.

In addition to searching all of the stored procedures for the use of a defined linked server, I am re-opening the same cursor to search all of the jobs defined on the server.  I do this in the event that the job was created with an ad-hoc query in lieu of using a stored procedure.

If you run the query, you will find that it should provide a quicker turnaround time on documenting the use of the linked servers than manually searching.

Linked Servers

by Jason Brimhall
Categories: News, Professional, Scripts, SSC, SSSOLV
Tags: , ,
Comments: No Comments
Published on: March 1, 2012

Do you have linked servers that have been created in your environment?  Do you know what these linked servers are?

Finding Linked Servers

SQL Server has provided us means to be able to find the linked servers on each installed instance.  I will show two simple means (that don’t involve traversing the SSMS GUI) that will list the linked servers.

Both methods are very similar in nature.  I will leave it up to you to determine which you prefer to use.

 

Stored Procedure

Microsoft has shipped a stored procedure called sp_linkedservers.  Execute this stored procedure and you will get a list of linked servers and the details related to those objects.  One problem with this method is that not all the results returned by this proc truly represent linked servers.  This procedure will also return the name of the instance to which you are also connected.

Catalog Query

This is really what the stored procedure is doing.  But here is a modified query to return only those servers that are linked servers in the catalog.

SELECT SRV_NAME = srv.name,
        SRV_PROVIDERNAME    = srv.provider,
        SRV_PRODUCT         = srv.product,
        SRV_DATASOURCE      = srv.data_source,
        SRV_PROVIDERSTRING  = srv.provider_string,
        SRV_LOCATION        = srv.location,
        SRV_CAT             = srv.CATALOG
	FROM sys.servers srv
	WHERE is_linked = 1

The main difference being the where condition.  Querying the sys.servers catalog with a condition of is_linked = 1 will return only those servers that are linked servers.  Getting this information is a pre-requisite for an upcoming post.  I hope you find this information useful.

 

Goals 2012

by Jason Brimhall
Categories: News, Professional, SSC
Tags:
Comments: No Comments
Published on: February 8, 2012

As I have wanted to do each year, here is my goals post for 2012.  Last year I set out to do this very same thing but was very unsuccessful at completing the post.  Here is all I had written:

Personal

  • Family
  • Blogging
  • Build out my Virtual Environment

Professional

  • SSIS
  • SSRS
  • SSAS
  • Blogging
  • Writing (articles)
  • Community
  • Presentations

Education

  • Certification
  • Books

Fitness

  • Running
  • Exercise

This year, I will do much better.  As proof of that goal, this post will get published – one way or another.  Despite not having published an actual list of goals, I think I did fairly well with this list.  I wrote a few articles.  I was able to maintain a steady pace with my blog.  I completed my MCITP for both Admin and Dev tracks in 2008.  I became very familiar with SSRS and learned a substantial amount with SSIS.

So, as I set out to establish my goals, I decided to keep the list very short.

Professional Development

I will finish the MCITP for the BI track in 2008.  I plan to have this done by March 2012.

I will study for and take the MCM written and Lab exams for SQL 2008.  I will take more time to achieve this goal but will have it done by year end.

Continue writing.  I will continue to contribute articles for publication as well as write on my blog.  I hope to also do some writing professionally.

Personal

I have several rooms in the basement to finish.  I plan on finishing the family room and my office.

Community

I will continue to volunteer at the UG level as well as at the PASS level.  I like volunteering with PASS and helping to achieve the greatness that is PASS Summit.

Despite some setbacks already, I will push forward and get a SQLSaturday event to happen in Las Vegas.

There you have it.  Nothing too elaborate.  I’m trying to keep the goals short and simple without overly complicated detail.  I think this year will be a successful year as well.

S3OLV – Get your Merge On

by Jason Brimhall
Categories: News, Professional, SSC, SSSOLV
Tags: ,
Comments: No Comments
Published on: February 7, 2012

We are rapidly closing in on the February meeting for S3OLV.  You might remember reading about the upcoming meeting here.

We are looking to have an excellent meeting this month.  We have had a good run on superb presentations, and Troy aims to keep us going in the right direction.

Troy will be teaching us how to use the Merge statement.  He will help us to learn how this statement can help improve our queries.

We will also continue our ugly code segment.  Bring your ugly code.  Send your ugly code to admin at s3olv dot com.  We are doing this segment in an effort to have the group help each other and learn from each other.  Honestly, it is also an effort to help drive up group interaction.

Here is the information for the meeting.

Date: February 9, 2012

Time: 6:30 PM Pacific

Virtual Meeting Info

Attendee URL: https://www.livemeeting.com/cc/UserGroups/join?id=H3ZGRQ&role=attend

Meeting ID:  H3ZGRQ

Physical Meeting Info

M Staff Solutions & Training / 2620 Regatta Drive Suite 102 Las Vegas, NV 89128

«page 3 of 57»
Calendar
May 2012
M T W T F S S
« Apr    
 123456
78910111213
14151617181920
21222324252627
28293031  
Follow me on Google+

In 0 people's circles

Add to circlesi
Content
Categories

Categories

Popular Posts

Popular Posts

Blogroll

Blogroll

Now Reading

Now Reading

Planned books:

Current books:

  • ChiRunning: A Revolutionary Approach to Effortless, Injury-Free Running

    ChiRunning: A Revolutionary Approach to Effortless, Injury-Free Running by Danny Dreyer, Katherine Dreyer

  • Advanced Marathoning – 2nd Edition

    Advanced Marathoning – 2nd Edition by Peter Pfitzinger, Scott Douglas

  • SQL Server MVP Deep Dives

    SQL Server MVP Deep Dives by Nielsen Paul, Delaney Kalen, Machanic Adam, Tripp Kimberly, Randal Paul, Low Greg

  • A World Without Heroes (Beyonders)

    A World Without Heroes (Beyonders) by Brandon Mull

Recent books:

View full Library

SQLHelp

SQLHelp


Recent Posts
  • Move the T-log file of the … by Jason Brimhall

    On more than one occasion, I have run into a case where a large database with a large transaction log was being mirrored.  Then a hiccup comes along – the transaction log on the primary server grows to a point where the transaction log on the secondary server fills the [...]

  • SQL 2012 has Launched by Jason Brimhall

    LAUNCH If you have been hiding under a rock, you have probably not heard that SQL 2012 has launched.  With it, some really cool features are now available to use in our SQL database environments. Some of these cool new features include some new DMVs, the AlwaysOn feature, and columnstore [...]

  • Seldom Used Indexes by Jason Brimhall

    On occasion you may ask yourself if there are any under used indexes in your database.  If not you, then possibly a manager or client.  Usually this comes up when evaluating for missing indexes or better indexes. SQL Server provides a means to find the information we seek.  We can [...]

  • Another Interesting Sort by Jason Brimhall

    In October of 2011, I shared an example of a peculiar set of sort requirements.  Today, I am going to share another similar set of requirements.  Based on prior experience, when I saw this request on a sort order, I was sure I could find a simpler solution.  Today, I [...]

  • Agent Jobs Using SSIS by Jason Brimhall

    This is a short script to help the DBA with documentation purposes.  This would come in handy especially in those cases where you are consulting or you have taken on a new job. Suppose you want/need to find out what SQL Agent jobs are running SSIS packages.  This script will [...]

  • Table Size and Missing FK Indexes by Jason Brimhall

    I am bringing an oldie back with another twist.  I recently ran into the need to correlate information between a couple of different queries that I like to use.  The two scripts returned different pieces of data about tables in a database.  This information was table size and missing foreign [...]

  • T-SQL Tuesday #028 – Jack o… by Jason Brimhall

    Another month and another opportunity to write about an interesting topic.  This month hosting TSQL Tuesday is Argenis Fernandez (Blog | Twitter). This month, Argenis has invited us to talk about demons from our past.  Ok, not necessarily demons but at least share why you might be a Jack of [...]

  • What is a Favicon? by Jason Brimhall

    If I were to show you the tabs in the following pic without a description, would you recognize the websites to which they belong? I would dare say that you would likely recognize them.  Not due to the labels on the tabs, but quite possibly due to the icon on [...]

  • Physical or Virtual by Jason Brimhall

    Today I get to share something that has been on my someday list for quite some time.  I have planned on getting a solution that involves TSQL to help determine if you are running SQL Server on a physical machine or if it happens to be a virtual machine.  Now, [...]

  • Views in Use? by Jason Brimhall

    Today, I am following up on a topic I mentioned in an earlier post.  In case you haven’t read the post on finding your linked servers, you can read it here. It is in that post that I spoke of altering a script I had just written for another client [...]

Categories
Recent Comments
  • Jason Brimhall about Move the T-log file of the …
    Thanks Robert.
  • Robert Pearl about Move the T-log file of the …
    Nice tip, Jason! Going to share it on DBA.Connect, and other Linkedin groups....
  • Ron Moses about Seldom Used Indexes
    Thanks Jason. I know for certain that none of our queries use hints, so that's a non-issue. But ...
  • Jason Brimhall about Seldom Used Indexes
    The script can certainly be changed to look at that. I did this script knowing that I only wanted ...
  • Ron Moses about Seldom Used Indexes
    Okay, bear with me because I might be a wee bit dense. I'm a bit of a n00b. "I want ...
  • Jason Brimhall about Seldom Used Indexes
    The purpose for that condition is to eliminate all indexes that have been used in some fashion. In this ...
  • Ron Moses about Seldom Used Indexes
    I do have a follow-up question, if you don't mind. What exactly is the purpose of this line: And (user_seeks ...
  • Jason Brimhall about Seldom Used Indexes
    Thanks. I am glad you said test. I can't stress enough the importance to research and test before ...
  • Ron Moses about Seldom Used Indexes
    One of my scheduled tasks for today was to work on a script to do exactly this. Thanks for ...
  • Jason Brimhall about T-SQL Tuesday #028 – Jack o…
    Muddy is an interesting way of stating it. Seems like something else is more fitting;)
Welcome , today is Friday, May 18, 2012