Query for finding the SQL server information

select
serverproperty('Edition') EDITION,
serverproperty('ComputerNamePhysicalNetBIOS') 'COMPUTER NAME',
serverproperty('InstanceName') 'INSTANCE NAME',
serverproperty('IsClustered') 'IS CLUSTERED',
serverproperty('MachineName') 'Machine name',
serverproperty('ProductVersion') 'SQLPRODUCT VERSION',
serverproperty('ProductLevel') 'PRODUCT LEVEL',
serverproperty('ServerName') 'Server Name'
GO

Finding Job and owner details of Scheduled jobs in MSDB database

select sj.name,sj.description,suser_sname(sj.owner_sid) 'Job Owner',sj.enabled,sj.date_created,sj.date_modified,
sjs.next_run_date,sjs.next_run_time from msdb..sysjobs sj join
msdb..sysjobschedules sjs on sj.job_id=sjs.job_id
order by sj.name
..

Above query will return the job name, owner name, and some important information needed for the user..

Hope it gets useful..

SQL Server DBA Checklists ( Daily, Weekly & Monthly )

Daily

    Check system uptime (just in case I need to check anything as a DBA)
    Check the last backup
    Check the transaction log backups
    Check the status of SQL Jobs
    Check the average CPU usage for the last 24 hours (or 1140 mins)
    Check the database status
    Check the SQL error log and event viewer

Weekly

    Check MSDB backup history
    Check to see when the last time CheckDB and Update stats was run
    Check index fragmentation
    Check index stats (reads vs writes etc)
    Check for IO bottlenecks

Monthly

    Check missing indexes
    Check indexes that are no longer used
    Estimate the Disk usage by MDF file growing

****************************************************************************
 DAILY CHECK LIST:

    Backups
        Check for backup emails
        How long did the backup take to run (database backup duration)
        Verify that all databases are being backed up according to a maintenance plan
    Disk free space. Note significant variations from previous check. Log files may be affected dramatically by monthly jobs
   
Job failures. Filter job activity for failures
   
System checks. Look in sql logs for any critical errors.
        Application logs
    Performance
        Check performance statistics on all servers
        Check that counters are in normal range on all production servers
    Connectivity
        Verify the customer application can get data from the database
        Verify acceptable speed of access data
    Replication. Verify that the each publication and distributor is running for each subscription.
    Logshipping :Check the all jobs which should be run on as per the scheduled and check the availability space drives which is residing the T-log backup files.

    Mirroring : check the stats of mirroring.

Installing the Service Pack and Hot Fixes on SQL2005 Cluster

Once you have installed SQL Server 2005 clustering, your next step is to install the latest SQL Server 2005 service pack and hot fixes, which can be downloaded from Microsoft’s Web site. Installing a service pack or hot fix is fairly straightforward because they are cluster-aware. Once the service pack or hot fix setup program is started, it detects that you have a cluster and will upgrade all nodes simultaneously. Once setup is complete, you may need to reboot your servers and failover the nodes. Generally, once I have run the service pack, I like to reboot the active node first. Once it has rebooted, then I reboot the passive node. This way, failover and failback is automatic.

Query for finding the data and log file space usage in a Database

Select a.FILEID,
[FILE_SIZE_MB] =
convert(decimal(12,2),round(a.size/128.000,2)),
[SPACE_USED_MB] =
convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)),
[FREE_SPACE_MB] =
convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) ,
NAME = left(a.NAME,15)
from
sysfiles a
Thanks
ASR

SQL Server Agent failed status

If the error

[298] SQLServer Error: 21, Encryption not supported on the client. [SQLSTATE 08001]

[298] SQLServer Error: 21, Client unable to establish connection [SQLSTATE 08001]

[165] ODBC Error: 0, An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. [SQLSTATE 08001]

[000] Unable to connect to server ‘\SQL2005′; SQLServerAgent cannot start

[298] SQLServer Error: 21, Encryption not supported on the client. [SQLSTATE 08001]

[298] SQLServer Error: 21, Client unable to establish connection [SQLSTATE 08001]

[165] ODBC Error: 0, An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. [SQLSTATE 08001]

[382] Logon to server ‘\SQL2005′ failed (DisableAgentXPs)

[098] SQLServerAgent terminated (normally)

Event ID:103

———————————————————

Follow the steps to FIX

Goto –>  SQL Server Configuration Manager –> SQL Native Client Configuration –> right Click on properties if no values are present

Goto–> Contol Panel –> Add remove programs –> (Right) Click on SQL Native Client –> Repair (Note: No reboot required)

Check the properties again, if values are present –> Start the SQL Server Agent.

–***************************–

If the error

[298] SQLServer Error: 65535, SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. [SQLSTATE 08001]

To fix this we need to do the changes in the Registry

Goto –> Run –> Regedit –> Take the back of the registry before doing the changes –>

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\MSSQL.1\SQLServerAgent\ServerHost –> Add Virtual Name\SQL Server Instance Name

And start the SQL Server Agent. If its in Cluster do the changes on Both the Nodes.

Finding the backup history details for specific Database

DECLARE @DBNAME VARCHAR(128)

SET @DBNAME = 'DATABASENAME'

SELECT A.database_name, B.physical_device_name
,A.media_set_id,A.backup_size,
A.backup_start_date,A.backup_finish_date
FROM msdb.dbo.backupset A
INNER JOIN msdb.dbo.backupmediafamily B
ON A.media_set_id = B.media_set_id
WHERE A.Database_Name= @DBNAME
ORDER BY A.backup_finish_date DESC


Thanks
Atcheswara Reddy