Auto growth for Transaction Log

Question

Auto growth for Transaction Log

Viewing 7 posts - 16 through 21 (of 21 total)

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

Hakuna Matata SSCertifiable Points: 5577 More actions · Answer 1

I tried the shrink option on my test server & the file size came down to 4.5GB.

JoeS 3024 Mr or Mrs. 500 Points: 533 More actions · Answer 2

Thanks for the info and sorry I didn't get back to answer the question asked. The current DB file size that we are working on is 190GB. The Transaction log is about 300GB. As for growing it is, and due to differences in opinion a good management plan for it hasn't been completely put in place. I'm reading more info on T-logs to support/learn more about my concerns.

Thanks

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

JoeS 3024 (5/11/2011)
Thanks for the info and sorry I didn't get back to answer the question asked. The current DB file size that we are working on is 190GB. The Transaction log is about 300GB.

Assuming full recovery, how often are the log backups?

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

JoeS 3024 Mr or Mrs. 500 Points: 533 More actions · Answer 4

That is one of the differences in opinion. It was placed into Full Recovery Mode with no T-log backups being done. And no I can't explain to you why.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

JoeS 3024 (5/11/2011)
That is one of the differences in opinion. It was placed into Full Recovery Mode with no T-log backups being done. And no I can't explain to you why.

Well that is a problem, but I'd be more concerned by how much HD space I have left on that drive.

The problem is that you need 190 GB for the full, then almost 300 for the log.

So assuming you don't have 0.5 TB of free space you're already in big troubles.

While you figure out the correct job setup, I'd run something like this when the server is not too busy :

USE [master]

Take full backup

GO

ALTER DATABASE [PROD] SET RECOVERY SIMPLE WITH NO_WAIT

GO

ALTER DATABASE [PROD] SET RECOVERY SIMPLE

GO

CHECKPOINT

GO

USE [PROD]

GO

DBCC SHRINKFILE (N'PROD_Log' , 19000)

GO

USE [master]

GO

ALTER DATABASE [PROD] SET RECOVERY FULL WITH NO_WAIT

GO

ALTER DATABASE [PROD] SET RECOVERY FULL

GO

Take full backup

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

JoeS 3024 (5/11/2011)
That is one of the differences in opinion. It was placed into Full Recovery Mode with no T-log backups being done. And no I can't explain to you why.

Then that log is going to grow until it fills the drive.

Either switch to simple recovery or start taking log backups (and no, there isn't a 3rd option)

Please read through this: http://www.sqlservercentral.com/articles/64582/

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

JoeS 3024 Mr or Mrs. 500 Points: 533 More actions · Answer 7

Thanks for the info. Very interesting read on exactly how the SQL Engine uses the T-log. I knew about the recovery model aspect(s) but the other stuff was new. This will assist with other DBs I am charged with as I now know I should make some tweaks. Again thanks