I want to update a scalar function in our database. This function is used for several calculated columns in a key table so I get a dependency error if I try to update it. I can explicitly remove the columns, update the function and re-add the columns but, without a lot of extra fannying about, that will change column orders and might have other unintended consequences. I wondered if there was a cleaner way of doing it?

I've seen a couple of blog posts where fairly knowledgeable sounding MVPs are still using sys.sysprocesses rather than the recommended DMVs: sys.dm_exec_connections, sys.dm_exec_sessions and sys.dm_exec_requests. Given that sysprocesses is deprecated, I'm curious why anyone would continue to use it, particularly MVPs. Is it just that using sysprocesses is less hassle than joining three DMVs or is there a better reason for using it? The two blog posts I mentioned were: Adam Machanic: Smashing a DMV Myth Tim Chapman: Find blocking processes using recursion in SQL Server 2005

In spite of our vehement protests, our management has decided that the development team must be granted 'sa' rights on the development server. The catch is that we, the DB support group are still responsible for maintaining this box. We have now been entrusted the task of coming up with a list of Dos and Don'ts for the development teams with these enhanced privileges. Please add to this list: DO -- - confine activities to the DB under development DO NOT -- - change any SQL instance settings - sp_configure (including cmdshell) - add/change/delete any security settings - add/change/delete database objects - add/change/delete server objects like backup devices and linked servers - add/change/delete replication - add/change/delete maintenance plans - touch any database that doesn't belong to your team Any pointers to tools available for tracking these users activities will be greatly appreciated.

Lately, a lot of queries to the database are timing out. While investigating I found that queries are in the suspended state with wait_type as PageIOLatch_Sh. I have not noticed so many queries being suspended before on the server. This has been happening for the since Saturday. A couple of weeks ago, the cpu on the db server was at 100% constantly. While trying to fix that I changed a couple of indexes which were not really being used and were resulting in user_scans. After changing the indexes, the cpu usage on the server went down, the performance of the site was generally faster and looking at the index stats now a high number of user_seeks were happening, which I thought was a good sign. However, now after a week or two, even though the cpu usage is not at 100, a large number of queries have started timing out and PageIoLatch_Sh waits are being observed. The interesting thing is that these timeouts were not happening at such a high frequency earlier even though the cpu was at 100%. Now maintaining indexes is also something which slows down processing of sql server, so I was wondering whether the new indexes could have resulted in a larger number of these waits and timeouts?

I have a user with the following server and database role memberships: 1. securityadmin (server) 2. public (server) 3. db_owner (database) 4. db_accessadmin (database) 5. db_securityadmin (database) When running the below script in SQL 2005, it runs through without any issues: BEGIN TRAN IF NOT EXISTS (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Opfix' AND COLUMN_NAME = 'ModifiedBy') BEGIN ALTER TABLE [OpFix] ADD [ModifiedBy] [int] NOT NULL CONSTRAINT DF_OpFixModifiedBy DEFAULT ([dbo].[fnSoftmarLoginUserID]()) END ROLLBACK TRAN When running the same script on SQL 2008 and SQL 2012, I get the below error: > Cannot find the object "Opfix" because it does not exist or you do > not have permissions. So, my questions are: 1. Does it makes sense that I get different results between SQL 2005 and SQL 2008+, with the same update and the exact same user permissions? 2. What are the minimum requirements for this script to run through? 3. The only way I could get this script to run through on SQL 2008+ was to grant sysadmin membership, is that as expected?

I am currently investigating an issue wherein sometimes the SSRS just suddenly stops accepting requests until we do an IISRESET command. This happens after we added an additional processor on the server. No new report is added on that time. Right now we have 2x Quad Core with HT enabled processors. Information in 'sys.dm_os_sys_info' is giving me the correct cpu_count which is 16. However, while looking at the 'ReportServer_XXX.log', I noticed that it's logging incorrect cpu count. w3wp!resourceutilities!1!3/22/2014-14:29:16:: i INFO: Reporting Services starting SKU: Standard w3wp!resourceutilities!1!3/22/2014-14:29:16:: i INFO: Evaluation copy: 0 days left w3wp!resourceutilities!1!3/22/2014-14:29:16:: i INFO: CPU throttling turned on w3wp!resourceutilities!1!3/22/2014-14:29:16:: i **INFO: Running on 127 physical processors, 255 logical processors** I am not certain if this is the cause but either way, how do I correct this issue? The count is way off. Also, on our other server, the ***CPU throttling turned on*** message is not being logged. Could also be this another reason? SSRS is running under this environment: - Windows Server 2003 R2 32-bit (PAE enabled) - SQL Server 2005 SP4 (AWE enabled) - 2xQuad core with HT processors - 64GB memory

I have C# application that uses SQLDependency to get updates from a message table. I have tested it quite extensively and worked great in the development environment but when I ran it on the production server the server crashed. I can not be absolutely sure that it is this because we picked it up ~10 minutes after I ran the application. From what I understand from how the SQLDependency works it uses stored procedures to interact with the service broker. The errors I received > *SQL Server must shut down in order to recover a database (database ID 1). The database is either a user database that could not be shut down or a system database. Restart SQL Server. If the database fails to recover after another startup, repair or restore the database.* > > *Could not find an entry for table or index with partition ID 428869310480384 in database 2. This error can occur if a stored procedure references a dropped table, or metadata is corrupted. Drop and re-create the stored procedure, or execute DBCC CHECKDB.* We are running SQL Server 2005 SP1. Edit:(removed code the link explains it better) I followed this example and mainly changed the query and what I do with the data. http://www.dreamincode.net/forums/topic/156991-using-sqldependency-to-monitor-sql-database-changes/

I am working on sql script and I am having a requirement of stop continuing the script if some conditions are not satisfied. When I Google it, I found the RaisError with 20 severity level will terminate it. But for some reasons I cannot use that option. Can please provide me what are the possible alternatives to stop SQL script execution.

Users enter a search term in a box, and that value gets passed to a stored procedure and checked against a few different fields in the database. These fields are not always of the same data type. One field (phone number) consists of all numbers, so when checking it strips out all non-numeric characters from the string using a .Net CLR function. SELECT dbo.RegexReplace('(123)123-4567', '[^0-9]', '') The problem is, this function abruptly stops working on occasion with the following error:

Msg 6533, Level 16, State 49, Line 2
AppDomain MyDBName.dbo[runtime].1575 was unloaded by escalation policy to ensure the 
consistency of your application. Out of memory happened while accessing a critical resource. 
System.Threading.ThreadAbortException: Exception of type 
'System.Threading.ThreadAbortException' was thrown.
System.Threading.ThreadAbortException: 

I've tried the suggestions posted on MSDN for this error, but am still getting the problem. At this time, switching to a 64-bit server is not an option for us. I know restarting the server releases whatever memory it has held, but that is not a viable solution in a production environment. Is there a way to strip non-numeric characters out of a string in SQL Server 2005 using T-SQL only?

With SQL Server 2005, you could look at the Task Manager and, at least, get a cursory look at how much memory is allocated to SQL Server. With SQL Server 2008, the Working Set or Commit Size never really goes above 500 MB, even though the SQLServer:Memory Manager/Total Server Memory (KB) perf counter states 16,732,760. Is there a setting where it will actually show the server memory in the Task Manager? Or is it a result of them changing how memory is used in SQL Server

I'm trying to upgrade SQL Server 2005 sp4 to SQL Server 2008 R2. However on the upgrade wizard doesn't show me any instances to upgrade. Any ideas? The instance is there just fine. I can connect via sql mgmt studio.

When I try to drop a database I get the error "Cannot drop database "dbname" because it is currently in use". However, when I run sp_who2, there are definitely no sessions connected to this database. I've also set the database to single_user mode with rollback immediate. Why is this happening?

TL;DR: How can I diagnose the source of a very inconsistent, unreproducible *'String or Binary data would be truncated.'* error, when I am quite positive the problem isn't related to user data being to large to fit into SQL objects? ---------- I have a project that is a C# application which uses SQL Server Express 2005 as a local database engine. The database is used to mostly cache data and very little processing or transforming is done within the local database. The exception to this is when I need to prepare locally cached data to use when a user prints a report from their application. In this case, the user calls a stored procedure and passes in a few variables. The stored procedure starts by deleting all data associated with the document being printed. In other words, it fully cleans the data cache before it begins. Next, it goes through a very long and complex process of consolidating and building information. Much of this information is stored in temp tables and after the process is finished, it copies the temp information into the report data tables. This application has existed for nearly 6 years. About once every few months, since the inception of the application, a random user has called me telling me that they have received a *'String or Binary data would be truncated.'* error. Furthermore, their application has historically worked perfectly, some times for years. Then, all of a sudden, they can't print from their application and they receive the above message from SQL Server. After receiving the error, they can't recover from it. In fact, every document they try to load (even one's that have worked minutes before the message appeared) are now impossible to generate because SQL Server keeps throwing this error. I know and understand the nature of this error. It's telling me that some data is larger than a specific field in which it's being inserted, however this doesn't make since in my context for two, main reasons. 1. I've never been able to reproduce this bug on any PC, but it is 100% consistent on the user's PC once it starts. 2. Across the years I've never been able to force this error to occur, regardless of the inputs I've stored in my associated data tables or passed to the stored procedure as parameters. The only way I've been able to fix this problem is to flag the user account to delete their local database. Once the user deletes their db, they rebuild and sync it with the server and everything works perfectly again. The failing stored procedure also has a ludicrous amount of data checks and washing routines, all because of this dumb error. I've never been able to isolate the problem nor solve it. I know that this type of question typically begs for the posting of code but I can't provide that. This is a BIG stored procedure and it contains some propitiatory details. Still, if anyone is aware of a SQL bug that could be caching something within the internals of SQL Server and produces this generic exception, I'd appreciate the information or your expert advice of where else to look for a solution. Please ask questions. I will comment and update my post as needed.

(A simplified example). I have Table1 with items:

+----+-------+
| id | name  |
+----+-------+
| 1  | car   |
| 2  | apple |
| 3  | grass |
| 4  | alien |
+----+-------+

and Table2 with colors that these items can be:

+----+------+--------+
| id | item | color  |
+----+------+--------+
| 1  | 1    | blue   |
| 2  | 1    | green  |
| 3  | 2    | red    |
| 4  | 2    | green  |
| 5  | 2    | yellow |
| 6  | 3    | green  |
| 7  | 3    | yellow |
| 8  | 4    | yellow |
| 9  | 4    | green  |
+----+------+--------+

I need to select all items (and ideally their colors, too) that can be either green or yellow, but not in any other color. I'm stuck at WHERE in SELECT Table1.id, Table1.name, Table2.color FROM (Table1 JOIN Table2 ON Table1.id=Table2.item) WHERE…. Can you help me? Ideally the result should be:

+-----------+-------------+---------------+
| Table1.id | Table1.name | Table2.color  |
+-----------+-------------+---------------+
| 3         | grass       | green         |
| 3         | grass       | yellow        |
| 4         | alien       | yellow        |
| 4         | alien       | green         |
+-----------+-------------+---------------+

(Sorry if this is a duplicate question of a already answered one, but I couldn't find one here, I even have no idea what keywords to search 🤷‍♂️)

Suppose I have to export data from one server to another (through linked servers). Which statement will be more efficient? Executing in source server: INSERT INTO [DestinationLinkedServer].[DestinationDB].[dbo].[Table] SELECT a, b, c, ... FROM [dbo].Udf_GetExportData() Or executing in target server: INSERT INTO [dbo].[Table] SELECT a, b, c, ... FROM OPENQUERY([OriginLinkedServer], 'SELECT a, b, c, ... FROM [OriginDB].[dbo].Udf_GetExportData()') Which one will be faster and consume fewer resourcers in total (both source and target server)? Both servers are SQL Server 2005.

We are moving to SQL Server 2014 Expensive Edition Q1 next year. Right now we are on SQL Server 2005 Standard Edition, so we don't have access to the oh-so-wonderful MIRROR TO option for our backups (We use Ola Hallengren's scripts). Right now we are using robocopy to copy files over to a network share just in case our backups stored locally become corrupt, lost, or decide to take a nice long vacation. My question is, when we use the MIRROR TO option, is it any more/less resource intensive than just using robocopy, xcopy or a Powershell solution? What exactly happens when we use MIRROR TO in SQL Server? Looking for a bit of a 'nuts and bolts' answer, if possible.

Whilst trying to restore a "master" backup file (on my Windows Server 2003 / SQL Server 2005) machine I've dropped a clanger and re set the number of users from "0" (unlimited) to "1" in the SQL enterprise manager console (ignoring the warning that Enterprise Manager shows about no longer being able to use the management console if I did this !). Can anyone explain how to reverse this? I'm sort of expecting that it will have to be done from a command prompt somehow? I have an identical twin "standby" Proliant ML350 as a backup for a Sage enterprise system. I periodically backup the SQL Server databases from Sage and restore them to my standby machine. This has worked well for years - but you do have to restore the "master" databases whilst in single user mode. In a moment of stupidity I thought I'd try doing this by altering the setting mentioned above rather than my usual method.

We have two production SQL Servers running SQL Server 2005 SP4 with cumulative update 3. Both servers run on physical machines that are identical. DELL PowerEdge R815 with 4 x 12 core CPUs and 512GB (yes GB) of ram, with 10GB iSCSI SAN connected drives for all SQL databases and logs. OS is Microsoft Windows Server 2008 R2 Enterprise edition with all SP's and windows updates. OS drive is a RAID 5 array of 3 x 72GB 2.5" 15k SAS drives. SAN is a Dell EqualLogic 6510 with 48 x 10K SAS 3.5" drives, configured in RAID 50, sliced into various LUNs for the 2 SQL Servers, and also shared with an Exchange machine and several VMWare servers. We have over 20 databases, 11 of which are mirrored with high availability using a witness server. The witness server is a lower powered machine running a SQL Server instance that is used for nothing other than providing witness services. The biggest mirrored database is 450GB and generates around 100-300 iops. Database Mirroring Monitor reports a current send rate around 100kb to 10mb per second, and a mirror commit overhead of (typically) 0 milliseconds. The mirror server has no problem keeping up with the principal. We are consistently experiencing mirroring failovers. Sometimes a single database will failover, other times almost all databases will failover simultaneously. For instance, last night, we had 10 of 11 databases failover, the remaining database stayed accessible until I manually failed it over. I have gone through several troubleshooting steps to attempt to identify the problem, but have so far not been able to resolve the issue: 1) The machine came with a Broadcom BCM5709C NetXtreme II 4 port Gigabit network adapter which we initially used as the primary network connection. We have since installed an Intel(R) PRO/1000 PT Dual Port Server Adapter on both machines to eliminate the NIC as the issue. 2) All databases have an automatic full backup nightly along with a log backup for databases involved in mirroring. Log file useage is monitored and rarely gets above 15% used. The log file for the main database is 125GB, consisting of 159 virtual log files that range in size from 511MB to 1GB. TempDB is on it's own LUN, and consists of 24 x 2GB files. 3) SQL Server log on the witness shows no errors other than: The mirroring connection to "TCP://SQL02.DOMAIN.INET:5022" has timed out for database "Data" after 30 seconds without a response. Check the service and network connections. SQL Server log on the primary and secondary servers show messages relating to mirroring: The mirroring connection to "TCP://SQL01.DOMAIN.INET:5022" has timed out for database "Data" after 30 seconds without a response. Check the service and network connections. The mirrored database "Data" is changing roles from "PRINCIPAL" to "MIRROR" due to Role Syncronization. (Synchronization is misspelled here on purpose since that is precisely how the actual message is display.) The mirrored database "Data" is changing roles from "PRINCIPAL" to "MIRROR" due to Failover. The mirrored database "Data" is changing roles from "MIRROR" to "PRINCIPAL" due to Failover from partner. The SQL Server services continue to run and network connections seem to stay up. We consistently have between 500 to 2500 sessions connected to each server (primarily robotic applications that connect to service broker queues on a single database). 4) TCP Chimney and RSS etc are disabled using NET SH syntax. 5) I have ran the SQL Server 2005 Best Practices Analyzer against both machines and find nothing other than the very occasional Application Event Log error 833, none of which are coincident with the failover events: SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [F:\Data.MDF] in database [Data] (9). The OS file handle is 0x00000000000010A0. The offset of the latest long I/O is: 0x000007d4b10000). 6) Occasionally we see "The client was unable to reuse a session with SPID XXX, which had been reset for connection pooling. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message." generated by both servers. There appears to be no "earlier" messages that indicate any issue. 7) Occasionally database mail writes an error to the Application event log: Exception Type: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException Message: There was an error on the connection. Reason: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding., connection parameters: Server Name: MGSQL02, Database Name: msdb Data: System.Collections.ListDictionaryInternal TargetSite: Void OpenConnection(Microsoft.SqlServer.Management.Common.SqlConnectionInfo) HelpLink: NULL Source: DatabaseMailEngine StackTrace Information at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.ConnectionManager.OpenConnection(SqlConnectionInfo ci) at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.DataAccessAdapter.OpenConnection(String dbServerName, String dbName, String userName, String password) at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.QueueItemProcesser.ProcessQueueItems(String dbName, String dbServerName, Int32 lifetimeMinimumSec, LogLevel loggingLevel) I believe the Timeouts are causing the failover; what could cause these timeouts? Obviously if there was an actual network issue such as a bad cable, or a bad switch, that might cause packet loss and therefore a timeout, however what other things could cause timeouts? Blocking? If MSDB, or some other system database had an I/O timeout could that cause the mirroring failover? Thanks for any advice! MSDN has the following to say about the timeout mechanism itself : >The Mirroring Time-Out Mechanism > >Because soft errors are not detectable directly by a server instance, a soft error could potentially cause a server instance to wait indefinitely. To prevent this, database mirroring implements its own time-out mechanism, based on each server instance in a mirroring session sending out a ping on each open connection at a fixed interval. > >To keep a connection open, a server instance must receive a ping on that connection in the time-out period defined, plus the time that is required to send one more ping. Receiving a ping during the time-out period indicates that the connection is still open and that the server instances are communicating over it. On receiving a ping, a server instance resets its time-out counter on that connection. > >If no ping is received on a connection during the time-out period, a server instance considers the connection to have timed out. The server instance closes the timed-out connection and handles the time-out event according to the state and operating mode of the session. netsh interface tcp show global shows: Receive-Side Scaling State : disabled Chimney Offload State : disabled NetDMA State : enabled Direct Cache Acess (DCA) : disabled Receive Window Auto-Tuning Level : disabled Add-On Congestion Control Provider : ctcp ECN Capability : disabled RFC 1323 Timestamps : disabled netsh interface ipv4 show dynamicportrange tcp Protocol tcp Dynamic Port Range Start Port : 1025 Number of Ports : 64510 SELECT name, value_in_use FROM sys.configurations

    Ad Hoc Distributed Queries               0         
    affinity I/O mask                        0         
    affinity mask                            0         
    affinity64 I/O mask                      0         
    affinity64 mask                          0         
    Agent XPs                                1         
    allow updates                            0         
    awe enabled                              0         
    blocked process threshold                5         
    c2 audit mode                            0         
    clr enabled                              1         
    common criteria compliance enabled       0         
    cost threshold for parallelism           4         
    cross db ownership chaining              0         
    cursor threshold                         -1        
    Database Mail XPs                        1         
    default full-text language               1033      
    default language                         0         
    default trace enabled                    1         
    disallow results from triggers           0         
    fill factor (%)                          0         
    ft crawl bandwidth (max)                 100       
    ft crawl bandwidth (min)                 0         
    ft notify bandwidth (max)                100       
    ft notify bandwidth (min)                0         
    index create memory (KB)                 0         
    in-doubt xact resolution                 0         
    lightweight pooling                      0         
    locks                                    0         
    max degree of parallelism                6         
    max full-text crawl range                4         
    max server memory (MB)                   393216    
    max text repl size (B)                   65536     
    max worker threads                       0         
    media retention                          0         
    min memory per query (KB)                2048      
    min server memory (MB)                   52427     
    nested triggers                          1         
    network packet size (B)                  1400      
    Ole Automation Procedures                1         
    open objects                             0         
    PH timeout (s)                           60        
    precompute rank                          0         
    priority boost                           0         
    query governor cost limit                0         
    query wait (s)                           -1        
    recovery interval (min)                  0         
    remote access                            1         
    remote admin connections                 0         
    remote login timeout (s)                 20        
    remote proc trans                        0         
    remote query timeout (s)                 600       
    Replication XPs                          0         
    scan for startup procs                   0         
    server trigger recursion                 1         
    set working set size                     0         
    show advanced options                    1         
    SMO and DMO XPs                          1         
    SQL Mail XPs                             0         
    transform noise words                    0         
    two digit year cutoff                    2049      
    user connections                         0         
    user options                             4216      
    Web Assistant Procedures                 0         
    xp_cmdshell                              1         

A while ago, I manually modified the mirroring_connection_timeout value for all mirrored databases to 30 seconds to attempt to remediate the problem; this has simply increased the amount of time between failover events. With the mirroring_connection_timeout setting set at the default of 10 seconds, we see a *lot* more failovers. A comment had asked me to ensure IPSec is disabled, so I'm posting the contents of several netsh commands that display the IPSec configuration of the operating system:

C:\>netsh ipsec dynamic show all
No currently assigned Policy
Mainmode Policies not available.
Quickmode Policies not available.
Generic Mainmode Filters not available.
Specific Mainmode Filters not available.
Generic Quickmode Filters not available.
Specific Quickmode Filters not available.
IPsec MainMode Security Associations not available.
IPsec QuickMode Security Associations not available.

IPsec Configuration Parameters
------------------------------
StrongCRLCheck         : 1
IPsecexempt            : 3

IPsec Statistics
----------------
Active Assoc                : 0
Offload SAs                 : 0
Pending Key                 : 0
Key Adds                    : 0
Key Deletes                 : 0
ReKeys                      : 0
Active Tunnels              : 0
Bad SPI Pkts                : 0
Pkts not Decrypted          : 0
Pkts not Authenticated      : 0
Pkts with Replay Detection  : 0
Confidential Bytes Sent     : 0
Confidential Bytes Received : 0
Authenticated Bytes Sent    : 0
Authenticated Bytes Received: 0
Transport Bytes Sent        : 0
Transport Bytes Received    : 0
Bytes Sent In Tunnels       : 0
Bytes Received In Tunnels   : 0
Offloaded Bytes Sent        : 0
Offloaded Bytes Received    : 0

C:\>netsh ipsec static show all
ERR IPsec : No Policies in Policy Store

---------------------------------------------------- UPDATE: 2012-12-20 We've now moved our production systems onto SQL Server 2012. We've been running this since the morning of December 17th - so far no failovers. However, a couple of days is well within what we saw with the 2005-based systems. In an effort to document the performance of our new systems, I've been looking at sys.dm_os_wait_stats more carefully; and noticed DBMIRROR_DBM_EVENT, which is an undocumented wait type. Graham Kent at Microsoft has an interesting article regarding troubleshooting unexpected failovers and this wait type. I shall recap his findings here: >The customer was experiencing a huge blocking chain built on a high volume OLTP database where all the head blockers were waiting on DBMIRROR_DBM_EVENT. Here is the sequence of events I went through: 1. Review the blocking chain itself - ho help here as all we can see is that we're waiting on DBMIRROR_DBM_EVENT 2. Review the source for the undocumented wait type. Obviously you can't do this outside of MS, but I can say that at the time of writing this wait type represents the wait used when the principal is waiting for the mirror to harden an LSN, meaning that the transaction it's part of cannot commit. This immediately points quite specifically to the problem that the principal cannot commit transactions as it's waiting on the mirror. Now we need to investigate why the mirror is not committing transactions or why the principal doesn't know whether it is. 3. Review the msdb system tables >(a) Look at the [backupset] table to see if the size of the logs produced at the time of the problem are significantly higher then normal. If they were exceptionally large it may be that the mirror was flooded with transactions and could simply not keep up with the volume. This is why books online will tell you sometimes to disable mirroring if you need to do an exceptionally large logged operation such as an index rebuild. (reference for why this is at http://technet.microsoft.com/en-us/library/cc917681.aspx) . Here i used the following TSQL SELECT backup_set_id,backup_start_date,database_name,has_bulk_logged_data,backup_size / 1000 FROM [backupset] where backup_start_date between '2011-01-05 14:00:00' and '2011-01-05 19:30:00' go select round((AVG(backup_size)/1000),0) FROM [backupset] where database_name = 'mydatabase' >(b) secondly I looked at the data in the tables [dbm_monitor_data]. The key here is to locate the timeframe in which we had a problem and then see if we were significant experiencing changes in any of the following: log_flush_rate send_queue_size send_rate redo_queue_size redo_rate >These are all indicators similar to part (a) in that they might show a component or piece of architecture that wasn't responding. For example if the send_queue suddenly starts to grow but the re_do queue doesn't grow, then it would imply that the the principal cannot send the log records to the mirror so you'd want to look at connectivity maybe, or the service broker queues dealing with the actual transmissions. >In this particular scenario we noted that all the counters appeared to have strange values, in that there were log backups going on of normal sizes, but there were no status changes, 0 send queue, 0 redo queue, a flat send rate and a flat redo rate. This is very strange as it implies that the DBM Monitor could not record any values from anywhere over the problem period. >4. Review the SQL Server error logs. In this case there were no errors or information messages whatsoever, but in other scenarios such as this, it’s very common for errors in the 1400 range to be reported, examples of which you can find in other places in my other mirroring blogs, such as this Error 1413 example >5. Review the default trace files – in this scenario I was not provided the default traces, however they are fantastic sources of DBM problem information, as they record state change events on all the partners.This is documented here: >Database Mirroring State Change Event Class >This often gives you a great picture of scenarios such as when network connectivity failed between one or all of the partners and then what the state of the partnership became afterwards. >CONCLUSIONS: >In this particular scenario I’m currently missing 2 key points of data, but that apart I can still make a reasonable hypothesis on the above information. We certainly can say that the blocking was caused by the fact that DBM was enabled to the due the blockers all waiting on the DBMIRROR_DBM_EVENT wait type. Since we know we didn’t flood the mirror with a large logged operation and that this deployment normally runs happily in this mode, we can exclude unusual large operations. This means that we have 2 potential candidates at this stage: 1. Hardware problems on the connectivity between some or all of the partners. 2. CPU exhaustion on the mirror server – simply unable to keep up with redos – the CPU exhaustion could itself be from a process outside of SQL Server or outside of the this mirror partnership. 3. A problem with the mirroring code itself (we’d really need some memory dumps to confirm this though). >Based upon experience I’d suspect 1 or 2, but I always keep an open mind about 3 as well, we’re trying to collect some more data now to look at this problem in more detail.

I have a SQL Server instance that runs 5 scheduled tasks each night, each of which run SSIS packages. These packages have been running for years and the associated steps run via a Proxy Account (PackageExecutor). PackageExecuter is associated with a SQL Credential that was a former domain admin account. Soon, the domain associated with this admin account is going to be shutdown. I have to use a new account, on a new domain, as the admin account associated with my proxy, PackageExecutor. When I created a new Credential for the new Admin account and associated it with PackageExecutor, I started to get the following error when I tried to run one of my SQL jobs as a test: Unable to start execution of step 1 (reason: Error authenticating proxy *Domain\Admin_Account*@*fully.qualified.domain.com*, system error: Logon failure: unknown user name or bad password.). The step failed. If I understand this reasonably explicit error, what it's telling me is that the Credential accounts, associated with my proxy is not correct. How do I validate this? I know that this account is legitimate-- I've already associated it with every associated server group, I have made it a sysadmin user on the server. What could be causing this problem? To be clear, I haven't mis-typed the account name or the password associated with the Proxy Credential. However, when I entered the account name Domain\Admin_Account and clicked the Check Names button, SQL Server automatically transformed the User ID to the fully-qualified version. I'm not sure if this has anything to do with this problem. I'm at a bit of a loss. I've given my credential account full access to everything that I can think of. What might I need to do to get this to work? **UPDATE** Sorry, one more quick mention. I've found this MSDN kb article . Resolution method #1 is what I've been doing for years. The others don't seem to apply, or I'm missing something. Any tips or clarification would be beneficial.

One way to determine the executing stored procedure is to use "dynamic management" methods, like so: SELECT sqlText.Text, req.* FROM sys.dm_exec_requests req OUTER APPLY sys.dm_exec_sql_text(req.sql_handle) AS sqltext However, this only displays the text of the stored procedure's create statement. e.g.: CREATE PROCEDURE IMaProcedure @id int AS SELECT * FROM AllTheThings Where id = @id Ideally I'd like to see what the parameters were for the running procedure that are causing it to run so long for the particular set of offending parameters. Is there a way to do that? (In this question Aaron Bertrand mentions DBCC InputBuffer , but I don't think that's appropriate for this problem.)