• DBA,  Junior DBA

    Drive space check SQL server steps and scripts

    November 13, 2018

    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…

    Read More
  • DBA

    Database Transaction log file full in the full recovery model

    October 16, 2018

    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…

    Read More
  • AlwaysON,  SQL party

    AlwaysON RECOVERY PENDING SQL Server Bugs Enhancement Requests T-SQL Tuesday

    January 11, 2017

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

    Read More
  • VLDB

    VLDB very large database DBCC checkDB

    December 31, 2016

    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. How important is the CHECKDB. 2. How to fine tune and use the checkDB for VLDBs. Methods of troubleshooting the corruption issues. Storage / VM Admin:  Sent a graph states that, IOPS for the…

    Read More