?
Solved

Script for auditing sqlserver

Posted on 2009-02-19
3
Medium Priority
?
314 Views
Last Modified: 2012-06-22
Hi all,

I search a tsql script for auditing :

-- List databases of which the RECOVERY model different from SIMPLE
-- to list the databases (others that the bases systems) whose option Auto Update Stats is with ONE
-- to list the databases which are not in mode MULTI_USER
-- to list the databases whose owner does not exist any more
-- To list the size of the datafiles > 12 Go (or more? or less?)
-- To list the maxsize of the unbounded datafiles or limit > 20 Go
-- to list the datafiles in autogrowth
-- DataFiles whose autoegrowth is 100 times smaller than the datafile
-- Filling of the datafiles + Transaction log > 90%
-- Datafiles which has a size higher than the maxsize
-- Datafiles which has identical file names (but in repertory different)
-- Summon transaction log > X% of the datafiles
-- OBJECTS
-- to check the presence of eligible indices for an update of the statistics (to express % on the whole of the indices of the base)
-- to check the presence of statistics whose sampling is lower than a certain threshold (50%? to express a percentage per quartile? by beach?)
-- to check the presence of eligible indices for a defragmentation/reindexation
-- to check the single presence of index or primary key on each table
-- to check the presence of an index covering each foreign key (and that the table presents a volumetry minimum (1000 pages?))
-- to check the presence of decontaminated indices
-- to check the presence of constraints decontaminated or WITH NOCHECK
-- to check the presence of empty tables (0 recordings)
-- to make a signal 10 of the bulkiest tables for each base
-- signal XX of the missing index
-- to check the presence of the DISTINCT operator in code SQL
-- to check the presence of the operator UNION (without Al) in code SQL?
-- to check the presence of hint (NOLOCK) in code SQL
-- SAFETY
-- to list the login having at least a privilege server role (sysadmin, securityadmin, serveradmin, dbcreator,&) by indicating their privileges
-- to list the Roles/users who have access to the tables systems
-- to list the accounts which have access to the bases systems
-- PERFORMANCE
-- to list the spots on standby (to use) (sys.dm_os_waiting_tasks)
-- Respect of the standards (name authority, name bases, name + way of the datafiles, etc&)
Thanks

Regards
Bibi
0
Comment
Question by:bibi92
  • 2
3 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 23683742
Hi,

What version of SQL? What I mean is that this will need to look at system tables or management views, which is version dependent.

Cheers
  David
0
 

Author Comment

by:bibi92
ID: 23690068
Hi,

2005.

Thanks

Regards

Bibi
0
 
LVL 35

Accepted Solution

by:
David Todd earned 750 total points
ID: 23694567
Hi,

much of what you requested above object I was able to findin sys.master_files
http://technet.microsoft.com/en-nz/library/ms186782(en-us,SQL.90).aspx

Also check out sys.database_files which I also used for several items as a double check. I'm more used to using the SQL 2000 version where I do have to go to each database to get the detail I'm after.

Some of the where clauses need a bit of customising for your needs.

I haven't completed your list, as I figure that at least half the benefit is actually building and finding these values yourself.

HTH
  David
-- sql 2005
use tempdb
go
 
print 'sys.databases'
select d.*
from master.sys.databases d
;
 
-- List databases of which the RECOVERY model different from SIMPLE
print 'Recovery not simple'
select d.*
from master.sys.databases d
where 
	d.recovery_model != 3
;
 
-- to list the databases (others that the bases systems) whose option Auto Update Stats is with ONE
print 'auto update stats is on'
select d.*
from master.sys.databases d
where 
	d.is_auto_update_stats_on = 1 -- on
	and d.name not in (
		'master'
		, 'model'
		, 'msdb'
		, 'tempdb'
	)
;
 
-- to list the databases which are not in mode MULTI_USER
print 'Access is NOT multi_user'
select d.*
from master.sys.databases d
where 
	d.user_access != 0
;
 
-- to list the databases whose owner does not exist any more
-- dgt can't delete login when it is an owner
 
-- To list the size of the datafiles > 12 Go (or more? or less?)
print 'large datafiles'
select f.size * 8192 / 1024 / 1024 / 1024 as size_gb, f.*
from master.sys.master_files f
where
	f.size * 8192 / 1024 / 1024 / 1024 > 12
	
execute sp_msForEachDB 'select f.size * 8192 / 1024 / 1024 / 1024 as size_gb, f.* from ?.sys.database_files f'
;
 
-- To list the maxsize of the unbounded datafiles or limit > 20 Go
print 'large datafile maxsize'
select f.max_size * 8192 / 1024 / 1024 / 1024 as max_size_gb, f.*
from master.sys.master_files f
where
	f.max_size = -1 -- until disk is full
	or f.max_size = 268435456 -- max file size of 2TB
	or f.max_size * 8192 / 1024 / 1024 / 1024 > 20
	
execute sp_msForEachDB 'select f.max_size * 8192 / 1024 / 1024 / 1024 as max_size_gb, f.* from ?.sys.database_files f'
;
 
-- to list the datafiles in autogrowth
print 'auto growing datafiles' 
select f.*
from master.sys.master_files f
where
	f.growth > 0
;	
 
-- DataFiles whose autoegrowth is 100 times smaller than the datafile
print 'small auto growing datafiles' 
select f.*
from master.sys.master_files f
where
	f.growth * 100 < f.size
	 -- percentage growth is whole number percent, so ignoring 1% growth as outside scope of question
	and f.is_percent_growth = 0
	and f.max_size != 0
;	
 
-- Filling of the datafiles + Transaction log > 90%
-- Datafiles which has a size higher than the maxsize
-- Datafiles which has identical file names (but in repertory different)-- Summon transaction log > X% of the datafiles
print 'similar datafile names'
select fo.*
from master.sys.master_files fo
where 
	right( fo.physical_name, charindex( '\', reverse( fo.physical_name )) - 1 ) in (
		select right( fi.physical_name, charindex( '\', reverse( fi.physical_name )) - 1 )
		from master.sys.master_files fi
		group by
			right( fi.physical_name, charindex( '\', reverse( fi.physical_name )) - 1 )
		having 
			count( * ) > 1
		)
;	

Open in new window

0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

599 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question