 | |
#41
 
| |||
| |||
|
Re: SQL 2005 Shrink issue -
06-17-2008
, 10:26 AM
|
night's backup, then switch to simple recovery mode (this will prevent the log from growing, because in simple recovery mode, it doesn't need to hold all of your active transactions). Otherwise, I would create a job that I believe you'd say "it doesn't need to hold all of your PASSIVE transactions" instead of "active" as passive virtual logs are deleted in every checkpoint if the recovery model is SIMPLE. Active transactions have to stay in the transaction log file as they are still active. This must be a typo. -- Ekrem nsoy "Aaron Bertrand [SQL Server MVP]" <ten.xoc (AT) dnartreb (DOT) noraa> wrote in message news:C47D418F.81DB%ten.xoc (AT) dnartreb (DOT) noraa... Are you able to clarify the tranasctional log backup, log backup and any other types of backup we're able to do in SQL 2005? I think I may be getting confused over some of these. You can backup the database, which is typically just called a backup. Backing up the database gives you a file that you can restore somewhere for testing or for recovery purposes. It is basically a point in time snapshot of your data. You can also backup the transaction log in between full backups. This allows you to take that full backup snapshot, restore it, and then apply the log backups to recover up until a point in time. Without log backups, if you take a full backup every night, and then you lose your disk at 4 PM, you lose all of the changes to your database that happened after the backup. If you have transaction log backups every 15 minutes, you can have little or even no data loss. (Assuming, of course, that you put the .BAK files on a different disk than your data files reside on. If you put them in the same place, you will lose both.) Books Online can explain this in much more detail than I can possibly do in a newsgroup post. We run backups nightly, creating a new .bak each time and we can go back up to 2 months. In FULL recovery mode, this is not a wise backup model. FULL recovery mode is meant to allow you to recover fully to a specific point in time. However to do so you need to back up your logs periodically in order to apply them to get up to a point in time. If you don't back up your log multiple times throughout the day, and only do a full backup every night, then the log will get bigger and bigger... it is waiting for you to back it up so it can free up space. If you don't back it up to free up the space used by what should be inactive transactions, the log needs to keep those as active because you haven't backed it up. In your current mode, you have no more safety than in simple recovery mode. Whether you need to be in full recovery mode, or can switch to simple recovery mode, depends on your tolerance for data loss. In a catastrophe, if it is okay to lose all data that has changed since last night's backup, then switch to simple recovery mode (this will prevent the log from growing, because in simple recovery mode, it doesn't need to hold all of your active transactions). Otherwise, I would create a job that backs up the transaction log periodically throughout the day. A |
#42
| |||
| |||
|
|
night's backup, then switch to simple recovery mode (this will prevent the log from growing, because in simple recovery mode, it doesn't need to hold all of your active transactions). Otherwise, I would create a job that I believe you'd say "it doesn't need to hold all of your PASSIVE transactions" instead of "active" as passive virtual logs are deleted in every checkpoint if the recovery model is SIMPLE. Active transactions have to stay in the transaction log file as they are still active. This must be a typo. -- Ekrem nsoy "Aaron Bertrand [SQL Server MVP]" <ten.xoc (AT) dnartreb (DOT) noraa> wrote in message news:C47D418F.81DB%ten.xoc (AT) dnartreb (DOT) noraa... Are you able to clarify the tranasctional log backup, log backup and any other types of backup we're able to do in SQL 2005? I think I may be getting confused over some of these. You can backup the database, which is typically just called a backup. Backing up the database gives you a file that you can restore somewhere for testing or for recovery purposes. It is basically a point in time snapshot of your data. You can also backup the transaction log in between full backups. This allows you to take that full backup snapshot, restore it, and then apply the log backups to recover up until a point in time. Without log backups, if you take a full backup every night, and then you lose your disk at 4 PM, you lose all of the changes to your database that happened after the backup. If you have transaction log backups every 15 minutes, you can have little or even no data loss. (Assuming, of course, that you put the .BAK files on a different disk than your data files reside on. If you put them in the same place, you will lose both.) Books Online can explain this in much more detail than I can possibly do in a newsgroup post. We run backups nightly, creating a new .bak each time and we can go back up to 2 months. In FULL recovery mode, this is not a wise backup model. FULL recovery mode is meant to allow you to recover fully to a specific point in time. However to do so you need to back up your logs periodically in order to apply them to get up to a point in time. If you don't back up your log multiple times throughout the day, and only do a full backup every night, then the log will get bigger and bigger... it is waiting for you to back it up so it can free up space. If you don't back it up to free up the space used by what should be inactive transactions, the log needs to keep those as active because you haven't backed it up. In your current mode, you have no more safety than in simple recovery mode. Whether you need to be in full recovery mode, or can switch to simple recovery mode, depends on your tolerance for data loss. In a catastrophe, if it is okay to lose all data that has changed since last night's backup, then switch to simple recovery mode (this will prevent the log from growing, because in simple recovery mode, it doesn't need to hold all of your active transactions). Otherwise, I would create a job that backs up the transaction log periodically throughout the day. A |
#43
| |||
| |||
|
|
night's backup, then switch to simple recovery mode (this will prevent the log from growing, because in simple recovery mode, it doesn't need to hold all of your active transactions). Otherwise, I would create a job that I believe you'd say "it doesn't need to hold all of your PASSIVE transactions" instead of "active" as passive virtual logs are deleted in every checkpoint if the recovery model is SIMPLE. Active transactions have to stay in the transaction log file as they are still active. This must be a typo. -- Ekrem nsoy "Aaron Bertrand [SQL Server MVP]" <ten.xoc (AT) dnartreb (DOT) noraa> wrote in message news:C47D418F.81DB%ten.xoc (AT) dnartreb (DOT) noraa... Are you able to clarify the tranasctional log backup, log backup and any other types of backup we're able to do in SQL 2005? I think I may be getting confused over some of these. You can backup the database, which is typically just called a backup. Backing up the database gives you a file that you can restore somewhere for testing or for recovery purposes. It is basically a point in time snapshot of your data. You can also backup the transaction log in between full backups. This allows you to take that full backup snapshot, restore it, and then apply the log backups to recover up until a point in time. Without log backups, if you take a full backup every night, and then you lose your disk at 4 PM, you lose all of the changes to your database that happened after the backup. If you have transaction log backups every 15 minutes, you can have little or even no data loss. (Assuming, of course, that you put the .BAK files on a different disk than your data files reside on. If you put them in the same place, you will lose both.) Books Online can explain this in much more detail than I can possibly do in a newsgroup post. We run backups nightly, creating a new .bak each time and we can go back up to 2 months. In FULL recovery mode, this is not a wise backup model. FULL recovery mode is meant to allow you to recover fully to a specific point in time. However to do so you need to back up your logs periodically in order to apply them to get up to a point in time. If you don't back up your log multiple times throughout the day, and only do a full backup every night, then the log will get bigger and bigger... it is waiting for you to back it up so it can free up space. If you don't back it up to free up the space used by what should be inactive transactions, the log needs to keep those as active because you haven't backed it up. In your current mode, you have no more safety than in simple recovery mode. Whether you need to be in full recovery mode, or can switch to simple recovery mode, depends on your tolerance for data loss. In a catastrophe, if it is okay to lose all data that has changed since last night's backup, then switch to simple recovery mode (this will prevent the log from growing, because in simple recovery mode, it doesn't need to hold all of your active transactions). Otherwise, I would create a job that backs up the transaction log periodically throughout the day. A |
#44
| |||
| |||
|
|
night's backup, then switch to simple recovery mode (this will prevent the log from growing, because in simple recovery mode, it doesn't need to hold all of your active transactions). Otherwise, I would create a job that I believe you'd say "it doesn't need to hold all of your PASSIVE transactions" instead of "active" as passive virtual logs are deleted in every checkpoint if the recovery model is SIMPLE. Active transactions have to stay in the transaction log file as they are still active. This must be a typo. -- Ekrem nsoy "Aaron Bertrand [SQL Server MVP]" <ten.xoc (AT) dnartreb (DOT) noraa> wrote in message news:C47D418F.81DB%ten.xoc (AT) dnartreb (DOT) noraa... Are you able to clarify the tranasctional log backup, log backup and any other types of backup we're able to do in SQL 2005? I think I may be getting confused over some of these. You can backup the database, which is typically just called a backup. Backing up the database gives you a file that you can restore somewhere for testing or for recovery purposes. It is basically a point in time snapshot of your data. You can also backup the transaction log in between full backups. This allows you to take that full backup snapshot, restore it, and then apply the log backups to recover up until a point in time. Without log backups, if you take a full backup every night, and then you lose your disk at 4 PM, you lose all of the changes to your database that happened after the backup. If you have transaction log backups every 15 minutes, you can have little or even no data loss. (Assuming, of course, that you put the .BAK files on a different disk than your data files reside on. If you put them in the same place, you will lose both.) Books Online can explain this in much more detail than I can possibly do in a newsgroup post. We run backups nightly, creating a new .bak each time and we can go back up to 2 months. In FULL recovery mode, this is not a wise backup model. FULL recovery mode is meant to allow you to recover fully to a specific point in time. However to do so you need to back up your logs periodically in order to apply them to get up to a point in time. If you don't back up your log multiple times throughout the day, and only do a full backup every night, then the log will get bigger and bigger... it is waiting for you to back it up so it can free up space. If you don't back it up to free up the space used by what should be inactive transactions, the log needs to keep those as active because you haven't backed it up. In your current mode, you have no more safety than in simple recovery mode. Whether you need to be in full recovery mode, or can switch to simple recovery mode, depends on your tolerance for data loss. In a catastrophe, if it is okay to lose all data that has changed since last night's backup, then switch to simple recovery mode (this will prevent the log from growing, because in simple recovery mode, it doesn't need to hold all of your active transactions). Otherwise, I would create a job that backs up the transaction log periodically throughout the day. A |
#45
| |||
| |||
|
|
night's backup, then switch to simple recovery mode (this will prevent the log from growing, because in simple recovery mode, it doesn't need to hold all of your active transactions). Otherwise, I would create a job that I believe you'd say "it doesn't need to hold all of your PASSIVE transactions" instead of "active" as passive virtual logs are deleted in every checkpoint if the recovery model is SIMPLE. Active transactions have to stay in the transaction log file as they are still active. This must be a typo. -- Ekrem nsoy "Aaron Bertrand [SQL Server MVP]" <ten.xoc (AT) dnartreb (DOT) noraa> wrote in message news:C47D418F.81DB%ten.xoc (AT) dnartreb (DOT) noraa... Are you able to clarify the tranasctional log backup, log backup and any other types of backup we're able to do in SQL 2005? I think I may be getting confused over some of these. You can backup the database, which is typically just called a backup. Backing up the database gives you a file that you can restore somewhere for testing or for recovery purposes. It is basically a point in time snapshot of your data. You can also backup the transaction log in between full backups. This allows you to take that full backup snapshot, restore it, and then apply the log backups to recover up until a point in time. Without log backups, if you take a full backup every night, and then you lose your disk at 4 PM, you lose all of the changes to your database that happened after the backup. If you have transaction log backups every 15 minutes, you can have little or even no data loss. (Assuming, of course, that you put the .BAK files on a different disk than your data files reside on. If you put them in the same place, you will lose both.) Books Online can explain this in much more detail than I can possibly do in a newsgroup post. We run backups nightly, creating a new .bak each time and we can go back up to 2 months. In FULL recovery mode, this is not a wise backup model. FULL recovery mode is meant to allow you to recover fully to a specific point in time. However to do so you need to back up your logs periodically in order to apply them to get up to a point in time. If you don't back up your log multiple times throughout the day, and only do a full backup every night, then the log will get bigger and bigger... it is waiting for you to back it up so it can free up space. If you don't back it up to free up the space used by what should be inactive transactions, the log needs to keep those as active because you haven't backed it up. In your current mode, you have no more safety than in simple recovery mode. Whether you need to be in full recovery mode, or can switch to simple recovery mode, depends on your tolerance for data loss. In a catastrophe, if it is okay to lose all data that has changed since last night's backup, then switch to simple recovery mode (this will prevent the log from growing, because in simple recovery mode, it doesn't need to hold all of your active transactions). Otherwise, I would create a job that backs up the transaction log periodically throughout the day. A |
#46
| |||
| |||
|
|
night's backup, then switch to simple recovery mode (this will prevent the log from growing, because in simple recovery mode, it doesn't need to hold all of your active transactions). Otherwise, I would create a job that I believe you'd say "it doesn't need to hold all of your PASSIVE transactions" instead of "active" as passive virtual logs are deleted in every checkpoint if the recovery model is SIMPLE. Active transactions have to stay in the transaction log file as they are still active. This must be a typo. -- Ekrem nsoy "Aaron Bertrand [SQL Server MVP]" <ten.xoc (AT) dnartreb (DOT) noraa> wrote in message news:C47D418F.81DB%ten.xoc (AT) dnartreb (DOT) noraa... Are you able to clarify the tranasctional log backup, log backup and any other types of backup we're able to do in SQL 2005? I think I may be getting confused over some of these. You can backup the database, which is typically just called a backup. Backing up the database gives you a file that you can restore somewhere for testing or for recovery purposes. It is basically a point in time snapshot of your data. You can also backup the transaction log in between full backups. This allows you to take that full backup snapshot, restore it, and then apply the log backups to recover up until a point in time. Without log backups, if you take a full backup every night, and then you lose your disk at 4 PM, you lose all of the changes to your database that happened after the backup. If you have transaction log backups every 15 minutes, you can have little or even no data loss. (Assuming, of course, that you put the .BAK files on a different disk than your data files reside on. If you put them in the same place, you will lose both.) Books Online can explain this in much more detail than I can possibly do in a newsgroup post. We run backups nightly, creating a new .bak each time and we can go back up to 2 months. In FULL recovery mode, this is not a wise backup model. FULL recovery mode is meant to allow you to recover fully to a specific point in time. However to do so you need to back up your logs periodically in order to apply them to get up to a point in time. If you don't back up your log multiple times throughout the day, and only do a full backup every night, then the log will get bigger and bigger... it is waiting for you to back it up so it can free up space. If you don't back it up to free up the space used by what should be inactive transactions, the log needs to keep those as active because you haven't backed it up. In your current mode, you have no more safety than in simple recovery mode. Whether you need to be in full recovery mode, or can switch to simple recovery mode, depends on your tolerance for data loss. In a catastrophe, if it is okay to lose all data that has changed since last night's backup, then switch to simple recovery mode (this will prevent the log from growing, because in simple recovery mode, it doesn't need to hold all of your active transactions). Otherwise, I would create a job that backs up the transaction log periodically throughout the day. A |
#47
| |||
| |||
|
|
Ok, Success. Quite interesting how we've shrunk the log files...can you comment at all how this has worked? We just used MS SQL 2005 Mngment tool and did a FULL BACKUP on the DB...then we did a TRANSACTION LOG BACKUP. The log file shrunk a few MBs. Then we did exactly the same again, FULL BACKUP then a TRANSACTION LOG backup. The .ldf then went from about 10GB to about 70MB. How did this happen, what has actually happened here? Also, what actual data will have been removed? The .bak is about 10GB so I'm guessing the data from the .ldf is in there, but is this true...and what data has been moved? Thanks so far!! Chris |
#48
| |||
| |||
|
|
Ok, Success. Quite interesting how we've shrunk the log files...can you comment at all how this has worked? We just used MS SQL 2005 Mngment tool and did a FULL BACKUP on the DB...then we did a TRANSACTION LOG BACKUP. The log file shrunk a few MBs. Then we did exactly the same again, FULL BACKUP then a TRANSACTION LOG backup. The .ldf then went from about 10GB to about 70MB. How did this happen, what has actually happened here? Also, what actual data will have been removed? The .bak is about 10GB so I'm guessing the data from the .ldf is in there, but is this true...and what data has been moved? Thanks so far!! Chris |
#49
| |||
| |||
|
|
Ok, Success. Quite interesting how we've shrunk the log files...can you comment at all how this has worked? We just used MS SQL 2005 Mngment tool and did a FULL BACKUP on the DB...then we did a TRANSACTION LOG BACKUP. The log file shrunk a few MBs. Then we did exactly the same again, FULL BACKUP then a TRANSACTION LOG backup. The .ldf then went from about 10GB to about 70MB. How did this happen, what has actually happened here? Also, what actual data will have been removed? The .bak is about 10GB so I'm guessing the data from the .ldf is in there, but is this true...and what data has been moved? Thanks so far!! Chris |
#50
| |||
| |||
|
|
Ok, Success. Quite interesting how we've shrunk the log files...can you comment at all how this has worked? We just used MS SQL 2005 Mngment tool and did a FULL BACKUP on the DB...then we did a TRANSACTION LOG BACKUP. The log file shrunk a few MBs. Then we did exactly the same again, FULL BACKUP then a TRANSACTION LOG backup. The .ldf then went from about 10GB to about 70MB. How did this happen, what has actually happened here? Also, what actual data will have been removed? The .bak is about 10GB so I'm guessing the data from the .ldf is in there, but is this true...and what data has been moved? Thanks so far!! Chris |
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.