log 다날리고....초기 600M만 쓰게 할수 있는 방법은 거의 없다고 보시는게 편합니다.

dump tran으로 로그를 날리셨다고 하셨는데

with truncate_only option을 주셨는지요?.......

옵션을 뭘 주셨는지요?

option은 with no_log도 해봤고, with truncate_only 둘다해봤는데

log는 줄어들지 않고, 그대로입니다.

참고로 DB option에는 trunc log on chkpt 옵션은 이미 설정해놓은 상태구요.. ㅠㅠ

혹시 rep server나 simple replication을 설정을 하셨는지요?

sp_helpdb DB-name한 결과와

sp_spaceused syslogs

dbcc tablealloc(syslogs) 한 결과 알려주세요...

1) sp_helpdb dbname의 결과(보안상 name과 owner를 test로 바꾸었습니다.)

 name                     db_size       owner                    dbid   created        status                                                                                                
 ------------------------ ------------- ------------------------ ------ -------------- ------------------------------------------------------------------------------------------------------
 test                          3500.0 MB test                        4 Nov 23, 2004   select into/bulkcopy/pllsort, trunc log on chkpt                                                      

(1 row affected)
 device_fragments               size          usage                created             free kbytes     
 ------------------------------ ------------- -------------------- ------------------- ----------------
 data_dev1                          2000.0 MB data only            Nov 23 2004 12:21PM          1322696
 log_dev1                            600.0 MB log only             Nov 23 2004 12:21PM not applicable  
 log_dev2                            300.0 MB log only             Dec 15 2005  8:50AM not applicable  
 log_dev3                            300.0 MB log only             Jan 16 2006  3:01PM not applicable  
 log_dev4                            300.0 MB log only             Jan 24 2006 11:40AM not applicable  
                                                               
 --------------------------------------------------------------
 log only free kbytes = 339976                                 
 device                       
         segment                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
 ------------------------------
         --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 data_dev1                    
         default                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
 data_dev1                    
         system                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
 log_dev1                     
         logsegment                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
 log_dev2                     
         logsegment                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
 log_dev3                     
         logsegment                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
 log_dev4                     
         logsegment                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
(return status = 0)

2)sp_spacedused syslogs 결과
 
name            total_pages     free_pages      used_pages      reserved_pages 
 --------------- --------------- --------------- --------------- ---------------
 syslogs         384000          84992           299006          2              

3)dbcc tablealloc(syslogs)의 결과

Table Corrupt: Page is allocated but not linked; check the following pages and ids: allocation pg#=810496 extent id=810696 logical pg#=810696  object id on extent=8 (object name = syslogs) indid on extent=0
Alloc page 810496 (# of extent=26 used pages=208 ref pages=200)
Total (# of extent=37338 used pages=298697 ref pages=298689) in this database
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) rol

아.. 깜빡한게 있는데요...Rep 서버는 사용하지 않습니다...^^

현상황으로 봐서는 또 약 300M 정도만 free가 된거 봐서는

계속 log가 안지워 지는 것 같은데요....

syslogs table이 이상이 있는듯합니다.

Table Corrupt: Page is allocated but not linked; check the following pages and ids

이거 실제 error message는 아니고요

rebuild log를 해보시는게 좋을듯 싶습니다.

하나더요

select * from master..syslogshold 한 결과 알려주세요....

그리고 그 db에서 checkpoint 해보세요

늦어서 죄송합니다.

dbid   reserved    spid   page        xactid         masterxactid   starttime                  name                                                                xloid      
 ------ ----------- ------ ----------- -------------- -------------- -------------------------- ------------------------------------------------------------------- -----------
      4           0     20     1233700 0x0012d3240013 0x000000000000        Jan 25 2006  9:37AM $user_transaction                                                            40

select * from master..syslogshold 다시 해보실래요

dbid   reserved    spid   page        xactid         masterxactid   starttime                  name                                                                xloid      
 ------ ----------- ------ ----------- -------------- -------------- -------------------------- ------------------------------------------------------------------- -----------
      4           0     20     1233700 0x0012d3240013 0x000000000000        Jan 25 2006  9:37AM $user_transaction      

또 이렇게 Spid가 20번인게 나오면 실제 이 20번이 작업을 계속 하고 있는 겁니다...