database raised exception 4147

This happened when I tried to restore a database with multiple data files.

When I used native SSMS tools instead of my custom stored procedure, the error did not show up.

SSRS align text to bottom of page

There was some text I wanted to display at the bottom of a page.  I couldn’t use a footer, because it was all part of a table, with a border.

The trick that solved it was this:

Add a text box, and write your text (or add your picture).  Then go the Properties, and change the VerticalAlign property to Bottom.

Then make the text box as big as you can, while still keeping the report to one page.

Cluster resource sql server agent failed

The SQL Server Agent service would not come up on the 2nd node.

The reason ended up being that the SQL Agent Resource was set up as a Generic Resource.  A Generic Resource is setup as a single server Resource.

You need to delete that resource, and then then add a new Clustered Resource for SQL Agent.

SQL Server Agent would not start (2008 R2)

The fix ended up being to open up SSMS, right-click on SQL Server Agent, go to Properties.  Then on the Connections tab, write in the name of the virtual SQL Server.

It may be that the Cluster Manager is the proper way to start the Agent.  But due to the weird way the customer set up my access, I didn’t have access to Cluster Manager.

 

Other errors:

Agent named pipes provider could not open a connection to sql server

sqlserveragent could not be started local

SQL Display time with seconds and AM PM

Instead of showing 12:00:00.0000000

I wanted to display 12:00:00 PM

There were some solutions built in for displaying 12:00 PM. However, I wanted the seconds to show as well.

Here are some ideas.  Grab the 2 right-hand most characters from the built in solution to get AM or PM.

/* Script by GlutenFree SQL, Daniel Gras, 1/18/2013 */
DECLARE @Time time
SET @Time = '12:00:00.0000000'

SELECT 
 CASE WHEN DATEPART(hour, @Time) in (0,10,11,12,22,23)
      THEN  LEFT( CONVERT(varchar(15),@Time,109 ),8) 
      ELSE LEFT( CONVERT(varchar(15),@Time,109 ),7) 
  END
  + ' ' + RIGHT( CONVERT(varchar(15),@Time,100 ) , 2)

One could also write a CASE statement to see if the time is >= 12:00:00.0000000, to decide PM or AM.   Test it out on your table with STATISTICS TIME ON or STATISTICS IO ON to see which one is better

/* Script by GlutenFree SQL, Daniel Gras, 1/18/2013 */
DECLARE @Time time
SET @Time = '12:00:00.0000000'

SELECT 
 CASE WHEN DATEPART(hour, @Time) in (0,10,11,12,22,23)
      THEN  LEFT( CONVERT(varchar(15),@Time,109 ),8) 
      ELSE LEFT( CONVERT(varchar(15),@Time,109 ),7) 
  END
  + CASE WHEN @Time >= '12:00:00.0000000' THEN ' PM' ELSE ' AM' END

Large sys_mailitems fix

I have a customer with an MSDB database that had grown to 40 GB.  After looking into it, I found that the table sys_mailitems was large.

There are some “fixes” out there to look at the sent_date and delete data based on that.  But when the table is so large, that will not work.  There is not an index on sent_date, so it will scan the clustered index looking for sent_dates.   Any attempt to create an index on sent_date takes a long time, and causes blocking on any INSERTS.  Let’s face it, if the table got that large, there are definitely going to be inserts.   In a 24/7 environment, we’re in a dilemma.  Of course, a truncate might be efficient

The other problem is, as a highly paid DBA, I don’t have time to babysit DELETING small records at a time, all day long!

However, I came up with a good solution.  The sent_dates do show up in order of the ID, which is indexed.  Schedule this SQL to run in a job about every 2 minutes.   It deletes 20,000 of the oldest records at a time.     The @DateToStop variable means it will not delete email history more recent than that date.

/* Created by GlutenFreeSQL, Daniel Gras, 1/17/2013 */
DECLARE @DateToStop datetime
SET @DateToStop = '2012-6-1'

DECLARE @CurrentMinDate datetime
SET @CurrentMinDate = (SELECT top 1 sent_date from sysmail_mailitems)

IF @CurrentMinDate < @DateToStop
BEGIN
DELETE sysmail_mailitems where mailitem_id < (select MIN(mailitem_id) + 20000 from sysmail_mailitems)
select top 1 sent_date from sysmail_mailitems select MAX(mailitem_id) - MIN(mailitem_id) from sysmail_mailitems
END

SQL Server error 3417

I have a customer who could not start their SQL Server. It was showing error 3417 on startup.

There is not much info out there about 3417, and what I saw wasn’t very helpful in our situation.  The folder and user permissions were squared away.

Microsoft Support actually joined this call, and they looked at the SQL Server logs.  Here’s how they opened it.

1. First, find the location of the log by opening SQL Server Configuration Manager.
2. Right-click on the SQL Server engine service.  Usually called “SQL Server ( MSSQLSERVER )”.
3. Click on the Advanced tab
4. Look next to the Dump Directory label, and copy that path
5. Open the path in Windows Explorer
6. Open the ERRORLOG file in Notepad

By taking a look at the error log Microsoft Support was able to get more insights other than the non-helpful Error 3417.

This ended up being due to a corrupt master database.  And the solution to that is too much for this blog post.   They did not have a master database backup. (This was not one that I administered ). And once that got running, every database was corrupt. I hope maybe this helps a bit, for anyone else who comes across 3417.  Sorry, you might be in for a long night.