MS SQL SERVER 2000 LOG檔太大
教你一個土方法
1. 先將 Transaction Log
先卸除資料庫
如果你的資料庫建置在預設目錄的話
可以在下列路徑刪除檔案
C:\Program Files\Microsoft SQL Server\MSSQL\Data\資料庫名稱.ldf
然後重新附加資料庫即可
最後再去限制交易記錄檔的成長空間
BACKUP LOG <db name> WITH NO_LOG
2. 利用 sp_helpdb 找出 DB 的 log file 的 logical name
一般預設的名稱是 <db name>_log
3. 將 Logfile 檔案變成指定大小的 size
DBCC SHRINKFILE ( <log file name>, <file size> )
ex. DBCC SHRINKFILE(ABC_log,3)
這樣就可以不須要停掉 DB 伺服器
至 於要自動覆蓋記錄檔？這個 SQL Server 2000 並不支援，不過您可以利用 BACKUP LOG <db name> WITH NO_LOG 的指令，定期清除記錄檔，或是是將復原模式改成 simply 或是 Bulk-Logged 如果交易記錄檔對您的復原並不重要
參考自 http://forum.tpc.edu.tw/ShowPost.aspx?PostID=3172
2008-01-11 新增 dellog.sql
今天在清除 HSDB 的 Log 檔時,想說每次都要手動修改很不方便,於是就想說要修改的資料庫只有 HSDB,CPA,PSM 寫一個 sql 檔,放在 mssql 的維護計劃內,讓它每個星期天清理一次就好了,於是就寫了一個 dellog.sql 的程序,禮拜一去看看,如果執行成功就太完美了,以後就不用擔心 LOG 檔太大的問題
迴響
MS SQL SERVER 2000 LOG檔太大 | 豬窩
Good information. Lucky me I ran across your blog by chance (stumbleupon).
I've bookmarked it for later!
MS SQL SERVER 2000 LOG檔太大 | 豬窩
Wonderful post! We will be linking to this particularly great content on our website.
Keep up the good writing.
MS SQL SERVER 2000 LOG檔太大 | 豬窩
I have read so many articles about the blogger lovers except this post is
actually a good piece of writing, keep it up.
MS SQL SERVER 2000 LOG檔太大 | 豬窩
Hi there! I could have sworn I've visited this blog before
but after going through many of the posts I realized it's new to me.
Anyways, I'm certainly delighted I stumbled upon it and I'll
be book-marking it and checking back regularly!
MS SQL SERVER 2000 LOG檔太大 | 豬窩
Wow that was strange. I just wrote an extremely long comment but after I clicked submit
my comment didn't appear. Grrrr... well I'm not writing all that over again. Regardless, just wanted to say wonderful blog!