transaction log ful

Question

Post reply

transaction log ful

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Animal Magic SSChampion Points: 13964 More actions · Answer 1

Krasavita (10/21/2009)
how can I shrink?

BACKUP LOG [MyDB] TO DISK = N'E:\sqldb\backup\\ChangeControl\MyDB_backup_200910220805.trn' WITH NOFORMAT, NOINIT

A full backup will not help here you here. you need to do a LOG backup and then shrink the log

use dbname

go

DBCC SHRINKFILE (N'<logfilename>' , 0, TRUNCATEONLY)

GO

Krasavita SSC-Insane Points: 23138 More actions · Answer 2

Krasavita

SSC-Insane

Points: 23138

October 22, 2009 at 6:51 am

#1069363

Thank you, I did that and what is the next step?

Krasavita SSC-Insane Points: 23138 More actions · Answer 3

Krasavita

SSC-Insane

Points: 23138

October 22, 2009 at 6:56 am

#1069367

for log space I have this 772.92973.181445

Animal Magic SSChampion Points: 13964 More actions · Answer 4

try the shrink command another couple of times, sometimes it doesnt shrink. if it still doesnt change change your recovery model to simple, run the shrink again and then check the log size. Once its ok change the model back to full

The other option is now that you have backed up your log, assuming you have the disk space (you have said already thats not an issue) then the log backups will only be small from now on as long as you do them regularly.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Animal Magic (10/22/2009)
try the shrink command another couple of times, sometimes it doesnt shrink. if it still doesnt change change your recovery model to simple, run the shrink again and then check the log size. Once its ok change the model back to full

and take a full backup to restart the log chain.

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

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

Krasavita SSC-Insane Points: 23138 More actions · Answer 6

I ran and it worked. I will be keeping this db in simple because it is development server.

What do you mean and take a full backup to restart the log chain?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Krasavita (10/22/2009)
What do you mean and take a full backup to restart the log chain?

If you were going to switch it back to full recovery, you would need to take a full backup after doing so to restart the log chain and allow log backups. Since you're going to leave it in simple, that's not required.

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

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

Animal Magic SSChampion Points: 13964 More actions · Answer 8

if you switch it back to full recovery model the next log backup you run will fail because the LSN sequence has been disrupted.

Read Gails (GilaMonsters) article on managing transaction logs, i think its in her post footers.