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 |
|
SQLSoaker
Posting Yak Master
169 Posts |
 Posted - 2011-11-30 : 10:49:13
|
| Hi.Database from our TEST system lies in D: 84.9 GB used 20.7 GB free. I've grabbed a .bak from our PRODUCTION system that I copied to the same server on the E: drive. Over 100 GB free space.When performing a restore on TEST from the .bak file I am getting a not enough disk space error:Exact message: There is insufficient free space on disk volume D:\ to create the database. The database requires 60613328896 additional free bytes, while only 54473646080 bytes are availableI've seen other posts that this might be a problem with the transaction log file on the PRODUCTION db that it needs to be shrunk and a backup needs to be created after it is shrunk.Is this the case? If so, how to shrink the transaction log from prod safely? |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-11-30 : 11:26:05
|
| How big is the dtabase on live. It will create files the same size as those in the database backed up.There are products you can get to get around this though - even use the backup file instead of creating the database files and only hold changes in the database - wouldn't go there though.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
SQLSoaker
Posting Yak Master
169 Posts |
Posted - 2011-11-30 : 11:34:37
|
quote:
Originally posted by nigelrivett
How big is the dtabase on live. It will create files the same size as those in the database backed up.There are products you can get to get around this though - even use the backup file instead of creating the database files and only hold changes in the database - wouldn't go there though.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy.
The .bak file is around 22 GB which is what I do not understand. It is saying I need over 60 GB free space to do the restore. The physical live DB file is 24.8 GB and the log file for live is 68.1 GB hence me asking if the log file is too big... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-11-30 : 19:37:42
|
quote:
Originally posted by SQLSoaker
Over 100 GB free space....... while only 54,473,646,080 bytes are available
Doesn't sound like you have 100GB free on the drive(s) the restoring database is being created onUnless you tell it otherwise RESTORE will restore the BAK file to the original file locations (of the Source Server)"The physical live DB file is 24.8 GB and the log file for live is 68.1 GB"You need 24.8 + 68.1GB free space to restore this database. The fact the BAK file is only 22GB is irrelevant, the database will be restored by creation of files matching the sizes of the original database from which the BAK was made - that's how it works I'm afraid ...You can shrink the source database, take a new backup, and then restore that. That RESTORE will create database files that are as small as possible.However, it is BAD PRACTICE to Shrink a database. That's not to say don't ever do it - but there are very few circumstances where it is a good idea. If the Source database is a Production System then almost certain that should not be shrunk."DB file is 24.8 GB and the log file is 68.1 GB"Log file is pretty big, relatively speaking. Probably a Transaction backup was not made in a timely fashion at some time, or a large transaction was created (e.g. by a large delete operation). But whether you should shrink the Production Database needs careful consideration; if the LOG file will immediately grow back (because it needs that space to do normal tasks during the week) then you definitely should not shrink it. |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-12-01 : 06:00:08
|
quote:
Originally posted by SQLSoaker
quote:
Originally posted by nigelrivett
How big is the dtabase on live. It will create files the same size as those in the database backed up.There are products you can get to get around this though - even use the backup file instead of creating the database files and only hold changes in the database - wouldn't go there though.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy.
The .bak file is around 22 GB which is what I do not understand. It is saying I need over 60 GB free space to do the restore. The physical live DB file is 24.8 GB and the log file for live is 68.1 GB hence me asking if the log file is too big...
The size of the .bak file doesn't make any difference. That's just the extents that are being used. The restore will create files the same size as the database had when it was backed up. If tehre is a lot of free space then the .bak file will be much smaller than the filesize.Read my previous post for what will happen and what you can do.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
SQLSoaker
Posting Yak Master
169 Posts |
Posted - 2011-12-01 : 09:53:44
|
Hi Kristen.Thank you for the reply. It has helped. I think shrinking a live DB is not a great idea, like you mentioned. It can cause some performance issues. What we really need to focus on is shrinking the size of the log file. There is no reason it should be so big with only a 22 GB DB. I've found some ideas online as to how we can accomplish this but I would love to get your input if possible. Is it a matter of going into that log file and deleting certain transactions that are, for example, still open. Perhaps an application crash caused these transactions to be there open but idle and this is causing the log file to be huge. Also, is it a possibility to get rid of the transaction log all together and just create a new fresh log. I am not sure of the affects that would have on the DB either. This would be ideal, to start from fresh. We would of course do this, if possible, during downtime where no transactions should be coming through. I am just worried about possible consequences of doing this in the first place.Any other input would be greatly appreciated.Thanks in advance.quote:
Originally posted by Kristen
quote:
Originally posted by SQLSoaker
Over 100 GB free space....... while only 54,473,646,080 bytes are available
Doesn't sound like you have 100GB free on the drive(s) the restoring database is being created onUnless you tell it otherwise RESTORE will restore the BAK file to the original file locations (of the Source Server)"The physical live DB file is 24.8 GB and the log file for live is 68.1 GB"You need 24.8 + 68.1GB free space to restore this database. The fact the BAK file is only 22GB is irrelevant, the database will be restored by creation of files matching the sizes of the original database from which the BAK was made - that's how it works I'm afraid ...You can shrink the source database, take a new backup, and then restore that. That RESTORE will create database files that are as small as possible.However, it is BAD PRACTICE to Shrink a database. That's not to say don't ever do it - but there are very few circumstances where it is a good idea. If the Source database is a Production System then almost certain that should not be shrunk."DB file is 24.8 GB and the log file is 68.1 GB"Log file is pretty big, relatively speaking. Probably a Transaction backup was not made in a timely fashion at some time, or a large transaction was created (e.g. by a large delete operation). But whether you should shrink the Production Database needs careful consideration; if the LOG file will immediately grow back (because it needs that space to do normal tasks during the week) then you definitely should not shrink it.
|
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-12-01 : 10:07:44
|
quote:
Originally posted by SQLSoaker
There is no reason it should be so big with only a 22 GB DB.
You can't make that assumption based on size alone. It may be that there are huge transactions, processed every day, that require that much LOG file-space.I agree its unlikely, but you don't want to shrink the Log file until you are sure that is not the case.If you were running in FULL Recovery Model (rather than SIMPLE) that would be easy to see because the size of the largest TLog backup file would correspond, roughly, to the largest log file usage.In the absence of that you would need to use Perf Monitor to "record" the used size of the log file over the course of, say, a week to see what the maximum size was. There are probably other ways - which may be easier to use / set up.If you decide to shrink the log you just need to use the appropriate shrink command and then check that it did indeed shrink to the size you asked - if not there is some open / stuck transaction; I would stop/start SQL Service at the next opportunity and then try the Shrink again and see if that fixes it before trying anything more "aggressive" to force it to shrink. |
|
|
|
SQLSoaker
Posting Yak Master
169 Posts |
Posted - 2011-12-01 : 10:21:17
|
Shrinking the log could cripple performance, no? If those huge transactions you mentioned are actually there and required, what does the shrink do to those?And shrinking cannot be done in a live environment without proper testing. And since I can't restore my test environment I'm kind of stuck here. Does the shrink command keep all transactions? If it deletes them and our front end application crashes, there would be major consequences.Thoughts?Thanks again, really appreciate it.quote:
Originally posted by Kristen
quote:
Originally posted by SQLSoaker
There is no reason it should be so big with only a 22 GB DB.
You can't make that assumption based on size alone. It may be that there are huge transactions, processed every day, that require that much LOG file-space.I agree its unlikely, but you don't want to shrink the Log file until you are sure that is not the case.If you were running in FULL Recovery Model (rather than SIMPLE) that would be easy to see because the size of the largest TLog backup file would correspond, roughly, to the largest log file usage.In the absence of that you would need to use Perf Monitor to "record" the used size of the log file over the course of, say, a week to see what the maximum size was. There are probably other ways - which may be easier to use / set up.If you decide to shrink the log you just need to use the appropriate shrink command and then check that it did indeed shrink to the size you asked - if not there is some open / stuck transaction; I would stop/start SQL Service at the next opportunity and then try the Shrink again and see if that fixes it before trying anything more "aggressive" to force it to shrink.
|
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-12-01 : 15:10:01
|
| SHRINK won't upset your transactions. However, if it shrinks the file, and its too small, the file will re-grow. That will fragment the physical file.The act of shrinking may fragment the indexes. So, for example, you rebuild the index and it is all nicely contiguous at the end of the file; optimal for best speed. Then you shrink the file and the index pages have to be moved to the start of the file, so all fragmented. (This doesn't apply to shrinking the Log file)If your Log file has been properly optimised, to keep the VLFs to a minimum etc., then shrinking it and letting it regrow is liable to undo that optimisation.The key is whether the space in the Log File is actually used at any time during the week, or not (e.g. it was a one-off expansion from some previous transaction a long time ago)On balance I doubt it will matter reducing the Log file by enough that you can backup and restore to your Test machineOr make some more space available on your test machine and/or restore the Data and Log to different drives (once you have restored it on TEST you can then shrink it as much as you like - assuming you don't really care about performance on Test machine) |
|
|
|
|
|
|
|
|
Not enough disk space when performing a restore