SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Corrupt?? Or what is going on


Corrupt?? Or what is going on

Author
Message
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87815 Visits: 45274
Can you run DBCC UpdateUsage and then check free space again?

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


lawson2305
lawson2305
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 429
I ran DBCC UPDATEUSAGE (DCCS_Berea_Prod)
The Messages say:
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DB size when still going into properties shows 33033.63 MB with space available of 82.99 MB

Now if I got in Shrink by file I get some strange data saying currently allocated space of 1300 MB and 82.81 MB free.

Ok if I go into Shrink for the log file it shows a 31733.63 MB currently allocated and 26354.71 MB free.

I also had a strange thing happen where my weekly plan this week executed on Saturday and was still running this morning. I stopped the job there is no reference that it did anything in the logs.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87815 Visits: 45274
lawson2305 (5/27/2014)
Now if I got in Shrink by file I get some strange data saying currently allocated space of 1300 MB and 82.81 MB free.

Ok if I go into Shrink for the log file it shows a 31733.63 MB currently allocated and 26354.71 MB free.


Ah, now things make sense. You've got a very small amount of data (1.3GB) but a massive log file. Backups don't contain the entire transaction log. They just need enough of the log to recover the database once restored. That large backup must have occurred when there was a huge uncommitted transaction, or lots of unreplicated transactions and hence it needed to backup most of the log. The more recent backup didn't.

You might want to investigate and see why a 1.3 GB database has 5 GB of log in use. Large transactions, replication falling behind, lack of log maintenance?

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


lawson2305
lawson2305
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 429
Ok a strange thing regarding the log.
1. This backup has been about 30GB for quite sometime and we do full daily log backups and hourly append log backups from a maintenance plan. Is there a setting in the backup that is not committing the transactions in the backup?? What is strange is right after the free space loss is when the backup all of a sudden changed.
Basically it has been a steady growth of space up to the 30GB and held there since around 5/14.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87815 Visits: 45274
Backups don't commit transactions, so not sure what you want there.
I don't know what was done when the DB ran out of space, so can't comment on whether it's strange or not.

It sounds like you possibly had a very long running transaction (someone ran begin tran and left the connection open), that would mean that the backup would have to include all the log back to that begin tran and nothing would commit that transaction until the connection was explicitly closed or the service restarted. Other possibility is unreplicated transactions from transactional replication, that would also require the full backup to include all the log back to the first unreplicated transaction.

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


lawson2305
lawson2305
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 429
ok sorry for my misunderstanding but I thought that is why you do Log backups to commit the data to the DB and clear the transaction data from the Log file to prevent excessive growth.

I understand your comment on the log transaction being open and makes me wonder about the application that does these transactions. Maybe I need to put into the routine to also restart this application daily to kill the connection.

So bottom line you think the db itself is ok and I just need to figure out why the heck we are getting this continued log growth. Any suggestions would be great and I can move to a different topic if required.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87815 Visits: 45274
lawson2305 (5/28/2014)
ok sorry for my misunderstanding but I thought that is why you do Log backups to commit the data to the DB and clear the transaction data from the Log file to prevent excessive growth.


No.
When I get home I'll post my article on transaction logs (if someone doesn't do so first) and explain further.

So bottom line you think the db itself is ok and I just need to figure out why the heck we are getting this continued log growth.


Yup. Pretty much.
I can't think of any 'corruption' that can cause a small backup that will restore correctly.

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


lawson2305
lawson2305
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 429
I want to say thank you very much for all your help.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87815 Visits: 45274
Ok... Take a read through this when you get a chance, and if you have questions please ask: http://www.sqlservercentral.com/stairway/73776/ (Yes, I know it's long)

Also, if your backups start to grow again or the used space in the log starts to grow and doesn't reduce after a few log backups, post back and I'll help you diagnose what's going on.

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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search