×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

thread962-1739608

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

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

(OP)
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?

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

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

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

(OP)
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.

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

(OP)
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?

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

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: What should I know before I post?

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

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close