|
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
(comments are locked)
|
|
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. Dear Grant, thanks for your reply. Can u pls tell me, why mdf isn't increasing?
Jul 21 '12 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 '12 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 '12 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 '12 at 06:06 AM
zabedbd
(comments are locked)
|
