Thursday, May 20, 2010

Useful T-SQL Queries For A SQL Server 2008 Administrator

Here is a list of Transact SQL Queries, which we need in our day to day database administration activity.

1. COLLECT SQL SERVER DETAILS 
SELECT
SERVERPROPERTY('MachineName') as 'Machine Name',
SERVERPROPERTY('ServerName') as 'Server Name',
SERVERPROPERTY('ProductLevel') as 'Product Level',
SERVERPROPERTY('ProductVersion') as 'Product Version',
SERVERPROPERTY('collation') as 'Server Collation',
SERVERPROPERTY('edition') as 'SQL Server Edition',
SERVERPROPERTY('InstanceName') as 'Instance Name',
CASE SERVERPROPERTY('IsClustered')
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
END as 'Is it clustered?',
CASE SERVERPROPERTY('IsFullTextInstalled')
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
END as 'Full text engine installed?',
CASE SERVERPROPERTY('IsIntegratedSecurityOnly')
WHEN 0 THEN 'Mixed'
WHEN 1 THEN 'Integrated security only'
END as 'Security mode',
SERVERPROPERTY('LicenseType') as 'License Type',
SERVERPROPERTY('NumLicenses') as 'NumLicenses'
GO 

2. COLLECT SQL SERVER DETAILS 
master..xp_msver Language, Platform,CompanyName,FileDescription,WindowsVersion,ProcessorCount,ProcessorActiveMask,ProcessorType,PhysicalMemory
GO 

3. SP_CONFIGURE 
EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
EXEC sp_configure
EXEC sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
GO 

4. GET ALL THE DATABASE PLACEMENT DETAILS 
EXEC master..sp_helpdb
GO 

5. XP_REGREAD 
EXEC master..xp_regread 'HKEY_LOCAL_MACHINE\',
'SOFTWARE\Microsoft\MSSQLServer\Setup',
'SQLPath'
GO 

6. FINDING DRIVE SPACE IN THE SERVER 
EXEC master..xp_fixeddrives
GO 

7.) FINDING WHEN WAS LAST BACK TAKEN AND LOCATION 
 SELECT A.database_name as 'DBName',
A.backup_finish_date as 'Backup Finished',
B.physical_device_name as 'Backup Filename'
FROM msdb.dbo.backupset A,
msdb.dbo.backupmediafamily B,
(SELECT database_name,
MAX(backup_finish_date) as 'maxfinishdate'
FROM msdb.dbo.backupset
WHERE Type = 'D'
GROUP BY database_name) C
WHERE A.media_set_id = B.media_set_id AND
A.backup_finish_date = C.maxfinishdate AND
A.type = 'D'
ORDER BY DBName
GO 

8. FINDING SQL SERVER VERSION AND DETAILS 
select @@VERSION
GO 

9. FINDING SUB DIRECTORIES 
EXEC master..xp_dirtree 'C:\Program Files\Microsoft SQL Server\MSSQL'
GO 

10. READ THE LOG THROUGH QUERY WINDOW 
EXEC master..xp_readerrorlog
GO 

11. FINDING THE DRIVE SPACE 
EXEC xp_availablemedia
GO 

12. FINDING THE TAPE DEVICE 
EXEC xp_get_tape_devices
GO 

13. FINDING THE FILE DETAILS 
xp_getfiledetails 'C:\NARESH\SQL Server\SITELITE\SP_PH_CREATE_DATABASE_USAGE_SCRIPT.SQL'
GO 

14. IP CONFIGURATION 
exec master..xp_cmdshell 'regedit /E "C:\MSSQL.reg" "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer"' 

exec master..xp_cmdshell 'regedit /E "C:\ODBC.reg" "HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI"' 

exec master..xp_cmdshell 'ipconfig /all'
GO
Useful T-SQL queries for a SQL Server 2008 administrator, Useful queries, Useful Queries for SQL Server DBA, Useful Commands for SQL Server DBA, mysql - Most useful SQL queries, Top 10 Transact-SQL Statements a SQL Server DBA Should Know, Checklist for Analyzing Slow Running Queries, useful query for DBAs, useful query for identifying performance problems within SQL Server, Very useful little query to monitor the health of service broker, SQL Queries for SQL Server, useful query for DBAs, useful query for identifying performance problems within SQL Server, Five Very Useful Index Selection Queries for SQL Server 2005, A Few Useful Queries for SQL Server 2008, Useful new DMV's in SQL Server 2008 R2, Useful new DMV's in SQL Server 2012, Useful new DMV's in SQL Server 2005,

6 comments:

Very good Job....please continue more

Hi Nareshkar,

Great queries…

Fantastic..it works perfactly…

Great Queries..

I think u’r master of Query.. hahaha

Thank u very much… :)

Post a Comment