'SQL Server' 태그의 글 목록

SQL Server Backup Script with TimeStamp

Posted by Alvin You

2015. 7. 2. 10:34 SQL Server

SQL Server를 운영하다보면 백업 스케쥴링에 대해서 고민하게 됩니다. 아래 스크립트는 SQL Server에 생성된 사용자 Database들에 대해서 TimeStamp를 찍어 일단위로 지정된 폴더 경로에 데이터 베이스를 백업하도록 하는 스크립트입니다.

저작자표시

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

Table의 사용량(RowCount, Size) 정보 보기 (0)	2015.04.24
Failed to initialize MSDB database for tuning (exit code: -1073741819). (DTAClient) (0)	2015.04.15
[Powershell]SQL Server 복원 작업 (0)	2015.04.07
Upgrading SQL Server 2012 License Edition (0)	2015.01.07
테이블들의 Row Count 가져오기! (0)	2014.03.22

Trackback: Comment:

Table의 사용량(RowCount, Size) 정보 보기

Posted by Alvin You

2015. 4. 24. 10:04 SQL Server

아래 스크립트는 Database내에 있는 모든 Table의 Row Count 정보와 Size 정보를 보여 주는 스크립트입니다.

Sp_MSForEachTable : Database내의 모든 테이블들을 돌며, 파라미터로 받은 SQL문을 수행
Sp_SpaceUsed : 파라미터로 받은 테이블의 사용량을 보여줌

exec sp_spaceused 'TBL_HOSP_Info'

CREATE TABLE #RowCountsAndSizes (TableName NVARCHAR(128),rows CHAR(11),

reserved VARCHAR(18),data VARCHAR(18),index_size VARCHAR(18),

unused VARCHAR(18))

EXEC sp_MSForEachTable 'INSERT INTO #RowCountsAndSizes EXEC sp_spaceused ''?'' '

SELECT TableName,CONVERT(bigint,rows) AS NumberOfRows,

CONVERT(bigint,left(reserved,len(reserved)-3)) AS SizeinKB

FROM #RowCountsAndSizes

ORDER BY NumberOfRows DESC,SizeinKB DESC,TableName

DROP TABLE #RowCountsAndSizes

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

SQL Server Backup Script with TimeStamp (0)	2015.07.02
Failed to initialize MSDB database for tuning (exit code: -1073741819). (DTAClient) (0)	2015.04.15
[Powershell]SQL Server 복원 작업 (0)	2015.04.07
Upgrading SQL Server 2012 License Edition (0)	2015.01.07
테이블들의 Row Count 가져오기! (0)	2014.03.22

Trackback: Comment:

Failed to initialize MSDB database for tuning (exit code: -1073741819). (DTAClient)

Posted by Alvin You

2015. 4. 15. 21:47 SQL Server

Analyze Query in Database Engine Tuning Advisor 실행시 아래와 같은 오류가 발생한다면, MSDB에 DTA 관련 Objects들을 생성해 주면 됩니다.

[오류 메시지]

[스크립트 실행]

USE [msdb] GO /****** Object: StoredProcedure [dbo].[sp_DTA_start_xmlprefix] Script Date: 12/31/2008 10:56:11 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create procedure [dbo].[sp_DTA_start_xmlprefix] as begin declare @startTags nvarchar(128) set @startTags = N'<DTAXML><DTAOutput><AnalysisReport>' select @startTags end GO /****** Object: Table [dbo].[DTA_input] Script Date: 12/31/2008 10:56:59 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DTA_input]( [SessionName] [sysname] NOT NULL, [SessionID] [int] IDENTITY(1,1) NOT NULL, [TuningOptions] [ntext] NOT NULL, [CreationTime] [datetime] NOT NULL DEFAULT (getdate()), [ScheduledStartTime] [datetime] NOT NULL DEFAULT (getdate()), [ScheduledJobName] [sysname] NOT NULL DEFAULT (''), [InteractiveStatus] [tinyint] NOT NULL DEFAULT ((0)), [LogTableName] [nvarchar](1280) NOT NULL DEFAULT (''), [GlobalSessionID] [uniqueidentifier] NULL DEFAULT (newid()), PRIMARY KEY CLUSTERED ( [SessionID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO /****** Object: Table [dbo].[DTA_progress] Script Date: 12/31/2008 10:57:29 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DTA_progress]( [ProgressEventID] [int] IDENTITY(1,1) NOT NULL, [SessionID] [int] NULL, [TuningStage] [tinyint] NOT NULL DEFAULT ((0)), [WorkloadConsumption] [tinyint] NOT NULL, [EstImprovement] [int] NOT NULL DEFAULT ((0)), [ProgressEventTime] [datetime] NOT NULL DEFAULT (getdate()), [ConsumingWorkLoadMessage] [nvarchar](256) NULL, [PerformingAnalysisMessage] [nvarchar](256) NULL, [GeneratingReportsMessage] [nvarchar](256) NULL ) ON [PRIMARY] GO /****** Object: Table [dbo].[DTA_output] Script Date: 12/31/2008 10:57:10 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DTA_output]( [SessionID] [int] NOT NULL, [TuningResults] [ntext] NOT NULL, [StopTime] [datetime] NOT NULL DEFAULT (getdate()), [FinishStatus] [tinyint] NOT NULL DEFAULT ((0)), PRIMARY KEY CLUSTERED ( [SessionID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO /****** Object: Table [dbo].[DTA_tuninglog] Script Date: 12/31/2008 11:01:21 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DTA_tuninglog]( [SessionID] [int] NOT NULL, [RowID] [int] NOT NULL, [CategoryID] [nvarchar](4) NOT NULL, [Event] [ntext] NULL, [Statement] [ntext] NULL, [Frequency] [int] NOT NULL, [Reason] [ntext] NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO /****** Object: Table [dbo].[DTA_reports_database] Script Date: 12/31/2008 10:57:56 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DTA_reports_database]( [DatabaseID] [int] IDENTITY(1,1) NOT NULL, [SessionID] [int] NOT NULL, [DatabaseName] [sysname] NOT NULL, [IsDatabaseSelectedToTune] [int] NULL, PRIMARY KEY CLUSTERED ( [DatabaseID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[DTA_reports_query] Script Date: 12/31/2008 10:59:42 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DTA_reports_query]( [QueryID] [int] NOT NULL, [SessionID] [int] NOT NULL, [StatementType] [smallint] NOT NULL, [StatementString] [ntext] NOT NULL, [CurrentCost] [float] NOT NULL, [RecommendedCost] [float] NOT NULL, [Weight] [float] NOT NULL, [EventString] [ntext] NULL, [EventWeight] [float] NOT NULL, CONSTRAINT [DTA_reports_query_pk] PRIMARY KEY CLUSTERED ( [SessionID] ASC, [QueryID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO /****** Object: Table [dbo].[DTA_reports_querydatabase] Script Date: 12/31/2008 11:00:04 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DTA_reports_querydatabase]( [QueryID] [int] NOT NULL, [SessionID] [int] NOT NULL, [DatabaseID] [int] NOT NULL ) ON [PRIMARY] GO /****** Object: Table [dbo].[DTA_reports_table] Script Date: 12/31/2008 11:00:52 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DTA_reports_table]( [TableID] [int] IDENTITY(1,1) NOT NULL, [DatabaseID] [int] NOT NULL, [SchemaName] [sysname] NOT NULL, [TableName] [sysname] NOT NULL, [IsView] [bit] NOT NULL DEFAULT ((0)), PRIMARY KEY CLUSTERED ( [TableID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[DTA_reports_partitionfunction] Script Date: 12/31/2008 10:58:57 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DTA_reports_partitionfunction]( [PartitionFunctionID] [int] IDENTITY(1,1) NOT NULL, [DatabaseID] [int] NOT NULL, [PartitionFunctionName] [sysname] NOT NULL, [PartitionFunctionDefinition] [ntext] NOT NULL, PRIMARY KEY CLUSTERED ( [PartitionFunctionID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO /****** Object: Table [dbo].[DTA_reports_partitionscheme] Script Date: 12/31/2008 10:59:19 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DTA_reports_partitionscheme]( [PartitionSchemeID] [int] IDENTITY(1,1) NOT NULL, [PartitionFunctionID] [int] NOT NULL, [PartitionSchemeName] [sysname] NOT NULL, [PartitionSchemeDefinition] [ntext] NOT NULL, PRIMARY KEY CLUSTERED ( [PartitionSchemeID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO /****** Object: Table [dbo].[DTA_reports_tableview] Script Date: 12/31/2008 11:01:02 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DTA_reports_tableview]( [TableID] [int] NOT NULL, [ViewID] [int] NOT NULL ) ON [PRIMARY] GO /****** Object: Table [dbo].[DTA_reports_querytable] Script Date: 12/31/2008 11:00:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DTA_reports_querytable]( [QueryID] [int] NOT NULL, [SessionID] [int] NOT NULL, [TableID] [int] NOT NULL ) ON [PRIMARY] GO /****** Object: Table [dbo].[DTA_reports_index] Script Date: 12/31/2008 10:58:24 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DTA_reports_index]( [IndexID] [int] IDENTITY(1,1) NOT NULL, [TableID] [int] NOT NULL, [IndexName] [sysname] NOT NULL, [IsClustered] [bit] NOT NULL DEFAULT ((0)), [IsUnique] [bit] NOT NULL DEFAULT ((0)), [IsHeap] [bit] NOT NULL DEFAULT ((1)), [IsExisting] [bit] NOT NULL DEFAULT ((1)), [Storage] [float] NOT NULL, [NumRows] [int] NOT NULL, [IsRecommended] [bit] NOT NULL DEFAULT ((0)), [RecommendedStorage] [float] NOT NULL, [PartitionSchemeID] [int] NULL, [SessionUniquefier] [int] NULL, PRIMARY KEY CLUSTERED ( [IndexID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[DTA_reports_column] Script Date: 12/31/2008 10:57:42 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DTA_reports_column]( [ColumnID] [int] IDENTITY(1,1) NOT NULL, [TableID] [int] NOT NULL, [ColumnName] [sysname] NOT NULL, PRIMARY KEY CLUSTERED ( [ColumnID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[DTA_reports_queryindex] Script Date: 12/31/2008 11:00:19 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DTA_reports_queryindex]( [QueryID] [int] NOT NULL, [SessionID] [int] NOT NULL, [IndexID] [int] NOT NULL, [IsRecommendedConfiguration] [bit] NOT NULL ) ON [PRIMARY] GO /****** Object: Table [dbo].[DTA_reports_querycolumn] Script Date: 12/31/2008 10:59:50 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DTA_reports_querycolumn]( [QueryID] [int] NOT NULL, [SessionID] [int] NOT NULL, [ColumnID] [int] NOT NULL ) ON [PRIMARY] GO /****** Object: Table [dbo].[DTA_reports_indexcolumn] Script Date: 12/31/2008 10:58:39 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DTA_reports_indexcolumn]( [IndexID] [int] NOT NULL, [ColumnID] [int] NOT NULL, [ColumnOrder] [int] NULL, [PartitionColumnOrder] [int] NOT NULL DEFAULT ((0)), [IsKeyColumn] [bit] NOT NULL DEFAULT ((1)), [IsDescendingColumn] [bit] NOT NULL DEFAULT ((1)) ) ON [PRIMARY] GO /****** Object: StoredProcedure [dbo].[sp_DTA_check_permission] Script Date: 12/31/2008 10:53:21 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create procedure [dbo].[sp_DTA_check_permission] @SessionID int as begin declare @retcode int declare @dbname nvarchar(128) declare @sql nvarchar(256) declare @dbid int set nocount on -- Check if SA if (isnull(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1) begin return(0) end -- if not yukon return if (patindex('%9.00.%',@@version) = 0) begin return (1) end -- declare and open a cursor and get all the databases specified in the input declare db_cursor cursor for select DatabaseName from DTA_reports_database where SessionID = @SessionID and IsDatabaseSelectedToTune = 1 -- open open db_cursor -- fetch first db name fetch next from db_cursor into @dbname -- loop and get all the databases selected to tune while @@fetch_status = 0 begin -- build use db string select @dbid = DB_ID(@dbname) -- In Yukon this masks the error messages set @sql = N'begin try dbcc autopilot(5,@dbid) WITH NO_INFOMSGS end try begin catch set @retcode = 1 end catch' execute sp_executesql @sql , N'@dbid int output, @retcode int OUTPUT' , @dbid output , @retcode output -- if caller is not member of dbo if (@retcode = 1) begin -- close and reset cursor,switch context to current -- database and return 1 close db_cursor deallocate db_cursor return(1) end fetch from db_cursor into @dbname end -- close and reset cursor,switch context to current -- database and return 1 close db_cursor deallocate db_cursor return(0) end GO /****** Object: StoredProcedure [dbo].[sp_DTA_index_usage_helper_xml] Script Date: 12/31/2008 10:54:29 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create procedure [dbo].[sp_DTA_index_usage_helper_xml] @SessionID int, @IsRecommended int as begin select 1 as Tag, NULL as Parent, '' as [IndexUsageReport!1!!ELEMENT], case when @IsRecommended = 1 then 'false' else 'true' end as [IndexUsageReport!1!Current], NULL as [Database!2!DatabaseID!hide], NULL as [Database!2!Name!ELEMENT] , NULL as [Schema!3!Name!ELEMENT] , NULL as [Table!4!TableID!hide], NULL as [Table!4!Name!ELEMENT], NULL as [Index!5!IndexID!hide], NULL as [Index!5!Name!ELEMENT], NULL as [Index!5!NumberOfReferences!ELEMENT], NULL as [Index!5!PercentUsage!ELEMENT] union all select 2 as Tag, 1 as Parent, NULL as [IndexUsageReport!1!!ELEMENT], NULL as [IndexUsageReport!1!Current], D.DatabaseID as [Database!2!DatabaseID!hide], D.DatabaseName as [Database!2!Name!ELEMENT] , NULL as [Schema!3!Name!ELEMENT] , NULL as [Table!4!TableID!hide], NULL as [Table!4!Name!ELEMENT], NULL as [Index!5!IndexID!hide], NULL as [Index!5!Name!ELEMENT], NULL as [Index!5!NumberOfReferences!ELEMENT], NULL as [Index!5!PercentUsage!ELEMENT] from [msdb].[dbo].[DTA_reports_database] as D where D.SessionID = @SessionID and D.DatabaseID in (select D.DatabaseID from [msdb].[dbo].[DTA_reports_queryindex] as QI, [msdb].[dbo].[DTA_reports_index] as I, [msdb].[dbo].[DTA_reports_table] as T, [msdb].[dbo].[DTA_reports_database] as D where QI.IndexID = I.IndexID and I.TableID = T.TableID and T.DatabaseID = D.DatabaseID and D.SessionID = @SessionID and QI.IsRecommendedConfiguration = @IsRecommended GROUP BY D.DatabaseID) union all select 3 as Tag, 2 as Parent, NULL as [IndexUsageReport!1!!ELEMENT], NULL as [IndexUsageReport!1!Current], D.DatabaseID as [Database!2!DatabaseID!hide], D.DatabaseName as [Database!2!Name!ELEMENT] , R.SchemaName as [Schema!3!Name!ELEMENT] , NULL as [Table!4!TableID!hide], NULL as [Table!4!Name!ELEMENT], NULL as [Index!5!IndexID!hide], NULL as [Index!5!Name!ELEMENT], NULL as [Index!5!NumberOfReferences!ELEMENT], NULL as [Index!5!PercentUsage!ELEMENT] from [msdb].[dbo].[DTA_reports_database] as D, ( select D.DatabaseID,T.SchemaName from [msdb].[dbo].[DTA_reports_queryindex] as QI, [msdb].[dbo].[DTA_reports_index] as I, [msdb].[dbo].[DTA_reports_table] as T, [msdb].[dbo].[DTA_reports_database] as D where QI.IndexID = I.IndexID and I.TableID = T.TableID and T.DatabaseID = D.DatabaseID and QI.IsRecommendedConfiguration = @IsRecommended and D.SessionID = @SessionID GROUP BY D.DatabaseID,T.SchemaName ) R where D.SessionID = @SessionID and D.DatabaseID = R.DatabaseID union all select 4 as Tag, 3 as Parent, NULL as [IndexUsageReport!1!!ELEMENT], NULL as [IndexUsageReport!1!Current], D.DatabaseID as [Database!2!DatabaseID!hide], D.DatabaseName as [Database!2!Name!ELEMENT] , R.SchemaName as [Schema!3!Name!ELEMENT] , R.TableID as [Table!4!TableID!hide], T.TableName as [Table!4!Name!ELEMENT], NULL as [Index!5!IndexID!hide], NULL as [Index!5!Name!ELEMENT], NULL as [Index!5!NumberOfReferences!ELEMENT], NULL as [Index!5!PercentUsage!ELEMENT] from [msdb].[dbo].[DTA_reports_database] as D, [msdb].[dbo].[DTA_reports_table] as T, ( select D.DatabaseID,T.SchemaName,T.TableID from [msdb].[dbo].[DTA_reports_queryindex] as QI, [msdb].[dbo].[DTA_reports_index] as I, [msdb].[dbo].[DTA_reports_table] as T, [msdb].[dbo].[DTA_reports_database] as D where QI.IndexID = I.IndexID and I.TableID = T.TableID and T.DatabaseID = D.DatabaseID and D.SessionID = @SessionID and QI.IsRecommendedConfiguration = @IsRecommended GROUP BY D.DatabaseID,T.SchemaName, T.TableID ) R where D.SessionID = @SessionID and D.DatabaseID = R.DatabaseID and R.TableID = T.TableID and T.DatabaseID = D.DatabaseID union all select 5 as Tag, 4 as Parent, NULL as [IndexUsageReport!1!!ELEMENT], NULL as [IndexUsageReport!1!Current], D1.DatabaseID as [Database!2!DatabaseID!hide], D1.DatabaseName as [Database!2!Name!ELEMENT] , T1.SchemaName as [Schema!3!Name!ELEMENT] , T1.TableID as [Table!4!TableID!hide], T1.TableName as [Table!4!Name!ELEMENT], I1.IndexID as [Index!5!IndexID!hide], I1.IndexName as [Index!5!Name!ELEMENT], R.Count as [Index!5!NumberOfReferences!ELEMENT], CAST(R.Usage as decimal(38,2)) as [Index!5!PercentUsage!ELEMENT] from [msdb].[dbo].[DTA_reports_database] as D1 , [msdb].[dbo].[DTA_reports_index] as I1, [msdb].[dbo].[DTA_reports_table] as T1, ( select D.DatabaseID,T.TableID , I.IndexID ,SUM(Q.Weight) as Count, 100.0 * SUM(Q.Weight) / ( 1.0 * ( select CASE WHEN SUM(Q.Weight) > 0 THEN SUM(Q.Weight) else 1 end from [msdb].[dbo].[DTA_reports_query] as Q where Q.SessionID = @SessionID )) as Usage from [msdb].[dbo].[DTA_reports_index] as I LEFT OUTER JOIN [msdb].[dbo].[DTA_reports_queryindex] as QI ON QI.IndexID = I.IndexID LEFT OUTER JOIN [msdb].[dbo].[DTA_reports_query] as Q ON QI.QueryID = Q.QueryID JOIN [msdb].[dbo].[DTA_reports_table] as T ON I.TableID = T.TableID JOIN [msdb].[dbo].[DTA_reports_database] as D ON T.DatabaseID = D.DatabaseID and Q.SessionID = QI.SessionID and QI.IsRecommendedConfiguration = @IsRecommended and Q.SessionID = @SessionID GROUP BY I.IndexID,T.TableID,D.DatabaseID) as R where R.DatabaseID = D1.DatabaseID and R.TableID = T1.TableID and R.IndexID = I1.IndexID and D1.SessionID = @SessionID and R.Count > 0 order by [Database!2!DatabaseID!hide],[Schema!3!Name!ELEMENT],[Table!4!TableID!hide], [Index!5!NumberOfReferences!ELEMENT] , [Index!5!IndexID!hide] FOR XML EXPLICIT end GO /****** Object: StoredProcedure [dbo].[sp_DTA_index_usage_helper_relational] Script Date: 12/31/2008 10:54:26 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create procedure [dbo].[sp_DTA_index_usage_helper_relational] @SessionID int, @IsRecommended int as begin select D1.DatabaseName as 'Database Name' ,T1.SchemaName as 'Schema Name' ,T1.TableName as 'Table/View Name' ,I1.IndexName as 'Index Name' ,R.Count as 'Number of references' ,CAST(R.Usage as decimal(38,2)) as 'Percent Usage' from DTA_reports_database as D1 , DTA_reports_index as I1, DTA_reports_table as T1, ( select D.DatabaseID,T.TableID , I.IndexID ,SUM(Q.Weight) as Count, 100.0 * SUM(Q.Weight) / ( 1.0 * ( select CASE WHEN SUM(Q.Weight) > 0 THEN SUM(Q.Weight) else 1 end from [msdb].[dbo].[DTA_reports_query] as Q where Q.SessionID = @SessionID )) as Usage from [msdb].[dbo].[DTA_reports_index] as I LEFT OUTER JOIN [msdb].[dbo].[DTA_reports_queryindex] as QI ON QI.IndexID = I.IndexID LEFT OUTER JOIN [msdb].[dbo].[DTA_reports_query] as Q ON QI.QueryID = Q.QueryID JOIN [msdb].[dbo].[DTA_reports_table] as T ON I.TableID = T.TableID JOIN [msdb].[dbo].[DTA_reports_database] as D ON T.DatabaseID = D.DatabaseID and Q.SessionID = QI.SessionID and QI.IsRecommendedConfiguration = @IsRecommended and Q.SessionID = @SessionID GROUP BY I.IndexID,T.TableID,D.DatabaseID) as R where R.DatabaseID = D1.DatabaseID and R.TableID = T1.TableID and R.IndexID = I1.IndexID and D1.SessionID = @SessionID and R.Count > 0 order by R.Count desc end GO /****** Object: StoredProcedure [dbo].[sp_DTA_database_access_helper_xml] Script Date: 12/31/2008 10:53:31 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create procedure [dbo].[sp_DTA_database_access_helper_xml] @SessionID int as begin select 1 as Tag, NULL as Parent, '' as [DatabaseAccessReport!1!!ELEMENT], NULL as [Database!2!Name!ELEMENT] , NULL as [Database!2!NumberOfReferences!ELEMENT], NULL as [Database!2!PercentUsage!ELEMENT] union all select 2 as Tag, 1 as Parent,NULL,D1.DatabaseName , R.Count , CAST(R.Usage as decimal(38,2)) from [msdb].[dbo].[DTA_reports_database] as D1 , ( select D.DatabaseID,SUM(Q.Weight) as Count, 100.0 * SUM(Q.Weight) / ( 1.0 * ( select CASE WHEN SUM(Q.Weight) > 0 THEN SUM(Q.Weight) else 1 end from [msdb].[dbo].[DTA_reports_query] as Q where Q.SessionID = @SessionID )) as Usage from [msdb].[dbo].[DTA_reports_database] as D LEFT OUTER JOIN [msdb].[dbo].[DTA_reports_querydatabase] as QD ON QD.DatabaseID = D.DatabaseID LEFT OUTER JOIN [msdb].[dbo].[DTA_reports_query] as Q ON QD.QueryID = Q.QueryID and Q.SessionID = QD.SessionID and Q.SessionID = @SessionID GROUP BY D.DatabaseID ) as R where R.DatabaseID = D1.DatabaseID and D1.SessionID = @SessionID and R.Count > 0 order by Tag,[Database!2!NumberOfReferences!ELEMENT] desc FOR XML EXPLICIT end GO

DTA_Objects_SQL.txt

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

SQL Server Backup Script with TimeStamp (0)	2015.07.02
Table의 사용량(RowCount, Size) 정보 보기 (0)	2015.04.24
[Powershell]SQL Server 복원 작업 (0)	2015.04.07
Upgrading SQL Server 2012 License Edition (0)	2015.01.07
테이블들의 Row Count 가져오기! (0)	2014.03.22

Trackback: Comment:

[Powershell]SQL Server 복원 작업

Posted by Alvin You

2015. 4. 7. 10:43 SQL Server

프로젝트를 진행하다 보면 운영 DB를 백업 받아, 다시 DEV/TEST 계에 데이터를 다시 올리는 경우가 빈번하게 발생합니다. 아래 Powershell 스크립트 내용은 Backup 받은 파일을 Powershell을 이용해 Restore 하는 내용을 담고 있습니다.

업무에 참고하세요.

Import-Module "SQLPS" -DisableNameChecking
$sqlsvr = New-Object -TypeName  Microsoft.SQLServer.Management.Smo.Server("localhost")
$BackupFile = "D:\SQLServer\DynamicsAX.BAK"

#echo "" 
#echo "Databases" 
#echo "———" 
#foreach ( $db in $sqlsvr.Databases) { write-host $db.name }

echo " "
echo "Begin Restore" 
echo "============="
$Restore = New-Object "Microsoft.SqlServer.Management.Smo.Restore"
$Restore.NoRecovery = $false 
$Restore.ReplaceDatabase = $true 
$Restore.Action = "Database" 
$Restore.PercentCompleteNotification = 10 
$BackupDevice = New-Object ("Microsoft.SqlServer.Management.Smo.BackupDeviceItem") ($BackupFile, "File") 
$Restore.Devices.Add($BackupDevice) 
$RestoreDetails = $Restore.ReadBackupHeader($sqlsvr) 
$logicalFileNameList = $Restore.ReadFileList($sqlsvr)
$Restore.Database = $RestoreDetails.Rows[0]["DatabaseName"]

foreach($row in $logicalFileNameList) { 
    $RestoreDBFile = new-object("Microsoft.SqlServer.Management.Smo.RelocateFile") 
    $RestoreDBFile.LogicalFileName = $row["LogicalName"] 
    $RestoreDBFile.PhysicalFileName = $row["PhysicalName"] 
    $Restore.RelocateFiles.Add($RestoreDBFile) 
}

$Restore.SqlRestore($sqlsvr) 
write-host ("Completed the Database Restore operation on server for Database " +  
                 $RestoreDetails.Rows[0]["DatabaseName"] + " on server $server")

저작자표시

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

Table의 사용량(RowCount, Size) 정보 보기 (0)	2015.04.24
Failed to initialize MSDB database for tuning (exit code: -1073741819). (DTAClient) (0)	2015.04.15
Upgrading SQL Server 2012 License Edition (0)	2015.01.07
테이블들의 Row Count 가져오기! (0)	2014.03.22
Object(SP, UDF 등) 검색 (0)	2014.03.22

Trackback: Comment:

Upgrading SQL Server 2012 License Edition

Posted by Alvin You

2015. 1. 7. 01:00 SQL Server

Dynamics AX의 Power BI가 SQL Server Enterprise에서 구동되는 관계로 로컬에 설치되어 있는 SQL Server의 License Edition을 변경해야 될 필요성이 생겼습니다. 그래서, 관련 자료를 찾아 보니 아래 방법을 통해서 License Edition을 변경 할 수 있습니다.

아래 절차는 Standard Edition à Enterprise Edition으로 변경하는 과정입니다.

1. SQL Server Enterprise Edition 설치 CD를 넣고 SQL Server Installation Center à Edition Upgrade를 실행합니다.

2. Product Key를 확인합니다.

3. 업그레이드 할 SQL Server Instance를 선택합니다.

4. 업그레이드 규칙을 확인합니다.

5. 업그레이드를 진행합니다.

6.Standard à Enterprise Edition 업그레이드가 완료되었습니다.

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

Failed to initialize MSDB database for tuning (exit code: -1073741819). (DTAClient) (0)	2015.04.15
[Powershell]SQL Server 복원 작업 (0)	2015.04.07
테이블들의 Row Count 가져오기! (0)	2014.03.22
Object(SP, UDF 등) 검색 (0)	2014.03.22
MS SQL Server 2008 R2에서 Collation 변경하기 (0)	2014.03.22

Trackback: Comment:

시스템 테이블을 이용한 Table Names, Column Names, Data Types 조회

Posted by Alvin You

2014. 3. 13. 11:26 SQL Server

프로젝트 산출물의 하나인 테이블 명세서나 DDL 스크립트를 참고해서 데이터를 분석하는 방법 보다는 아래 Query를 이용하는 방법! 강츄~

select SchemaName=SCHEMA_NAME(t.schema_id), TableName=t.name, ColumnName=c.name, DataType=ty.name, TextLength=c.max_length, c.precision, c.scale
from sys.tables t
join sys.columns c on c.object_id = t.object_id
join sys.types ty on ty.user_type_id =c.user_type_id
order by 1, 2, 3

저작자표시

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

Object(SP, UDF 등) 검색 (0)	2014.03.22
MS SQL Server 2008 R2에서 Collation 변경하기 (0)	2014.03.22
모든 DB 테이블에서 특정값을 찾는 프로시저 (0)	2014.03.13
모든 DB 테이블에서 특정값을 찾고 변경하는 프로시저 (0)	2014.03.13
sp_MSforeachdb, sp_MSforeachtable 프로시저 활용 (0)	2014.03.13

Trackback: Comment:

모든 DB 테이블에서 특정값을 찾는 프로시저

Posted by Alvin You

2014. 3. 13. 11:24 SQL Server

SQL Server Database내의 모든 테이블의 'char', 'varchar', 'nchar', 'nvarchar' 값에 들어있는 특정값을 찾아서 출력해 주는 Stored Procedure입니다.

향후 데이터 변경 프로젝트에서 사용하면 유용할 듯 합니다.

--

CREATE PROC [dbo].[SearchAllTables]
(
@SearchStr nvarchar(100)
)
AS
BEGIN

-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 28th July 2002 22:50 GMT

DECLARE @Results TABLE(ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO @Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END

SELECT ColumnName, ColumnValue FROM @Results
END

저작자표시

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

MS SQL Server 2008 R2에서 Collation 변경하기 (0)	2014.03.22
시스템 테이블을 이용한 Table Names, Column Names, Data Types 조회 (0)	2014.03.13
모든 DB 테이블에서 특정값을 찾고 변경하는 프로시저 (0)	2014.03.13
sp_MSforeachdb, sp_MSforeachtable 프로시저 활용 (0)	2014.03.13
SQL Server db lock 확인 방법 (0)	2013.09.24

Trackback: Comment:

모든 DB 테이블에서 특정값을 찾고 변경하는 프로시저

Posted by Alvin You

2014. 3. 13. 11:23 SQL Server

이번엔 찾은 값을 다른값으로 변경해주는 기능까지 포함된 Stored Procedure입니다.
유용하게 쓰셈!

--
CREATE PROC [dbo].[SearchAndReplace]

(
@SearchStr nvarchar(100),
@ReplaceStr nvarchar(100)
)
AS
BEGIN

-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string and replace it with another string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 2nd November 2002 13:50 GMT

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110), @SQL nvarchar(4000), @RCTR int
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
SET @RCTR = 0

WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
SET @SQL= 'UPDATE ' + @TableName +
' SET ' + @ColumnName
+ ' = REPLACE(' + @ColumnName + ', '
+ QUOTENAME(@SearchStr, '''') + ', ' + QUOTENAME(@ReplaceStr, '''') +
') WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
EXEC (@SQL)
SET @RCTR = @RCTR + @@ROWCOUNT
END
END
END

SELECT 'Replaced ' + CAST(@RCTR AS varchar) + ' occurence(s)' AS 'Outcome'
END

저작자표시

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

MS SQL Server 2008 R2에서 Collation 변경하기 (0)	2014.03.22
시스템 테이블을 이용한 Table Names, Column Names, Data Types 조회 (0)	2014.03.13
모든 DB 테이블에서 특정값을 찾는 프로시저 (0)	2014.03.13
sp_MSforeachdb, sp_MSforeachtable 프로시저 활용 (0)	2014.03.13
SQL Server db lock 확인 방법 (0)	2013.09.24

Trackback: Comment:

sp_MSforeachdb, sp_MSforeachtable 프로시저 활용

Posted by Alvin You

2014. 3. 13. 11:21 SQL Server

master 데이터베이스는 sp_MSforeachtdb, sp_MSForeachtable 과 같은 유용한 Stored Procedure가 있습니다.

[sp_MSforeachdb]

sp_MSforeachdb 프로시저는 SQL Server 인스턴스 내에 있는 모든 데이터베이스의 이름을 반환하며 이 이름을 참조하여 지정된 반복문을수행합니다..

아래 스크립트는 인스턴스내의 모든 데이터베이스의 helpfile 정보를 보여줍니다..

use master

exec sp_MSforeachdb 'use ? exec sp_helpfile'

[sp_MSforeachtable]

sp_MSforeachtable 프로시저는 현재 데이터베이스 내의모든 테이블 이름을 반환하며 이 테이블이름을 참조하여 지정된 반복문을 수행 한다.

아래 스크립트는 해당 데이터베이스에 존재하는 모든 테이블의 row count를테이블에 삽입하여 출력한다.

begin try

create table #rowcount (tablename varchar(128), rowcnt int)

end try begin catch end catch

exec sp_MSforeachtable

'insert into #rowcount select ''?'',

count(*) from ?'

select top 5 * from #rowcount

order by tablename

drop table #rowcount

위의 두 시스템 프로시저를 잘 활용하면 사용자 프로시저 생성시 코드를 간결하고 빠르게 개발 할 수 있을 듯 하다.

[참고자료]

l http://www.codeproject.com/Articles/459536/SQL-Server-Applying-Filter-on-sp_MSforeachDB#

l http://www.databasejournal.com/features/mssql/article.php/3441031/SQL-Server-Undocumented-Stored-Procedures-spMSforeachtable-and-spMSforeachdb.htm

저작자표시

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

MS SQL Server 2008 R2에서 Collation 변경하기 (0)	2014.03.22
시스템 테이블을 이용한 Table Names, Column Names, Data Types 조회 (0)	2014.03.13
모든 DB 테이블에서 특정값을 찾는 프로시저 (0)	2014.03.13
모든 DB 테이블에서 특정값을 찾고 변경하는 프로시저 (0)	2014.03.13
SQL Server db lock 확인 방법 (0)	2013.09.24

Trackback: Comment:

SQL Server db lock 확인 방법

Posted by Alvin You

2013. 9. 24. 22:47 SQL Server

AX Client에서 해당 프로그램을 실행시켰는데, 해당 프로그램이 아무런 응답없이 멈춰있다면 SQL Server DB Lock을 의심해 볼 수도 있습니다.

현재 진행중인 프로젝트가 DB Transaction이 많아, Stored Procedure를 호출해서 구성되는 화면이 많다보니 종종 화면이 멈춘듯한 느낌이 받을때가 많습니다.

이럴 경우 확인 방법은 sp_who2 라는 SQL 명령어를 통해서 blkBy 칼럼을 통해 확인할 수 있습니다.

실행중인 SQL문장을 보고 싶다면 아래 내용을 통해 확인이 가능합니다.

DECLARE @sqltext VARBINARY(128) SELECT @sqltext = sql_handle FROM sys.sysprocesses WHERE spid = 70


SELECT TEXT 
FROM sys.dm_exec_sql_text(@sqltext) 
GO
 
당연히 Lock 이 걸린 프로세스를 Kill 명령어를 통해서 종료시키면, 해당 lock은 풀립니다.

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

MS SQL Server 2008 R2에서 Collation 변경하기 (0)	2014.03.22
시스템 테이블을 이용한 Table Names, Column Names, Data Types 조회 (0)	2014.03.13
모든 DB 테이블에서 특정값을 찾는 프로시저 (0)	2014.03.13
모든 DB 테이블에서 특정값을 찾고 변경하는 프로시저 (0)	2014.03.13
sp_MSforeachdb, sp_MSforeachtable 프로시저 활용 (0)	2014.03.13

Trackback: Comment:

«이전 1 다음»

준영이 아빠의 Dynamics AX World!

준영이 아빠의 Dynamics AX World!

SQL Server Backup Script with TimeStamp

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

Table의 사용량(RowCount, Size) 정보 보기

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

Failed to initialize MSDB database for tuning (exit code: -1073741819). (DTAClient)

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

[Powershell]SQL Server 복원 작업

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

Upgrading SQL Server 2012 License Edition

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

시스템 테이블을 이용한 Table Names, Column Names, Data Types 조회

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

모든 DB 테이블에서 특정값을 찾는 프로시저

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

모든 DB 테이블에서 특정값을 찾고 변경하는 프로시저

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

sp_MSforeachdb, sp_MSforeachtable 프로시저 활용

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

SQL Server db lock 확인 방법

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

티스토리툴바

2024.4
일	월	화	수	목	금	토
	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