Some Notes, Some Scripts

These are the noted taken for searching solutions and some scripts to identify the problem

Categories:

SELECTCONVERT(DECIMAL(12,3),SUM(user_object_reserved_page_count) / 128.)AS [User Objects (MB)],CONVERT(DECIMAL(12,3),SUM(internal_object_reserved_page_count) / 128.) AS [Internal Objects (MB)],CONVERT(DECIMAL(12,3),SUM(version_store_reserved_page_count) / 128.) AS [Version Store (MB)],CONVERT(DECIMAL(12,3),SUM(unallocated_extent_page_count) / 128.) AS [Free Space (MB)]FROMtempdb.sys.dm_db_file_space_usage WITH (NOLOCK);

Categories:

Steps are: Suspend your database to close data movements Change Readable secondary Nodes to “NO” Move Your Datafiles Alter database <database_name> MODIFY FILE (NAME = <logical file_name>, FILENAME = <physical_file_name>) Close SQL Service Move Datafiles to specified path […]

Categories:

;WITH BufPoolStatsAS(SELECTdatabase_id,COUNT_BIG() AS page_count ,CONVERT(DECIMAL(16,3),COUNT_BIG() * 8 / 1024.) AS size_mb,AVG(read_microsec) AS avg_read_microsecFROMsys.dm_os_buffer_descriptors WITH (NOLOCK)GROUP BYdatabase_id)SELECTDB_NAME(database_id) AS [DB],size_mb,page_count,avg_read_microsec,CONVERT(DECIMAL(5,2), 100. * (size_mb / SUM(size_mb) OVER()))AS [Percent]FROMBufPoolStatsORDER BYsize_mb DESCOPTION (MAXDOP 1, RECOMPILE);

Categories:

SELECT total_physical_memory_kb / 1024 AS [Physical Memory (MB)],available_physical_memory_kb / 1024 AS [Available Memory (MB)],total_page_file_kb / 1024 AS [Page File Commit Limit (MB)],available_page_file_kb / 1024 AS [Available Page File (MB)],(total_page_file_kb – total_physical_memory_kb) / 1024AS [Physical Page File Size (MB)],system_cache_kb […]

Categories:

DECLARE @now BIGINT;SELECT @now = cpu_ticks / (cpu_ticks / ms_ticks)FROM sys.dm_os_sys_info WITH (NOLOCK);;WITH RingBufferData([timestamp], rec)AS(SELECT [timestamp], CONVERT(XML, record) AS recFROM sys.dm_os_ring_buffers WITH (NOLOCK)WHEREring_buffer_type = N’RING_BUFFER_SCHEDULER_MONITOR’ ANDrecord LIKE N’%%’),Data(id, SystemIdle, SQLCPU, [timestamp])AS(SELECTrec.value(‘(./Record/@id)[1]’, ‘int’),rec.value(‘(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]’,’int’),rec.value(‘(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]’,’int’),[timestamp]FROM RingBufferData)SELECT TOP 256dateadd(MS, -1 * (@now […]

Categories:

Your database's LDF file, that is, the LOG file, has grown, you are trying to SHRINK, but if it doesn't work: First of all, if the Recovery Mode of your database is FULL, you need to do it […]

Categories:

Running DBCC CHECKDB and get out of TEMPDB errors although having enough disk space. CHKDSK commands also cannot find any bad sector but I cannot perform SELECT some of my tables and I cannot perform BACKUP. So you […]

Categories:

You realized that your database has grown and you want to check who perform data load or run heavy queries: DECLARE @current_tracefilename VARCHAR(500);DECLARE @0_tracefilename VARCHAR(500);DECLARE @indx INT;SELECT @current_tracefilename = pathFROM sys.tracesWHERE is_default = 1;SET @current_tracefilename = REVERSE(@current_tracefilename);SELECT @indx […]

Categories:

Let's assume that you have created separate datafile for non-clustered indexes but you want to check them weather you had created in the right datafile