SQL Server General DBA

How to Differentiate System and User Databases In SQL Server

This question is frequently asked in many forums and the most popular answer is to use the DB_ID() function as shown below. So if you want to get only the system databases you can use DB_ID(databasename) < 5 in your where clause. This work around is used since there is no dedicated column in sys.databases […]

Drop a Database by Closing Existing Connections using SSMS or T-SQL

To drop a SQL Server database, you will need exclusive access to the database ensure there are no other current users of the database or you will encounter the error: Drop failed for Database ‘dbName’ …. Cannot drop database because it is currently in use Ensuring there are no other current users can be very […]

Configuring Email in SQL Server 2000

A couple of days back I was asked to configured database email for a DB Server which was hosted on a SQL Server 2000 environment. Configuring a database email in SQL Server 2000 is somewhat different than the SQL Server 2005 environment as there is no GUI available in SQL Server 2000. The following steps […]

Altering a Column Which has a Default Constraint

Setting up a default column is a common task for  developers.  But, are we naming those default constraints explicitly? In the below  table creation, for the column, sys_DateTime the default value Getdate() will be allocated. CREATE TABLE SampleTable (ID int identity(1,1), Sys_DateTime Datetime DEFAULT getdate() ) We can check the relevant information from the system […]

Troubleshooting High-CPU Utilization for SQL Server

The objective of this FAQ is to outline the basic steps in troubleshooting high CPU utilization on  a server hosting a SQL Server instance. The first and the most common step if you suspect high CPU utilization (or are alerted for it) is to login to the physical server and check the Windows Task Manager. […]

invoke-sqlcmd Not Recognized

When attempting to run the invoke_sqlcmd from PowerShell you may receive the below exception: The term ‘invoke-sqlcmd’ is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again. The Invoke-sqlcmd […]

Automate Generate Scripts in SSMS

The SSMS allows you to manually generate scripts for a database by right-clicking a database and selected Generate Scripts and selecting what elements you wish to be scripted. However, suppose you do wish do manually perform this task, how can it be automated? Actually there is no way to automate it from SSMS, currently the […]

Invoke-Sqlcmd vs sqlcmd utility

Invoke-Sqlcmd can run many of the scripts which can be run with the sqlcmd utility. However, Invoke-Sqlcmd runs in the PowerShell environment which is different than the command prompt environment sqlcmd runs in. Invoke-Sqlcmd has been modified to work in a PowerShell environment. Not all the sqlcmd commands can be used in Invoke-Sqlcmd. Commands which […]

Change Select Top 1000 Rows value in SSMS

One seemingly minor change to SQL Server Management Studio (SSMS) in SQL Server 2008 is that by default it will only allow the visually select the top 1000 rows . This is primary to save on memory which can be drained by loading numerous rows into the SSMS visual editor. If, however, you don’t want […]

Change The SQL Server Port Number

By default the default instance of SQL Server is configured to listen on TCP port 1433 (named instances listen on dynamic ports so they select an available port when SQL Server service starts). To change the TCP port that SQL Server listens on, first go to SQL Server Configuration Manager expand SQL Server 2008 Network […]
Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |