Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server Blog Forum

My name is Muthukkumaran Kaliyamoorthy and I am living in India (Chennai). I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server, and I’m specialized in Administration and Performance tuning.

AlwaysON database not synchronizing mode SQL service account change

AlwaysON database not synchronizing mode SQL service account change

Suddenly, one of our alwaysON server databases are went not synchronizing mode.

Errors from the log

The availability group database “DB” is changing roles from “RESOLVING” to “PRIMARY” because the mirroring session or availability group failed over due to role synchronization.… Read more

0 comments, 117 reads

Posted in SQL Server Blog Forum on 24 November 2016

AlwaysON database not synchronizing suspect mode

 

I got a call the database, not online/available mode, we suspect there was a corruption. One of my AlwaysON secondary database went suspect mode, it’s because of the log file and drive was full. I tried to resume the database. ALTER DATABASE dbname SET HADR RESUME;.

It went “in… Read more

0 comments, 375 reads

Posted in SQL Server Blog Forum on 17 September 2016

TempDB database is Full and Optimization

What is TempDB and best practice for TempDB

TempDB is the system database and it is per instance. It is a common and shared by all other databases. All the temporary activities are done here and yes, definitely the TempDB will become full and occupy more space depends on the… Read more

3 comments, 644 reads

Posted in SQL Server Blog Forum on 13 September 2016

How to solve the LSN mismatch in SQL server

 

There are many times, we face the LSN mismatch issue in alwaysON and other HA technologies. It is a bit hard to find the missing transaction log backup to apply. Since, there are hundreds of thousands log generated, depends on the transaction log frequency and it can be run… Read more

3 comments, 423 reads

Posted in SQL Server Blog Forum on 31 August 2016

AlwaysON database NOT SYNCHRONIZED and RECOVERY PENDING

AlwaysON database NOT SYNCHRONIZED and RECOVERY PENDING

 

I have recently come across an issue that, one of my alwaysON secondary replica databases are went into “NOT SYNCHRONIZED and RECOVERY PENDING” state. It is a geo cluster alwaysON with 4+2 nodes configured in both synchronous and asynchronous mode. 

The issue… Read more

0 comments, 1,446 reads

Posted in SQL Server Blog Forum on 6 August 2016

AlwaysON move database without breaking HADR

Move database without breaking alwaysON

 

This post is going to show the database movement from one drive to another drive, without breaking the database from alwaysON configuration. An application has created the many databases to both primary and secondary replica servers to the default location of C drive.

There are… Read more

6 comments, 1,700 reads

Posted in SQL Server Blog Forum on 3 August 2016

Performance Tuning Series – Main Part

 

Performance tuning is a tricky part. We need to analysis all points of view. Sometimes it is an easy fix and can be solved in a minute and sometimes it take a day or more to find and fix.

I received many emails for performance tuning help. It has… Read more

2 comments, 549 reads

Posted in SQL Server Blog Forum on 18 July 2016

Whoisactive capturing a SQL server queries Performance Tuning

 

Whoisactive stored procedure is a powerful script to capture the SQL server queries with tons of information. It’s written by Adam Machanic. It makes DBAs job easier.

It’s a great script with lots of parameter and I am going to show, which are all more important.

Run the stored… Read more

13 comments, 2,347 reads

Posted in SQL Server Blog Forum on 18 July 2016

How important is clustered index for a table

 

I am not sure, how to name this topic. May be, start by following questions.

 

How important and effective is clustered index for a table?

How to solve the heap fragmentation?

Why my query is running slow most of the time for this particular table?

Are the primary… Read more

1 comments, 1,209 reads

Posted in SQL Server Blog Forum on 9 March 2016

Lost Administrator SA password SQL server 2012

There are many blog posts talking about, how to recover “SysAdmin” password in case if it’s lost OR the server is new to you and no idea that has own and access to it. In my case the second option J

In my opinion, it’s very easy to work on… Read more

10 comments, 10,232 reads

Posted in SQL Server Blog Forum on 27 July 2014

An application error occurred on the server running on SQL server

Recently one of my application website went down.

 

I checked all the basic connectivity troubleshooting and seem everything was looking and working fine. Finally, I found the problem with the browser service but that’s also in running state.

Error from the event viewer:

 

 

The quick solution is… Read more

0 comments, 614 reads

Posted in SQL Server Blog Forum on 25 November 2013

Create and setup a SQL clustering on your Desktop or Laptop

How to create and setup a SQL clustering on your Desktop or Laptop

 

I thought to write this post after I discussed in a forum. SQL clustering is a bit complicated one. I mean most of the DBAs are not configured or tested it, because we don’t get… Read more

14 comments, 1,466 reads

Posted in SQL Server Blog Forum on 26 September 2012

Login failed for [SQLSTATE 28000] (Error 18456) the step failed

Login failed for [SQLSTATE 28000] (Error 18456) the step failed

 

When you query the data from another SQL server (Linked server) and SQL server & agent service is run as a domain user. You may get the error following error message in your job history.

Error:

Executed as user:… Read more

1 comments, 7,908 reads

Posted in SQL Server Blog Forum on 18 June 2012

Find the database free space sql server The transaction log for database is full

The transaction log for database is full. To find out why space in the log cannot be reused

OR

Could not allocate space for object because the filegroup is full

Reema:

Hello DBA it seems our application not working today. We got some error it looks like some problem with… Read more

0 comments, 4,050 reads

Posted in SQL Server Blog Forum on 23 May 2012

SQL server failed to start the system cannot find the path specified

Recently one of our SQL server unable to start it. The problem with the storage LUNs.

 

 

Things you have to do: When you get this above message while starting SQL server service.

Check the event viewer both system and application log. There you will know the exact problem. Read more

0 comments, 2,034 reads

Posted in SQL Server Blog Forum on 9 April 2012

Find the latest databse backup SQL server

Use the simplest script to find the latest database backup and the location.

 

SELECT b.server_name ,b.database_name,b.user_name, f.physical_device_name,
b.backup_finish_date,b.backup_size /1024/1024 AS size_MB,b.type,b.recovery_model,
b.has_bulk_logged_data,b.is_copy_only,f.mirror
FROM MSDB.DBO.BACKUPMEDIAFAMILY F
JOIN MSDB.DBO.BACKUPSET B
ON (f.media_set_id=b.media_set_id)
WHERE database_name='test'
--AND B.type='L'
ORDER BY b.backup_finish_date DESC

 

You can add more conditions on where clause http://msdn.microsoft.com/en-us/library/ms186299.aspx. Read more

2 comments, 603 reads

Posted in SQL Server Blog Forum on 5 March 2012

SQL server the timeout period elapsed prior to completion of the operation

SQL server clustering the timeout period elapsed prior to completion of the operation

Recently I faced one of our application could not connect. It throws an error ” Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. (Microsoft SQL Server, Error:… Read more

3 comments, 15,356 reads

Posted in SQL Server Blog Forum on 22 January 2012

SQL Server 2012 licensing details

Microsoft’s SQL Server 2012 (AKA SQL Server code-name ‘Denali’), new details have been released http://www.microsoft.com/sqlserver/en/us/future-editions/sql2012-licensing.aspx.

 

1.      What is new in SQL Server 2012?

SQL Server 2012 will provide Mission Critical Confidence with greater uptime, blazing-fast performance and enhanced security for mission critical workloads; Breakthrough Insight  with managed self-service…

Read more

2 comments, 2,833 reads

Posted in SQL Server Blog Forum on 24 November 2011

SQL server could not start cannot find object or property (0×80092004)

SQL server could not start cannot find object or property (0×80092004)

Recently I got a call from my team the users couldn’t connect the SQL server and it’s throwing error. As always, I checked the SQL server status via configuration manager and noticed the server 2008 r2 stopped.  I tried… Read more

0 comments, 1,617 reads

Posted in SQL Server Blog Forum on 7 November 2011

AWE has removed from SQL server 2012

AWE has removed from SQL server 2012

AWE has removed from SQL server Denali onwards. Say bye bye to 32 bit servers. To use more than 4GB memory you have to buy 64 bit servers.

 

A small organization may suffer because of this feature has removed from the next… Read more

4 comments, 747 reads

Posted in SQL Server Blog Forum on 18 October 2011

Older posts