Plan Explorer I recently started using this. Since it was licensed earlier, now it’s free. This is an awesome tool for query performance tuning. SQL Server Sentry Plan Explorer is the free tool given to SQL community you can download here https://www.sentryone.com/plan-explorer click download plan explorer button with right bit. It will not ask you much for download. Plan Explorer really makes DBAs job easier, this will help to read the bigger execution plans quickly and which code block taken more time duration, even if it’s 1000 lines code. Once you have installed, open the tool click file –> new and on command text ” select * from sysobjects o…
-
-
FCB::Open failed: Could not open file number 2. OS error: 3(The system cannot find the path specified.).
Troubleshooting steps to do when we cannot start SQL service and receive this alert. Run –> eventvwr –> windows log –> system and filter the errors and read one by one. You can get a hint that what is the real issue. OR open the SQL server errorlog and read. — Search this on my computer ‘ERRORLOG” The description for Event ID 7024 from source Service Control Manager cannot be found. Either the component that raises this event is not installed on your local computer or the installation is corrupted. You can install or repair the component on the local computer. If the event originated on another computer, the display…
-
Load SQL error log to a table
SQL server error log load to a table There are cases, we need a SQL server error log load to a table for troubleshooting. Since there will be a more informational message and logon enabled etc. Here is the script to load and read. -- create table use dba go -- drop table [tbl_errorlog_from_Nov_16_2018] create table [tbl_errorlog_from_Nov_16_2018] ( loaddate datetime, info varchar(20), text varchar(max)) -- load error log from 0 to 6 numbers default insert into [tbl_errorlog_from_Nov_16_2018] exec master..sp_readerrorlog 0 -- select errorlog select * from [tbl_errorlog_from_Nov_16_2018] --where text like '%memory%' -- delete unwanted information delete from [tbl_errorlog_from_Nov_16_2018] where text like 'Login%' delete from [tbl_errorlog_from_Nov_16_2018] where text like '%Error: 18456%' delete…
-
Drive space check SQL server steps and scripts
Following is the script used for drive space related issues. --SQL 2000 -- select name,(size/128.0) as size_mb,* from master.dbo.sysaltfiles order by size_mb desc -- To check the drive free space xp_fixeddrives ------------------------------- get all the db file info & filter the drive, mdf/ldf select db_name(a.database_id) as DBname,a.name as DBfile , size/128.0 AS CurrentSizeMB, --size/128.0 - ((size/128.0) - CAST(FILEPROPERTY(a.name, 'SpaceUsed') AS INT)/128.0) AS UsedSpaceMB, --size/128.0 - CAST(FILEPROPERTY(a.name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB, b.recovery_model_desc,a.type_desc , CASE WHEN is_percent_growth = 0 THEN LTRIM(STR(a.growth * 8.0 / 1024,10,1)) + ' MB, ' ELSE 'By ' + CAST(a.growth AS VARCHAR) + ' percent, 'END + CASE WHEN max_size = -1 THEN 'unrestricted growth' ELSE 'restricted…
-
Database Transaction log file full in the full recovery model
I am seeing in more environment the database recovery model is set FULL and there is no transaction log backup scheduled and the log files are growing bigger, until it reaches a file system space. Me: Hello sir, May I know the reason, why we set the database in full and not taking any transaction log backup. John: I do not know, but it is good to have a database in full mode. Me: No sir, We are having an issue whenever there is a high load in the transaction by application or re-indexing , the transaction log file gets full and we are running out of space. John: So…