Edward Dortland

general recent conversations

Discussion on answer by Edward Dortla…

Imported from a comment discussion on dba.stackexchange.com/qu...

Edward Dortland

Aug 29, 2017 15:59

mmkay. Well glad it worked out for you.

Edward Dortland

Aug 29, 2017 15:59

I', at loss on how many different users you have and what their roles are and what you want them to do. can you clarify that by updating your question please?

Edward Dortland

Aug 29, 2017 15:59

you are getting that message because the user running the code hasn't got enough permissions to change the permissions. Please update your question with all the relevant information. What users have you got, what do you want each user to do.

Edward Dortland

Aug 29, 2017 15:59

I updated the answer giving you a example using your own scenario that you provided in the comments. If you still need help it might be more clear for other people as well to edit your question and include your exact steps and code in your question.

Edward Dortland

Aug 29, 2017 15:59

Okay the user that in the end is going to use the Asym key has got to have control permission. So when you dynamically create the Asym key, you also have to grant control permission on that Asym key to all the DB users that are going to use the Asym key.

Edward Dortland

Aug 29, 2017 15:59

okay in that case if user A has created the Asymmetric Key and user B is creating the Symmetric key and encrypting it with the Asymmetric key, User B would need Control permission on the Asymmetric key. GRANT CONTROL ON ASSYMETRIC KEY::asym_CommonKey TO userB

Edward Dortland

Aug 29, 2017 15:59

Did you create the asymmetric key with another user? Was that by accident or intentional?

Edward Dortland

Aug 29, 2017 15:59

That is because the first key that you create is a Asymmetric key and not a Symmetric key. So you have to give permission to the dbuser to altrer asymmetric keys. ALTER ANY ASYMMETRIC KEY.

Discussion on question by user3224291…

Imported from a comment discussion on dba.stackexchange.com/qu...

Edward Dortland

Aug 29, 2017 11:06

Can you rule out SQL Server by just copying a large file from the same location as the backup files to the same location as the mdf and ldf files. (and if these mdf and lfd are on different disks, just perform the test two times..)

Edward Dortland

Aug 29, 2017 11:06

does the SQL Service account have "perform volume maintenance tasks" permissions and when you start the restore, are the mdf and ldf files of the database that you are restoring the database over a representative size? or do they need to grow a lot before the backup that you are trying to restore "fits" ?

Edward Dortland

Aug 29, 2017 11:06

can you post: The script used to create the backup. The script used to restore the backup and the waitstats of the server while it has been hanging for a long time. with for example this script: sqlskills.com/blogs/paul/…

Discussion on question by SteLoe: Wei…

Imported from a comment discussion on dba.stackexchange.com/qu...

Edward Dortland

Aug 29, 2017 09:23

repro on: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64) Jul 9 2008 14:17:44 Copyright (c) 1988-2008 Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1).

Discussion on answer by Thomas Kejser…

Imported from a comment discussion on dba.stackexchange.com/qu...

Edward Dortland

Aug 29, 2017 07:34

yes there are, Thomas has a nice example. But it gets more complex quite quickly.

Edward Dortland

Aug 29, 2017 07:34

@JonSeigel Okay, I just wanted to point out that the reality is that you can hit last page hot spot problems on far lower numbers. People say edge case, extreme volumes. etc. etc. But the need for randomizing key inserts or partitioning is on a far lower rate then one might expect. (Unfortunately, since I agree with most cons against GUIDs)

Edward Dortland

Aug 29, 2017 07:34

@jonSeigel Although I agree with the general vibe on that care should be taken with implementation of GUIDs as clustered indexes.. the 1M rows/sec is way higher than the reality. I'm already hitting massive page latch contention on a 2 core machine doing 15k/sec inserts with 12 threads on a sequential clustered index..

The Heap™ – Consultancy ©®

General on- and off-site discussion for dba.stackexchange.com....

Edward Dortland

Jan 10, 2014 21:30

@bluefeet I guess that joke would have gone better if you could have seen my face. but thanks for answering anyway :-)

Edward Dortland

Jan 10, 2014 21:28

@bluefeet How many times a week do you wash? how many clothes per batch? Do you hang it outside to dry ? do you have a dryer? Do you use washing liquid or Powder? Do you seperate colour and whites, or just don't care? Does your back hurt? It's a pretty broad question..

Edward Dortland

Jan 10, 2014 21:22

hahaha

Edward Dortland

Jan 10, 2014 21:21

user32711, happens to be my lucky guy ;-)

Edward Dortland

Jan 10, 2014 21:21

Yes I know., pretty naive. but it's like the people asking for money on the street: Every 1 in 50, you give in and give the chap some money, even though you know most likely he is going to spend the money on drugs and not food...

Edward Dortland

Jan 10, 2014 21:18

@Kin Can we leave it on for just a second more.. I've writen a answer as an experiment.. to see IF a OP can be pursuaded...

Edward Dortland

Jan 9, 2014 21:42

@AaronBertrand I can understand, I noticed you had a fair share of it. ;-)

Edward Dortland

Jan 9, 2014 21:38

why is that an offtopic question?

Edward Dortland

Jan 9, 2014 21:38

A: Msg 4834 "You do not have permission to use the bulk load statement"

As I said in the comment, server level permissions are stripped the moment you use impersonation. There are 2 ways around this: The bad and quick way: Set your database thrustworthy to ON. It will get the job done. But if you don't fully understand the what this does, then my advise would be t...

Edward Dortland

Jan 9, 2014 21:32

@Lamak in heavy incremental insert load, the fragmentation works for you.

Edward Dortland

Jan 9, 2014 21:32

@Zane Ord do you mean, size? more space, less page density?

Edward Dortland

Jan 9, 2014 21:31

@Zane Why? Or do you mean clustered index scans?

Edward Dortland

Jan 9, 2014 21:28

@Phil he is talking about in memory latch contention on the last pages on very heavy incremental insert loads

Edward Dortland

Jan 8, 2014 17:52

@swasheck Give it a chance. Try and learn in depth what's good and bad about it and come back and teach us ;-).. And test everything yourself! gotta go.

Edward Dortland

Jan 8, 2014 17:46

@swasheck No problem, It didn't perform well in the projects I worked with it. But to be honest, there were to many unknown environment variables to make any conclusions..

Edward Dortland

Jan 8, 2014 17:36

@swasheck nope not with heavy performing systems.. sorry

Edward Dortland

Jan 8, 2014 17:31

I think 42MB datatypes would be perfect for Hitachi SAN based DBs, Wouldn't you agree @swasheck?

Edward Dortland

Jan 8, 2014 16:33

@swasheck regarding the " the impression that storage team gave us was that if we didn't fill that hitachi page with data (from sql server), something else would" that's not true. Once a LUN has 42MB Allocation page allocated. Then that is for the LUN. Nothing else can write to it..

Edward Dortland

Jan 8, 2014 16:31

@AaronBertrand +1

Edward Dortland

Jan 8, 2014 16:30

@swasheck The other option would be to have the LUN sit on a RAID GROUP that is made out of more disks.

Edward Dortland

Jan 8, 2014 16:29

@AaronBertrand Aaah, sorry.

Edward Dortland

Jan 8, 2014 16:29

@swasheck Well, you should test that, That might be the case with instant file initialization. It just came to my mind. If you don't use IFI, then all 8 files are allocated right from the start. so if you have 8 1 GB files. the LUN will instantly consume 8 GB on the SAN. Those 8 GB are nicely devided in 42MB blocks across all available RAID groups.

Edward Dortland

Jan 8, 2014 16:27

@AaronBertrand no, it's not about the space reservation. It's about the fact that Hitachi allocates space in 42MB blocks. and each 42MB block goes to a dedicated raid group. In this case, a 7 disk RAID5 group.

Edward Dortland

Jan 8, 2014 16:25

@swasheck So, you add a 42 MB temp table. it will allocate a extent to file 1, file 1 is on LUN1 and LUN1 will allocate a 42MB Hitachi ALLOC page on a RAID GROUP.. Now you add the second extent to file2, This is on the same LUN, so this will just go in the just created 42MB page..

Edward Dortland

Jan 8, 2014 16:24

@swasheck Okay, so 8 files on 1 LUN. Think with me here for a second. If you have instant file initialization configured correctly. those 8 files are not allocating their total space on the SAN. They start doing that as you start using data pages..

Edward Dortland

Jan 8, 2014 16:21

@swasheck Okay, I'm just thinking, if you have instant file initialization turned on, and lets say you create those 24 (or 8 @aaron ;-) ) files on a single LUN. As you are adding data in a round robin fashion, Dynamic provisioning algoritm of Hitachi would still Allocate the first 42MB to the first RAID group, then the second 42MB to the second. etc.

Edward Dortland

Jan 8, 2014 16:18

@swasheck I'm wondering if 2 tempdb files on 1 LUN with instant file initialization correclty configured would help you? I think the instant file initialization would fight against you in this case..

Edward Dortland

Jan 8, 2014 16:17

@swasheck Okay, think about Tempdb. Not only could multiple files be important to fight against allocation page contention. On this storage system, you might need multiple files to make sure you are running your load across multiple RAID 5(7+1) groups.

Edward Dortland

Jan 8, 2014 15:49

@swasheck Could be worse, : RAID5 3+1 :-). But are you building a 1 application Database backend? Or a shared environment?

Edward Dortland

Jan 8, 2014 09:30

I know, people blog about it, but I still think it's beter to not help spread this DBCC option. ;-)

Edward Dortland

Jan 8, 2014 09:29

I answered this question, I left out How I corrupted a page using dbcc writepage. I'm wondering what people here think... Should I include it or not?

Edward Dortland

Jan 8, 2014 09:28

A: SQL Server Replication breaks up after doing DBCC CheckDB with Allow data loss on Primary

3 important pieces of information are missing to pinpoint exactly what went wrong in your particular scenario: What where the exact steps you took, from the moment you found a problem until the moment you discovered that replication didn't work? What was broken, what was repaired, what was lost...

Edward Dortland

Jan 7, 2014 11:00

@MartinSmith, nice info. Might be nice to post as a additional answer. thanks for explaining.

Edward Dortland

Jan 7, 2014 10:33

Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
Dec 28 2012 20:23:12
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

Edward Dortland

Jan 7, 2014 10:32