Skip to main content
replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/
Source Link

Standard disclaimer: I am an "involuntary DBA" (a nice phrase I saw in this article) and have done lotslots and lots and lots and lots and lotslots of reading on this subject, but am still confused/concerned...

I need to change the recovery model of several SQL Server 2014 production databases from Full to Simple.

The databases are currently set to Full Recovery and were being backed-up using SqlBackupAndFTP, which would do a daily full backup, with 3 differential backups and 20 transaction backups each day. This was working fine until we had huge problems with the company providing FTP-based storage.

About 3 months ago I switched to using Attix5 which is working well - however, I've now discovered that due to the way the software works the transaction logs are as big as the data files (in one case over 14Gb against a 13Gb data file).

I have been recommended to switch the recovery model to Simple, so that the transaction log doesn't grow.

I know that having Simple Recovery will not provide point-in-time restoration - that is absolutely fine, as full backups are taking place hourly and we are "happy to lose" anything that might have happened in the previous 1-59 minutes.

My understanding is that I should set the recovery model to Simple and let the backup take place, which will set a checkpoint in the transaction log allowing for the space to be re-used.

However, I really need to shrink the transaction log to a sensible size (so that it's not taking up so much of the backup space), but I keep reading that shrinking is a bad idea. Or am I getting the wrong end of the stick?

If I shrink the transaction log down to around 25Mb (which should easily be big enough for a single hourly set of transaction) using the following command, is that enough?

DBCC SHRINKFILE('log file name',25)

(I'm really sorry to be asking something that appears to have been asked a zillion times before, but this is not something I can get wrong.)

Standard disclaimer: I am an "involuntary DBA" (a nice phrase I saw in this article) and have done lots and lots and lots and lots and lots of reading on this subject, but am still confused/concerned...

I need to change the recovery model of several SQL Server 2014 production databases from Full to Simple.

The databases are currently set to Full Recovery and were being backed-up using SqlBackupAndFTP, which would do a daily full backup, with 3 differential backups and 20 transaction backups each day. This was working fine until we had huge problems with the company providing FTP-based storage.

About 3 months ago I switched to using Attix5 which is working well - however, I've now discovered that due to the way the software works the transaction logs are as big as the data files (in one case over 14Gb against a 13Gb data file).

I have been recommended to switch the recovery model to Simple, so that the transaction log doesn't grow.

I know that having Simple Recovery will not provide point-in-time restoration - that is absolutely fine, as full backups are taking place hourly and we are "happy to lose" anything that might have happened in the previous 1-59 minutes.

My understanding is that I should set the recovery model to Simple and let the backup take place, which will set a checkpoint in the transaction log allowing for the space to be re-used.

However, I really need to shrink the transaction log to a sensible size (so that it's not taking up so much of the backup space), but I keep reading that shrinking is a bad idea. Or am I getting the wrong end of the stick?

If I shrink the transaction log down to around 25Mb (which should easily be big enough for a single hourly set of transaction) using the following command, is that enough?

DBCC SHRINKFILE('log file name',25)

(I'm really sorry to be asking something that appears to have been asked a zillion times before, but this is not something I can get wrong.)

Standard disclaimer: I am an "involuntary DBA" (a nice phrase I saw in this article) and have done lots and lots and lots and lots and lots of reading on this subject, but am still confused/concerned...

I need to change the recovery model of several SQL Server 2014 production databases from Full to Simple.

The databases are currently set to Full Recovery and were being backed-up using SqlBackupAndFTP, which would do a daily full backup, with 3 differential backups and 20 transaction backups each day. This was working fine until we had huge problems with the company providing FTP-based storage.

About 3 months ago I switched to using Attix5 which is working well - however, I've now discovered that due to the way the software works the transaction logs are as big as the data files (in one case over 14Gb against a 13Gb data file).

I have been recommended to switch the recovery model to Simple, so that the transaction log doesn't grow.

I know that having Simple Recovery will not provide point-in-time restoration - that is absolutely fine, as full backups are taking place hourly and we are "happy to lose" anything that might have happened in the previous 1-59 minutes.

My understanding is that I should set the recovery model to Simple and let the backup take place, which will set a checkpoint in the transaction log allowing for the space to be re-used.

However, I really need to shrink the transaction log to a sensible size (so that it's not taking up so much of the backup space), but I keep reading that shrinking is a bad idea. Or am I getting the wrong end of the stick?

If I shrink the transaction log down to around 25Mb (which should easily be big enough for a single hourly set of transaction) using the following command, is that enough?

DBCC SHRINKFILE('log file name',25)

(I'm really sorry to be asking something that appears to have been asked a zillion times before, but this is not something I can get wrong.)

edited tags
Link
Paul White
  • 90.3k
  • 30
  • 424
  • 663
Source Link
freefaller
  • 217
  • 2
  • 4
  • 10

Switching to Simple Recovery - shrinking transaction logs

Standard disclaimer: I am an "involuntary DBA" (a nice phrase I saw in this article) and have done lots and lots and lots and lots and lots of reading on this subject, but am still confused/concerned...

I need to change the recovery model of several SQL Server 2014 production databases from Full to Simple.

The databases are currently set to Full Recovery and were being backed-up using SqlBackupAndFTP, which would do a daily full backup, with 3 differential backups and 20 transaction backups each day. This was working fine until we had huge problems with the company providing FTP-based storage.

About 3 months ago I switched to using Attix5 which is working well - however, I've now discovered that due to the way the software works the transaction logs are as big as the data files (in one case over 14Gb against a 13Gb data file).

I have been recommended to switch the recovery model to Simple, so that the transaction log doesn't grow.

I know that having Simple Recovery will not provide point-in-time restoration - that is absolutely fine, as full backups are taking place hourly and we are "happy to lose" anything that might have happened in the previous 1-59 minutes.

My understanding is that I should set the recovery model to Simple and let the backup take place, which will set a checkpoint in the transaction log allowing for the space to be re-used.

However, I really need to shrink the transaction log to a sensible size (so that it's not taking up so much of the backup space), but I keep reading that shrinking is a bad idea. Or am I getting the wrong end of the stick?

If I shrink the transaction log down to around 25Mb (which should easily be big enough for a single hourly set of transaction) using the following command, is that enough?

DBCC SHRINKFILE('log file name',25)

(I'm really sorry to be asking something that appears to have been asked a zillion times before, but this is not something I can get wrong.)