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

avatar 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

avatar image

Grant Fritchey ♦♦
137k 20 47 81

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.

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: 1431 times

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

Copyright 2018 Redgate Software. Privacy Policy