Welcome to my World of SQL

As the market share of SQL Server grows over time, there has been a increasing demand for SQL Server performance tuning. There has been various approaches taken by different teams and individu…

SQL Server is well known for being a memory hog. When installed, the default configuration allocates a ridiculous amount of memory (2147483647 MB) to the instance. In effect, this grants SQL Server a…

I always set this value to CHECKSUM. When CHECKSUM is enabled for the PAGE_VERIFY database option, the SQL Server Database Engine calculates a checksum over the contents of the whole page, and stores…

In SQL Server versions before SQL 2012 single page allocations and multi-Page allocations are handled by different components, the Single Page Allocator (which is responsible for Buffer Pool allocati…

Some body have this error : Could not generate mail report.An exception occurred while executing a Transact-SQL statement or batch.No global profile is configured. Specify a profile name in the @prof…

Here are the main differences you should know about these three date types:   Range of Dates Accuracy Size usage …

This is one of the issues the consultant faces, when there client has little documentation on the systems and the databases they use.  The first step in this process is simply looking at the date &am…

To understand what this is doing, first we need to understand what Nul is, in the context of a file. It is not just a misspelling of NULL. DOS, since the earliest days, has had a number of devices …

Most of the novice dba’s forget to take the transaction log backups, which eventually ends up consuming a lot of disk space. This is one of the questions people ask me very often. Here are the steps …

In SQL Server versions before SQL 2012 single page allocations and multi-Page allocations are handled by different components, the Single Page Allocator (which is responsible for Buffer Pool allocati…

I saw many people asking this question over and over again and today, I’d like to share with you a backdoor to SQL Server 2012 which would help you gain SYSADMIN access to your production SQL Se…

I recently installed SQL server 2014 on my machine; I was using the SSMS for SQL 2012 as it as all the pluggins installed for my daily use.  Now for testing an application, i have restored a database…

I got this  error message when I was trying to enable CDC on a SQL Server  database for “ Msg 22830, Level 16, State 1, Procedure sp_cdc_enable_db_internal, Line 193 Could not update the meta…

Below is the query to list the users in a specific role.  if you are looking for the users who can manage the sql server agent jobs, use the filter ( Where rp.name = ‘SQLAgentRole’)  and run this…

The following logon trigger will restrict a user from connecting thru SQL Server management studio. As always, test this first before implementing. This will deny all the connections made by the us…

I was writing another article on the smallest dataset possible in sql server, and i came across a situation where I need to use DBCC PAGE () statement for a table. DBCC PAGE () statement, uses data…

Statistics are very important for sql server engine,  it is  used by the SQL Server optimizer to choose the most efficient plan.  When we don’t have up to date statistics it may end with SQL serve…

Since SQL Server 2012 , you can find this information in the SQL Server Error Logs. Before SQL Server 2008 R2 there used to be some properties maintained  but is now unused Mode of this instance …

Today I was working on updating operator emails on SQL Server and when i ran the script  it gave me given below error. SQL Server blocked access to procedure 'dbo.sp_update_operator' of c…

Here is the script that will help you the database backup and restore histories SELECT TOP 10 b.database_name , BMF.physical_device_name as BackupFileName , B.backup_f…