-->
SQL Clone
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.

Move database between drives

Advertisements

SQL server Move user database steps and scripts:

Check you have a free space on the moving drive and make sure the SQL service account has read and write permission. (You can check the account name in configuration manager)

Move TempDB database

--========================= for tempdb

-- note the existing…

Read more

0 comments, 173 reads

Posted in SQL Server Blog Forum on 21 January 2019

Msg 8909, Level 16, State 1, Line 1 Table error: Object ID index ID partition ID

Advertisements

 

Being a DBA , you will see database corruptions in your environments. I got four or five and not affected much of the data. Most of the time logical corruption and memory errors. The bellow one I got sometimes back and the worst part is backup also corrupted… Read more

0 comments, 232 reads

Posted in SQL Server Blog Forum on 8 January 2019

SQL server change single user mode or backup or recovery model of all database sql script

Advertisements

Following is the DBA script which generally used for most cases the action needed for all the databases like migration, upgrade etc.

Change the database recovery model and set to single_user mode.

Backup of all databases.

-- Dynamic SQL

select 'alter database ['+name+'] set single_user with rollback immediate' ,…

Read more

0 comments, 212 reads

Posted in SQL Server Blog Forum on 27 December 2018

Test connection failed because of an error in initializing provider. Oracle client and networking components were not found

Advertisements

You may experience the following error. When a developer installed oracle client and tested the visual studio BIDS packages ask you to fix this. We know the MS only has a visual studio BIDS 32 bit.

Recently, It asked in the forum, I had managed Oracle sometimes and had… Read more

6 comments, 129 reads

Posted in SQL Server Blog Forum on 15 December 2018

Setup Ola Hallengren SQL server Maintenance script

Advertisements

Ola Hallengren Maintenance Plan Deployment

Many of DBAs already started using the Ola hallengren’s maintenance plan script. It is a free script replacing traditional GUI maintenance plan. It is very good script for all type and variety of database, application and size.

He is a Microsoft award winner for… Read more

0 comments, 148 reads

Posted in SQL Server Blog Forum on 10 December 2018

FCB::Open failed: Could not open file number 2. OS error: 3(The system cannot find the path specified.).

Advertisements

Troubleshooting steps to do when we cannot start SQL service and receive this alert.

Run –> eventvwr –> windows log –> system and filter the errors and read one by one. You can get a hint that what is the real issue.

OR open the SQL server errorlog and… Read more

0 comments, 126 reads

Posted in SQL Server Blog Forum on 26 November 2018

Load SQL error log to a table

Advertisements

SQL server error log load to a table

There are cases, we need a SQL server error log load to a table for troubleshooting. Since there will be a more informational message and logon enabled etc.

Here is the script to load and read.

-- create table 

use dba…

Read more

0 comments, 205 reads

Posted in SQL Server Blog Forum on 16 November 2018

Drive space check SQL server steps and scripts

Advertisements

Following is the script used for drive space related issues.

--SQL 2000
-- select name,(size/128.0) as size_mb,* from master.dbo.sysaltfiles order by size_mb desc

-- To check the drive free space
xp_fixeddrives

------------------------------- get all the db file info & filter the drive, mdf/ldf
select db_name(a.database_id) as DBname,a.name as DBfile…

Read more

0 comments, 158 reads

Posted in SQL Server Blog Forum on 13 November 2018

Reading execution plan sql server use of Plan Explorer

Advertisements

Plan Explorer

I recently started using this. Since it was licensed earlier, now it’s free. This is an awesome tool for query performance tuning.

SQL Server Sentry Plan Explorer is the free tool given to SQL community you can download here https://www.sentryone.com/plan-explorer click download plan explorer button with right… Read more

0 comments, 36 reads

Posted in SQL Server Blog Forum on 1 November 2018

Test post

Test post

Read more

0 comments, 170 reads

Posted in SQL Server Blog Forum on 17 October 2018

Database Transaction log file full in the full recovery model

Advertisements

I am seeing in more environment the database recovery model is set FULL and there is no transaction log backup scheduled and the log files are growing bigger, until it reaches a file system space.

Me: Hello sir, May I know the reason, why we set the database in… Read more

0 comments, 229 reads

Posted in SQL Server Blog Forum on 16 October 2018

AlwaysON RECOVERY PENDING SQL Server Bugs Enhancement Requests T-SQL Tuesday

Advertisements

This month’s T-SQL Tuesday topic is “SQL Server Bugs & Enhancement Requests” and hosted by my favorite and inspirational Brent Ozar.

 

 

Here is my bug report:

https://connect.microsoft.com/SQLServer/feedback/details/3022019 and it is still in active.

It happen one of my production database, when I tried to remove the… Read more

0 comments, 518 reads

Posted in SQL Server Blog Forum on 10 January 2017

VLDB very large database DBCC checkDB

Advertisements

Database corruption – DBCC checkDB for Very large database

We know SQL server data is stored in a filesystem storage. There has been always an (I/O) input and output interaction between SQL server and storage subsystem both in the memory and disk. IO subsystem plays a major role, 99%… Read more

2 comments, 2,802 reads

Posted in SQL Server Blog Forum on 31 December 2016

AlwaysON database not synchronizing mode SQL service account change

Advertisements

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… Read more

0 comments, 988 reads

Posted in SQL Server Blog Forum on 24 November 2016

AlwaysON database not synchronizing suspect mode

Advertisements

 

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… Read more

1 comments, 5,630 reads

Posted in SQL Server Blog Forum on 17 September 2016

TempDB database is Full and Optimization

Advertisements

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… Read more

3 comments, 1,381 reads

Posted in SQL Server Blog Forum on 13 September 2016

How to solve the LSN mismatch in SQL server

Advertisements

 

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… Read more

6 comments, 4,202 reads

Posted in SQL Server Blog Forum on 31 August 2016

AlwaysON database NOT SYNCHRONIZED and RECOVERY PENDING

Advertisements

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… Read more

1 comments, 3,215 reads

Posted in SQL Server Blog Forum on 6 August 2016

AlwaysON move database without breaking HADR

Advertisements

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… Read more

8 comments, 4,377 reads

Posted in SQL Server Blog Forum on 3 August 2016

Performance Tuning Series – Main Part

Advertisements

 

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… Read more

2 comments, 1,550 reads

Posted in SQL Server Blog Forum on 18 July 2016

Older posts