Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-12-11 : 09:46:30
|
| Patrick writes "Part IWe are running SQL Server 7.0 SP3 on Win2000 SP2. When I attempt to backup a database, it terminates with the following error: BackupMedium::ReportIoError: write failure on backup device D:\MSSQL7\BACKUP\CMGMail'. Operating system error 1453 (Insufficient quota to complete the requested service.)I have searched google groups for this, as well as MSKB; all I see are references to get SQL Server SP3 (which we have done), or to disable quotas on the disk (we are not running quotas). There is plenty of memory to go around here - the server has 2GB RAM, and only 1.6GB is allocated to SQL Server (the 'set working set size' option is enabled). Thus, Win2K has 400MB RAM to play with. I don't think this is a resources issue. Also, the disk is a RAID5 array of SCSI disks, with 90GB free space. Part III have one more thing to add, last night we did a UNC convention in the backup script instead of "D:\DBa\BackupFolder\etc" and it worked! How bizarre is that!? So in an example:We replaced:"D:\DB\BackupFolder"with\\Servername\d$\DB\BackupFolderM$ Response to the first part (I) and my response to their questions are below:Answers to your questions:1. Make sure the database is clean by running DBCC CHECKDB. Done with 'FEATURECREATURE' and made no difference!2. Run hardware diagnostics to make sure that there are no hardware issues. Did this on the controller card and ALL checks out fine! All other hardware checks out fine too! 3. Check the accessibility and condition of the device in question by trying to backup a sample database like Pubs or Northwind. 'PUBS' BU fine, 'NORTHWIND' came back with the same error message as some of the other DBs. Which is as follows: 2001-12-04 14:52:12.28 kernel BackupMedium::ReportIoError: write failure on backup device 'D:\MSSQL7\BACKUP\Northwind_Test'. Operating system error 1453(Insufficient quota to complete the requested service.).2001-12-04 14:53:45.93 spid27 DBCC CHECKDB (Northwind) executed by BLOOMINGTON\PATRICKMIDDLETON found 0 errors and repaired 0 errors.2001-12-04 14:54:23.25 kernel BackupMedium::ReportIoError: write failure on backup device 'D:\MSSQL7\BACKUP\NORTHWIND_TEST'. Operating system error 1453(Insufficient quota to complete the requested service.). 4. If any problem is identified by the above, work on correcting the problem first and then re-execute DBCC CHECKDB to ensure that no other damage was caused. N/A5. Are there any other applications on the same box? If yes, please provide details. The only other apps are Office 2000 (OUTLOOK for mail), ActivePerl Build 623, CompuWare ECOTools Agent 7.3.1, IBM AS/400 Client Access Express for Windows, Intel PRO Ethernet Adapter and Software, LiveReg (Symantec), LiveUpdate 1.6 (Symantec), PCAnywhere 10.0, Windows 2K Hotfix pre-SP3 (Q299553), Win2K SP2, WinZip 6. Could you please post the SQLDIAG report for this SQL Server as well as the NT system and application event logs in the .evt format? Attached are the files you have requested. 7. Have you set both the min server memory and max server memory to the same value (1.6 GB)? Do you mean to just set the memory at a 'FIXED' memory size of 1.6 gb, as don't the min / max memory settings have to be different? YES, no differenece!8. Is Log Shipping and/ or any replication setup on that server? If yes, please provide details. NO9. Could you also try backing up the databases to a different drive and see if you still get the same error message? 'C'=Yes; 'L'=Yes; over network=worked fine10. Do you get the error all the time you backup any database or only intermittently? YES on the DBs that will NOT BU, some DBs will backup w/o any problems!11. I' |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2001-12-11 : 12:30:10
|
| I am totally shooting in the dark here. So don't be surprised if this is wrong, but try it anyway.Go over the NT/2000 file permissions of ALL of the accounts that perform the backups and that use or have rights to SQL Server. Check the permissions of ALL the NT users that have rights or restrictions to the files and folders for both the backups AND the database files, including the log. It may not be that a quota exists, but somehow someone or something may have put file-level restrictions on files or folders. |
 |
|
|
pmiddleton@nuttyweb.com
Starting Member
2 Posts |
Posted - 2001-12-12 : 16:37:34
|
| OK, let me add a little more spice to this! Today, we tried and succeeded backing up EVERY database after a successful transaction log backup! But wait a couple of minutes and try some of these same databases and the error pops up! Now we have deduced that if there has been any data changes or manipulation to any database after a successfull t-log backup then it is those databases that will fail!This is a 24x7 production box with constant activity! Any thoughts on this?Patrick MiddletonMCSE / MCDBA |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2001-12-12 : 17:18:46
|
| Sounds like you have "Truncate log on checkpoint" set for each database. This invalidates transaction log backups. You can find in under the database properties on the Options tab, using Enterprise Manager. |
|
|
|
pmiddleton@nuttyweb.com
Starting Member
2 Posts |
Posted - 2001-12-19 : 17:18:34
|
| No that is not the issue. After spending a week with Dell, by their recommendation we unchecked both, "Reserve physical memory for SQL server", located on the server properties page and "Boost SQL server priority on Windows"! Now what this has done, I am not sure, but our problem has miracously disappeared!Thanks for your comments! If anyone can explain the functions we turned off, I would greatly appreciate this! I actually know what "Boost SQL server priority on Windows" does, but why does unchecking this have anything to do with the problem!?Patrick MiddletonMCSE / MCDBA |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2001-12-19 : 17:30:43
|
| Perhaps Windows and SQL Server were having thread or resource contention issues, because SQL Server was being too greedy (no! not a Microsoft product!) and not releasing them to the operating system. Clearing those settings would give more control to the operating system. |
|
|
|
|
|
|
|
|
Error 1453 on SQL 7 Sp3 on Win2K Sp2