Hello. I've got a situation where a live database has stopped working because
the transaction logs have filled all available disk space. I would really
appreciate any advice on how to get it running again. My knowledge of SQL
Server 2000 is very limited so I would need so quite detailed instructions! I
am happy with entering statements into Query Analyzer.
I've tried to shrink the transaction log by running a backup on it in
Enterprise Manager. However, the largest .LDF file is 33Gb and there is not
enough disk space to back this up.
So somehow I need to shrink these .LDF transaction log files. Also, if
anyone could advise on how to stop this situation happening again, that'd be
fantastic. If anyone can give advice on how to do this, you really would be
saving my life!
1. Consider the recovery model for the database. If not "simple" then SQL Server will not empty the
log files automatically, and the log will keep growing. When not "simple", only backup of the
transaction log will empty the log files.
2. After either setting to simple or scheduling regular transaction log backups, verify that the log
files are more or less empty. Use:
DBCC SQLPERF(LOGSPACE)
3. Now time for the shrink. Use DBCC SHRINKFILE, (not SHRINKDATABASE), as documented in Books
Online.
Also, there are some links and general info about shrink on
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Fluffy_Ninja" <FluffyNinja@.discussions.microsoft.com> wrote in message
news:C338EACF-9E37-47B3-B307-D9A15A87539C@.microsoft.com...
> Hello. I've got a situation where a live database has stopped working because
> the transaction logs have filled all available disk space. I would really
> appreciate any advice on how to get it running again. My knowledge of SQL
> Server 2000 is very limited so I would need so quite detailed instructions! I
> am happy with entering statements into Query Analyzer.
> I've tried to shrink the transaction log by running a backup on it in
> Enterprise Manager. However, the largest .LDF file is 33Gb and there is not
> enough disk space to back this up.
> So somehow I need to shrink these .LDF transaction log files. Also, if
> anyone could advise on how to stop this situation happening again, that'd be
> fantastic. If anyone can give advice on how to do this, you really would be
> saving my life!
|||Hi,
What is your recovery model? Full, Simple ?
If you are using Full recovery model then you need to backup the transaction
log regularly.
If you don't need to set recovery model to Full then use Simple Recovery
Model you can find detailed information about it from Books Online.
Check out BACKUP LOG WITH TRUNCATE_ONLY (if it's acceptable for you..)
FROM BOL:
NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and
truncates the log. This option frees space. Specifying a backup device is
unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY are
synonyms.
After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes
recorded in the log are not recoverable. For recovery purposes, immediately
execute BACKUP DATABASE.
"Fluffy_Ninja" wrote:
> Hello. I've got a situation where a live database has stopped working because
> the transaction logs have filled all available disk space. I would really
> appreciate any advice on how to get it running again. My knowledge of SQL
> Server 2000 is very limited so I would need so quite detailed instructions! I
> am happy with entering statements into Query Analyzer.
> I've tried to shrink the transaction log by running a backup on it in
> Enterprise Manager. However, the largest .LDF file is 33Gb and there is not
> enough disk space to back this up.
> So somehow I need to shrink these .LDF transaction log files. Also, if
> anyone could advise on how to stop this situation happening again, that'd be
> fantastic. If anyone can give advice on how to do this, you really would be
> saving my life!
Showing posts with label filled. Show all posts
Showing posts with label filled. Show all posts
Friday, February 24, 2012
Cannot shrink Transaction Log
Hello. I've got a situation where a live database has stopped working becaus
e
the transaction logs have filled all available disk space. I would really
appreciate any advice on how to get it running again. My knowledge of SQL
Server 2000 is very limited so I would need so quite detailed instructions!
I
am happy with entering statements into Query Analyzer.
I've tried to shrink the transaction log by running a backup on it in
Enterprise Manager. However, the largest .LDF file is 33Gb and there is not
enough disk space to back this up.
So somehow I need to shrink these .LDF transaction log files. Also, if
anyone could advise on how to stop this situation happening again, that'd be
fantastic. If anyone can give advice on how to do this, you really would be
saving my life!1. Consider the recovery model for the database. If not "simple" then SQL Se
rver will not empty the
log files automatically, and the log will keep growing. When not "simple", o
nly backup of the
transaction log will empty the log files.
2. After either setting to simple or scheduling regular transaction log back
ups, verify that the log
files are more or less empty. Use:
DBCC SQLPERF(LOGSPACE)
3. Now time for the shrink. Use DBCC SHRINKFILE, (not SHRINKDATABASE), as do
cumented in Books
Online.
Also, there are some links and general info about shrink on
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Fluffy_Ninja" <FluffyNinja@.discussions.microsoft.com> wrote in message
news:C338EACF-9E37-47B3-B307-D9A15A87539C@.microsoft.com...
> Hello. I've got a situation where a live database has stopped working beca
use
> the transaction logs have filled all available disk space. I would really
> appreciate any advice on how to get it running again. My knowledge of SQL
> Server 2000 is very limited so I would need so quite detailed instructions
! I
> am happy with entering statements into Query Analyzer.
> I've tried to shrink the transaction log by running a backup on it in
> Enterprise Manager. However, the largest .LDF file is 33Gb and there is no
t
> enough disk space to back this up.
> So somehow I need to shrink these .LDF transaction log files. Also, if
> anyone could advise on how to stop this situation happening again, that'd
be
> fantastic. If anyone can give advice on how to do this, you really would b
e
> saving my life!|||Hi,
What is your recovery model? Full, Simple ?
If you are using Full recovery model then you need to backup the transaction
log regularly.
If you don't need to set recovery model to Full then use Simple Recovery
Model you can find detailed information about it from Books Online.
Check out BACKUP LOG WITH TRUNCATE_ONLY (if it's acceptable for you..)
FROM BOL:
NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and
truncates the log. This option frees space. Specifying a backup device is
unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY ar
e
synonyms.
After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes
recorded in the log are not recoverable. For recovery purposes, immediately
execute BACKUP DATABASE.
"Fluffy_Ninja" wrote:
> Hello. I've got a situation where a live database has stopped working beca
use
> the transaction logs have filled all available disk space. I would really
> appreciate any advice on how to get it running again. My knowledge of SQL
> Server 2000 is very limited so I would need so quite detailed instructions
! I
> am happy with entering statements into Query Analyzer.
> I've tried to shrink the transaction log by running a backup on it in
> Enterprise Manager. However, the largest .LDF file is 33Gb and there is no
t
> enough disk space to back this up.
> So somehow I need to shrink these .LDF transaction log files. Also, if
> anyone could advise on how to stop this situation happening again, that'd
be
> fantastic. If anyone can give advice on how to do this, you really would b
e
> saving my life!
e
the transaction logs have filled all available disk space. I would really
appreciate any advice on how to get it running again. My knowledge of SQL
Server 2000 is very limited so I would need so quite detailed instructions!
I
am happy with entering statements into Query Analyzer.
I've tried to shrink the transaction log by running a backup on it in
Enterprise Manager. However, the largest .LDF file is 33Gb and there is not
enough disk space to back this up.
So somehow I need to shrink these .LDF transaction log files. Also, if
anyone could advise on how to stop this situation happening again, that'd be
fantastic. If anyone can give advice on how to do this, you really would be
saving my life!1. Consider the recovery model for the database. If not "simple" then SQL Se
rver will not empty the
log files automatically, and the log will keep growing. When not "simple", o
nly backup of the
transaction log will empty the log files.
2. After either setting to simple or scheduling regular transaction log back
ups, verify that the log
files are more or less empty. Use:
DBCC SQLPERF(LOGSPACE)
3. Now time for the shrink. Use DBCC SHRINKFILE, (not SHRINKDATABASE), as do
cumented in Books
Online.
Also, there are some links and general info about shrink on
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Fluffy_Ninja" <FluffyNinja@.discussions.microsoft.com> wrote in message
news:C338EACF-9E37-47B3-B307-D9A15A87539C@.microsoft.com...
> Hello. I've got a situation where a live database has stopped working beca
use
> the transaction logs have filled all available disk space. I would really
> appreciate any advice on how to get it running again. My knowledge of SQL
> Server 2000 is very limited so I would need so quite detailed instructions
! I
> am happy with entering statements into Query Analyzer.
> I've tried to shrink the transaction log by running a backup on it in
> Enterprise Manager. However, the largest .LDF file is 33Gb and there is no
t
> enough disk space to back this up.
> So somehow I need to shrink these .LDF transaction log files. Also, if
> anyone could advise on how to stop this situation happening again, that'd
be
> fantastic. If anyone can give advice on how to do this, you really would b
e
> saving my life!|||Hi,
What is your recovery model? Full, Simple ?
If you are using Full recovery model then you need to backup the transaction
log regularly.
If you don't need to set recovery model to Full then use Simple Recovery
Model you can find detailed information about it from Books Online.
Check out BACKUP LOG WITH TRUNCATE_ONLY (if it's acceptable for you..)
FROM BOL:
NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and
truncates the log. This option frees space. Specifying a backup device is
unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY ar
e
synonyms.
After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes
recorded in the log are not recoverable. For recovery purposes, immediately
execute BACKUP DATABASE.
"Fluffy_Ninja" wrote:
> Hello. I've got a situation where a live database has stopped working beca
use
> the transaction logs have filled all available disk space. I would really
> appreciate any advice on how to get it running again. My knowledge of SQL
> Server 2000 is very limited so I would need so quite detailed instructions
! I
> am happy with entering statements into Query Analyzer.
> I've tried to shrink the transaction log by running a backup on it in
> Enterprise Manager. However, the largest .LDF file is 33Gb and there is no
t
> enough disk space to back this up.
> So somehow I need to shrink these .LDF transaction log files. Also, if
> anyone could advise on how to stop this situation happening again, that'd
be
> fantastic. If anyone can give advice on how to do this, you really would b
e
> saving my life!
Cannot shrink Transaction Log
Hello. I've got a situation where a live database has stopped working because
the transaction logs have filled all available disk space. I would really
appreciate any advice on how to get it running again. My knowledge of SQL
Server 2000 is very limited so I would need so quite detailed instructions! I
am happy with entering statements into Query Analyzer.
I've tried to shrink the transaction log by running a backup on it in
Enterprise Manager. However, the largest .LDF file is 33Gb and there is not
enough disk space to back this up.
So somehow I need to shrink these .LDF transaction log files. Also, if
anyone could advise on how to stop this situation happening again, that'd be
fantastic. If anyone can give advice on how to do this, you really would be
saving my life!1. Consider the recovery model for the database. If not "simple" then SQL Server will not empty the
log files automatically, and the log will keep growing. When not "simple", only backup of the
transaction log will empty the log files.
2. After either setting to simple or scheduling regular transaction log backups, verify that the log
files are more or less empty. Use:
DBCC SQLPERF(LOGSPACE)
3. Now time for the shrink. Use DBCC SHRINKFILE, (not SHRINKDATABASE), as documented in Books
Online.
Also, there are some links and general info about shrink on
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Fluffy_Ninja" <FluffyNinja@.discussions.microsoft.com> wrote in message
news:C338EACF-9E37-47B3-B307-D9A15A87539C@.microsoft.com...
> Hello. I've got a situation where a live database has stopped working because
> the transaction logs have filled all available disk space. I would really
> appreciate any advice on how to get it running again. My knowledge of SQL
> Server 2000 is very limited so I would need so quite detailed instructions! I
> am happy with entering statements into Query Analyzer.
> I've tried to shrink the transaction log by running a backup on it in
> Enterprise Manager. However, the largest .LDF file is 33Gb and there is not
> enough disk space to back this up.
> So somehow I need to shrink these .LDF transaction log files. Also, if
> anyone could advise on how to stop this situation happening again, that'd be
> fantastic. If anyone can give advice on how to do this, you really would be
> saving my life!|||Hi,
What is your recovery model? Full, Simple ?
If you are using Full recovery model then you need to backup the transaction
log regularly.
If you don't need to set recovery model to Full then use Simple Recovery
Model you can find detailed information about it from Books Online.
Check out BACKUP LOG WITH TRUNCATE_ONLY (if it's acceptable for you..)
FROM BOL:
NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and
truncates the log. This option frees space. Specifying a backup device is
unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY are
synonyms.
After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes
recorded in the log are not recoverable. For recovery purposes, immediately
execute BACKUP DATABASE.
"Fluffy_Ninja" wrote:
> Hello. I've got a situation where a live database has stopped working because
> the transaction logs have filled all available disk space. I would really
> appreciate any advice on how to get it running again. My knowledge of SQL
> Server 2000 is very limited so I would need so quite detailed instructions! I
> am happy with entering statements into Query Analyzer.
> I've tried to shrink the transaction log by running a backup on it in
> Enterprise Manager. However, the largest .LDF file is 33Gb and there is not
> enough disk space to back this up.
> So somehow I need to shrink these .LDF transaction log files. Also, if
> anyone could advise on how to stop this situation happening again, that'd be
> fantastic. If anyone can give advice on how to do this, you really would be
> saving my life!
the transaction logs have filled all available disk space. I would really
appreciate any advice on how to get it running again. My knowledge of SQL
Server 2000 is very limited so I would need so quite detailed instructions! I
am happy with entering statements into Query Analyzer.
I've tried to shrink the transaction log by running a backup on it in
Enterprise Manager. However, the largest .LDF file is 33Gb and there is not
enough disk space to back this up.
So somehow I need to shrink these .LDF transaction log files. Also, if
anyone could advise on how to stop this situation happening again, that'd be
fantastic. If anyone can give advice on how to do this, you really would be
saving my life!1. Consider the recovery model for the database. If not "simple" then SQL Server will not empty the
log files automatically, and the log will keep growing. When not "simple", only backup of the
transaction log will empty the log files.
2. After either setting to simple or scheduling regular transaction log backups, verify that the log
files are more or less empty. Use:
DBCC SQLPERF(LOGSPACE)
3. Now time for the shrink. Use DBCC SHRINKFILE, (not SHRINKDATABASE), as documented in Books
Online.
Also, there are some links and general info about shrink on
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Fluffy_Ninja" <FluffyNinja@.discussions.microsoft.com> wrote in message
news:C338EACF-9E37-47B3-B307-D9A15A87539C@.microsoft.com...
> Hello. I've got a situation where a live database has stopped working because
> the transaction logs have filled all available disk space. I would really
> appreciate any advice on how to get it running again. My knowledge of SQL
> Server 2000 is very limited so I would need so quite detailed instructions! I
> am happy with entering statements into Query Analyzer.
> I've tried to shrink the transaction log by running a backup on it in
> Enterprise Manager. However, the largest .LDF file is 33Gb and there is not
> enough disk space to back this up.
> So somehow I need to shrink these .LDF transaction log files. Also, if
> anyone could advise on how to stop this situation happening again, that'd be
> fantastic. If anyone can give advice on how to do this, you really would be
> saving my life!|||Hi,
What is your recovery model? Full, Simple ?
If you are using Full recovery model then you need to backup the transaction
log regularly.
If you don't need to set recovery model to Full then use Simple Recovery
Model you can find detailed information about it from Books Online.
Check out BACKUP LOG WITH TRUNCATE_ONLY (if it's acceptable for you..)
FROM BOL:
NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and
truncates the log. This option frees space. Specifying a backup device is
unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY are
synonyms.
After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes
recorded in the log are not recoverable. For recovery purposes, immediately
execute BACKUP DATABASE.
"Fluffy_Ninja" wrote:
> Hello. I've got a situation where a live database has stopped working because
> the transaction logs have filled all available disk space. I would really
> appreciate any advice on how to get it running again. My knowledge of SQL
> Server 2000 is very limited so I would need so quite detailed instructions! I
> am happy with entering statements into Query Analyzer.
> I've tried to shrink the transaction log by running a backup on it in
> Enterprise Manager. However, the largest .LDF file is 33Gb and there is not
> enough disk space to back this up.
> So somehow I need to shrink these .LDF transaction log files. Also, if
> anyone could advise on how to stop this situation happening again, that'd be
> fantastic. If anyone can give advice on how to do this, you really would be
> saving my life!
Tuesday, February 14, 2012
Cannot save server properties on Case Sensitive instances
Hi all,
I've filled this suspect bug to Connect:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=282812
The issue is: I'm unable to save any change to server properties if instance is set to Case Sensitive collation .
The process returns an error like (translated from Spanish, so not exact message):
"Error in metadata manager. Log file extension should be .LOG. Error in metadata manager. Error setting ConfigurationSettings\Log\File configuration property.".
As far as I've tested this happens with any property you're trying to set when in CS collation.
My bypass has been to edit msmdsrv.ini file directly with Notepad, reverting CollationName to case insensitive.
Then everything goes fine.
Details:
Tested with AS 2005 SP2 Collation: Latin1_General_CS_AI Happens both with SSMS and direct scripting (as expected)Best regards,
Jordi Rambla
Solid Quality Mentors
wenn Collation Latin1_CS and soon is, you should change in msmdsrv.ini in
<Log>
<File>msmdsrv.log</File>... </Log> to <File>msmdsrv.LOG</File>
Cannot save server properties on Case Sensitive instances
Hi all,
I've filled this suspect bug to Connect:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=282812
The issue is: I'm unable to save any change to server properties if instance is set to Case Sensitive collation .
The process returns an error like (translated from Spanish, so not exact message):
"Error in metadata manager. Log file extension should be .LOG. Error in metadata manager. Error setting ConfigurationSettings\Log\File configuration property.".
As far as I've tested this happens with any property you're trying to set when in CS collation.
My bypass has been to edit msmdsrv.ini file directly with Notepad, reverting CollationName to case insensitive.
Then everything goes fine.
Details:
Tested with AS 2005 SP2 Collation: Latin1_General_CS_AI Happens both with SSMS and direct scripting (as expected)Best regards,
Jordi Rambla
Solid Quality Mentors
wenn Collation Latin1_CS and soon is, you should change in msmdsrv.ini in
<Log>
<File>msmdsrv.log</File>... </Log> to <File>msmdsrv.LOG</File>
Subscribe to:
Posts (Atom)