Thursday, 28 October 2010

Bulk Insert Permissions

In order to be able to execute bulk operations you need to certain level of privilege both on the database (table) you are performing the operation against and the instance you are performing the operation on.
If you don't have the necessary permissions you will get an error similar to the following:
Msg 4834, Level 16, State 4, Line 5
You do not have permission to use the bulk load statement.
This is what one of my users got earlier today when trying to perform a BULK INSERT. To be able to do this successfully they will need the ability (permission) to INSERT records into the table in question.
In our case the following script will do the trick:
USE orderdemo
go
GRANT INSERT ON dbo.orders_BULK  TO bulktest
go


They will also need Administer Bulk Operations permission, you can give that permission by executing the following, this is a server level permission:


USE master
go 
GRANT ADMINISTER BULK OPERATIONS TO  bulktest



The user will need ALTER table permissions in certain circumstances.


This information is available in BOL but I came across the need to give this permission today and I thought I would share it.


Monday, 18 October 2010

What I Did This Weekend

About two weeks ago my good friend and former colleague Justin Hostettler-Davie -JHD (Blog | Twitter) asked me via my wife on  Facebook if I fancied climbing Scafell Pike, the highest peak in England, with him on Saturday the 16th October and for some reason I said OK why not.

photo (4)

Justin is training to climb Kilimanjaro in the new year (January 2011) to raise money for Velindre Cancer Centre. You can read more about Justin’s challenge and make a donation if you want to here. He has climbed several mountains in preparation for his challenge including Cadair Idris, Pen Y Fan, and Snowden and this Saturday we attempted Scafell Pike he has been accompanied by several friends over the course of his training, but Lance Thomas (No Blog | No Twitter) has been on most of his training climbs with him.

We set off from South Wales Friday lunch time, slightly late because JHD missed my junction on the motorway, again! He never remembers where I live, so instead of leaving at 1230 we left at 1315. JHD, Lance and myself made our way from South Wales via the M4, M5 and M6 finally arriving in  Cumbria and the Lake District. When we left my house  the TomTom said it would take us 5.5 hours to reach our destination, in reality though, with Friday afternoon traffic it took us just over 7 hours and we arrived at our digs at around 2030. We had a couple of beers in the bar and a game of pool and we got a relatively early night.

We were up at 0700 Saturday morning, had some breakfast and made our way to the mountain. We started our climb at around 915. The mountain stands at 978 metres high (3208 feet) and the first 400 metres were very very steep and very very difficult. after that though despite it getting colder and windy the climb eased out and we ascended to the top in just over 2 hours. Here are some pictures from the summit:

Me (left) JHD (Right)  View from the top View from the top Lance Thomas at the top

As you can see there are some amazing views from the summit and despite how I struggled up the first half the climb it was well worth the effort.

The terrain on Scafell Pike is very rocky and after spending about 30 minutes at the summit taking photo’s and admiring the views we began our decent. The rockiness of the terrain meant that we had be as careful the descent as we were on the way up and it took us about 1.5 hours to down, we got back to the car with a feeling of satisfaction and achievement at around 1445 and made our 6 hour drive home. I had a great time and intend to go on a few more training climbs before JHD heads off to Africa in the New Year.

There are two reasons for this post. I wanted to tell you about my weekend and I also wanted to raise awareness for my friends cause and challenge. If you would like to give a donation please use this link; http://www.justgiving.com/JustinTreksKilimanjaro if you can’t give but would like to show you support please retweet this post on twitter on post on facebook.

What I Did This Weekend

About two weeks ago my good friend and former colleague Justin Hostettler-Davie -JHD (Blog | Twitter) asked me via my wife on  Facebook if I fancied climbing Scafell Pike, the highest peak in England, with him on Saturday the 16th October and for some reason I said OK why not.

photo (4)

Justin is training to climb Kilimanjaro in the new year (January 2011) to raise money for Velindre Cancer Centre. You can read more about Justin’s challenge and make a donation if you want to here. He has climbed several mountains in preparation for his challenge including Cadair Idris, Pen Y Fan, and Snowden and this Saturday we attempted Scafell Pike he has been accompanied by several friends over the course of his training, but Lance Thomas (No Blog | No Twitter) has been on most of his training climbs with him.

We set off from South Wales Friday lunch time, slightly late because JHD missed my junction on the motorway, again! He never remembers where I live, so instead of leaving at 1230 we left at 1315. JHD, Lance and myself made our way from South Wales via the M4, M5 and M6 finally arriving in  Cumbria and the Lake District. When we left my house  the TomTom said it would take us 5.5 hours to reach our destination, in reality though, with Friday afternoon traffic it took us just over 7 hours and we arrived at our digs at around 2030. We had a couple of beers in the bar and a game of pool and we got a relatively early night.

We were up at 0700 Saturday morning, had some breakfast and made our way to the mountain. We started our climb at around 915. The mountain stands at 978 metres high (3208 feet) and the first 400 metres were very very steep and very very difficult. after that though despite it getting colder and windy the climb eased out and we ascended to the top in just over 2 hours. Here are some pictures from the summit:

Me (Right) JHD (Left)  View from the top View from the top Lance Thomas at the top

As you can see there are some amazing views from the summit and despite how I struggled up the first half the climb it was well worth the effort.

The terrain on Scafell Pike is very rocky and after spending about 30 minutes at the summit taking photo’s and admiring the views we began our decent. The rockiness of the terrain meant that we had be as careful the descent as we were on the way up and it took us about 1.5 hours to down, we got back to the car with a feeling of satisfaction and achievement at around 1445 and made our 6 hour drive home. I had a great time and intend to go on a few more training climbs before JHD heads off to Africa in the New Year.

There are two reasons for this post. I wanted to tell you about my weekend and I also wanted to raise awareness for my friends cause and challenge. If you would like to give a donation please use this link; http://www.justgiving.com/JustinTreksKilimanjaro if you can’t give but would like to show you support please retweet this post on twitter on post on facebook.

Wednesday, 13 October 2010

TSQL Tuesday Misconceptions in SQL Server

I haven’t written a post for a TSQL Tuesday for a little while and I know that I’m late getting this one out too. This month’s party is being hosted by Sankar Reddy This topic  I think, maybe expect, really I know, that this topic has been covered already, I know that the Steve Jones has posted on this misconception already today and Paul Randal (Blog | Twitter) in his misconceptions series back in April covered this too much better than I have here so go and check out Paul’s post . The misconception I will look at here is - SQL Server will truncate the the transaction log after a full backup when the database is in full recovery mode. After my blog post around recovery models earlier today I think this maybe quite apt for me.
TSQL2sDay
The misconception I am going to talk about here is SQL Server truncates the log of a database in full recovery mode when a full backup is taken. It doesn’t.
This one of the biggest problems I come across, a database in full recovery mode and regular full backups being taken but  no transaction log backups being taken. The log grows and grows and eventually fills up all drive space.
I tested this on a SQL Server 2000 instance earlier today and it holds true in that version too. SQL Server does not truncate the log when a full backup of a database is taken in full recovery mode, although I believe in versions gone by it may have been the case that a full backup truncated the log. If you know the exact version please leave a comment.

The Proof

I will create a database for the purpose of this test called demodb and then create a table in that database called t1 that will hold some data.
CREATE DATABASE demodb
GO

CREATE  TABLE t1 ( id int, amount int )
go 





Firstly I will take a full database backup, the reason for backup is to ensures the database is in Full recovery mode and not in psuedo-simple mode.


BACKUP database demodb to disk = 'C:\BACKUP\demodb.bak'



If I then run a DBCC LOGINFO we can see that 1 of the 2 virtual log files (VLFs) are are active, they have a status of 2. I will cover VLF’s and the circular nature of the transaction log in a later posts, but for now DBCC LOGINFO shows some information on VLFs. a status of 2 means the VLF  is active, a status of 0 means that it is inactive or has been been truncated and can be used again.


TST1


I will then run some inserts on my table, all of which will be logged in the transaction log:


DECLARE @i INT
SET @i = 0

DECLARE @j INT
SET @j = 100000

WHILE @i < 10000

BEGIN
INSERT  INTO dbo.t1 ( id, Amount )
VALUES  ( @i, @j + @i )
SET @i = @i + 1
END
SELECT  GETDATE()



Running the DBCC LOGINFO command again,  you can see from the below diagram the log has grown and the number of active VLF’s has increased, I now have 103 VLF’s in my log and 102 are active, this means they cannot be re-used until they are marked as inactive and truncated.


TST2



I will then run another full backup of my database, to prove that a full backup of a database in full recovery mode does not truncate the log.


backup database demodb to disk = 'C:\BACKUP\demodb2.bak'



Running DBCC LOG info shows that the log has not been truncated by the full backup, the same number of VLFs are still active with a status of 2.


dbcc loginfo


The following results show that we still have 103 VLFs with 102 with a status of 2 – active.


TST3


If we then run a LOG backup and then run the DBCC LOGINFO command we can see that the log has been truncated and more of the VLF’s have been marked as inactive with a status of 0 and can now be reused.






backup log demodb to disk = 'C:\BACKUP\demodblog.trn'
GO
dbcc loginfo


TST4 


You will see that I still have 103 VLFs (more on that later) but now only one of those is active with a status of 2. The rest have been truncated and can be reused in the log. I hope that is helpful. I have rushed this post to get it out in time, I didn’t realise it was that time again until a couple of hours ago, if you find any inaccuracies in the post please let me know in the comments.

Tuesday, 12 October 2010

SQL Server Recovery Models

A SQL Server database can be in one of three recovery models. FULL, BULK_LOGGED and SIMPLE. The recovery model you choose for your databases will  affect your restore plans in the event of failure or need to restore, the recovery model you pick will also influence how you mange the transaction log of your database. So you need to choose carefully.

Full

The full recovery model offers your database the most protection. Full recovery mode means all database operations are fully logged. It allows point in time and point of failure restores. It does come with some overhead though, you need to implement both Full and Log backups as part of your backup plan. You need the transaction log backups for two reasons. 1. Transaction log backups allow you to perform point of failure or point in time restores. 2. If you don’t take transaction log backups of your database when its in Full recovery mode then your transaction log will grow uncontrollably and will eventually use up all available drive space. In fact this is one of the biggest problems I have seen in my  experience, people having a database in Full recovery mode, taking regular full backups but not taking regular log backups, they only notice the problem when they run out of disk space. By backing up the log you allow the inactive portions of the log to be truncated and reused.

Bulk Logged

Bulk Logged recovery mode is not dissimilar to the Full recovery mode, Transaction log backups still need to be an integral part of your backup plan. The difference between bulk logged and full is that certain bulk operations are only minimally logged when a database is in ‘Bulk Logged’ mode. Operations such as CREATE INDEX, SELECT * INTO, BCP, BULK INSERT are only minimally logged, according to Books Online (BOL) “ Minimal logging involves logging only the information that is required to recover the transaction without supporting point-in-time recovery.” This means that if your database crashes during one of these minimally logged operations and you need to restore you will not be able to restore to a point in time.

Simple

The main advantage of the simple recovery mode is that you do not need to worry  about managing the transaction log. In fact you are not able to take transaction log backups when a database is in Simple mode. The obvious disadvantage then is that you are not able to perform a point-in-time restore when a database is in simple mode which may not be appropriate for OLTP production systems.

What do I Use?

Well in true DBA fashion, it depends. I will generalise a little here, usually for production OLTP databases I go with the full recovery model, these are business critical systems that I need to be able to recover point of failure or point in time. In this mode I will use Full backups combined with transaction log backups (differentials if needed.) on a schedule dictated by the business and what it needs in terms of recovery time. I like to copy the log backups to a different server and write the full backup to tape and store off-site. I do use the Simple recovery model on certain production systems occasionally, mainly for databases where the data remains static or does not change then I tend to go with simple with because that is usually enough and removes the need for me to manage the log.

What to consider when choosing a recover model

1. How much data loss, if any, is acceptable.

2. How often does the data change.

Setting the Recovery Model

When you create a database,  the newly created database will get it’s recovery model from the Model database.

My very simple CREATE DATABASE statement below creates a new database called ModRecTest:

CREATE DATABASE [RecModTest]
GO



The following query selects from sys.databases and selects the recovery model of both the Model database and the newly created RecModTest database:



SELECT  Name AS 'DBname', recovery_model_desc
FROM sys.databases
WHERE name = 'Model'
OR Name = 'RecModTest'

GO



Here are the results:



Img1



As you can see the recovery model is the same for both databases.



If you would like to change the recovery model of your database you can do this from object explorer in Management Studio by right clicking on the database in question selecting <Properties> move to the <options> tab and you can change the recovery model by choosing a different option from the drop-down list <Recovery Model> and select <OK> you can also run TSQL:



ALTER DATABASE [dbname] SET RECOVERY BULK_LOGGED



Is your Database Really in Full Recovery?



It is worth noting here, even though sys.databases tells me that my database is full recovery mode it is not in technically in ‘Full’ recovery until a Full database backup is taken. You can prove this in several ways:



1. If you try to run a transaction log backup against your database you will get an error saying that no full backup exists:



BACKUP LOG RecModTest TO DISK = 'C:\DATA\Backup\RecModTest.BAK'


You will get an error:



Img2



2. Another way to see if your database really is in full recovery mode is to use  the following query:



SELECT d.Name, d.recovery_model_desc, dr.last_log_backup_lsn 
FROM sys.databases d
INNER JOIN sys.database_recovery_status dr
ON d.database_id = dr.database_id
WHERE d.database_id = db_id('RecModTest')



Now if this query returns values similar to the following for your database then, even though it says it is Full recovery the lack of a value in the last_log_backup_lsn field means that a Full backup does not exist and the database is still in auto truncate (pseudo-simple) mode.



Img4



If you run a FULL database backup and run the same query again, you will see you get an LSN in the last_log_backup_lsn field. The full backup kicks off the log sequence chain and SQL Server knows to expect log backups.



BACKUP DATABASE RecModTest TO DISK = 'C:\DATA\Backup\RecModTest.BAK'


Img5 



If you create the database in simple mode, take a full backup and re-run this query you will see that the last_log_backup_lsn field is still a NULL value. This means that SQL Server does not expect log backups (It won’t allow them on a database in Simple mode) and the database in auto-truncate mode.



USE master 
GO

DROP DATABASE RecModTest



CREATE DATABASE RecModTest
GO
ALTER DATABASE RecModTest SET RECOVERY SIMPLE


Then run a full backup:



BACKUP DATABASE RecModTest TO DISK = 'C:\DATA\Backup\RecModTest.BAK'



Then re-run the above query and we can see that the last_log_backup_lsn  field is still NULL and therefore SQL Server is not expecting log backups and is auto-truncate mode.



Img3