SQL Server Performance Forum – Threads Archive
Restore a BAK file with big tranny log
I want to restore a database on my local. I get the following. I have 4.5GB available space. Can I somehow force the Restore command to restore the backup or discard the tranny log? Server: Msg 3257, Level 16, State 1, Line 8There is insufficient free space on disk volume ‘C:’ to create the database. The database requires 13241352192 additional free bytes, while only 4512319488 bytes are available.
Server: Msg 3013, Level 16, State 1, Line 8
RESTORE DATABASE is terminating abnormally. Thanks, CanadaDBA
As far I know, no. May be you have to shrink before backup and then restore in local.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS†with no warranties for accuracy.
One of the KBA refers to resolve
This error occurs when the file size of the restored database is 2 GB or greater and the database is being restored over an existing database that is less than 2 GB. This error indicates that SQL Server cannot correctly check the free space. To bypass checking for free space, you can issue DBCC TRACEON (3104) on the same connection on which you are attempting the restore operation. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
The backup comes from production. The data file size is 894MB and the Transaction log size is 15619MB. My local harddrive has 4.5GB free. 1. If I need to restore the DB on my local, do I need to have the transaction log, too? How can cut that portion from the BAK file? Or restrict the backup to not backup the transaction log part? 2. The File Growth is by percent and I think I have to change it to by MegaBytes. Right? 3. And totally, is it good idea to set the transaction log to a fix size. I mean to restrict the file growth. I know this is not a recommended idea but if for example all my production’s daily transactions for the DB doesn’t go more than 2GB, then should I restrict the transaction logs grow to 2GB or 4GB or what? CanadaDBA
1. You can deploy SIMPLE recovery model but then ensure to maintain full database backup at regular intervals. The transaction log file is must in order to restore the databases. 2. IN general it is recommended to leave it as %age than size in MB.
3. Yes you can fix the size for Transaction log and disable auto-grow, as we have the same setup on our production servers without any issues. Ensure to maintain regular backups for transaction log in order to keepup the size of virtual log. Also deploy some sort of alerting whenever the size of Tlog is going beyond 70 or 80%, you can take help of alerts as specified in books online. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Ref to # 1, This means that when I am creating backup, I need to use SIMPLE… but how about the current situation? My production creates the backups as FULL recovery. I don’t want to change that. So, the only way is to have more space on my local. Right? Ref to # 2, even with the size of my tranny log, still you suggest go with %age? Ref to # 3, Alert? Do you want to prevent the TLog to get the maximum size? Why? Shouldn’t it go back and using the space from the head of the TLog? I mean like a circle. If not, what happens when it gets into maximum defined size?
quote:Originally posted by satya
1. You can deploy SIMPLE recovery model but then ensure to maintain full database backup at regular intervals. The transaction log file is must in order to restore the databases.
2. IN general it is recommended to leave it as %age than size in MB.
3. Yes you can fix the size for Transaction log and disable auto-grow, as we have the same setup on our production servers without any issues. Ensure to maintain regular backups for transaction log in order to keepup the size of virtual log. Also deploy some sort of alerting whenever the size of Tlog is going beyond 70 or 80%, you can take help of alerts as specified in books online. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
3. Yes you can fix the size for Transaction log and disable auto-grow, as we have the same setup on our production servers without any issues. Ensure to maintain regular backups for transaction log in order to keepup the size of virtual log. Also deploy some sort of alerting whenever the size of Tlog is going beyond 70 or 80%, you can take help of alerts as specified in books online. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
CanadaDBA
To be able to restore your current backup you need more space yes. With 15GB of transaction log it seems that you are either not doing transaction log backups or you perform very large transactions in the database. Perform more frequent log backups to keep the size in control or it will just grow and grow. If you perform very large transactions then you will need the space (unless you create a process to switch to bulk logged recovery mode and then switch back and do backup etc). Even if you increase the log backups now, you still need to shrink your existing log file. Check bol for dbcc commands.
You may Shrink+truncate your log in production, which wil bring the log file in MB size.then take a full backup and restore in test server. Dont forget to take Full back of Prod database after shrink operation.
I practiced as follow:
1. Restored the BAK file into my test server,
2. Changed the recovery mode to SIMPLE,
3. Get a full backup.
The resulted BAK file had small size and I could send it to developers and they could restore it on their locals. I think this was the only way. Argyl,
What do you mean with "you are either not doing transaction log backups"? Everynight I backup my databases and they are full backup. Also, every hour I backup transaction logs. But it doesn’t affect the Transaction log size. And second question is that should I usually shrink the log file by using DBCC SHRINKFILE? Thanks,
CanadaDBA
The BACKUP log will not reduce physical size of Tlog file it only works on logical size of log, to reduce the size physically you must use DBCC SHRINKFILE> Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
]]>