• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 280
  • Last Modified:

Disk quotas can be overruled by SQL

Hello,

I have a little problem with my disk quotas.
My quotas (hard) work fine in normal use. When a quota is reached, nothing can be added using the windows explorer.
The problem is with a SQL backup. Users store sometimes a SQL backup on their network shares, this is an allowed company policy.
Now when a user takes a backup in SQL directly to his network share the quota doesn't work, with other words SQL keeps writing data to the share until its finished.
With as result that some users have a quota of 300% and more.
Is there a way to counter this? (without the use of filescreens)

Info: W2k3 R2 x64 with file resource managment role. hard quota's on username.

Thanks for the reply's
0
NR_EIS
Asked:
NR_EIS
  • 2
2 Solutions
 
Chris MConsulting - Technology ServicesCommented:
Hi,
Disk Quotas work by checking file properties before a file (or files) get written on the disk.
If the file size does not exceed the limit, windows will allow the write.

On the contrary, an SQL backup starts with zero bytes of a file and writes inside the file hence growing it till when the backup completes.

This means that disk quotas definitely will not work here.

I suggest that you setup a different backup server with a backup drive which definitely sets a limit to the size of backups on this drive.

Secondly, ask to modify the company policy as far as database backups are concerned to have the backups only written on these new backup drives either on the same server or another dedicated backup server.

All the best,
Chris Musasizi.
0
 
RimvisCommented:
Hello NR_EIS,

Backup file is written under permissions of SQL server process, not the user who initiated backup. So disk quatas may not apply in this case.

Regards,

Rimvis
0
 
NR_EISAuthor Commented:
Changing the company policy it is then. And making a file screen with bak extension to be sure
Thanks for the replies
0
 
NR_EISAuthor Commented:
No solution possible to limit a sql backup at the hand of a disk quota
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now