May 16, 2009 at 1:31 pm
Friends its very urgent....
I have an I(Index drive): which only stores SQL Server .ldf (log files). now the space of that drive is 65 gb and left space is 10MB. what should i do. can i do something like below
can i shrink the log files for SQL Server---right click database --- then----task-----shrink-----files .
May 16, 2009 at 5:18 pm
Are you getting 9002 errors in your errorlog saying log files are out of space? If not you might not have an actual problem (yet), you may have a full logdrive but still have space within the logs files
run dbcc sqlperf(logspace) to see how full each transaction log actually is. If some are full and those databases are not in simple recovery mode, run a log backup on those, rerun the command and see if space is cleared within the logs.
If physical drive space still needs to be recovered then only shrink those logs that appear to be larger than they need to be, and then only shrink them to a sensible value and not right down to nothing. also check the filegrowth setting is sensible, it should in most cases be set in MB rather than say 10%.
You want to avoid being in the situation of having to constantly shrink log files (it fragments them) so if you still have a space shortage look to get more disk space or move some log files elsewhere.
---------------------------------------------------------------------
May 17, 2009 at 1:51 am
Please read through this - Managing Transaction Logs[/url]
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 18, 2009 at 6:39 am
It sounds like one or more of your databases is in full recovery mode but you don't have log backups running.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 20, 2009 at 6:14 am
Backup transaction log and shrink db
[font="Comic Sans MS"]+++BLADE+++[/font]:cool:
May 22, 2009 at 9:01 am
check which log files contains the maximum space, then perform a transactional log backup on that. Once the backup is complete, shrink the log file to some percentage, as per requirement.
Hope this will help...
Thanks & Regards,
Sudeepta.
http://twitter.com/skganguly
May 22, 2009 at 12:33 pm
Hi folks i'm new to this.
Let me know if i say anything wrong.
Y can't the person take a full backup of all the databases manually and truncate the log files. So that every database will be up to date. He can have a fresh start to save the Tlogs in his I disk.
May 22, 2009 at 2:08 pm
sam (5/22/2009)
Hi folks i'm new to this.Let me know if i say anything wrong.
Y can't the person take a full backup of all the databases manually and truncate the log files. So that every database will be up to date. He can have a fresh start to save the Tlogs in his I disk.
Because truncating the log files breaks the log chain, forcing a new backup to be performed before any additional transaction log backups can be done. Read the article I link to in my signature on how to manage the transaction logs.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 23, 2009 at 8:01 am
hi,
delete unwanted log files those before the full backup taken.
Goodluck
Manjunath
May 23, 2009 at 8:48 am
I think you mean delete un-needed log file backups. If you delete log files made since the last full backup, you lose the backup chain. What if your backup fails? What if the server crashes during the backup??
You need to keep a full backup and all logs since that full was made UNTIL the next full backup is made.
May 27, 2009 at 9:45 am
use the following
Backup log with truncate_only
dbcc SHRINKFILE(DBNAME,0,Truncateonly)
May 27, 2009 at 9:58 am
Aaack!
Truncating the log breaks the log chain. If that is run, no log backups may be made afterwards and hence no point in time restores are possible until a full backup is made.
Shrink file does not take the DB name as the first parameter. It takes the file name of the file to be shrunk.
Shrinking to 0 is really a very bad idea on an active database. The first thing that will happen is that the log will grow again, and again, and again. If it does have to be shrunk, it should be to a reasonable size based on the DB's activity and the frequency of the log backups.
TRUNCATE ONLY is only applicable to data files, not to log files.
Please read through the article that both Jeffrey and I have referenced.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 27, 2009 at 10:04 am
Pradyothana Shastry (5/27/2009)
use the followingBackup log with truncate_only
dbcc SHRINKFILE(DBNAME,0,Truncateonly)
This is very bad advice to give, unless you want to break the log chain and destroy your ability to recover to a point in time. In fact, after doing this you cannot even backup the transaction log again until you take a full backup.
This command has been deprecated in SQL Server 2005 and is not functional in 2008. Please do not give this advice. The proper way to do this now is:
1) ALTER DATABASE dbname SET RECOVERY SIMPLE;
2) CHECKPOINT -- might need to issue this a couple of times
3) DBCC SHRINKFILE(file, size) -- size should be large enough to handle transactions you need
4) ALTER DATABASE dbname SET RECOVERY FULL;
5) BACKUP DATABASE dbname ... -- restart backups and log chain
The CHECKPOINT is needed to force the log processing back to the beginning of the file. You can't shrink the log until the active portion of the log is at the beginning.
And finally, this should only be done after an extraordinary event has occurred and should never be scheduled on a regular basis.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 27, 2009 at 12:09 pm
Hi Jeffrey Williams,
Really thanks for your advice. I'm new to this SQL SERVER. I'm trying to learn the best.
Can you please provide me the some kind of sample script for what you have given. Since i don't know much about the check points . I mean how i should use them or where i should use them.
thanks,
sam.
May 27, 2009 at 12:35 pm
The script is really the commands he's given. Look them up in BOL and use the parameters that would make sense for your system.
for shrinkfile, use something that seems reasonable to you given what you can guess a backup size is.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply