Think Web!

특정 칼럼 또는 테이블을 사용하는 프로시저 찾기

select so.name

from sys.syscomments sc

inner join sys.sysobjects so

on sc.id = so.id

where so.xtype = 'p'

and sc.text like '%칼럼명또는테이블명%'

group by so.name

SQL Connection String 중 눈여겨 볼만한 내용은 Failover Partner 라는 부분이다.

Database mirroring

If you connect with ADO.NET or the SQL Native Client to a database that is being mirrored, your application can take advantage of the drivers ability to automatically redirect connections when a database mirroring failover occurs. You must specify the initial principal server and database in the connection string and the failover partner server.

Data Source=myServerAddress;Failover Partner=myMirrorServerAddress;
Initial Catalog=myDataBase;Integrated Security=True;

SQL Server 2005 / 2008 / 2012 에 해당하며 자세한 내용은 출처 URL 을 참고하면 된다.

출처 : http://www.connectionstrings.com/sql-server-2012/

데이터베이스 별로 로그축소 쿼리를 자동으로 생성하는 쿼리

select 'use '+ name +'
go
alter database '+ name +' set recovery simple
go
dbcc shrinkfile('+ name +'_log)
go
alter database '+ name +' set recovery full
go'
from master.dbo.sysdatabases

생성된 실행 쿼리 (member database인 경우)

use member
go
alter database member set recovery simple 
go   
dbcc shrinkfile(member_log) 
go   
alter database member set recovery full
go 

2005 이상 버전에서만 적용

alter database [database name] set recovery simple
go

dbcc shrinkfile([database log name], 1)
go

alter database [database name] set recovery full
go

ex)
use tsdms_New;
alter database tsdms_new set recovery simple;
dbcc shrinkfile (tsdms_new_log, 1);
alter database tsdms_new set recovery full;

use vims;
alter database vims set recovery simple;
dbcc shrinkfile (vims_log, 1);
alter database vims set recovery full;