.ldf file INSTANTLY filling up drive minutes after succesful shrink!

[dpsmith]

Hello. I've done lots of searching for this.

SQL2008R2, system database on Simple mode.
I get the call that there's no space left on the server.
Everything had been fine for the past year.
This server is only used for one app.

I see that the culprit is on .ldf file that had ballooned to 20GB

I also see that there's no maintenance plan setup at all.
I setup a shrink job on the database.
After shrink the.MDF is 4Gb and the .ldf was around 8GB.
Literally 5 minutes later the .ldf justs to 20GB with makes out the free space on the drive.

Any ideas what is going on?

 

[gmmastros]

What are your auto grow settings for this database?

-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

 

[dpsmith]

By 10%, restricted growth to 2097152 MB"

That's a lot.
Is that the problem?

 

[gmmastros]

I doubt that is the problem.

You said that after shrinking, the ldf was 8 G. If it grows by 10%, that would make it 8.8 G. Then 9.something, etc... it would have to grow many times before it got to 20 gigs.

Most DBA's that I know prefer to set the auto grow to a particular number instead of a percentage. For example, in your situation, you may want to set the autogrow to 2 gigs (for the log and the data file).

You see... when SQL Server grows a file, it can take some time to do that. You really don't want SQL Server to grow your file(s) during your busiest time, so it is better to monitor your database and grow the file yourself (manually) during a time when the db is less busy.

You should also make sure that "Instant File Initialization" is turned on. This only affects the mdf file, but it makes a dramatic improvement in the time it takes to grow the database. If you don't know whether this is turned on, I encourage you to download and install SQLCop. This is a free utility that I wrote. There is a link to it in my signature.

Ok... so that was a lot of background, but it's not solving your immediate problem. One potential cause could be a huger transaction that is trying to finish. You see, even in simple recovery mode, the transaction log is still used. Think of it this way.... Suppose I wanted to update data in a row:

1. Mark the transaction log that I will be updating data.
2. Put the original data in the transaction log.
3. actually update the data in the table.
4. mark the transaction log that the update was successful.

All of the steps are important for the stability of your database. You can literally pull the plug on the server in the middle of any process and SQL Server will be able to recover the DB to a good state. When SQL starts up, it checks for uncommitted transactions and processes those first, leaving your DB in a workable state.

Now, imagine a HUGE transaction like updating all the data in a very large table. This huge update will the transaction log and could (possibly) fill it up and cause the problem you are seeing.

I would encourage you to run this:

sp_who2

check the output of this command. Any spid below 50 is an internal SQL Server process. Check the spids above 50 for any unusual activity like extremely long running times and/or huge amounts of Disk IO.

You can see the query that was executed by the SPID by running this command.

[tt]DBCC INPUTBUFFER([!]The SPID Goes Here[/!])[/tt]


-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

 

[dpsmith]

Thank you for your response.
My experience with SQL has been pretty basic over the years.
What do you make of these results?
There were a few more in the 50 range but with minimal DiskIO.


The results of "sp_who2"

EXEC dbo.sp_MShistory_cleanup @history_retention = 48

52 RUNNABLE NT AUTHORITY\NETWORK SERVICE HMADATABASE . distribution DELETE 94625 181419 12/07 12:10:00 SQLAgent - TSQL JobStep (Job 0x0BB5383403FA014B99ACCA9FC69D44F6 : Step 1) 52 0


The results of "DBCC INPUTBUFFER (52)"

EXEC dbo.sp_MShistory_cleanup @history_retention = 48

 

[gmmastros]

I don't see anything wrong with that.

-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

 

[dpsmith]

This SQL server was setup by a 3rd party.

There's notes about this particular database being "part of SQL replication".
I have no idea what that means.

 

[dpsmith]

OK, a contractor was calling in. The problem was with "rebuilding the replication publication".
No idea what that is.
I guess he redid it and it's ok now.

Thanks everyone for the help!

 

[SQLBill]

Glad to see it got resolved. Now to give you a bit of extra knowledge:

Replication is the process of copying data from one database to another automatically. One method is transactional replication, this is where any transaction that happens in Database A is then done on Database B (keep in mind both databases can be on separate servers and even in separate locations). If you run one delete statement that deletes 100 rows, replication turns that into 100 separate commands that are passed to the subscriber database.

It sounds like one of two things happened....
replication broke down and a bunch of transactions 'backed up' or
they were setting up replication and that caused the log to fill up.

One thing you might want to find out....why are they doing replication and how is the replication set up? Are they replicating data from somewhere else to your server or are they replicating data from your server to another place?

-SQLBill

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