September 2, 2009 at 2:08 am
any one is having automated daily checklist?
kindly upload here sample daily monitoring sheet if any one is having.
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
September 2, 2009 at 2:57 am
sanketahir1985 (9/2/2009)
any one is having automated daily checklist?kindly upload here sample daily monitoring sheet if any one is having.
not quite sure what you are after, you mention a automated checklist, then you want a sheet. You can find daily checks scripts on this site. You dont mention which versions of sql server you are managing either.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 2, 2009 at 3:20 am
i m having sql2000 & sql2005 both servers
now i want to make daily monitoring sheet which should be automated.
i want a sample sheets if u have it.so that i can get idea.
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
September 2, 2009 at 4:40 am
sanketahir1985 (9/2/2009)
i m having sql2000 & sql2005 both serversnow i want to make daily monitoring sheet which should be automated.
i want a sample sheets if u have it.so that i can get idea.
So basically you'd like us to do your job for you and cant even be bothered to do some research?
I'm with Silver, here. Firstly you have given us nothing to go on and any daily checks (other then the blindingly obvious) are dependant on your system and environment.
Have a think and do some research and when you have some specific questions, then ask.
Adam Zacks-------------------------------------------Be Nice, Or Leave
September 2, 2009 at 5:19 am
I dont have a problem with helping you. But daily checklists apart from the usual server checks and checking for failed jobs, failed logins and performance issues, are very different depending on the environment and a lot of scripts used to assist in this are tailored specifically by the dba. or monitoring is provided by third party tools.
I am old fashioned and check every server manually, even though, we have server side monitoring, either provided by tivoli or MOM, and reporting services reports created to give us failed jobs, failed logins, database sizes and low disk space.
Have a look at the articles and scripts on this site for daily checklists and monitoring.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 3, 2009 at 4:25 pm
I was once new to DBA work and I remember what that was like, so I'll give you some places to start.
First, your job is to create a condition of certainty about your server(s), your database(s), and your data. You do this by removing uncertainty.
This process involves looking at things to make certain that there are no problems. Where to look? I'll give you some good starting points:
1. Right click on My Computer, select Manage.
2. Under Storage, select Disk Defragmenter. Check the fragmentation on all of your disk drives.
3. Under Storage, select Disk Management. Check the free space on all of your disk drives.
4. Under System Tools, select Event Viewer. Check each of its 4 sub-categories for errors or warnings. It is a good idea to scrub your servers logs clean by any solving problems in the logs. Your goal should be to have clean, error and warning free, logs.
5. Using SQL Server Management Studio, view the history of all maintenance jobs.
6. Using SQL Server Management Studio, view the history of all SQL Agent jobs.
7. Using SQL Server Management Studio, under SQL Server Agent, open the latest Error Log file and examine it for errors and warnings.
8. Make sure all of your database backups are where they are supposed to be every day.
9. Make sure your server(s) obtain and install the most current, critical Microsoft Updates.
10. Using Excel, inventory all of your server maintenance and SQL Agent jobs and record their start times and approximate execution times. Keep this inventory current to avoid having jobs running at the same time.
11. Develop a database Backup and Restore strategy that works for your employer. The important issues are:
a. How much data can your employer afford to lose?
b. How long can your employer afford to be down?
c. Do you need point-in-time restore capability?
d. Select a database restoration model that works best for your employer. Explain the tradeoffs to your management and your choices.
12. Examine your server's hardware logs. Look for warnings. They are often precursors to hardware failures.
13. Once a week, using SQL Server Management Studio, select Databases, then right click on a production database, select Reports, Standard Reports, Disk Usage. This report will tell you how much space your selected database and log files have allocated and how much space they are actually using. This information is important because it gives you a chance to manually grow your database and log files, rather than have them auto-grow, which is not desireable.
This is my program. I perform audits on my employer's 2 database servers every day. I compile the results into a Word document and save it with the date in its title. Then, I create an email with an executive summary of the exceptions I've noted, attach the Word document and send it directly to the CEO and the VP of Technology. They appreciate it and sleep better than they used to, before I came on board.
I've got some notifications automated but frankly, a human being has to check the automation to make sure it works, so you might as well look at things yourself. By doing so, you will familiarize yourself with the server(s), the database management system, and the database(s) you're responsible for. And, by looking at things every day, you'll notice subtle changes that are early warnings that something is changing, like a backup job that suddenly goes from 30 minutes to 3 hours. That sort of information is hard to create automated notifications for but something which a human being will easily notice.
My daily server audit process takes about an hour a day. It's cheap insurance for creating certainty. I've used this program to put out database and server "fires" in several companies. My management has always appreciated it.
Ultimately, what I've recommended is a starting point. You have to customize your program to your employer's environment.
Good luck,
LC
September 3, 2009 at 11:09 pm
Thanx for the reply guys
but i want the format of daily monitoring sheet.
whatever we do in the morning it should be documented
i want sample of that document.
i use to keep it in excel sheet in chart format (attached here).
kindly upload such sheets if possible.
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
September 4, 2009 at 1:56 am
nice explanation crainlee2, very thorough. however I think that you would spend most of your day if not run out of time if you had a medium to large sql server estate. other things to mention is that depending on the company, some servers are locked down, so you have limited access on servers, so you cannot always look at the event logs and certanity not anything storage related. some companies i have worked for have 600+ servers, even from a production point of view, 50+ servers with averaging 30+ databases on every box.
I am not an advocate of spreadsheets, so fancy diagrams showing database sizes and growth analysis mean very little, especially when you have potentially hundreds of servers and thousands of databases to manage. it is not practical to have that information in spreadsheets unless you only have a few servers and you have the time.
personally i would do everything using reporting services if you can, and if not create a simple web page and write queries to report on the information that you need.
regarding this dailycheck list, if you are working as a DBA, you should have this already. how else are you doing your job.
post what you are doing every day and then we can help you with best practises. There is no standard daily monitoring check list, it is different for each DBA.
I have looked at your spreadsheet, and we have asp pages that do exactly the same thing. most daily checklists are word documents, and are just tasks that a dba does every day. I have yet to see in my years of doing this. an automated daily checklist. there are documents that list what a dba should do every morning, and there are scripts that can do checks. but correct me if i am wrong you are asking for a formatted automated dailychecklist, which would be a combination of the two. spreadsheets are not workable if you have hundreds of servers or hundreds of databases.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 4, 2009 at 5:04 am
sanketahir1985 (9/3/2009)
Thanx for the reply guysbut i want the format of daily monitoring sheet.
whatever we do in the morning it should be documented
i want sample of that document.
i use to keep it in excel sheet in chart format (attached here).
kindly upload such sheets if possible.
Hi,
I think you actually need to design your own spreadsheet tailored to your own environment.
There are a number of elements of daily checks which are generic (backups, event logs, sql logs, disk space to name but a few) and would apply to every DBA, but each enviroment is different.
September 4, 2009 at 7:08 am
Hi
Check this link: http://www.mssqltips.com/tip.asp?tip=1240
Hope this will be helpful.
Regards
Mohan Kumar VS
September 6, 2009 at 6:26 am
I agree with Silverfox - a list provided by crainlee2 is not bad - but only on small environments. All of these tasks should be automated throud powershell/vb/tsql scripts. And only send a alert if something goes wrong.
2nd) crainlee2 was talking about reading error logs - yeah it is quite interesting but i prefer to setup some alerts on errors with severity higher then 16-20 (depends on environment/apps).
my checklist (not daily, just a checklist)
- error logs/event logs
- disks (spaces/fragmentation)
- checkdb
- index fragmentation/statistics maintaining
- database file mgmt
- backups (databases/sql/server)
- other stuff such as (SSIS, SSAS, SSRS, Replication, Logshipping, Clustering - depends what you are using)
- tempdb monitoring
- performance monitoring
- security monitoring (depends on env)
September 6, 2009 at 7:38 am
Marvin,
What do you monitor on the tempdb?
What problems might there be and what indicators would alert you?
LC
September 6, 2009 at 7:52 am
Last year I had some speakings about tempdb - unfortunetly my pptx are in polish - so maybe you can use this whitepaper to get more information: http://technet.microsoft.com/en-us/library/cc966545.aspx <- it is REALLY good and i strongly recomended to all sql persons (developers and dbas)
Other source of information might be http://blogs.msdn.com/sqlserverstorageengine/ <- Sunil Agarwal wrote a lot of posts about tempdb.
September 7, 2009 at 2:00 am
here are the automated checks I have started writing for SQL2005, they are not complete yet (some of the scripts have been taken off the net and modified).
First thing to do is create a folder for the checks e.g C:\DAILYCHECKS
Inside there create a batch file e.g dailychecks.bat
within there paste in:
@echo off
echo *********************************
echo ** PERFORMING SQL SERVER CHECKS**
echo *********************************
FOR /F "usebackq eol=# tokens=1" %%g IN (c:\DAILYCHECKS\SERVERLIST.TXT)DO "c:\DAILYCHECKS\DATABASECHECKS.BAT" %%g
PAUSE
.......................................................
This calls another batch file I have called databasechecks.bat which runs against all instances listed in serverlist.txt
create a file called serverlist.txt and put in all of your instances in there
create this batch file in the same location (databasechecks.bat):
@echo off
set fullinstance=%1
echo running for %1
SQLCMD -E -S %1 -i \\testserver\dailychecks\test.sql >>\\testserver\dailychecks\results.out
.......................................................
The last part is the sql script
create a file called test.sql and put in the following:
print''
print ' =================================================================='
SET NOCOUNT ON
select 'daily check for ==> ' + convert(char(27),serverproperty('servername'))
PRINT '-============DATABASES ON INSTANCE================-'
if object_id('#databasestatus','U')
IS NOT NULL
DROP TABLE #databasestatus
go
CREATE TABLE #databasestatus
(DATABASENAME VARCHAR(50),
DatabaseStatus varchar(30),
DatabaseRecoveryModel varchar(50))
GO
INSERT #databasestatus
SELECT NAME, RECOVERY_MODEL_DESC, STATE_DESC
FROM SYS.DATABASES
GO
--SELECT * FROM #databasestatus
--ORDER BY DATABASENAME
--GO
SELECT substring(DATABASENAME, 1,20) as [DATABASENAME],
substring(DatabaseStatus, 1,20) as [DatabaseStatus],
substring(DatabaseRecoveryModel, 1,20) as [DatabaseRecoveryModel]
FROM #databasestatus
DROP TABLE #databasestatus
GO
PRINT '-============JOBS ON INSTANCE================-'
if object_id('#INSTANCEJOBS','U')
IS NOT NULL
DROP TABLE #INSTANCEJOBS
CREATE TABLE #INSTANCEJOBS
(NAME NVARCHAR(512),
ENABLED TINYINT,
DATE_CREATED DATETIME,
DATE_MODIFIED DATETIME)
GO
INSERT #INSTANCEJOBS
SELECT NAME, ENABLED, DATE_CREATED, DATE_MODIFIED
FROM MSDB.dbo.sysjobs
SELECT substring(NAME, 1, 45) as [NAME],
ENABLED,
DATE_CREATED,
DATE_MODIFIED
FROM #INSTANCEJOBS
DROP TABLE #INSTANCEJOBS
GO
PRINT '-============BACKUP STATUS================-'
if object_id('#backupstatus','U')
IS NOT NULL
DROP TABLE #backupstatus
go
CREATE TABLE #backupstatus
(DatabaseID varchar(30),
backupsize varchar(30),
time_taken varchar(30),
startdate datetime,
type varchar (15),
recovery_model varchar(15),
days_since_backup int)
INSERT #backupstatus
SELECT s.database_name,
--m.physical_device_name,
cast(s.backup_size/1000000 as varchar(14))+' '+'MB' as bkSize,
CAST (DATEDIFF(second,s.backup_start_date , s.backup_finish_date)AS VARCHAR(4))+' '+'Seconds' TimeTaken,
s.backup_start_date,
CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END as BackupType,
--s.server_name,
s.recovery_model,
datediff(day, s.backup_start_date, GETDATE()+ 1)
AS Days_since_backup
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m
ON s.media_set_id = m.media_set_id
--WHERE s.database_name = 'BackupReport'
ORDER BY database_name, backup_start_date, backup_finish_date
GO
SELECT SUBSTRING(DatabaseID, 1,15) as [DBNAME],
SUBSTRING(backupsize, 1,15) as [backupsize],
SUBSTRING(time_taken, 1,10) as [time_taken],
startdate,
SUBSTRING(type, 1,10) as [type],
SUBSTRING(recovery_model, 1,12) as [recovery_model],
days_since_backup
FROM #backupstatus
ORDER BY DBNAME
GO
drop table #backupstatus
PRINT '-============DATABASE SIZES================-'
SET QUOTED_IDENTIFIER OFF
SET NOCOUNT ON
DECLARE @dbname varchar(50)
declare @string varchar(250)
set @string = ''
create table #datafilestats
( Fileid tinyint,
FileGroup1 tinyint,
TotalExtents1 dec (8, 2),
UsedExtents1 dec (8, 2),
[Name] varchar(50),
[FileName] sysname )
create table #dbstats
( dbname varchar(50),
FileGroupId tinyint,
FileGroupName varchar(25),
TotalSizeinMB dec (8, 2),
UsedSizeinMB dec (8, 2),
FreeSizeinMB dec (8, 2))
DECLARE dbnames_cursor CURSOR FOR SELECT name FROM master..sysdatabases
OPEN dbnames_cursor
FETCH NEXT FROM dbnames_cursor INTO @dbname
WHILE (@@fetch_status = 0)
BEGIN
set @string = 'use ' + @dbname + ' DBCC SHOWFILESTATS'
insert into #datafilestats exec (@string)
insert into #dbstats (dbname, FileGroupId, TotalSizeinMB, UsedSizeinMB)
select @dbname, FileGroup1, sum(TotalExtents1)*65536.0/1048576.0,
sum(UsedExtents1)*65536.0/1048576.0
from #datafilestats group by FileGroup1
set @string = 'use ' + @dbname + ' update #dbstats set FileGroupName =
sysfilegroups.groupname from #dbstats, sysfilegroups where
#dbstats.FileGroupId = sysfilegroups.groupid and #dbstats.dbname =''' +
@dbname + ''''
exec (@string)
update #dbstats set FreeSizeinMB = TotalSizeinMB - UsedSizeinMB where
dbname = @dbname
truncate table #datafilestats
FETCH NEXT FROM dbnames_cursor INTO @dbname
END
CLOSE dbnames_cursor
DEALLOCATE dbnames_cursor
------------------------
--Script to calculate information about the Log Files
set nocount on
create table #LogUsageInfo
( db_name varchar(50),
log_size dec (8, 2),
log_used_percent dec (8, 2),
status dec (7, 1) )
insert #LogUsageInfo exec ('dbcc sqlperf(logspace) with no_infomsgs')
select
substring(d.dbname, 1, 15) as [db NAME], d.totalsizeinmb, d.usedsizeinmb, d.freesizeinmb,l.log_size,l.log_used_percent
from #dbstats d join #LogUsageInfo l on d.dbname = l.[db_name]
drop table #datafilestats
drop table #dbstats
drop table #LogUsageInfo
September 7, 2009 at 2:12 am
The only thing that I can add to this, is that each company is different and the resources and access that you have are different.
Powershell has to be installed and normally requires admin access to run.
VB also requires knowledge and are you always going to be the one who maintains it.
I have seen a lot of monitoring over the years from t-sql scripts to custom vb apps, as well as the usage of reporting services and web pages. all custom monitoring requires maintenance and updates. bear this in mind what ever solution you put in place.
The use of reporting services in my opinion is the cleanest and easiest solution to implement in a medium to large server estate.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply