bibi92
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/reindexati on
-- 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
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/reindexati
-- 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
ASKER
Hi,
2005.
Thanks
Regards
Bibi
2005.
Thanks
Regards
Bibi
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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