이스트럭님 글(http://www.sqler.com/390533) 보다가… DB 엔진의 단일사용자 모드도 있지만 Database별로도 단일사용자 모드를 지정할 수 있습니다.
방법은 다음과 같습니다.
|
-- DB 단일사용자모드로(둘 중 하나) exec sp_dboption TestDB, 'single_user',true Go ALTER DATABASE TestDB SET SINGLE_USER Go -- DB 멀티사용자모드로(둘 중 하나) exec sp_dboption TestDB, 'single_user', false Go ALTER DATABASE TestDB SET MULTI_USER Go |
아래 명령은 잘 아실 테고요… 이를 실행하면 Virtual Log File이 몇 개인지 보여줍니다.
DBCC LOGINFO(DB명)
하지만, 전체적으로 얼마나 되는지 보려면 약간의 삽질을 해야 하는데, 아래 명령으로 편하게 보실 수 있습니다.
CREATE TABLE #eachDB(
FileID INT
, FileSize BIGINT
, StartOffset BIGINT
, FSeqNo BIGINT
, [Status] BIGINT
, Parity BIGINT
, CreateLSN NUMERIC(38)
);
CREATE TABLE #summary(
Database_Name sysname
, VLF_count INT
, Log_File_count INT
);
EXEC sp_MSforeachdb N'Use [?];
Insert Into #eachDB
Exec sp_executeSQL N''DBCC LogInfo(?)'';
Insert Into #summary
Select DB_Name(), Count(*), Count(Distinct FileID)
From #eachDB;
Truncate Table #eachDB;'
-- 결과 확인하기!
SELECT *
FROM #summary
ORDER BY VLF_count DESC;
-- 결과 확인 후 임시테이블 삭제
DROP TABLE #eachDB;
DROP TABLE #summary;
제가 직접 작성한 건 아니지만...
의외로 이거 쓸 데 많습니다.
권한 분리할 때 꼭 필요한 부분이라서요...
단, 실제 사용자에 따라 달리 줘야 하는 경우가 있으니 주의하시기 바랍니다.
SQL Server 2000
|
-- SELECT @CMD1 = 'GRANT EXEC ON ' + '[' + @OwnerName + ']' + '.' + '[' + @ObjectName + ']' + ' TO [' + @user +']' CREATE PROCEDURE spGrantExectoAllStoredProcs @user sysname AS /*---------------------------------------------------------------------------- -- Object Name: spGrantExectoAllStoredProcs -- Author: Edgewood Solutions -- Development Date: 03.19.2007 -- Called By: TBD -- Description: Issue GRANT EXEC statement for all stored procedures -- based on the user name that is passed in to this stored procedure -- Project: SQL Server Security -- Database: User defined databases -- Business Process: SQL Server Security -- ---------------------------------------------------------------------------- -- Num | CRF ID | Date Modified | Developer | Description ---------------------------------------------------------------------------- -- 001 | N\A | 03.15.2007 | Edgewood | Original code for the GRANT -- EXEC process -- -- */ SET NOCOUNT ON -- 1 - Variable declarations DECLARE @CMD1 varchar(8000) DECLARE @MAXOID int DECLARE @OwnerName varchar(128) DECLARE @ObjectName varchar(128) -- 2 - Create temporary table CREATE TABLE #StoredProcedures (OID int IDENTITY (1,1), StoredProcOwner varchar(128) NOT NULL, StoredProcName varchar(128) NOT NULL) -- 3 - Populate temporary table INSERT INTO #StoredProcedures (StoredProcOwner, StoredProcName) SELECT u.[Name], o.[Name] FROM dbo.sysobjects o INNER JOIN dbo.sysusers u ON o.uid = u.uid WHERE o.Type = 'P' AND o.[Name] NOT LIKE 'dt_%' -- 4 - Capture the @MAXOID value SELECT @MAXOID = MAX(OID) FROM #StoredProcedures -- 5 - WHILE loop WHILE @MAXOID > 0 BEGIN -- 6 - Initialize the variables SELECT @OwnerName = StoredProcOwner, @ObjectName = StoredProcName FROM #StoredProcedures WHERE OID = @MAXOID -- 7 - Build the string SELECT @CMD1 = 'GRANT EXEC ON ' + '[' + @OwnerName + ']' + '.' + '[' + @ObjectName + ']' + ' TO ' + @user -- 8 - Execute the string -- SELECT @CMD1 EXEC(@CMD1) -- 9 - Decrement @MAXOID SET @MAXOID = @MAXOID - 1 END -- 10 - Drop the temporary table DROP TABLE #StoredProcedures SET NOCOUNT OFF GO |
SQL Server 2005 이상
|
CREATE PROCEDURE spGrantExectoAllStoredProcs @user sysname AS /*---------------------------------------------------------------------------- -- Object Name: spGrantExectoAllStoredProcs -- Author: Edgewood Solutions -- Development Date: 03.19.2007 -- Called By: TBD -- Description: Issue GRANT EXEC statement for all stored procedures -- based on the user name that is passed in to this stored procedure -- Project: SQL Server Security -- Database: User defined databases -- Business Process: SQL Server Security -- ---------------------------------------------------------------------------- -- Num | CRF ID | Date Modified | Developer | Description ---------------------------------------------------------------------------- -- 001 | N\A | 03.15.2007 | Edgewood | Original code for the GRANT -- EXEC process -- -- */ SET NOCOUNT ON -- 1 - Variable declarations DECLARE @CMD1 varchar(8000) DECLARE @MAXOID int DECLARE @OwnerName varchar(128) DECLARE @ObjectName varchar(128) -- 2 - Create temporary table CREATE TABLE #StoredProcedures (OID int IDENTITY (1,1), StoredProcOwner varchar(128) NOT NULL, StoredProcName varchar(128) NOT NULL) -- 3 - Populate temporary table INSERT INTO #StoredProcedures (StoredProcOwner, StoredProcName) SELECT ROUTINE_SCHEMA, ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME NOT LIKE 'dt_%' AND ROUTINE_TYPE = 'PROCEDURE' -- 4 - Capture the @MAXOID value SELECT @MAXOID = MAX(OID) FROM #StoredProcedures -- 5 - WHILE loop WHILE @MAXOID > 0 BEGIN -- 6 - Initialize the variables SELECT @OwnerName = StoredProcOwner, @ObjectName = StoredProcName FROM #StoredProcedures WHERE OID = @MAXOID -- 7 - Build the string SELECT @CMD1 = 'GRANT EXEC ON ' + '[' + @OwnerName + ']' + '.' + '[' + @ObjectName + ']' + ' TO ' + @user -- 8 - Execute the string -- SELECT @CMD1 EXEC(@CMD1) -- 9 - Decrement @MAXOID SET @MAXOID = @MAXOID - 1 END -- 10 - Drop the temporary table DROP TABLE #StoredProcedures SET NOCOUNT OFF GO |
영어의 압박이 좀 있긴 하지만.
그래도 Parallelism, Workers, Threads 등을 이해하는 데 도움이 될 것 같네요.
http://www.simple-talk.com/content/article.aspx?article=1250
안녕하세요? 쓸만한게없네 윤선식입니다.
이전 아티클에서 “Database Engine Tuning Advisor”에 대한 소개를 했는데요, 이후 작업에 대해 설명하려고 합니다.
이 DTA 참 좋긴 한데, 한 가지 문제가 있습니다. 바로 MSDB에 모든 기록이 남는다는 것이죠.
1. 다음과 같은 후유증이 있습니다.
가. MSDB에 DTA 관련 테이블이 그대로 존재합니다. 특히 DTA_tuninglog 테이블등의 Row수는 튜닝대상 쿼리가 많으면 많을수록 사이즈가 클 수밖에 없습니다.
나. 백업파일의 사이즈가 커집니다. 그림과 같이 갑자기 사이즈가 커진 것을 알 수 있습니다.
다. 그렇다면 MSDB의 사이즈는??? 사실 Job을 그리 많이 돌리는 Database가 아니기 때문에 이렇게까지 클 이유가 없죠.
2. 굳이 이를 보관할 필요가 있다면 별도의 DB에 보관하시고, 만약 보관의 필요가 없다면 다음 링크에 있는 명령어를 실행해 봅니다.
http://support.microsoft.com/kb/899634
|
/* Purpose of the Script This script cleans up objects created by DTA client on the target server (server being tuned). DTA creates support tables and stored procedures on the target server. The schema of the DTA tables and the DTA SP interfaces changed from Beta 2. When to use it If a Beta 2 DTA client was used to tune/evaluate against the target server then this script needs to be executed (against the target server) for later versions of DTA to function properly. Impact Previous session details are lost. */ go use msdb go -- Drop DTA msdb Tables if (exists (select * from msdb.dbo.sysobjects where (name = N'DTA_reports_indexcolumn') and (type = 'U') and (uid = user_id('dbo')))) drop table dbo.DTA_reports_indexcolumn if (exists (select * from msdb.dbo.sysobjects where (name = N'DTA_reports_querycolumn') and (type = 'U') and (uid = user_id('dbo')))) drop table dbo.DTA_reports_querycolumn if (exists (select * from msdb.dbo.sysobjects where (name = N'DTA_reports_querytable') and (type = 'U') and (uid = user_id('dbo')))) drop table dbo.DTA_reports_querytable if (exists (select * from msdb.dbo.sysobjects where (name = N'DTA_reports_tableview') and (type = 'U') and (uid = user_id('dbo')))) drop table dbo.DTA_reports_tableview if (exists (select * from msdb.dbo.sysobjects where (name = N'DTA_reports_querydatabase') and (type = 'U') and (uid = user_id('dbo')))) drop table dbo.DTA_reports_querydatabase if (exists (select * from msdb.dbo.sysobjects where (name = N'DTA_reports_queryindex') and (type = 'U') and (uid = user_id('dbo')))) drop table dbo.DTA_reports_queryindex if (exists (select * from msdb.dbo.sysobjects where (name = N'DTA_reports_column') and (type = 'U') and (uid = user_id('dbo')))) drop table dbo.DTA_reports_column if (exists (select * from msdb.dbo.sysobjects where (name = N'DTA_reports_index') and (type = 'U') and (uid = user_id('dbo')))) drop table dbo.DTA_reports_index if (exists (select * from msdb.dbo.sysobjects where (name = N'DTA_reports_table') and (type = 'U') and (uid = user_id('dbo')))) drop table dbo.DTA_reports_table if (exists (select * from msdb.dbo.sysobjects where (name = N'DTA_reports_query') and (type = 'U') and (uid = user_id('dbo')))) drop table dbo.DTA_reports_query if (exists (select * from msdb.dbo.sysobjects where (name = N'DTA_reports_partitionscheme') and (type = 'U') and (uid = user_id('dbo')))) drop table dbo.DTA_reports_partitionscheme if (exists (select * from msdb.dbo.sysobjects where (name = N'DTA_reports_partitionfunction') and (type = 'U') and (uid = user_id('dbo')))) drop table dbo.DTA_reports_partitionfunction if (exists (select * from msdb.dbo.sysobjects where (name = N'DTA_tuninglog') and (type = 'U') and (uid = user_id('dbo')))) drop table dbo.DTA_tuninglog if (exists (select * from msdb.dbo.sysobjects where (name = N'DTA_reports_database') and (type = 'U') and (uid = user_id('dbo')))) drop table dbo.DTA_reports_database if (exists (select * from msdb.dbo.sysobjects where (name = N'DTA_progress') and (type = 'U') and (uid = user_id('dbo')))) drop table dbo.DTA_progress if (exists (select * from msdb.dbo.sysobjects where (name = N'DTA_output')and (type = 'U')and (uid = user_id('dbo')))) drop table dbo.DTA_output if (exists (select * from msdb.dbo.sysobjects where (name = N'DTA_input') and (type = 'U') and (uid = user_id('dbo')))) drop table dbo.DTA_input -- Drop DTA msdb SP's if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_check_permission' and type = 'P') drop procedure dbo.sp_DTA_check_permission if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_add_session' and type = 'P') drop procedure dbo.sp_DTA_add_session if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_delete_session' and type = 'P') drop procedure dbo.sp_DTA_delete_session if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_get_session_tuning_results' and type = 'P') drop procedure dbo.sp_DTA_get_session_tuning_results if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_set_interactivestatus' and type = 'P') drop procedure dbo.sp_DTA_set_interactivestatus if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_help_session' and type = 'P') drop procedure dbo.sp_DTA_help_session if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_update_session' and type = 'P') drop procedure dbo.sp_DTA_update_session if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_get_tuninglog' and type = 'P') drop procedure dbo.sp_DTA_get_tuninglog if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_index_usage_helper_xml' and type = 'P') drop procedure dbo.sp_DTA_index_usage_helper_xml if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_index_usage_helper_relational' and type = 'P') drop procedure dbo.sp_DTA_index_usage_helper_relational if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_database_access_helper_xml' and type = 'P') drop procedure dbo.sp_DTA_database_access_helper_xml if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_database_access_helper_relational' and type = 'P') drop procedure dbo.sp_DTA_database_access_helper_relational if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_table_access_helper_xml' and type = 'P') drop procedure dbo.sp_DTA_table_access_helper_xml if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_table_access_helper_relational' and type = 'P') drop procedure dbo.sp_DTA_table_access_helper_relational if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_column_access_helper_xml' and type = 'P') drop procedure dbo.sp_DTA_column_access_helper_xml if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_column_access_helper_relational' and type = 'P') drop procedure dbo.sp_DTA_column_access_helper_relational if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_query_costrange_helper_xml' and type = 'P') drop procedure dbo.sp_DTA_query_costrange_helper_xml if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_query_costrange_helper_relational' and type = 'P') drop procedure dbo.sp_DTA_query_costrange_helper_relational if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_query_cost_helper_xml' and type = 'P') drop procedure dbo.sp_DTA_query_cost_helper_xml if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_query_cost_helper_relational' and type = 'P') drop procedure dbo.sp_DTA_query_cost_helper_relational if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_event_weight_helper_xml' and type = 'P') drop procedure dbo.sp_DTA_event_weight_helper_xml if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_event_weight_helper_relational' and type = 'P') drop procedure dbo.sp_DTA_event_weight_helper_relational if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_query_detail_helper_xml' and type = 'P') drop procedure dbo.sp_DTA_query_detail_helper_xml if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_query_detail_helper_relational' and type = 'P') drop procedure dbo.sp_DTA_query_detail_helper_relational if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_query_indexrelations_helper_xml' and type = 'P') drop procedure dbo.sp_DTA_query_indexrelations_helper_xml if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_query_indexrelations_helper_relational' and type = 'P') drop procedure dbo.sp_DTA_query_indexrelations_helper_relational if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_index_current_detail_helper_xml' and type = 'P') drop procedure dbo.sp_DTA_index_current_detail_helper_xml if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_index_recommended_detail_helper_xml' and type = 'P') drop procedure dbo.sp_DTA_index_recommended_detail_helper_xml if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_index_detail_current_helper_relational' and type = 'P') drop procedure dbo.sp_DTA_index_detail_current_helper_relational if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_index_detail_recommended_helper_relational' and type = 'P') drop procedure dbo.sp_DTA_index_detail_recommended_helper_relational if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_view_table_helper_xml' and type = 'P') drop procedure dbo.sp_DTA_view_table_helper_xml if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_view_table_helper_relational' and type = 'P') drop procedure dbo.sp_DTA_view_table_helper_relational if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_wkld_analysis_helper_xml' and type = 'P') drop procedure dbo.sp_DTA_wkld_analysis_helper_xml if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_wkld_analysis_helper_relational' and type = 'P') drop procedure dbo.sp_DTA_wkld_analysis_helper_relational if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_get_session_report' and type = 'P') drop procedure dbo.sp_DTA_get_session_report if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_set_tuninglogtablename' and type = 'P') drop procedure dbo.sp_DTA_set_tuninglogtablename if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_get_tuningoptions' and type = 'P') drop procedure dbo.sp_DTA_get_tuningoptions if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_get_interactivestatus' and type = 'P') drop procedure dbo.sp_DTA_get_interactivestatus if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_insert_progressinformation' and type = 'P') drop procedure dbo.sp_DTA_insert_progressinformation if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_set_progressinformation' and type = 'P') drop procedure dbo.sp_DTA_set_progressinformation if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_set_outputinformation' and type = 'P') drop procedure dbo.sp_DTA_set_outputinformation if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_insert_reports_database' and type = 'P') drop procedure dbo.sp_DTA_insert_reports_database if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_insert_reports_partitionscheme' and type = 'P') drop procedure dbo.sp_DTA_insert_reports_partitionscheme if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_insert_reports_partitionfunction' and type = 'P') drop procedure dbo.sp_DTA_insert_reports_partitionfunction if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_insert_reports_column' and type = 'P') drop procedure dbo.sp_DTA_insert_reports_column if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_insert_reports_tableview' and type = 'P') drop procedure dbo.sp_DTA_insert_reports_tableview if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_insert_reports_query' and type = 'P') drop procedure dbo.sp_DTA_insert_reports_query if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_insert_reports_index' and type = 'P') drop procedure dbo.sp_DTA_insert_reports_index if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_insert_reports_table' and type = 'P') drop procedure dbo.sp_DTA_insert_reports_table if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_insert_reports_queryindex' and type = 'P') drop procedure dbo.sp_DTA_insert_reports_queryindex if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_insert_reports_indexcolumn' and type = 'P') drop procedure dbo.sp_DTA_insert_reports_indexcolumn if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_insert_reports_querytable' and type = 'P') drop procedure dbo.sp_DTA_insert_reports_querytable if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_insert_reports_querydatabase' and type = 'P') drop procedure dbo.sp_DTA_insert_reports_querydatabase if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_insert_reports_querycolumn' and type = 'P') drop procedure dbo.sp_DTA_insert_reports_querycolumn if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_insert_DTA_tuninglog' and type = 'P') drop procedure dbo.sp_DTA_insert_DTA_tuninglog if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_get_databasetableids' and type = 'P') drop procedure dbo.sp_DTA_get_databasetableids if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_get_pftableids' and type = 'P') drop procedure dbo.sp_DTA_get_pftableids if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_get_pstableids' and type = 'P') drop procedure dbo.sp_DTA_get_pstableids if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_get_tableids' and type = 'P') drop procedure dbo.sp_DTA_get_tableids if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_get_columntableids' and type = 'P') drop procedure dbo.sp_DTA_get_columntableids if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_get_indexableids' and type = 'P') drop procedure dbo.sp_DTA_get_indexableids if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_update_tuninglog_errorfrequency' and type = 'P') drop procedure dbo.sp_DTA_update_tuninglog_errorfrequency -- Drop unused SP's if they exist. if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_columnaccesshelper' and type = 'P') drop procedure dbo.sp_DTA_columnaccesshelper if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_databaseaccesshelper' and type = 'P') drop procedure dbo.sp_DTA_databaseaccesshelper if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_indexusagehelper' and type = 'P') drop procedure dbo.sp_DTA_indexusagehelper if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_queryCRhelper' and type = 'P') drop procedure dbo.sp_DTA_queryCRhelper if exists (select name from msdb.dbo.sysobjects where name = 'sp_DTA_tableaccesshelper' and type = 'P') drop procedure dbo.sp_DTA_tableaccesshelper |
3. 실행 후엔 어떤 변화가 있을까요?
가. 구문을 보면 아시겠지만, DTA 관련 테이블을 모두 DROP합니다. 하여 아무런 User Table도 남아있지 않게 됩니다.
나. MSDB 사이즈는 어떻게 될까요? 사용 가능한 공간이 924MB 중 892MB나 되네요.
다. 이제 Database Shrink(축소)를 해 보도록 합니다.
라. 짜잔… 다음과 같이 22.5MB로 사이즈가 줄게 됩니다.
DTA를 사용할 때 반드시 알아두어야 할 것 같네요.