Dedicated Administrator Connection (DAC)

SQLCMD is particularly useful for creating batch scripting jobs for administrative purposes. SQLCMD has replace OSQL. It is using OLEDB to connect the SQL Server. However, as an emergency utility to diagnose and hopefully correct server problems, it has no peer. By Using the –A argument, the SQLCMD utilizes an exclusive connection to SQL Server. If no other connection is possible, the SQLCMD –A command is the last and best hope for diagnosing server problems and preventing data loss. By default, only local DACs are allowed because the DAC components only listen on the loopback connection. However, remote DACs can be enabled using the sp_configure stored procedure by changing the remote admin connections option to true, as the following code illustrates:

sp_configure ‘remote admin connections’, 1
RECONFIGURE

SQL Server Full Backup Internally

Q. What are the steps SQL Server performs internally at the time of FULL backup?
Ans. SQL Server follow the below steps once you execute the BACKUP command
1.Backup Process will lock the database and block all the transaction
2.Place a mark in the transaction log
3.Release the database lock
4.Extract all the pages in the data files and write them to the backup device
5.Lock the database and block all the transactions
6.Place a mark in the transaction log
7.Release the database lock
8.Extract the portion of the log between the marks and append it to backup

Q. Which operations do not allowed during the full backup?
1.Adding and removing database files
2.Shrinking the database

Monitor, Start and Stop services using xp_servicecontrol

Please read the below article for more detail.

http://www.mssqltips.com/tip.asp?tip=2036

Split-Brain/tiebreaker in Cluster

HA clusters usually use a heartbeat private network connection which is used to monitor the health and status of each node in the cluster. One difficult, but serious condition every clustering software must be able to handle is split-brain. Split-brain occurs when all of the private links go down simultaneously, but the cluster nodes are still running. If that happens, each node in the cluster may mistakenly decide that every other node has gone down and attempt to start services that other nodes are still running. Having duplicate instances of services may cause data corruption on the shared storage.

It's also used as a tiebreaker when nodes can no longer communicate (that is, are "split-brain"). When it cannot communicate with the nodes, Cluster Service cannot really detect the problem: It's possible that the nodes are dead, but it may also be possible that just the communication links are. In this situation, to prevent each node from thinking that it is the sole survivor and bringing your database online, they go into arbitration, using the quorum resource.

The node that owns the quorum resource puts a reservation on the device every three seconds; this guarantees that the second node cannot write to the quorum resource. When the second node determines that it cannot communicate with the quorum-owning node and wants to grab the quorum, it first puts a reset on the bus.

The reset breaks the reservation, waits for about 10 seconds to give the first node time to renew its reservation at least twice, and then tries to put a reservation on the quorum for the second node. If the second node's reservation succeeds, it means that the first node failed to renew the reservation. And the only reason for the failure to renew is because the node is dead. At this point, the second node can take over the quorum resource and restart all the resources.

Reference: http://technet.microsoft.com/en-us/library/bb742593.aspx

Sign Up Procedure for Interview Agent

Interview Agent

[caption id="attachment_616" align="aligncenter" width="600" caption="Call on +91-9881710142 for more information"][/caption]

SQLDBPool has now its own domain

Dear Readers,

Now you dont have to write the long http://sqldbpool.wordpress.com/ URL, We have now our own domain. You can now check your favourite articles by just typing http://sqldbpool.com/

Thank You

Best Regards,
Jugal Shah
jugal.shah@sqldbpool.com

Error: 1101, Severity: 17, State: 2.

Error: 1101, Severity: 17, State: 2.
Could not allocate a new page for database 'tempdb' because of insufficient disk space in filegroup 'PRIMARY'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

We have configured the tempdb on J:\ which has only 10GB space. TempDB has occupied the 10GB space. We have tried to shrink the tempdb but no luck, after sometime we are not able to even right click the TempDB and neither execute the some system stored procedures (sp_who2)

Solution 1
If you don’t want to restart the SQL Server you can add the another data file to different drive in tempdb

ALTER DATABASE tempdb
ADD FILE
(
NAME = tempdev1,
FILENAME = 'E:\tempdb1.mdf',
SIZE = 5MB,
MAXSIZE = 5000MB,
FILEGROWTH = 10MB
) TO FILEGROUP [PRIMARY];
GO

Solution 2
If the disk space is available on the drive where tempdb residing you can change file MAX SIZE option to allocate more space.

Solution 3
Restart the SQL Services, it will create the new instance of tempdb

Solution 4
For user databases with restricted growth you can use the AUTO GROWTH option true or increase the file max size

Note: You can't move the tempdb files, if you execute the ALTER command to move the tempdb files, it will just mark the file to diffrent drive and will create the file the target drive on SQL Server restart only

How to move data and log file using Alter statement

You can use Alter command to move data and log file.

Sample Script

-- Moving data file to E:\ drive
ALTER DATABASE tempdb
MODIFY FILE (Name = tempdev,FILENAME = 'E:\tempdb.mdf')
-- Moving log file to E:\ drive
ALTER DATABASE tempdb
MODIFY FILE (Name = templog,FILENAME = 'E:\templog.ldf')