Skip to content

Archive for August, 2011

Aug 27

Dedicated Administrator Connection (DAC)

Some times we are not able to connect to SQL Server database instance because of huge blocking, etc and the only option available for us, as a DBA is to try connecting to the server using dedicated admin connection (DAC). DAC allows database administrators to connect to the instance when database engine is running abnormally and is not responding to any connection requests. Using this connection you can run SQL queries and other diagnostic commands to find and resolve the issue.

From where can I connect using DAC?

By default you can connect using DAC only from the system where SQL Server is installed. To check if DAC is allowed from outside the box, run the below query: -

sp_configure ‘remote admin connections’

If run_value is ‘0’ that mean it’s not allowed to connect using DAC from outside the box. We can always change this to accept DAC connections from outside the box by running: -

sp_configure ‘remote admin connections’,1

For clustered environments, it is always recommended to enable remote admin connections. Changes takes effect immediately, no SQL Server services restart is required.

How to make dedicated admin connection?

We can connect as using DAC only from sqlcmd or query analyzer. If we are trying to connect using sqlcmd, then we can connect using –A parameter at the end like: -

sqlcmd –S Servername –U sa -P password –A

And if we are connecting from query analyzer, then we need to type ‘admin:’ before the instance name like: -

admin:servername\instancename

Which port number does DAC use?

By default dedicated admin connection always listen on loop-back IP 127.0.0.1 at port 1434 for making connection.

If multiple database instances are installed on the server and port number 1434 is used by some other instance, then SQL Server will dynamically assign port for DAC during instance startup.  The port number assigned to DAC can be found by looking at the SQL Server log entry. Entry will be like: -

Date  8/11/2011 8:08:08 PM
Log  SQL Server (Current – 8/24/2011 10:47:00 AM)

Source  Server

Message
Dedicated admin connection support was established for listening locally on port 3357.


Date  8/11/2011 8:08:08 PM
Log  SQL Server (Current – 8/24/2011 10:47:00 AM)

Source  Server

Message
Server is listening on [ 127.0.0.1 <ipv4> 3357].

Aug 26

Configuring firewall settings for SQL Server on Windows 2008 R2 Server

Configuring firewall settings for SQL Server installed on Microsoft Windows server 2008 R2 is quite different from how we do the configuration/open ports on Microsoft Windows 2008 R2 server.

Here we will be sharing details on how to configure firewall settings for SQL Server on windows 2008 R2 in full details, step by step in next couple of article.

In next couple of articles, we will publish below articles: -

1)   Configuring firewall settings for SQL Server Engine on Windows 2008 R2

2)   Configuring firewall settings for SQL Browser on Windows 2008 R2

3)   Configuring firewall settings for SQL Server Integration Services (SSIS) on Windows 2008 R2

4)   Configuring firewall settings for SQL Server Analysis Service (SSAS) on Windows 2008 R2

5)   Configuring firewall settings for SQL Server Reporting Services (SSRS) on Windows 2008 R2

Aug 25

Database Backups

Mapping database backup with the backup location:-

select BS.database_name,BS.backup_finish_date BackupDateTime, BS.backup_size/1024/1024 SizeInMB, BMF.physical_device_name

from dbo.backupset BS, dbo.backupmediafamily BMF

where BS.media_set_id=BMF.media_set_id and BS.type=’D’

–BS.type= D means full backup

order by BS.database_name, BS.backup_finish_date

We can use the above script for multiple purpose, like:-

1) It can be used to check backup details foe full backup, incremental backup, transaction backup, etc. Just use the appropriate backup type. Just replace BS.type with -

D = Database

I = Differential database

L = Log

F = File or filegroup

G =Differential file

P = Partial

Q = Differential partial

2) It can be helpful, when we are not able to apply t-log backup during recovery, because some one took additional t-log backup at some other location.

3) If we do not maintain database growth then we can get the backup size of the database in history and can calculate the approximate database growth.

4) We can get lots of information regarding database backups by querying dbo.backupmediafamily and dbo.backupset tables in msdb database.

Aug 24

1 Comment

Sending email from SQL Server agent Job using operators

In this article, we will talk about sending email from SQL Server agent jobs by making use of operator and database mail feature. Before we can continue, make sure database mail is already configured. If database mail is not configured, please configure this feature by visiting here.

Below are the steps to be followed to send email from SQL Server agent Jobs: -

1) Connect to the database instance from SQL Server Management Studio and navigate to SQL Server Agent à Operator. Right click on Operator and click on ‘New Operator’

2) In this new window, provide the new operator name that you want to create. Also, provide the email address, which should appear as sender name, when email is sent to recipients. Avoid using net send and pager, as these features will be removed from future release.

3) Here in the Notification tab, you can select the appropriate options if you want to get notified for any alerts. Else click ok to save this new operator.

4) Now navigate to Jobs and go to the properties of SQL Server agent job for which you want to get notified via email.

5) Here go to the Notification tab and then select the check box against e-mail. Then select the operator we have created in previous step. You can also opt for sending this email when job completes or it runs successfully or it fails. Chose the appropriate option as per your requirement and click OK.

6) Just to verify, you can go back to Operators and select the properties of the Operator that we have used in above job.

Here go to notification tab and you will see that Jobs radio button is now enabled. When you select this radio button, you can see all the jobs, which are using this operator.

Also, you can go to the history tab of this operator to check when was the last time, email was sent by this operator.

7) If you have multiple database mails configured on this server and want to make sure that the operators to send email for SQL Server agent jobs use correct profile. Then navigate to SQL Server agent and go to the properties of SQL Server agent.

Here go to the Alert System tab and make sure that Enable mail profile option is enabled. Against mail system, select Database mail and against Mail profile, select the database mail profile that is to be used. Then click OK. SQL Server agent is to be restarted in order for above changes to take effect.

Now making use of the database mail profile selected above will send all emails from SQL Server agent operator.

Aug 23

2 Comments

How frequently is database growing?

We always keep our database data and log file to auto grow by some value. But are we sure that the current auto growth parameters is correct. Is the auto growth happening to frequently? Or we have allocated more than sufficient space in one go that database have not grown for a year.

Auto growing data files very frequently with small increments is bad for database as well as OS. When ever auto growth happens database takes a performance hit. At OS level, this new chunk of space is not always adjacent to the previous space allocated to databases. Thus causing disk fragmentation.

So, how to determine, how frequently our database auto growth is happening?

Make use of below script to check how frequently the data and log file is growing.

DECLARE @path NVARCHAR(1000)

SELECT @path = Substring(PATH, 1, Len(PATH) – Charindex(‘\’, Reverse(PATH))) + ‘\log.trc’

FROM   sys.traces

WHERE  id = 1

– id =1 means the active trace file

SELECT databasename,

       te.name   AS eventname,

       starttime

FROM   ::fn_trace_gettable(@path, 0)

       INNER JOIN sys.trace_events te

         ON eventclass = trace_event_id

       INNER JOIN sys.trace_categories AS cat

         ON te.category_id = cat.category_id

WHERE  te.name IN( ‘Data File Auto Grow’, ‘Log File Auto Grow’ )

ORDER  BY databasename,starttime DESC;

Output will be the database name, start time when auto growth of data or log file happened. Now we can check, if auto growth is happening too frequently, we can increase the growth size.

« Older Entries