db maintenance

What daily maintenance should I be doing on the SQL server?
Regular or daily?
I generally do:
Frequent transaction log backups. Somewhere between every hour and every 10 minutes.
Daily database backup.
Daily integrity check of database (DBCC CHECKDB)
Regular defragmentation
(http://www.microsoft.com/technet/pro.../ss2kidbp.mspx)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ngan" <ngan@.discussions.microsoft.com> wrote in message
news:D7C67C88-4250-4BD2-98EE-C1BE09FA2EFC@.microsoft.com...
> What daily maintenance should I be doing on the SQL server?
|||Both.
Since I moved my access tables to SQL, I haven't had to deal with compacting
and such. But I just wondered if there is much maintenance with SQL. Oh
yea, the other reason I asked was that my boss is doing my job description
and wanted to know my daily SQL tasks, excluding the normal updates/changes
to the FE.
I do the DBCC CHECKDB on a weekly basis (or whenever I remember...I should
make a scheduled job for that..).
As for the transaction logs, it gets backed up every nite with the nitely
backup jobs.
Thanks.
"Tibor Karaszi" wrote:

> Regular or daily?
> I generally do:
> Frequent transaction log backups. Somewhere between every hour and every 10 minutes.
> Daily database backup.
> Daily integrity check of database (DBCC CHECKDB)
> Regular defragmentation
> (http://www.microsoft.com/technet/pro.../ss2kidbp.mspx)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "ngan" <ngan@.discussions.microsoft.com> wrote in message
> news:D7C67C88-4250-4BD2-98EE-C1BE09FA2EFC@.microsoft.com...
>
|||All of the things I mention are things that you schedule. If you want to make it easy for yourself,
use the Maintenance Wizard for it (if it suits you needs). And make sure you do more frequent
CHECKDB (insurance...). I prefer to do checkdb daily. If I get a problem, I backup the tail of the
log, restore the most recent db backup and all subsequent log backups - no data loss...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ngan" <ngan@.discussions.microsoft.com> wrote in message
news:B1CBD6D5-31C2-4086-8E57-872300239027@.microsoft.com...[vbcol=seagreen]
> Both.
> Since I moved my access tables to SQL, I haven't had to deal with compacting
> and such. But I just wondered if there is much maintenance with SQL. Oh
> yea, the other reason I asked was that my boss is doing my job description
> and wanted to know my daily SQL tasks, excluding the normal updates/changes
> to the FE.
> I do the DBCC CHECKDB on a weekly basis (or whenever I remember...I should
> make a scheduled job for that..).
> As for the transaction logs, it gets backed up every nite with the nitely
> backup jobs.
> Thanks.
> "Tibor Karaszi" wrote:
|||Thanks for your input.
One question: the integrity checks...does it require everyone out of the
dbs? If so, I can't schedule it because we have a call center that works
24/7 and is always in one or two of the dbs.
I did set up the two jobs that my boss has to run manually (he gets here
before office hours). One job is the optimization (reorganize data and index
pages and remove unused db files). The other job is the check the db
integrity and include indexes. I just have to remind my boss to run the job
then.
"Tibor Karaszi" wrote:

> All of the things I mention are things that you schedule. If you want to make it easy for yourself,
> use the Maintenance Wizard for it (if it suits you needs). And make sure you do more frequent
> CHECKDB (insurance...). I prefer to do checkdb daily. If I get a problem, I backup the tail of the
> log, restore the most recent db backup and all subsequent log backups - no data loss...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "ngan" <ngan@.discussions.microsoft.com> wrote in message
> news:B1CBD6D5-31C2-4086-8E57-872300239027@.microsoft.com...
>
|||> One question: the integrity checks...does it require everyone out of the
> dbs?
Here's a quote from Books Online, DBCC CHECKDB:
"DBCC CHECKDB does not acquire table locks by default. Instead, it acquires schema locks that
prevent meta data changes but allow changes to the data. The schema locks acquired will prevent the
user from getting an exclusive table lock required to build a clustered index, drop any index, or
truncate the table. "

> I just have to remind my boss to run the job
> then.
Schedule the jobs. I've never encountered an installation where you rely on somebody to manually
start the jobs...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ngan" <ngan@.discussions.microsoft.com> wrote in message
news:AFCF4C2D-F96E-4A23-B67E-58B6DFB9E6F1@.microsoft.com...[vbcol=seagreen]
> Thanks for your input.
> One question: the integrity checks...does it require everyone out of the
> dbs? If so, I can't schedule it because we have a call center that works
> 24/7 and is always in one or two of the dbs.
> I did set up the two jobs that my boss has to run manually (he gets here
> before office hours). One job is the optimization (reorganize data and index
> pages and remove unused db files). The other job is the check the db
> integrity and include indexes. I just have to remind my boss to run the job
> then.
>
> "Tibor Karaszi" wrote: