Maintenance on SQL 2012 - Do you have to take databases offline for mainenance 1

[Luvsql]

I have never taken our production databases offline to do weekly maintenance as it occurs when no one is using the applciations that access the databases and all the maintenance runs without issues. I'm now being questioned why they are not all taken offline to perform the maintenance. I run the DBCCs, Rebuild Indexes, Update Statistics and all run without error (ie there are always successful and not any instance where it can't be done due to any connections), so do they NEED to be taken offline? What benefits will be gained by taking them offline?

 

[gmmastros]

Just for fun, create a new database, add a table and an index. Then take the database offline. See how far you get with your maintenance.

My point is... you cannot do any of that stuff while the database is offline.

There can be advantages with rebuilding indexes when the database is put in to single_user mode (and your process is the user that is connected to the database). However, if you are rebuilding indexes while nobody is connected anyway it should not matter. I say this because under normal circumstances, rebuilding an index will put a lock on the table for the duration of the rebuild. If it takes a long time to rebuild the index, this could lead to a blocking issue. Enterprise edition of SQL Server allows for ONLINE rebuild.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Luvsql]

That's what I thought. To rebuild the indexes on 11 databases takes under an hour (it's very fast). Our databases are also all under 15Gb so we have no issues.

 

[Luvsql]

The databases that I do weekly maintenance on are those databases with Full recovery mode, which have their logs backed up hourly. When maintenance is run (DBCC checkdb, reindexing, update stats etc) the log files grow to almost the same size of the database. As a workaround I change the recovery method to Simple first, then run the maintenance, then set it back to Full as I have a full backup done before the maintenance.

We now have issues during restores because of this (know SQL 2012 error about the LSN chain). I can fix that by doing a restore by file no problem.

What I'm wondering though is how to do maintenance without logging (since we have a full backup). Is that even possible?

 

[SQLBill]

You can follow your current plan with one addition. You must take a full backup after you put the database back into Full Recovery mode.
You have broken the backup chain by placing the database into simple mode and the chain starts anew when you put the database back into Full recovery mode.

-SQLBill



The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[Luvsql]

Good point. I'll add another step to backup the databases again IF the maintenance succeeds. Thanks.