We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Script for auditing sqlserver

bibi92
bibi92 asked
on
Medium Priority
324 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
Comment
Watch Question

David ToddSenior Database Administrator
CERTIFIED EXPERT

Commented:
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

Author

Commented:
Hi,

2005.

Thanks

Regards

Bibi
Senior Database Administrator
CERTIFIED EXPERT
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.