June 16, 2008 at 9:09 am
Hi, I have a similar issue. I'd like to run a transaction log backup and truncate the transaction log. Currently is 26gigs. Will the back up be that size? space is an issue here..thanks.
June 23, 2008 at 2:44 pm
Hi people,
I had an issue with the tlog of a db in SQL Server 2000, the db file was 300 mb and 2 gb for tlog.
When I tried to shrink the file using DBCC SHRINKFILE nothing happens and the file still size 2 gb.
I tried all suggestions from this thread with same results.
Finally I solved the problem creating another log file (a small one) to the db, after that SHRINKFILE worked fine resizing the file to a reazonable size.
June 23, 2008 at 4:30 pm
Magic bullet!
If you can update the recovery type.
Backup database.
Set recoverytype to simple
task-shrink-file-log (note: do not shrink to almost nothing, sql just has to recreate.
set recoverytype to full
add backing up transactionlog into nightly maintenance
d
June 24, 2008 at 12:02 am
Add to that a full backup after switching back into full recovery mode. After truncating log you have no point-in-time recovery untill you take a full/diff backup.
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
June 24, 2008 at 5:52 am
Delete the ldf file and rebuild it once again ....
By using following Steps
a)Stop the services
b)delete the ldf (Only ldf file not mdf)
c)Now your database comes in suspect mode..
d)
use master
DBCC rebuild_log ('Dbaname','Mdf file path')
e) Now you'r database comes in single user mode set it in to multi user mode....
Ashwin VP
CSC India ...
June 24, 2008 at 6:02 am
You mean, if you're lucky, your database comes out of suspect mode. Deleting a log file is like playing russian roulette with your database. You are asking for trouble.
See here for some more detailed points on why you don't want to delete and rebuild the log
http://www.sqlskills.com/blogs/paul/2007/09/15/CorruptionLastResortsThatPeopleTryFirst.aspx
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
June 24, 2008 at 7:22 am
I have Hr_database
Hr_database_data.mdf 1235MB
Hr_database_log.LDF 25623502MB
The LDF is growing how to truncate this log file, any scripts??
Thanks NITa
The correct way is:
Set the recovery mode to Simple then shrink log file ...but before doing it you should have a copy of HR_database!
USE [master]
GO
ALTER DATABASE [HR_database] SET RECOVERY SIMPLE WITH NO_WAIT
GO
ALTER DATABASE [HR_database] SET RECOVERY SIMPLE
GO
THEN
DBCC SHRINKFILE ('HR_database_log' , 0, TRUNCATEONLY)
GO
hope that it will help to you!?
but be attention that if you want to restore from the log something, you should forget it, for that reason I write before that you should have a copy of that database!
For more infor how to do that visit Books Online!
Dugi
September 18, 2008 at 4:53 am
you can try this..
USE database_name
GO
DBCC SHRINKFILE (database_log,55)
GO
backup LOG database_name with truncate_only
GO
DBCC SHRINKFILE (database_log,50)
GO
USE master
DBCC SHRINKDATABASE (database_name, 2)
GO
September 18, 2008 at 12:41 pm
USE Hr_database
GO
BACKUP LOG Hr_database WITH TRUNCATE_ONLY
DBCC SHRINKFILE(Hr_database_log)
This should reduce the file to the max possible...thanks
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
September 18, 2008 at 3:39 pm
detach it, when attach,ignore the log file,then it is ok.
September 19, 2008 at 1:47 am
sqlservercenter (9/18/2008)
detach it, when attach,ignore the log file,then it is ok.
Especially if you're trying to corrupt the database.
Log files aren't optional, deleting them should be an absolute last resort and deleting them discards all sorts of information on transactions that have occurred.
As I posted earlier in this thread - http://www.sqlskills.com/blogs/paul/2007/09/15/CorruptionLastResortsThatPeopleTryFirst.aspx
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
September 19, 2008 at 4:31 am
Hi,
I anybody have any dml statments that while deleting the records it does not goes into the log or the moment 1 delete is complete it should truncate the log file?
September 19, 2008 at 5:39 am
harsha.bhagat1 (9/19/2008)
Hi,I anybody have any dml statments that while deleting the records it does not goes into the log or the moment 1 delete is complete it should truncate the log file?
Sorry, I don't understand the question.
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
September 19, 2008 at 5:55 am
Do you mean that you do not want to have entry for deleted record in the log?
September 19, 2008 at 5:58 am
yes!!!
I want to delete records from table but do not want any entry in the log file ..
is it possible..
or
while deleting only can i truncate the log file???
Viewing 15 posts - 31 through 45 (of 54 total)
You must be logged in to reply to this topic. Login to reply