The transaction log for database is full due to replication CDC

    I have got an email alert differential backup is failed for one of the database. There are 80+ databases are hosted the error message from job history is not clear. I tried to run differential backup for that database and got error ‘The transaction log for database is full due to replication’. Just checked the disk space of the log file and could see it has only 10 mb free in drive. I just checked log_reuse_wait_desc it says Replication, I am sure that none of our SQL instances are having replication. Strange that we have some CDC been enabled and for CDC it is reporting the log_reuse_wait_desc as replication. USE…

  • DBA

    Remove Trim last character of string in the column – SP_helpdb

    The requirement is load sp_helpdb for all databases in the server, about 100+ serves to check database size quickly (sp_helpdb will hold both used and unused). Use CMS registered server, copy the data and paste in excel, import the excel file into a table. (OR) use BCP command.   sp_helpdb create table tbl_sp_helpdb ( server_name varchar (100),name varchar(100),Size_in_mb varchar(100),owner varchar(100), dbid int,created datetime, status varchar(100), compatibility_level int ) -- Import the excel select * from tbl_sp_helpdb order by Size_in_mb alter table tbl_sp_helpdb alter column Size_in_mb numeric --Error converting data type nvarchar to numeric. select left (Size_in_mb,LEN(Size_in_mb) -2) as Size_in_mb,* from tbl_sp_helpdb order by Size_in_mb update tbl_sp_helpdb set Size_in_mb =left (Size_in_mb,LEN(Size_in_mb)…

  • DBA

    Database suspect mode SQL server

    Database suspect mode One of my database went into suspect mode, it is not a critical one and small sized database. There was no backup 🙂 How to recovery the suspect database?   Paul Randal’s post https://www.sqlskills.com/blogs/paul/creating-detaching-re-attaching-and-fixing-a-suspect-database/ If you don’t have any backups, then the only way to get into the database is to use EMERGENCY mode. This lets you into the database but you need to be aware that recovery has not completed so the contents of the database are transactionally (and possibly structurally) inconsistent. I’m going to choose to repair the database using EMERGENCY-mode repair. See CHECKDB From Every Angle: EMERGENCY mode repair – the very, very last…

  • AlwaysON,  DBA

    AlwaysON SQL server database blocking SSISDB Active Operations

    AlwaysON SQL server database blocking I had two blocking case on one of my alwaysON databases. Backup is third party tool and it got blocked by each other sessions. SSIS package deployed in SSIS folder and it run long and had blocking as well.   The first one, tried to kill the session ID, it went in to rollback state and it took more than half day, no response. Fix: Just suspend the database in the primary AG this will remove the killed SPID. The second one, we can look into the Active Operations of packages. Integration service catalog – right click SSISDB – Active Operations  

  • DBA

    No sysadmin access Microsoft SQL server error 18461

    Login failed for user reason single user mode only one administrator can connect at this time Microsoft SQL server error 18461   You might get a situation that no sysadmin access to SQL server and server installed by someone. Breaking SQL server and adding SA sysadmin of yourself by SQLCMD. You should be OS administrator.   First find out ‘Binn’ folder where it is installed C or D drive. Better search in my computer. Open a CMD with administrator Type: cd /d E:\Program Files\Microsoft SQL Server\130\Tools\Binn (OR) 1.Stop the SQL server service 2.Start the service in Single user mode by adding ‘;-mSQLCMD’ to the parameters from SQL server configuration manager…