The following script will help to do a quick health check of SQL estate that you manage. To check all SQL server health check, you need to create Registered or Centralized server. I usually automated everything using my AutoMon script package that will trigger an email from CMS server. But, some small clients and where I do not have long term scope, I use Registered Server and will run various script than the health check like – DB list, Login list & agent job list etc. -- 1) Disk Free Space Check XP_FIXEDDRIVES -- 2) DB Online Status (Including SQL 2000) SELECT NAME,DATABASEPROPERTYEX (NAME,'STATUS') AS STATUS, GETDATE() as [Today_Date]…
-
-
The operation cannot be performed on a database with database snapshots or active DBCC replicas
Background of the issue. There is a script stored procedure for both backup and restore is running on one of the server. I have got this from one of our customer. I have checked the restore and looks like there was a database snapshot been created that was a part of backup stored procedure. Msg 5094, Level 16, State 2, Line 1 The operation cannot be performed on a database with database snapshots or active DBCC replicas. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally. Solution: Drop the database snapshot and restore the database. Drop database <Snapshot DB Name> T-SQL to find the database snapshot…
-
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…
-
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)…
-
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…