CPU start consuming 100% in this case i run even a simple select command, it also take time..i have checked the query that are consuming most CPU time. I need...

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...

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 have checked the CPU usage and found all the CPU are usage by SQLSERVER. I ran the query to see the CPU utilization summary and found in 100% 95-98...

Yes, vehicle is main table. So you mean first i should select data from vehicle table based on all filters into a temp table with index.

Then for other filters should...

you mean instead of storing in single temp table, should store in multiple temp table?

Hi Grant,

Thanks for your reply,

Surely i will remove coalesce. Should i remove nolock, does it also affect my performance. Performance is our first priority.

Below is SP, it may be difficult to analyse due to text arrangement.

REATE procedure [dbo].[usp_SearchVehicles]

-- Add the parameters for the stored procedure here

@siteId int,

@pageIndex int,

@pageSize int,

@total int output,

@make varchar(50) =...

Thanks GilaMonster and Vedran.

I am supposing to set recover mode as SIMPLE and will check transaction log backup job is working or not.

Thanks to all 🙂

Thanks Ben, I will check all the stored procedures..

Ben,

Yes we have maintenance window, because have very less user.

HI Ben.

Thanks for your promptness,

Only 20 users are using my application, and there is no frequently use of database interaction. And this log file increased up to 96 GB in...

Hi Ben,

data file size is 3 GB and Log file size is 96 GB, Database we are using from last 1 month. I want to know what is the reason...

I have checked size of .mdf and .ldf file.

.mdf - 3 GB

.ldf - 96 GB

I have checked what all the traces is ON and find only System Trace is ON.

But...

I have checked size of .mdf and .ldf file.

.mdf - 3 GB

.ldf - 96 GB

I have checked what all the traces is ON and find only System Trace is ON.

But...