Michael Berry

I am working on some policy based management issues where I need to run some tasks across my servers.  To do so would require me to create some one-the-fly linked servers that would have sysadmin rights and destroy themselves after they are finished.  Then I came upon this:

 http://blogs.msdn.com/sqlrem/archive/2008/02/04/multiple-server-query-execution-in-sql-server-2008.aspx

Now I can see myself running maintenance and scripts across servers with the greatest of ease.  I hope that Microsoft has this out of the box, secure and without bugs.  Is this too much to ask for?:)

Ever restore (a) database(s) which have users that are on the server you are restoring to?  When you look at the users, they dont have the database assigned to them, but they are in the security area of the database you restored.  This is a issue where the userids do not synch up from one server to another.  If you use my script it will go through the entire server and fix these issues.  This usually happens when doing database restores from prod to dev.  If there are no orphans, then the script wont return a thing!

set nocount on
go
 
DECLARE @databasename as varchar(200)
declare @strSQL as nvarchar (4000)
DECLARE Curse CURSOR local fast_forward
FOR
SELECT
name
FROM
master.dbo.sysdatabases
WHERE
name not in ('master', 'msdb', 'model', 'tempdb','AdventureWorks','AdventureWorksDW')
OPEN Curse
 
FETCH next FROM Curse INTO @databasename
WHILE @@fetch_status = 0
BEGIN
SET @strSQL = 'USE '+ @databasename + '
declare @usrname varchar(100), @command varchar(100)
declare Crs insensitive cursor for
select name as UserName from sysusers
where issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
order by name
for read only
open Crs
fetch next from Crs into @usrname
while @@fetch_status=0
begin
IF exists(select * FROM master..syslogins WHERE [name] = @usrname)
select @command='' sp_change_users_login auto_fix, '' + @usrname
print @command
exec(@command)
fetch next from Crs into @usrname
end
close Crs
deallocate Crs'
EXEC dbo.sp_executesql @strSQL
fetch next from Curse into @databasename
end
close Curse
deallocate Curse

Someone tells you that the DB is running slow.  What is the first thing you do?  Run sp_who2?  Fire up a trace? 

While these are all good things in themselves, a DBA needs a better tool!  I have one that I got from some website (so long ago, I dont recall where).  I call this proc NOW.  It can be run on any server at any time (I used it on both 2000 and 2005).  You need to have sysadmin rights to use it, but it will tell you THE EXACT STATEMENTS RUNNING - in a heatbeat!  It is great for knowing what procs are killing the server and which are using a lot of IO, etc.  The trick is to run it with text results, not in a grid!  It will put out an easy to read display that will give you no trouble:)

 Just use it once in text mode and let me know what you think of it?  Wish I could take credit for it!

USE [DBA]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[now]

as

set nocount on

declare @handle binary(20),

@spid smallint,

@rowcnt smallint,

@output varchar(500)

declare ActiveSpids CURSOR FOR

select sql_handle, spid

from master.dbo.sysprocesses

where sql_handle not in (0x0000000000000000000000000000000000000000)

and spid <> @@SPID

and (status not in('sleeping')

or upper(cmd) not in (

'AWAITING COMMAND'

,'LAZY WRITER'

,'CHECKPOINT SLEEP')

)

order by cpu desc

OPEN ActiveSpids

FETCH NEXT FROM ActiveSpids

INTO @handle,

@spid

 

set @rowcnt = @@CURSOR_ROWS

print '===================='

print '= CURRENT ACTIVITY ='

print '===================='

print ' '

set @output = 'ACTIVE SPIDS: ' + convert(varchar(4),@rowcnt)

print @output

 

WHILE (@@FETCH_STATUS = 0)

BEGIN

print ' '

print ' '

print 'O' + replicate('x',120) + 'O'

print 'O' + replicate('x',120) + 'O'

print ' '

print ' '

print ' '

select 'loginame' = left(loginame, 30),

'hostname' = left(hostname,30),

'database' = left(db_name(dbid),30),

'spid' = str(spid,4,0),

'block' = str(blocked,5,0),

'phys_io' = str(physical_io,8,0),

'cpu(mm:ss)' = str((cpu/1000/60),6) + ':' + case when left((str(((cpu/1000) % 60),2)),1) = ' ' then stuff(str(((cpu/1000) % 60),2),1,1,'0') else str(((cpu/1000) % 60),2) END ,

'mem(MB)' = str((convert(float,memusage) * 8192.0 / 1024.0 / 1024.0),8,2),

'program_name' = left(program_name,50),

'command' = cmd,

'lastwaittype' = left(lastwaittype,15),

'login_time' = convert(char(19),login_time,120),

'last_batch' = convert(char(19),last_batch,120),

'status' = left(status, 10),

'nt_username' = left(nt_username,20)

--into #working1

from master..sysprocesses

where spid = @spid

--and status <> 'sleeping'

print ' '

print ' '

 

-- Dump the inputbuffer to get an idea of what the spid is doing

dbcc inputbuffer(@spid)

print ' '

print ' '

-- Use the built-in function to show the exact SQL that the spid is running

select * from ::fn_get_sql(@handle)

 

FETCH NEXT FROM ActiveSpids

INTO @handle,

@spid

END

close ActiveSpids

deallocate ActiveSpids

I have  shared this code with others in the fourms but I think it is good enough for me to blog.  I create a proc that will go through every db on the server or one you sepcify and look through all code object (proc, view, function, etc) for a particular string you give it (@search).  This is one of the most powerful procs you can have.  The reason is clear.  For example, lets say you wrote a proc last month and you dont remember what you called it, if you remember anything that was in it, you can pass that string into the proc and it will find it.  It is used by every database developer I show it to.  Now it can be yours for free! 

 

create table FIND_WORKING (databasename varchar(15),name varchar(70), type varchar(30))

GO

use mydatabase

--*****************************************************************

-- Proc Name: Find

--

-- Purpose: This proc will find any string in all SQL code on this

-- server. It will not look in the data itself, but only

-- in code

--

-- Called By: anyone

--

-- Created By: Michael F. Berry

--

-- Created on: 12/19/2006

--

-- Modified By: Michael F. Berry

--

-- Modified Dte:1/25/2007

--

-- Modified Rsn:Make it put out into one main recordset for clarity

--*****************************************************************

CREATE Procedure [dbo].[Find]

(

@search varchar(150),

@DB varchar(200) = 'ALL'

)

as

set nocount on

--declare @search varchar(150)

--set @search = 'temp'

declare @databasename as varchar(200)

truncate table dba.dbo.find_working

select 'Searching For: '''+ @search + ''''

declare Curse cursor local fast_forward

for

select distinct

name

from

master.dbo.sysdatabases

where

name not in ('master', 'msdb', 'model', 'tempdb','AdventureWorks','AdventureWorksDW','indataDB_main','IndataDB_MarketValues','IndataDB_PriorMonth','PartnerControl','PartnerData')

and

(

@db = 'all'

or name = @db

)

open Curse

 

fetch next from Curse into @databasename

while @@fetch_status = 0

begin

exec ('use ' + @databasename + '

insert into DBA.dbo.find_working

select distinct

''' + @databasename + ''',

cast(o.[name] as varchar(40)) as objectname,

o.type -- left(c.text,50) as place

 

from

syscomments c

inner join

sysobjects o ON

c.[id] = o.[id]

 

where

c.[text] like ''%' +@search+ '%''

order by cast(o.[name] as varchar(40))

 

')

 

fetch next from Curse into @databasename

end

close Curse

deallocate Curse

select

databasename,

name,

type

from mydatabase.dbo.find_working

order by databasename,name

set nocount off

Put this code on a common database (or master) and put a proc in each db to call it so no matter what DB your in you can call it.  It will output the database name, code object name and type.  It is handy when you want to see what code touches a table that you are considering changing.  Very cool code.

Hi, 

I am Michael Berry and this is my new blog.  I will be writing about different ways to improve performance on your SQL Server.  I am a MCDBA in Ohio and I work for a large pension fund. I also have been published in the January 2008 edition of SQL Magazine.  Any comments you provide would be appreciated.  Please watch this space for great code ideas on SQL Server, RS, SSIS, etc  and geneal SQL geekdom.    Welcome and I hope you check in often.

 Michael Berry