SQL in Stockholm

SQL Server thoughts from the support team in Stockholm

SQL Server troubleshooting thoughts from Graham Kent, (ex) Senior Support Escalation Engineer at Microsoft Sweden.

  • SQL in Stockholm

    How to configure SQL Server database mirroring between 2 instances on a standalone server

    • 1 Comments

    There are lots of troubleshooting articles about how to diagnose connectivity problems when setting up database mirroring, especially errors such as these:

    Msg 1418, Level 16, State 1, Line 2

    The server network address "tcp://mirrortest.com:5023" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.

    or

    Msg 1456, Level 16, State 3, Line 1

    The ALTER DATABASE command could not be sent to the remote server instance

    but here's something a little different.

    Imagine that you have a server which is not connected to a domain. It might just be a standalone machine, it could be a virtual machine that you've just built. I've done this a few times in virtual environments to do demos on laptops and the like. It's a not a production scenario that you'd come across really, but it works really well in test.

    The questions that usually come up are how can I configure the partners (and endpoints) without having a FQDN, since I'm not connected to a domain, I'm just in "WORKGROUP". SQL Server still wants you to use FQDN formatting for the partner addresses. When you're trying to configure it, you might have even encountered an error like this when trying to start mirroring (depending on exactly how you did the setup)

    Database Mirroring login attempt failed with error: 'Connection handshake failed.

    There is no compatible authentication protocol. State 21

    What you need to do to be able to set this up is as follows:

    1. adding "local" as the primary DNS suffix as in detailed this article here (ok it's about exchange server but it works the same)

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

    (remember to reboot)

    2. You can now specify FQDN formatted names for the local machine in the format:

    For the principal:

    TCP://MIRRORTEST.LOCAL:5022

    For the mirror

    TCP://MIRRORTEST.LOCAL:5023

    (Or other port numbers of your choice, but remember that they'll need to be different)

    3. You'll need these ports as exceptions in Windows Firewall if you're running it (I was even though it was a local machine!)

    4. My endpoints are setup like this (but I just did them through the GUI) there's nothing exceptional here:

    CREATE ENDPOINT [Mirroring]

    AUTHORIZATION [MIRRORTEST\Administrator]

    STATE=STARTED

    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)

    FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = DISABLED)

    You can now run some cool tests and demos without ever being connected to a network.

  • SQL in Stockholm

    Database Mirroring Monitor can cause large tempdb growth in SQL Server 2005 - changed behavior in SQL Server 2008

    • 3 Comments

    On extremely rare occasions in SQL Server 2005, when the following conditions are true:

     

    1.       You have a database configured for database mirroring

    2.       You have replication configured on the same database

    3.       You are running the database mirroring monitor

    4.       The SQL Agent job created by the database mirroring monitor is enabled

     

    It was possible to encounter a situation where tempdb grew suddenly and significantly, leading to all the complications that such a situation causes, including but not limited to:

     

    Msg 1105, Level 17, State 2, Line 3

    Could not allocate space for object xxxxxxx in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

     

    Error: 17053, Severity: 16, State: 1.

    C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\tempdb.mdf: Operating system error 112(There is not enough space on the disk.) encountered.

     

    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.

     

    If you were able to capture this situation and have some monitoring running, you would notice the following event captured in profiler:

     

    An execution of the following which is the main entry point stored procedure for the mirroring monitor:

     

    exec sys.sp_dbmmonitorupdate @database_name

     

    followed by an execution of :

     

    exec @retcode = sys.sp_dbmmonitorMSgetthelatestlsn @database_name, @end_of_log_lsn output

     

    which was the last procedure executed by the mirroring monitor before tempdb started to grow. If you were to actually trace the details of this procedure, you could see the statements which it executes. What you will see is that last statement actually executed before tempdb starts filling is this:

     

    dbcc dbtable(blog_mirror) with tableresults, no_infomsgs

     

    Being that this procedure is actually held in the resource database (the others are in msdb so you can take a look easily if you want to), it is protected from normal viewing, so I won't reproduce it here, but it does exactly what it's name would imply, it gets the latest LSN and then uses this to do some calculations of amount of time required to synchronize and other such functions.

     

    The problem lies here in that in extremely rare occurrences this procedure can cause huge tempdb growth due to the way it calculates the latest LSN using dbcc dbtable (an undocumented internal DBCC command) and stores it's temporary calculations in a temp table. Unfortunately the only way to resolve this problem in SQL Server 2005 is to disable the mirroring monitor job in SQL Agent. You should only consider or need to do this though if you are definitely experiencing this specific problem, i.e. you have all the pre-requisite conditions and you have a profiler trace proving that the dbcc command was the command executed before tempdb filled up.

     

    However the good news is that in SQL Server 2008 we made a design change to collect this data in a far more efficient way. If you look at the results returned from a query of the sys.database_mirroring DMV in SQL 2008:

     

    select *

    from sys.database_mirroring

     

    you will note 2 columns have been added to the output:

     

    mirroring_end_of_log_lsn

    and

    mirroring_replication_lsn

     

    Meaning that the information that the mirroring monitor requires is now available in a much simpler format. Therefore if you look at the SQL 2008 version of the msdb stored procedure sys.sp_dbmmonitorupdate, you will note that the execution of sys.sp_dbmmonitorMSgetthelatestlsn has been replaced with a simple select from the new DMV columns:

     

    select @role = (mirroring_role - 1),

                      @status = mirroring_state,

                      @witness_status = mirroring_witness_state,

                      @failover_lsn = mirroring_failover_lsn,

                      @end_of_log_lsn = mirroring_end_of_log_lsn

    from sys.database_mirroring where database_id = @database_id

     

    which is both simpler, faster and avoids any possibility of the tempdb problem occurring.

     

  • SQL in Stockholm

    Interesting things to watch out for when using the autoclose database option

    • 3 Comments

    Here's something which I've been looking at this week, surrounding the autoclose database option.

    When you restore any database to a SQL Server 2005 SP2 or later instance, you'll more than likely get errors of this nature written to the log.

    2008-11-12 17:24:16.58 spid13s     SQL Server has encountered 5 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
    2008-11-12 17:24:16.58 spid13s     SQL Server has encountered 5 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
    2008-11-12 17:24:16.58 spid13s     SQL Server has encountered 5 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.

    This is expected behavior and is documented in Books Online

    "Restoring a database clears the plan cache for the instance of SQL Server. Clearing the plan cache causes a recompilation of all subsequent execution plans and can cause a sudden, temporary decrease in query performance. In SQL Server 2005 Service Pack 2, for each cleared cachestore in the plan cache, the SQL Server error log contains the following informational message: "SQL Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache) due to some database maintenance or reconfigure operations". This message is logged every five minutes as long as the cache is flushed within that time interval."

    I'd been looking at some problems which at first glance appeared to be related to this, in that after restoring a database, there were lots of occurances of cache flush which was causing performance problems.

    The answer in this case was that this database was in autoclose mode. A quick check of sp_dboption and this was confirmed. Another quick check to Books Online confirms that autoclose databases will cause a cache flush event when they shutdown.

    "When the database is set to AUTOCLOSE = ON, an operation that initiates an automatic database shutdown clears the plan cache for the instance of SQL Server. Clearing the plan cache causes a recompilation of all subsequent execution plans and can cause a sudden, temporary decrease in query performance. In SQL Server 2005 Service Pack 2, for each cleared cachestore in the plan cache, the SQL Server error log contains the following informational message: "SQL Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache) due to some database maintenance or reconfigure operations". This message is logged every five minutes as long as the cache is flushed within that time interval."

    I'd forgotten about this behavior, as its very rare that i deal with autoclose databases as most of the systems I tend to work with don't use it, autoclose is more designed for desktop systems as books online states.

    The act of the cache flush in itself is not particularly problematic, unless of course you constantly access the database and then disconnect from it, causing numerous SQL instance wide cache flushes, which severly impact performance on any server.

    Another indication of this type of behavior is that your error log will be full of lots of these messages

    2008-11-13 16:10:16.07 spid51      Starting up database 'test'.
    2008-11-13 16:10:16.22 spid51      CHECKDB for database 'test' finished without errors on 2008-11-12 16:11:55.453 (local time). This is an informational message only; no user action is required.
    2008-11-13 16:10:18.46 spid51      Starting up database 'test'.
    2008-11-13 16:10:18.50 spid51      CHECKDB for database 'test' finished without errors on 2008-11-12 16:11:55.453 (local time). This is an informational message only; no user action is required.

    The thing that I didn't realize and that really surprised me was that it was possible to create such flushes by just accessing the database through Management Studio. Basically if you were the only user on the database, and you decided to browse its contents in SSMS, you'd open a connection, and then every time that you expanded an object explorer tree control for example, you issue an query to the server to generate the list of objects in that part of the tree (this is easy enough to trace in profiler). However when such a query finishes if you are the only user then the database closes as it deems all active users to be disconnected, and therefore a cache flush event takes place. This of course is a bad thing, especially if you happen to restore your autoclose database to an instance which is running lots of other production databases maintaining large numbers of cache entries on your server.

    Just something to think about....

  • SQL in Stockholm

    Problems executing maintenance plans in SQL Server 2008

    • 3 Comments

    In the RTM build of SQL Server 2008 , if you install the tools in any edition, you are able to create maintenance plans. However depending upon your version and your extact installation components, when you execute the maintenance plan you created, you may receive the following message:

    Executed as user: <domain>\<user>. Microsoft (R) SQL Server Execute Package Utility  Version 10.0.1600.22 for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    The SQL Server Execute Package Utility requires Integration Services to be installed by one of these editions of SQL Server 2008: Standard, Enterprise, Developer, or Evaluation. To install Integration Services, run SQL Server Setup and select Integration Services.  The package execution failed.  The step failed.

    This is caused by the fact that you need to have the full SSIS component installed to execute the package that you have created. Many people will not come across this message as they have just installed the SSIS component by default, but you should consider the following scenario:

    If you are running a lower level edition, such as web or workgroup, then you cannot install SSIS as part of that setup and configuration, as it is not available in that edition. Therefore to be able to run the packages that you have created, you would, as the message says, need to install the component, but from a licensed edition that supports SSIS.

    If you are running Standard or Enterprise edition, you will need to re-run setup and add the SSIS component. This will allow you to run the maintenance plan.

    This behavior is by design as SSIS is only supported in the higher editions of SQL Server. However we understand that for some customers this is not the optimal type of behavior with regard to maintenance plans (this is a good example of Microsoft Connect feedback), and as such it has been logged and is under review by the development team.

  • SQL in Stockholm

    Want to read some tips and tricks from the SQL Developer support team?

    • 0 Comments

    Well if you do you should check out the blog of my colleague and Microsoft pool team partner Spike, who works in the SQL Support team here with me in Stockholm, but specialises in the area that we refer to as developer support. This is a very broad area and encompasses many technologies but to give you a small taster of what he can get up to, he has just posted an article on connectivity issues.

    http://blogs.msdn.com/spike/archive/2008/11/07/sql-server-error-10060-sql-server-error-10061.aspx

    He has many more interesting articles in his archive. This is well worth adding to your RSS reader.

Page 1 of 1 (5 items)