Bora.Choi

Problem

In the course of any database development project, searching for database objects is something you will probably do.  It can be any kind of search which is done using some text as the search mechanism. Some of the examples of search that a developer / DBA may make within a database or database server are: searching for a database object, searching for occurance of particular text within database objects, searching within the schema of a database object, search within the results of query results or entire tables, etc..

In this tip, we look at different mechanisms that can be used to facilitate this type of searching.

Solution

Searching is generally required across database objects, across databases, and also across database servers. There are different mechanism that can be used for different kinds of search requirements.  For this tip I will be using SQL Server Management Studio (SSMS) 2008.

We will look at four different ways for our search that cover various search requirements.

1. SSMS Object Search
2. T-SQL Scripts
3. SQL Search Add-in
4. SSMS Tools Pack Add-in

1) SSMS Object Search:

As a part of the enhancements in SSMS 2008, object explorer details window has a nice object search toolbar which allows context sensitive searching of database objects. As show in the below screenshot, I attempted searching database object named "Address".  It fetched all the tables that it found with the same name. But there can be many database object having the text "Address" within it, or "Address" text can also appear in the definition of the database object. Also you can use '%' as a wild card character along with the text that you want to search in the same way as you use it with "like" for T-SQL.



2) T-SQL Scripts:

This one is the most flexible to search anything you would like to search within your database server. Using this mechanism, you have the flexibility to interrogate any schema or data level information that you would like, but the only down-side is that you need to create code and create a set of scripts to search across databases. You can create parameterized stored procedures to faciliate the search. But if a change is required, either you need to create your stored procedures with all the parameters based on how you would like to slice and dice your search or you will need to change the code.

In the below screen-shot, I attempted searching the text "Address" within "AdventureWorks" database. I found a stored-procedure containing "Address" text within it's name. Then I created a query to search text "Address" within all the stored-procedures in this database. There is no limit to the kind of scripts that can be created for searching using this technique as long as you know which catalog views to use.



3) SQL Search Add-in:

This is a free SSMS add-in from Red Gate Software and can be downloaded from here. Once installed, it becomes visible on the toolbar. This search requires no explanation and the benefit is that you just type the text and see the results. You can also limit the scope for a database or type of database objects as per your requirement. It also shows you if the text you are searching for appeared in the name of the database object and definition of the database object.

In the screenshot below, you can see that I attempted searching "Address" keyword within "AdventureWorks" database, and then tried locating the stored procedure that we looked at using the T-SQL Script. It shows this stored procedure twice in the search result, as "Address" is a part of the name of this stored procedure and also "Address" appears in the definition of the stored procedure. Not only does it list the object, but also highlights the places where the text appeared in the definition.

The only downside is that it won't can not copy the search results from the grid. I have requested this feature from Red Gate Software and I heard back that there is a good possibility that this feature will get added.



4) SSMS Tools Pack Add-in:

This is another free SSMS add-in and can be downloaded from here. After installation, it can be accessed from the object explorer by right-clicking as shown below.  Also at context specific areas, it is availalbe by right-clicking. If we extend our search to the next level, say we want to search the entire database including all tables and every column for a text that contains "Napa", and we need a report of the same. See the screen-shot below, where I did a right-click on database and did a search on the entire database data for any text that contains the word "Napa". This add-in has a lot of featuers, I leave it to the reader to browse the rest of these features.

복제 삭제

• 방법 1: http://msdn.microsoft.com/ko-kr/library/ms152757.aspx
• 방법 2: http://support.microsoft.com/kb/324401

• 이상하게 잘 못 삭제했을 경우

2. select replinfo,* from sysobjects where replinfo = 0 을 찾아서 입력합니다.

3.           sp_removedbreplication [ [ @dbname = ] 'dbname' ]
                 [ , [ @type = ] type ]
             

4. 1. [ @dbname=] 'dbname'

   1. 데이터베이스의 이름입니다. dbname은 sysname이며 기본값은 NULL입니다. NULL인 경우 현재 데이터베이스를 사용합니다.

   1. [ @type = ] type

   1. 데이터베이스 개체를 제거 중인 복제의 유형입니다. type은 nvarchar(5)이며 다음 값 중 하나일 수 있습니다.

   tran	트랜잭션 복제 게시 개체를 제거합니다.
   merge	병합 복제 게시 개체를 제거합니다.
   both(기본값)	모든 복제 게시 개체를 제거합니다.

ERROR CASE

게시& 배포자가 먼저 삭제되고 구독이 남아 있을 경우

 

CPU에 해당하는 Task 보기

select  
	 ss.scheduler_id ,ss.parent_node_id,object_name(sql_text.objectid, sql_text.dbid)  as 'SP명'
	,ses.session_id , ses.host_name , ses.program_name , ses.client_interface_name 
	,ses.login_name 
	 ,substring(sql_text.text,sr.statement_start_offset/2,
			(case when sr.statement_end_offset = -1
			then len(convert(nvarchar(max), sql_text.text)) * 2
			else sr.statement_end_offset end - sr.statement_start_offset)/2) as query
from sys.dm_os_schedulers as ss with (nolock) 
inner join sys.dm_os_tasks as st with (nolock) on ss.scheduler_id = st.scheduler_id
inner join sys.dm_exec_requests as sr with (nolock) on st.request_id = sr.request_id
inner join sys.dm_exec_sessions as ses with (nolock) on sr.session_id = ses.session_id 
cross apply sys.dm_exec_sql_text(sr.sql_handle) as sql_text
-- where ss.parent_node_id = 2 -- numa 노드에서 실행되는 쿼리
-- where ss.scheduler_id = 24  -- cpu 스케줄에 할당된 쿼리
 

작업으로 인해 배포자 agent를 중지하고 작업 처리후 시작하는 것을 잊어버리는 수가 있다. 혹은 다른 문제로 인해 배포 agent가 실행되고 있지 않을때,

오래 동안 실행되지 않으면 복제가 실패되어 다시 구축해야 하는 경우가 발생한다.

 

아래 쿼리는 배포 agent 실행여부를 확인하여 메일이나 SMS를 받을 수 있게 처리하는 것이다.

 

이또는 해당 job이 실행되고 있거나 오래 실행되는것도 찾을 수 있다.

 

[code sql]

DECLARE @is_sysadmin INT 
DECLARE @job_owner sysname 
DECLARE @job_id uniqueidentifier 
DECLARE @job_name sysname 
DECLARE @running int 
DECLARE @cnt int 
DECLARE @msg varchar(8000) 
DECLARE @msg_header varchar(4000) 
DECLARE @categoryid int 


SELECT @job_owner = SUSER_SNAME() 
,@is_sysadmin = 1 
,@running = 0 
,@categoryid = 10 -- Distributor jobs 


CREATE TABLE #jobStatus (job_id UNIQUEIDENTIFIER NOT NULL, 
last_run_date INT , 
last_run_time INT , 
next_run_date INT , 
next_run_time INT , 
next_run_schedule_id INT , 
requested_to_run INT , 
request_source INT , 
request_source_id sysname COLLATE database_default NULL, 
running int , 
current_step INT , 
current_retry_attempt INT , 
job_state INT) 


INSERT INTO #jobStatus 
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner--, @job_id 
--
select j.name, js.command, jss.running
from msdb.dbo.sysjobsteps js
join msdb.dbo.sysjobs j on js.job_id = j.job_id
join #jobStatus jss on js.job_id = jss.job_id
where step_id = 2 and subsystem = 'Distribution'
and command like '%-Continuous'
and jss.running <> 1 -- Not running



[/code]

Attach 스크립트 만들기

 

 

--==========================================
--DB 복원
--==========================================
SET NOCOUNT ON
DECLARE @sql            nvarchar(max)
DECLARE @sql_move       nvarchar(max)
DECLARE @move           nvarchar(200)
DECLARE @dbid           int


SET @sql_move = ''
SET @sql = ''
DECLARE cur_restore CURSOR FOR
      SELECT dbid
            , 'CREATE DATABASE ' + name + ' ON' + char(13)
      FROM sys.sysdatabases WHERE dbid > 4 and NAME != 'LiteSpeedLocal' 
      ORDER BY name

OPEN cur_restore
FETCH NEXT FROM cur_restore
INTO @dbid, @sql_move

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @move = ''

        DECLARE cur_move CURSOR FOR
            
            select '(NAME = ''' + name + ''', FILENAME = ''' + filename + '''),' 
            from sys.sysaltfiles
            where dbid = @dbid

        OPEN cur_move
        FETCH NEXT FROM cur_move
        INTO @move


        WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @sql_move = @sql_move + @move + char(13)

            FETCH NEXT FROM cur_move
            INTO @move
        END
        CLOSE cur_move
        DEALLOCATE cur_move


    SET @sql = @sql + @sql_move + 'FOR ATTACH' + char(13)
  --  select @sql
    print @sql
    set @sql = ''
    FETCH NEXT FROM cur_restore 
    INTO @dbid, @sql_move

END
CLOSE cur_restore
DEALLOCATE cur_restore

테이블의 rowcount와 예약된 공간, 인덱스 공간 사이즈.

sp_spaceused 를 사용해도 되지만, 테이블 여러개를 한번에 조회시 사용하면  좋다.

 

--==================================
-- DB 테이블 목록, row수 ceusee
-- =================================
DECLARE @tablename sysname
SET @tablename = ''
SELECT sys.name, ind.row_count
    ,convert(int,(sum(convert(numeric(15,2),reserved) * m.low /1024))) as 'reserved(KB)'
    ,convert(int,(sum(convert(numeric(15,2),used) * m.low /1024))) as 'indexp(KB)'
FROM sys.sysobjects as sys 
    join sys.dm_db_partition_stats as ind on sys.id = ind.object_id
    join sysindexes as i on  i.id = ind.object_id
    ,master.dbo.spt_values  as m
WHERE sys.type = 'U' and ind.index_id < 2 
    and m.number = 1 and m.type = 'E'
    and i.indid in (0,1,255)
    and sys.name  = @tablename
)
GROUP BY sys.name, ind.row_count

현재 존재하는 Index 스크립트 생성.

T-SQL::List all indexes in database  에 관련 함수, view 생성해야함.

 

-- INDEX Script
SELECT  
    CASE WHEN T.TABLE_NAME IS NULL THEN 
        'CREATE ' 
        + CASE IS_UNIQUE WHEN 1 THEN ' UNIQUE' ELSE '' END 
        + CASE IS_CLUSTERED WHEN 1 THEN ' CLUSTERED' ELSE '' END 
        + ' INDEX [' + INDEX_NAME + '] ON [' + v.TABLE_NAME + ']' 
        + ' (' + COLUMN_LIST + ') ON ' + FILE_GROUP 
    ELSE 
        'ALTER TABLE ['+T.TABLE_NAME+']' 
        +' ADD CONSTRAINT ['+INDEX_NAME+']' 
        +' PRIMARY KEY ' 
        + CASE IS_CLUSTERED WHEN 1 THEN ' CLUSTERED' ELSE '' END 
        + ' (' + COLUMN_LIST + ')' 
    END 
FROM 
    dbo.vAllIndexes v 
LEFT OUTER JOIN 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS T  
ON 
    T.CONSTRAINT_NAME = v.INDEX_NAME 
    AND T.TABLE_NAME = v.TABLE_NAME  
    AND T.CONSTRAINT_TYPE = 'PRIMARY KEY' 
ORDER BY 
    v.TABLE_NAME, 
    IS_CLUSTERED DESC

데이터 베이스에 있는 모든 인덱스 정보를 보기 위함.

sys.indexes, sys.index_columns, sys.columns  정보를 보면 복합 인덱스 일경우 row가 한개 이상으로 나타나서 한눈에 보기가 힘들다.

select  t.name as table_name ,ind.name as index_name, 
        ind.index_id, ic.index_column_id, col.name as column_name 
from    sys.indexes ind
inner join    sys.index_columns ic on ind.object_id = ic.object_id and ind.index_id = ic.index_id
inner join    sys.columns col on  ic.object_id = col.object_id and ic.column_id = col.column_id 
inner join    sys.tables t on  ind.object_id = t.object_id
where   ind.is_primary_key = 0   
    and ind.is_unique = 0     
    and ind.is_unique_constraint = 0    
    and t.is_ms_shipped = 0
order by    t.name, ind.name, ind.index_id, ic.index_column_id
 

그래서 다음과 같이 처리한다.

CREATE FUNCTION dbo.GetIndexColumnOrder 
( 
    @object_id INT, 
    @index_id TINYINT, 
    @column_id TINYINT 
) 
RETURNS NVARCHAR(5) 
AS 
BEGIN 
    DECLARE @r NVARCHAR(5) 
    SELECT @r = CASE INDEXKEY_PROPERTY 
    ( 
        @object_id, 
        @index_id, 
        @column_id, 
        'IsDescending' 
    ) 
        WHEN 1 THEN N' DESC' 
        ELSE N'' 
    END 
    RETURN @r 
END ;

CREATE FUNCTION dbo.GetIndexColumns 
( 
    @table_name SYSNAME, 
    @object_id INT, 
    @index_id TINYINT 
) 
RETURNS NVARCHAR(4000) 
AS 
BEGIN 
    DECLARE 
        @colnames NVARCHAR(4000),  
        @thisColID INT, 
        @thisColName SYSNAME 
         
    SET @colnames = '[' + INDEX_COL(@table_name, @index_id, 1) + ']'  
        + dbo.GetIndexColumnOrder(@object_id, @index_id, 1) 
 
    SET @thisColID = 2 
    SET @thisColName = '[' + INDEX_COL(@table_name, @index_id, @thisColID) + '] '
        + dbo.GetIndexColumnOrder(@object_id, @index_id, @thisColID)
 
    WHILE (@thisColName IS NOT NULL) 
    BEGIN 
        SET @thisColID = @thisColID + 1 
        SET @colnames = @colnames + ', ' + @thisColName 
 
        SET @thisColName = INDEX_COL(@table_name, @index_id, @thisColID) 
            + dbo.GetIndexColumnOrder(@object_id, @index_id, @thisColID) 
    END 
    RETURN @colNames 
END ;

 

CREATE VIEW dbo.V_ALLINDEXES 
AS
SELECT TABLE_NAME = OBJECT_NAME(i.id), 
      INDEX_NAME = i.name, 
      COLUMN_LIST = dbo.GetIndexColumns(OBJECT_NAME(i.id), i.id, i.indid), 
      IS_CLUSTERED = INDEXPROPERTY(i.id, i.name, 'IsClustered'),
      IS_UNIQUE = INDEXPROPERTY(i.id, i.name, 'IsUnique'), FILE_GROUP = g.GroupName 
FROM sysindexes i
INNER JOIN sysfilegroups g ON i.groupid = g.groupid
WHERE (i.indid BETWEEN 1 AND 254) -- leave out AUTO_STATISTICS: 
 AND (i.Status & 64)=0 -- leave out system tables: 
 AND OBJECTPROPERTY(i.id, 'IsMsShipped') = 0 ; 

디스크의 남은 용량은 xp_fixeddrives 를 이용해서 구할 수 있지만 디스크 전체 사이즈를 쉽게 얻기는 쉽지 않다.

여러 방법이 있었는데 그중 OLE 사용하는 방법이다.

 

이 방법을 사용하기 위해서는 기능 영역에 OLE 자동화 사용이 활성화 되어 있어야 한다.

서비스 되고 있는 DB 장비는 이 기능을 ON 해 놓지 않기에 평범하게 사용할 수는 없다.

 

(클릭해서 크게 보세요)

 

Deadlock 발생시 원인과 해결법.

 

1. Deadlock 이유를 알고 싶으면 trace 1204 를 켜 준다.

 

2. Deadlock 발생되면 SQL의 에러로그에 로그가 남게 된다.

 

3.  에러로그를 확인해 보면 'Example_stored_proc'  프로시저에서 update 시 exclusive lock  이 발생하고 있다는 것을 확인 할 수 있다.

 

4. sp_helptext 'Example_stored_proc'  를 실행하여 프로시저의 내용을 확인 한다.

 

5. 이와 관련있는 프로시저를 확인하고 deadlock 이 발생하는 page를 확인할 수 있다.

Node:1 PAG: 9:1:18061 CleanCnt:2 Mode: SIU Flags: 0x2

 

6. 해당 페이지를 조사해 본다. , DBCC Page 사용

MSDN : http://support.microsoft.com/kb/83065

 

object_id를 알아 낼 수 있다.

 

7.  object 확인

 

Use DBNAME
Select object_name(OBJECT_ID)

8. 프로시저에서 해당 object를 확인하고  update 문을 찾아 그대로 update 테스트 해 본다.

이때, 해당 컬럼에 인덱스가 있는지 확인 한다. update 시 인덱스가 없으면 table scan 을 하게되고 exclusive lock 이 발생한다.

 

9. Key 되는 컬럼에 인덱스를 생성 해 준다.

 

10. 후에 다시 실행해봐서 deadlock 이 발생하는지 확인 한다.

 

 

복제를 구성한 후 article 속성을 셋팅해서 게시 한 경우 추후 Aritical 속성을 확인할때 UI를 통한 확인도 가능하지만 부하가 있는 서버의 복제를 확인할때 UI가 응답 없음으로 될 경우가 있다.

 

sysarticles 테이블의 schema_option 값에 바이너리 값으로 속성값이 저장되며, 도움말 확인하여 연산해서 속성 확인 가능하다.

 

http://msdn.microsoft.com/ko-kr/library/ms173857.aspx

 

(트랜잭션 복제 기준)