SQL How To – Page 2 – TroubleshootingSQL

How to find out top version store generating objects

I recently found the need to write a query to find out which tables are contributing to version store usage on the server, then you can use the following piece of T-SQL:

EXEC sp_MSforeachdb 'IF ((select DB_ID(''?'')) in (select distinct database_id from sys.dm_tran_top_version_generators)) begin print ''Database Name :'' + ''?'' + char(13) select a.*,object_name(b.object_id) as TableName from sys.dm_tran_top_version_generators a inner join ?.sys.partitions b on a.rowset_id = b.partition_id end'

The reason this got so convoluted is because the Object_Name function doesn’t accept a partition ID or a allocation hobt ID. So, I have to get the object ID from sys.partitions for the associated database and then use the object_name function.

If anyone has an alternative quicker way, then please feel to leave a comment.

October 8, 2010

How to move data files of the mirror database

The ideal scenario for database mirroring would be to have the principal and secondary server instances be exact clones of each other from a hardware and disk space standpoint. This would ensure that the performance on the mirror instance is the same as the principal instance. But due to multiple constraints, this is not always possible. I recently had a question on how to move a secondary data file to a different physical location on the mirror instance. The catch here is that the physical data file locations on the principal and mirror instances are not the same. Now this can be tricky if not attempted or tested in your environment. The Microsoft SQLCAT(Twitter|Blog) team has a blog post on how to move the transaction log of a mirrored database to a secondary location. We shall use the same logic for the data file as well.

Steps

If the server on which the data file relocation has to be done is not the mirror server currently, then failover the mirrored database so that the server on which the OS File has to be physically moved is the current Mirror.
Change to High Performance mode if running under High Safety.
Execute the ALTER DATABASE statement to move the data file to the new location on the mirror instance.
Pause Mirroring to prevent any automatic failover while OS File Copy is happening.
Stop the Mirror instance.
Copy the file across to the new location (OS File Copy).
Start the Mirror Instance.
Resume mirroring and switch back to High Safety if the mirroring was running under High Safety. The mirroring should continue without any issues. Failback to original server if needed.

If the data file locations are the same on the principal and mirror and the data files need to be moved to different locations on both the server instances, then this is much easier. You would need to fire the ALTER DATABASE command on the principal server instance and then move the data files to the new location after failing over the database. The new data file location would take effect only after the server instance is restarted.

Technorati Tags: Database Mirroring,Microsoft SQL Server

September 26, 2010

How to disable a SQL Agent job?

Recently I had a Tweep (read:Twitter users) asking about how to disable a SQL Agent job using the Hash Tag #sqlhelp. The information out there is sketchy but there is a MSDB stored procedure to accomplish this: sp_update_job.

You would need to first verify if this job is currently executing or not.

Use @enabled = 1 to enable the job and to disable the job use @enabled = 0.

Example: To disable a job if it is not currently executing


DECLARE @jobid uniqueidentifier
SET @jobid = '83CE6BD4-80BB-4294-A06E-89BBC2AB8101' -- replace with appropriate job id
CREATE TABLE #tblJobInfo
(job_id                UNIQUEIDENTIFIER NOT NULL,
                            last_run_date         INT              NOT NULL,
                            last_run_time         INT              NOT NULL,
                            next_run_date         INT              NOT NULL,
                            next_run_time         INT              NOT NULL,
                            next_run_schedule_id  INT              NOT NULL,
                            requested_to_run      INT              NOT NULL, -- BOOL
                            request_source        INT              NOT NULL,
                            request_source_id     sysname          COLLATE database_default NULL,
                            running               INT              NOT NULL, -- BOOL
                            current_step          INT              NOT NULL,
                            current_retry_attempt INT              NOT NULL,
                            job_state             INT              NOT NULL
)

INSERT INTO #tblJobInfo
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1,SUSER_SNAME,@jobid

IF ((select [job_state] from #tblJobInfo where Job_ID = @jobid) = 4)
BEGIN
    EXEC msdb..sp_update_job @job_id = @jobid,@enabled = 0
END
DROP TABLE #tblJobInfo

Please note that the above XSP used is undocumented and the script itself will not be supported by CSS in case you run into any issues. This is an AS-IS posting and please test the same in your environment before using it.

Reference:

BOL Topic: sp_update_job

Monitoring Job Activity: http://msdn.microsoft.com/en-us/library/ms188272.aspx

September 9, 2010

Did you know about the –m startup parameter

I am sure –m is startup parameter that most number of DBAs have used at one point of time or the other during a disaster recovery scenario involving an issue with system databases or even while moving system database files.

There is a hidden gem about the –m startup parameter which most of us are not aware of or do not use often when required. That is the Client Application Name filter along with the –m parameter. I have gotten frustrated multiple times when I started the SQL instance in single user mode and a sysadmin connection latched onto the only connection (read: SQL Performance monitoring app, CRM apps, or any other 24X7 LOB apps).

If you want to start SQL Server in Single User mode using –m startup parameter, then you can now prevent other applications from connecting to the instance by specifying the restricted Client Application Name which can only connect to the instance. Note that this is a case sensitive parameter. Before specifying the Client Application Name filter, check what the program_name is showing your application name in the sys.dm_exec_sessions DMV output.

If you are doing this for SQL instance maintenance operations, then specify the filter as “SQLCMD” or “Microsoft SQL Server Management Studio – Query” as you should be able to run your scripts from either of the two applications SQLCMD or SSMS.

No more tearing your hair out to figure out who took up the sole connection to the SQL instance when you don’t want that lone wolf to be in demand by other applications.

July 8, 2010

How to generate an Automatic Failover for a Mirrored Database

I have heard this question multiple times:

How do I generate a condition which will cause an Automatic Failover of a SQL Server Mirrored database?

First of all, you would need to setup mirroring under Full Safety mode with a Witness instance.

The only condition that triggers an automatic database failover would be if the Mirror and Witness instance are unable to connect to the Principal server instance.

Figure: Synchronized database mirroring session

As per the TechNet article, Automatic Failover would be under the following condition:

When safety is FULL, if neither the mirror nor the witness can see the principal, the mirror server can form a quorum with the witness and change its role to become a new principal server, assuming a the mirroring session was SYNCHRONIZED when the principal went away.

So, the condition above is self explanatory.

How can this be done?

1. If your Witness and Mirror are on different boxes or on the same box and the Principal instance is on a different box. Then:

Stop the Database Mirroring endpoint using the following command on the Principal instance: ALTER ENDPOINT <mirroring endpoint name> STATE = STOPPED. Note: This would put the database mirroring into a disconnected state. To bring database mirroring out of disconnected state, you need to start the endpoint using the following command: ALTER ENDPOINT <mirroring endpoint name> STATE = STARTED
Plug out the network cable for the Principal box provided the Principal and (Mirror & Witness) instances are on two different boxes.
Block all TCP traffic on the port that the server has established the Mirroring session.
Pause the Principal Instance service (Have not tried this one yet but theoretically it should work!)

2. Attach a Visual Studio Debugger or Windows Debugger to the Principal Server instance. Do not hit GO to continue the execution of the process being debugged.

THE ABOVE METHODS SHOULD ONLY BE TRIED ON A TEST ENVIRONMENT. THESE ARE NOT SOMETHING THAT SHOULD BE ATTEMTPED ON A PRODUCTION BOX.

This is just an informational article for simulating automatic database failover for testing any alert jobs or any other business logic that you need to kick off during an automatic failover.

What messages would be present in the SQL Server Errorlogs?

When the Automatic Failover is initiated, it would have the following messages printed in the SQL Server Errorlog of the Mirror instance:

2010-04-19 22:08:03.590 spid15s      Error: 1479, Severity: 16, State: 1.
2010-04-19 22:08:03.590 spid15s      The mirroring connection to “TCP://<FQDN>:5022” has timed out for database “dbMirror” after 10 seconds without a response. Check the service and network connections.
2010-04-19 22:08:03.590 spid15s      Database mirroring is inactive for database ‘dbMirror’. This is an informational message only. No user action is required.
2010-04-19 22:08:03.590 spid15s      The mirrored database “dbMirror” is changing roles from “MIRROR” to “PRINCIPAL” due to Auto Failover.
2010-04-19 22:08:03.720 spid15s      Recovery is writing a checkpoint in database ‘dbMirror’ (15). This is an informational message only. No user action is required.

The Witness instance would have the following messages in the Errorlog:

2010-04-19 22:08:03.360 spid25s Error: 1479, Severity: 16, State: 1.
2010-04-19 22:08:03.360 spid25s The mirroring connection to “TCP://<FQDN>:5022” has timed out for database “dbMirror” after 10 seconds without a response. Check the service and network connections.

On the Principal server, the system event logs might report that the SQL Server service is unresponsive based on the Service Timeout value configured on the machine (Default is 30 seconds):

Log Name:      System
Source:        Service Control Manager
Date:          4/19/2010 10:04:02 PM
Event ID:      7011
Task Category: None
Level:         Error
Keywords:      Classic
Description:
A timeout (30000 milliseconds) was reached while waiting for a transaction response from the MSSQLSERVER service.

After the Principal instance comes back online, the following messages would be printed in it’s Errorlog:

2010-04-19 22:28:19.190 spid26s      Error: 1474, Severity: 16, State: 1.
2010-04-19 22:28:19.190 spid26s      Database mirroring connection error 4 ‘An error occurred while receiving data: ’64(The specified network name is no longer available.)’.’ for ‘TCP://<FQDN>:5023’.
2010-04-19 22:28:19.200 spid27s      Error: 1474, Severity: 16, State: 1.
2010-04-19 22:28:19.200 spid27s      Database mirroring connection error 4 ‘An error occurred while receiving data: ’64(The specified network name is no longer available.)’.’ for ‘TCP://<FQDN>:5024’.
2010-04-19 22:28:19.360 spid20s      Database mirroring is inactive for database ‘dbMirror’. This is an informational message only. No user action is required.
2010-04-19 22:28:19.490 spid35s      Starting up database ‘dbMirror’.
2010-04-19 22:28:19.530 spid20s      The mirrored database “dbMirror” is changing roles from “PRINCIPAL” to “MIRROR” due to Role Syncronization.
2010-04-19 22:28:19.590 spid35s      Bypassing recovery for database ‘dbMirror’ because it is marked as a mirror database, which cannot be recovered. This is an informational message only. No user action is required.
2010-04-19 22:28:19.810 spid20s      Database mirroring is active with database ‘dbMirror’ as the mirror copy. This is an informational message only. No user action is required.
2010-04-19 22:28:45.440 spid20s      Database mirroring is inactive for database ‘dbMirror’. This is an informational message only. No user action is required.

If you notice above, you will see that even though the Principal was not available during the failover, it assumed the role of the Mirror after it came back online. This Role Synchronization is what keeps the Principal and the Mirror database status updated to prevent Split Brain scenarios.

References:

SQL Server Best Practices Article

Database Mirroring Best Practices and Performance

April 20, 2010

Category: SQL How To

How to find out top version store generating objects

Share this post:

How to move data files of the mirror database

Share this post:

How to disable a SQL Agent job?

Share this post:

Did you know about the –m startup parameter

Share this post:

How to generate an Automatic Failover for a Mirrored Database

Share this post: