Big Transaction Log
-
Good estimates Evenings ,
Had previously expressed my concern , I have a database that size is huge.
Attached a picture
so that you can appreciate , however the recovery mode is simple. There any way or manner that can reduce this size ?? , and if so that both would impact me to run a script . I've already done SHRINK . But I have not given to reduce this size.
Thanks
-
Can't see the picture.
Shrink is how you reduce the file size, but unless there's a really good reason, and a lot of free space in the file that will never be reused, it's generally better not to bother shrinking.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
-
USE DATABSE;
GO
SELECT file_id, name, type_desc, physical_name, size, max_size
FROM sys.database_files ;
GO
file_id name physical_name size max_size
1 DATABASE_DATA C:\Program File\MySQL 4188064 -1
2 DATABASE_Log C:\Program File\MySQL 124106952 -1
3 DATABASE_Data_01 C:\Program File\MySQL 5400 -1
4 DATABASE_Data_02 C:\Program File\MySQL 256 -1
5 DATABASE_Data_03 C:\Program File\MySQL 128 -1
6 DATABASE_Data_04 C:\Program File\MySQL 2824 -1
7 DATABASE_Data_05 C:\Program File\MySQL 3904 -1
8 DATABASE_Data_06 C:\Program File\MySQL 128 -1
9 DATABASE_Data_07 C:\Program File\MySQL 128 -1
10 DATABASE_Data_08 C:\Program File\MySQL 128 -1
as could not attach the imange , here you step more or less what shows me the execution of this script
-
You want to first understand why the log grew to that size - Do you know why? Are you using CDC, replication, have a long running transaction or any other process that would cause this? If you don't know why then often you really shouldn't shrink if the log needs to be that size for some process. Growing and shrinking files over and over is not a good thing. You really want to understand why it grew and then you can figure out the best way to handle the size issue and size the log accordingly.
If the log grew do to some ad hoc, one off type of thing, you can't afford the disk space it uses and it won't grow like that ever again in your lifetime, then you may want to consider shrinking the file.
You said you tried to shrink the file but it didn't shrink. In that case, you would typically want to check:
SELECT log_reuse_wait_desc
FROM sys.databases
WHERE name = 'NameOfYourDatabase'
Sue
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply