Full-Text Search is disabled

I recently upgraded one of my SQL Server 2014 instance to SQL Server 2017 and applied CU5. Very recently I installed Full Text Feature and instance is enabled for Full Text. Database is enabled  for Full Text. Now when I am trying to create Full-Text Index from SSMS on one of my table , Full-Text Index  […]

Change Tracking Internals (SQL2008R2) Part 4

Change Tracking Overhead When change tracking is enabled for a table, some administration operations are affected. The following table lists the operations and the effects you should consider. Operation When change tracking is enabled DROP TABLE All change tracking information for the dropped table is removed. ALTER TABLE DROP CONSTRAINT An attempt to drop the […]

Perform full database backup without breaking the backup chain

In today’s time every organization retains a daily full database backup copy on tape. A DBA also make sure that he has full database backup and transactional log backup files to perform a point in time / Full recovery. When there is a major Change Deployment happens on the database a full database backup is […]

Database (Full, Diff & Transaction Log) Backup List

I thought of writing about the subject after visiting a customer. It is really important to have the list of fullback and transaction log back sequentially performed. When it comes to Recovery I have seen most of the DBA running queries on msdb database and struggling to get the list of Backups and sequential Transaction […]

How to manually generate cluster Log on Win 2008

So far I have seen cluster logging is not enabled by default and it really makes difficult to troubleshoot cluster issues; to troubleshoot the issue I have to manually generate the log for command prompt. Here we will learn how to generate the cluster log from command prompt. Steps Open the command with administrator privileges. […]

Running Ad Hoc Queries on Remote SQL Server without Linked Server

How to run Ad Hoc T-SQL Queries between SQL Server Instances (on Local or Remote Location) without Linked Server.   To execute Ad Hoc T-SQL you need to enable ‘Ad Hoc Distributed Queries’ server option from sp_configure.   sp_configure ‘Ad Hoc Distributed Queries’,1 reconfigure with override     TO access remote data from an OLE […]

How Bulk-Logged Recovery Model Works

    It’s always been suggested / recommended to use Bulk-Logged Recovery model for Bulk Operation. But do we really know how Bulk-Logged Recovery model Works and what the risk associated to it. Bulk Recovery model improves the performance of Certain Large Scale Bulk Operations; Such as Large BCP File (IN), Database Maintenance (Index Maintenance, […]

How to Backup Database in Multiple Files

As the subject goes lot of DBA think why to do database backup in multiple files; what’s the use and in what scenario this will be applicable; well if you have a huge database and the backup file is huge (ex: +500 GB) and you have to copy the same on different location for Replication, […]

How to get Day/Week/Month/Year/DayOfYear/WeekDay/Hour/Min/Sec .etc from DateTime

It’s always been tricky for T-SQL Developer to Datepart the Date. With DATENAME Function it has become easy and manageable. DATENAME can be used for below Datepart YEAR QUARTER MONTH DAYOFYEAR DAY WEEK WEEKDAY HOUR MINUTE SECOND MILLISECOND MICROSECOND NANOSECOND TZOFFSET   SELECT convert(datetime,convert(varchar(10),GETDATE(),101)) Date_As, Day(GETDATE())Day_As,MONTH(GETDATE()) Month_As,YEAR(GETDATE())Year_As, DATENAME(DW,GETDATE()) DayName_As, DATENAME(MONTH,GETDATE())MonthName_As,DATENAME(QUARTER,GETDATE()) QuarterNo_As     Examples   […]

Restore LiteSpeed Backup from Local and Remote Location

Restoring LiteSpeed Backup from Local and Remote Location.   –Read the Backup file—- exec master.dbo.xp_restore_filelistonly   @filename = ‘Server nameDrive$restoreDatabaseName_Format.LSB or BAK’ —Restore the Database exec master.dbo.xp_restore_database @database=‘DataabseName_NewDatabase’ , @filename = ‘Server nameDrive$restoreDatabaseName_Format.LSB or BAK’ , @with = ‘move “Logical Filename” to “LocationDataFileName.mdf”‘ , @with = ‘move “Logical Filename” to “LocationLogFile.ldf”‘ , @with = ‘recovery’ […]

Create Indexed Views

An indexed view is a view that has been materialized. This means it has been computed and stored. Indexed views dramatically improve the performance of some types of queries. Indexed views work best for queries that aggregate many rows. They are not well-suited for underlying data sets that are frequently updated Create the unique clustered […]