December 20, 2015 at 5:42 pm
HI All ,
I have an issue with my database LOG file which is very big now ( 299 GB ) for 1 database ONLY
The time when I was about to shrink , I noticed the available free space is only 5%
A few days ago I changed the RECOVERY MODE to SIMPLE but it doesn’t help .
I also noticed that FULL backup and Transaction Log Backup were performed ( Log Backup is every 3 hours )
At the moment I really have no idea how to reduce the Log file size .
Any feedback are really appreciated
Thank you
December 20, 2015 at 6:07 pm
It sounds an awful lot like someone has a runaway query going on. What does sp_Who2 have to say about disk IO for currently active transactions?
EXEC sp_Who2 ACTIVE
--Jeff Moden
Change is inevitable... Change for the better is not.
December 20, 2015 at 7:27 pm
Nothing stand out there ( after running EXEC sp_Who2 ACTIVE)
I also run OPEN TRAN to check if there is any open transaction
It said :
Transaction information for database 'XYZ'.
Replicated Transaction Information:
Oldest distributed LSN : (21164:33781:256)
Oldest non-distributed LSN : (0:0:0)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
When I check Other databases
The result is : No active open transactions.
So previously this database is used in Replication but 2 days ago I heard they don’t have replication anymore ..
Will it be due to Replication ?
December 20, 2015 at 8:07 pm
WhiteLotus (12/20/2015)
Nothing stand out there ( after running EXEC sp_Who2 ACTIVE)I also run OPEN TRAN to check if there is any open transaction
It said :
Transaction information for database 'XYZ'.
Replicated Transaction Information:
Oldest distributed LSN : (21164:33781:256)
Oldest non-distributed LSN : (0:0:0)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
When I check Other databases
The result is : No active open transactions.
So previously this database is used in Replication but 2 days ago I heard they don’t have replication anymore ..
Will it be due to Replication ?
It sounds like that's exactly your problem. They may have stopped replicating but the system still thinks it is. There's a command to kill that but I don't know it off the top of my head.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 20, 2015 at 8:12 pm
Not sure but I believe that the following command may do the trick...
sp_removedbreplication [ [ @dbname = ] 'dbname' ]
--Jeff Moden
Change is inevitable... Change for the better is not.
December 20, 2015 at 10:28 pm
Yayy you are right !
Now I get 99% free space . Thanks so much !!
December 21, 2015 at 7:46 am
WhiteLotus (12/20/2015)
Yayy you are right !Now I get 99% free space . Thanks so much !!
Glad that helped. When you get a chance, please mark Jeff's response as having helped you. It will mark the answer with a green highlight so others can zero in on it.
Thanks.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
December 21, 2015 at 8:26 am
WhiteLotus (12/20/2015)
Yayy you are right !Now I get 99% free space . Thanks so much !!
Now you need to shrink it. It's going to take a long time and you might actually want to do it in 5 or 10 GB steps. Before you shrink it, set the Growth to something like 1000MB, then ultimately shrink it to 0 and regrow it to something like 8000MB (8GB) to repair all the VLFs that I think may be present.
Of course, the best thing to do would be to do all of this while no one else was actually in the database. Taking frequent log file backups during the shrink process may help. If not, post back and we'll try a more extreme method.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 21, 2015 at 9:24 am
Is the transaction log competing for space with the data files or another database? Perhaps there is no need to actually shrink the log, just let it recycle.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 21, 2015 at 9:47 am
Eric M Russell (12/21/2015)
Is the transaction log competing for space with the data files or another database? Perhaps there is no need to actually shrink the log, just let it recycle.
Even if the log file isn't competing for space, there's the subject of doing a restore if it should ever become necessary. Unlike an MDF file, which greatly benefits from things like instant file initialization, log files must be "formatted" behind the scenes to map out the VLFs. Even if there aren't so many, a 299GB log file will add a whole lot of time to a restore, which is when you can least afford the extra time.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 21, 2015 at 11:59 am
Once the db is changed to Simple model, it's easy enough to shrink the log, you just have to keep an eye on where the current end-of-file marker is, as you can't shrink to before that point.
Re-growing the log will take some time if you choose to shrink it all the way down, since log space has to be re-formatted. But if the log grew with small allocations, performance-wise you'd likely be better off fully shrinking it, to reduce the number of VLFs. Then re-allocate/expand the log in good-sized chunks, at least 2GB if your drive set can handle that in a responsive time frame.
Specifically, first run this command:
EXEC sp_helpfile
to get the logical_log_file_name, in the first column of the results, which is needed in the commands below.
Then:
CHECKPOINT
DBCC SHRINKFILE(2, 1) --or SHRINKFILE(<logical_log_file_name>, 1)
ALTER DATABASE <db_name> MODIFY FILE ( NAME = <logical_log_file_name>, SIZE = 3GB ) --or 2GB
ALTER DATABASE <db_name> MODIFY FILE ( NAME = <logical_log_file_name>, SIZE = 6GB ) --or 4GB
ALTER DATABASE <db_name> MODIFY FILE ( NAME = <logical_log_file_name>, SIZE = 9GB ) --or 6GB
--...
--to as big as you need to make the log: if you want to make the log really large, use 3GB+ increments,
--again so you don't get too many VLFs.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 21, 2015 at 4:31 pm
And if you do cycle through the SIMPLE Recovery Model, don't forget to at least do a DIF backup after you return to FULL recovery to restart the log chain.
Also understand that once you go to the SIMPLE Recovery Model, you can't do a restore up to that point. You'll only be able to do a restore up to and trough your last log file backup, which you should do immediately before going to SIMPLE.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 21, 2015 at 6:35 pm
Yeah, everybody always "warns" about that, but who on earth would want to apply a 299GB log anyway??
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 21, 2015 at 6:52 pm
ScottPletcher (12/21/2015)
Yeah, everybody always "warns" about that, but who on earth would want to apply a 299GB log anyway??
Obviously and especially in this case, it wouldn't. The log file was cleared by stopping replication leaving only that (1% or less) that hadn't yet been backed up.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 21, 2015 at 7:22 pm
I haven't changed the autogrow option yet , it is still in 10% growth position.
I am thinking to change it to 512 MB . What do you think ? Is it still OK ?
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply