Get all information for all Database

Use below query to find all information for all database.


SELECT Database_ID, CONVERT(VARCHAR(25), DB.Name) AS DBName, 
CONVERT(VARCHAR(10), DATABASEPROPERTYEX(name, 'status')) AS [Status], State_Desc,
(SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS DataFiles,
(SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS [Data File Size (MB)],
(SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'log') AS LogFiles,
(SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'log') AS [Log File Size (MB)],
User_Access_Desc AS [User Access], Recovery_Model_Desc AS [Recovery Model], 
CASE Compatibility_Level 
WHEN 60 THEN '60 (SQL Server 6.0)'
WHEN 65 THEN '65 (SQL Server 6.5)'
WHEN 70 THEN '70 (SQL Server 7.0)'
WHEN 80 THEN '80 (SQL Server 2000)'
WHEN 90 THEN '90 (SQL Server 2005)'
WHEN 100 THEN '100 (SQL Server 2008)'
WHEN 110 THEN '110 (SQL Server DENALI)'
END AS [DB Compatibility Level],
CONVERT(VARCHAR(20), create_date, 103) + ' ' + CONVERT(VARCHAR(20), create_date, 108) AS [Creation Date],
ISNULL((SELECT TOP 1 CASE TYPE WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction log' END + ' – ' +
LTRIM(ISNULL(STR(ABS(DATEDIFF(DAY, GETDATE(),Backup_finish_date))) + ' days ago', 'NEVER')) + ' – ' +
CONVERT(VARCHAR(20), backup_start_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_start_date, 108) + ' – ' +
CONVERT(VARCHAR(20), backup_finish_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_finish_date, 108) +
' (' + CAST(DATEDIFF(second, BK.backup_start_date,
BK.backup_finish_date) AS VARCHAR(4)) + ' ' + 'seconds)'
FROM msdb..backupset BK WHERE BK.database_name = DB.name ORDER BY backup_set_id DESC),'-') AS [Last Backup],
CASE WHEN is_fulltext_enabled = 1 THEN 'Fulltext enabled' ELSE '' END AS [FullText],
CASE WHEN is_auto_close_on = 1 THEN 'autoclose' ELSE '' END AS [AutoClose],
page_verify_option_desc AS [Page Verify Option],
CASE WHEN is_read_only = 1 THEN 'read only' ELSE '' END AS [Read Only],
CASE WHEN is_auto_shrink_on = 1 THEN 'autoshrink' ELSE '' END AS [AutoShrink],
CASE WHEN is_auto_create_stats_on = 1 THEN 'auto create statistics' ELSE '' END AS [Auto Create Statistics],
CASE WHEN is_auto_update_stats_on = 1 THEN 'auto update statistics' ELSE '' END AS [Auto Update Statistics],
CASE WHEN is_in_standby = 1 THEN 'standby' ELSE '' END AS [StandBy],
CASE WHEN is_cleanly_shutdown = 1 THEN 'cleanly shutdown' ELSE '' END AS [Cleanly Shutdown]
FROM sys.databases DB ORDER BY DBName, [Last Backup] DESC, NAME


Ref: http://blog.sqlauthority.com

TempDB space needed to execute checkdb.

Sometime we may need to check the space required to execute CheckDB. We can run this query in live database because actually it is not running CheckDB Query. It is only checking the amount of space required to run this query. Use Below query to check this:

DBCC CheckDB('Database_Name') WITH EstimateOnly

Example:

DBCC CheckDB('DemoDatabase') WITH EstimateOnly

It will the below output:

Database Mirroring login attempt by User ' ' failed with error: 'Connection handshake failed',

Sometime you may receive an error in SQL Server's log and Database Mirroring stops:

Database Mirroring login attempt by user ' ' failed with error: 'Connection handshake failed. The login ' ' does not have CONNECT permission on the endpoint. State 84.'.  [CLIENT: ]

This message actually informs that your security configuration for the mirroring protocol is broken. You can easily rectify this error by fixing the permission on endpoint using below SQL:

GRANT Connect on EndPoint::[EndPoint Name] To [Account]

Below command will fix this issue assuming end point name is “mirroring” and account name used is “ArunL\test”

GRANT Connect on EndPoint::[EndPoint Name] To [ArunL\test]

Note: If your mirroring is suspended then you have to resume mirrirong after this command.

Ctrl + N shortcut key is not working in SQL Server 2008

Ctrl + N key is used to open new query window in SQL Server. It is not working in SQL Server 2008. To enable this we have to change the keyboard layout to “SQL Server 2000” and then back to “Standard” and that will restore the default keyboard mappings.

To change the keyboard mappings, select Tools->Options from the SSMS menu and then go to Environment->Keyboard. From that spot, you can change the keyboard layout. Change the layout and press the “Ok” button to save the changes. Then, repeat the steps and select the standard layout and press “Ok” again. That will set the default keyboard mappings, which will include the Ctrl-N mapping.

SQL Server connectivity issues troubleshooting

Xinwei Hong (MSFT) wrote a great troubleshooting guide on how to identify and resolve SQL Server connectivity issues on the SQL Protocols blog.

Xinwei broke it down to 6 possible causes:

1. Network issue.
2. SQL Server configuration issue.
3. Firewall issue.
4. Client driver issue.
5. Application configuration issue.
6. Authentication and logon issue.

For each cause. Xinwei lists some background on why that cause could a problem along with specific commands to run to help diagnose the problem.  For example, to determine if #2, SQL Server configuration issue, is the root cause of the problem; the following steps are listed:

You need to make sure the target SQL Server is running and is listening on appropriate protocols. You can use SQL Server Configuration Manager (SCM) to enable protocols on the server machine. SQL Server supports Shared Memory, Named Pipes, and TCP protocols (and VIA which needs special hardware and is rarely used). For remote connection, NP and/or TCP protocols must be enabled. Once you enabled protocols in SCM, please make sure restart the SQL Server.

You can open errorlog file to see if the server is successfully listening on any of the protocol. The location of errorlog file is usually under:
%ProgramFile%Microsoft SQL Server/MSSQLxx.xxx/MSSQL/Log
If the target SQL instance is a named instance, you also need to make sure SQL Browser is running on the target machine. If you are not able to access the remote SQL Server, please ask your admin to make sure all these happen.