January 27, 2013 at 11:31 pm
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?
January 28, 2013 at 12:39 am
purushottam2 (1/27/2013)
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%.
Have you done Instant File Initialization?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 28, 2013 at 1:23 am
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.
January 28, 2013 at 1:25 am
As mentioned in post, Instant File Initialization applies only to database files, and does not apply to log files.
And i have renamed the log file. Do you really think i need Instant File Initialization?
January 28, 2013 at 1:31 am
Initially Recovery model was bulk. And it lead to grow the database file up to 100 GB. I have changed it to Simple mode and deattch\attach database to delete log file.
Do you need to be able to perform point in time restores?:- NO
Do you do transaction log backups?:- NO
We take backup on daily basics.
January 28, 2013 at 1:35 am
You didnt need to detach the DB to do what you wanted.
A simple change of the recovery model to SIMPLE then CHECKPOINT the DB then shrink the file would of been enough.
As you have now detached and attached a single file DB, you wont be able to do this.
Get a copy of the Accidental DBA guid in my signature and read through the High CPU chapter.
January 28, 2013 at 2:01 am
You need to manage that log better
Please read through this - Managing Transaction Logs[/url]
Do not ever delete a database's transaction log. SQL cannot always recreate the log, only if the database was cleanly shut down prior. Deleting the log can result in the database not attaching and a restore from backup being needed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 28, 2013 at 2:25 am
Your steps to shrink the log file are extremely dangerous with potential risk of losing the db completely. No one should do that.
You could diff backup db, set it to simple recovery, shrink the log file, set db back to full recovery, diff backup db.
Instant file initialization works only on data files, not on log files.
See which windows process is taking CPU. See what SQL sessions are doing (sp_whoisactive or sp_who2).
January 28, 2013 at 2:29 am
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?
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.
-- Babu
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply