Query to get All Databases Size

User below query to find Database Size :

SELECT  d.name AS [Database Name],

        ROUND(SUM(mf.size) * 8 / 1024, 0) [Size (MB)]

FROM    sys.master_files mf

        INNER JOIN sys.databases d ON d.database_id = mf.database_id

WHERE   d.database_id > 4 

GROUP BY d.name 

ORDER BY d.name

Database Engine Tuning Adviser not found

Today, I want to optimize some some queries. I searched the net and found that we can use Database Engine Tuning Adviser for this. But I could not found that in SSMS. 

Again I searched the net and found that it is not available with SQL server express edition.

So I uninstalled the SQL Server Express Edition and  installed SQL Server Developer Edition. 

Now I got the Database Engine Tuning Adviser

SQL Version 32bit or 64 bit

The version information of SQL server you are running can be returned using below query:

SELECT @@VERSION

If the version returned contained x86 that means it is a 32 bit installation and if it contains x64 then it means it is a 64 bit installation.

Backup file information

Below script shows you all database backup files. 

It shows you Database Name, backup done by which user, backup file location, backup start date time and end date time and backup file size.

SELECT database_name, user_name as ExecutedBy, physical_device_name,
backup_start_date, backup_finish_date, backup_size
FROM msdb..backupset bckset
INNER JOIN msdb..backupmediafamily bckfmly
ON bckset.media_set_id = bckfmly.media_set_id

Error 233

Sometime you may get the below error while connecting to SQL Server.

A Connection was successfully established with the server, but than an error occurred during the login process, (Provider: Shared Memory Provider, error: 0 - No process is on the other end end of pipe.)
(Microsoft SQL Server Error: 233)

The above error clearly states that maximum value for user connection is already reached so you can't login to SQL Server. If you are the system administrator than you can increase the maximum value by using the SP sp_configure.

Use the below steps to increase the value. 

Open the SQL Server Management Studio.
Write down the below queries:

sp_configure 'show advanced options', 1
Go
reconfigure
Go
sp_configure 'user connections', 0
Go
reconfigure
Go