Denali – Day 2: AlwaysON High Availability (aka Project HADRON )

AlwaysON Availability Group:

is another very important achievement done by development team project called “HADRON”. As the name implies AlwaysON is a High availability feature on Failure Clustering where it has option to make Secondary server as “Read-Only” purpose. Read more

Denali — Day 1: Tempdb Compatibility – Bug?

I have installed sql server 2012 express and evolution edition on my personal laptop, and observed that the compatibility of databases is 110 (11) for Denali.

And till now when you change the compatibility of “model” database the compatibility of “Tempdb”… Read more

When your service account is expired/disabled or is not having access than sql service could not able to start and you may get an error an en eventvwr as

“Could not open error log file ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG’. Operating system error = 5(Access is denied.).

so you just change… Read more

Denali

Microsoft has launches sql server 2012 aka Denali on March, 2012. Free Express and evolution edition for the same is also available now.

http://www.microsoft.com/sqlserver/en/us/default.aspx

After Sql server 2008 (compatibility 10) and sql server 2010 /sql server 2008 R2 (compatibility 10.5) another major release from Microsoft sql server is… Read more

This is a first blog for this month, yes I was busy with Powershell quiz and some other personal stuff.

Okay, continue with Miscellaneous Info
here is some more important stuff/ information about sql server.

>>Ghost cleanup task runs every 5minuts (in 2005) and runs every 10minuts in (2008… Read more

For sql server 2005 and above, When you take a full backup of the FULL recovered database and try to restore earlier backup it may fail with following error

Msg 3159, Level 16, State 1, Line 1

The tail of the log for the database “restoretest” has not been backed… Read more

Sometimes on a heavy loaded server(many databases/connection). You may get an error log when your backup maintenance plan job gets fails with the following error

Message:

Executed as user: xxx\SYSTEM. Microsoft (R) SQL Server Execute Package Utility Version 10.0.5500.0 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:… Read more

When you try to connect to sql server using isql/osql or sqlcmd there are two authentication you can use windows authentication and sql authentication.

-E option is used for windows authentication where you domain windows login which you have connected to will be used(your windows credential).

Eg. Domainname/loginname

There is… Read more

This is very important to understand what is high availability 9′s, This is a measurement of high availability  where you can tell how much downtime is feasible for your organization for a Year(annually).

the calculation is as follows

=Total Hours in a Year-(Total Hours in a Year* # of 9′s)/100 Read more

for sql server 2000 (I know its old) desktop edition does not includes any GUI. so installation of desktop edition ie MSDE is via command prompt.

you can install the setup for desktop edition for free from here.

http://www.microsoft.com/download/en/details.aspx?id=22661

to install, just download the exe. and extract it.

setup.exe /settings… Read more

Yesterday we were having discussion on high availability feature Mirroring and Log shipping with my colleagues (Amol & Alankar) on whether can we dynamically add/drop any new file in our principle or primary database, got conclusion that YES it is possible to the same will be affects on the mirrored… Read more

Find Login in AD

Many times we require to know if particular login is a part of ad user group. Or sometimes we need to know a AD group and wanted to know who all logins are part of it.

There are many different ways to achieve this.

1. I follow…

Read more

*This is related to physical file size of a MDF or LDF file not internal allocation.

One of our user asked that the ldf file is not changing, on checking the last modified date time, user was asking this.

Its a good question, the we have to educate to user… Read more

Good Bye 2011

Hey there, this year is going to an end and just want to recall what all happened this year in my life both personally (cannot disclose much J) and professionally.

The good news about this year is on 24^th Dec 2011, we blessed a second… Read more

Basics QA on Extended Event

I am learning Extended Event so this is my first blog on extended event, There is a great whitepaper on XEvent by Jonathan Kehayias.

Q1. What is extended event?

A1. It is a light weighted profiler kind of trace introduced on sql… Read more

Suspect database generally caused by internal IO issue, and the best way to resolve it to ask hardware person to check and correct the issue. And sql server dba has to monitor it.

Check the database consistence using

DBCC CHECKDB (‘YourDBname’) WITH NO_INFOMSGS, ALL_ERRORMSGS

And try to understand the messages. Read more

We come across a situation where we have to kill one spid which was running for quite some time, so we though killing will help us, but after killing found that that spid is not killed but stuck in our processes spid. And we could not able to kill even Read more

As I did for powershell a day Series Blog April 2011, consolidated the all months blog into one pdf file here. I have done this month as well, I Blog about Troubleshooting sql server issues -Errors , which we get during our day to day activity.

you can download… Read more

Day 30 More Fatal Errors and BUGs

1. For sql server 2000 and earlier you may get 5242 and 5243 corruption in the record  structure(for sql server 2005 onwards you may get 824).
   

Msg 5242, Level 22, State 1, Line 1

An inconsistency was detected during an internal operation in database… Read more

Description:-

Yesterday, we discussed what happen when tempdb data file grown very high, now what if log file of any user database has grown high and occupying most of its space. Please see the great blog post to know about what is log and how log file works here by… Read more