SQL Server database inspection script

-- 1. view Database Version Information Select @ version -- 2. view All Database names and sizes exec sp_helpdb -- 3. view the operating system parameter exec master of the machine where the database is located .. xp_msver -- 4. view the database startup parameter exec sp_configure -- 5. view database start time select convert (varchar (30), login_time, 120) from master .. sysprocesses where spid = 1--6. view database server name select 'server name: '+ ltrim (@ servername) -- 7. view database instance name select 'instance: '+ ltrim (@ servicename) -- 8. what about the disk space of the database? Exec sp_spaceused -- 9. log File Size and usage DBCC sqlperf (logspace) -- 10. table disk space usage information exec sp_spaceused 'tablename' -- 11. obtain disk read/write status select @ total_read [number of disk reads], @ total_write [number of disk writes], @ total_errors [number of disk write errors], getdate () [current time] -- 12. obtain the I/O operation status select @ io_busy, @ timeticks [number of microseconds corresponding to each clock cycle], @ io_busy * @ timeticks [number of milliseconds for I/O operations], getdate () [current time] -- 13. view CPU activity and working conditions select @ cpu_busy, @ timeticks [number of microseconds corresponding to each clock cycle], @ cpu_busy * cast (@ timeticks as float) /1000 [CPU working time (seconds)], @ idle * cast (@ timeticks as float)/1000 [CPU idle time (seconds)], getdate () [current time] -- 14. check the lock and wait for exec sp_lock -- 15. check the deadlock exec sp_who_lock -- write a stored procedure by yourself./* Create procedure sp_who_lock as begin declare @ spid int, @ BL int, @ inttransactioncountonentry int, @ introwcount int, @ intcountproperties int, @ intcounter int create table # tmp_lock_who (ID int identity (1,1), spid smallint, BL smallint) If @ error <> 0 return @ error insert into # tmp_lock_who (spid, BL) Select 0, blocked from (select * From sys. sysprocesses where blocked> 0) A where not exists (select * from (select * From sys. sysprocesses where blocked> 0) B where. blocked = spid) Union select spid, blocked from sys. sysprocesses where blocked> 0 if @ error <> 0 return @ error -- find the number of records in the temporary table select @ intcountproperties = count (*), @ intcounter = 1 from # tmp_lock_who if @ error <> 0 return @ error if @ intcountproperties = 0 select 'no blocking and deadlock information' as message -- the loop starts while @ intcounter <= @ intcountproperties begin -- obtain the first record select @ spid = spid, @ BL = bl from # tmp_lock_who where id = @ intcounter begin if @ spid = 0 select 'causes a database deadlock:' + Cast (@ BL as varchar (10 )) + 'process No. The SQL syntax it executes is as follows: 'else select' process no. spid: '+ Cast (@ spid as varchar (10 )) + 'blocked by' + 'process number spid:' + Cast (@ BL as varchar (10) +, the SQL syntax executed by the current process is as follows: 'dbcc inputbuffer (@ BL) end -- move the loop pointer down set @ intcounter = @ intcounter + 1 end drop table # tmp_lock_who return 0 end */-- 16. user and process information exec sp_whoexec sp_who2 -- 17. information about active users and processes exec sp_who 'activity' -- 18. view the sqldbcc inputbuffer (process number) exec sp_who3/* Create procedure sp_who3 (@ sessionid Int = NULL) being executed in the process as begin select spid = ER. session_id, status = SES. status, [Login] = SES. login_name, host = SES. host_name, blkby = ER. blocking_session_id, dbname = db_name (ER. database_id), commandtype = ER. command, sqlstatement = ST. text, objectname = object_name (St. objectid), elapsedms = ER. total_elapsed_time, cputime = ER. cpu_time, ioreads = ER. logical_reads + er. reads, iowrites = ER. writes, lastwaittype = ER. last_wait_type, starttime = ER. start_time, protocol = con.net _ transport, connectionwrites = con. num_writes, connectionreads = con. num_reads, clientaddress = con. client_net_address, authentication = con. auth_scheme from sys. dm_exec_requests er outer apply sys. dm_exec_ SQL _text (ER. SQL _handle) ST left join sys. dm_exec_sessions ses on SES. session_id = ER. session_id left join sys. dm_exec_connections con on Con. session_id = SES. session_id where er. session_id> 50 and @ sessionid is null or ER. session_id = @ sessionid order by er. blocking_session_id DESC, er. session_id end */-- 19. view All Database User Logon Information exec sp_helplogins -- 20. view the role information of all database users exec sp_helpsrvrolemember -- 21. view the linked server exec sp_helplinkedsrvlogin -- 22. view remote database user logon information exec sp_helpremotelogin -- 23. get network packet statistics select @ pack_received [number of input packets], @ pack_sent [number of output packets], @ packet_errors [number of error packets], getdate () [current time] -- 24. check whether all objects in the database are allocated and the Organization integrity is incorrect DBCC checkdb -- 25. query the file group and select DF. [name], DF. physical_name, DF. [size], DF. growth, F. [name] [filegroup], F. is_default from sys. database_files DF join sys. filegroups F on DF. data_space_id = f. data_space_id -- 26. view the number of all tables in the database select B. name as tablename,. rowcnt as datacount from sysindexes A, sysobjects B where. id = B. ID and. indid <2 and objectproperty (B. ID, 'ismsshipped ') = 0 -- 27. obtain the first 10 most time-consuming T-SQL statements; with MACO as (select top 10 plan_handle, sum (total_worker_time) as total_worker_time, sum (execution_count) as execution_count, count (1) as SQL _count from sys. dm_exec_query_stats group by plan_handle order by sum (total_worker_time) DESC) Select T. text,. total_worker_time, a.exe cution_count,. SQL _count from MACO a cross apply sys. dm_exec_ SQL _text (plan_handle) t -- 28. check the actual memory usage of SQL Server. Select * From sysperfinfo where counter_name like '% memory %' -- 29. display the log space information of all databases DBCC sqlperf (logspace) -- 30. shrink database DBCC shrinkdatabase (databasename)