• DBA

    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…

  • DBA,  Junior DBA

    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…

  • DBA

    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,  SQL party

    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,…

  • VLDB

    VLDB very large database DBCC checkDB

    Database corruption – DBCC checkDB for Very large database We know SQL server data is stored in a filesystem storage. There has been always an (I/O) input and output interaction between SQL server and storage subsystem both in the memory and disk. IO subsystem plays a major role, 99% of the time database corruption can happen with IO subsystem (Such as in the controllers, disk and driver level etc.) In this post, I am sharing few things. 1. How important is the CHECKDB. 2. How to fine tune and use the checkDB for VLDBs. 3. Methods of troubleshooting the corruption issues. Storage / VM Admin:  Sent a graph states that, IOPS…