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檔太大 | 豬窩
Thankfulness to my father who informed me on the subject of this web site, this website is truly remarkable.
MS SQL SERVER 2000 LOG檔太大 | 豬窩
That is really attention-grabbing, You are a very skilled
blogger. I've joined your feed and look forward to looking for more of your fantastic post.
Additionally, I've shared your site in my social networks
MS SQL SERVER 2000 LOG檔太大 | 豬窩
Yesterday, while I was at work, my sister stole my apple ipad and tested to see if it can survive a 25 foot drop,
just so she can be a youtube sensation. My apple ipad is now destroyed and
she has 83 views. I know this is entirely off topic but I had to share it
with someone!
MS SQL SERVER 2000 LOG檔太大 | 豬窩
What's Going down i am new to this, I stumbled upon this I have found It positively useful
and it has aided me out loads. I hope to contribute &
assist other customers like its helped me. Good job.
MS SQL SERVER 2000 LOG檔太大 | 豬窩
I needed to be grateful for this great read!! I definitely enjoyed every little it.
I've got you book-marked to think about new things you post