| Author |
Topic |
|
The_Hoff
Starting Member
6 Posts |
 Posted - 2009-04-14 : 11:36:31
|
| I have just became the new DBA and am now in the process of making a maintenance schedule (hasn’t been done in 2 years apparently). I have only one question at this time (though many will follow). I am under the impression that you have to detach the DB in order to perform the rebuild. Though I do know there is the online feature now but I do not know if that will encompass everything. So pretty much I am asking, Must I detach the DB in order to run a full rebuild and will an online rebuild cover all the bases?-Phil |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2009-04-14 : 11:55:36
|
| No, you do not need to detach the DB for the rebuild, offline just means that the index will not be available while the command is being run. Please read BOL before doing this, under ALTER INDEX |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
The_Hoff
Starting Member
6 Posts |
Posted - 2009-04-14 : 13:31:23
|
| we are using sql enterprise 2005-Phil |
|
|
|
The_Hoff
Starting Member
6 Posts |
Posted - 2009-04-14 : 13:39:08
|
| i had no i idea that you could not rebuild on a detached database, as i said i have been "thrown" into this large tast of being not only a DBA but also DRA. i have a maintenance schedule made, and from what i have read seems to be a little much, it follows like this:Backs every day (Diff and Trans)and Full w/ a Trans on SundaysMon: Update StatTues: ReorganizeWed: Update Stat w/ DB Integrity CheckThurs: ReorganizeFri: Update StatSat: Update StatSun: Online Rebuild w/ DB Integrity CheckAlso we plan on performing an Offline Rebuild QuarterlyAlso with periodicly check if we must shrink DB which will probably turn into a quarterly thing-Phil |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
The_Hoff
Starting Member
6 Posts |
Posted - 2009-04-15 : 05:07:50
|
| with your script, you have graciously provided, when it rebuilds the database and if it has to take it offline what kind of effect will we see when it comes to users? we are currently running VMs from sql along with Solarwinds, now taking those offline could bring down not only multiple servers but also all monitoring abilities we have of or extensive network, or am i looking at this wrong?-Phil |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
The_Hoff
Starting Member
6 Posts |
Posted - 2009-04-15 : 16:00:53
|
| i get that offline does not mean detach, i understand that it will still be there, i just want to know if an offline rebuild will affect a server that is built off the database. we use virtuak machines and they must have sql inorder to operate no sql no server.-Phil |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-04-15 : 16:22:52
|
Here is what OFFLINE means in regards to ALTER INDEX:quote:
Table locks are applied for the duration of the index operation. An offline index operation that creates, rebuilds, or drops a clustered index, or rebuilds or drops a nonclustered index, acquires a Schema modification (Sch-M) lock on the table. This prevents all user access to the underlying table for the duration of the operation.
The above quote means that queries will have to wait until the locks are removed before the query can complete. Depending upon the timeout value in the application and how long it remains offline will determine what affect it has on the application that is connecting to the database.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
The_Hoff
Starting Member
6 Posts |
Posted - 2009-04-16 : 05:53:03
|
| Originally i became angry with the response i received, but after thinking about my posts, i realized i was not direct with explaining what i knew and what my issues were. i do know that scripts are the best way, but for now we are just formulating something basic to start out with (until we can expand our SQL knowledge). I was given a 3 day deadline to come up some sort of beginning maintenance, and this was the fastest way. I will be back with more questions (hopefully more direct and intelligent questions) once i start digging deeper into sql. thank you for your time tkizer and your brief presence RickD.-Phil |
|
|
|
|
Could use some help with Maintenance