Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Deattach/Attach actions made CPU utilization 100 % Expand / Collapse
Author
Message
Posted Sunday, January 27, 2013 11:31 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 29, 2013 1:59 AM
Points: 53, Visits: 101
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?
Post #1412197
Posted Monday, January 28, 2013 12:39 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, March 14, 2014 2:19 AM
Points: 2,820, Visits: 3,916
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?
http://sqlblog.com/blogs/tibor_karaszi/archive/2009/03/09/do-you-have-instant-file-initialization.aspx


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1412212
Posted Monday, January 28, 2013 1:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, August 28, 2013 2:37 AM
Points: 5,075, Visits: 4,834
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.




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1412222
Posted Monday, January 28, 2013 1:25 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 29, 2013 1:59 AM
Points: 53, Visits: 101
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?
Post #1412224
Posted Monday, January 28, 2013 1:31 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 29, 2013 1:59 AM
Points: 53, Visits: 101
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.
Post #1412227
Posted Monday, January 28, 2013 1:35 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, August 28, 2013 2:37 AM
Points: 5,075, Visits: 4,834
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.




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1412230
Posted Monday, January 28, 2013 2:01 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:52 AM
Points: 41,530, Visits: 34,446
You need to manage that log better
Please read through this - Managing Transaction Logs

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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1412251
Posted Monday, January 28, 2013 2:25 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, February 21, 2014 4:34 PM
Points: 369, Visits: 1,197
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).


_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths
Post #1412266
Posted Monday, January 28, 2013 2:29 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 9:27 PM
Points: 1,242, Visits: 1,097
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
Post #1412269
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse