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…
-
AlwaysON RECOVERY PENDING SQL Server Bugs Enhancement Requests T-SQL Tuesday
This month’s T-SQL Tuesday topic is “SQL Server Bugs & Enhancement Requests” and hosted by my favorite and inspirational Brent Ozar. Here is my bug report: https://connect.microsoft.com/SQLServer/feedback/details/3022019 and it is still in active. It happen one of my production database, when I tried to remove the database from alwaysON and other important databases went recovery pending and inaccessible. I have no idea what happened to all other databases, then I came to know it is a bug. ” The issue is when the database removed from the primary replica, with the secondary disconnection the higher database IDs on the secondary went into “NOT SYNCHRONIZED and RECOVERY PENDING” state,…
