Update Without Where Clause

Update Without Where Clause

Update Without Where Clause

Running Update Without Where Clause might causes a data damage when it is with the critical table that creates a lot of problems and escalations. Recently while working with one of the customer we had a situation where a DBA executed the release script on production and the script contains an update statement without where clause. He immediately realized that he executed an update statement without where clause and he reported us explaining the situation. There were few things we found:

  • The change request implemented on a premium database
  • Update statement provided from Pre-Prod team which is not having any control on that:
    No Where clause
    No Transaction control – Commit / Rollback
    No Validation on update – Validating and Reporting
  • As per the business requirement single record should be updated but in real it updates all records in that table.
  • Usually when a DBA implements a CR on prod server a backup or snapshot has to be taken prior to implementing the change. Since it was a small database (6 GB) he did take a full backup before implementing the CR.

Solution we have provided:

Continue reading

Posted in SQL Development, SQL Server DBA | Tagged , , , , , , , , , , , , | Leave a comment

Script to get row count for all tables in a SQL Server database

Script to get row count for all tables in a SQL Server database

Script to get row count for all tables in a SQL Server database

This post helps you in writing a Script to get row count for all tables in a SQL Server database. For any database developer or administrator the most common requirement is to identifying the larger tables based on data size, index size and row count. This script can quickly list out all tables in the given database and retrieves the below details:

  • Name: Table Name
  • Rows: Total row count
  • Reserved: Space reserved for the table – MB
  • Data: Total space allocated for data pages – MB
  • Index_size: Total space allocated for Index pages – MB
  • Unused: Unused space – MB

Here is the script to get row count for all tables in a SQL Server database:

When you need to get the list of tables along with the row counts, connect to the database instance open a new query window, paste the above script and execute it. It shouldn’t take much time to execute the script as we are using “sp_spaceused”, for us it took maximum 6 sec on a 3.5 TB database.

Remember there are chances where sp_spaced might give the wrong row counts due to several reasons. There are mainly 3 reasons that cause sp_spaceused gives us the wrong counts:

  • When there are huge DDL changes
  • Statistics are not updated
  • Index maintenance is not being taken care

Here you will find more details and resolution.

Posted in SQL Development, SQL Scripts, SQL Server DBA | Tagged , , , | 2 Comments

New Features Added in SQL Server

New Features Added in SQL Server

New Features Added in SQL Server

This post helps you in quickly reviewing the “New Features Added in SQL Server” from 2008 to 2016. The most common interview question is “What are the new features added in SQL Server XXXX?” To simplify the answer we are just giving the single line abbreviations. While preparing for an interview just have a quick look and try to remember 4 to 5 features on current working and last released version. Let’s say you are currently working on 2012 then you need to look for 2012 and 2008 R2.   Also people would expect you to know few new features added in the latest version which is SQL Server 2016. Features are categorised for DBA, Developer and Security. That doesn’t mean that SQL Developer need not look into DBA section or vice versa. This is just to make it more readable, when a feature is more related to Developer we added in Developer section when a feature is more relevant administration part it will be in DBA section. Believe this will be helpful for a quick review for version wise feature support.

 

New Features Added in SQL Server 2008

 

SQL DBA:

Activity Monitor: Great tool to showcase resource utilization and performance using GUI.

Policy Based Management: The ability to manage standards on multiple servers

Enhanced Database Mirroring: Automatic data page repair and compressing outgoing log stream

Resource Governor: We can configure it to control SQL Server resource utilization and workload.

External Key Management: Provides a comprehensive solution for encryption and key management.

Hot Add CPU: Adding resources online without downtime.

PowerShell: SQL Server 2008 ships with PowerShell snap-in built on .Net framework 2.0.

Table Compression: Compress data and index pages to save memory and I/O.

Backup Compression: Native backup compression.

Performance data collection: Centralized data repository for storing and reporting performance data

Extended Events: Event collection is easier now compares to running a trace

 

SQL Developer:

Continue reading

Posted in Interview Q&A, SQL Development, SQL Server DBA | Tagged , , , , , , , | 4 Comments

Microsoft Acquiring LinkedIn

Microsoft Acquiring LinkedIn

Microsoft Acquiring LinkedIn

I am very excited to see the news “Microsoft Acquiring LinkedIn” on Monday morning. I have been working on Microsoft products from last 10 years and I love LinkedIn. “Satya Nadella” (the man of inspiration who made Indians proud especially “Telugu” people) announced the news on one of the video from Microsoft website.

Satya Nadella” has made a first biggest deal as the Microsoft CEO. I believe this is the fantastic step by Microsoft and this is a big deal as Microsoft will be acquiring the world’s largest and the biggest professional social network LinkedIn for $26.2 billion, $196 per share.

Satya Nadella said

“The LinkedIn team has grown a fantastic business cantered on connecting the world’s professionals, I have always had a great admiration for LinkedIn, I have been talking with Reid and Jeff for a while … I have been thinking about this for a long time.”

By this deal Microsoft targeted Organization Growth by integrating Microsoft Office Business suite users with LinkedIn and Targeted advertising.

Jeff Weiner will continue as a CEO for LinkedIn and reports to Satya Nadella. LinkedIn shares surged 47 percent after the announcement on Monday to near $193, Microsoft’s stock was down 3.2 percent.

The deal has been approved by boards from both the companies and it is expected to be closed by end of 2016.

Microsoft Acquiring LinkedIn

Posted in Miscellaneous | Tagged , , | 1 Comment

Search for an Object in SQL Server

Search for an Object in SQL Server

This article helps you in understanding how to Search for an object in SQL Server using T-SQL script. When we need to search for an object inside a database we can do that using sys.objects but when it comes to cross database search that needs a solution. There are different ways to search for an object in SQL Server:

  • T-SQL Script: A native T-SQL script.
  • Object Explorer Search: User required proper permissions to use this
  • Red Gate SQL Search: It’s not always possible installing freeware on enterprise environments
  • SSMS Tools Pack Add-in: An extra tool has to be added to SSMS

Why Instance level object search is required?

There are few cases where we search for objects across the instance.

Continue reading

Posted in SQL Development, SQL Scripts, SQL Server DBA | Tagged , , , , , , , , , | 4 Comments

Script to Monitor SQL Server Memory Usage

Script to Monitor SQL Server Memory Usage

Script to Monitor SQL Server Memory Usage

Introduction:

This post will takes you through the T-SQL Script to monitor SQL Server Memory Usage. In previous blog post we have explained the parameters involved in understanding sql server memory usage. There are total 7 scripts to monitor SQL Server Memory Usage.

  • Buffer Pool Usage
  • System Memory Information
  • SQL Server Process Memory Usage Information
  • Buffer Usage by Database
  • Object Wise Buffer Usage
  • Top 25 Costliest Stored Procedures – Logical Reads
  • Top Performance Counters

Script to Monitor SQL Server Memory Usage: Buffer Pool Usage

Continue reading

Posted in Interview Q&A, Performance Tuning, SQL Scripts, SQL Server DBA | Tagged , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , | 4 Comments