How to make maintenance plan in SQL server
-
I need somebody to explain how we do the maintenance in SQL server.
Is it normal to run every night CHECKDB/CHECKTABLE? And why?
Our new DBA runs DBCC CHECKDB/CHECKTABLE with auto-repair option checked and this is the reason he puts the SQL server in a single user mode from 10 pm to 5 am every day.
This is a huge problem for all of the developers and applications.
Is this really necessary?
Please, let somebody give an idea how the optimization and maintenance should run in SQL Server 2000.
Thanks a lot,
MJ
-
I saw your post "Availability of SQL server when backup runs".
I have my maint plans do them daily, but do not check them to run before backups. They will fail on any DB they can't get into single-user mode. But I have 40+ DB's of varying sizes split over two servers (I can't wait till I can cluster).
I generally breakup my Maint Plans into system DBs, User DBs (tran log BU), and IS DBs (simple recovery and SW control DBs).
The 3 days on disk to allow for weekends and to give a window to grab EOM copies for baselining/report databases. We do run Backup Express and write the files to tape. But the tapes are off-site within 24-36 hours of their creation.
But even with a maint plan it shouldn't take a 10-5 window for the checkdb to run. Your new DBA sounds like he's a newbie or he's 404.
I would say to try and get them accomplished at least once a week, just for grins...but even then you could probably go through and set it up to do one db or a group of db's per night in a published sequence, and a much smaller window.
Tell the newbie to check the logs and RTFM!
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
Do not use the auto-repair option for the reason you've seen (single user mode issue). If a database throws an inconsistency error with a DBCC check, you'll probably want to handle it manually anyway. These errors should be fairly rare.
K. Brian Kelley
@kbriankelley -
Hi, Backuping frequency (as well as other maintenance like space allocations etc.) depends on the update and growth rate of the DB.
Basicly Full backup once or twice or more times a week, then transaction log backups at least every night and a differential backup at least once between a full backup cycle. The backup ratio of different type backups is something like that in my system in various db's and numerous servers and it works just fine.
A backup is held on disk approx. for two cycles and then recorded on a cassette by Veritas Net backup system.
Db integrity check is made before backups but not autorepairs etc., you don't want to backup a broken db or restore it so info of db status must be available every day.
If db is corrupted then repair actions are made manually. As bkelley says corrupt data is more likely to be the case than corrupt data structure.
Don't know if this helps at all, posted it anyway.
-j-
-
Micro Soft advices that you should run DBCC Commands in a single user mode in SQL Server 7.0 and below because in SQL Server 7.0,DBCC CHECKDB statement get a shared lock on the database and the objects. Performance of user and DBCC transaction would decreased significantly in this circumstances.
With the introduction of SQL 2000, Micro Soft says you can run the DBCC statement without turning your DB into single user mode. This can be done because in 2000 the DBCC commands holds a schema level lock instead of object level lock.
You can have the new DBA to run DBCC CHECKDB statement with auto-repair option selected WITHOUT changing the database into single user mode. When choosing the auto-repair option and the DBCC finds any problem, it will seized exclusive access to the objects and fixed the problem. At this time SQL Server will automatically turned the object or the database into single user mode when performing a repair. SQL Server returns the DB into normal mode when the job is done.
-
Microsoft acknowledges the auto-repair option requiring single user mode in SQL Server 2000 as a bug. It affects all versions.
The title of the article refers to system databases. However, the note in the article indicates the behavior is true on user databases as well if users are connected.
K. Brian Kelley
@kbriankelley -
Thanks a lot, guys, for all of your input.
The problems my co-workers have with their new DBA are almost solved due to this posting and they have their DB on line again.
I guess that the biggest issue was that we do not run DBCC Commands every night for all system and user DBs. And not every time before backup we run DBCC Commands.
Simple - and most important we could not have 6 hours down time per day.
There are some other issues like differential daily nightly backups on a db with size of 1.5G – why – the backup time for full back up is not more then 10 minutes. What about your recovery time if there’s crash? Once for the last full backup, once for the differential, then for the transaction log/s… It increases the time… But this is personal choice, I guess…
Thanks a lot for putting the things to make sense again.
MJ
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply