| Author |
Topic |
|
lakhva
Starting Member
22 Posts |
 Posted - 2005-03-29 : 18:01:44
|
| Hi,i'm trying to restore production database on my development server but i got following error message."Modify file encountered operating system error 112(there is not enough space on the disk) while attempting to expand the physical file. Could not adjust the space allocation for file 'xyz_LOG'. restore database is terminating abnormally."Here is some more information.* I do have enough disk space. I don't know why its saying there is not enough disk space because i've around 2.55 GB disk space available and my backup file from which i'm trying to do this database restore (with .bak extension) is only 177 MB!I also checked database property of the production database from which I created this back up file. That database has datafile size 278MB and trasaction log file size 3277 MB.I'm new to sql server and trying to get this solved since last week but no luck. While trying to resolve this issue I am creating new problems every day with the existing databases (One of which i posted just 2 days ago here)! please help me out. |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2005-03-29 : 18:34:35
|
| 278MB + 3277MB > 2.55GBYou need to shrink your transaction logs on production, perform a backup, and then try to restore THAT backup on development.You may also need to set the recovery model of your database to Simple before you shrink your database if it's not already at simple.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
 |
|
|
lakhva
Starting Member
22 Posts |
Posted - 2005-03-29 : 20:53:21
|
| Hi Michael,I will give it a try and let you know tomorrow. Just a basic question befor that. That database has datafile size 278MB and trasaction log file size 3277 MB.Then Why the size of its back up file(file with .bak extension from which i'm trying to do this restore) is only 177MB then? Did I not back up it up correctly at the first place? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-03-29 : 21:17:48
|
| Backup files only contain allocated data pages, plus the log transactions made since the backup first started. Your database files have significant unused space in them, and your transaction has probably never been backed up, and contains many old transactions that have already been committed.If you will not being backing up your transaction log regularly, I'd suggest changing the recovery model on the database to Simple. This will truncate the log after a checkpoint operation, and will keep it from growing out of control. |
|
|
|
kish
Starting Member
45 Posts |
Posted - 2005-03-30 : 07:48:00
|
| You may even detach the .mdf file from your original server and try this :sp_attach_single_file_db [ @dbname = ] 'dbname' , [ @physname = ] 'physical_name'This will automatically create a new log file for you.--Kishore |
|
|
|
VijayMallapur
Starting Member
4 Posts |
Posted - 2005-03-30 : 12:22:48
|
| Did your production server is SQL server 2000 running on the Win 2003. In some cases it shows backup completed succesfully but the its of a bak file will be less as comlared to the MDF and LDF size. Please Check the the Event whether its registering the message Backup completed Succesfully in Aplication Log "Database backed up: Database: tt, creation date(time):" If so then If possible then try to restore on the production server with another DB name. If its not working fine then there are some patches available for such problems on microsoft site Just check and apply the patches.If you are taking the backup on a disk drive and copying this file to your test server and restoring there then please check the file system on yor test server it the Source server is a NTFS then the your test server should be on the NTFS I gone through the same problem where my one sytem was on NTFS and another was on the FAT. The machine on FAT was refusing to expand the log file while restoring the database. So the drive on which the mdf and ldf are kept should be NTFS.If you want to shrink the log file then try with thisUSE UserDBGODBCC SHRINKFILE (DataFil1, 7)GO |
|
|
|
lakhva
Starting Member
22 Posts |
Posted - 2005-03-30 : 21:25:52
|
| I changed database model to simple and then shrink the database. That reduced the size of database. After that I did the back up again and I was able to successfull restore it to my development website.This forum has been very helpful. My thanks to everyone who gave me their valuable feedback. |
|
|
|
lakhva
Starting Member
22 Posts |
Posted - 2005-03-31 : 03:10:42
|
| Hi,There is a catch here. After I did the restore there was not any error message and now I do see all the database tables and stored procedures restored to my development database from the production server. But None of the ASP pages are working. Whereever there is a call to the database by a stored procedure i'm getting the follwing error message.Microsoft OLE DB Provider for ODBC Drivers error '80040e14' [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored procedure 'storedproceure_name'. I did this restore with my user name and password. I thought it might be related to permission issue so I also changed database owner to "SA" but still no change. I"m still getting the same error message. Any suggestios? |
|
|
|
lakhva
Starting Member
22 Posts |
Posted - 2005-04-01 : 00:19:29
|
| Default database of the ASP account was master and that was the problem. I changed the default database to my user database and Its working perfectly now. |
|
|
|
jpatti
Starting Member
1 Post |
Posted - 2006-07-28 : 14:04:49
|
| Please don't reply to me here, I only joined the forum to post to this one thread.I built a site on a development server. Then I moved the SQL server database by backing it up on the development server and restoring it on the production server. And none of my stored procedures worked anymore.I checked everything you could imagine... that the stored procedures ran via Query Analyzer, that the dbo owner was OK, that the public group had permission to every individual stored proc...I spent an entire night and day, pulling an all-nighter, trying to figure this out and Googled everything I could think of. Just been one of those typical days where you curse Microsoft while pulling out your hair. And then I found this thread.It never would've occured to me that iusr_machinename account had to have it's default pointing to my database - that's specified in the connection string after all. I would NEVER have thought of this. Thank you so much for posting your solution. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-07-29 : 04:41:21
|
| Welcome to SQL Team jpatti!Excellent!!! "Please don't reply to me here,"Damn, that's one of those "Read the question first" things they tell you in school, isn't it!!Thanks for taking the trouble to join in order to tell us. Glad it helped, and hurry back if you have any further questions!Kristen |
|
|
|
soulhacker
Starting Member
1 Post |
Posted - 2012-03-29 : 08:47:20
|
| MODIFY FILE encountered operating system error 112(there is not enough space on the disk) while attempting to expand the physical file. could not adjust the space allocation for file'MDF_Log'. RESTORE DATABSE is terminating abnormally.i am using Microsoft SQL Server 2000 and my database file size is 08mb but mdf_log file is over 40gb i am so afraid how to decrease this size and how to successful restore my database.ahmad |
|
|
|
|
error 112 there is not enough space on disk