Monday, September 30, 2013

Query to get All Databases Size

User below query to find Database Size :

SELECT 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 

Monday, September 23, 2013

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

Monday, September 16, 2013

SQL Version 32bit or 64 bit

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


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.

Monday, September 9, 2013

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

Monday, September 2, 2013

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
sp_configure 'user connections', 0