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

SQL 2008 R2 Backup Question - Log File Expand / Collapse
Author
Message
Posted Friday, April 19, 2013 5:27 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, April 21, 2013 12:25 PM
Points: 9, Visits: 45
I'm helping a friend troubleshoot an issue with a SQL 2008 R2 database that is sitting behind SharePoint. Currently, SQL is loaded on a server that is using RAID 1 for the OS (13GB space free) and RAID 5 for data (O GB free).

There are 8 log files totaling approximately 100 GB (6-11 GB per file). The automated backups stopped running over a month ago b/c the log files kept growing and maxed out the space on the RAID 5 array. We tried to shrink the logfile, but that option is grayed out in SSMS (possibly b/c the log file has not committed?). The drive bays in the server and motherboard will not support additional drives. Can I plug in a USB drive and backup the log file to the USB drive?

I'm looking for options to backup, shrink, the log file so that we can get SharePoint, SQL backonline, and then adjust the backup strategy longer term to prevent this from happening again.

Appreciate your feedback!

Michael
Post #1444652
Posted Friday, April 19, 2013 5:46 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 1:51 PM
Points: 21,644, Visits: 15,317
What is the recovery model of the databases?


Definitely not a Best Practice, but you can add a usb or sata drive and backup to that.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1444654
Posted Friday, April 19, 2013 6:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 25, 2014 9:11 AM
Points: 7, Visits: 91
Your backup log is greyed out because it needs a full backup. You should be able to use a USB drive. This is not a cluster right?


Post #1444657
Posted Friday, April 19, 2013 7:35 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 1:51 PM
Points: 21,644, Visits: 15,317
fivetenmm (4/19/2013)

The automated backups stopped running over a month ago b/c the log files kept growing and maxed out the space on the RAID 5 array.


Do you have any of those backups available (curious)? Also, are the backups set to expire by chance?

Did any of those backups succeed?




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1444659
Posted Saturday, April 20, 2013 5:01 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:44 AM
Points: 42,822, Visits: 35,953
Please read through this: http://www.sqlservercentral.com/articles/Transaction+Log/72488/


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 #1444702
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse