Jobs - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Jobs

Author

Topic

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2005-10-26 : 02:30:29

Hi All,

What all jobs should a DBA create in the database to have good monitoring on the database.

I have created the following jobs.

1) Jobs to backup the database
2) Job to CHECKDB database and dump the output in a text file which is monitored regularly.
3) Job to check the database space and dump the output.
4) Job to defrag database (Using INDEXFRAG).

Please let me know if I can few more to my list.

Thanks in advance.

Regards
Sachin

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2005-10-26 : 19:59:15

Have a look at Tara's Stuff:
[url]http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx[/url]
Some really good stuff!
Andy

There's never enough time to type code right,
but always enough time for a hotfix...

Kristen
Test

22859 Posts

Posted - 2005-10-27 : 00:43:10

"3) Job to check the database space and dump the output"

What do you do in that one?

"4) Job to defrag database (Using INDEXFRAG)"

We REINDEX any tables which are small enough. We also only Defrag / Reindex when the table/index is fragmented. We also keep a log of what gets rebuilt, and how often, so we can "worry about it!"

I'm interested what other people do too. Here are some things off the top of my head:

5) Update stats - don't want that happening automatically at an unpredictable time - but leave the automatic one on in case the tables get "out of shape" before the next scheduled Update Stats.

6) Extend files - again, don't want that happening at an unpredictable time - but let it happen if it needs to!

7) We do DBCC UPDATEUSAGE after defrag/index rebuild. Dunno if its needed.

8) We purge MSDB - note that the standard sp_delete_backuphistory provided with SQL Server will take WEEKS (I mean that) to purge this data if you have never cleared it before, and you've been doing backups of 10 or more databases every hours (or more frequently) for a year or more. See here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=36201

9) Restore backups onto another server and DBCC CHECKDB them

10) Monitor stats. We store the following stats in SQL Server every minute, if the CPU load gets too high we deny new sessions in the application

@@cpu_busy
'SQLServer:Access Methods', 'Page Splits/sec'
'SQLServer:Buffer Manager', 'Buffer cache hit ratio'
'SQLServer:Databases', 'Transactions/sec', '_Total'
'SQLServer:General Statistics', 'User Connections'
'SQLServer:Locks', 'Number of Deadlocks/sec', '_Total'
'SQLServer:Memory Manager', 'Total Server Memory (KB)'
'SQLServer:Memory Manager', 'Target Server Memory(KB)'

Kristen

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2005-10-27 : 08:02:20

Thanks a lot Kristen/Andy

Below is my reply.

quote:
Originally posted by Kristen

"3) Job to check the database space and dump the output"

What do you do in that one?

set nocount on
go
select left(sd.name,15) as 'DatabaseName',sum(sa.size *8/1024) as 'TotalSize (MB)' from master..sysaltfiles sa
join master..sysdatabases sd on sd.dbid = sa.dbid
where sa.dbid>4 and sd.name='<DB_NAME>' group by sd.name order by sd.name
Go
declare @SQLstring as nvarchar(50)
set @SQLstring = N'DBCC SHOWFILESTATS'
create table #DBSpace(FielId tinyint, Filegroup tinyint, TotalSpace int, Used_Space int, name1 varchar(25),NameofFile Varchar(200) )
use <DB_NAME>
insert into #DBSpace execute sp_executesql @SQLstring
select Name1,Used_Space/16 as 'Total Used Space (MB)' from #DBSpace
Drop table #DBSpace

I dump the output of the job in an append moded in a text file, so that I can have a stats of database growth. (Don't know if there is another way of doing this. Please share if you have any).

I am also planing to push these data's in some table and will make a trigger, so that in case the DB run out of space, I will get an SMS by making an alert.

quote:

"4) Job to defrag database (Using INDEXFRAG)"

We REINDEX any tables which are small enough. We also only Defrag / Reindex when the table/index is fragmented. We also keep a log of what gets rebuilt, and how often, so we can "worry about it!"

I IndexFrag every day prior my backup jobs. And Reindex every week.

quote:

7) We do DBCC UPDATEUSAGE after defrag/index rebuild. Dunno if its needed.

8) We purge MSDB - note that the standard sp_delete_backuphistory provided with SQL Server will take WEEKS (I mean that) to purge this data if you have never cleared it before, and you've been doing backups of 10 or more databases every hours (or more frequently) for a year or more. See here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=36201

I never did these. Thanks for the suggestion.

quote:

10) Monitor stats. We store the following stats in SQL Server every minute, if the CPU load gets too high we deny new sessions in the application

@@cpu_busy
'SQLServer:Access Methods', 'Page Splits/sec'
'SQLServer:Buffer Manager', 'Buffer cache hit ratio'
'SQLServer:Databases', 'Transactions/sec', '_Total'
'SQLServer:General Statistics', 'User Connections'
'SQLServer:Locks', 'Number of Deadlocks/sec', '_Total'
'SQLServer:Memory Manager', 'Total Server Memory (KB)'
'SQLServer:Memory Manager', 'Target Server Memory(KB)'

Please tell me how to do this. If you doing using perfmon, is it recommended to store these data every minute?

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2005-10-27 : 08:25:20

From BOL:
System Monitor
Enables you to monitor server performance and activity using predefined objects and counters or user-defined counters to monitor events. System Monitor (Performance Monitor in Microsoft Windows NT® 4.0) collects counts rather than data about the events (for example, memory usage, number of active transactions, number of blocked locks, or CPU activity). You can set thresholds on specific counters to generate alerts that notify operators. System Monitor primarily tracks resource usage, such as the number of buffer manager page requests in use.
--> This won't lock a session, but if you've never done this sort of thing it is a good starting point. By monitoring stats like this you can quickly see WHAT needs more attention, and then formulate the best way to go about it. We got into a real problem with locks when an update to vendor software was screwed up; our lock monitor worked fine, and denied sessions all over the place. Problem was, the app was causing locks quicker than a user could log in! shutting down the lock monitor let the app run fine, and after the vendor fixed the code we put the lock monitor back in place. This would have been a good time to get an alert rather than just deny the session, so we added monitoring with a threshold just below the lock level. Now we are able to respond to an issue before it gets out of hand if need be.
Keep in mind that most of the DB jobs/maintenance plans in MSSQL are crud- Members here have posted some really elegant and well working snippets that will function better!!
Andy

There's never enough time to type code right,
but always enough time for a hotfix...

Kristen
Test

22859 Posts

Posted - 2005-10-27 : 14:15:51

"I dump the output of the job in an append moded in a text file"

Why not insert into a table - easier to analyse the trend etc. over time. We have an Admin database that we store this type of data in.

"And Reindex every week"

I think you might need a more cautious approach. Reindex will [I believe] create a new index, then drop the old one. That needs a lot of logging space, and will inherently move the index/data elsewhere on the disk. My opinion is that Defrag is fine in most cases (but a bit pointless for really small tables)

"If you doing using perfmon ..."

Not exactly, you can get them directly in SQL Server using:

FROM master..sysperfinfo (nolock)

And I thought of:

11) Recompile the Sprocs now & again - I favour A-D on Monday, E-G on Tuesday ... type thing

12) During downtime defrag the MDF / LDF files (we use the "single file defrag tool" from the Sysinternal people)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources