We will be moving to SAN in couple of weeks but till then we cant ignore the job,it has to run.

I have tried its working fine.

Yes it is being used in the where clause so what can be used instead of this fuction which will take less time

So can any1 help from where we can study for certification are there any notes for that or we need to buy any books for the exam

70-432 and 70-450

I got it there is job which runs at 1:45 which truncates the log and so the log backup starts failing and it resumes once differential is completed at 3:00...

Please Refer below link :

http://www.sql-server-performance.com/2007/clustering-basics-2005/

Check this query:

selectdf.name AS LogicalFileName

, isnull(fg.name, 'Log') AS FilegroupName

, df.physical_name AS PhysicalOSName

, (df.size * 8 / 1024) AS SizeMBs

, (fileproperty(df.name, 'SpaceUsed') / 128) AS SpaceUsedMBs

, (df.size * 8 / 1024)...

It can be because ur log file is set to restricted growth or drive on which log file is kept is full.

This query might help

select

a1.object_id

,a2.name

, a1.used_page_count * 8 as total_used_pages

, cast(a1.used_page_count * 8 /1000 as varchar(10)) +...

1st run a log backup then run Dbcc Shrinkfile(2),

After that again run log backup then run Dbcc shrinkfile(2) again this will shrink log file.There might be active transaction which needs...

As suggested by Gail When the Server Restarted there must be some Transaction that might be in rollback state.It might take lot of time to recover and also consume lot...

Refer below link which will make u understand what happens when u use NOLOCK option

http://beyondrelational.com/modules/2/blogs/28/posts/10465/sql-server-transaction-isolation-level-read-uncommitted.aspx

refer below link

http://blog.sqlauthority.com/2009/11/09/sql-server-removing-key-lookup-seek-predicate-predicate-an-interesting-observation-related-to-datatypes/

You need to check which all maintanence job are running during evening and night time by which u can conclude why log size is growing.