Copying Database using AS COPY OF in SQL Azure

Copying databases from one server to the other has become the easiest with SQL Azure. Now you can copy the database simply using the CREATE DATABASE statement in SQL Azure. CREATE DATABASE command using AS COPY OF in SQL Azure creates a snapshot of the source database as of the time of the copy request. You can select the same server or a different server, its service tier and compute size, or a different compute size within the same service tier (edition). After... Read more

Check if PowerShell is Installed on SQL Server

Many database related tasks can be automated using PowerShell. This T-SQL script can be used to check the following: Is PowerShell installed on your local SQL Server? Is it enabled for script execution? What is the version of PowerShell installed on your machine? You can obtain all the above information using the below T-SQL script. Since we have to obtain information from the server registry and from the output of PowerShell.exe file, we will be using both... Read more

Server side lightweight SQL Server tracing

There are times when you would want to capture database activity and the most popular tool that database engineers use is SQL Server Profiler. Note: SQL Server profiler has been deprecated and it is recommended to use Extended Events for tracing any database related activity. However, in this article, we will look into how we can use SQL Server profiler without the need to run from the SQL Server Profiler tool and how we can minimize the performance impact by... Read more

T-SQL Script to find High CPU Queries

There are times when you would want to know what query in SQL Server may be causing CPU spikes. There are many reason why a query may hit the CPU and the best way to troubleshoot it to: Identify the query that is causing high CPU Identify the total time taken by query to execute that query Find out when was the last time the query was executed Obtain Query execution plan for the offending query There are many scripts available on the internet and many-a-times that... Read more

How to restore damaged pages from a backup

Are your database pages corrupt or damaged? Now you can restore damaged pages without impacting the availability of the database. SQL Server provides an extension to the RESTORE command that can help restore damaged pages from the backups. (Note: Only the portion that is being restored will be unavailable) Important things to Keep in Mind: You cannot restore log pages You cannot restore GAM, SGAM and PFS pages. You cannot restore Page 0 of all data files (Page 0... Read more

Why do Queries behave differently in production

You may have experienced something similar where the query or a stored procedure performs bad when deployed in the production and the same query / stored procedure works perfectly well in the test or development environment. Behind the scenes (Query Internals) It is possible for the query or the stored procedure to behave differently in lower environments than Production if you have not cleaned up the query execution plan cache when performing tests. Internally,... Read more

How to Recover Data from a Corrupt Database

Remember those days when the database would go in to suspect mode and you had to perform various steps to recover the database by putting the database in the emergency mode and then extracting the data out of that emergency database? Here’s a flash back on how we used to do in the past: Enable modifications to system catalogs. Change the status of the database in SysDatabases system object to 32768 Restart SQL Server services (This would put the database in... Read more

Improve Performance of SQL Server File Operations

Ever wondered why SQL Server takes long time to perform below operations? Create a database Expand data files for additional growth Automatic data file growth due to auto-grow settings Restoring a database, etc. Today we will be looking in to a powerful and yet not very popular feature that many DBAs can benefit from called SQL-WIFI (Windows Instant File Initialization). What Happens Internally: Every time SQL Server needs space for data or log file for any... Read more

Why to Optimize Ad-Hoc Workload in SQL Server

By default, when a query is executed in SQL Server, it compiles and generates an execution plan for that query and stores that plan in the plan cache. Now if this happens to be a one-off/ad-hoc query that will never be run again, then the newly generated plan is wasted and its space that is being used in the plan cache is also wasted. This means, the buffer pool now contains compiled plan of queries that will never be executed. Now imagine if you have hundreds of... Read more

Automatic database backups in Azure Cloud

Backups are extremely important for the continuity of business. Many companies fire their engineers just because they didnt have proper backups in place. Wouldnt it be great if there was a way that DBAs and engineers didnt have to worry about backups and SQL Server was smart enough to perform the backup based on workload pattern (data changes) or based on some defined recovery interval and the backups are auto-magically stored offsite without having to worry about... Read more

Hacking SA Password in SQL Server

You are a proud and trusted DBA of your organization who is responsible for maintaining and managing SQL Servers in the production environment. To prevent any unauthorized access to your production environment, you have decided to perform the following steps that are kind of best practices to secure your companys SQL Servers from any unauthorized access: You have removed any and all built-in administrators account from SQL Server logins You have removed all users... Read more

Failure inserting into the read-only column

At times when transferring data from one database to other using either Import Export wizard or using SQL Server Integration Services, you may encounter the following error: Failure inserting into the read-only column .(SQL Server Import and Export Wizard) This happens when the source table has an identity column which is auto-generated at source and also at the destination. Since it’s auto-generated by system, import/export can’t overwrite identity... Read more

T-SQL Script to Update Statistics

Index Statistics are important for query optimizer to generate optimal query plans. If indexes have outdated or stale statistics, your query’s performance will suffer. It is important to update the statistics to make sure query optimizer always generates the best and optimal plan. This script allows you to report and update outdated statistics. In reporting mode, this script provides the following information: 1. Name of the Database 2. Table Name 3. Index... Read more

T-SQL Script to Attach and Detach Databases

DBAs and Developers at times may need to detach and attach the databases for various reasons including the following: 1. Moving files to other drives 2. Server migration 2. Upgrades, 3. system maintenance, etc. This script takes away the headache of remembering the filename or folder location of every single data or log file for every single user databases on your server. It allows you to generate script to detach and attach one or all databases avoiding the need... Read more

T-SQL Script to Check Database Compatibility

Setting the database to the right compatibility level is very important as it can have negative impact on the performance and behavior of your databases. I’ve seen many customers upgrade their SQL Servers from older versions to newer but forget to upgrade the compatibility of their databases. This means that even though you have an upgraded version of SQL Server, your database cannot benefit from any new or enhanced features of SQL Server since your database... Read more

T-SQL Script to Obtain Database Growth Information

DBA’s and Developers often have a need to query the current state of database file size and growth information. This script allows you to obtain file-growth information from single or all databases. It provides the following information: 1. Local Server Name 2. Database Name 3. Logical File Name 4. Initial File Size 5. File Growth Value/Percentage 6. Any Growth Restrictions Please run the SPROC without any parameters to obtain help on how to execute this... Read more

EXECUTE Query AT Remote Server

We use EXEC or EXECUTE command almost every day of our database life. Its a command that allows us execute Stored Procedures (extended, system, user, CLR, etc.)  and Functions. However, EXEC command also has a powerful feature that allows you to run any command remotely. Not many developers/DBA’s are aware of this feature. Historically, EXEC / EXECUTE executed commands locally, meaning if you have a stored procedure, you can only run that on your local... Read more

Running Antivirus on Production SQL Server

Running Antivirus software is critical part of server security but it is also important to understand the impact and effect it has on SQL Server. Imagine this: Youve installed Anti-Virus on a server that already has SQL Server running, once the server reboots, it is highly likely that the Anti-virus software depending upon the scanning schedule will scan the SQL Server folders and lock SQL Server files before SQL Server can gain access to those files. When... Read more

T-SQL Query to find CPU and NUMA Nodes

I’m often asked on how to identify the number of CPU’s (physical) and Numa Nodes (logical CPU’s) from SQL Server. Here’s a quick and easy way to obtain such information. Run this query on SQL Server Instance you want to get data from SELECT (cpu_count / hyperthread_ratio) AS Number_of_PhysicalCPUs, CPU_Count AS Number_of_LogicalCPUs FROM sys.dm_os_sys_info 1234 SELECT   (cpu_count / hyperthread_ratio) AS... Read more

Use LOGON Triggers to Audit or Control Users

Ever felt the need for knowing who is logging on to your SQL Server and at what time? Ever felt the need to restrict specific users for certain time-period or firing a trace to track down user activity? Ever felt like limiting the number of concurrent connections for specific users? Well, you can do all that now with Logon Triggers. Logon Trigger allows you to fire a T-SQL, or a stored procedure in response to a LOGON event. You may use logon trigger to audit and... Read more