Friday, 13 July 2018

Backup Compression

Who wants big backups?  Not me, and probably not you

You can compress your backups, starting from SQL Server 2008. 

In a maintenance plan, just select the option Compress

In a script, add the magic words WITH COMPRESS

But you can set the default so that if you forget, it compresses for you. 

I'm indebted to Microsoft for this unusually clear description

-- before - 0 means OFF
SELECT value  
FROM sys.configurations  
WHERE name = 'backup compression default' ; 

EXEC sp_configure 'backup compression default', 1 ; 

--after - 1 means ON
SELECT value  
FROM sys.configurations  
WHERE name = 'backup compression default' ; 

You don't even have to restart anything!


What maniac thought that the Approved field would be a good candidate for an index?

Approved  HowMany
0 876528
1 446593
-1 24

And what on earth can -1 signify?

And why did they use an INT as the data type?


Wednesday, 20 June 2018

Database Mail – What could possibly go wrong?

Fans of SQL Server will know that the answer is LOTS!  So I shall try to make some kind of sense of it.  I hope you find it helpful.
If you are in a rush, skip to the end where I have listed three things to make sure
Start by sending a test message to yourself
Easiest way I think is to open Management in SSMS, right click on database mail, and select Send Test E-Mail. Have a look in your inbox.
But if you want to do it with a bit of T-SQL, why not?

--send test message
USE msdb

EXEC sp_send_dbmail
@profile_name='H2SO4',     -- whatever the mail profile is called
@recipients='',  -- YOUR email, duh
@subject='Test Message',
@body='If you can read this email, your database mail is working';

If you find that message in your inbox, well done, go and have a nice cup of tea. 

No sign of your message?
Check out your Junk / Deleted Items folders – I did a lot of troubleshooting today, as emails were being  caught by a Junk rule and being automatically moved to my Junk folder.  If you have set up rules to send automated messages to a folder, check there too. 

Is it a permissions issue? 
Add this little bit of code before the above script and try again. 

Check the Logs
       ,sel.description as Log_Message 

FROM dbo.sysmail_allitems as sai
INNER JOIN dbo.sysmail_event_log AS sel
    ON sai.mailitem_id = sel.mailitem_id;

If all are UNSENT, think about restarting the SQL server agent service

The Event Log may or may not be helpful.  It usually either tells you either

1)                DatabaseMail process is started
2)                DatabaseMail process is shutting down 

You can ignore these by adding a where clause, but the started and shutting down messages indicate that it is working normally. 

use msdb

select *
from dbo.sysmail_event_log
--where event_type != 'information'
order by log_id desc;

There are a couple of other things you can look at, but probably not helpful if they are not in sysmail_allitems

-- list all emails
SELECT sent_status, *
FROM dbo.sysmail_mailitems;

--List all SENT emails
SELECT sent_status, *
FROM dbo.sysmail_sentitems;

And one of these things might give you a clue

--Should be 1 ie Yes
 SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb';

--should be started
 EXECUTE msdb.dbo.sysmail_help_status_sp;

--state should be active
 sysmail_help_queue_sp @queue_type = 'Mail' ;

 --ensure that sysmail has been started
 EXECUTE dbo.sysmail_start_sp;

Things to make sure

A)     Database Mail insists that you have .NET Framework 3.5 on your server.  You might notice that you have .NET Framework 4.5, so you don’t need the old version, right? 
WRONG Check out how to do it here

B)     If you can send an email from the Database Mail, but not from SQL Agent jobs, you may have forgotten to enable a mail profile for alerts.
  1. Right click on "SQL Server Agent"
  2. Then "Properties".
  3. Then go to "Alert System" section.
  4. Tick the "Enable mail profile" box.
  5. Then "OK".         
  6. MUST do - Restart the SQL Server Agent service. 

C)     And if Database Mail seems to be sending, but you are not receiving, check again that your message is not being caught by a filter.  Sysadmins will be able to look for the message in Exchange.  You can check it by sending a test message to a colleague.

Monday, 19 March 2018


SQL server Agent runs jobs, and if they fail to run, it can be configured to send you a message to say they have fallen over.  You can if you like configure it to send a message if it succeeds too.    Beware!  That way lies madness. 

Problem with a Job Succeeded message is that there may be dozens, even hundreds of servers.  Each server might easily have dozens, even hundreds of jobs running on them.  Once a day, once an hour, once a minute even.  So for 50 servers running 25 jobs every hour on the hour, that is a lot of emails.  You will probably delete them automatically without reading them, and so miss the one you really care about - the one job that failed last night.

So set it up to notify you of jobs that fail. 

All well and good.  But how will you know if SQL Server Agent itself has failed?  It does, occasionally. 

I set up a job on each server which runs a Job Succeeded job on each server, once a day.  At midnight, before the more significant jobs run.  It send me a message.  I never read it. 

But I have set up rules in my email which put the messages into folders, one per server.   The job almost always succeeds, and every day, the number of messages goes up by one. 

But if SQL Server Agent ever fails on any server, the number of messages will be different.  I can see at a glance that something has gone wrong

And I can go and fix it

Friday, 9 March 2018

SSRS access denied

Nobody can run the SSRS reports I laboriously created, which rather makes life pointless.  Yet they have permissions to do so.  At the highest level.  I upgrade a user to Content Manager.  Nope.  As soon as he tries to run it, it fails. 

I'm indebted to Sajid Pandore for the solution

To see where it was denying the access, he ran this QUERY on the RS1 server

Use ReportServer

select *
from ExecutionLog3
order by TimeStart desc;

It seemed that the query was being denied permission to run one specific dataset, dtsAcYear, which contains data for the Academic Year parameter.  Unfortunately, this is the first parameter the system gets to. 

Checked the permission on that Dataset – we found that the BI User group for some strange reason was not listed there.

I am not sure why it did not inherit the permissions from the higher level.  All the other datasets did!

We added this manually and tested the report all seems to be working now. 

Until the next thing goes wrong

SSRS Job Failure @P21 error

My SSIS job fails with an @P21 error message

Huh?  There is no such parameter in my stored procedure.  There are quite a few, but not 21 of the dratted things. 

The job was trying to run a stored procedure to updated changed records.  The procedure ran perfectly well in SQL server.  But not when I built it into an SSIS job.

Here's why.  Muggins here typed:
exec usp_update ?.?.?.?.?

And I should have written:
exec usp_update ?,?,?,?,?


Tuesday, 4 July 2017

Sometimes Database Administration Isn't Challenging Enough

First, we wrap the "hole guy's" arm in a skin for protection

Then we find a big hole and the "hole guy" crawls in.

We use modern lighting...

There it is.

Those must be eggs.

I let it take my protected arm, sort of like noodling for fish.

Then my buddy pulls me out with the snake attached.

Ain't it a beauty?

It will feed the whole village for a while.

Snake Noodling - - - - - What real men do!
Maybe standing in line at the grocery store isn't as bad as it seems!!!