Holidays and Traditions

The space in the database can be reclaimed after a large deletion from the table.

A verified professional is this person.IT peers will see that you are a professional if you verify your account.On Feb 12, 2019.

I have a database with some tables that store logs.I deleted old records from each table.It's something like 20GBs.I deleted the old records from the tables to make this space available.The free disk space on the data drive was enough to store the old records.The amount of free disk space is the same after deletion of old records.

The database is called Logs.The Logs_log.ldf transaction log file is located on the Data drive in the server.

There could be a few things going on here.Do you know if the file is full?You should be able to see that in the properties with the right click.You can see the img attached.

Without a proper backup job in place, the ldf will continue to grow.

When a transaction is complete, it will stay in the transaction log until you backup it.If you use the transaction log backups to back up your backups, you can do a "point-in-time" recovery in the event of a failure.

Full recovery requires a full and t log backups.If you don't specify copy_only, you will break the log chain.

I want to make sure the backups are in place before I change the size of something.The worst case is a data file that has more room in it and a log that needs a backup to clear out transactions.

If you really want to shrink this file, you can right click the database and hit the task menu, which will shrink the file.I don't advise this without further investigation of the backups and recovery model.

Check your recovery mode.If you don't have full and differentials, your log will keep growing.

The recovery model is simple.Commvault is the backup software that backs up all the databases.

It sounds like everything is on track.Unless working space is needed and dependent on the queries being run/written, simple mode tlogs won't grow.If you really want to shrink the files, do it based on the actual size of the database.Unless you're running a lot of bad queries, tlogs should be less than the fill db size.

I have a look in the shrink section.You can see percentages to give you a better idea of workload.

Use caution when changing file properties.If it is in use or not, this will have an impact on the database.

Will there be more log records in the database?The database size will not increase until all the deleted space has been re-used.If you shrink the database now, it will get bigger when you store newer log records.

I would leave it as is.It is reasonable to have less than a quarter of the database size.

Simple recovery mode truncated t-logs, which means you can ignore them if you back up your databases.

Truncation has nothing to do with file size.It is likely that the t-log file is what it needs to be.

Unless you are desperate, don't worry about reclaiming space.If your drive is about to fill up.There is one constant about databases.You're going to fill that space anyways.

You can find advice on the internet about using DBCC SHRINKFILE.Don't.Not on data files.This post was written by Paul Randal.Paul owned the shrinkfile code when he worked at Microsoft.

The short version is that leave the space there.Unless you're desperate, the cost of reclaiming it isn't worth it.

I agree with Alec6638 that if you don't change the settings in your logging app it will be the same size again.It will only get worse as the file continues to grow and the performance will be impacted.

Some people are confused.The OP's database is in recovery mode and he isn't asking how to shrink his transaction logs.

He was asking how to recover the available space after record deletions when he thought the space was no longer available.

Some people are confused.The OP's database is in recovery mode and he isn't asking how to shrink his transaction logs.

He was asking how to recover the available space after record deletions when he thought the space was no longer available.

When the words "reclaim space" appear, people start throwing around SHRINKFILE or otherwise shrinking the data file.

Unless a recovery mode was switched at some point, that's a lot of working space.

Yes!I like to make sure that OP is on track.It's almost as if backups are second nature to me.

Unless a recovery mode was switched at some point, that's a lot of working space.

Simple isn't concerned with the t-log size.It will get truncated after a checkpoint and the logs are circular so it is best to leave them as they are.They are similar to mdfs in that they will re-use the space as needed.

There will be more log records imported into this database.Log Parser is used to import the logs from various websites.I keep the most recent 90 days worth of logs for review.

The space in this drive is always full.I have to allocate more drive space via VMware.

Deleting records from the database won't clear space in the t-logs.It will do the opposite.Changes made to the database are recorded in Transaction Logs.Every deletion causes the t-logs to grow.If you set your t-logs to grow by 10%, each successive growth is larger than the last.

If that is the case, set your autogrowth settings to a fixed size.This is dependent on workload and there is no formula for determining it.A number that is small enough not to take a long time but large enough they aren't happening all the time is what you want.

The same behaviors should be exhibited if you continue to use the database the same way.Shrinking it will be temporary.

There will be more log records imported into this database.Log Parser is used to import the logs from various websites.I keep the most recent 90 days worth of logs for review.