SQL Server log_reuse_wait_desc LOG_BACKUP, Transaction log full, does not become smaller after log backup
up vote
0
down vote
favorite
I did the backup of database which is small 2MB.
I have transaction log file (LDF file) of 15GB!
My database is in Full recovery mode.
First I tried to do the backup of transaction log but it failed because of the space on the server.
Then I succeeded to do the backup of transaction log and now backup+backup of transaction log have 9MB.
statement SELECT name,log_reuse_wait_desc FROM sys.databases; returns now NOTHING instead of LOG_BACKUP which is good.But transaction log is still 15 GB.
What should I do to release it?
sql-server database-backups transaction-log asked Jan 29 '15 at 14:17
Dejan
55382147
add a comment |
up vote
0
down vote
favorite
I did the backup of database which is small 2MB.
I have transaction log file (LDF file) of 15GB!
My database is in Full recovery mode.
First I tried to do the backup of transaction log but it failed because of the space on the server.
Then I succeeded to do the backup of transaction log and now backup+backup of transaction log have 9MB.
statement SELECT name,log_reuse_wait_desc FROM sys.databases; returns now NOTHING instead of LOG_BACKUP which is good.But transaction log is still 15 GB.
What should I do to release it?
sql-server database-backups transaction-log asked Jan 29 '15 at 14:17
Dejan
55382147
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I did the backup of database which is small 2MB.
I have transaction log file (LDF file) of 15GB!
My database is in Full recovery mode.
First I tried to do the backup of transaction log but it failed because of the space on the server.
Then I succeeded to do the backup of transaction log and now backup+backup of transaction log have 9MB.
statement SELECT name,log_reuse_wait_desc FROM sys.databases; returns now NOTHING instead of LOG_BACKUP which is good.But transaction log is still 15 GB.
What should I do to release it?
sql-server database-backups transaction-log asked Jan 29 '15 at 14:17
Dejan
55382147
I did the backup of database which is small 2MB.
I have transaction log file (LDF file) of 15GB!
My database is in Full recovery mode.
First I tried to do the backup of transaction log but it failed because of the space on the server.
Then I succeeded to do the backup of transaction log and now backup+backup of transaction log have 9MB.
statement SELECT name,log_reuse_wait_desc FROM sys.databases; returns now NOTHING instead of LOG_BACKUP which is good.But transaction log is still 15 GB.
What should I do to release it?
sql-server database-backups transaction-log sql-server database-backups transaction-log asked Jan 29 '15 at 14:17
Dejan
55382147
asked Jan 29 '15 at 14:17
Dejan
55382147
edited Jan 29 '15 at 14:41
asked Jan 29 '15 at 14:17
Dejan
55382147
asked Jan 29 '15 at 14:17
Dejan
55382147
asked Jan 29 '15 at 14:17
Dejan
55382147
55382147
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
up vote
0
down vote
If you aren't regularly backing up the transaction log, the log fie will continue to grow. If you are willing to risk losing changes since your last backup, change the database recovery model to Simple. My experience has been that SQL Server will shrink the log file automatically in a short while after making this change. If it does not, you may need to issue a manual shrink.
You can switch back to Full recovery afterwards-- but verify you are performing the regularly scheduled log backups required for Full recovery.
There are many resources online about recovery models. Here is an article on MSDN about Backup Under the Full Recovery Model.
answered Jan 29 '15 at 14:30
Paul Williams
12.7k33575
Hi Paul, thank you for your time I did some change in my question. I succeeded to do the backup of transaction log (it is 9MB). I changed the recovery model to Simple. but Log is still 15GB although database is only 2MB. Log does not become smaller although it is Simple model. Do I have to do some restart?
– Dejan
Jan 29 '15 at 14:33
Can you give me instruction how to do the Shrink? Do I have to do the shrink of the database or of the files?
– Dejan
Jan 29 '15 at 14:36
Hi Paul, now in database is the value NOTHING for ClickMobile which is good. But file is still 15GB? Any idea please?
– Dejan
Jan 29 '15 at 14:40
Paul can you please assist me with this what to do? LDF file is stil 15GB:(
– Dejan
Jan 29 '15 at 15:02
You may need to do a 1DBCC SHRINKFILE1 to get it to shrink afterwards.
– Paul Williams
Jan 29 '15 at 23:17
|
show 1 more comment
up vote
0
down vote
The log file size on disk doesn't get smaller after a log backup. The reason for this is that the engine assumes that it's likely that it needs to be that size for future use and growing the log file is an expensive (in terms of time) operation. What the log backup does is mark the internal structures (called "virtual log files" or "VLFs") in the log file as available for reuse. You can check the status of the VLFs in a couple of different ways:
DBCC SQLPERF('LOGSPACE')will show you how much of the log is internally used as a percentageDBCC LOGINFOwill show you the information regarding each VLF in the log file.
All that said, if you know for a fact that it's unlikely that you'll need a 15 Gb log file going forward, you can shrink the file using DBCC SHRINKFILE. There's a lot of good information out there on VLFs in general. This blog post is a good start.
answered Jan 29 '15 at 17:54
Ben Thul
22k32445
add a comment |
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
If you aren't regularly backing up the transaction log, the log fie will continue to grow. If you are willing to risk losing changes since your last backup, change the database recovery model to Simple. My experience has been that SQL Server will shrink the log file automatically in a short while after making this change. If it does not, you may need to issue a manual shrink.
You can switch back to Full recovery afterwards-- but verify you are performing the regularly scheduled log backups required for Full recovery.
There are many resources online about recovery models. Here is an article on MSDN about Backup Under the Full Recovery Model.
answered Jan 29 '15 at 14:30
Paul Williams
12.7k33575
Hi Paul, thank you for your time I did some change in my question. I succeeded to do the backup of transaction log (it is 9MB). I changed the recovery model to Simple. but Log is still 15GB although database is only 2MB. Log does not become smaller although it is Simple model. Do I have to do some restart?
– Dejan
Jan 29 '15 at 14:33
Can you give me instruction how to do the Shrink? Do I have to do the shrink of the database or of the files?
– Dejan
Jan 29 '15 at 14:36
Hi Paul, now in database is the value NOTHING for ClickMobile which is good. But file is still 15GB? Any idea please?
– Dejan
Jan 29 '15 at 14:40
Paul can you please assist me with this what to do? LDF file is stil 15GB:(
– Dejan
Jan 29 '15 at 15:02
You may need to do a 1DBCC SHRINKFILE1 to get it to shrink afterwards.
– Paul Williams
Jan 29 '15 at 23:17
|
show 1 more comment
up vote
0
down vote
If you aren't regularly backing up the transaction log, the log fie will continue to grow. If you are willing to risk losing changes since your last backup, change the database recovery model to Simple. My experience has been that SQL Server will shrink the log file automatically in a short while after making this change. If it does not, you may need to issue a manual shrink.
You can switch back to Full recovery afterwards-- but verify you are performing the regularly scheduled log backups required for Full recovery.
There are many resources online about recovery models. Here is an article on MSDN about Backup Under the Full Recovery Model.
answered Jan 29 '15 at 14:30
Paul Williams
12.7k33575
Hi Paul, thank you for your time I did some change in my question. I succeeded to do the backup of transaction log (it is 9MB). I changed the recovery model to Simple. but Log is still 15GB although database is only 2MB. Log does not become smaller although it is Simple model. Do I have to do some restart?
– Dejan
Jan 29 '15 at 14:33
Can you give me instruction how to do the Shrink? Do I have to do the shrink of the database or of the files?
– Dejan
Jan 29 '15 at 14:36
Hi Paul, now in database is the value NOTHING for ClickMobile which is good. But file is still 15GB? Any idea please?
– Dejan
Jan 29 '15 at 14:40
Paul can you please assist me with this what to do? LDF file is stil 15GB:(
– Dejan
Jan 29 '15 at 15:02
You may need to do a 1DBCC SHRINKFILE1 to get it to shrink afterwards.
– Paul Williams
Jan 29 '15 at 23:17
|
show 1 more comment
up vote
0
down vote
up vote
0
down vote
If you aren't regularly backing up the transaction log, the log fie will continue to grow. If you are willing to risk losing changes since your last backup, change the database recovery model to Simple. My experience has been that SQL Server will shrink the log file automatically in a short while after making this change. If it does not, you may need to issue a manual shrink.
You can switch back to Full recovery afterwards-- but verify you are performing the regularly scheduled log backups required for Full recovery.
There are many resources online about recovery models. Here is an article on MSDN about Backup Under the Full Recovery Model.
answered Jan 29 '15 at 14:30
Paul Williams
12.7k33575
If you aren't regularly backing up the transaction log, the log fie will continue to grow. If you are willing to risk losing changes since your last backup, change the database recovery model to Simple. My experience has been that SQL Server will shrink the log file automatically in a short while after making this change. If it does not, you may need to issue a manual shrink.
You can switch back to Full recovery afterwards-- but verify you are performing the regularly scheduled log backups required for Full recovery.
There are many resources online about recovery models. Here is an article on MSDN about Backup Under the Full Recovery Model.
answered Jan 29 '15 at 14:30
Paul Williams
12.7k33575
answered Jan 29 '15 at 14:30
Paul Williams
12.7k33575
answered Jan 29 '15 at 14:30
Paul Williams
12.7k33575
answered Jan 29 '15 at 14:30
Paul Williams
12.7k33575
12.7k33575
Hi Paul, thank you for your time I did some change in my question. I succeeded to do the backup of transaction log (it is 9MB). I changed the recovery model to Simple. but Log is still 15GB although database is only 2MB. Log does not become smaller although it is Simple model. Do I have to do some restart?
– Dejan
Jan 29 '15 at 14:33
Can you give me instruction how to do the Shrink? Do I have to do the shrink of the database or of the files?
– Dejan
Jan 29 '15 at 14:36
Hi Paul, now in database is the value NOTHING for ClickMobile which is good. But file is still 15GB? Any idea please?
– Dejan
Jan 29 '15 at 14:40
Paul can you please assist me with this what to do? LDF file is stil 15GB:(
– Dejan
Jan 29 '15 at 15:02
You may need to do a 1DBCC SHRINKFILE1 to get it to shrink afterwards.
– Paul Williams
Jan 29 '15 at 23:17
|
show 1 more comment
Hi Paul, thank you for your time I did some change in my question. I succeeded to do the backup of transaction log (it is 9MB). I changed the recovery model to Simple. but Log is still 15GB although database is only 2MB. Log does not become smaller although it is Simple model. Do I have to do some restart?
– Dejan
Jan 29 '15 at 14:33
Can you give me instruction how to do the Shrink? Do I have to do the shrink of the database or of the files?
– Dejan
Jan 29 '15 at 14:36
Hi Paul, now in database is the value NOTHING for ClickMobile which is good. But file is still 15GB? Any idea please?
– Dejan
Jan 29 '15 at 14:40
Paul can you please assist me with this what to do? LDF file is stil 15GB:(
– Dejan
Jan 29 '15 at 15:02
You may need to do a 1DBCC SHRINKFILE1 to get it to shrink afterwards.
– Paul Williams
Jan 29 '15 at 23:17
Hi Paul, thank you for your time I did some change in my question. I succeeded to do the backup of transaction log (it is 9MB). I changed the recovery model to Simple. but Log is still 15GB although database is only 2MB. Log does not become smaller although it is Simple model. Do I have to do some restart?
– Dejan
Jan 29 '15 at 14:33
Hi Paul, thank you for your time I did some change in my question. I succeeded to do the backup of transaction log (it is 9MB). I changed the recovery model to Simple. but Log is still 15GB although database is only 2MB. Log does not become smaller although it is Simple model. Do I have to do some restart?
– Dejan
Jan 29 '15 at 14:33
Can you give me instruction how to do the Shrink? Do I have to do the shrink of the database or of the files?
– Dejan
Jan 29 '15 at 14:36
Can you give me instruction how to do the Shrink? Do I have to do the shrink of the database or of the files?
– Dejan
Jan 29 '15 at 14:36
Hi Paul, now in database is the value NOTHING for ClickMobile which is good. But file is still 15GB? Any idea please?
– Dejan
Jan 29 '15 at 14:40
Hi Paul, now in database is the value NOTHING for ClickMobile which is good. But file is still 15GB? Any idea please?
– Dejan
Jan 29 '15 at 14:40
Paul can you please assist me with this what to do? LDF file is stil 15GB:(
– Dejan
Jan 29 '15 at 15:02
Paul can you please assist me with this what to do? LDF file is stil 15GB:(
– Dejan
Jan 29 '15 at 15:02
You may need to do a 1DBCC SHRINKFILE1 to get it to shrink afterwards.
– Paul Williams
Jan 29 '15 at 23:17
You may need to do a 1DBCC SHRINKFILE1 to get it to shrink afterwards.
– Paul Williams
Jan 29 '15 at 23:17
|
show 1 more comment
up vote
0
down vote
The log file size on disk doesn't get smaller after a log backup. The reason for this is that the engine assumes that it's likely that it needs to be that size for future use and growing the log file is an expensive (in terms of time) operation. What the log backup does is mark the internal structures (called "virtual log files" or "VLFs") in the log file as available for reuse. You can check the status of the VLFs in a couple of different ways:
DBCC SQLPERF('LOGSPACE')will show you how much of the log is internally used as a percentageDBCC LOGINFOwill show you the information regarding each VLF in the log file.
All that said, if you know for a fact that it's unlikely that you'll need a 15 Gb log file going forward, you can shrink the file using DBCC SHRINKFILE. There's a lot of good information out there on VLFs in general. This blog post is a good start.
answered Jan 29 '15 at 17:54
Ben Thul
22k32445
add a comment |
up vote
0
down vote
The log file size on disk doesn't get smaller after a log backup. The reason for this is that the engine assumes that it's likely that it needs to be that size for future use and growing the log file is an expensive (in terms of time) operation. What the log backup does is mark the internal structures (called "virtual log files" or "VLFs") in the log file as available for reuse. You can check the status of the VLFs in a couple of different ways:
DBCC SQLPERF('LOGSPACE')will show you how much of the log is internally used as a percentageDBCC LOGINFOwill show you the information regarding each VLF in the log file.
All that said, if you know for a fact that it's unlikely that you'll need a 15 Gb log file going forward, you can shrink the file using DBCC SHRINKFILE. There's a lot of good information out there on VLFs in general. This blog post is a good start.
answered Jan 29 '15 at 17:54
Ben Thul
22k32445
add a comment |
up vote
0
down vote
up vote
0
down vote
The log file size on disk doesn't get smaller after a log backup. The reason for this is that the engine assumes that it's likely that it needs to be that size for future use and growing the log file is an expensive (in terms of time) operation. What the log backup does is mark the internal structures (called "virtual log files" or "VLFs") in the log file as available for reuse. You can check the status of the VLFs in a couple of different ways:
DBCC SQLPERF('LOGSPACE')will show you how much of the log is internally used as a percentageDBCC LOGINFOwill show you the information regarding each VLF in the log file.
All that said, if you know for a fact that it's unlikely that you'll need a 15 Gb log file going forward, you can shrink the file using DBCC SHRINKFILE. There's a lot of good information out there on VLFs in general. This blog post is a good start.
answered Jan 29 '15 at 17:54
Ben Thul
22k32445
The log file size on disk doesn't get smaller after a log backup. The reason for this is that the engine assumes that it's likely that it needs to be that size for future use and growing the log file is an expensive (in terms of time) operation. What the log backup does is mark the internal structures (called "virtual log files" or "VLFs") in the log file as available for reuse. You can check the status of the VLFs in a couple of different ways:
DBCC SQLPERF('LOGSPACE')will show you how much of the log is internally used as a percentageDBCC LOGINFOwill show you the information regarding each VLF in the log file.
All that said, if you know for a fact that it's unlikely that you'll need a 15 Gb log file going forward, you can shrink the file using DBCC SHRINKFILE. There's a lot of good information out there on VLFs in general. This blog post is a good start.
answered Jan 29 '15 at 17:54
Ben Thul
22k32445
answered Jan 29 '15 at 17:54
Ben Thul
22k32445
answered Jan 29 '15 at 17:54
Ben Thul
22k32445
answered Jan 29 '15 at 17:54
Ben Thul
22k32445
22k32445
add a comment |
add a comment |
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%2fstackoverflow.com%2fquestions%2f28216905%2fsql-server-log-reuse-wait-desc-log-backup-transaction-log-full-does-not-become%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
