Thanks for your posts.Only stored procedures are used by the application.

My question here is :Are all the 4 processors being used by sql server ?Can you explain me about parallelism...

SQL Server uses the log file itself for the rebuild operations unless you specify the tempdb option.If you notice the logfile while the rebuild is in process...u would have seen...

scope_identity() function returns the last inserted identity value across any table within a stored procedure(within scope).

select @value=scope_identity() ---will return the last inserted identity value within that scope.It may be identity...

There is a function fn_split().Make use of it.

fn_split(@Orderid)

u can pass multiple values to order id now.

I too faced this issue.But we had only one database in that server.So I gave sysadmin to that login instead of mapping a user to that database.It worked for our...

use DBCC SHOWCONTIG(tablename) WITH FAST .

This will not lock the table while execution and also gives you results quicker(in seconds).

Then check logical scan fragmentation .It should be less than 1%...

When sql server 2005 is installed ,two folders 80 and 90 will be created in your installation path.They are for backward compatability with sql server 2000.Thats it.It does'nt mean that...

Use ident_current(table_name) instead of @@identity.

@@identity gives the last inserted identity value across the server.

Is this secondary server in a log-shipping setup??

check the server settings for max. concurrent connections.

Also if you have 8+ gb of RAM,then enable AWE .

Check the disk space once...whether it has enough space or not?

Create a job and select the job category as T-SQL.

Write your alter table statement and schedule the job at your off-peak time.schedule it to occur only once.

USE Master

GO

ALTER DATABASE databasename

SET RECOVERY SIMPLE

GO

Yes...truncate frees up the logfile.But shrink command releases that free space to OS.

At the end of your query keep SET ROWCOUNT 0.

Set rowcount is within the scope.so others will not be affected with this.

I suggest you to use TOP instead of rowcount.rowcount...