Top 10 SQL Server administration commands used in daily.
Essential commands for any DBA life.
These are some of simple commands daily i will use in my environment.
These top 10 commands might vary in different environment to environment depends on setup.
1.SP_who2
SP_who2 active
2.SELECT @@version
SELECT SERVERPROPERTY(‘MachineName’) as HOST,
SERVERPROPERTY(‘INSTANCENAME’) as ‘Instance Name’,
SERVERPROPERTY(‘PRODUCTLEVEL’) as ‘Service Pack’,
SERVERPROPERTY(‘PRODUCTVERSION’) as ‘Product Version’,
CASE SERVERPROPERTY(‘IsClustered’)
WHEN 1 THEN ‘CLUSTERED’ ELSE ‘STAND ALONE’ END as ‘Server Type’
3. DBCC SQLPERF(LOGSPACE)
4. select * from sys.configurations order by NAME
or
SP_CONFIGURE
5. select l.name,l.dbname,l.denylogin,l.isntname,
l.isntgroup,isntuser,sysadmin,securityadmin
from master.dbo.syslogins l
where l.sysadmin=1 OR l.SecurityAdmin=1
6. DBCC TRACESTATUS
7. select name,compatibility_level,
state_desc,recovery_model_desc
from sys.databases
8. select db_name(database_id) ,type_desc,physical_name
from sys.master_files
or sp_hepdb <>
9. SELECT db.name,
case when MAX(b.backup_finish_date) is NULL
then ‘No Backup’ else convert(varchar(100),
MAX(b.backup_finish_date)) end AS last_backup_finish_date
FROM sys.databases db
LEFT OUTER JOIN msdb.dbo.backupset b
ON db.name = b.database_name AND b.type = ‘D’
WHERE db.database_id NOT IN (2)
GROUP BY db.name
ORDER BY 2 DESC
No comments:
Post a Comment