블로그 이미지
bedbmsguru

Notice

Recent Post

Recent Comment

Recent Trackback

Archive

calendar

 « 2018.10  »
일 월 화 수 목 금 토
1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30 31
  • total
  • today
  • yesterday

bufferpool(버퍼풀) 메모리 사용량

2018. 10. 27. 22:37 SQL SERVER

--DB별 버퍼풀 사용량
SELECT 
    DB_NAME(database_id) AS [Database Name]
    ,CAST(COUNT(*) * 8/1024.0 AS DECIMAL (10,2))  AS [Cached Size (MB)]FROM sys.dm_os_buffer_descriptors WITH (NOLOCK)
WHERE database_id not in (1,3,4) -- system databases
AND database_id <> 32767 -- ResourceDB
GROUP BY DB_NAME(database_id)ORDER BY [Cached Size (MB)] 

DESC OPTION (RECOMPILE);

--DB에서 테이블별 버퍼풀 사용량
;WITH src AS
(
   SELECT
       [Object] = o.name,
       [Type] = o.type_desc,
       [Index] = COALESCE(i.name, ''),
       [Index_Type] = i.type_desc,
       p.[object_id],
       p.index_id,
       au.allocation_unit_id
   FROM
       sys.partitions AS p
   INNER JOIN
       sys.allocation_units AS au
       ON p.hobt_id = au.container_id
   INNER JOIN
       sys.objects AS o
       ON p.[object_id] = o.[object_id]
   INNER JOIN
       sys.indexes AS i
       ON o.[object_id] = i.[object_id]
       AND p.index_id = i.index_id
   WHERE
       au.[type] IN (1,2,3)
       AND o.is_ms_shipped = 0
)
SELECT
   src.[Object],
   src.[Type],
   src.[Index],
   src.Index_Type,
   buffer_pages = COUNT_BIG(b.page_id),
   buffer_mb = COUNT_BIG(b.page_id) / 128
FROM
   src
INNER JOIN
   sys.dm_os_buffer_descriptors AS b
   ON src.allocation_unit_id = b.allocation_unit_id
WHERE
   b.database_id = DB_ID()
GROUP BY
   src.[Object],
   src.[Type],
   src.[Index],
   src.Index_Type
ORDER BY
   buffer_pages DESC;

저작자표시

'SQL SERVER' 카테고리의 다른 글

tempdb 경합 모니터링  (0) 2018.10.27
SQL SERVER 로그  정보(사용량, usage)  (0) 2018.10.27
통계 업데이트 날짜 조회하기  (0) 2018.10.27
Procedure 실행횟수 확인  (0) 2018.10.27
실행중인 쿼리 확인  (0) 2018.10.27
posted by bedbmsguru

통계 업데이트 날짜 조회하기

2018. 10. 27. 22:37 SQL SERVER

SELECT object_name (sp. object_id) as object_name ,name as stats_name , sp .stats_id,
    last_updated, rows, rows_sampled, steps, unfiltered_rows, modification_counter
FROM sys .stats AS s
CROSS APPLY sys. dm_db_stats_properties(s .object_id, s. stats_id) AS sp
WHERE sp .object_id > 100;

그 이외버전
SELECT schema_name (schema_id) AS SchemaName,  object_name(o .object_id) AS ObjectName,
    i.name AS IndexName, index_id, o.type ,
    STATS_DATE(o .object_id, index_id) AS statistics_update_date
FROM sys .indexes i join sys. objects o
       on i .object_id = o .object_id
WHERE o .object_id > 100 AND index_id > 0
  AND is_ms_shipped = 0;
  

저작자표시

'SQL SERVER' 카테고리의 다른 글

SQL SERVER 로그  정보(사용량, usage)  (0) 2018.10.27
bufferpool(버퍼풀) 메모리 사용량  (0) 2018.10.27
Procedure 실행횟수 확인  (0) 2018.10.27
실행중인 쿼리 확인  (0) 2018.10.27
SERVER property를 일괄로 보여주는 procedure  (0) 2018.10.27
posted by bedbmsguru

로컬( local)계정 생성후 Administrator 그룹에 넣기

2018. 10. 27. 22:36 Powershell

 $ServerArray = "test-dev" , "test"    # place computername here for remote access
$username = '계정'
$password = '암호'
$desc = '백업용'


foreach ($server in $ServerArray)
{
    try
    {
       
        $computer = [ADSI ]"WinNT://$server ,computer"
        $user = $computer. Create("user", $username)
        $user.SetPassword( $password)
        $user.Setinfo()
        $user.description = $desc
        #$user.UserFlags = 65536  #암호사용기간 제한없음
        $user.PasswordExpired = 1 #다음번 로그인시 암호변경해야함
        $user.SetInfo()
        $group = [ADSI ]("WinNT:// $server/administrators,group")
        $group.add( "WinNT://$username,user" )

        Write-Host $server + "\t" + "완료"
    }
    catch
    {
        Write-Host $server + "`t" + $_. Exception.Message;
    }
}

저작자표시

'Powershell' 카테고리의 다른 글

AD계정 정보 가져오기  (0) 2018.12.26
Powershell 방화벽 관리하기  (0) 2018.10.27
TCP 소켓 통신 예제  (0) 2018.10.27
Powershell 외부서버의 스크립트 실행하기  (0) 2018.05.25
posted by bedbmsguru

Procedure 실행횟수 확인

2018. 10. 27. 22:34 SQL SERVER

SELECT CASE WHEN database_id = 32767 then 'Resource' ELSE DB_NAME( database_id)END AS DBName
      ,OBJECT_SCHEMA_NAME( object_id,database_id ) AS [SCHEMA_NAME]
      ,OBJECT_NAME( object_id,database_id )AS [OBJECT_NAME]
      ,sum( execution_count) AS Execution_Count
      ,sum( total_worker_time) / sum (execution_count) AS AVG_CPU
      ,sum( total_elapsed_time) / sum (execution_count) AS AVG_ELAPSED
      ,sum( total_logical_reads) / sum (execution_count) AS AVG_LOGICAL_READS
      ,sum( total_logical_writes) / sum (execution_count) AS AVG_LOGICAL_WRITES
      ,sum( total_physical_reads)  / sum (execution_count) AS AVG_PHYSICAL_READS
                  , GETDATE () AS write_time
FROM sys .dm_exec_procedure_stats
group by
CASE WHEN database_id = 32767 then 'Resource' ELSE DB_NAME (database_id) END
      ,OBJECT_SCHEMA_NAME( object_id,database_id )
      ,OBJECT_NAME( object_id,database_id )
ORDER BY AVG_LOGICAL_READS DESC
  


--정기적 체크를 위해 Batch Job으로 만들경우의 소스
CREATE TABLE [dbo]. [TBL_CHECK_PROC_EXECUTION_COUNT](
                 [DBName] [nvarchar] (128) NULL,
                 [SCHEMA_NAME] [nvarchar] (128) NULL,
                 [OBJECT_NAME] [nvarchar] (128) NULL,
                 [Execution_Count] [bigint] NULL,
                 [AVG_CPU] [bigint] NULL,
                 [AVG_ELAPSED] [bigint] NULL,
                 [AVG_LOGICAL_READS] [bigint] NULL,
                 [AVG_LOGICAL_WRITES] [bigint] NULL,
                 [AVG_PHYSICAL_READS] [bigint] NULL,
                 [write_time] [datetime] NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo]. [TBL_CHECK_PROC_EXECUTION_COUNT_TEMP] (
                 [DBName] [nvarchar] (128) NULL,
                 [SCHEMA_NAME] [nvarchar] (128) NULL,
                 [OBJECT_NAME] [nvarchar] (128) NULL,
                 [Execution_Count] [bigint] NULL,
                 [AVG_CPU] [bigint] NULL,
                 [AVG_ELAPSED] [bigint] NULL,
                 [AVG_LOGICAL_READS] [bigint] NULL,
                 [AVG_LOGICAL_WRITES] [bigint] NULL,
                 [AVG_PHYSICAL_READS] [bigint] NULL,
                 [write_time] [datetime] NOT NULL
) ON [PRIMARY]
  

create procedure usp_chk_proc_execute_count
AS
SET NOCOUNT ON;
INSERT INTO TBL_CHECK_PROC_EXECUTION_COUNT (DBName, [SCHEMA_NAME], [OBJECT_NAME], Execution_Count,
AVG_CPU, AVG_ELAPSED, AVG_LOGICAL_READS, AVG_LOGICAL_WRITES, AVG_PHYSICAL_READS, write_time)
SELECT A .DBName, B. [SCHEMA_NAME], B.[OBJECT_NAME] , B .Execution_Count - A .Execution_Count AS Execution_Count
,B. AVG_CPU,B .AVG_ELAPSED, B. AVG_LOGICAL_READS, B.AVG_LOGICAL_WRITES ,B. AVG_PHYSICAL_READS
, getdate () as write_time
FROM TBL_CHECK_PROC_EXECUTION_COUNT_TEMP AS A
inner JOIN
(
SELECT CASE WHEN database_id = 32767 then 'Resource' ELSE DB_NAME( database_id)END AS DBName
      ,OBJECT_SCHEMA_NAME( object_id,database_id ) AS [SCHEMA_NAME]
      ,OBJECT_NAME( object_id,database_id )AS [OBJECT_NAME]
      ,sum( execution_count) AS Execution_Count
      ,sum( total_worker_time) / sum (execution_count) AS AVG_CPU
      ,sum( total_elapsed_time) / sum (execution_count) AS AVG_ELAPSED
      ,sum( total_logical_reads) / sum (execution_count) AS AVG_LOGICAL_READS
      ,sum( total_logical_writes) / sum (execution_count) AS AVG_LOGICAL_WRITES
      ,sum( total_physical_reads)  / sum (execution_count) AS AVG_PHYSICAL_READS
                  , GETDATE () AS write_time
FROM sys .dm_exec_procedure_stats
group by
CASE WHEN database_id = 32767 then 'Resource' ELSE DB_NAME (database_id) END
      ,OBJECT_SCHEMA_NAME( object_id,database_id )
      ,OBJECT_NAME( object_id,database_id )
HAVING sum (execution_count) > 50000
)AS B
ON A .DBName = B .DBName
AND A .[OBJECT_NAME] = B .[OBJECT_NAME]

truncate table TBL_CHECK_PROC_EXECUTION_COUNT_TEMP

insert into TBL_CHECK_PROC_EXECUTION_COUNT_TEMP
SELECT CASE WHEN database_id = 32767 then 'Resource' ELSE DB_NAME( database_id)END AS DBName
      ,OBJECT_SCHEMA_NAME( object_id,database_id ) AS [SCHEMA_NAME]
      ,OBJECT_NAME( object_id,database_id )AS [OBJECT_NAME]
      ,sum( execution_count) AS Execution_Count
      ,sum( total_worker_time) / sum (execution_count) AS AVG_CPU
      ,sum( total_elapsed_time) / sum (execution_count) AS AVG_ELAPSED
      ,sum( total_logical_reads) / sum (execution_count) AS AVG_LOGICAL_READS
      ,sum( total_logical_writes) / sum (execution_count) AS AVG_LOGICAL_WRITES
      ,sum( total_physical_reads)  / sum (execution_count) AS AVG_PHYSICAL_READS
                  , GETDATE () AS write_time
FROM sys .dm_exec_procedure_stats
group by
CASE WHEN database_id = 32767 then 'Resource' ELSE DB_NAME (database_id) END
      ,OBJECT_SCHEMA_NAME( object_id,database_id )
      ,OBJECT_NAME( object_id,database_id )
--ORDER BY AVG_LOGICAL_READS DESC

저작자표시

'SQL SERVER' 카테고리의 다른 글

bufferpool(버퍼풀) 메모리 사용량  (0) 2018.10.27
통계 업데이트 날짜 조회하기  (0) 2018.10.27
실행중인 쿼리 확인  (0) 2018.10.27
SERVER property를 일괄로 보여주는 procedure  (0) 2018.10.27
Index 생성이 안되어 있는 Foreign key 찾기  (0) 2018.10.27
posted by bedbmsguru

실행중인 쿼리 확인

2018. 10. 27. 22:34 SQL SERVER
SELECT
SERVERPROPERTY('ServerName') AS sql_instance,
s.session_id
,r.STATUS
,CONVERT(varchar,r.start_time,20) start_time
,r.blocking_session_id
,r.wait_type
,wait_resource
,r.wait_time 
,r.cpu_time
,r.logical_reads
,r.reads
,r.writes
,r.total_elapsed_time 
,r.open_transaction_count
,Substring(st.TEXT, (r.statement_start_offset / 2) + 1, (
(
CASE r.statement_end_offset
WHEN - 1
THEN Datalength(st.TEXT)
ELSE r.statement_end_offset
END - r.statement_start_offset
) / 2
) + 1) AS statement_text
,Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) + N'.' + Quotename(Object_name(st.objectid, st.dbid)), '') AS command_text
,r.command
,s.login_name
,s.host_name
,s.program_name
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp
WHERE r.session_id != @@SPID
ORDER BY start_time
  

 

저작자표시

'SQL SERVER' 카테고리의 다른 글

통계 업데이트 날짜 조회하기  (0) 2018.10.27
Procedure 실행횟수 확인  (0) 2018.10.27
SERVER property를 일괄로 보여주는 procedure  (0) 2018.10.27
Index 생성이 안되어 있는 Foreign key 찾기  (0) 2018.10.27
링크드 서버(linked Server)  (0) 2018.10.26
posted by bedbmsguru

SERVER property를 일괄로 보여주는 procedure

2018. 10. 27. 22:33 SQL SERVER

alter  proc sp_list_server_property
AS
DECLARE @props TABLE ( propertyname sysname PRIMARY KEY)
INSERT INTO @props( propertyname )
SELECT 'BuildClrVersion'
UNION
SELECT 'Collation'
UNION
SELECT 'CollationID'
UNION
SELECT 'ComparisonStyle'
UNION
SELECT 'ComputerNamePhysicalNetBIOS'
UNION
SELECT 'Edition'
UNION
SELECT 'HadrManagerStatus'
UNION
SELECT 'EngineEdition'
UNION
SELECT 'InstanceName'
UNION
SELECT 'IsClustered'
UNION
SELECT 'IsFullTextInstalled'
UNION
SELECT 'IsIntegratedSecurityOnly'
UNION
SELECT 'IsSingleUser'
UNION
SELECT 'LCID'
UNION
SELECT 'LicenseType'
UNION
SELECT 'MachineName'
UNION
SELECT 'NumLicenses'
UNION
SELECT 'ProcessID'
UNION
SELECT 'ProductVersion'
UNION
SELECT 'ProductLevel'
UNION
SELECT 'ResourceLastUpdateDateTime'
UNION
SELECT 'ResourceVersion'
UNION
SELECT 'ServerName'
UNION
SELECT 'SqlCharSet'
UNION
SELECT 'SqlCharSetName'
UNION
SELECT 'SqlSortOrder'
UNION
SELECT 'SqlSortOrderName'
UNION
SELECT 'FilestreamShareName'
UNION
SELECT 'FilestreamConfiguredLevel'
UNION
SELECT 'FilestreamEffectiveLevel'
 
SELECT propertyname , SERVERPROPERTY ( propertyname ) FROM @props
 UNION ALL
SELECT 'EditionID' AS  propertyname,
                                 CASE SERVERPROPERTY ( 'EditionID' )
                                                 WHEN 1804890536 THEN 'Enterprise'
                                                 WHEN 1872460670 THEN 'Enterprise With CORE Base Liserence'
                                                 WHEN 610778273 THEN 'Enterprise Evaluation'
                                                 WHEN 284895786 THEN 'Business Intelligence'
                                                 WHEN - 2117995310 THEN 'Developer'
                                                 WHEN - 1592396055 THEN 'Express'
                                                 WHEN - 133711905 THEN 'Express with Advanced Services'
                                                 WHEN - 1534726760 THEN 'Standard'
                                                 WHEN 1293598313 THEN 'WEB'
                                 END AS EditionId
UNION ALL                                                                                                           
SELECT 'IsHadrEnabled' AS propertyname,
                                 CASE SERVERPROPERTY ( 'IsHadrEnabled' )
                                                 WHEN 0 THEN 'AlwaysOn 가용성 그룹 기능을 사용하지 않습니다.'
                                                 WHEN 1 THEN 'AlwaysOn 가용성 그룹 기능을 사용합니다'
                                 END AS IsHadrEnabled
UNION ALL                                                                                                           
SELECT 'HadrManagerStatus' AS propertyname,
                                 CASE SERVERPROPERTY ( 'HadrManagerStatus' )
                                                 WHEN 0 THEN '시작되지 않았습니다. 통신 보류 중입니다.'
                                                 WHEN 1 THEN '시작되어 실행 중입니다.'
                                                 WHEN 2 THEN '시작되지 않고 실패했습니다.'
                                 END AS HadrManagerStatus
  

저작자표시

'SQL SERVER' 카테고리의 다른 글

Procedure 실행횟수 확인  (0) 2018.10.27
실행중인 쿼리 확인  (0) 2018.10.27
Index 생성이 안되어 있는 Foreign key 찾기  (0) 2018.10.27
링크드 서버(linked Server)  (0) 2018.10.26
Linked Server 연결 테스트(TEST)  (0) 2018.10.26
posted by bedbmsguru

tempdb 모니터링 쿼리

2018. 10. 27. 22:28 카테고리 없음
;WITH task_space_usage AS (
    -- SUM alloc/delloc pages
    SELECT session_id,
           request_id,
           SUM(internal_objects_alloc_page_count) AS alloc_pages,
           SUM(internal_objects_dealloc_page_count) AS dealloc_pages
    FROM sys.dm_db_task_space_usage WITH (NOLOCK)
    WHERE session_id <> @@SPID
    GROUP BY session_id, request_id
)SELECT TSU.session_id,
       TSU.alloc_pages * 1.0 / 128 AS [internal object MB space],
       TSU.dealloc_pages * 1.0 / 128 AS [internal object dealloc MB space],
       EST.text,
       -- Extract statement from sql text
       ISNULL(
           NULLIF(
               SUBSTRING(
                 EST.text, 
                 ERQ.statement_start_offset / 2, 
                 CASE WHEN ERQ.statement_end_offset < ERQ.statement_start_offset 
                  THEN 0 
                 ELSE( ERQ.statement_end_offset - ERQ.statement_start_offset ) / 2 END
               ), ''
           ), EST.text
       ) AS [statement text],
       EQP.query_plan
FROM task_space_usage AS TSU
INNER JOIN sys.dm_exec_requests ERQ WITH (NOLOCK)
    ON  TSU.session_id = ERQ.session_id
    AND TSU.request_id = ERQ.request_id
OUTER APPLY sys.dm_exec_sql_text(ERQ.sql_handle) AS EST
OUTER APPLY sys.dm_exec_query_plan(ERQ.plan_handle) AS EQP
WHERE EST.text IS NOT NULL OR EQP.query_plan IS NOT NULL ORDER BY 3 DESC;

 

저작자표시
posted by bedbmsguru

Index 생성이 안되어 있는 Foreign key 찾기

2018. 10. 27. 22:21 SQL SERVER

WITH FK_ColumnCount AS
(
        SELECT kc.constraint_object_id
                , ColumnCount = max(kc.constraint_column_id)
        FROM sys.foreign_key_columns kc
        GROUP BY kc.constraint_object_id
)
, ParentIndexGood AS
(
        SELECT kc.constraint_object_id 
                , FK_CC = cc.ColumnCount
                , ic.index_id
                , I_CC = COUNT(1)
        FROM sys.foreign_key_columns kc
                INNER JOIN FK_ColumnCount cc ON kc.constraint_object_id = cc.constraint_object_id
                INNER JOIN sys.index_columns ic ON ic.key_ordinal <= cc.ColumnCount
                                                                                AND ic.object_id = kc.parent_object_id 
                                                                                AND ic.column_id = kc.parent_column_id
        GROUP BY kc.constraint_object_id 
                , cc.ColumnCount
                , ic.index_id 
        HAVING cc.ColumnCount = COUNT(1)
)
, ReferencedIndexGood AS
(
        SELECT kc.constraint_object_id 
                , FK_CC = cc.ColumnCount
                , ic.index_id
                , I_CC = COUNT(1)
        FROM sys.foreign_key_columns kc
                INNER JOIN FK_ColumnCount cc ON kc.constraint_object_id = cc.constraint_object_id
                INNER JOIN sys.index_columns ic ON ic.key_ordinal <= cc.ColumnCount
                                                                                AND ic.object_id = kc.referenced_object_id 
                                                                                AND ic.column_id = kc.referenced_column_id
        GROUP BY kc.constraint_object_id 
                , cc.ColumnCount
                , ic.index_id 
        HAVING cc.ColumnCount = COUNT(1)
)
, ReferencedBoundIndexGood AS
(
        SELECT kc.constraint_object_id 
                , FK_CC = cc.ColumnCount
                , ic.index_id
                , I_CC = COUNT(1)
        FROM sys.foreign_keys k
                INNER JOIN sys.foreign_key_columns kc ON k.object_id = kc.constraint_object_id
                INNER JOIN FK_ColumnCount cc ON kc.constraint_object_id = cc.constraint_object_id
                INNER JOIN sys.index_columns ic ON ic.key_ordinal <= cc.ColumnCount
                                                                                AND ic.object_id = kc.referenced_object_id 
                                                                                AND ic.column_id = kc.referenced_column_id
                                                                                AND ic.index_id = k.key_index_id
        GROUP BY kc.constraint_object_id 
                , cc.ColumnCount
                , ic.index_id 
        HAVING cc.ColumnCount = COUNT(1)
)
SELECT FK_Name = k.name
        , k.is_disabled
        , k.is_not_trusted
        , k.delete_referential_action_desc
        , k.update_referential_action_desc
        , ParentTable = ps.name + '.' + pt.name 
        , ParentColumns = substring((SELECT (', ' + c.name)
                                                        FROM sys.foreign_key_columns kc
                                                                INNER JOIN sys.columns c ON kc.parent_object_id = c.object_id AND kc.parent_column_id = c.column_id
                                                        WHERE kc.constraint_object_id = k.object_id 
                                                        ORDER BY kc.constraint_column_id 
                                                        FOR XML PATH ('')
                                                        ), 3, 4000)
        , ReferencedTable = rs.name + '.' + rt.name 
        , ReferencedColumns = substring((SELECT (', ' + c.name)
                                                        FROM sys.foreign_key_columns kc
                                                                INNER JOIN sys.columns c ON kc.referenced_object_id = c.object_id AND kc.referenced_column_id = c.column_id
                                                        WHERE kc.constraint_object_id = k.object_id 
                                                        ORDER BY kc.constraint_column_id 
                                                        FOR XML PATH ('')
                                                        ), 3, 4000)
        , ReferenceBoundIndex = ri.name
        , IsParentIndexedForFK = CASE WHEN EXISTS (SELECT * FROM ParentIndexGood WHERE ParentIndexGood.constraint_object_id = k.object_id) THEN 'Yes' ELSE 'No' END
        , IsReferenceIndexedForFK = CASE WHEN EXISTS (SELECT * FROM ReferencedIndexGood WHERE ReferencedIndexGood.constraint_object_id = k.object_id) THEN 'Yes' ELSE 'No' END 
        , IsReferenceBoundToGoodIndex = CASE WHEN EXISTS (SELECT * FROM ReferencedBoundIndexGood WHERE ReferencedBoundIndexGood.constraint_object_id = k.object_id) THEN 'Yes' ELSE 'No' END 
FROM sys.foreign_keys k 
        INNER JOIN sys.tables pt ON k.parent_object_id = pt.object_id
        INNER JOIN sys.schemas ps ON pt.schema_id = ps.schema_id
        INNER JOIN sys.tables rt ON k.referenced_object_id = rt.object_id
        INNER JOIN sys.schemas rs ON rt.schema_id = rs.schema_id
        LEFT JOIN sys.indexes ri ON k.referenced_object_id = ri.object_id AND k.key_index_id = ri.index_id
ORDER BY 1

저작자표시

'SQL SERVER' 카테고리의 다른 글

실행중인 쿼리 확인  (0) 2018.10.27
SERVER property를 일괄로 보여주는 procedure  (0) 2018.10.27
링크드 서버(linked Server)  (0) 2018.10.26
Linked Server 연결 테스트(TEST)  (0) 2018.10.26
MDF DATA파일 사용량 조회  (0) 2018.10.26
posted by bedbmsguru

링크드 서버(linked Server)

2018. 10. 26. 21:51 SQL SERVER

 

--Linked Server 생성

sp_addlinkedserver'test1','MSSQL','SQLOLEDB','아이피,포트','디비이름'

sp_addlinkedsrvlogin 'test1', 'false',  'local login', 'remote login', 'remote password'

 
 
--rpc out 을 열어줘야 할 경우
--link이름.디비이름.스키마.procedure 로 실행하기 위해서는 TRUE로 해야함
 EXEC sp_serveroption '링크드서버명', 'RPC OUT', TRUE

 

 

--링크드서버 계정삭제sp_droplinkedsrvlogintest_oracle,hyun_user
--링크드서버 삭제sp_dropserver test_oracle--계정과 함께 삭제sp_dropserver 'test1' , 'droplogins'

 

--링크드서버 리스트 보는 쿼리

SELECT ss .server_id,
       ss. NAME,
       'Server ' = CASE ss. server_id
                     WHEN 0 THEN 'Current Server'
                     ELSE 'Remote Server'
                   END,
       ss. product,
       ss. provider,
       ss. catalog,
       'Local Login ' = CASE sl. uses_self_credential
                          WHEN 1 THEN 'Uses Self Credentials'
                          ELSE ssp .NAME
                        END,
       'Remote Login Name' = sl .remote_name,
       'RPC Out Enabled' = CASE ss. is_rpc_out_enabled
                             WHEN 1 THEN 'True'
                             ELSE 'False'
                           END,
       'Data Access Enabled' = CASE ss. is_data_access_enabled
                                 WHEN 1 THEN 'True'
                                 ELSE 'False'
                               END,
       ss. modify_date
FROM   sys .servers ss
       LEFT JOIN sys .linked_logins sl
              ON ss .server_id = sl .server_id
       LEFT JOIN sys .server_principals ssp
              ON ssp .principal_id = sl .local_principal_id 

저작자표시

'SQL SERVER' 카테고리의 다른 글

SERVER property를 일괄로 보여주는 procedure  (0) 2018.10.27
Index 생성이 안되어 있는 Foreign key 찾기  (0) 2018.10.27
Linked Server 연결 테스트(TEST)  (0) 2018.10.26
MDF DATA파일 사용량 조회  (0) 2018.10.26
Find Missing Index  (0) 2018.10.26
posted by bedbmsguru

Linked Server 연결 테스트(TEST)

2018. 10. 26. 21:50 SQL SERVER

DECLARE @Cursor CURSOR
DECLARE @ServerName NVARCHAR( 128)
DECLARE @ServerID INT
DECLARE @SQL VARCHAR( MAX)

--Create temp table to store results
IF object_id (N'tempdb..##LinkedServers') IS NOT NULL
                 DROP TABLE ##LinkedServers

CREATE TABLE ##LinkedServers
                 (
                 [LinkedServerID] INT IDENTITY( 1,1 ) NOT NULL,
                 [Name] SYSNAME NULL,
                 [ProvName] NVARCHAR (128) NULL,
                 [Product] NVARCHAR (128) NULL,
                 [DataSource] NVARCHAR (4000) NULL,
                 [ProvString] NVARCHAR (4000) NULL,
                 [Location] NVARCHAR (4000) NULL,
                 [Cat] SYSNAME NULL
                 )
--Get list of linked servers from system proc
INSERT INTO ##LinkedServers
EXEC [sys] .sp_linkedservers
--Add tested field to result set
ALTER TABLE ##LinkedServers ADD [TestSuccess] BIT

--Cursor over list of linked servers testing each
SET @Cursor = CURSOR FOR
                                                                                                 SELECT
                                                                                                                 [LinkedServerID],
                                                                                                                 [Name]
                                                                                                 FROM
                                                                                                                 ##LinkedServers
OPEN @Cursor
FETCH NEXT FROM @Cursor INTO
                                                                                                                 @ServerID,
                                                                                                                 @ServerName

WHILE (@@FETCH_STATUS = 0 )
BEGIN
                
                 SET @SQL =

                 '
                BEGIN TRY
                                EXEC sp_testlinkedserver [' + @ServerName + ']
                                UPDATE
                                                ##LinkedServers
                                SET
                                                [TestSuccess] = 1
                                WHERE
                                                [LinkedServerID] = ' + CAST(@ServerID AS VARCHAR) + '
                END TRY
                BEGIN CATCH
                                UPDATE
                                                ##LinkedServers
                                SET
                                                [TestSuccess] = 0
                                WHERE
                                                [LinkedServerID] = ' + CAST(@ServerID AS VARCHAR) + '
                END CATCH
                '
                 EXEC(@SQL )

                 FETCH NEXT FROM @Cursor INTO
                                                                                                                                 @ServerID,
                                                                                                                                 @ServerName
END

--Return results
SELECT
                 [Name] AS 'LinkedServerName',
                 [Product],
                 [TestSuccess]
FROM
                 ##LinkedServers

DROP TABLE ##LinkedServers

저작자표시

'SQL SERVER' 카테고리의 다른 글

Index 생성이 안되어 있는 Foreign key 찾기  (0) 2018.10.27
링크드 서버(linked Server)  (0) 2018.10.26
MDF DATA파일 사용량 조회  (0) 2018.10.26
Find Missing Index  (0) 2018.10.26
서비스팩(SERVICE PACK) 제거  (0) 2018.10.26
posted by bedbmsguru

티스토리툴바