Link to home
Start Free TrialLog in
Avatar of bibi92
bibi92Flag for France

asked on

Script for auditing sqlserver

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
Avatar of David Todd
David Todd
Flag of New Zealand image

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
Avatar of bibi92

ASKER

Hi,

2005.

Thanks

Regards

Bibi
ASKER CERTIFIED SOLUTION
Avatar of David Todd
David Todd
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial