MDF file not growing, ldf not clearing space

Our Database is in simple recovery model MDF sizE:45 GB ( It's same for last couple od days) LDF size: 23 GB (Growing daily) My proble is why LDF size isn't decreasing after full backup? ( I do a daily full backup) Why MDf size isn't growing?? where my data is going?? I am really worried Pls help MDF & LDF configured unrestricted growth, sql server 008 64 bit
more ▼

asked Jul 21, 2012 at 11:19 AM in Default

zabedbd gravatar image

0 1 1 2

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first
Full backups will not affect the size of a log file, ever. The only thing that affects log file size are log backups and checkpoints when in simple recovery. If you're in simple recovery, you should not have to worry about log backups. I would look at sys.dm_exec_requests and see if you have any open, running transactions that are preventing the logs from being cleared by the checkpoint process. Also check sys.dm_tran_active_transactions. You can link from that back to other dynamic management objects (DMO) to see what the query is (sys.dm_exec_sql_text) and other things.
more ▼

answered Jul 21, 2012 at 05:04 PM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
103k 19 21 74

Dear Grant, thanks for your reply. Can u pls tell me, why mdf isn't increasing?
Jul 21, 2012 at 07:08 PM zabedbd
Could simply be a transaction in a hung state stopping everything from getting cleared. Also, deletes & updates are unlikely to grow the data storage files, but are still recorded as transactions.
Jul 21, 2012 at 08:54 PM Grant Fritchey ♦♦

You can also query sys.databases and look at the log_reuse_wait_desc to see why your log space isn't being freed up (could be something like replication or CDC)

SELECT name, log_reuse_wait_desc FROM sys.databases
Jul 21, 2012 at 09:22 PM SirSQL
SELECT name, log_reuse_wait_desc FROM sys.databases Master: Nothing tempDB: active_transactions All other DB: nothing
Jul 22, 2012 at 06:06 AM zabedbd

link text

Dear ALl, Can u please look at result of sys.dm_exec_requests as attached. Thanks
query.zip (7.1 kB)
Jul 22, 2012 at 06:40 AM zabedbd
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jul 21, 2012 at 11:19 AM

Seen: 1188 times

Last Updated: Jul 22, 2012 at 06:13 PM