October 13, 2017 at 3:54 am
You can query the backup tables in msdb to find out the size of your log backups. Take the largest size of those, add a safety marging of 50% (say), and that's the size your transaction log needs to be.
You don't need to change the recovery model to Simple to shrink the log file.
John
October 13, 2017 at 4:01 am
It might also be worth checking the results of DBCC OPENTRAN in your problematic database.
Also, have a look at the results of
SELECT log_reuse_desc from sys.databases where name = 'yourdatabasenamehere'
Thomas Rushton
blog: https://thelonedba.wordpress.com
October 13, 2017 at 4:47 am
Guys, thank you
Actually, 80 % I'm sure that I found the reason for the growth of the log in 18 September.
The database is used by the application. Its a special application developed for our company!
I talked with the person who administering this application and he says that he running application task - "regeneration of data representation"
(he does not know what this procedure does (undocumented procedure), but he said that she scans all tables and recreates objects in the database)
in 18 September evening.
I tried to do DBCC OPENTRAN , but no results..
And what about SELECT log_reuse_desc from sys.databases where name = 'Mydatabase' , result - "LOG BACKUP"
October 13, 2017 at 5:23 am
after query
dbcc sqlperf(logspace)
go
Database Name Log Size (MB) Log Space Used (%) Status
master 1,992188 29,01961 0
tempdb 259,1172 38,33931 0
model 3,367188 95,93967 0
msdb 31,67969 30,09864 0
Mydatabase 449928,6 0,0718155 0
October 13, 2017 at 5:26 am
dmbsseller - Friday, October 13, 2017 4:47 AMGuys, thank you
Actually, 80 % I'm sure that I found the reason for the growth of the log in 18 September.
The database is used by the application. Its a special application developed for our company!
I talked with the person who administering this application and he says that he running application task - "regeneration of data representation"
(he does not know what this procedure does (undocumented procedure), but he said that she scans all tables and recreates objects in the database)
in 18 September evening.
I tried to do DBCC OPENTRAN , but no results..
And what about SELECT log_reuse_desc from sys.databases where name = 'Mydatabase' , result - "LOG BACKUP"
Yikes. undocument procedure "scans all tables and recreates objects in the database" - that's rather disturbing...
...and another indicator that you should be running TLog backups at all times (possibly at a higher frequency, eg every 10-15 minutes), rather than hourly during office hours.
Thomas Rushton
blog: https://thelonedba.wordpress.com
Viewing 5 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply