February 20, 2007 at 11:37 am
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/sjones/2877.asp
February 21, 2007 at 6:19 am
We will hopefully be migrating to SQL Server 2005 this summer. I noticed on the MS Product Life Cycle board that SQL Server 2005 has projected end of standard shelf life in 2011. SQL Server 2000 was out nearly 6 years before SQL 2005 was released. Do you have information regarding the projected release date of the next version of SQL Server?
I have noticed some of the other applications (specifically O/S) are now on a 2 year track between releases - will SQL Server follow that model?
February 21, 2007 at 7:08 am
Nice summary Steve. We're looking to put SP2 into place the first weekend in April (assuming we don't read too much bad press before then!)
Student of SQL and Golf, Master of Neither
February 21, 2007 at 7:16 am
I have heard SQL Server moving to a 2 year model, but I'd have expected to see a Beta by now if they were. Essentially we're 9 months from the 2 year mark.
I expect SQL Server 2008 and to see a Beta sometime later this year and possibly a CTP late.
February 21, 2007 at 8:35 am
Thank GOODNESS they have added the cleanup of old db backups back into the db backup routine. It was a minor pain to have this split up. I will be applying SP2 to a test server later this week and play around with it. I hope the db reports are a little easier as well. I have been holding off on opening the floodgates to migrate to SQL 2005 as I have seen a few oddball bugs in SP1 and even in the hotfix after SP1 so my warm and fuzzy was not so warm and fuzzy.
February 21, 2007 at 9:01 am
The "Maintenance Plan" in SP2 CTP is still having problems and not anywhere close to the way it used to work flawlessly in 2000. Here is one example..
The "Update Statistics Task" of Maintenance Plan fails with a strange error. I have a maintenance plan created on SQL Server 2005 SP2 CTP using windows xp account with administrative privileges. One of the steps.. "Update Statistics Task".. has been setup to update "All existing statistics" with scan type of "50% sampling" for "Tables and Views" is failing with the following error:
Executing the query "UPDATE STATISTICS [NWRptUsr].[NW_ALL_ACT_CHRGS_V]
WITH SAMPLE 50 PERCENT,NORECOMPUTE
" failed with the following error: "Cannot create or update statistics on view "NWRptUsr.NW_ALL_ACT_CHRGS_V" because both FULLSCAN and NORECOMPUTE options are required.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Here is the actual statement from "show T-SQL":
use [nwreport_20061212]
GO
UPDATE STATISTICS [NWRptUsr].[NW_ALL_ACT_CHRGS_V]
WITH SAMPLE 50 PERCENT,NORECOMPUTE
GO
Here are my questions:
- Since SQLServer is supposed to calculate statistics only for tables and materialized views, why is Maintenance Plan even attempting to run "UPDATE STATISTICS" for a regular view like.. NW_ALL_ACT_CHRGS_V?
- Why is Maintenance Plan trying to use "50 PERCENT" option for a regular view? Why is it not smart enough to figure out that this option is not applicable to a regular view?
- Does MS expect everyone to create two separate Maintenance Plans to Update Statistics.. one for tables and one for views.. if one is using sampling instead of fullscans for tables?
February 21, 2007 at 2:23 pm
Why are "New Features" being released with a "Service Pack"? Why can't MS just put all the bug/fixes into Service Packs and newer feature into Feature Packs? This way we can get the "Service Packs" faster (only have to test the bug fix).
Rudy
February 21, 2007 at 9:20 pm
Hello all,
For local development I have been relying on the "local administrators" being sysadmins in SQL by default (I know, bad practice ;-). This service pack removes this default role, so make sure that you have explicitly added your accounts to the sysadmin role prior to installing this service pack.
Regards,
Michael Lato
March 12, 2007 at 10:07 am
guys! watch out for the sp2! MS pushed out 1 sp and 1 critical update within a week. pls be sure your version is 9.0.3050 instead of 9.0.3040. the initial release sp2 can delete all your backup files before new ones were created (something to do with miscalculation of clean up interval)
May 9, 2011 at 1:26 pm
This is an old thread but still - for the reference of whoever comes accross it in search of a workaround:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=114332
Create an SP, schedule it with Agent and enjoy - works as a charm.
/*
Update Statistics in All User Databases
Works in SQL Server 2005 and above
BY Michael Valentine Jones
*/
declare @cmd nvarchar(max)
set @cmd = ''
-- Build command to update statistics
select @cmd = @cmd+
'
use '+quotename(a.name)+'
print ''*** Start Update Statistics for database ''+quotename(db_name())+
'' at ''+convert(varchar(30),getdate(),121)
exec sp_updatestats
print ''*** End Update Statistics for database ''+quotename(db_name())+
'' at ''+convert(varchar(30),getdate(),121)
'
from
(
select top 100 percent
aa.name
from
sys.sysdatabases aa
where
-- Exclude system database
-- Add more database names to exclude as needed.
name not in ('master','model','msdb','tempdb') and
-- Include only databases that are online
databasepropertyex(aa.name,'Status') = 'ONLINE' and
-- Include only databases that are updatable
databasepropertyex(aa.name,'Updateability') = 'READ_WRITE' and
-- Exclude databases in single user mode
databasepropertyex(aa.name,'UserAccess ') in ('RESTRICTED_USER','MULTI_USER')
order by
aa.name
) a
print '*** Start Update Statistics at '+convert(varchar(30),getdate(),121)
exec ( @cmd ) -- Execute Update Statistics commands
print '*** End Update Statistics at '+convert(varchar(30),getdate(),121)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply