May 1, 2022 at 8:06 am
Hi everyone
I ran a script to see the size of DB.
SELECT
mdf.database_id,
mdf.name,
mdf.physical_name as data_file,
ldf.physical_name as log_file,
db_size_MB = CAST((mdf.size * 8.0)/1024 AS DECIMAL(8,2)),
log_size_MB = CAST((ldf.size * 8.0 / 1024) AS DECIMAL(8,2))
FROM (SELECT * FROM sys.master_files WHERE type_desc = 'ROWS' ) mdf
JOIN (SELECT * FROM sys.master_files WHERE type_desc = 'LOG' ) ldf
ON mdf.database_id = ldf.database_id
Running this script gives db_size_MB = 11080.00 and log_size_MB = 15176.00
I am a rookie so I don't know how to intrepret this. The log is way more than the actual data. What is the log db doing? Does it need to be that big?
Thank you
May 1, 2022 at 4:06 pm
Here is an article you should review: https://qa.sqlservercentral.com/articles/managing-transaction-logs (also linked to in my signature).
If your database is in full recovery model - you *must* perform frequent log backups (at least every hour - preferably every 15 minutes and quite often more frequently). If your database is in simple recovery model then you cannot perform log backups - so the size will be the size it needs to be to support normal transactions.
With that said, if (and only if) there was some non-normal process that was run that caused the log file to grow - then you can and should shrink it back to a normal size. To do that, use SHRINKFILE and don't shrink the database.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
May 1, 2022 at 9:25 pm
Here is an article you should review: https://qa.sqlservercentral.com/articles/managing-transaction-logs (also linked to in my signature).
If your database is in full recovery model - you *must* perform frequent log backups (at least every hour - preferably every 15 minutes and quite often more frequently). If your database is in simple recovery model then you cannot perform log backups - so the size will be the size it needs to be to support normal transactions.
With that said, if (and only if) there was some non-normal process that was run that caused the log file to grow - then you can and should shrink it back to a normal size. To do that, use SHRINKFILE and don't shrink the database.
Thank you for this.
I ran this:
use OptionsDB
go
DBCC SHRINKFILE (OptionsDB_log);
GO
the size went up and not down. it is now 15240.00. Is that expected?
May 1, 2022 at 9:29 pm
In Access, I would see that the DB size would keep increasing even after I deleted data. I had to shrink the database to get back to its true size. Does SQL Server do the same? In my test Sql Server DB, I have been adding and deleting data so I wonder if the DB is artificially too high. I can tell from the data value I mentioned earlier that the data is way too high. There is no way the test data I used is 11 GB. No way. It should be closer to less than 1GB at most.
May 1, 2022 at 9:39 pm
Database files don't auto shrink, and we don't want them to do that. As you add data the file will grow.
When you shrink a database file it can cause your tables and indexes to fragment. If you do that you want to rebuild the indexes, which will require space in the file causing it to just grow again.
Review the article to understand how the transaction log works.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
May 2, 2022 at 12:38 pm
Is your database in FULL , BULK_LOGGED or SIMPLE recovery mode?
Right click "Database" -> "Properties" -> "Options" look at "Recovery Model"
If FULL or BULK LOGGED, how frequent are your Transaction Log Backups ?
Look at "Database" -> "Properties" -> "General" -> "Last Database Log Backup"
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply