https://social.msdn.microsoft.com/Forums/en-US/52a58b4e-1afa-43ef-8783-c359d69cbd65/sql-2008-r2-dynamic-ports-and-static-ports?forum=sqldatabaseengine

Comments

Post a Comment

Popular posts from this blog

current running queries in sql

SELECT r.session_id, s.TEXT, r.[status], r.blocking_session_id, r.cpu_time, r.total_elapsed_time FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s 1) When an application reports a memory pressure issue – use the DMV to identify the current load and choose a session that may be causing a problem. view source print? 1. SELECT * FROM sys.dm_exec_sessions 2) The memory_usage column returns the number of pages used by this session. ORDER BY memory_usage DESC and analyse the session. It may not be the session causing performance hit , but a methodical approach is important. view source print? 1. SELECT * FROM sys.dm_exec_sessions ORDER BY memory_usage DESC 3) Create a query to join with sys.dm_exec_requests. The sys.dm_exec_requests DMV returns detailed information about executed requests in SQL Server. This query includes a CROSS APPLY to sys.dm_exec_sql_text. view source print? 01. select es.session_id,es.memory_usage,sql_h...
Read more

Shrink TempDB

http://www.sqldbadiaries.com/2010/11/13/tempdb-please-allow-me-to-shrink-you/ https://sqlsunday.com/2013/08/11/shrinking-tempdb-without-restarting-sql-server/ use tempdb GO SELECT name, size FROM sys.master_files WHERE database_id = DB_ID(N’tempdb’); GO DBCC FREEPROCCACHE — clean cache DBCC DROPCLEANBUFFERS — clean buffers DBCC FREESYSTEMCACHE (‘ALL’) — clean system cache DBCC FREESESSIONCACHE — clean session cache DBCC SHRINKDATABASE(tempdb, 10); — shrink tempdb dbcc shrinkfile (‘tempdev’) — shrink default db file dbcc shrinkfile (‘tempdev2’) — shrink db file tempdev2 dbcc shrinkfile (‘tempdev3’) — shrink db file tempdev3 dbcc shrinkfile (‘tempdev4’) — shrink db file tempdev4 dbcc shrinkfile (‘templog’) — shrink log file GO — report the new file sizes SELECT name, size FROM sys.master_files WHERE database_id = DB_ID(N’tempdb’); GO sp_WhoIsActive to get to the root cause of this. It was really clear once I filtered down to the timeframe in question and sorted by the tempdb allocation c...
Read more