LDF file Size is so increased
-
Dear I take full back of DB after that i take only logs backup then try to shrink the log file but failed to reduce the LDF size.
Can you share the commands you run?
Skipper: We'll fix it. Alex: Fix it? How you gonna fix this? Skipper: Grit, spit and a whole lotta duct tape.
-
Can you share the commands you run?
Skipper: We'll fix it. Alex: Fix it? How you gonna fix this? Skipper: Grit, spit and a whole lotta duct tape.
-
I do with MS management studio.
OK. Explain how! (when you are using the Management Studio you have a 'Script' button at the top of the backup window. Pressing that button will give you the script Management Studio will run - copy that here!) (I ask you this because these kind of things are working so probably you miss something and I try to figure out what)
Skipper: We'll fix it. Alex: Fix it? How you gonna fix this? Skipper: Grit, spit and a whole lotta duct tape.
-
OK. Explain how! (when you are using the Management Studio you have a 'Script' button at the top of the backup window. Pressing that button will give you the script Management Studio will run - copy that here!) (I ask you this because these kind of things are working so probably you miss something and I try to figure out what)
Skipper: We'll fix it. Alex: Fix it? How you gonna fix this? Skipper: Grit, spit and a whole lotta duct tape.
Please see the generated script USE [TEST_APP] GO DBCC SHRINKFILE (N'TEST_APP_Log' , 0, TRUNCATEONLY) GO I just want to truncate the logs.
-
Please see the generated script USE [TEST_APP] GO DBCC SHRINKFILE (N'TEST_APP_Log' , 0, TRUNCATEONLY) GO I just want to truncate the logs.
TRUNCATE_ONLY option does not reorganize the file but try to free the empty block at the end of the log file...It is possible that without page-reorganization there is no actual space to free... Instead of TRUNCATE_ONLY use tager_size...
DBCC SHRINKFILE('your-log-file', 200) -- for 200 MB
It will take much more time as SQL will try to reorganize pages inside the log file and drop them if marked properly by the backup process...
Skipper: We'll fix it. Alex: Fix it? How you gonna fix this? Skipper: Grit, spit and a whole lotta duct tape.
-
TRUNCATE_ONLY option does not reorganize the file but try to free the empty block at the end of the log file...It is possible that without page-reorganization there is no actual space to free... Instead of TRUNCATE_ONLY use tager_size...
DBCC SHRINKFILE('your-log-file', 200) -- for 200 MB
It will take much more time as SQL will try to reorganize pages inside the log file and drop them if marked properly by the backup process...
Skipper: We'll fix it. Alex: Fix it? How you gonna fix this? Skipper: Grit, spit and a whole lotta duct tape.
I just need some help to configure the DB as when i take bake up of LDF then DB must truncate the logs and LDF again in 2mb size. is it possible ? i am doing the same exercise on another DB but on this i am failed.
-
I just need some help to configure the DB as when i take bake up of LDF then DB must truncate the logs and LDF again in 2mb size. is it possible ? i am doing the same exercise on another DB but on this i am failed.
Not with your model...
Skipper: We'll fix it. Alex: Fix it? How you gonna fix this? Skipper: Grit, spit and a whole lotta duct tape.
-
Not with your model...
Skipper: We'll fix it. Alex: Fix it? How you gonna fix this? Skipper: Grit, spit and a whole lotta duct tape.
-
Can i change the model ? how ?
ALTER DATABASE [your-database-name] SET RECOVERY SIMPLE;
Skipper: We'll fix it. Alex: Fix it? How you gonna fix this? Skipper: Grit, spit and a whole lotta duct tape.
-
ALTER DATABASE [your-database-name] SET RECOVERY SIMPLE;
Skipper: We'll fix it. Alex: Fix it? How you gonna fix this? Skipper: Grit, spit and a whole lotta duct tape.
my issue is still with me. on recovery model simple nothing happend ... can you suggest me some more options??
-
my issue is still with me. on recovery model simple nothing happend ... can you suggest me some more options??
After changing to simple try shrinking the log file
Mongo: Mongo only pawn... in game of life.
-
my issue is still with me. on recovery model simple nothing happend ... can you suggest me some more options??
Are you using replication?
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
Are you using replication?
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
No i am not using replication
In which case all you need to read this article[^]
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
ALTER DATABASE [your-database-name] SET RECOVERY SIMPLE;
Skipper: We'll fix it. Alex: Fix it? How you gonna fix this? Skipper: Grit, spit and a whole lotta duct tape.
how can i check which one is my current recovery model?? i change it from DB options but on changing full to simple LOG backup is not possible... ** DB is showing its size 221GB including log instead of showing only MDF file size ? what should i do next ?
-
ALTER DATABASE [your-database-name] SET RECOVERY SIMPLE;
Skipper: We'll fix it. Alex: Fix it? How you gonna fix this? Skipper: Grit, spit and a whole lotta duct tape.
how can i check the old recovery model? i try to change it from DB options but after changing to simple LOGS backup is not possible. DB is showing its size 221GB including LDF size.. Any suggestion to get out from this issue.
-
No i am not using replication
Try running the following query:
SELECT
[name],
recovery_model_desc,
log_reuse_wait_desc
FROM
sys.databases
WHERE
[name] = 'YourDatabaseName'Compare the value returned to the list on TechNet: Factors That Can Delay Log Truncation[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
Try running the following query:
SELECT
[name],
recovery_model_desc,
log_reuse_wait_desc
FROM
sys.databases
WHERE
[name] = 'YourDatabaseName'Compare the value returned to the list on TechNet: Factors That Can Delay Log Truncation[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
running this query, i come to know that log_reuse_wait_desc is REPLICATION. what should i do next ?
-
running this query, i come to know that log_reuse_wait_desc is REPLICATION. what should i do next ?
Is your database set up for replication? If so, the replication is broken and you'll need to fix it. If it's not set up for replication, then you'll need to remove the replication settings. Try sp_removedbreplication[^]:
EXEC sp_removedbreplication 'YourDatabaseName'
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
Is your database set up for replication? If so, the replication is broken and you'll need to fix it. If it's not set up for replication, then you'll need to remove the replication settings. Try sp_removedbreplication[^]:
EXEC sp_removedbreplication 'YourDatabaseName'
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
Thank you so much its work for me. thank you so much for you cooperation.
