Unable to shrink the transaction log file in Sql Server 2008 The Next CEO of Stack OverflowSQL Server 2008 Recovery Mode reverts from FULL to SIMPLESQL 2008 - Transaction log is fullHow do I identify and fix the cause of transaction log growth on SIMPLE recovery model databases?Minimizing SQL transaction log file size on developer box running simple recovery modelHow to reduce the SQL Server transaction log on the fly for production DB?Sql Server 2008 Backup and RestoreBackup and Restore Strategy in sql server 2008SQL Server 2008 - Restore database without restoring transaction log (LDF)SQL Server transaction log BACKUPS are very largeSQL Server 2008: Large transaction log file in Simple Recovery Mode
Why is quantifier elimination desirable for a given theory?
Does soap repel water?
Is it possible to use a NPN BJT as switch, from single power source?
Why doesn't UK go for the same deal Japan has with EU to resolve Brexit?
Would this house-rule that treats advantage as a +1 to the roll instead (and disadvantage as -1) and allows them to stack be balanced?
Why does standard notation not preserve intervals (visually)
How to avoid supervisors with prejudiced views?
Do I need to write [sic] when a number is less than 10 but isn't written out?
Solving system of ODEs with extra parameter
INSERT to a table from a database to other (same SQL Server) using Dynamic SQL
Is there a difference between "Fahrstuhl" and "Aufzug"
Can a Bladesinger Wizard use Bladesong with a Hand Crossbow?
How did people program for Consoles with multiple CPUs?
Method for adding error messages to a dictionary given a key
How to invert MapIndexed on a ragged structure? How to construct a tree from rules?
Would a grinding machine be a simple and workable propulsion system for an interplanetary spacecraft?
Is micro rebar a better way to reinforce concrete than rebar?
How to get from Geneva Airport to Metabief?
I believe this to be a fraud - hired, then asked to cash check and send cash as Bitcoin
A Man With a Stainless Steel Endoskeleton (like The Terminator) Fighting Cloaked Aliens Only He Can See
Is it convenient to ask the journal's editor for two additional days to complete a review?
What connection does MS Office have to Netscape Navigator?
Proper way to express "He disappeared them"
"misplaced omit" error when >centering columns
Unable to shrink the transaction log file in Sql Server 2008
The Next CEO of Stack OverflowSQL Server 2008 Recovery Mode reverts from FULL to SIMPLESQL 2008 - Transaction log is fullHow do I identify and fix the cause of transaction log growth on SIMPLE recovery model databases?Minimizing SQL transaction log file size on developer box running simple recovery modelHow to reduce the SQL Server transaction log on the fly for production DB?Sql Server 2008 Backup and RestoreBackup and Restore Strategy in sql server 2008SQL Server 2008 - Restore database without restoring transaction log (LDF)SQL Server transaction log BACKUPS are very largeSQL Server 2008: Large transaction log file in Simple Recovery Mode
Am using the below to script shrink the transaction log
*USE AVPC
GO
BACKUP DATABASE AVPC
TO DISK = 'H:DBBackupAVPCFullBackUpAVPC.bak'
USE AVPC
GO
BACKUP LOG AVPC
TO DISK = 'H:DBBackupAVPCTransactionAVPC.trn'
GO
USE AVPC
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AVPC
SET RECOVERY SIMPLE;
GO
checkpoint
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AVPC_log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AVPC
SET RECOVERY FULL;
GO*
But I was not able to succeed, I didnt get any error but the log didnt shrink.I also verified for any open transactions,but there was none.
And also we do Full Back for twice a week and transaction log back up for every mins.
Can any one help me to shrink the log file.It is really growing big.
sql-server-2008
asked Dec 16 '11 at 11:52
user18576user18576
13637
bumped to the homepage by Community♦ yesterday
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
Am using the below to script shrink the transaction log
*USE AVPC
GO
BACKUP DATABASE AVPC
TO DISK = 'H:DBBackupAVPCFullBackUpAVPC.bak'
USE AVPC
GO
BACKUP LOG AVPC
TO DISK = 'H:DBBackupAVPCTransactionAVPC.trn'
GO
USE AVPC
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AVPC
SET RECOVERY SIMPLE;
GO
checkpoint
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AVPC_log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AVPC
SET RECOVERY FULL;
GO*
But I was not able to succeed, I didnt get any error but the log didnt shrink.I also verified for any open transactions,but there was none.
And also we do Full Back for twice a week and transaction log back up for every mins.
Can any one help me to shrink the log file.It is really growing big.
sql-server-2008
asked Dec 16 '11 at 11:52
user18576user18576
13637
bumped to the homepage by Community♦ yesterday
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
Am using the below to script shrink the transaction log
*USE AVPC
GO
BACKUP DATABASE AVPC
TO DISK = 'H:DBBackupAVPCFullBackUpAVPC.bak'
USE AVPC
GO
BACKUP LOG AVPC
TO DISK = 'H:DBBackupAVPCTransactionAVPC.trn'
GO
USE AVPC
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AVPC
SET RECOVERY SIMPLE;
GO
checkpoint
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AVPC_log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AVPC
SET RECOVERY FULL;
GO*
But I was not able to succeed, I didnt get any error but the log didnt shrink.I also verified for any open transactions,but there was none.
And also we do Full Back for twice a week and transaction log back up for every mins.
Can any one help me to shrink the log file.It is really growing big.
sql-server-2008
asked Dec 16 '11 at 11:52
user18576user18576
13637
Am using the below to script shrink the transaction log
*USE AVPC
GO
BACKUP DATABASE AVPC
TO DISK = 'H:DBBackupAVPCFullBackUpAVPC.bak'
USE AVPC
GO
BACKUP LOG AVPC
TO DISK = 'H:DBBackupAVPCTransactionAVPC.trn'
GO
USE AVPC
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AVPC
SET RECOVERY SIMPLE;
GO
checkpoint
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AVPC_log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AVPC
SET RECOVERY FULL;
GO*
But I was not able to succeed, I didnt get any error but the log didnt shrink.I also verified for any open transactions,but there was none.
And also we do Full Back for twice a week and transaction log back up for every mins.
Can any one help me to shrink the log file.It is really growing big.
sql-server-2008
sql-server-2008
asked Dec 16 '11 at 11:52
user18576user18576
13637
asked Dec 16 '11 at 11:52
user18576user18576
13637
asked Dec 16 '11 at 11:52
user18576user18576
13637
asked Dec 16 '11 at 11:52
user18576user18576
13637
asked Dec 16 '11 at 11:52
user18576user18576
13637
13637
bumped to the homepage by Community♦ yesterday
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ yesterday
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
DBCC LOGINFO will show you the active portion of your transaction log. Is the active part of the log at the end?
answered Dec 19 '11 at 19:06
jl.jl.
1,076810
add a comment |
It also depends on sizes of your Virtual Log Files inside of your log
If the log file was created as 16 Gb file, it has 16 VLFs of 1 Gb each inside and thus can be shrunk only to 1 Gb size, not less. And if the active VLF is the 3rd for example - it will not shrink less than 3 Gb
answered Dec 19 '11 at 19:28
Oleg DokOleg Dok
26125
add a comment |
Your Answer
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "2"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fserverfault.com%2fquestions%2f341584%2funable-to-shrink-the-transaction-log-file-in-sql-server-2008%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
DBCC LOGINFO will show you the active portion of your transaction log. Is the active part of the log at the end?
answered Dec 19 '11 at 19:06
jl.jl.
1,076810
add a comment |
DBCC LOGINFO will show you the active portion of your transaction log. Is the active part of the log at the end?
answered Dec 19 '11 at 19:06
jl.jl.
1,076810
add a comment |
DBCC LOGINFO will show you the active portion of your transaction log. Is the active part of the log at the end?
answered Dec 19 '11 at 19:06
jl.jl.
1,076810
DBCC LOGINFO will show you the active portion of your transaction log. Is the active part of the log at the end?
answered Dec 19 '11 at 19:06
jl.jl.
1,076810
answered Dec 19 '11 at 19:06
jl.jl.
1,076810
answered Dec 19 '11 at 19:06
jl.jl.
1,076810
answered Dec 19 '11 at 19:06
jl.jl.
1,076810
1,076810
add a comment |
add a comment |
It also depends on sizes of your Virtual Log Files inside of your log
If the log file was created as 16 Gb file, it has 16 VLFs of 1 Gb each inside and thus can be shrunk only to 1 Gb size, not less. And if the active VLF is the 3rd for example - it will not shrink less than 3 Gb
answered Dec 19 '11 at 19:28
Oleg DokOleg Dok
26125
add a comment |
It also depends on sizes of your Virtual Log Files inside of your log
If the log file was created as 16 Gb file, it has 16 VLFs of 1 Gb each inside and thus can be shrunk only to 1 Gb size, not less. And if the active VLF is the 3rd for example - it will not shrink less than 3 Gb
answered Dec 19 '11 at 19:28
Oleg DokOleg Dok
26125
add a comment |
It also depends on sizes of your Virtual Log Files inside of your log
If the log file was created as 16 Gb file, it has 16 VLFs of 1 Gb each inside and thus can be shrunk only to 1 Gb size, not less. And if the active VLF is the 3rd for example - it will not shrink less than 3 Gb
answered Dec 19 '11 at 19:28
Oleg DokOleg Dok
26125
It also depends on sizes of your Virtual Log Files inside of your log
If the log file was created as 16 Gb file, it has 16 VLFs of 1 Gb each inside and thus can be shrunk only to 1 Gb size, not less. And if the active VLF is the 3rd for example - it will not shrink less than 3 Gb
answered Dec 19 '11 at 19:28
Oleg DokOleg Dok
26125
answered Dec 19 '11 at 19:28
Oleg DokOleg Dok
26125
answered Dec 19 '11 at 19:28
Oleg DokOleg Dok
26125
answered Dec 19 '11 at 19:28
Oleg DokOleg Dok
26125
26125
add a comment |
add a comment |
Thanks for contributing an answer to Server Fault!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fserverfault.com%2fquestions%2f341584%2funable-to-shrink-the-transaction-log-file-in-sql-server-2008%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
