Roy Ashbrook

let go or be dragged
posts - 197, comments - 29, trackbacks - 8


print commands to give a group dbo in all the sql dbs

something silly i need to do every once in a while and i forget how to do it. the double double single quotes always mess me up =P   sp_msforeachdb 'print ''EXEC ?.dbo.sp_addrolemember N''''db_owner'''', N''''<GROUPNAME>'''' '''

posted @ Tuesday, January 12, 2010 4:15 PM | Feedback (2) | Filed Under [ DBA ]

delete objects belonging to a schema in sql

so you try to wack a crazy user who created stuff under his/her own schema in a sql database, but when you try to drop the schema, you get the evil 'something is owned' message. then you look and see they have a billion objects. booo..... oh well, not too big of a deal. you can use something similar to below. the below just drops syn's but you can put a case statement in the build portion based on type and drop everything you need to. obviously this can be tweaked some based on your taste.  declare @s nvarchar(max) while ( select count(*) from sys.objects obj join...

posted @ Monday, October 5, 2009 7:51 PM | Feedback (3) | Filed Under [ DBA ]

find sql users and roles assigned to my databases

thanks to this article for the base script. i needed to get a list of all users and the roles they had in each db. this script is great in that you can use the raw base script (mine is slightly modified) and list things for every object instead of just at the db level. in my case and in most people's case i imagine that makes this dataset rather large. this allowed me to get a large union on the raw data i needed for all of the databases and then i could put it one table so i...

posted @ Friday, September 5, 2008 7:19 PM | Feedback (0) | Filed Under [ DBA ]

how can i get some performance statistics on my stored procedures in sql2005?

select     case when dbid = 32767         then 'resource'         else db_name(dbid)     end [db_name]     , object_schema_name(objectid,dbid) [schema_name]     , object_name(objectid,dbid) [object_name]     , sum(usecounts) [executions]     , max(max_execution_time) [last_execution_time]     , sum(total_logical_reads) [total_logical_reads]     , sum(total_logical_reads) / sum(usecounts) * 1.0 [avg_logical_reads]     , sum(total_elapsed_time) / 1000 [total_elapsed_time_ms]     , ( sum(total_elapsed_time) / sum(usecounts) * 1.0 ) / 1000 [avg_elapsed_time_ms] from     sys.dm_exec_cached_plans cp     cross apply sys.dm_exec_sql_text(cp.plan_handle) qt     join (         select             plan_handle             , max(last_execution_time) [max_execution_time]             , sum(total_elapsed_time) [total_elapsed_time]             , sum(total_logical_reads) [total_logical_reads]         from             sys.dm_exec_query_stats         group by             plan_handle         ) r on r.plan_handle = cp.plan_handle where     objtype = 'Proc'    ...

posted @ Friday, March 21, 2008 8:57 PM | Feedback (0) | Filed Under [ DBA ]

SQL Server 2005 Emergency Diagnostic and Performance Queries

Great useful base diag scripts! Snipped from!45041418ECCAA960!893.entry -- SQL Server 2005 Emergency Diagnostic and Performance Queries-- Glenn Berry 3-17-2008 -- Step 1 - Check Task Manager. Are all CPUs above 90-95% for an extended period of time?-- If yes, run HIGH CPU queries below:-- Step 2 - Check Performance Monitor-- SQL Server Buffer Manager: Buffer Cache Hit Ratio and Page Life Expectancy-- SQL Server Memory Manager: Memory Grants Pending and Memory Grants Pending-- Physical Disk: Avg disk sec/Read and Avg disk sec/Write-- Step 3 - Check for locking, blocking and missing indexes-- Run the...

posted @ Friday, March 21, 2008 7:19 PM | Feedback (0) | Filed Under [ DBA ]

Full DBA Archive

Powered by: