if you tick "verify backup integrity" it performs this:

BACKUP DATABASE [admin] TO [adminFull] WITH NOFORMAT, NOINIT, NAME = N'admin_backup_20080312141909', SKIP, REWIND, NOUNLOAD, STATS = 10

GO

declare @backupSetId...

- I would advise to use a datetime variable in stead of a smalldatetime.

- probably the other sql2000 run on another servicepack as the one where it fails.

did you test the scripts we provided ?

They may be your starting point.

$sanjayattray (3/11/2008)

---

I think he also needs to execute this to get database back to multi user mode.

USE [master]

GO

ALTER DATABASE [xyz] SET MULTI_USER WITH ROLLBACK IMMEDIATE

GO

ALTER DATABASE [xyz] SET ...

Suppose your filegroup contains 3 files,

data is balanced over these 3 files.

If you empty one file, the data will be spread over the remaining

2 files within the same filegroup.

Keep in...

- another thing to keep in mind is that you need more authority to

be able to use truncate compared to delete.

Well, since this is sql2005 right ?

As from sp2 you can use login triggers.

So if your application logges into the server connecting to the appropriate database, you might update...

- I'm glad to see you've set the max server memory for this instance !

This is a must for large systems.

- did you configure the os best performance for programs...

btw you can exclude a table for autoupdate statistics by using

sp_autostats [ @tblname = ] 'table_name'

[ , [ @flagc = ] 'stats_flag' ]

...

- So does the proc reside in every individual db ?

If not, make sure you've installed it in master, called it sp_*** and marked it as systemobject (database transparency)

e.g. exec...

Yep, that's what occurs with delete.

A truncate is only...

Truncate is the fastest way ! (minimal logged)

Delete is a logged operation and will cause the most locking overhead as well as logging overhead (db-logfile)!

For the non catalog files you can use:

DBCC SHRINKFILE ( file_name , EMPTYFILE )

and later on

alter database xyz REMOVE FILE logical_file_name

Read BOL for more...

Oops, I missed the reconfigure statement.

That's why not all config options show up.

exec sp_configure 'show advanced options' , 1

reconfigure

exec sp_configure -- information (extended info activated please)

Can you post again...

well ... a function (datediff) can be avoided just by adding

one variable, calculating that and then have a straight comparisson

where clause.

This way chances of index usage will be...