|
How have you done the log backup?
(Remember that even full database backup will not truncate log if you are using full recovery mode...Only direct log backup will work here)
Skipper: We'll fix it.
Alex: Fix it? How you gonna fix this?
Skipper: Grit, spit and a whole lotta duct tape.
|
|
|
|
|
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.
|
|
|
|
|
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.
|
|
|
|
|
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)
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.
|
|
|
|
|
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.
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
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 ?
|
|
|
|
|
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.
|
|
|
|
|
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 ?
|
|
|
|
|
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.
|
|
|
|
|
We all have functions and code for business day calculations, or last day of the month calculations, ect... I am adding a caveat to this for Last business day of the month, not a holiday. I.e. (hypothetical), Last day is 12/31/2015, this is a Friday, however the company give this day off as a holiday so 12/30/2015 needs to be determined as the last business day of the month.
I am looking at a couple of ways to approach this.
1) Calculate the Last Day of the month, Determine if it is not a Sat\sun, if is DateDiff to a business day, then take that date and compare to a Holiday table and adjust accordingly.
2) use a function that already exists for Last Business day of the month and then take that date and compare to a Holiday table and adjust accordingly.
3) some better what that I can not think of and no we can not use the XLeratorDB or what ever that is called.
The out put I am looking for is just a DATE, the date of the very last company business day of the month.
Thank you In advance!
No reason to re-invent the wheel, just improve upon existing.
|
|
|
|
|
swoozie1 wrote: use a function that already exists for Last Business day of the month
Maybe somneone already wrote one, but there's no such thing built in, AFAIK. What a "week" or "weekend" is, is determined by your locale, and a company may have multiple of those. Holidays are even more complex, and tend to change rather frequently.
Create a loop that inserts weekdays for your locale in a table; that way you have list with the weekends already eliminated. Next, delete every date in there that is mentioned in the holidays table (or join, or make a new table). Select the top result for that month.
There's your last theoretical work-day.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
I use a view and a holiday table as I need to keep holidays for a number of countries/currencies. The view starts at the beginning of last year and goes for 10 years, arbitrarily picked by me, it has date part DayOfWeek so I can exclude weekends.
It will be fairly simple to combine the two as Eddy suggested, fiddling with the filters/joins to get the last working sate.
Never underestimate the power of human stupidity
RAH
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
