Monitor User Connections in SQL Server

This article explores the causes and remedies for applications failing due to too many open connections to SQL Server.

Read the full article at http://www.sql-server-performance.com/articles/dev/monitor_user_connections_sql_server_p1.aspx

Happy Birth Day, Windows

Twenty-five years ago, on Nov. 20, 1985, Microsoft introduced its first version of Windows to the world. Not many people outside the technical press or the tech industry took notice.

What's changed in Windows in the last 25 years? Plenty. In this image gallery, You can get an idea or revise your memories of Windows.

Visual Tour

Drop-Add-Drop-Add .. Column of SQL Server table

This error was encountered by one of our team mates. His task was to simply add three columns to the table and populate data on them. So he has written a rollback script to drop this column. While testing he executed these scripts few times and he noticed that it was failing at the 6th instance. He was able to recover this by doing a Index Rebuild.

I was trying to figure out by writing an sample script for this but took little while.

SET NOCOUNT ON
IF OBJECT_ID('dbo.InitTable') IS NOT NULL
DROP TABLE InitTable
CREATE
TABLE InitTable
(
ID Int identity (1,1) PRIMARY KEY CLUSTERED,
Data1 varchar(1000) NOT NULL,
Data2 varchar(1000) NOT NULL)

INSERT INTO InitTable
(Data1,Data2)
VALUES
( REPLICATE('A' ,1000),
REPLICATE('B' ,1000))

DECLARE @LOOP_COUNT INT
DECLARE @MAX_LOOPS INT

DECLARE @DYNAMIC_SQL VARCHAR(8000)
SET @LOOP_COUNT = 1
SET @MAX_LOOPS = 1500

WHILE @LOOP_COUNT <= @MAX_LOOPS BEGIN SET @DYNAMIC_SQL = 'ALTER TABLE dbo.InitTable ADD ' + 'Data3 [varchar](400) NULL, ' + 'Data4 [varchar](400) NULL, ' + 'Data5 [varchar](400) NULL ' EXECUTE (@DYNAMIC_SQL) SET @DYNAMIC_SQL = 'Update InitTable' + ' SET Data3 = Replicate(''A'',400) ' + ' ,Data4 = Replicate(''B'',400)' + ' ,data5 = Replicate(''C'',400)' EXECUTE (@DYNAMIC_SQL) Alter table InitTable Alter Column Data3 varchar(400) NOT NULL Alter table InitTable Alter Column Data4 varchar(400) NOT NULL Alter table InitTable Alter Column Data5 varchar(400) NOT NULL Alter table InitTable drop column Data3 Alter table InitTable drop column Data4 Alter table InitTable drop column Data5 SET @LOOP_COUNT = @LOOP_COUNT + 1 END

So in the sixth loop this script is failing and also I notice if I commentet out following lines, this script is not failing at 6th instance but at the 11th instance.

Alter table InitTable
Alter Column Data3 varchar(400) NOT NULL

Alter table InitTable
Alter Column Data4 varchar(400) NOT NULL

Alter table InitTable
Alter Column Data5 varchar(400) NOT NULL


Things not are not finished my friends.
For updating you can see I have used 400 charactors, if I increased it to 1000 it is failing at different point. Guess what, if it is 1000 it is not failing at 6th but at 9th. Why why why? know whos, I gave it up.

Denali Arrives

Microsoft is making available for download on November 9 the first test version of its next-generation SQL Server release, code-named “Denali.”

Denali will be focused on providing users with more high-availability, self-service and BI functionality.

Here’s a list of some of what is on tap to be included in Denali:

•SQL Server AlwaysOn
a new high-availability “solution that will deliver “increased application availability, lower TCO (total cost of ownership) and ease of use, according to the Softies
•Project codename “Apollo”
new column-store database technology aiming to provide greater query performance
•Project codename “Juneau”
a single development environment for developing database, business intelligence (BI) and web solutions
•Project codename “Crescent”
a web-based, data visualization and presentation solution, and follow-on to the PowerPivot technology that is part of SQL Server 2008 R2
•SQL Server Data Quality Services (based on technology from Microsoft’s 2008 Zoomix acquisition)

In addition to making the first Denali CTP available, Microsoft also is making available today a first CTP of the SQL Azure Reporting Services and the SQL Azure Dat Sync Service

Microsoft also launched a first beta today of a new service, codenamed “Atlanta.” Atlanta is a configuration monitoring cloud service that aims to help customers to reduce downtime and improve the performance of Microsoft SQL server deployments. The service monitors the configuration of SQL deployments to help database administrators proactively avoid configuration problems and to resolve identified issues.The target ship date for Atlanta is first half of calendar 2011.

more at here

SQL Server Masters Certification Goes Global

Well, I haven't posted any post about certification before this. That is simply because my lazyness to pass certificate. However, this time I thought of publishing this blog post on SQL Server Microsoft Certified Master (MCM).

The SQL Server MCM exam was previously available exclusively as part of a three-week training and certification program offered at the Microsoft campus in Redmond, Wash. Now candidates who wish to earn the certification will be able to choose from multiple testing locations all over the world and take the exam in a matter of hours rather than weeks.

As Field notes, the cost of the exam has also dropped significantly. Previously, the required three-week SQL Server 2008 MCM training session plus four exams necessary to earn the certification cost candidates approximately US$18,500 — in addition to associated travel expenses. Now, candidates can earn the certification by passing just two exams: the four-hour Knowledge Exam, and a six-hour hands-on Lab Exam, which will be available in early 2011.

Read more at here

SQL Server Security Concerns

A survey of SQL Server pros highlights the challenges posed to database security by such factors as insider threats, human error and poor patch deployment.

Only a third of SQL Server professionals polled in a recent survey say that personal identity information, such as Social Security and credit-card numbers, are encrypted in all of their databases. Another 25 percent say they aren't using encryption to protect the data at all.

Among its findings: While 20 percent of respondents say a data breach in their organization is either "inevitable" or "somewhat likely" during the next 12 months, a full two thirds describe such an event as "highly unlikely" or "somewhat unlikely."

Read the full survey report here

Top 10 Secrets of a SQL Server Expert

Here are the top 10 ways a SQL Server DBA can take control of his environment and reduce the overall potential for crises to occur.

10. Take Inventory
9. Standardize Configurations
8. Understand the I/O Subsystem
7. Create a Customized Maintenance Plan
6. Ensure the Security of Your System
5. Get on Good Terms with Your Developers
4. Develop a Comprehensive Disaster Recovery Strategy
3. Take and Test Regular Backups
2. Monitor and Maintain Performance
1. Know Where to Find Information

Read the article here

SQL Server Management Service (SSMS) of 2008 R2 is crashing when editing steps in SQL Server Agent Jobs

SQL Server Management Service (SSMS) of 2008 R2 is crashing when editing steps in SQL Server Agent Jobs
When editing/viewing steps in SQL Server Agent Jobs in SQL Server 2008 R2, SOMETIMES SSMS crashes with following error message.

------------------------------

Creating an instance of the COM component with CLSID {AA40D1D6-CAEF-4A56-B9BB-D0D3DC976BA2} from the IClassFactory failed due to the following error: c001f011. (Microsoft.SqlServer.ManagedDTS)

------------------------------
ADDITIONAL INFORMATION:

Creating an instance of the COM component with CLSID {AA40D1D6-CAEF-4A56-B9BB-D0D3DC976BA2} from the IClassFactory failed due to the following error: c001f011. (Microsoft.SqlServer.ManagedDTS)

------------------------------
BUTTONS:

OK
------------------------------


http://connect.microsoft.com/SQLServer/feedback/details/557402/ssms-can-no-longer-create-or-edit-job-steps



Temporary work round would be to close SSMS and start it again. However, this is bug exists with SQL Server 2008 R2 and you need to install CU3 for this. When the service pack for SQL Server 2008 R2 is released you can use that service pack to resolve the issue.