question

zabedbd avatar image
zabedbd asked

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
sql-server-2008backup
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Grant Fritchey avatar image
Grant Fritchey answered
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.
6 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Dear Grant, thanks for your reply. Can u pls tell me, why mdf isn't increasing?
0 Likes 0 ·
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.
0 Likes 0 ·
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
0 Likes 0 ·
SELECT name, log_reuse_wait_desc FROM sys.databases Master: Nothing tempDB: active_transactions All other DB: nothing
0 Likes 0 ·
[link text][1] [1]: /storage/temp/321-query.zip Dear ALl, Can u please look at result of sys.dm_exec_requests as attached. Thanks
0 Likes 0 ·
query.zip (6.9 KiB)
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.