May 15, 2011 at 11:36 pm
Digs (5/15/2011)
name ][ , [ @new_cmptlevel = ] version ]
--to change to level 80
dbo.sp_dbcmptlevel @dbname=N'test', @new_cmptlevel=80
--to change to level 90
dbo.sp_dbcmptlevel @dbname=N'test', @new_cmptlevel=90
--or
sp_dbcmptlevel 'test', '80'
sp_dbcmptlevel 'test', '90'
sp_dbcmptlevel is deprecated, is included only for backward compatibility, will be removed in a future version and should no longer be used.
Use ALTER DATABASE SET COMPATIBILITY_LEVEL.
DBCC UpdateUsage should only be necessary after upgrading from SQL 2000. There were bugs in the page tracking code in that version that should all have been fixed in SQL 2005.
Updating all stats is recommended after a database version change (eg a restore from 2005 to 2008), not a compatibility level change.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 16, 2011 at 1:40 am
I did the change compatibility code, update stats
All seams fine.
I went from 90 to 100 (ie 2005 to 2008 R2)
Do I need to worry about re building indexes etc, joins etc
May 16, 2011 at 2:01 am
Rebuilding indexes, no you can wait for the regular maintenance jobs.
What do you mean by 'joins'?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 16, 2011 at 2:06 am
What was the command you used to update stats?
I would recommend to use Update statistics.
May 16, 2011 at 12:17 pm
.."Rebuilding indexes, no you can wait for the regular maintenance jobs."...
ME: Like what sort of code does that??
.." recommend to use Update statistics."..
ME: yes I did, thanks
May 16, 2011 at 2:04 pm
Digs (5/16/2011)
.."Rebuilding indexes, no you can wait for the regular maintenance jobs."...ME: Like what sort of code does that??
.." recommend to use Update statistics."..
ME: yes I did, thanks
Digs, Regular Maintenance can be setup in a number of ways. The easiest and a lot of people may not like the suggestion but it does give you the simplest and way to begin. You can create Maintenance plans from inside of SSMS. Connect to your Instance | Expand Management | Right Click on Maintenance Plans and New Maintenance Plan. You can create simple of complex plans.
I would suggest something along the lines of a System Maintenance Plan, A User Maintenance Plan. These would have a couple of sub plans so that you can schedule your subplans based on your requirements.
IE System Backups - performs database backups of your systems DBs, Update statistics Task and has a Maintenance Cleanup task to remove backups older than x days/hours etc
The same for your user DB's except you could include a rebuild index task or a reorg index task.
These maintenance plans create SSIS packages and when you save them they are saved internally and create your associated SQL Agent Jobs.
If you are not really into the gui side of things you can setup your own Maintenance plans using powershell to do the same things and have your powershell jobs scheduled through what ever means you have available. Creating your maintenance this way also gives you more control and flexability with moving between environments.
Hopefully this has help.
MCT
MCITP Database Admin 2008
MCITP Database Admin 2008
MCITP Database Dev 2008
www.jnrit.com.au/Blog.aspx
May 16, 2011 at 2:41 pm
Thanks, I have a better idea, hire some one to help me, when and if my DB gets that large..:-)
May 16, 2011 at 2:54 pm
Digs (5/16/2011)
Thanks, I have a better idea, hire some one to help me, when and if my DB gets that large..:-)
You know where to find us me ! 🙂
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply