Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

backup log TO DISK NUL and Database Mirror Expand / Collapse
Author
Message
Posted Monday, July 14, 2014 4:03 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 9:36 PM
Points: 30, Visits: 110
Dears,

My database base DB1 is mirrored to another DB1 on another server. I found the size of log file has been increating quickly.
I want to run the following two statements to release the disk space of the log file:
backup log DB1 TO DISK = 'nul:' with stats = 10
DBCC SHRINKFILE ( DB1_log, 1)

I have questions:
1. Does this operation affect the database mirror? Will this operation cause the database mirror fail?
2. Shall I setup a job to run these 2 statements everyday?

Thank you in advance.
Post #1592114
Posted Monday, July 14, 2014 4:19 AM This worked for the OP Answer marked as solution


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:50 AM
Points: 39,977, Visits: 36,339
No, but it will break your log chain and remove any ability to restore to a point in time. Rather set up proper log backups at an interval based on your data loss SLAs.

No you should definitely not run those daily. Never is a good frequency for those statements

Please take a read through this: http://www.sqlservercentral.com/articles/Administration/64582/



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1592121
Posted Monday, July 14, 2014 6:55 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 9:36 PM
Points: 30, Visits: 110
I'll make a plan to backup log daily and copy it to another server according your suggestion.

But, now, I need a little disk size.

I backup the database and run the two statements and found the funny result:
the mirror database log size also be truncated.

Thank you, Gail.
Post #1592165
Posted Monday, July 14, 2014 7:30 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:50 AM
Points: 39,977, Visits: 36,339
Daily? Does that meet your data loss SLAs in cases where mirroring doesn't help (like an accidentally dropped table)?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1592174
Posted Monday, July 14, 2014 11:23 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 9:36 PM
Points: 30, Visits: 110
ah?
Will this happen, the DB and mirroring DB are corrupt in the same time? I never thought about this.
Thank you for your reminding. I'll make a new solution for the log backup and storing..
Post #1592411
Posted Monday, July 14, 2014 11:23 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 9:36 PM
Points: 30, Visits: 110
ah?
Will this happen, the DB and mirroring DB are corrupt in the same time? I never thought about this.
Thank you for your reminding. I'll make a new solution for the log backup and storing..
Post #1592412
Posted Tuesday, July 15, 2014 12:41 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:50 AM
Points: 39,977, Visits: 36,339
Simon Su (7/14/2014)
Will this happen, the DB and mirroring DB are corrupt in the same time? I never thought about this.


Corrupt? Highly unlikely, the chance of two IO subsystems damaging a database file at the same time are incredibly slim, however that wasn't the example I gave. I said 'accidentally dropped table', as in "Woops, I think I just dropped a table on Production by accident."



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1592416
Posted Wednesday, July 16, 2014 5:56 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 9:36 PM
Points: 30, Visits: 110
Your advice is invaluable. I backup the database log in 10 minutes and copy it to another server.

THANK YOU, Gail.
Post #1593009
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse