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