

Finally, we just have to click on "Test Connection" in the tab "Connection" to proceed with the test. If the connectivity to the SQL instance is ok, you will see the message "Test connection succeeded", it couldn't have been simpler!

select spid, StartTime, Textdata, EventSubclass, ObjectID, DatabaseID, SQLHandle from fn_trace_gettable('C:\RecompilationTrace_01.trc', 1) where EventClass in(37,75,166) -- 37 = Sp:Recompile, 75 = CursorRecompile, 166 = SQL:StmtRecompile
select top 20 SQLText.text, sql_handle, plan_generation_num, execution_count, dbid, objectid from sys.dm_exec_query_stats cross apply sys.dm_exec_sql_text(sql_handle) as SQLText where plan_generation_num >1 order by plan_generation_num desc
Ports and Protocols Used by Microsoft SQL Server 2000 | ||
Service / Purpose | Protocol | Port |
Analysis Services | TCP | 2725 |
Client connections when "hide server" option enabled | TCP | 2433 |
Clients using Named Pipes over Netbios | TCP | 139/445 |
Microsoft SQL Monitor port | UDP | 1434 |
OLAP Services connections from downlevel clients OLAP Services 7.0 | TCP | 2393/2394 |
SQL over TCP ** | TCP | 1433 |
Standard URL for a report server (Reporting Services) | TCP | 80 HTTP /443 SSL |
Ports and Protocols Used by Microsoft SQL Server 2005 | ||
Service / Purpose | Protocol | Port |
Analysis Services connections via HTTP (default) | TCP | 80 |
Analysis Services connections via HTTPS (default) | TCP | 443 |
Clients using Named Pipes over Netbios | TCP | 137/138/139/445 |
Dedicated Administrator Connection | TCP | 1434 by default (local port). But this port is assigned dynamically by SQL Server during startup. |
Reporting services on Windows 2003/2008/Vista (default) | TCP | 80 |
Reporting services on Windows XP SP2 | TCP | 8080 |
SQL Server 2005 Analysis Services | TCP | 2383 |
SQL Server Browser Service | TCP | 2382 |
SQL Server Integration Services (MSDTSServer) | TCP | 135 |
SQL Server Resolution Protocol | TCP | 1434 |
SQL over TCP (default instance) | TCP | 1433 |
SQL over TCP (named instances) | TCP | 1434 / 1954 |
Ports and Protocols Used by Microsoft SQL Server 2008/2012/2014/2016/2017 | ||
Service / Purpose | Protocol | Port |
Analysis Services connections via HTTP (default) | TCP | 80 |
Analysis Services connections via HTTPS (default) | TCP | 443 |
Clustering | UDP | 135 |
Clustering | TCP | 135 (RPC) / 3343 (Cluster Network Driver) / 445 SMB / 139 NetBIOS / 5000-5099 (RPC) / 8011-8031 (RPC) |
Database Mirroring | TCP | There is no default port for this service. Use the following T-SQL statements to identify which ports are in use: SELECT name, port FROM sys.tcp_endpoints. |
Dedicated Administrator Connection | TCP | 1434 by default (local port). But this port is assigned dynamically by SQL Server during startup. |
Filestream | TCP | 139 y 445 |
Microsoft Distributed Transaction Coordinator (MS DTC) | TCP | 135 |
Reporting services Web Services | TCP | 80 |
Reporting Services configured for use through HTTPS | TCP | 1433 |
Service Broker | TCP | 4022 |
SQL Server Analysis Services | TCP | 2382 (SQL Server Browser Services for SSAS port) |
2383 (Clusters will listen only on this port) | ||
SQL Server Browser Service (Database Engine) | UDP | 1434. Might be required when using named instances. |
SQL Server Browser Service | TCP | 2382 |
SQL Server default instance running over an HTTPS endpoint. | TCP | 443 |
SQL Server Instance (Database Engine) running over an HTTP endpoint. | TCP | 80 y 443 (SSL) |
SQL Server Integration Services | TCP | 135 (DCOM) |
SQL over TCP (default instance) | TCP | 1433 |
Transact-SQL Debugger | TCP | 135 |
Windows Management Instrumentation | TCP | 135 (DCOM) |
SELECT isnull(d.name,'') DatabaseName, CASE is_read_only WHEN 1 THEN 'YES' ELSE 'NO' END as IsReadOnly, cast(sum(size)/128.0/1024.0 as decimal(8,2) ) as DBSize_InGB , isnull( last_backup.recovery_model,'')RecoveryModel,isnull(last_backup.name,'') as BackupName , isnull(cast(last_backup.backup_start_date as varchar(24)),'(No Taken)') as BackupStartDate, isnull(case when [backup_size]>1024 then cast( cast([backup_size]/1024.0 as decimal(8,2)) as varchar(12)) + 'GB' else cast([backup_size] as varchar(12)) + 'MB' end,0) as BackupSize, isnull(case when compressed_backup_size>1024 then cast( cast(compressed_backup_size/1024.0 as decimal(8,2)) as varchar(12)) + 'GB' else cast(compressed_backup_size as varchar(12)) + 'MB' end,0) as CompressedBackupSize , isnull([duration],'') as Duration, isnull(is_copy_only,'') as IsCopyOnly, isnull(is_damaged,'') as IsDamaged, isnull(has_backup_checksums,'') as HasBackupCheckSUM, isnull([user_name],'')as UserName FROM master.sys.sysaltfiles f inner join sys.databases d on f.dbid=d.database_id inner join (select database_name,backup_start_date, name, is_damaged,is_password_protected, has_incomplete_metadata,is_copy_only, cast(backup_size/1024.0/1024.0 as decimal(8,2)) as [backup_size], cast(compressed_backup_size/1024.0/1024.0 as decimal(8,2)) as compressed_backup_size, isnull(cast( CAST((DATEDIFF(second, bs.backup_start_date, bs.backup_finish_date))/3600 AS varchar) + ' hours ' + CAST(((DATEDIFF(second, bs.backup_start_date, bs.backup_finish_date))%3600 )/60 AS varchar)+ ' min' as varchar(40) ), '(No Available)') as [duration],bs.backup_finish_date, has_backup_checksums , [user_name], recovery_model FROM msdb.dbo.backupset bs WHERE bs.backup_set_id IN (SELECT MAX(backup_set_id) FROM msdb.dbo.backupset where type = 'D' GROUP BY database_name) AND bs.database_name IN (SELECT name FROM master.dbo.sysdatabases ) ) as last_backup on d.name = last_backup.database_name where fileid<>2 group by dbid,d.name , d.is_read_only,d.recovery_model_desc, last_backup.database_name, last_backup.backup_start_date,last_backup.[backup_size] ,last_backup.compressed_backup_size,last_backup.name, last_backup.duration, last_backup.backup_finish_date,is_copy_only,is_damaged,has_backup_checksums , [user_name], last_backup.recovery_model ORDER BY DATEDIFF(minute, last_backup.backup_start_date, last_backup.backup_finish_date) desc