June 27, 2009 at 1:40 pm
Hi,
I don't know whether you faced this problem or not.
We have a production server that was configured for log-shipping and logs being transported to DR server for every 15mints. Yesterday, we added extra storage to PROD server and I resized data files. We did this because we have an issue with Disk I/O due to heavy disk fragmentation.
Now log-shipping is failing and I couldn't able to restore log files or differential backup to DR server.
Below is the error when I'm trying to restore log file:
RESTORE LOG is terminating abnormally.
Could not adjust the space allocation for file 'DB_LOG'.
MODIFY FILE encountered operating system error 112(There is not enough space on the disk.) while attempting to expand the physical file.
Error while restoring Differential file:
RESTORE DATABASE is terminating abnormally.
Cannot apply the backup on device LiteSpeed 2005 backup file to database 'PROD'.
Could anyone suggest me what could be the reason? I know that PROD and DR servers configuration should be almost similar. Is that compulsory?
June 27, 2009 at 2:04 pm
You will need to add the extra disk space to the DR server as well. The DR server needs at least enough space to be able to support the size of the database files.
Increasing file size is recorded in the transaction log, so when you restore the log on the DR server the database files will be expanded there as well.
---------------------------------------------------------------------
June 27, 2009 at 2:14 pm
Oh!.. my god .. I'm DEAD ... So, till we add extra storage to DR server, logshipping will be out of sync... I don't know what is going to happen in office on Monday.
June 27, 2009 at 3:30 pm
well, unless a disaster happens, nothing has gone wrong yet. 🙂
From the name of the file for which there is not enough space it is the transaction log? Can you shrink this file down on the primary to a size that will fit on the DR server and re-initialise log shipping.
You need to check data files will still fit as well. Look to see if there is any space you can reclaim on the DR server (keep fewer transaction log backups on that side perhaps)
the two servers in the pair do need to match as well as possible though, and certainly in space allocated for the database files.
---------------------------------------------------------------------
June 27, 2009 at 7:17 pm
Yes. It is a transaction log file but the problem is not only with the log file but also with data files. I shrunk the log file on the primary but it didn't worked as data files are not shrunk.
Just to be on safer side till I talk to the management and till we come up with a plan for DR server, I will shrink all database files back to normal and get this log-shipping going.
I thought I will get appreciation on Monday but it is looking like they will blame me like anything. I will let them know slowly after appreciation.:-D
June 28, 2009 at 4:22 am
you say you have heavy disk fragmentation? If this is at the drive level invest in a tool such as diskeeper to defrag the disk. If it is fragmentation within the database then obviously reindex. MAybe you won't need the extra disk immediately.
If you keep the extra disk take the opportunity to do a one off growth of the database to a size that will support normal use of the database, so incremental increases (or shrinks) are not required as they fragment the disk.
Also if you are applying all your transaction logs to the DR site you don't need to apply the differential backups as well.
---------------------------------------------------------------------
June 28, 2009 at 9:32 am
The problem with our server is we have database files and backup files in the same disk. As backups (70 GB after compression with litespeed) are created and deleted, it won't work for us even if we do defragmentation. So, we decided to segregate all of them to their respective disks.
July 6, 2009 at 6:00 pm
I saw that you mentioned that you have backup and your database on the same drive. I am curious why you think Diskeeper will not work for you.
Diskeeper works automatically so it is defragmenting in real-time. In addition to defragmenting your database so it can be accessed faster, Diskeeper will also defragment your other files so you can perform backups faster. (Files will take much longer to backup if they are in a number of fragments instead of one contiguous file.)
Since the Diskeeper runs in automatic mode and constantly monitors the processes on your server, this will not affect the performance of your computer. If you would like to test Diskeeper, you can download the 30 day trial at http://www.diskeeper.com
July 6, 2009 at 7:20 pm
You can do the following.
How many drives do you have on your DR Server? What is the free space available on each drive
What is the status of the database that is being logshipped on secondary \ DR Server, I mean is database is in restoring state or standby state?
If it is in standby state, bring the database offline, move the log file to drive where there is ample space, execute alter database statement so as when database come up online it picksup the new location of the log file, bring database back online and then start restore job.
If the database is in restore state, you can give a try , start DR Server in single user mode with trace flag 3608 and with -c option, then move the transaction log file to drive where there is ample space, execute alter database statement so as when database come up in restoring state it picksup the new location of the log file. Start sql server and then start restore job.
Hope this should solve your problem.
July 13, 2009 at 7:39 am
Thanks a lot for your replies.
Jake,
The reason why DiskKeeper will not work for us because we have SQLServer backups on the same disk where data & log files are residing. Because backups will be deleted and recreated, obviously there will be a huge fragmentation on the disk. If we do defragmentation today and will go worse in 1 or 2 days due to backups. This is how SQL Server was configured 5 years back but been working on this server from past 1.5 years.
Kalyani,
PROD & DR Server has 3 drives each [C:, D: & E:]. All database files and backups are on E: drive and the database status is in loading state. To resolve fragmentation problem, what we did is we procured new storage like a mini SAN and attached to the PROD server and now we have extra 5 drives. so, we moved each database file (3 data files and 1 log file) to each new drive (250GB) and tempdb to another drive. SQL Backups are still existing in 'E:' drive. We expanded database files to around 200GB. But the mistake that we did is we didn't thought about DR server and we didn't implement SAN to it. Because database files are now 200GB on PROD, DR server is also trying to resize database files on its server and will always fail due to insufficient disk space. So, at one transaction log it is getting stuck and going into 'loading' status. I had informed the same to the management and they are thinking about DR server now. For now, I compressed PROD database files back to normal and fixed logshipping issue.
July 14, 2009 at 5:58 pm
Thank you for the reply.
Actually, Diskeeper will work for you since it is defragmenting all of the time when running in automatic mode. (Diskeeper runs in automatic mode when installed with it's default settings.) When in automatic mode, Diskeeper will monitor the resources on your server. If the server is under heavy load such as when you are performing a backup, Diskeeper will pause until more resources are available. Once the resources become available, Diskeeper will automatically start defragmenting. Therefore, you are getting instant constant defragmentation whenever there are spare resources which are not in use. This automatic monitoring of resources is compared to the @home screen savers that use unused cycles to calculate folding proteins. It should also be noted that if you do not defragment your server, this server will become more and more fragmented resulting in further performance loss for this server.
In your case, since you would be getting instant defragmentation, this should also speed up your backups. I would suggest that you download the free trialware version at http://www.diskeeper.com. The software is fully functional for 30 days and is not crippled in any way. Once this is installed, you can check the reporting information to determine how Diskeeper is working for you.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply