log file growing tremendously

Question

log file growing tremendously

Viewing 15 posts - 16 through 29 (of 29 total)

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

george sibbald SSC Guru Points: 104210 More actions · Answer 1

shanila_minnu (10/11/2010)
george sibbald (10/11/2010)
do you have 20Gb of space to do a T-log backup because it is likely to be that size.
what is the result of running dbcc sqlperf(logspace)
dbcc sqlperf(logspace) is giving 1.5% usage of 20 GB log file.I want to submit the root cause of this growing TLOG.Any sugessions

er people, the statement above does not tally with not taking log backups!

please post the results of that query shanila so we check results. Just maybe you had a very large query.

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

shanila_minnu Hall of Fame Points: 3465 More actions · Answer 2

Create a maintenance Plan to run Transaction Log backups every 15 minutes or so.
Create a cleanup step that deletes the t-log backups over 'x' days or weeks old.

I read that its not good to scheduled t log backup using management studio when database mirroring is scheduled.We need to schedule the job using T-SQL statement.Is that right.

Can we create job using the script,

BACKUP log databasename TO DISK ='NUL'

So that it will not consume disk space

homebrew01 SSC Guru Points: 55548 More actions · Answer 3

I'm not familiar with DPM. You need to backup the SQL database with the command BACKUP DATABASE

This can be done manually, through a script in a scheduled job, a maint plan, or a 3rd party tool such as Redgate, Idera, Quest .....

shanila_minnu Hall of Fame Points: 3465 More actions · Answer 4

[/quote]

er people, the statement above does not tally with not taking log backups!

please post the results of that query shanila so we check results. Just maybe you had a very large query.[/quote]

Database name log siz(MB) % used

SharedServices1_Search_DB21159.37 0.8902307 0

shanila_minnu Hall of Fame Points: 3465 More actions · Answer 5

er people, the statement above does not tally with not taking log backups!
please post the results of that query shanila so we check results. Just maybe you had a very large query.

DB NAME LOG SPACE(MB) log space used(%)

SharedServices1 21159.3 0.8902307

shanila_minnu Hall of Fame Points: 3465 More actions · Answer 6

Actually we are taking DPM backup .So backups are not scheduled at all.

sorry! not DPM backup,Instead .MDF & .LDF files are backing up to tape on a daily basis.Here the principal database is in clustered environment

george sibbald SSC Guru Points: 104210 More actions · Answer 7

If log is only 1% used suggests it is being backed up. check the properties of the database, does it have a value for last log backup?

Also does this query return any rows:

select * from msdb..backupset where database_name = 'SharedServices1' and type = 'L'

(check I have correct dbname)

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

homebrew01 SSC Guru Points: 55548 More actions · Answer 8

george sibbald (10/11/2010)
If log is only 1% used suggests it is being backed up. check the properties of the database, does it have a value for last log backup?
Also does this query return any rows:
select * from msdb..backupset where database_name = 'SharedServices1' and type = 'L'
(check I have correct dbname)

You could also check TYPE = 'D' to verify Full backups are occuring regularly.

shanila_minnu Hall of Fame Points: 3465 More actions · Answer 9

select * from msdb..backupset where database_name = 'SharedServices1' and type = 'L'
(check I have correct dbname)

Above query is returning results in such a way that log back up is happening daily once excluding weekends.But

it is not through maintenance plan or job.

From error log we are getting

2010-10-08 19:13:21.87 Backup Database backed up. Database: SharedServices1_Search_DB, creation date(time): 2008/10/22(09:41:22), pages dumped: 1, first LSN: 90725:81:37, last LSN: 90755:493:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{A962F524-3DFE-45E3-B77F-DC45EC19CE9A}1'}). This is an informational message only. No user action is required.

2010-10-08 19:15:54.57 Backup Log was backed up. Database: SharedServices1_Search_DB, creation date(time): 2008/10/22(09:41:22), first LSN: 90725:210:1, last LSN: 90755:545:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'F:\SQL Logs\MSSQL.1\MSSQL\Log\DPM_SQL_PROTECT\MOSSCLUSTER01\SharedServices1_Search_DB_log.LDF\TempLog\TempLog.log'}). This is an informational message only. No user action is required.

Do you have any idea what the above log describes

george sibbald SSC Guru Points: 104210 More actions · Answer 10

OK, this looks like MOSS (Sharepoint). That can do its own backups vis sharepoint administration tool. Ask whoever runs that to increase the frequency of tlog backups and include weekends. I would say you had a lot of activity over the weekend.

Just to confirm not an open transaction run dbcc opentran whilst connected to databases and also dbcc loginfo(SharedServices1), check that column status does not have a value of 2 anywhere near the last row returned.

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

shanila_minnu Hall of Fame Points: 3465 More actions · Answer 11

Just to confirm not an open transaction run dbcc opentran whilst connected to databases and also dbcc loginfo(SharedServices1), check that column status does not have a value of 2 anywhere near the last row returned.

Dbcc opentran is not returning any results.But dbcc loginfo is returning the value 2 for the column status

2126025728215564288009130406491290000001548200454

2126025728216824545289130526491290000001548200454

2126025728218084802569130626491290000001548200454

21260257282193450598400091290000001548200454

21266810882206053171200091290000001548200454

last five rows is pasted above

george sibbald SSC Guru Points: 104210 More actions · Answer 12

running a log backup should shift those. Find out who is controlling sharepoint administration.

Personally I would turn their log backups off and rely on native SQL ones.

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

shanila_minnu Hall of Fame Points: 3465 More actions · Answer 13

george sibbald (10/11/2010)
running a log backup should shift those. Find out who is controlling sharepoint administration.
Personally I would turn their log backups off and rely on native SQL ones.

what is the significance of status 2.Can you please share.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 14

shanila_minnu (10/11/2010)
Instead .MDF & .LDF files are backing up to tape on a daily basis.Here the principal database is in clustered environment

That is not how you take SQL backups. File backups are typically useless as SQL has the files locked open and due to the way it caches and writes to the files.

Make sure that you have database backups or all your databases, log backups of critical ones.

Transaction log backups do not impact mirroring in any way, regardless of how they are created and scheduled.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

george sibbald SSC Guru Points: 104210 More actions · Answer 15

shanila_minnu (10/11/2010)
george sibbald (10/11/2010)
running a log backup should shift those. Find out who is controlling sharepoint administration.
Personally I would turn their log backups off and rely on native SQL ones.
what is the significance of status 2.Can you please share.

logs are divided into vlf's (virtual log files). dbcc loginfo returns 1 row for each vlf. A status of 2 means that vlf contains active transactions. Books Online (BOL) explains it well.

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