Transaction Log File - exceptionally large , can't shrink? 3

[1DMF]

Hi,

We have an issue where we have a DB with an MDF of 720 MB, but an LDF of 25.5 GB!

Server support said it was because the backup was failing, and so the log file couldn't be culled until SQL accepted that a backup had been done and I did have some trouble with editing and deleting the backup maintenance plan with some odd FK constraint issue and other errors.

I finally removed the corrupted backup plan (via T-SQL), created a new one (via maintenance wizard), which included not only an entire SQL server backup (all DB's including system), but also a separate full backup against the particular DB with the large log file issue.

I then ran the backups successfully.

I then ran a shrink log file task with options to re-allocate and reclaim space, but it hasn't made any difference.

I then found info that it could be to do with some setting for 'log_reuse_wait_desc = replication', which may be stopping the auto-shrink if SQL thinks there is an outstanding replication (we don't use replication), but when I check this setting I have : LOG_BACKUP.

So why is my log file so big and why won't it shrink?

Thanks,
1DMF



"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[djj55]

Did you do a transaction log backup?

If you truly do not need the log then after a good full backup you can put the database into SIMPLE recovery mode shrink the file then return it to FULL. This is not normally a best practice though.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[1DMF]

D'oh, I was under the false impression that a full backup included the transaction log. I thought the clue was in the name, FULL!

Thanks for enlightening me, appreciated.

1DMF.

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[djj55]

I had that same notion until I asking a question similar to yours.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[1DMF]

I'm at a loss.

I have done a transaction log backup, which completed successfully over night, I have ran and ran and ran Shrink task after Shrink task against both the DB and log files.

I have taken the DB and SQL offline and put back online, I have tried to rename the LDF file (that just crashes SQL - I was hoping it might create a new blank one!).

I don't know what else to do.

So se have a DB file that is only 500MB but a LOG file of 25GB as well as now a LogFile backup of 25GB, so for a 500MB DB it's taking up 50.5GB!!!!

What can I try next?

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[gmmastros]

Transaction log files usually get smaller when you backup then shrink. When it doesn't get smaller, it's usually for 1 of 2 reasons.

Log files have "initial sizes". You can see the initial size by right clicking on the db, click properties, and then files. When you shrink a database or log file, it will only get s small as the initial size. This is by design and actually makes a lot of sense unless things get out of whack like you are experiencing now. This is not likely your problem because it would be a bit ridiculous to have a 25GB initial size.

The 2nd reason is caused by open transactions. If you have an open transaction when you backup the transaction log file, it will back up just fine, but it will not allow you to recover unused space from a shrink operation because it doesn't want to mess up the open transaction. This is likely your problem.

My suggestion is to set the DB to single user mode, change the Recovery model to simple, backup the transaction log, and then shrink it. Once this is done, set everything back to the way it was.

ALTER DATABASE [YourDbName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [YourDbName] SET RECOVERY SIMPLE WITH NO_WAIT;
BACKUP LOG [YourDbName] TO DISK='NUL:';
DBCC SHRINKFILE (N'YourLogFileName' , 0, TRUNCATEONLY);
ALTER DATABASE [YourDbName] SET RECOVERY FULL WITH NO_WAIT;
ALTER DATABASE [YourDbName] SET MULTI_USER WITH ROLLBACK IMMEDIATE;

Please only ever run this script once. The backup log command will not actually backup your log file. It tricks SQL Server in to thinking that the transaction log file is backed up, but it will not create a backup file. The backup will essentially be thrown away. Immediately after running the above commands, you should take a full database backup and a transaction log backup.

You then need to think about your backup strategy. You should periodically take a full database backup as well as transaction log backups. Transaction log backups usually occur a lot more frequently than full backups. When your database is in full recovery mode, this allows for point in time recovery. So... I would recommend daily full backups and transaction log backups every X number of minutes. If your database blows up, you can restore the full backup and then the transaction log backups (several if need be) to get you back to a running state.

You should also practice your restores occasionally so that you will be ready for the situation where you actually need it.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

Hey George,

Thank you so much for your help with this. It was definitely an open transaction causing the problem as nothing else would resolve the issue.

The question is what transaction and why? Is this just a glitch or is there potentially an inherent problem somewhere that needs resolving with one of my transactions (SP's)?

I have a very strong backup regime, I backup every 2 hours just the main DB, I also do a full backup of all DB's every 2 hours and both are securely and remotely backed up to or disaster recovery backup service provider.

I now also do a TransLog backup of the main DB every evening (which includes a shrink step) which is also remotely backed up to our DR site.

Then each evening the server service provider does an entire snap-shot backup of the Hyper-V cluster.

The backup process wasn't the issue here, it was my lack of knowledge of the way transactions get backed up and cleared outside of a Full DB backup.

As mentioned I think there may be an issue somewhere causing the DB Trans Log to grow the way it is, which I will need to keep an eye on.

Any suggestions on how I can track down a potentially rouge Stored Procedure?

As always, much obliged for your help

1DMF

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[gmmastros]

I don't know of any methods to track down what caused an open transaction. Sometimes (here) that can occur when a dev is working in SSMS and leaves an open transaction.

As far as your backup strategy is concerned, I think you may have things a little backwards... or... room for improvement.

Once you have your transaction log under control, it should be much smaller than the actual DB. In fact, it should probably be about 25% of the main DB file (the MDF). Transaction log backups are usually even smaller. My suggestion to you is this....

Backup your main DB once per day. Schedule this to occur when there is the least amount of traffic to it. Then, schedule your transaction log backups to occur more frequenetly (like every 30 minutes). This will allow you to do a point in time restore if need be in 1/2 hour increments.

Don't forget! If you change your backup strategy, make sure you test the restores a couple times to make sure everything is working the way you expect it to.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

Do all DB changes go into the transaction log or only those done via a transaction?

A lot of the DB updates come from MS Access applications that rightly or wrongly do not use transactions, just issue SQL DDL commands.

How does the transaction log help in this situation, the DB backup is the important one, and is what I have used to do restores in the past.

I'm currently a little confused as to what is being updated and when.





"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[SQLBill]

Are you reindexing nightly? Reindexing can cause the t-log to grow to a huge size. You could run a trace to see if you can capture what is going on .... look at long duration activity.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[gmmastros]

Everything that changes data is logged in the transaction log file, whether you implicitly create a transaction or not.

How does the transaction log help in this situation, the DB backup is the important one, and is what I have used to do restores in the past.

When you restore a database that has good transaction log backups, you restore the full database, and then restore the transaction logs up to the point in time that you want. The benefit here is that full backups take longer than transaction log backups so you do the slow (and large) full backup during slow times and then you do transaction log backups which are usually very small and therefore very quick.

Bottom line, the benefit is that you use less storage space and done in less time while impacting your database less.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[yelworcm]

You can track down an open transaction using DBCC OPENTRAN() (run in the database you suspect has the open transaction). This will give you a SPID to hunt down. There are various DMVs that are slowly taking the place of DBCC OPENTRAN (sys.dm_tran_active_transactions comes to mind), but it takes a bit of detective work to work out how to map a transaction to a session, then to a statement. sys.system_objects has become one of my favorite tables in the later versions of SQL Server.

 

[1DMF]

George ->

I have amended my backup regime. Hopefully this time you will think it adequate!

Every hour on the hour 24 hours a day -> translog backup of our main DB
Every hour at 15 minutes past the hour 24 hours a day -> DR remote backup of translog backup -> week retention

Every 2 hours on the hour -> Main DB backup
Every 2 hours at 30 minutes past the hour -> DR remote backup of main DB backup -> week retention

Every night @ 8pm -> Full DB backup
Every night @ 8:30pm -> DR remote backup of Full DB backup -> 3 month retention

VSS is also on and takes a snapshot of the system every 2 hours from 7am for 12 hours.

Plus as mentioned the VM cluster image is backed up every night -> week retention

yelworcm ->

Thanks, so far no open transactions found on suspect DB, hopefully it was just a glitch and not a rouge SP!

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[gmmastros]

Sounds good to me.

Have you tested the restore process yet? Backups are useless if you can't restore them.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom