1

Please help me in understanding the below issue :

SQL Server Version 2014 with AOAGs. We have separate Data , Log , temp DB , SQLFS Drives individually.

Our Backup Strategy is as below:

  • Weekly Full Backup
  • Daily Differential
  • Every 15 Minutes Log backup

My Total Log drive space is 300 GB. When I checked the DBCC SQLPERF(LOGSPACE) only tempdb is highest consumer.

AutoGrowth Settings :

enter image description here

NOTE : Currently I have only 20% free space left. Autogrowth is in '%'.

Also we have Filestream data enabled . Will Filestream have separate Log file ?

Result of T-Log utilization:

enter image description here

Please help me with below concerns as I am new to SQL Server DBA.

  1. In what case I should go for increasing my Log drive space . For ex: E:\ is for my SQLLOG, in what case I should expand or increase my drive. What is the end point I should consider before expanding drive ?
  2. Sometimes DBCC Shrink does not free up much space ! what could be the valid reason.
  3. And I read some articles where it is mentioned not to use Autogrowth in '%' instead we should use in 'MB's'. I have mentioned my case above . Kindly correct me if I am in good lines or false affect. Please correct me and help me on going for good considerations.

Thanks

  • 1
    With MS SQL and this respect maybe put the Temp DB log file and data file on their own separate partition(s) away from your user DB transaction logs and data files partitions so tempdb can full up to capacity and the rest of your DB processes won't come to a halt, etc. – Pimp Juice IT Jun 11 '17 at 5:11
  • 1
    If there are poorly performing large transactions running and using tempdb during the shrink operations you are performing, then there may not be any free space available to release as the transactions are still running and not yet been committed and thus the correlated poorly performing queries potentially causing this behavior. – Pimp Juice IT Jun 11 '17 at 5:14
  • 1
    Once you get your SQL Server cleaned up and performance tune it for maybe smaller transactions or whatever at this level and get your disk configuration setup and have room for growth, etc. Try to setup a daily automated report to send you all the DB data and tran log sizes including free space in each as well as all SQL DB disk partition free space per SQL Server instance as well and start becoming familiar with what's normal so it's easy to spot when it's not. – Pimp Juice IT Jun 11 '17 at 5:17
1

I think you can calculate each 15 min time slot before doing transaction backup, because after transaction backup, the previous transaction logs will be rewriteable, and that will be the maximum of the transaction log file size.

DBCC Shrinkfile sometimes could not work well because there are some transaction not write down to database already (transaction will be wrote to log file first and then write to database), you can wait a while and DBCC again.

1

I do agree with all the comments by @McDonald's.

Will Filestream have separate Log file ?

No it will not.

In what case I should go for increasing my Log drive space . For ex: E:\ is for my SQLLOG, in what case I should expand or increase my drive. What is the end point I should consider before expanding drive ?

You should monitor your log used space over a period of time. Collect it in a table with xx hour or minute interval. Chart it and see the usage pattern. Plan for peaks and consider some headroom for unexpected usage. Then you can forecast the future usage growth. Based on that information and depending on your company policy expand the drive for next xx months.

Sometimes DBCC Shrink does not free up much space ! what could be the valid reason.

There are enough good articles about why you should not shrink your files. In summary if that file needs to grow again you are just wasting your time. Because you asked I will mention few here.

These answers will help with your questions.

  1. https://stackoverflow.com/questions/7193445/dbcc-shrinkfile-on-log-file-not-reducing-size-even-after-backup-log-to-disk
  2. https://stackoverflow.com/questions/779153/why-cant-i-shrink-a-transaction-log-file-even-after-backup

For tempdb this tip helped me out many times.

And I read some articles where it is mentioned not to use Autogrowth in '%' instead we should use in 'MB's'. I have mentioned my case above . Kindly correct me if I am in good lines or false affect. Please correct me and help me on going for good considerations.

You definitely want to change percent to fix size. Please read this and this.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.