Category: SQL Server
-
Read a big file using PowerShell
# Command-Format: Get-Content ‘your-file-path’ -Tail <number-of-line>Get-Content ‘.\mylargefile.txt’ -Tail 5000 # Get-Content D:\MyBigFeedFile.txt | Select-Object -skip 123798766555672 -first 6
//
-
Win OS Related Quick Commands
Find Who Restarted Windows Server –Run the following command to filter the System Logs with Source as User32. Get-EventLog -LogName System | Where Source -eq User32 –You may also filter the system logs with Event ID 1074 with the following command. Get-EventLog -LogName System | Where EventID -eq 1074
//
-
Update Patch Error: Failed to retrieve data for this request.
Update Patch Error: Failed to retrieve data for this request. Step: 1– Login to the Primary Server and install Cumulative Update (CU20) on the Server. Double click on Cumulative patch Update and Install the update. When the Dialog box opens, Click on Yes While installing the Patch in the cluster Environment, if you get the following…
//
-
-
Data Integrity
Introduction to Data Integrity It is important that data maintain data integrity, which is adherence to business rules determined by the database administrator or application developer. Business rules specify conditions and relationships that must always be true or must always be false. For example, each company defines its own policies about salaries, employee numbers, inventory tracking,…
//
-
Copy User DB and System Data and Log Files from One Drive to Another Due to Disk Slowness Issue
Hi guys, in this blog I am going to explain you about copy User DB and System Data and Log Files from One Drive to Another Due to Disk Slowness Issue. Issue- Client Disk Slowness Issue. Solution- These guys set up a 1TB SSD in the server and made partitions from it as well. In…
//
-
SQL Loader
Hi guys, in this blog I am going to explain you about SQL Loader. SQL Loader- Oracle has a powerful application called SQL Loader, which is often referred to as SQL*Loader, that makes it easy to load data from external files into Oracle database tables. When dealing with enormous volumes of data that must be…
//
-
Data file and Log File
Data file: Datafiles are physical files of the operating system that store the data of all logical structures in the database. They must be explicitly created for each tablespace. Oracle assigns each datafile two associated file numbers, an absolute file number and a relative file number, that are used to uniquely identify it. In Oracle, you can…
//
-
Performance Monitoring Related
Performance Monitoring Related- Session Related –Returns information about all the waits encountered by threads that executed for each session. select * from sys.dm_exec_session_wait_stats where session_id in (select session_id from sys.dm_exec_sessions where status not like ‘sleeping’ and session_id <> @@SPID –and session_id <> *** ) order by 3 desc –Returns information about running session. select * from sys.dm_exec_sessions where status not like ‘sleeping’ and session_id <> @@SPID –Find all queries waiting in the memory queue. SELECT * FROM sys.dm_exec_query_memory_grants WHERE grant_time IS NULL; — Retrieve every query plan from the plan cache SELECT * FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle); –sp_whoisactive –Active requests with memory grants SELECT…
//
-
Scan SQL Server Error Log
Scan SQL Server Error Log create table #t_789(Log_date datetime,Process_Info Nvarchar(1000),Txt Nvarchar(3500) ) insert into #t_789 exec sp_readerrorlog; insert into #t_789 exec sp_readerrorlog 1; insert into #t_789 exec sp_readerrorlog 2; insert into #t_789 exec sp_readerrorlog 3; select * from #t_789 where Txt like ‘%error %’; Scan Current Error Log –create table #t_789(Log_date datetime,Process_Info Nvarchar(1000),Txt Nvarchar(3500) ) insert into #t_789 exec sp_readerrorlog; select Log_date,txt from #t_789 order by 1 desc ; truncate table #t_789
//
-
Database Maintenance
Index Fragmentation Info –All Indexes select object_schema_name(ps.object_id) as ObjectSchema, object_name (ps.object_id) as ObjectName, ps.object_id ObjectId, i.name as IndexName, ps.avg_fragmentation_in_percent, ps.page_count from sys.dm_db_index_physical_stats(db_id(), null, null, null, null) ps inner join sys.indexes i on i.object_id = ps.object_id and i.index_id = ps.index_id where avg_fragmentation_in_percent > 10 and ps.index_id > 0 and page_count >100 order by page_count desc –Specific Index select object_schema_name(ps.object_id) as ObjectSchema, object_name (ps.object_id) as ObjectName, ps.object_id ObjectId, i.name as IndexName, ps.avg_fragmentation_in_percent, ps.page_count from sys.dm_db_index_physical_stats(db_id(), null, null, null, null) ps inner join sys.indexes i on i.object_id = ps.object_id and i.index_id = ps.index_id where ps.index_id > 0 and i.name like ‘Index_Name‘ order by 5 desc —————————————————————- ————————————–> use Database_Name ————————————– Declare @TBname nvarchar(255), @schema nvarchar(255), @DB_NAME nvarchar(255), @SQL nvarchar(max) DECLARE @db_id SMALLINT; DECLARE @object_id INT; ————————————–> SET @DB_NAME =…
//
-
Missing Indexes in a DB
–It runs at DB Instance Level. SELECT CONVERT (varchar(30), getdate(), 126) AS runtime, mig.index_group_handle, mid.index_handle, CONVERT (decimal (28, 1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) ) AS improvement_measure, ‘CREATE INDEX missing_index_’ + CONVERT (varchar, mig.index_group_handle) + ‘_’ + CONVERT (varchar, mid.index_handle) + ‘ ON ‘ + mid.statement + ‘ (‘ + ISNULL (mid.equality_columns, ”) + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ‘,’ ELSE ” END + ISNULL (mid.inequality_columns, ”) + ‘)’ + ISNULL (‘ INCLUDE (‘ + mid.included_columns + ‘)’, ”) AS create_index_statement, migs.*, mid.database_id, mid.[object_id] FROM sys.dm_db_missing_index_groups mig INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle WHERE CONVERT (decimal (28, 1),migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10 ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC
//