Simple recovery model and auto_shrink in SQL Server 2005

Last Post 03 Jul 2009 03:52 AM by river1. 2 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
river1
New Member
New Member

--
02 Jul 2009 02:17 AM
Hi masters,

I'm upgrading to SQL Server 2005.
I have some doughts about SQL Server 2005 behavior when compared with SQL Server 2000.


First question is:

Are there any differences between the behavior of a SQL Server 2000 database using simple recovery model and a SQL Server 2000 converted to 2005 (trought restore and changing the compatibility level) using the simple recovery model? in other words, when i restore a database (Sql server 2000) in SQL server 2005 , and change compatibility level to 90, i leave the recovery model in simple mode.

Will my database have a different behavior ? or is the same? including to my apps.


What about the auto_shrink? are there any diferences?


Note - I know that simple recovery model is not a good practice for production databases because i can not do point in time restores and much other questions. I know to that auto_shrink causes much fragmentation , even more bad if i have the auto_grow option activated too.

But in this case i just what to know if there are any diferences to me or to my applications.

that you very much,

Pedro
river1
New Member
New Member

--
02 Jul 2009 06:19 AM
ok master gunneyk,
Thank you very mutch for the answer.

Other question that i have is related to the procedure that i will do to upgrade my databases from SQL server 2000 to SQL Server 2005.

I have two diferente databases , suppose one is called db1 and the other is called db2.

I have db1 and db2 on several diferente places.

db1 is placed in at least 30 differente places (physical places) and db2 the same.

We don't have any DBAs there to do the upgrade, so i need to do like this:


1) i ask for a person of my company in each place , to backup the SQL Server 2000 database.

2) with the backup made in the earlier step, restore it on a SQL Server 2005 engine.

3) run a script that i will send to do the rest of the work (convert to 2005).


This script will change the compatibility level to 90 and make many other things like see if the auto update statistics are ON, and if not, it puts to ON.

But i have a dought about indexs.

Should the script recreate the index of the entire databases? what about the statistics?

thank you master Gunneyk,

Pedro






river1
New Member
New Member

--
03 Jul 2009 03:52 AM
Hi master Gunneyk,

Thank you very much for reply.
I'm upgrading to 2005 because the policy of my company (we work for the government) says that we can only upgrade to a new engine when it as the SP3 released, because of bogs that can can appear.

so, we will upgrade to 2008 wen SP3 for this engine is released, until then, it's 2005.


Sorry to put so much questions about Stats and Indexs, but i still have some doughts about it.


1) i was talking to a guy about this upgrade and he told me that if i make this command:

UPDATE STATISTICS table_name WITH FULLSCAN

That my table would be updated with the correct statistics.
i'm thinking in using this command to do the update to statistics.

But i have to execute one comand for each table and index in the database. This will take me much time to build the script.


Is there a way of, in a single command, update the entire statistics to the new values that we want?



2) Other question that i have is this:

I understud from your post, that if i make the rebuild of an index, then i don't need to update it's statistics, because the rebuild process will do also the update to the statistics of that index. is this correct?

3) If the anwser is yes, i still have other question:

what are the objects in a database that have statistics and therefour have to see them updated?
It's only the indexes and the tables?


4) If the objects that i have to update statitcs are tables and indexs , other question that i have is this:

If i rebuild the indexs , i will no need to update statistics for this objects, but i still have to make update the all the tables (it's statistis) correct?

thank you master,

Pedro
















Acceptable Use Policy
---