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.

When a stored procedure was last modified?

We can get Stored procedure's create date easily by management studio. But finding modified date is not easy. Use below Script to find this. Using below script we can get other objects details also.

USE <Database_Name>;

GO

SELECT name, create_date, modify_date,type_desc

FROM sys.objects

where

name = '<Object_Name>'

GO

Error during Backup

Sometime during backup below error comes.

Error Number: 18272

Error Message: I/O error on backup or restore restart-checkpoint file '%1'. Operating system error %2. The statement is proceeding but is non-restartable.

This generally happens when SQL Server is not able to find the default backup directory. SQL Server needs to create a checkpoint file when a BACKUP or RESTORE operation happens. This happens when the RESTART option is specified. WITH RESTART tells SQL Server to start from the point where the operation was last aborted. When SQL Server searches for the file and its not available then this error is thrown.

How to avoid this error:

1. Check the default backup directory for the SQL Server and see if exists, if not create it.
2. Verify if the backup directory has enough space in it.
3. Verify if SQL Server service account has permissions to read and write into that directory

Find Estimated time for Backup and Restore

Use below script to find Estimated time for Backup and Restore:


SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete)
AS [PERCENT Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GETDATE()),20) AS [ETA COMPLETION TIME],
CONVERT(NUMERIC(6,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed MIN],
CONVERT(NUMERIC(6,2),r.estimated_completion_time/1000.0/60.0) AS [ETA MIN],
CONVERT(NUMERIC(6,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],
CONVERT(VARCHAR(100),(SELECT SUBSTRING(TEXT,r.statement_start_offset/2,
CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
FROM sys.dm_exec_sql_text(sql_handle)))
FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')