October 11, 2010 at 8:30 am
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.
---------------------------------------------------------------------
October 11, 2010 at 8:31 am
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
October 11, 2010 at 8:32 am
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 .....
October 11, 2010 at 8:38 am
[/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_DB 21159.37 0.8902307 0
October 11, 2010 at 8:41 am
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
October 11, 2010 at 8:47 am
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
October 11, 2010 at 8:58 am
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)
---------------------------------------------------------------------
October 11, 2010 at 9:08 am
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.
October 11, 2010 at 9:21 am
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
October 11, 2010 at 9:37 am
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.
---------------------------------------------------------------------
October 11, 2010 at 9:46 am
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
2 126025728 21556428800 91304 0 64 91290000001548200454
2 126025728 21682454528 91305 2 64 91290000001548200454
2 126025728 21808480256 91306 2 64 91290000001548200454
2 126025728 21934505984 0 0 0 91290000001548200454
2 126681088 22060531712 0 0 0 91290000001548200454
last five rows is pasted above
October 11, 2010 at 9:50 am
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.
---------------------------------------------------------------------
October 11, 2010 at 9:57 am
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.
October 11, 2010 at 10:10 am
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
October 11, 2010 at 10:28 am
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.
---------------------------------------------------------------------
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply