database log size, log shipping and performance

Question

database log size, log shipping and performance

prem.budhu

SSC Rookie

Points: 36
More actions
August 12, 2011 at 10:40 am

#246150

Hello,
some of my users are having some performance issues with an application that uses sqlserver 2005 and I am running the profiler to get some stats. However, I notice that my database log file is 170 GB. I do a full backup of my database every night but not of the transactionlog.
Could the size of log contribute to my performance issue.
Also, I have log shipping enable for this database. If I do a backup of the transaction log how would it affect my log shipping job
Thanks for your help

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 1

If you have log shipping enable for that database, the transaction log backups must have been already taken. Check this.

My blog

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 2

What "log_reuse_wait_desc" it shows in sys.databases

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

santhubt Mr or Mrs. 500 Points: 591 More actions · Answer 3

I Agree with Ignacio.

Once you have a log shipping, log backups are already taken care. Regarding Logfile being 170GB, u could check log usage within that file.

Thanks and Regards

Santhubt

prem.budhu SSC Rookie Points: 36 More actions · Answer 4

Thanks

Can you expand on how to check usage with the log file.

So, the log file size could not be the issue with performance. I have enough space on the hard drive. Also, I would expect it not to grow any bigger bcause it should reuse the space log file automatically. Am I correct?

Thanks

santhubt Mr or Mrs. 500 Points: 591 More actions · Answer 5

yes, Thats true. U can use either

DBCC sqlperf(logspace) to check logusage. To be more specific can run the below query

use <database Name>

go;

select name,filename,size/128"Actual Size",maxsize/128"Maxsize",fileproperty(name,'spaceused')/128"spaceUsed" from sysfiles

Thanks and Regards

Santhubt

Perry Whittle SSC Guru Points: 234013 More actions · Answer 6

Hi

please execute the following against your Log shipping primary server and post the results back

use master

exec sp_help_log_shipping_primary_database @database = 'yourdbname'

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 7

muthukkumaran Kaliyamoorthy (8/13/2011)
What "log_reuse_wait_desc" it shows in sys.databases

You should run the following script and post the results select name, log_reuse_wait, log_reuse_wait_desc from sys.databases where [name] = 'yourdb'

My blog

prem.budhu SSC Rookie Points: 36 More actions · Answer 8

Thanks.

here is information from running the command

use master

exec sp_help_log_shipping_primary_database @database = 'yourdbname'

Last backup date = 2011-08-15 22:45:11.803

History_retension persiod = 5760

backup_retension_period =43200

prem.budhu SSC Rookie Points: 36 More actions · Answer 9

Thanks,

here is what I see after running the command

select name,filename,size/128"Actual Size",maxsize/128"Maxsize",fileproperty(name,'spaceused')/128"spaceUsed" from sysfiles

Database -- actual size =78981, max size =0 space used = 68741

DB_Log -- Actual size = 170806, max size = 2097152, space used = 88

prem.budhu SSC Rookie Points: 36 More actions · Answer 10

Thanks

Here is output from sys.database for "log_reuse_wait_desc" column

my _dbFULLACTIVE_BACKUP_OR_RESTORE

prem.budhu SSC Rookie Points: 36 More actions · Answer 11

Thanks

Here is the result from the sys.database

masterSIMPLENOTHING

tempdbSIMPLEACTIVE_TRANSACTION

modelFULLLOG_BACKUP

msdbSIMPLENOTHING

mydbFULLLOG_BACKUP

santhubt Mr or Mrs. 500 Points: 591 More actions · Answer 12

Well,

This rules out the logspace issue for good.

Santhubt

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 13

prem.budhu (8/15/2011)
Thanks,
here is what I see after running the command
select name,filename,size/128"Actual Size",maxsize/128"Maxsize",fileproperty(name,'spaceused')/128"spaceUsed" from sysfiles
Database -- actual size =78981, max size =0 space used = 68741
DB_Log -- Actual size = 170806, max size = 2097152, space used = 88

The biggest part of your log file is not in use, so you don't have to worry about it. The only thing I find very weird is that your log file is 170 GB, while your data file is about 80 GB.

My blog

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 14

prem.budhu (8/12/2011)
Hello,
some of my users are having some performance issues with an application that uses sqlserver 2005 and I am running the profiler to get some stats. However, I notice that my database log file is 170 GB. I do a full backup of my database every night but not of the transactionlog.
Could the size of log contribute to my performance issue.
Also, I have log shipping enable for this database. If I do a backup of the transaction log how would it affect my log shipping job
Thanks for your help

Did you get anything that may explain the performance issues after running the profiler?

I advice you to check this link[/url], it is a Bret Ozar's blog post about performance tuning. It may help.

My blog