블로그 이미지
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 31        
  • 30,524total
  • 0today
  • 14yesterday
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
2011.07.07 08:31 SQL SERVER
원문출처
http://www.databasejournal.com/features/mssql/sql-server-index-t-sql-statements.html

 Find all Indexes

The following query can be used to query all the tables, columns and indexes on the current database:


SELECT OBJECT_SCHEMA_NAME(BaseT.[object_id],DB_ID()) AS [Schema],
BaseT.[name] AS [table_name], I.[name] AS [index_name], AC.[name] AS [column_name],
I.[type_desc]
FROM sys.[tables] AS BaseT
INNER JOIN sys.[indexes] I ON BaseT.[object_id] = I.[object_id]
INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id]
INNER JOIN sys.[all_columns] AC ON BaseT.[object_id] = AC.[object_id] AND IC.[column_id] = AC.[column_id]
WHERE BaseT.[is_ms_shipped] = 0 AND I.[type_desc] <> 'HEAP'
ORDER BY BaseT.[name], I.[index_id], IC.[key_ordinal]

Fragmentation

The following query can be used to find the index fragmentation on all the tables in the current database:


SELECT object_name(IPS.object_id) AS [TableName],
SI.name AS [IndexName],
IPS.Index_type_desc,
IPS.avg_fragmentation_in_percent,
IPS.avg_fragment_size_in_pages,
IPS.avg_page_space_used_in_percent,
IPS.record_count,
IPS.ghost_record_count,
IPS.fragment_count,
IPS.avg_fragment_size_in_pages
FROM sys.dm_db_index_physical_stats(db_id(DB_NAME()), NULL, NULL, NULL , 'DETAILED') IPS
JOIN sys.tables ST WITH (nolock) ON IPS.object_id = ST.object_id
JOIN sys.indexes SI WITH (nolock) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id
WHERE ST.is_ms_shipped = 0
order by IPS.avg_fragment_size_in_pages desc

 Missing index

SQL Server keeps track of the indexes that it thinks you should create that will help in improving the performance of queries. The following query list all missing indexes.


SELECT sys.objects.name
, (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) AS Impact
, 'CREATE NONCLUSTERED INDEX ix_IndexName ON ' + sys.objects.name COLLATE DATABASE_DEFAULT + ' ( ' + IsNull(mid.equality_columns, '') + CASE WHEN mid.inequality_columns IS NULL
THEN ''
ELSE CASE WHEN mid.equality_columns IS NULL
THEN ''
ELSE ',' END + mid.inequality_columns END + ' ) ' + CASE WHEN mid.included_columns IS NULL
THEN ''
ELSE 'INCLUDE (' + mid.included_columns + ')' END + ';' AS CreateIndexStatement
, mid.equality_columns
, mid.inequality_columns
, mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle AND mid.database_id = DB_ID()
INNER JOIN sys.objects WITH (nolock) ON mid.OBJECT_ID = sys.objects.OBJECT_ID
WHERE (migs.group_handle IN
(
SELECT TOP (500) group_handle
FROM sys.dm_db_missing_index_group_stats WITH (nolock)
ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC))
AND OBJECTPROPERTY(sys.objects.OBJECT_ID, 'isusertable')=1
ORDER BY 2 DESC , 3 DESC

 Unused index

The following statement lists all the indexes that have not been used. This also generates the DROP index statement which can come handy when deleting the indexes.


SELECT o.name, indexname=i.name, i.index_id
, reads=user_seeks + user_scans + user_lookups
, writes = user_updates
, rows = (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id)
, CASE
WHEN s.user_updates < 1 THEN 100
ELSE 1.00 * (s.user_seeks + s.user_scans + s.user_lookups) / s.user_updates
END AS reads_per_write
, 'DROP INDEX ' + QUOTENAME(i.name)
+ ' ON ' + QUOTENAME(c.name) + '.' + QUOTENAME(OBJECT_NAME(s.object_id)) as 'drop statement'
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON i.index_id = s.index_id AND s.object_id = i.object_id
INNER JOIN sys.objects o on s.object_id = o.object_id
INNER JOIN sys.schemas c on o.schema_id = c.schema_id
WHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1
AND s.database_id = DB_ID()
AND i.type_desc = 'nonclustered'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
AND (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id) > 10000
ORDER BY reads

Index Maintainenance

Rebuild index

When an index gets fragmented, it requires defragmentation. Defragmentation can be done using the rebuild clause when altering a table. This command is equivalent to DBCC DBREINDEX in SQL Server versions prior to 2005. The command that can be used to rebuild the index is as follows:


USE AdventureWorks2008R2;
GO
ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD;
GO

If ALL is not specified in rebuild, it will not rebuild a nonclustered index.

REORGANIZE index

Specifies that the index leaf level will be reorganized. The REORGANIZE statement is always performed online. This command is equivalent to DBCC INDEXDEFRAG in SQL Server versions prior to 2005.


USE AdventureWorks2008R2;
GO
ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto
REORGANIZE ;
GO

posted by bedbmsguru
2011.06.29 11:12 SQL SERVER


원문 출처
http://blog.sqlauthority.com/2007/04/29/sql-server-random-number-generator-script-sql-query/


가장 간단한 방법만 퍼왔다. 요긴하게 쓸 수 있을듯.ㅋㅋ
Method 3 : Random Numbers Quick Scripts
---- random float from 0 up to 20 - [0, 20)
SELECT 20*RAND()
-- random float from 10 up to 30 - [10, 30)
SELECT 10 + (30-10)*RAND()
--random integer BETWEEN 0
AND 20 - [0, 20]
SELECT CONVERT(INT, (20+1)*RAND())
----random integer BETWEEN 10
AND 30 - [10, 30]
SELECT 10 + CONVERT(INT, (30-10+1)*RAND()


posted by bedbmsguru
2011.05.19 14:13 SQL SERVER
시스템에 맞는 버전의 첨부파일을 다운 받는다.
Microsoft SQL-DMO (ODBC SQLState: 42000)
70.dll DLL 또는 이 DLL이 참조하는 DLL 중 하나를 로드할 수 없습니다. 이유: 126(지정된 모듈을 찾을 수 없습니다.).
--sql 설치 경로
D:\Microsoft SQL Server\MSSQL\Binn
여기에다가 xplog70.dll 를 집어넣는다.


posted by bedbmsguru
2011.03.09 16:38 SQL SERVER
CREATE IINDEX [INDEX_NAME] ON TABLE
(
    column,....
)
INCLUDE(column, ...)

-INCLUDE절에 지정된 non-key column을 noncluster index의 leaf레벨에 저장하여 covered index 수준의 성능을 낼 수 있도록 해준다.
-Where 절에 나오는 칼럼은 key-column으로 그냥 select list에만 나오는 칼럼은 non-key column으로 세팅한다.


0. include절에 나오는 칼럼은 update가 되더라도 index를 다시 정렬하지 않아도 되기때문에 DISK I/O를 줄일 수 있다.
1. Index의 크기가 900byte를 넘더라도 인덱스 생성이 가능하다.
2. 16개로 제한된 Index key column갯수의 제한을 받지 않는다.
3. non-key column 에는 text, ntext, and image 를 제외한 모든 데이타 타입이 가능하다.
posted by bedbmsguru
2011.01.31 16:54 SQL SERVER
Select exec

프로시져 결과값을 select 해야할 경우에 사용할 것~

--테스트 테이블 생성
CREATE TABLE TEST1
(
  col1 int, 
  col2 varchar(500)
)

--데이타 입력
INSERT INTO TEST1 VALUES(1, 'AAA')
INSERT INTO TEST1 VALUES(2, 'BBB')
INSERT INTO TEST1 VALUES(3, 'CCC')
INSERT INTO TEST1 VALUES(4, 'DDD')
INSERT INTO TEST1 VALUES(5, 'EEE')

--테스트 프로시져 생성
CREATE PROCEDURE usp_test_proc
(
    @index As INT
)
AS
SELECT *
FROM TEST1
WHERE col1 > @index


방법1
--######### 임시 테이블을 생성해서 데이타를 넣은후 필터링 ###################
CREATE TABLE #TEMP
(
     col1 int, 
    col2 varchar(500)
 )

INSERT #TEMP EXEC usp_test_proc 3
SELECT * FROM #TEMP WHERE col2 = 'EEE'
DROP TABLE #TEMP

--#######방법2 OPENROWSET 함수를 이용한다.

--2005에서는 Ad Hoc Distributed Queries 옵션이 default로 꺼져 있으므로 켜줘야 한다.
--켜주지 않을 경우 아래와 같은 에러가 발생한다.
메시지 15281, 수준 16, 상태 1, 줄 1
구성 요소 'Ad Hoc Distributed Queries'이(가) SQL Server 보안 구성의 일부로 해제되었으므로 이 구성 요소의 문 'OpenRowset/OpenDatasource'에
대한 액세스가 차단되었습니다. 시스템 관리자는 sp_configure를 사용하여 'Ad Hoc Distributed Queries'의 사용을 활성화할 수 있습니다. 'Ad Hoc Distributed Queries'
활성화 방법은 SQL Server 온라인 설명서의 "노출 영역 구성"을 참조하십시오.

sp_configure 'show advanced options', 1;
reconfigure


sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;

sp_configure 'show advanced options', 0;
reconfigure
GO
---------------------------------------------------------------------

SELECT * FROM OPENROWSET('sqloledb', 'server=(local);trusted_connection=yes' ,'exec 디비이름.dbo.usp_test_proc 4')
posted by bedbmsguru
2010.11.03 11:38 SQL SERVER

포트 지정을 , 로 할줄이야....-_-;;
아이피, Port\ Instanc이름
posted by bedbmsguru

티스토리 툴바