블로그 이미지
bedbmsguru

Notice

Recent Comment

Recent Trackback

Archive

calendar

          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
  • 27,734total
  • 0today
  • 12yesterday
2016.05.11 17:07 SQL SERVER

--데이터 테이블로 옮기기

SELECTevent_data=CONVERT(XML,event_data)
INTO #t
FROM sys .fn_xe_file_target_read_file( N'C:\temp\PERFORMA.xel', NULL, NULL, NULL);

--
sys.fn_xe_file_target_read_file ( path, mdpath, initial_file_name, initial_offset )
path: 읽기 시작할 파일 경로
mdpath : 메타데이터 파일의 경로(2012부터는 필요없음)
initial_file_name:  path에서 읽을 첫 번째 파일(null을 주게되면 모든 파일을 읽는다.)
initial_offset:  이전에 읽은 마지막 오프셋을 지정하는데 사용


  SELECT
  ts    = event_data. value(N'(event/@timestamp)[1]' , N'datetime' ),
  duration  = event_data. value(N'(event/data[@name="duration"]/value)[1]' , N'int' ),
  cpu_time  = event_data. value(N'(event/data[@name="cpu_time"]/value)[1]' , N'int' ),
  physical_reads  = event_data. value(N'(event/data[@name="physical_reads"]/value)[1]' , N'int' ),
  logical_reads  = event_data. value(N'(event/data[@name="logical_reads"]/value)[1]' , N'int' ),
  writes  = event_data. value(N'(event/data[@name="writes"]/value)[1]' , N'int' ),
  row_count  = event_data. value(N'(event/data[@name="row_count"]/value)[1]' , N'int' ),
  username = event_data. value(N'(event/action[@name="username"]/value)[1]' , N'varchar(100)' ),
  sql_text = event_data. value(N'(event/data[@name="statement"]/value)[1]' , N'varchar(max)' )
FROM #t
WHERE
  event_data. value(N'(event/data[@name="cpu_time"]/value)[1]' , N'int' ) > 100

  

저작자 표시
신고
posted by bedbmsguru
2016.04.26 16:05 SQL SERVER

SELECT
    s.NAME AS SchemaName, 
    t.NAME AS TableName,
    p.rows AS RowCounts,
    SUM(a .total_pages) * 8 /1024 AS TotalSpaceMB,  
    SUM(a .used_pages) * 8 /1024 AS UsedSpaceMB,  
    (SUM( a.total_pages ) - SUM( a.used_pages )) * 8 /1024 AS UnusedSpaceMB
FROM  sys .tables t
INNER JOIN sys. Schemas s ON t.schema_id = s.schema_id
INNER JOIN sys. indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys. partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys. allocation_units a ON p.partition_id = a.container_id
WHERE 
    t.NAME NOT LIKE 'dt%'  
    AND t .is_ms_shipped = 0
    AND i .OBJECT_ID > 255 
GROUP BY
 s. Name, 
    t.Name ,
    p.[Rows]
ORDER BY  
 TotalSpaceMB desc

저작자 표시
신고
posted by bedbmsguru
2016.04.26 11:18 SQL SERVER


Index Rebuild 시 아래의 에러가 발생할 경우


메시지 1943 수준 16, 상태 1, 줄 1

페이지 수준 잠금이 비활성화되어 있으므로 테이블 "테이블명" 의 인덱스 "인덱스명"을(를) 다시 구성할 수 없습니다.



개발자가 SSMS에서 인덱스를 생성하여 page_locked 옵션 체크가 해제된 상태에서 인덱스를 생성하였음



수정쿼리

ALTER INDEX [인덱스명] ON [테이블명] SET ( ALLOW_PAGE_LOCKS  = ON )




저작자 표시
신고
posted by bedbmsguru
2013.10.23 18:02 SQL SERVER

장애로 비정상 종료된 DB를 로그없이 다른 SQL서버 Instance 에서 복원하는 방법





(1) SQL SERVER 2005 이상


1. 복원하고자 하는 database와 같은 이름을 가진 Database를 새로 생성



2. SQL서버를 중지한 다음 복원하려고 하는 MDF파일을 새로만들었던 MDF파일에 덮어쓴다.

그리고 LDF파일은 삭제한다.


3.해당 database를 emergency mode로 변경한다.

alter database ABC set emergency

alter database ABC set single_user -- 먼저 싱글유저 모드로 변경해야함


4. 새로운 로그파일을 생성한다.

alter database ABC rebuild log on

(Name=ABC_log,filename='C:\SQLDAT\ABC_log.ldf')


5. database를 online으로 변경

alter database ABC set online


6. DB를 체크 복구가능한 데이터 복구

DBCC CHECKDB('abc', REPAIR_REBUILD) WITH ALL_ERRORMSGS, NO_INFOMSGS


7.DB체크 결과를 토대로 테이블 복구


--데이터 손실없이 복구 가능한 테이블 복구

dbcc checktable('temp_aaa')


--복구불가능한 데이터는 버리고 테이블 복구

dbcc checktable('temp_aaa', REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS


alter database ABC set MULTI_user




(2) SQL SERVER 2000



1. 복원하고자 하는 database와 같은 이름을 가진 Database를 새로 생성

주의:MDF의 이름과 LDF의 이름은 복원하고자하는 DB의 이름과 같아야 한다.


2. SQL서버를 중지한 다음 복원하려고 하는 MDF파일을 새로만들었던 MDF파일에 덮어쓴다.

그리고 LDF파일은 삭제한다.


3. SQL SERVER를 시작하면 해당 Database는 '주의대상' 상태로 표시됨


4. master DB를 업데이트 가능한 상태로 만들어준다.

USE MASTER

GO

sp_CONFIGURE 'allow updates', 1

RECONFIGURE WITH OVERRIDE

GO


5. database의 모드를 emergency mode로 변경한다.

--이 쿼리는 Database의 현재 상태를 볼수 있다 (나중에 다시 복구를 위해 status 값을 기억해 둬야함)

SELECT *

FROM sysdatabases

WHERE name = 'yourdatabasename'


—-status 값을 update

BEGIN TRAN

UPDATE sysdatabases

SET status = 32768

WHERE name = 'yourdatabasename'

COMMIT TRAN


6.SQL SERVER 재시작한다.


7.아래의 DBCC 커맨드를 실행한다. 이 명령은 새 로그파일을 생성하는 명령이다.

주의:LDF의 이름은 새 서버에서 삭제한 이름과 같아야 한다.

DBCC TRACEON (3604)

DBCC REBUILD_LOG(yourdatabasename,'c:\yourdatabasename_log.ldf')

GO


8.주의대상 으로 된 database 상태를 변경한다.

sp_RESETSTATUS yourdatabasename



9. Database mode를 5번에서 저장했던 값으로 변경한다.

BEGIN

UPDATE sysdatabases

SET status = (value retrieved IN first query OF STEP 5)

WHERE name = 'yourdatabasename‘

COMMIT TRAN

GO



10.master DB를 업데이트 불가능하도록 다시 수정한다.

USE MASTER

GO

sp_CONFIGURE 'allow updates',0

RECONFIGURE WITH OVERRIDE

GO



8, 9, 10 단계를 진행하다 database가 사용중이라고 나올 경우 

sp_DBOPTION 'yourdatabasename', 'single user','true'  실행할 것

작업이 완료되면 

sp_DBOPTION 'yourdatabasename', 'single user','false' 실행




저작자 표시
신고
posted by bedbmsguru
2013.07.09 09:23 SQL SERVER

1.레지스트리에서 Disable Performance Counters  값이 1로 되어있을 경우
   0으로 바꿔준다. (바로해결됨)

--여기나
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib\Disable Performance Counters
--여기
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLSERVER\Performance\Disable Performance Counters

위 상황이 해당되지 않을경우

services.msc에 Remote Registry가 시작되었는지 체크
잘 실행되고 있다면
unlodctr 와 lodctr 을 이용해서 SQL 관련 카운터를 다시 등록할것
1) cmd(Administrator권한으로)

2) SQL SERVER의 binn폴더로 이동

3)unlodctr을 이용해서 SQL counters를 unload한다.
    e.g. unlodctr MSSQLSERVER (for default instance)
    e.g. unlodctr SQLSERVERAGENT (for default SQL Agent)
    e.g. unlodctr MSSQL$TEST (for named instance)
    e.g. unlodctr SQLAGENT$TEST (for SQL agent)

4)lodctr을 이용해서 SQL counters를 다시 등록한다.
    e.g. lodctr perf-MSSQLSERVERsqlctr.ini (for default instance)
    e.g. lodctr perf-SQLSERVERAGENTsqlagtctr.ini (for default SQL Agent)
    e.g. lodctr perf-MSSQL$TESTsqlctr.ini (for named instance)
    e.g. lodctr perf-SQLAGENT$TESTsqlagtctr.ini (for SQL Agent)

5)Remote Registry service를 다시 시작한다.
    net stop "Remote Registry"
    net start "Remote Registry"

6)필요할 경우 WMI와 WinPrivSE.exe 싱크를 다시 맞춘다.
    e.g. winmgmt /resyncperfctr "5660"
   cf)5660은 WinPrivSE.exe 의 pid

위 방법으로도 해결 되지 않을 경우
lodctr /R --> 모든 performance counter를 재등록 한다.
  주의!!! 모든 performance counter registry 세팅을 재등록하게 된다.


출처:http://www.travisgan.com/2013/05/missing-sql-performance-counters.html

저작자 표시
신고
posted by bedbmsguru
2013.02.27 11:37 SQL SERVER

SQL 2008에서 2000으로 링크드서버를 연결하기위해서는 2000의 master DB에 아래의 프로시져를 생성해 두어야 한다.

 

 

USE MASTER

GO

CREATE PROCEDURE SP_TABLES_INFO_ROWSET_64
      @TABLE_NAME SYSNAME
    , @TABLE_SCHEMA SYSNAME = NULL
    , @TABLE_TYPE NVARCHAR(255) = NULL
AS
BEGIN
    DECLARE @RESULT INT SET @RESULT = 0

    EXEC @RESULT = SP_TABLES_INFO_ROWSET @TABLE_NAME, @TABLE_SCHEMA, @TABLE_TYPE
END

 

 

CF)Linked Server 구성하는법

EXEC sp_addlinkedserver @server='DEVSERVER',@srvproduct='', -- 연결이름.
@provider='SQLOLEDB', @datasrc='목적지IP', @catalog='디비이름' (옵션임)
EXEC sp_addlinkedsrvlogin 'DEVSERVER', 'false', NULL, '아이디', '암호'

저작자 표시
신고
posted by bedbmsguru
2013.01.23 11:38 SQL SERVER

DB owner가 누구인지 확인하는 쿼리

SELECT  SUSER_SNAME(owner_sid)
FROM    sys.databases
WHERE   name = 'yourDatabase';


--owner를 sa로 변경

ALTER AUTHORIZATION ON DATABASE::yourDatabase TO sa;




 

저작자 표시
신고
posted by bedbmsguru
2013.01.14 15:09 SQL SERVER

출처:http://www.mssqltips.com/sqlservertip/2561/querying-sql-server-agent-job-information/

agent job 관련 각종 정보를 쿼리로 확인 하기

 

 

--agent job 설정 & 스케줄  정보 확인하기

SELECT 
    [sJOB].[job_id] AS [JobID]
    , [sJOB].[name] AS [JobName]
    , [sDBP].[name] AS [JobOwner]
    , [sCAT].[name] AS [JobCategory]
    , [sJOB].[description] AS [JobDescription]
    , CASE [sJOB].[enabled]
        WHEN 1 THEN 'Yes'
        WHEN 0 THEN 'No'
      END AS [IsEnabled]
    , [sJOB].[date_created] AS [JobCreatedOn]
    , [sJOB].[date_modified] AS [JobLastModifiedOn]
    , [sSVR].[name] AS [OriginatingServerName]
    , [sJSTP].[step_id] AS [JobStartStepNo]
    , [sJSTP].[step_name] AS [JobStartStepName]
    , CASE
        WHEN [sSCH].[schedule_uid] IS NULL THEN 'No'
        ELSE 'Yes'
      END AS [IsScheduled]
    , [sSCH].[schedule_uid] AS [JobScheduleID]
    , [sSCH].[name] AS [JobScheduleName]
    , CASE [sJOB].[delete_level]
        WHEN 0 THEN 'Never'
        WHEN 1 THEN 'On Success'
        WHEN 2 THEN 'On Failure'
        WHEN 3 THEN 'On Completion'
      END AS [JobDeletionCriterion]
FROM
    [msdb].[dbo].[sysjobs] AS [sJOB]
    LEFT JOIN [msdb].[sys].[servers] AS [sSVR]
        ON [sJOB].[originating_server_id] = [sSVR].[server_id]
    LEFT JOIN [msdb].[dbo].[syscategories] AS [sCAT]
        ON [sJOB].[category_id] = [sCAT].[category_id]
    LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sJSTP]
        ON [sJOB].[job_id] = [sJSTP].[job_id]
        AND [sJOB].[start_step_id] = [sJSTP].[step_id]
    LEFT JOIN [msdb].[sys].[database_principals] AS [sDBP]
        ON [sJOB].[owner_sid] = [sDBP].[sid]
    LEFT JOIN [msdb].[dbo].[sysjobschedules] AS [sJOBSCH]
        ON [sJOB].[job_id] = [sJOBSCH].[job_id]
    LEFT JOIN [msdb].[dbo].[sysschedules] AS [sSCH]
        ON [sJOBSCH].[schedule_id] = [sSCH].[schedule_id]
ORDER BY [JobName]

 

 

--마지막 실행 상세정보+ 다음 실행스케쥴

SELECT 
    [sJOB].[job_id] AS [JobID]
    , [sJOB].[name] AS [JobName]
    , CASE 
        WHEN [sJOBH].[run_date] IS NULL OR [sJOBH].[run_time] IS NULL THEN NULL
        ELSE CAST(
                CAST([sJOBH].[run_date] AS CHAR(8))
                + ' ' 
                + STUFF(
                    STUFF(RIGHT('000000' + CAST([sJOBH].[run_time] AS VARCHAR(6)),  6)
                        , 3, 0, ':')
                    , 6, 0, ':')
                AS DATETIME)
      END AS [LastRunDateTime]
    , CASE [sJOBH].[run_status]
        WHEN 0 THEN 'Failed'
        WHEN 1 THEN 'Succeeded'
        WHEN 2 THEN 'Retry'
        WHEN 3 THEN 'Canceled'
        WHEN 4 THEN 'Running' -- In Progress
      END AS [LastRunStatus]
    , STUFF(
            STUFF(RIGHT('000000' + CAST([sJOBH].[run_duration] AS VARCHAR(6)),  6)
                , 3, 0, ':')
            , 6, 0, ':') 
        AS [LastRunDuration (HH:MM:SS)]
    , [sJOBH].[message] AS [LastRunStatusMessage]
    , CASE [sJOBSCH].[NextRunDate]
        WHEN 0 THEN NULL
        ELSE CAST(
                CAST([sJOBSCH].[NextRunDate] AS CHAR(8))
                + ' ' 
                + STUFF(
                    STUFF(RIGHT('000000' + CAST([sJOBSCH].[NextRunTime] AS VARCHAR(6)),  6)
                        , 3, 0, ':')
                    , 6, 0, ':')
                AS DATETIME)
      END AS [NextRunDateTime]
FROM 
    [msdb].[dbo].[sysjobs] AS [sJOB]
    LEFT JOIN (
                SELECT
                    [job_id]
                    , MIN([next_run_date]) AS [NextRunDate]
                    , MIN([next_run_time]) AS [NextRunTime]
                FROM [msdb].[dbo].[sysjobschedules]
                GROUP BY [job_id]
            ) AS [sJOBSCH]
        ON [sJOB].[job_id] = [sJOBSCH].[job_id]
    LEFT JOIN (
                SELECT 
                    [job_id]
                    , [run_date]
                    , [run_time]
                    , [run_status]
                    , [run_duration]
                    , [message]
                    , ROW_NUMBER() OVER (
                                            PARTITION BY [job_id] 
                                            ORDER BY [run_date] DESC, [run_time] DESC
                      ) AS RowNumber
                FROM [msdb].[dbo].[sysjobhistory]
                WHERE [step_id] = 0
            ) AS [sJOBH]
        ON [sJOB].[job_id] = [sJOBH].[job_id]
        AND [sJOBH].[RowNumber] = 1
ORDER BY [JobName]
 
--agent job 스케쥴 정보 보는 쿼리
SELECT 
    [schedule_uid] AS [ScheduleID]
    , [name] AS [ScheduleName]
    , CASE [enabled]
        WHEN 1 THEN 'Yes'
        WHEN 0 THEN 'No'
      END AS [IsEnabled]
    , CASE 
        WHEN [freq_type] = 64 THEN 'Start automatically when SQL Server Agent starts'
        WHEN [freq_type] = 128 THEN 'Start whenever the CPUs become idle'
        WHEN [freq_type] IN (4,8,16,32) THEN 'Recurring'
        WHEN [freq_type] = 1 THEN 'One Time'
      END [ScheduleType]
    , CASE [freq_type]
        WHEN 1 THEN 'One Time'
        WHEN 4 THEN 'Daily'
        WHEN 8 THEN 'Weekly'
        WHEN 16 THEN 'Monthly'
        WHEN 32 THEN 'Monthly - Relative to Frequency Interval'
        WHEN 64 THEN 'Start automatically when SQL Server Agent starts'
        WHEN 128 THEN 'Start whenever the CPUs become idle'
      END [Occurrence]
    , CASE [freq_type]
        WHEN 4 THEN 'Occurs every ' + CAST([freq_interval] AS VARCHAR(3)) + ' day(s)'
        WHEN 8 THEN 'Occurs every ' + CAST([freq_recurrence_factor] AS VARCHAR(3)) 
                    + ' week(s) on '
                    + CASE WHEN [freq_interval] & 1 = 1 THEN 'Sunday' ELSE '' END
                    + CASE WHEN [freq_interval] & 2 = 2 THEN ', Monday' ELSE '' END
                    + CASE WHEN [freq_interval] & 4 = 4 THEN ', Tuesday' ELSE '' END
                    + CASE WHEN [freq_interval] & 8 = 8 THEN ', Wednesday' ELSE '' END
                    + CASE WHEN [freq_interval] & 16 = 16 THEN ', Thursday' ELSE '' END
                    + CASE WHEN [freq_interval] & 32 = 32 THEN ', Friday' ELSE '' END
                    + CASE WHEN [freq_interval] & 64 = 64 THEN ', Saturday' ELSE '' END
        WHEN 16 THEN 'Occurs on Day ' + CAST([freq_interval] AS VARCHAR(3)) 
                     + ' of every '
                     + CAST([freq_recurrence_factor] AS VARCHAR(3)) + ' month(s)'
        WHEN 32 THEN 'Occurs on '
                     + CASE [freq_relative_interval]
                        WHEN 1 THEN 'First'
                        WHEN 2 THEN 'Second'
                        WHEN 4 THEN 'Third'
                        WHEN 8 THEN 'Fourth'
                        WHEN 16 THEN 'Last'
                       END
                     + ' ' 
                     + CASE [freq_interval]
                        WHEN 1 THEN 'Sunday'
                        WHEN 2 THEN 'Monday'
                        WHEN 3 THEN 'Tuesday'
                        WHEN 4 THEN 'Wednesday'
                        WHEN 5 THEN 'Thursday'
                        WHEN 6 THEN 'Friday'
                        WHEN 7 THEN 'Saturday'
                        WHEN 8 THEN 'Day'
                        WHEN 9 THEN 'Weekday'
                        WHEN 10 THEN 'Weekend day'
                       END
                     + ' of every ' + CAST([freq_recurrence_factor] AS VARCHAR(3)) 
                     + ' month(s)'
      END AS [Recurrence]
    , CASE [freq_subday_type]
        WHEN 1 THEN 'Occurs once at ' 
                    + STUFF(
                 STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
        WHEN 2 THEN 'Occurs every ' 
                    + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Second(s) between ' 
                    + STUFF(
                   STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
                    + ' & ' 
                    + STUFF(
                    STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
        WHEN 4 THEN 'Occurs every ' 
                    + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Minute(s) between ' 
                    + STUFF(
                   STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
                    + ' & ' 
                    + STUFF(
                    STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
        WHEN 8 THEN 'Occurs every ' 
                    + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Hour(s) between ' 
                    + STUFF(
                    STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
                    + ' & ' 
                    + STUFF(
                    STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
      END [Frequency]
    , STUFF(
            STUFF(CAST([active_start_date] AS VARCHAR(8)), 5, 0, '-')
                , 8, 0, '-') AS [ScheduleUsageStartDate]
    , STUFF(
            STUFF(CAST([active_end_date] AS VARCHAR(8)), 5, 0, '-')
                , 8, 0, '-') AS [ScheduleUsageEndDate]
    , [date_created] AS [ScheduleCreatedOn]
    , [date_modified] AS [ScheduleLastModifiedOn]
FROM [msdb].[dbo].[sysschedules]
ORDER BY [ScheduleName]
 
 
저작자 표시
신고
posted by bedbmsguru
2012.09.26 17:17 SQL SERVER

 Agent Job을 작업스크립팅으로 복사할때


메시지 8145, 수준 16, 상태 1, 프로시저 sp_add_jobschedule, 줄 0
@schedule_uid은(는) 프로시저 sp_add_jobschedule의 매개 변수가 아닙니다.


job을 복사해서 실행했더니 위와같은 에러가 났다.


2008용 ssms 에서 작업스크립팅을 하고 2005 서버에서 스크립트를 실행하였기 때문에 생기는 문제임.

왜 그런가 살펴봤더니  sql server 2005 와 2008 에서 sp_add_jobschedule 프로시져가 좀 달라졌다.


2005는 sp_add_jobschedule 의 파라미터가  @schedule_id (an integer)이고

2008은 @schedule_uid (a uniqueidentifier) 를 받기 때문에 생긴 문제


2008 ssms로 스크립트를 생성해서 2005db서버에서 실행할때 주의하자.



저작자 표시
신고
posted by bedbmsguru
2012.04.19 10:25 SQL SERVER

pwdencrypt()  function 으로 데이타를 저장하고

pwdcompar('비교대상값', DB칼럼이름) 로 결과를 비교한다.


create table temp_T
(
    aaa varchar(100) not null,
    abc varbinary(200) not null
)

insert into temp_T values('abc', pwdencrypt('abc'))


select pwdcompare('add', abc) from temp_T where aaa='abc'


pwdcompar 결과가 일치할 경우 1 틀릴경우에는 0이 리턴됨

저작자 표시
신고
posted by bedbmsguru

티스토리 툴바