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%.
It seems you are managing the log very poorly. Please check the log file auto growth. Number of VLFS you have using (DBCC loginfo). if you have more VLFS meaning, your server is busy with your log file.
Gail's Link will be very useful. Trust me read it thoroughly couple of times to understand the basics.