DexCore.Net

Lock 걸린 SPID 검출 (Mode 부분이 X표시된 것이 Lock)exec sp_lock Lock 걸린 SPID를 입력하면 현재 돌고 있는 쿼리 확인 가능dbcc inputbuffer([spid]) Lock 유발하는 SPID 강제 종료 kill [spid] Lock 의심 SPID 추출 쿼리 select *from master..sysprocesses p where (status like 'run%' or waittime > 0 or blocked 0 or open_tran 0 or exists(select * from master..sysprocesses p1 where p.spid = p1.blocked and p1.spid p1.blocked))

SELECTA.TABLE_NAME,C.VALUE AS TABLE_COMMENT,A.COLUMN_NAME, A.DATA_TYPE,ISNULL(CAST(A.CHARACTER_MAXIMUM_LENGTH AS VARCHAR) ,CAST(A.NUMERIC_PRECISION AS VARCHAR) + ',' + CAST(A.NUMERIC_SCALE AS VARCHAR)) AS COLUMN_LENGTH,A.COLUMN_DEFAULT,CASE WHEN A.IS_NULLABLE = 'YES' THEN '' ELSE 'Not Null' END,'PK'= CASE WHEN OBJECTPROPERTY(OBJECT_ID(D.CONSTRAINT_NAME), 'ISPRIMARYKEY') = 1 THEN 'Y' ELSE '' END,..

DBCC FREEPROCCACHE select cp.objtype,st.text,cp.usecountsfrom sys.dm_exec_cached_plans cpcross apply sys.dm_exec_sql_text(cp.plan_handle)storder by text

CREATE PROCEDURE SP_HELPTEXT2( @objname NVARCHAR(MAX))ASBEGIN--DECLARE @objname nvarchar(MAX) = N'sp_Test_sp_helptext'; DECLARE @ObjectText nvarchar(MAX)='';DECLARE @SyscomText nvarchar(MAX);DECLARE @LineLen INT;DECLARE @LineEnd BIT = 0;DECLARE @CommentText TABLE( LineId int IDENTITY(1,1) , Text nvarchar(MAX) collate catalog_default ); DECLARE #ms_cur_syscom CURSOR LOCAL FOR SELECT text FROM sys..

MS SQL LocalDB 사용 방법 ※ SQL LocalDB 설치 제약사항운영체제- Windows Vista SP2, Windows Server 2008 SP2 이상의 운영체제에서 지원합니다.- Windows XP, Windows Server 2003, 2003 R2는 지원하지 않습니다. 닷넷프레임워크- .NET Framework 4.0.2부터 지원을 합니다.- .NET Framework 버전 및 종속성: https://msdn.microsoft.com/ko-kr/library/bb822049(v=vs.110).aspx 1. 설치- 운영체제 시스템(x64 or x86)에 맞는 버전으로 SqlLocalDB.MSI와 sqlncli.msi를 순서대로 설치합니다. SQL LocalDB 설치- 아래 다운로드 경..

테이블,컬럼 개체 설명 보기 및 등록 1. fn_listextendedproperty - 데이터베이스 개체의 확장 속성 값 반환 1) 테이블 개체 설명(주석)view plaincopy to clipboardprint?SELECT objtype, objname, name, value FROM ::fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', default, default, default); 2) 테이블 컬럼 개체 설명(주석)view plaincopy to clipboardprint?SELECT objtype, objname, name, value FROM ::fn_listextendedproperty (NULL, 'schema', 'dbo', 'tab..

SELECTNAME AS TABLE_NAME , (SELECT VALUE FROM SYS.EXTENDED_PROPERTIES WHERE MAJOR_ID = A.ID AND MINOR_ID = 0 ) TABLE_COMMENT FROM SYSOBJECTS A WHERE RTRIM(A.XTYPE) = 'U' ORDER BY NAME

Log 파일 사이즈 줄이기 USE VK_CRM select name,recovery_model_desc from sys.databases ALTER DATABASE VK_HR_BAK SET RECOVERY simple DBCC SHRINKFILE (VK_HR_BAK_log , 1) ALTER DATABASE DuckILERP SET RECOVERY FULL

CREATE TABLE dbo.SYS_PROC_LOG([SERIAL_NO] [int] IDENTITY(1,1) NOT NULL,[OBJECT_NM] [varchar](100) NULL,[OBJECT_TYPE] [varchar](20) NULL,[SQLCMD] [text] NULL,[UPDATER] [varchar](20) NULL,[UPDATEDATE] [datetime] NULL, CONSTRAINT XPKSPLOG PRIMARY KEY NONCLUSTERED (SERIAL_NO ASC)) GO CREATE TRIGGER [TRG_SYS_PROC_LOG] ON DATABASEFORCREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,CREATE_VIEW, ALTER_..

다운로드 : http://www.zeusedit.com/agent/ssms/ms_ssms.html Using Microsoft SQL Server Management Studio with SubversionThe Agent SVN plug-in allows you to seamlessly integrate SSMS with Subversion.Features of the plug-in include:Easy to install, easy to configure, easy to useAdd solutions, projects and files to Subversion, all from within SSMSCheck out, check in, get latest version all without leavi..