purushottam2 (1/27/2013)
Production db had the 100 GB of log file. to remove log file i did following steps.- Deattached Database
- Renamed logfile
- Attached Database with-ought log file.
Above process creates new log file. But CPU utilization is 100 % now.
I have rebuild-ed all the indexes and updated stats of tables but still SQL is consuming 100%. I am sure SQL server is consuming 100%.
Any solution?
Sounds like you need to put in place some transaction log management, as you have now lost all recovery-ablity of what was in the transaction log, as the log chain is now broken.
What is the recovery model of the database? Simple? Bulk Logged? Full?
Do you need to be able to perform point in time restores?
Do you have a SLA with the business that you can only loose X minutes worth of data? What is your recovery point objective (RPO)?
Do you do transaction log backups?
http://www.sqlservercentral.com/stairway/73776/
http://www.sqlservercentral.com/articles/Administration/64582/
http://www.sqlservercentral.com/articles/books/76296/ - Chapter 8 - Transaction Log Management.