Testing a SQL database connection

This should ideally be performed on a web server that you feel is not seeing the database server.

1) Open notepad and save a blank file with the name "connection.udl" to your desktop.
2) Right-click on connection.udl to open and run it with the credentials used to access the database.
3) On the Provider tab, choose "Microsoft OLE DB provider for Sql Server" and click Next.
4) On the Connection tab, enter the server name and connection method.
5) Choose a database from the populated list in "Select a database on this server" drop-down list.
6) Click the "Test the connection" button.

Reload missing .NET performance counters

C:\WINDOWS\Microsoft.NET\Framework64\v2.0.50727>lodctr aspnet_perf.ini
C:\WINDOWS\Microsoft.NET\Framework64\v2.0.50727>lodctr aspnet_perf2.ini
C:\WINDOWS\Microsoft.NET\Framework64\v2.0.50727>lodctr corperfmonsymbols

Add private PDB's files to Windows Debugger

Copy the PDB files to a directory on your hard drive. Open WinDBG and ensure that the symbol path includes the directory that contains the PDB files:

.sympath+ c:\pdbs
.reload

Query to find TCP/IP port of SQL Server

http://www.sqlservercentral.com/scripts/Miscellaneous/67422/

Use logparser to grab a specific event from a remote event log (and stuff it into a CSV file)

logparser -i:evt "select distinct Message INTO filename.csv from \\yourservername\Application where Message Like '%http://yoursite.domain.com/Person.aspx?guid%' " -o:CSV

Result of enabling AWE on 8GB SQL Server

AWE enables SQL Server to use ram beyond 4GB in a 32-bit architecture. Here is the "before" and "after" states on a SQL Server with 8GB of RAM (where SQL Server was allocated 7GB). Note the 272MB available before AWE and the 6.79GB available AWE was enabled.

Turn off default tracing in SQL Server

SQL Server 2005 runs a default trace. Run this query to determine if tracing is enabled:

SELECT *
FROM fn_trace_getinfo(default);
GO

This yields the result set below, where property 5 ("Current Trace status") is set to a value of 1 (where "1" means on and "0" means off).



To turn off default tracing, run this query:

sp_configure 'default trace enabled', 0

To reenable it, run this query:
sp_configure 'default trace enabled', 1

Enable Fusion log to troubleshoot assembly binding.

HKLM/Software/Microsoft/Fusion/EnableLog DWORD 1

Determine fragmentation level of SQL Server databases

select * from sys.dm_db_index_physical_stats (null,null,null,null,null)