June 17, 2012 at 3:04 am
I have a job,Occurs every day at 5:00:00,
and my databases(total 5 database in the server) size is 150G.
but i found that the Duration time of the jobs is just 10S sometimes.
Is there some bug? thank you.
DECLARE @dbname VARCHAR(128)
DECLARE @cmd VARCHAR(max)
DECLARE dbCUR CURSOR FOR
SELECT name
FROM sys.databases
WHERE database_id>4
OPEN dbCUR
FETCH NEXT FROM dbCUR
INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @cmd =
'use ' + @dbname + ';' +
'exec sp_msforeachtable ''update statistics ?'' '
EXEC(@cmd)
FETCH NEXT FROM dbCUR
INTO @dbname
END
CLOSE dbCUR
DEALLOCATE dbCUR
June 18, 2012 at 6:56 am
Thomas Stringer (6/17/2012)
CELKO (6/17/2012)
Why use all this code to fake arrays or magnetic tapes? Just write five UPDATE STATISTICS commands and run the script.The OP wants to update stats on 5 databases.
Oh yeas, it is hard!
EXEC Database1.sys.sp_updatestats
EXEC Database2.sys.sp_updatestats
EXEC Database3.sys.sp_updatestats
EXEC Database4.sys.sp_updatestats
EXEC Database5.sys.sp_updatestats
June 18, 2012 at 10:17 am
Eugene Elutin (6/18/2012)
Thomas Stringer (6/17/2012)
CELKO (6/17/2012)
Why use all this code to fake arrays or magnetic tapes? Just write five UPDATE STATISTICS commands and run the script.The OP wants to update stats on 5 databases.
Oh yeas, it is hard!
EXEC Database1.sys.sp_updatestats
EXEC Database2.sys.sp_updatestats
EXEC Database3.sys.sp_updatestats
EXEC Database4.sys.sp_updatestats
EXEC Database5.sys.sp_updatestats
I agree, your suggestion is the correct way to do it, but the original suggestion was to run UPDATE STATISTICS and that won't satisfy the OP's desire to update stats on the whole database. sp_updatestats, like you show, is the way.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply