How long does it take to create a database?

How long does it take to create a database? 

Well, depends on the piece of string on your pocket, of course – how big is the database, how zippy your server is, and so on. 

But ideally, you want to create it ASAP. 

Does the database ever need to grow? 

Well, if you have sized it perfectly, it will never need to grow.  <Howls of derisive laughter>

OK, seriously, of course it does, and you want it to grow as quickly as possible too. 

By default, before it creates or grows a file, SQL Server pauses for a moment, maybe quite a few moments, to wipe out any existing data by writing zeroes all over the space it needs.  Which is a sensible precaution if you want to make absolutely sure that nobody can hack into your server and read stuff that you had previously deleted.  But if you are writing data over it anyway, that might be a bit excessively paranoid.  Or maybe you ARE excessively paranoid?  Perhaps you have enough security in place that you would like to consider trading that precaution for speed and performance.  Only you can decide that.  

Find a development server, and check the file location of an existing database, like this:

use dbadmin

go

sp_helpfile

Now create a database.  See how long it takes.  In my Dev environment, this ran in 3 minutes, 17 seconds:

USE [master]

GO

CREATE DATABASE [Fred] ON  PRIMARY

( NAME = N'Fred_Data', FILENAME = N'G:\data\Fred_Data.mdf' , SIZE = 102596608KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0)

LOG ON

( NAME = N'Fred_Log', FILENAME = N'H:\logs\Fred_Log.ldf' , SIZE = 2621440KB , MAXSIZE = 2048GB , FILEGROWTH = 0)

GO

Okay,  three minutes for 100GB, not totally shabby. 

Let’s try it without writing the zeroes.  It's called Instant File Initialization, if you want to do any further reading - I like Brent Ozar's write-up

It isn’t a box to tick or a script to run, I’m afraid.   you need to restart the SQL Server service, so it will have to be done out of hours on Production.

First thing you need is the name of your service account:

-- 2008r2 onwards

SELECT service_account, * FROM sys.dm_server_services;

For example - e.g. GANDALF\sqldev_frodo

Open a Remote Desktop Connection to the server

Run SECPOL.MSC (Local Security Policy)

Open up the Local Policies

Click on User Rights Assignment

Scroll right down to Perform Volume Maintenance Tasks

Open it up and add the service account

Click on OK

Now you need to restart the SQL Server Service

Run SERVICES.MSC

Scroll down to the SQL Server services – there are probably a few of them

Stop the SQL Server Agent (MSSQLSERVER) service first

Then stop SQL Server (MSSQLSERVER)

Then start SQL Server (MSSQLSERVER)

And finally start SQL Server Agent (MSSQLSERVER)

Why in that order?  If you stop SQL Server (MSSQLSERVER), the Agent service should stop automatically

If you restart SQL Server (MSSQLSERVER), it should stop, stop the agent, and restart (though you have to remember to restart the agent).

So why bother with the extra steps?

Because that is what SHOULD happen.  Sometimes the restart thing gets stuck.  This may be me remembering something that used to happen and has now been fixed, senile old fool, stuck in his ways.  So feel free to try restart.  Just don’t come whingeing to me if it doesn’t do what it should do.  Because I shall say I Told You So

Back to that script.  Remember the script to create the database?

USE [master]

GO

DROP DATABASE [Fred]

GO

CREATE DATABASE [Fred] ON  PRIMARY

( NAME = N'Fred_Data', FILENAME = N'G:\data\Fred_Data.mdf' , SIZE = 102596608KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0)

LOG ON

( NAME = N'Fred_Log', FILENAME = N'H:\logs\Fred_Log.ldf' , SIZE = 2621440KB , MAXSIZE = 2048GB , FILEGROWTH = 0)

GO

Before, this ran in 3 minutes, 17 seconds.

Now?  18 seconds. 

That’s a pretty good improvement

Tables with Spaces in the Name

Someone has created a table called Complaint Log
Not ComplaintLog or even Complaint_Log

I want to drop an index, so enclosing it in square brackets does the trick

DROP INDEX [IX_Complaint_Log_SNP_Number] ON [dbo].[Complaint Log];

And of course I could do it with the GUI, just by pointing and clicking, all that stuff

But, I want to do it with a script and run it at 0300, and I want to check whether the index actually exists before my script tries to drop it. 

Normal thing would be to do something like this:

IF EXISTS(SELECT * FROM sys.indexes

       WHERE object_id = object_id('dbo.Complaint Log')

       AND NAME ='IX_Complaint_Log_SNP_Number')

DROP INDEX [IX_Complaint_Log_SNP_Number] ON [dbo].[Complaint Log];

But because of that pesky space, it doesn't work.  Oh, it says it does - it says
Command(s) completed successfully.
But it is lying - the index is not found, and it is not deleted

It would be very satisfying to hunt down the guy who created Complaints Log instead of Complaints_Log, but in these namby pamby days of political correctness gone mad, torturing and killing Developers is frowned upon.

Stefan Spraakman gave me the answer - thanks Stefan!  I need to join sys.indexes with sys.objects and test on the name, like this:

IF EXISTS(SELECT * FROM sys.indexes AS i

          INNER JOIN sys.objects AS o ON o.object_id = i.object_id

          WHERE o.name = 'Complaint Log'

          AND i.NAME ='IX_Complaint_Log_SNP_Number')


DROP INDEX [IX_Complaint_Log_SNP_Number] ON [dbo].[Complaint Log];

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 COMPRESSION

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' ; 
GO 

EXEC sp_configure 'backup compression default', 1 ; 

RECONFIGURE WITH OVERRIDE ; 
GO
 

--after - 1 means ON

SELECT value  
FROM sys.configurations  
WHERE name = 'backup compression default' ; 
GO


You don't even have to restart anything!


Is it worth doing?  What do you mean, don't you trust me?
I think so.  Here's a way to calculate the actual compression achieved - on my server, out of 1,628 backups, I got a compression ratio of 3.02

SELECT

       sum(backup_size) as All_Backups
       ,sum(compressed_backup_size) as All_Compressed
       ,sum(backup_size)/sum(compressed_backup_size) AS Compression_Ratio
FROM msdb..backupset; 

Madness

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?

Argh!

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
GO

EXEC sp_send_dbmail
@profile_name='H2SO4',     -- whatever the mail profile is called

@recipients='jack.whittaker@mycompany.co.uk',  -- YOUR email, duh
@subject='Test Message',
@body='If you can read this email, your database mail is working';
go

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. 

EXECUTE AS LOGIN = 'SA'

Check the Logs

USE MSDB
GO

SELECT

       sai.sent_status

       ,sai.subject

       ,sai.body

       ,sai.last_mod_date

       ,sai.recipients

       ,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 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

go

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 you have already looked at 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.