Author |
Topic |
BigMeat
Yak Posting Veteran
56 Posts |
Posted - 2009-10-22 : 16:14:53
|
HiI have a Win 2003 server running SQL Server Express 2008 SP1, I have a datbase which has been running fine for about 9 months. The .mdf file is 1.8Gb in size For the past 6 months I have been running a script every night to backup the datbase. THis has worked great, but all of a sudden my database fails to Backup. I have even tried to backup the database using SQL Server Management Studio Bacup Wizard but this also fails. The error I get is:System.Data.SqlClient.SqlError: Write "D:\MSSQL\...myDB.BAK" failed: 27(failed to retrieve text for this error. Reason:1815 (Microsft.SqlServer.Smo)I did some research and thought it might be a permissions problem for the SQL Server user on the File System. But I have given the SQL Server user accounf Full Control over the datbase and file location to which the backup is being placed.I dont understand why it has stopped backing up.... I have been pulling my hair out.... any help would be much appreciatedMany thanks in advance |
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
BigMeat
Yak Posting Veteran
56 Posts |
Posted - 2009-10-22 : 17:42:21
|
HiI checked the SQL Server Error Log and it says:"BACKUP failed to complete command BACKUP DATABASE myDbName Check the backup application log for detailed messages"Im not sure where the "backup application log" is?Any ideas? |
|
|
BigMeat
Yak Posting Veteran
56 Posts |
Posted - 2009-10-22 : 17:51:35
|
HiI have some bits of teh error in the log, it says:This error occures first: Error: 18210, Severity: 16 State 1Then an error of: Error 3041, Severity 16, State 1Does this help?Thanks in advance |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-10-22 : 19:06:04
|
to view the application log, click START, click RUN, type in EVENTVWR hit <ENTER>the error #s you posted above represent the error messages you posted 2 posts abovemake sure D: drive isn't full.make sure SQL Service account has permission on D: |
|
|
BigMeat
Yak Posting Veteran
56 Posts |
Posted - 2009-10-23 : 06:44:16
|
HiI have checked the event viewer and it says the above error message. I have got over 60Gb free on my D drive, however I have 500Mb of space on my C drive, woudl this affect the backup process?The only other thing I can think of was that I changed the "temp" and "tnp" folder location for my windows 2003 server. It was mapped to "systemroot" and I changed this to teh D drive. I have given my SQL Server User full permissions on this folder. As I thought it might be trying to right to thsi folder but didnt have permisions.Is there anything else I can check?Many thanks in advance |
|
|
BigMeat
Yak Posting Veteran
56 Posts |
Posted - 2009-10-23 : 06:50:55
|
Opps I also forgot to mention that I have other databases on the SQL Server and they all seem to backup fine, they are much smaller in size, about 300Mg each. The database im having problems with is 1.8Gb, could it be due to the size? Or maybe it might be creating a large temp file somewhere and it is running out of space whilst doing the backup? |
|
|
BigMeat
Yak Posting Veteran
56 Posts |
Posted - 2009-10-23 : 15:36:51
|
I was just wondering if anybody had any ideas on the above.Im pulling hair out on this oneMany thanks in advance |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-10-23 : 16:41:04
|
you can backup other databases to same location?1.8 GB is very small, not the problem.is location on D: drive read/write?tried making service account local admin on the machine?can you backup the db successfully to a different location?finally, run checkdb on the database in question |
|
|
BigMeat
Yak Posting Veteran
56 Posts |
Posted - 2009-10-24 : 09:22:30
|
Superb, thanks fro the above. I started by doing a CHECKDB and it returned the below. I then did a backup and it all worked fine.CHECKDB found 0 allocation errors and 0 consistency errors in database 'myDB'.Im not exactley sure what CHECKDB did but it seems to have fixed the problem. Thankyou so much for your help...... your a star!!! |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-10-25 : 12:43:15
|
Might be a good time to reboot the box. When was last reboot? Sounds like a disk or OS error, becuase running checkdb shouldn't have fixed it...Make sure disks aren't failing. By the way, if they are, a reboot could finish 'em off, so proceed carefully. |
|
|
BigMeat
Yak Posting Veteran
56 Posts |
Posted - 2009-10-25 : 12:56:54
|
HiI did a defrag of the drive and it took all night to complete. I did a reboot and everything seems ok now.Thanks again for all your help |
|
|
|