| T O P I C R E V I E W |
| MadCow |
Posted - 02/15/2012 : 10:12:57 AM
Team Foundation Log file grew to 100GB and drive is 100GB so it ran out of disk space.
Current settings by default in place:
The DB Log file is set to auto growth by 10%. Restricted growth to 2TB.
I want to set the restricted growth to 85GB and perform full backups.
Question:
By limiting the growth to 85GB what happens if it grows to 85GB and the backup fails? What are the ramifications?
Advise Please, Thanks!
|
| 14 L A T E S T R E P L I E S (Newest First) |
| Curt |
Posted - 02/17/2012 : 3:25:18 PM
Once you shrink it the multiple backup (TRN files) should be very small.
Only shrink when really needed.
For instance , if you have a 10 gig empty file that your are backing up it's a waste of disk space.
But if you have a 100 GB database and a 10 GB Transaction log, its that that bad but for sure it's another story.
If you shrink every night get ready for some down time when you have to defrag the database.
Better to do the backups.
Try it and let us know what happens.
And remember, if you don't need the trn files they can be deleted in the SQL job after a few hours. I generally have to done to a compressed folder on another drive on another server accross the network.
quote:
Originally posted by MadCow
Yes - thanks for all yur assistance Curt.
I cannot do multiple backups of the Log file daly since I dont have enought disk space. I do it once a day full backup of the MDF and LDF.
So doing a Full backup of MDB and LDF is enough and simply shrink it daily after the full backup - rathern than deleting the log?
|
| MadCow |
Posted - 02/17/2012 : 10:32:07 AM
Yes - thanks for all yur assistance Curt.
I cannot do multiple backups of the Log file daly since I dont have enought disk space. I do it once a day full backup of the MDF and LDF.
So doing a Full backup of MDB and LDF is enough and simply shrink it daily after the full backup - rathern than deleting the log?
|
| Curt |
Posted - 02/16/2012 : 5:22:22 PM
Great, now create a job to back up the transaction log a few times a day, and if you do a daily backup and don't need the transaction log backups, you can discard them in the SQL Job to be deleteded later after the full backup is done of the MDF file.
The reason why the backup command has a truncate option is because you can allow only the empty space to be removed in the file by the backup command.
I guess it's time for a good defrag of the disk as well.
Glad your out of trouble.
|
| MadCow |
Posted - 02/16/2012 : 3:26:33 PM
Thanks Curt.
ran the shrink from the SQL Management Console and the LDFile shrank to 1MB and all the disk space is back.
|
| Curt |
Posted - 02/16/2012 : 2:59:14 PM
In this case the log will grow again.
Did you get my email. Feel free to call me.
quote:
Originally posted by MadCow
Thanks Much Curt.
Using the management Studio ...
I did a full backup of the log file with truncate option enabled yesterday and a full backup of the DB today. The log file is still at 100GB and no drive space freed up.
Is it not the same as running the commands you mentioned???
Run them one at a time.
Backup log Sharepoint_Config to disk = 'e:\sharepointconfig.trn'
Backup log Sharepoint_Config to disk = 'e:\sharepointconfig.trn, TRUNCATEONLY'
DBCC SHRINKFILE(2,TRUNCATEONLY)
DBCC SHRINKFILE (2,1000)
Backup log Sharepoint_Config to disk = 'e:\sharepointconfig.trn'
Then run DBCC SHRINKFILE(2,TRUNCATEONLY)
I did not shrink the log file yet. Just concerned about the performance?
Advise Please.
|
| MadCow |
Posted - 02/16/2012 : 2:11:34 PM
Thanks Much Curt.
Using the management Studio ...
I did a full backup of the log file with truncate option enabled yesterday and a full backup of the DB today. The log file is still at 100GB and no drive space freed up.
Is it not the same as running the commands you mentioned???
Run them one at a time.
Backup log Sharepoint_Config to disk = 'e:\sharepointconfig.trn'
Backup log Sharepoint_Config to disk = 'e:\sharepointconfig.trn, TRUNCATEONLY'
DBCC SHRINKFILE(2,TRUNCATEONLY)
DBCC SHRINKFILE (2,1000)
Backup log Sharepoint_Config to disk = 'e:\sharepointconfig.trn'
Then run DBCC SHRINKFILE(2,TRUNCATEONLY)
I did not shrink the log file yet. Just concerned about the performance?
Advise Please. |
| Curt |
Posted - 02/16/2012 : 11:46:28 AM
Ok, open up a query window in SQL Server Managment Studio.
Now say for instance you had a Share point server.
You would have a database called Share Point config.
So you would execute the commands below.
use Sharepoint_config
select * from sysfiles
Here's the return:
1 1 792 -1 128 2 0 SharePoint_Config
2 0 71112 268435456 10 1048642 0 SharePoint_Config_log
Ok, so the log is about 10 times as large as the mdf file.
Now if I run DBCC OPENTRAN:
I get a return of :
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
There are no open, non commited transactions in the file.
So in essence it's a balloon.
So let's shrink the balloon.
Except in a situation that you have I would leave this size alone.
In some instances and applications simple mode will slow down performance.
Your goal now though is to get your space back first.
Frequent backups at perhaps 4 per day will help keep the size down.
Replace your database name and drive locations into the follow T-SQL commands.
Run them one at a time.
Backup log Sharepoint_Config to disk = 'e:\sharepointconfig.trn'
Backup log Sharepoint_Config to disk = 'e:\sharepointconfig.trn, TRUNCATEONLY'
DBCC SHRINKFILE(2,TRUNCATEONLY)
DBCC SHRINKFILE (2,1000)
Backup log Sharepoint_Config to disk = 'e:\sharepointconfig.trn'
Then run DBCC SHRINKFILE(2,TRUNCATEONLY)
Again.
This should reduce the space.
Now DO NOT do this every night. It's fragmentation city.
quote:
Originally posted by MadCow
Thanks Michael.
If I switch the DB mode to Simple and run a FULL backup of the DB will that reduce the physical size of the current log file which is 100GB????
I have already done a full transaction log bakcup yesterday.
|
| MadCow |
Posted - 02/16/2012 : 11:17:43 AM
Thanks Michael.
If I switch the DB mode to Simple and run a FULL backup of the DB will that reduce the physical size of the current log file which is 100GB????
I have already done a full transaction log bakcup yesterday. |
| NMDANGE |
Posted - 02/16/2012 : 10:58:49 AM
If you set the max log size, and you run up to the 85GB limit, all updates to the database will fail until you run another backup. I would either run transaction log backups more often (once an hour for example) or switch the database to SIMPLE mode and run full backups every day. Unless you need to be able to do point-in-time recovery on the database, you should run in SIMPLE not FULL mode. |
| MadCow |
Posted - 02/16/2012 : 08:09:48 AM
Thanks All.
Curt,
The backup of the log file took 6 hours. I was backing it up to an external USB HD. But the physical logfile size is still 100GB.
During the transaction log backup I had the option selected to truncate the transaction logs.
Currently the default max log file size growth is set to 2TB. What happens if I change that to 85GB so we don't run out of space? And continue to do FULL backups of the TLogs once a day???
Advise Please - Thanks. |
| Rambler |
Posted - 02/16/2012 : 01:36:48 AM
Bad wording, sorry, it's been discontinued :)
quote:
The BACKUP LOG WITH NO_LOG and WITH TRUNCATE_ONLY options have been discontinued. If you are using the full or bulk-logged recovery model recovery and you must remove the log backup chain from a database, switch to the simple recovery model. For more information, see Considerations for Switching from the Full or Bulk-Logged Recovery Model.
I'm not trying to be smart here and I know you know a lot about SQL Curt, it's just we've had similar problem in the past and I think SQL 2008 (it was possibly 2008 R2), didn't allow me to use that option at all. So I thought I'd mention it.
http://msdn.microsoft.com/en-us/library/ms186865.aspx |
| Curt |
Posted - 02/15/2012 : 9:02:56 PM
IT's still working. I use it all the time on the SQL 2008 services I work on.
quote:
Originally posted by Rambler
Note that TRUNCATE_ONLY was deprecated in SQL 2008.
|
| Rambler |
Posted - 02/15/2012 : 5:10:22 PM
Note that TRUNCATE_ONLY was deprecated in SQL 2008. |
| Curt |
Posted - 02/15/2012 : 2:47:04 PM
Time for some DBA work.
DBCC OPENTRAN will reveal any open transactions.
Backup the log.
Then:
You may be able to release the empty space of the file.
Backup log <database> to DISK='g:\database.trn. TRUNCATEONLY' should be done first
Then run dbcc shrinkfile(2, Truncateonly)
Then backup the log again.
Don't get the idea that you should shrink the log every night like this. Bad Idea. Books Online shows the above method as a way out of a full disk situation.
What you really need to do is have a backup job that will run several times a day to keep the log from growing so much.
In SQL a Backup process does a great deal more than backup data.
Databases are not Spread Sheets. Many of the SQL commands are groups of proceedures that process the database and keep it functioning.
|
