블로그 이미지
LifeisSimple

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

Notice

'script'에 해당되는 글 2

  1. 2012.04.03 [MSSQL] Database 파일 정보 조회 Script
  2. 2011.03.22 [MSSQL] MSSQL Sub 디렉토리 생성
2012. 4. 3. 22:48 Brain Trainning/DataBase


Database 파일들의 정보를 조회하는 스크립트 입니다. 

대충 대충 필요하겠다 싶은것을 정리했습니다. 

/*

           Database file 별상태점검

                     - by Koon

*/

create proc dbo.DBA_GetDBFilesInfo

as

 

set nocount on

 

if exists (select top 1 * from sys.tables where name = 'DBA_DBFileInfo')

           truncate table DBA_DBFileInfo

else

           create table DBA_DBFileInfo (db_name nvarchar(100), db_id int, file_id int, type_desc nvarchar(60), data_space_id int, name sysname, physical_name nvarchar(260),  state_desc nvarchar(60), size int, max_size int, growth int, is_percent_growth bit)

 

insert into  DBA_DBFileInfo (db_name , db_id, file_id, type_desc, data_space_id, name, physical_name,  state_desc, size, max_size, growth, is_percent_growth)

exec sp_msforeachdb 'use ?; select db_name(), db_id(), file_id, type_desc, data_space_id, name, physical_name,  state_desc, size, max_size, growth, is_percent_growth from sys.database_files'

 

select a.db_id, a.db_name, a.file_id, a.name, a.physical_name, state_desc, convert(varchar(20), (a.size * 8 / 1024)) + 'MB' as file_size, -- (BytesOnDisk / 1024 / 1024)  as used_size,

           max_size, (case when is_percent_growth = 1 then convert(varchar(3), a.growth) + '%' else convert(varchar(20), (a.growth * 8) / 1024) + 'MB' end) as file_growth,

           convert(decimal(13, 2), (NumberReads * 1.00 / (NumberReads + NumberWrites * 1.00)) * 100) as ReadsPercent,

           convert(decimal(13, 2), (NumberWrites * 1.00 / (NumberReads + NumberWrites * 1.00)) * 100) as WritesPercent,

           NumberReads, (BytesRead / 1024 / 1024) as MBytesRead, (IoStallReadMS / 1000) as IoStallReadSec,

           NumberWrites, (BytesWritten / 1024 / 1024) as MBytesWritten, (IoStallWriteMS / 1000) as IoStallWriteSec

from DBA_DBFileInfo a

           cross apply sys.fn_virtualfilestats(a.db_id, a.file_id)

order by a.db_id, a.file_id

posted by LifeisSimple
2011. 3. 22. 10:07 Brain Trainning/DataBase
MSSQL  Sub 디렉토리 생성

EXECUTE master.dbo.xp_create_subdir N'Y:\BackupFolder\TestDB'

GO

 

BACKUP DATABASE [aspsearchDB] TO  DISK = N'Z:\BackupFolder\TestDB\TestDB_backup_2011_03_22_100247_1759220.bak' WITH NOFORMAT, NOINIT,  NAME = N'TestDB_backup_20##_##_##', SKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 10

GO

 

이렇게 하면 됩니다. 머리가 나쁘면 계속 뭔가를 기록하게 된다는... 
 



posted by LifeisSimple
prev 1 next