2014. 3. 3. 13:35

declare @RowCount int, @tablename varchar(100)


declare @Tables table (


                                    PK int IDENTITY(1,1),


                                    tablename varchar(100),


                                    processed bit


                                    )


INSERT into @Tables (tablename)


SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' and TABLE_NAME not like 'dt%' order by TABLE_NAME asc


 


declare @Space table (


                              name varchar(100), 


                              rows nvarchar(100), 


                              reserved varchar(100), 


                              data varchar(100), 


                              index_size varchar(100), 


                              unused varchar(100)


                              )


 


select top 1 @tablename = tablename from @Tables where processed is null


 


SET @RowCount = 1


 


WHILE (@RowCount <> 0)


      BEGIN


      insert into @Space exec sp_spaceused @tablename


      update @Tables set processed = 1 where tablename = @tablename


      select top 1 @tablename = tablename from @Tables where processed is null


      SET @RowCount = @@RowCount


END


 


update @Space set data = replace(data, ' KB', '')


update @Space set data = convert(int, data)/1000


update @Space set data = data + ' MB'


update @Space set reserved = replace(reserved, ' KB', '')


update @Space set reserved = convert(int, reserved)/1000


update @Space set reserved = reserved + ' MB'


 


select * from @Space order by convert(int, replace(data, ' MB', '')) desc

Posted by YKYun
2014. 3. 1. 17:07

*********  로그정리

backup log DATABASE_NAME with truncate_only

dbcc shrinkdatabase(DATABASE_NAME, truncateonly)

dbcc shrinkdatabase(DATABASE_NAME, 500) 


ALTER INDEX ALL ON 테이블명 REBUILD WITH (PAD_INDEX = ON, FILLFACTOR = 90)


//파일삭제

forfiles /P E:\DB_BACKUP\ /D -14  /M *.bak /C "cmd /C echo copy: @file && del @file"



*********  2008 로그삭제


USE TestDB;

GO


-- Truncate the log by changing the database recovery model to SIMPLE.

ALTER DATABASE TestDB

SET RECOVERY SIMPLE;

GO


-- Shrink the truncated log file to 1 MB.

DBCC SHRINKFILE (TestDB_Log, 1);

GO


-- Reset the database recovery model.

ALTER DATABASE TestDB

SET RECOVERY FULL;

GO





********* 2008이상 cmd명령어 허용


-- To allow advanced options to be changed.

EXEC sp_configure 'show advanced options', 1

GO

-- To update the currently configured value for advanced options.

RECONFIGURE

GO

-- To enable the feature.

EXEC sp_configure 'xp_cmdshell', 1

GO

-- To update the currently configured value for this feature.

RECONFIGURE

GO






**** 계속 복원중인 DB 재복원

RESTORE DATABASE F40_SAMOH WITH RECOVERY



'SQL' 카테고리의 다른 글

MS-SQL 유용한 명령어  (0) 2018.03.21
SQL Datetime범위  (0) 2014.03.27
Send_Mail  (0) 2012.06.21
서버에있는 image형식 파일 다운로드  (0) 2012.06.21
SQL Backup Schedule root : NAS  (0) 2012.05.22
Posted by YKYun
2014. 3. 1. 16:45

int 예제

Add zeroes before number

String.Format("{0:00000}", 15); // "00015"
String.Format("{0:00000}", -15); // "-00015"

Align number to the right or left

String.Format("{0,5}", 15); // " 15"
String.Format("{0,-5}", 15); // "15 "
String.Format("{0,5:000}", 15); // " 015"
String.Format("{0,-5:000}", 15); // "015 "

Different formatting for negative numbers and zero

String.Format("{0:#;minus #}", 15); // "15"
String.Format("{0:#;minus #}", -15); // "minus 15"
String.Format("{0:#;minus #;zero}", 0); // "zero"

Custom number formatting (e.g. phone number)

String.Format("{0:+### ### ### ###}", 447900123456); // "+447 900 123 456"
String.Format("{0:##-####-####}", 8958712551); // "89-5871-2551"

double 예제

Digits after decimal point

// just two decimal places
String.Format("{0:0.00}", 123.4567); // "123.46"
String.Format("{0:0.00}", 123.4); // "123.40"
String.Format("{0:0.00}", 123.0); // "123.00"
// max. two decimal places
String.Format("{0:0.##}", 123.4567); // "123.46"
String.Format("{0:0.##}", 123.4); // "123.4"
String.Format("{0:0.##}", 123.0); // "123"

Digits before decimal point

// at least two digits before decimal point
String.Format("{0:00.0}", 123.4567); // "123.5"
String.Format("{0:00.0}", 23.4567); // "23.5"
String.Format("{0:00.0}", 3.4567); // "03.5"
String.Format("{0:00.0}", -3.4567); // "-03.5"

Thousands separator

String.Format("{0:0,0.0}", 12345.67); // "12,345.7"
String.Format("{0:0,0}", 12345.67); // "12,346"

Zero

String.Format("{0:0.0}", 0.0); // "0.0"
String.Format("{0:0.#}", 0.0); // "0"
String.Format("{0:#.0}", 0.0); // ".0"
String.Format("{0:#.#}", 0.0); // ""

Align numbers with spaces

String.Format("{0,10:0.0}", 123.4567); // " 123.5"
String.Format("{0,-10:0.0}", 123.4567); // "123.5 "
String.Format("{0,10:0.0}", -123.4567); // " -123.5"
String.Format("{0,-10:0.0}", -123.4567); // "-123.5 "

Custom formatting for negative numbers and zero

String.Format("{0:0.00;minus 0.00;zero}", 123.4567); // "123.46"
String.Format("{0:0.00;minus 0.00;zero}", -123.4567); // "minus 123.46"
String.Format("{0:0.00;minus 0.00;zero}", 0.0); // "zero"

Some funny examples

String.Format("{0:my number is 0.0}", 12.3); // "my number is 12.3"
String.Format("{0:0aaa.bbb0}", 12.3); // "12aaa.bbb3"

DateTime 예제

Custom DateTime Formatting

// create date time 2008-03-09 16:05:07.123
DateTime dt = new DateTime(2008, 3, 9, 16, 5, 7, 123);

String.Format("{0:y yy yyy yyyy}", dt); // "8 08 008 2008" year
String.Format("{0:M MM MMM MMMM}", dt); // "3 03 Mar March" month
String.Format("{0:d dd ddd dddd}", dt); // "9 09 Sun Sunday" day
String.Format("{0:h hh H HH}", dt); // "4 04 16 16" hour 12/24
String.Format("{0:m mm}", dt); // "5 05" minute
String.Format("{0:s ss}", dt); // "7 07" second
String.Format("{0:f ff fff ffff}", dt); // "1 12 123 1230" sec.fraction
String.Format("{0:F FF FFF FFFF}", dt); // "1 12 123 123" without zeroes
String.Format("{0:t tt}", dt); // "P PM" A.M. or P.M.
String.Format("{0:z zz zzz}", dt); // "-6 -06 -06:00" time zone


// date separator in german culture is "." (so "/" changes to ".")
String.Format("{0:d/M/yyyy HH:mm:ss}", dt); // "9/3/2008 16:05:07" - english (en-US)
String.Format("{0:d/M/yyyy HH:mm:ss}", dt); // "9.3.2008 16:05:07" - german (de-DE)

// month/day numbers without/with leading zeroes
String.Format("{0:M/d/yyyy}", dt); // "3/9/2008"
String.Format("{0:MM/dd/yyyy}", dt); // "03/09/2008"

// day/month names
String.Format("{0:ddd, MMM d, yyyy}", dt); // "Sun, Mar 9, 2008"
String.Format("{0:dddd, MMMM d, yyyy}", dt); // "Sunday, March 9, 2008"

// two/four digit year
String.Format("{0:MM/dd/yy}", dt); // "03/09/08"
String.Format("{0:MM/dd/yyyy}", dt); // "03/09/2008"

Standard DateTime Formatting

String.Format("{0:t}", dt); // "4:05 PM" ShortTime
String.Format("{0:d}", dt); // "3/9/2008" ShortDate
String.Format("{0:T}", dt); // "4:05:07 PM" LongTime
String.Format("{0:D}", dt); // "Sunday, March 09, 2008" LongDate
String.Format("{0:f}", dt); // "Sunday, March 09, 2008 4:05 PM" LongDate+ShortTime
String.Format("{0:F}", dt); // "Sunday, March 09, 2008 4:05:07 PM" FullDateTime
String.Format("{0:g}", dt); // "3/9/2008 4:05 PM" ShortDate+ShortTime
String.Format("{0:G}", dt); // "3/9/2008 4:05:07 PM" ShortDate+LongTime
String.Format("{0:m}", dt); // "March 09" MonthDay
String.Format("{0:y}", dt); // "March, 2008" YearMonth
String.Format("{0:r}", dt); // "Sun, 09 Mar 2008 16:05:07 GMT" RFC1123
String.Format("{0:s}", dt); // "2008-03-09T16:05:07" SortableDateTime
String.Format("{0:u}", dt); // "2008-03-09 16:05:07Z" UniversalSortableDateTime


Posted by YKYun