Sql Server - Find the Size of Database File and Find the Size of Log File

use below script to find the size of the DB

SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = '<<your database name>>'
GO

fix: sql server 2008 r2 cpu usage 100%

CPU usage can be caused by a lot of reasons like:

• Bad execution plans caused by bad statistics
• Locks and blocks
• DBCC CHECKDB
• Index rebuild
• etc

I have faced cpu usage touch 90-100%  around 10 hour.  follow action taken but not able to fix it.

1. select * from sys.sysprocesses where blocked>=1 (kill unwanted spid) 

2. restarted sql services

3. Sp_who2 'active'

Referred following articles : 

http://www.sqlservercentral.com/Forums/Topic1411982-391-1.aspx

http://raaviblog.com/how-to-investigate-100-cpu-usage-problem-in-sql-server-2008/


 "after 8 hour search found link and offline dba support to arrive the lack of index. in maintenance activity sp_updatestats missing."

====================================================================

Query execution causing CPU spike:

Query execution  takes long times and spikes CPU commonly because of in-correct carnality estimates caused by outdated statistics, Lack of Index, Server configuration, Distributed queries, etc.

When the server is experiencing this problem run the query in below link to list all the queries which are executing in the server order by CPU time desc along with plan.

{

Get SQL Text and Query Plan for statements which are executing now

}

 It could be one query which is driving the majority CPU time or Multiple queries each driving the CPU. Look at the CPU time of the above query output.

If it is single query/Store procedure which is driving the majority of CPU.

1.        Update the stats of tables and indexes used by the query (If the stats are up to date Estimated rows and estimated execution will  be approximately

same in execution plan .If there is huge difference stats are out dated and requires update

=======.=====================================================

Thanks god found link and read first step and stick-out run the 

Solution: "sp_updatestats"

1) did you update ALL statistics with a FULL SCAN?

2) I would simply use profiler to capture rpc batch completed and tsql batch completed events and find the big hitters. Tune these. I would also consider running a trace to disk and using Qure from DBSophic to do aggregate trace analysis. The thing(s) you need to tune most are not necessarily the ones that run the longest or use the most resources individually...

3) it is possible that someone altered the schema in any way - i.e. dropped some indexes?

4) I also see this routinely at clients these days: did you perchance upgrade to a much better IO subsystem? Getting data into the CPUs faster can cause them to actually start earning their keep.