Description:
Error: 9002, Severity: 17, State: 6
The log file for database 'tempdb' is full. Back up the
transaction log for the database to free up some log
space.
Rather than have to check the size of the tempdb.mdf every
day to make sure it's not consuming too much drive space,
we would like to find out if anyone has found an automated
way of backing up the tempdb to reduce the log size, or
maybe even schedule a restart of the SQL services in the
middle of the night to reset the log to its default size?
Any help would be greatly appreciated.
If it's growing too large, it's because your application requires it.
Either make more room on the disk for tempdb, move tempdb to a different
drive, or fix the application so it doesn't require so much space.
http://www.aspfaq.com/2446
Of course you can schedule a job to restart SQL Server, etc. But this is a
really bad hack at best.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Animatrix1" <anonymous@.discussions.microsoft.com> wrote in message
news:4d2601c42c6b$7e9f2be0$a601280a@.phx.gbl...
> Description:
> Error: 9002, Severity: 17, State: 6
> The log file for database 'tempdb' is full. Back up the
> transaction log for the database to free up some log
> space.
> Rather than have to check the size of the tempdb.mdf every
> day to make sure it's not consuming too much drive space,
> we would like to find out if anyone has found an automated
> way of backing up the tempdb to reduce the log size, or
> maybe even schedule a restart of the SQL services in the
> middle of the night to reset the log to its default size?
> Any help would be greatly appreciated.
|||We've also had tempdb spiral out of control. We are
using PeopleSoft as the front end application, and I can
tell you with great assurance, that there is no possible
way the application requires a 20 gig tempdb, to support
a 5 gig database.
We find the tempdb slowly grows over time. Sometimes
quickly, normally slowly. The only solution we came up
with, was the limit the size of tempdb, to something
large, but not all consuming.
We have about 15 installations of PeopleSoft, of varying
version levels with SQLServer. I've only seen two of
them suffer from this problem. So it's certainly not the
norm.
Fred...
>--Original Message--
>If it's growing too large, it's because your application
requires it.
>Either make more room on the disk for tempdb, move
tempdb to a different
>drive, or fix the application so it doesn't require so
much space.
>http://www.aspfaq.com/2446
>Of course you can schedule a job to restart SQL Server,
etc. But this is a
>really bad hack at best.
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>
>"Animatrix1" <anonymous@.discussions.microsoft.com> wrote
in message[vbcol=seagreen]
>news:4d2601c42c6b$7e9f2be0$a601280a@.phx.gbl...
every[vbcol=seagreen]
space,[vbcol=seagreen]
automated[vbcol=seagreen]
the[vbcol=seagreen]
size?
>
>.
>
Showing posts with label log. Show all posts
Showing posts with label log. Show all posts
Monday, March 19, 2012
Controlling the size of the Tempdb
Description:
Error: 9002, Severity: 17, State: 6
The log file for database 'tempdb' is full. Back up the
transaction log for the database to free up some log
space.
Rather than have to check the size of the tempdb.mdf every
day to make sure it's not consuming too much drive space,
we would like to find out if anyone has found an automated
way of backing up the tempdb to reduce the log size, or
maybe even schedule a restart of the SQL services in the
middle of the night to reset the log to its default size?
Any help would be greatly appreciated.If it's growing too large, it's because your application requires it.
Either make more room on the disk for tempdb, move tempdb to a different
drive, or fix the application so it doesn't require so much space.
http://www.aspfaq.com/2446
Of course you can schedule a job to restart SQL Server, etc. But this is a
really bad hack at best.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Animatrix1" <anonymous@.discussions.microsoft.com> wrote in message
news:4d2601c42c6b$7e9f2be0$a601280a@.phx.gbl...
> Description:
> Error: 9002, Severity: 17, State: 6
> The log file for database 'tempdb' is full. Back up the
> transaction log for the database to free up some log
> space.
> Rather than have to check the size of the tempdb.mdf every
> day to make sure it's not consuming too much drive space,
> we would like to find out if anyone has found an automated
> way of backing up the tempdb to reduce the log size, or
> maybe even schedule a restart of the SQL services in the
> middle of the night to reset the log to its default size?
> Any help would be greatly appreciated.|||We've also had tempdb spiral out of control. We are
using PeopleSoft as the front end application, and I can
tell you with great assurance, that there is no possible
way the application requires a 20 gig tempdb, to support
a 5 gig database.
We find the tempdb slowly grows over time. Sometimes
quickly, normally slowly. The only solution we came up
with, was the limit the size of tempdb, to something
large, but not all consuming.
We have about 15 installations of PeopleSoft, of varying
version levels with SQLServer. I've only seen two of
them suffer from this problem. So it's certainly not the
norm.
Fred...
>--Original Message--
>If it's growing too large, it's because your application
requires it.
>Either make more room on the disk for tempdb, move
tempdb to a different
>drive, or fix the application so it doesn't require so
much space.
>http://www.aspfaq.com/2446
>Of course you can schedule a job to restart SQL Server,
etc. But this is a
>really bad hack at best.
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>
>"Animatrix1" <anonymous@.discussions.microsoft.com> wrote
in message
>news:4d2601c42c6b$7e9f2be0$a601280a@.phx.gbl...
>> Description:
>> Error: 9002, Severity: 17, State: 6
>> The log file for database 'tempdb' is full. Back up the
>> transaction log for the database to free up some log
>> space.
>> Rather than have to check the size of the tempdb.mdf
every
>> day to make sure it's not consuming too much drive
space,
>> we would like to find out if anyone has found an
automated
>> way of backing up the tempdb to reduce the log size, or
>> maybe even schedule a restart of the SQL services in
the
>> middle of the night to reset the log to its default
size?
>> Any help would be greatly appreciated.
>
>.
>
Error: 9002, Severity: 17, State: 6
The log file for database 'tempdb' is full. Back up the
transaction log for the database to free up some log
space.
Rather than have to check the size of the tempdb.mdf every
day to make sure it's not consuming too much drive space,
we would like to find out if anyone has found an automated
way of backing up the tempdb to reduce the log size, or
maybe even schedule a restart of the SQL services in the
middle of the night to reset the log to its default size?
Any help would be greatly appreciated.If it's growing too large, it's because your application requires it.
Either make more room on the disk for tempdb, move tempdb to a different
drive, or fix the application so it doesn't require so much space.
http://www.aspfaq.com/2446
Of course you can schedule a job to restart SQL Server, etc. But this is a
really bad hack at best.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Animatrix1" <anonymous@.discussions.microsoft.com> wrote in message
news:4d2601c42c6b$7e9f2be0$a601280a@.phx.gbl...
> Description:
> Error: 9002, Severity: 17, State: 6
> The log file for database 'tempdb' is full. Back up the
> transaction log for the database to free up some log
> space.
> Rather than have to check the size of the tempdb.mdf every
> day to make sure it's not consuming too much drive space,
> we would like to find out if anyone has found an automated
> way of backing up the tempdb to reduce the log size, or
> maybe even schedule a restart of the SQL services in the
> middle of the night to reset the log to its default size?
> Any help would be greatly appreciated.|||We've also had tempdb spiral out of control. We are
using PeopleSoft as the front end application, and I can
tell you with great assurance, that there is no possible
way the application requires a 20 gig tempdb, to support
a 5 gig database.
We find the tempdb slowly grows over time. Sometimes
quickly, normally slowly. The only solution we came up
with, was the limit the size of tempdb, to something
large, but not all consuming.
We have about 15 installations of PeopleSoft, of varying
version levels with SQLServer. I've only seen two of
them suffer from this problem. So it's certainly not the
norm.
Fred...
>--Original Message--
>If it's growing too large, it's because your application
requires it.
>Either make more room on the disk for tempdb, move
tempdb to a different
>drive, or fix the application so it doesn't require so
much space.
>http://www.aspfaq.com/2446
>Of course you can schedule a job to restart SQL Server,
etc. But this is a
>really bad hack at best.
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>
>"Animatrix1" <anonymous@.discussions.microsoft.com> wrote
in message
>news:4d2601c42c6b$7e9f2be0$a601280a@.phx.gbl...
>> Description:
>> Error: 9002, Severity: 17, State: 6
>> The log file for database 'tempdb' is full. Back up the
>> transaction log for the database to free up some log
>> space.
>> Rather than have to check the size of the tempdb.mdf
every
>> day to make sure it's not consuming too much drive
space,
>> we would like to find out if anyone has found an
automated
>> way of backing up the tempdb to reduce the log size, or
>> maybe even schedule a restart of the SQL services in
the
>> middle of the night to reset the log to its default
size?
>> Any help would be greatly appreciated.
>
>.
>
Controlling the size of the Tempdb
Description:
Error: 9002, Severity: 17, State: 6
The log file for database 'tempdb' is full. Back up the
transaction log for the database to free up some log
space.
Rather than have to check the size of the tempdb.mdf every
day to make sure it's not consuming too much drive space,
we would like to find out if anyone has found an automated
way of backing up the tempdb to reduce the log size, or
maybe even schedule a restart of the SQL services in the
middle of the night to reset the log to its default size?
Any help would be greatly appreciated.If it's growing too large, it's because your application requires it.
Either make more room on the disk for tempdb, move tempdb to a different
drive, or fix the application so it doesn't require so much space.
http://www.aspfaq.com/2446
Of course you can schedule a job to restart SQL Server, etc. But this is a
really bad hack at best.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Animatrix1" <anonymous@.discussions.microsoft.com> wrote in message
news:4d2601c42c6b$7e9f2be0$a601280a@.phx.gbl...
> Description:
> Error: 9002, Severity: 17, State: 6
> The log file for database 'tempdb' is full. Back up the
> transaction log for the database to free up some log
> space.
> Rather than have to check the size of the tempdb.mdf every
> day to make sure it's not consuming too much drive space,
> we would like to find out if anyone has found an automated
> way of backing up the tempdb to reduce the log size, or
> maybe even schedule a restart of the SQL services in the
> middle of the night to reset the log to its default size?
> Any help would be greatly appreciated.|||We've also had tempdb spiral out of control. We are
using PeopleSoft as the front end application, and I can
tell you with great assurance, that there is no possible
way the application requires a 20 gig tempdb, to support
a 5 gig database.
We find the tempdb slowly grows over time. Sometimes
quickly, normally slowly. The only solution we came up
with, was the limit the size of tempdb, to something
large, but not all consuming.
We have about 15 installations of PeopleSoft, of varying
version levels with SQLServer. I've only seen two of
them suffer from this problem. So it's certainly not the
norm.
Fred...
>--Original Message--
>If it's growing too large, it's because your application
requires it.
>Either make more room on the disk for tempdb, move
tempdb to a different
>drive, or fix the application so it doesn't require so
much space.
>http://www.aspfaq.com/2446
>Of course you can schedule a job to restart SQL Server,
etc. But this is a
>really bad hack at best.
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>
>"Animatrix1" <anonymous@.discussions.microsoft.com> wrote
in message
>news:4d2601c42c6b$7e9f2be0$a601280a@.phx.gbl...
every[vbcol=seagreen]
space,[vbcol=seagreen]
automated[vbcol=seagreen]
the[vbcol=seagreen]
size?[vbcol=seagreen]
>
>.
>
Error: 9002, Severity: 17, State: 6
The log file for database 'tempdb' is full. Back up the
transaction log for the database to free up some log
space.
Rather than have to check the size of the tempdb.mdf every
day to make sure it's not consuming too much drive space,
we would like to find out if anyone has found an automated
way of backing up the tempdb to reduce the log size, or
maybe even schedule a restart of the SQL services in the
middle of the night to reset the log to its default size?
Any help would be greatly appreciated.If it's growing too large, it's because your application requires it.
Either make more room on the disk for tempdb, move tempdb to a different
drive, or fix the application so it doesn't require so much space.
http://www.aspfaq.com/2446
Of course you can schedule a job to restart SQL Server, etc. But this is a
really bad hack at best.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Animatrix1" <anonymous@.discussions.microsoft.com> wrote in message
news:4d2601c42c6b$7e9f2be0$a601280a@.phx.gbl...
> Description:
> Error: 9002, Severity: 17, State: 6
> The log file for database 'tempdb' is full. Back up the
> transaction log for the database to free up some log
> space.
> Rather than have to check the size of the tempdb.mdf every
> day to make sure it's not consuming too much drive space,
> we would like to find out if anyone has found an automated
> way of backing up the tempdb to reduce the log size, or
> maybe even schedule a restart of the SQL services in the
> middle of the night to reset the log to its default size?
> Any help would be greatly appreciated.|||We've also had tempdb spiral out of control. We are
using PeopleSoft as the front end application, and I can
tell you with great assurance, that there is no possible
way the application requires a 20 gig tempdb, to support
a 5 gig database.
We find the tempdb slowly grows over time. Sometimes
quickly, normally slowly. The only solution we came up
with, was the limit the size of tempdb, to something
large, but not all consuming.
We have about 15 installations of PeopleSoft, of varying
version levels with SQLServer. I've only seen two of
them suffer from this problem. So it's certainly not the
norm.
Fred...
>--Original Message--
>If it's growing too large, it's because your application
requires it.
>Either make more room on the disk for tempdb, move
tempdb to a different
>drive, or fix the application so it doesn't require so
much space.
>http://www.aspfaq.com/2446
>Of course you can schedule a job to restart SQL Server,
etc. But this is a
>really bad hack at best.
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>
>"Animatrix1" <anonymous@.discussions.microsoft.com> wrote
in message
>news:4d2601c42c6b$7e9f2be0$a601280a@.phx.gbl...
every[vbcol=seagreen]
space,[vbcol=seagreen]
automated[vbcol=seagreen]
the[vbcol=seagreen]
size?[vbcol=seagreen]
>
>.
>
Controlling size of database log
Hi,
Our production database log size (9 GB) is 3 times greater than size of the
actual database itself (3GB)!
Is it normal? If not how can I bring the log size down? Can someone let me
know please.
Thanks in advance,
Harish Mohanbabuback it up.
BACKUP LOG databaseName
you can even back it up just to truncate it.
BACKUP LOG databaseName WITH TRUNCATE_ONLY
Greg Jackson
PDX, Oregon|||In addition to pdxjaxon,
you can run DBCC Shrinkfile, to reduce the physical log file size to the
desired target size.
Thanks
Yogish
"pdxJaxon" wrote:
> back it up.
> BACKUP LOG databaseName
>
> you can even back it up just to truncate it.
> BACKUP LOG databaseName WITH TRUNCATE_ONLY
>
> Greg Jackson
> PDX, Oregon
>
>|||And also check the recovery model for the database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Yogish" <yogishkamathg@.icqmail.com> wrote in message
news:DDB0D749-6F74-4673-993E-A5767020BFD6@.microsoft.com...[vbcol=seagreen]
> In addition to pdxjaxon,
> you can run DBCC Shrinkfile, to reduce the physical log file size to the
> desired target size.
> --
> Thanks
> Yogish
> "pdxJaxon" wrote:
>
Our production database log size (9 GB) is 3 times greater than size of the
actual database itself (3GB)!
Is it normal? If not how can I bring the log size down? Can someone let me
know please.
Thanks in advance,
Harish Mohanbabuback it up.
BACKUP LOG databaseName
you can even back it up just to truncate it.
BACKUP LOG databaseName WITH TRUNCATE_ONLY
Greg Jackson
PDX, Oregon|||In addition to pdxjaxon,
you can run DBCC Shrinkfile, to reduce the physical log file size to the
desired target size.
Thanks
Yogish
"pdxJaxon" wrote:
> back it up.
> BACKUP LOG databaseName
>
> you can even back it up just to truncate it.
> BACKUP LOG databaseName WITH TRUNCATE_ONLY
>
> Greg Jackson
> PDX, Oregon
>
>|||And also check the recovery model for the database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Yogish" <yogishkamathg@.icqmail.com> wrote in message
news:DDB0D749-6F74-4673-993E-A5767020BFD6@.microsoft.com...[vbcol=seagreen]
> In addition to pdxjaxon,
> you can run DBCC Shrinkfile, to reduce the physical log file size to the
> desired target size.
> --
> Thanks
> Yogish
> "pdxJaxon" wrote:
>
Controlling size of database log
Hi,
Our production database log size (9 GB) is 3 times greater than size of the
actual database itself (3GB)!
Is it normal? If not how can I bring the log size down? Can someone let me
know please.
Thanks in advance,
Harish Mohanbabu
back it up.
BACKUP LOG databaseName
you can even back it up just to truncate it.
BACKUP LOG databaseName WITH TRUNCATE_ONLY
Greg Jackson
PDX, Oregon
|||In addition to pdxjaxon,
you can run DBCC Shrinkfile, to reduce the physical log file size to the
desired target size.
Thanks
Yogish
"pdxJaxon" wrote:
> back it up.
> BACKUP LOG databaseName
>
> you can even back it up just to truncate it.
> BACKUP LOG databaseName WITH TRUNCATE_ONLY
>
> Greg Jackson
> PDX, Oregon
>
>
|||And also check the recovery model for the database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Yogish" <yogishkamathg@.icqmail.com> wrote in message
news:DDB0D749-6F74-4673-993E-A5767020BFD6@.microsoft.com...[vbcol=seagreen]
> In addition to pdxjaxon,
> you can run DBCC Shrinkfile, to reduce the physical log file size to the
> desired target size.
> --
> Thanks
> Yogish
> "pdxJaxon" wrote:
Our production database log size (9 GB) is 3 times greater than size of the
actual database itself (3GB)!
Is it normal? If not how can I bring the log size down? Can someone let me
know please.
Thanks in advance,
Harish Mohanbabu
back it up.
BACKUP LOG databaseName
you can even back it up just to truncate it.
BACKUP LOG databaseName WITH TRUNCATE_ONLY
Greg Jackson
PDX, Oregon
|||In addition to pdxjaxon,
you can run DBCC Shrinkfile, to reduce the physical log file size to the
desired target size.
Thanks
Yogish
"pdxJaxon" wrote:
> back it up.
> BACKUP LOG databaseName
>
> you can even back it up just to truncate it.
> BACKUP LOG databaseName WITH TRUNCATE_ONLY
>
> Greg Jackson
> PDX, Oregon
>
>
|||And also check the recovery model for the database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Yogish" <yogishkamathg@.icqmail.com> wrote in message
news:DDB0D749-6F74-4673-993E-A5767020BFD6@.microsoft.com...[vbcol=seagreen]
> In addition to pdxjaxon,
> you can run DBCC Shrinkfile, to reduce the physical log file size to the
> desired target size.
> --
> Thanks
> Yogish
> "pdxJaxon" wrote:
Controlling size of database log
Hi,
Our production database log size (9 GB) is 3 times greater than size of the
actual database itself (3GB)!
Is it normal? If not how can I bring the log size down? Can someone let me
know please.
Thanks in advance,
Harish Mohanbabuback it up.
BACKUP LOG databaseName
you can even back it up just to truncate it.
BACKUP LOG databaseName WITH TRUNCATE_ONLY
Greg Jackson
PDX, Oregon|||In addition to pdxjaxon,
you can run DBCC Shrinkfile, to reduce the physical log file size to the
desired target size.
--
Thanks
Yogish
"pdxJaxon" wrote:
> back it up.
> BACKUP LOG databaseName
>
> you can even back it up just to truncate it.
> BACKUP LOG databaseName WITH TRUNCATE_ONLY
>
> Greg Jackson
> PDX, Oregon
>
>|||And also check the recovery model for the database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Yogish" <yogishkamathg@.icqmail.com> wrote in message
news:DDB0D749-6F74-4673-993E-A5767020BFD6@.microsoft.com...
> In addition to pdxjaxon,
> you can run DBCC Shrinkfile, to reduce the physical log file size to the
> desired target size.
> --
> Thanks
> Yogish
> "pdxJaxon" wrote:
>> back it up.
>> BACKUP LOG databaseName
>>
>> you can even back it up just to truncate it.
>> BACKUP LOG databaseName WITH TRUNCATE_ONLY
>>
>> Greg Jackson
>> PDX, Oregon
>>
Our production database log size (9 GB) is 3 times greater than size of the
actual database itself (3GB)!
Is it normal? If not how can I bring the log size down? Can someone let me
know please.
Thanks in advance,
Harish Mohanbabuback it up.
BACKUP LOG databaseName
you can even back it up just to truncate it.
BACKUP LOG databaseName WITH TRUNCATE_ONLY
Greg Jackson
PDX, Oregon|||In addition to pdxjaxon,
you can run DBCC Shrinkfile, to reduce the physical log file size to the
desired target size.
--
Thanks
Yogish
"pdxJaxon" wrote:
> back it up.
> BACKUP LOG databaseName
>
> you can even back it up just to truncate it.
> BACKUP LOG databaseName WITH TRUNCATE_ONLY
>
> Greg Jackson
> PDX, Oregon
>
>|||And also check the recovery model for the database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Yogish" <yogishkamathg@.icqmail.com> wrote in message
news:DDB0D749-6F74-4673-993E-A5767020BFD6@.microsoft.com...
> In addition to pdxjaxon,
> you can run DBCC Shrinkfile, to reduce the physical log file size to the
> desired target size.
> --
> Thanks
> Yogish
> "pdxJaxon" wrote:
>> back it up.
>> BACKUP LOG databaseName
>>
>> you can even back it up just to truncate it.
>> BACKUP LOG databaseName WITH TRUNCATE_ONLY
>>
>> Greg Jackson
>> PDX, Oregon
>>
Controlling log file size
What's the best practice to keep log file size low. I have some databases
that run DTS packages to import data every night. The logs get huge, 17
gigs. I have an Arcserve agent backing them up, but it does not seem to
shrink them.
Any ideas?
Thanks
Greg
What recovery model are you using? Do you care about your transaction logs?
If you don't care about the data within the logs you can set your recovery
model to simple. You can also perform a BACKUP LOG <databasename> WITH
NO_LOG between each export to clear the transaction log.
NOTE: these steps will break transaction log backup/restore. Make sure that
you know what you are doing and how it will impact your database backups
before setting the recovery model or truncating the transaction log.
Keith
"Greg Richards" <grichards@.matrixwebs.com> wrote in message
news:uyL9M0FYEHA.3520@.TK2MSFTNGP10.phx.gbl...
> What's the best practice to keep log file size low. I have some databases
> that run DTS packages to import data every night. The logs get huge, 17
> gigs. I have an Arcserve agent backing them up, but it does not seem to
> shrink them.
> Any ideas?
> Thanks
> Greg
>
that run DTS packages to import data every night. The logs get huge, 17
gigs. I have an Arcserve agent backing them up, but it does not seem to
shrink them.
Any ideas?
Thanks
Greg
What recovery model are you using? Do you care about your transaction logs?
If you don't care about the data within the logs you can set your recovery
model to simple. You can also perform a BACKUP LOG <databasename> WITH
NO_LOG between each export to clear the transaction log.
NOTE: these steps will break transaction log backup/restore. Make sure that
you know what you are doing and how it will impact your database backups
before setting the recovery model or truncating the transaction log.
Keith
"Greg Richards" <grichards@.matrixwebs.com> wrote in message
news:uyL9M0FYEHA.3520@.TK2MSFTNGP10.phx.gbl...
> What's the best practice to keep log file size low. I have some databases
> that run DTS packages to import data every night. The logs get huge, 17
> gigs. I have an Arcserve agent backing them up, but it does not seem to
> shrink them.
> Any ideas?
> Thanks
> Greg
>
Sunday, March 11, 2012
Controlling log file size
What's the best practice to keep log file size low. I have some databases
that run DTS packages to import data every night. The logs get huge, 17
gigs. I have an Arcserve agent backing them up, but it does not seem to
shrink them.
Any ideas?
Thanks
GregWhat recovery model are you using? Do you care about your transaction logs?
If you don't care about the data within the logs you can set your recovery
model to simple. You can also perform a BACKUP LOG <databasename> WITH
NO_LOG between each export to clear the transaction log.
NOTE: these steps will break transaction log backup/restore. Make sure that
you know what you are doing and how it will impact your database backups
before setting the recovery model or truncating the transaction log.
--
Keith
"Greg Richards" <grichards@.matrixwebs.com> wrote in message
news:uyL9M0FYEHA.3520@.TK2MSFTNGP10.phx.gbl...
> What's the best practice to keep log file size low. I have some databases
> that run DTS packages to import data every night. The logs get huge, 17
> gigs. I have an Arcserve agent backing them up, but it does not seem to
> shrink them.
> Any ideas?
> Thanks
> Greg
>
that run DTS packages to import data every night. The logs get huge, 17
gigs. I have an Arcserve agent backing them up, but it does not seem to
shrink them.
Any ideas?
Thanks
GregWhat recovery model are you using? Do you care about your transaction logs?
If you don't care about the data within the logs you can set your recovery
model to simple. You can also perform a BACKUP LOG <databasename> WITH
NO_LOG between each export to clear the transaction log.
NOTE: these steps will break transaction log backup/restore. Make sure that
you know what you are doing and how it will impact your database backups
before setting the recovery model or truncating the transaction log.
--
Keith
"Greg Richards" <grichards@.matrixwebs.com> wrote in message
news:uyL9M0FYEHA.3520@.TK2MSFTNGP10.phx.gbl...
> What's the best practice to keep log file size low. I have some databases
> that run DTS packages to import data every night. The logs get huge, 17
> gigs. I have an Arcserve agent backing them up, but it does not seem to
> shrink them.
> Any ideas?
> Thanks
> Greg
>
Controlling log file size
My log file size is 38 GB and database file is only 1.5
GB. Only 8 GB space is left to cover the entire disk
space. I backed up the log file and shrink the database
to 10% but it released only 3 GB disk space. What would
be the best way to further reduce the log file size and
control the growth at this point?
Thanks in advance...Hi Jessica,
It should work if u have done exactly as shown in the
below KB
INF: Shrinking the Transaction Log in SQL Server 2000 with
DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;EN-
US;272318
Also do a frequent transaction log backups to over come
this . You can also turn the Database from FULL recovery
to SIMPLE recovery if your data is not critical so that
the log is truncated immediately.
There are some other pointers on MS site which tell why a
Tran. log grows rapidly.
HTH
--
Regards
Thirumal
www.thirumal.com
>--Original Message--
>My log file size is 38 GB and database file is only 1.5
>GB. Only 8 GB space is left to cover the entire disk
>space. I backed up the log file and shrink the database
>to 10% but it released only 3 GB disk space. What would
>be the best way to further reduce the log file size and
>control the growth at this point?
>Thanks in advance...
>.
>
GB. Only 8 GB space is left to cover the entire disk
space. I backed up the log file and shrink the database
to 10% but it released only 3 GB disk space. What would
be the best way to further reduce the log file size and
control the growth at this point?
Thanks in advance...Hi Jessica,
It should work if u have done exactly as shown in the
below KB
INF: Shrinking the Transaction Log in SQL Server 2000 with
DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;EN-
US;272318
Also do a frequent transaction log backups to over come
this . You can also turn the Database from FULL recovery
to SIMPLE recovery if your data is not critical so that
the log is truncated immediately.
There are some other pointers on MS site which tell why a
Tran. log grows rapidly.
HTH
--
Regards
Thirumal
www.thirumal.com
>--Original Message--
>My log file size is 38 GB and database file is only 1.5
>GB. Only 8 GB space is left to cover the entire disk
>space. I backed up the log file and shrink the database
>to 10% but it released only 3 GB disk space. What would
>be the best way to further reduce the log file size and
>control the growth at this point?
>Thanks in advance...
>.
>
Controlling log file size
What's the best practice to keep log file size low. I have some databases
that run DTS packages to import data every night. The logs get huge, 17
gigs. I have an Arcserve agent backing them up, but it does not seem to
shrink them.
Any ideas?
Thanks
GregWhat recovery model are you using? Do you care about your transaction logs?
If you don't care about the data within the logs you can set your recovery
model to simple. You can also perform a BACKUP LOG <databasename> WITH
NO_LOG between each export to clear the transaction log.
NOTE: these steps will break transaction log backup/restore. Make sure that
you know what you are doing and how it will impact your database backups
before setting the recovery model or truncating the transaction log.
Keith
"Greg Richards" <grichards@.matrixwebs.com> wrote in message
news:uyL9M0FYEHA.3520@.TK2MSFTNGP10.phx.gbl...
> What's the best practice to keep log file size low. I have some databases
> that run DTS packages to import data every night. The logs get huge, 17
> gigs. I have an Arcserve agent backing them up, but it does not seem to
> shrink them.
> Any ideas?
> Thanks
> Greg
>
that run DTS packages to import data every night. The logs get huge, 17
gigs. I have an Arcserve agent backing them up, but it does not seem to
shrink them.
Any ideas?
Thanks
GregWhat recovery model are you using? Do you care about your transaction logs?
If you don't care about the data within the logs you can set your recovery
model to simple. You can also perform a BACKUP LOG <databasename> WITH
NO_LOG between each export to clear the transaction log.
NOTE: these steps will break transaction log backup/restore. Make sure that
you know what you are doing and how it will impact your database backups
before setting the recovery model or truncating the transaction log.
Keith
"Greg Richards" <grichards@.matrixwebs.com> wrote in message
news:uyL9M0FYEHA.3520@.TK2MSFTNGP10.phx.gbl...
> What's the best practice to keep log file size low. I have some databases
> that run DTS packages to import data every night. The logs get huge, 17
> gigs. I have an Arcserve agent backing them up, but it does not seem to
> shrink them.
> Any ideas?
> Thanks
> Greg
>
controller
Can any one let me know about the controller like in the optimization
concern, we need to spearate our log file and data file into different
controller, means sepearate physical hard disk
What the controller is ?
Hi
Controller = Disk Driver Controller or RAID Controller.
It is the device that talk to the hard drives.
One controller can talk to lofg and data drive, but for performance, it is
better to go each drive set it's own controller.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rogers" <Rogers@.discussions.microsoft.com> wrote in message
news:D92BF589-8725-4BCB-9490-8E8D0AD810CA@.microsoft.com...
> Can any one let me know about the controller like in the optimization
> concern, we need to spearate our log file and data file into different
> controller, means sepearate physical hard disk
> What the controller is ?
|||We have implemented RAID 5 but the thing is that can any one guide me like
how can i check my RAID help my set my log and data file into different
controller.
Thanks
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Controller = Disk Driver Controller or RAID Controller.
> It is the device that talk to the hard drives.
> One controller can talk to lofg and data drive, but for performance, it is
> better to go each drive set it's own controller.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Rogers" <Rogers@.discussions.microsoft.com> wrote in message
> news:D92BF589-8725-4BCB-9490-8E8D0AD810CA@.microsoft.com...
>
>
|||Hi
Once you have implemented a RAID level, you can not change the controller
without destroying the RAID volume.
If you want to move your databases from one drive to another, look at
sp_attachdb and sp_detachdb in Books Online.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rogers" <Rogers@.discussions.microsoft.com> wrote in message
news:61E7C4CB-B4C4-494E-9CD4-60F94F3C3126@.microsoft.com...[vbcol=seagreen]
> We have implemented RAID 5 but the thing is that can any one guide me like
> how can i check my RAID help my set my log and data file into different
> controller.
> Thanks
> "Mike Epprecht (SQL MVP)" wrote:
concern, we need to spearate our log file and data file into different
controller, means sepearate physical hard disk
What the controller is ?
Hi
Controller = Disk Driver Controller or RAID Controller.
It is the device that talk to the hard drives.
One controller can talk to lofg and data drive, but for performance, it is
better to go each drive set it's own controller.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rogers" <Rogers@.discussions.microsoft.com> wrote in message
news:D92BF589-8725-4BCB-9490-8E8D0AD810CA@.microsoft.com...
> Can any one let me know about the controller like in the optimization
> concern, we need to spearate our log file and data file into different
> controller, means sepearate physical hard disk
> What the controller is ?
|||We have implemented RAID 5 but the thing is that can any one guide me like
how can i check my RAID help my set my log and data file into different
controller.
Thanks
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Controller = Disk Driver Controller or RAID Controller.
> It is the device that talk to the hard drives.
> One controller can talk to lofg and data drive, but for performance, it is
> better to go each drive set it's own controller.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Rogers" <Rogers@.discussions.microsoft.com> wrote in message
> news:D92BF589-8725-4BCB-9490-8E8D0AD810CA@.microsoft.com...
>
>
|||Hi
Once you have implemented a RAID level, you can not change the controller
without destroying the RAID volume.
If you want to move your databases from one drive to another, look at
sp_attachdb and sp_detachdb in Books Online.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rogers" <Rogers@.discussions.microsoft.com> wrote in message
news:61E7C4CB-B4C4-494E-9CD4-60F94F3C3126@.microsoft.com...[vbcol=seagreen]
> We have implemented RAID 5 but the thing is that can any one guide me like
> how can i check my RAID help my set my log and data file into different
> controller.
> Thanks
> "Mike Epprecht (SQL MVP)" wrote:
Wednesday, March 7, 2012
continuous merge replication - event log
Hi
I have set up merge replication and it works nicely.
I have set it up to work continuously, because I thought that if it can't find the subscriber or is offline then that's fine it will just sync again when it's back on line.
This is true
BUT it keeps throwing lots of messages into the event log to tell me the merge has failed.
SO
a. Can i just turn off the error reporting
or
b. How can I get it to sync this way automatically on connection without the error messages
thanks
ICW
Unfortunately there's nothing built in to suppress these messages. Maybe instead of running continuously, you can schedule it to run frequently, like every 10 minutes?Continuos Starting up database 'myDatabase'
Hi group. I have this problem. When I review the Sql Log appears the same
message second by second for each database in th server.
Starting up database 'myDatabase'.
Starting up database 'myDatabase'.
Starting up database 'myDatabase'.
Starting up database 'myDatabase'.
I don't know if is a problem but I think who isn't normal.
Any help will be apprtiate.
Regards,
RodrigoRodrigo wrote:
> Hi group. I have this problem. When I review the Sql Log appears the same
> message second by second for each database in th server.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
> I don't know if is a problem but I think who isn't normal.
> Any help will be apprtiate.
> Regards,
> Rodrigo|||Looks like you have the database option 'Auto close' set.
Rodrigo wrote:
> Hi group. I have this problem. When I review the Sql Log appears the same
> message second by second for each database in th server.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
> I don't know if is a problem but I think who isn't normal.
> Any help will be apprtiate.
> Regards,
> Rodrigo
message second by second for each database in th server.
Starting up database 'myDatabase'.
Starting up database 'myDatabase'.
Starting up database 'myDatabase'.
Starting up database 'myDatabase'.
I don't know if is a problem but I think who isn't normal.
Any help will be apprtiate.
Regards,
RodrigoRodrigo wrote:
> Hi group. I have this problem. When I review the Sql Log appears the same
> message second by second for each database in th server.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
> I don't know if is a problem but I think who isn't normal.
> Any help will be apprtiate.
> Regards,
> Rodrigo|||Looks like you have the database option 'Auto close' set.
Rodrigo wrote:
> Hi group. I have this problem. When I review the Sql Log appears the same
> message second by second for each database in th server.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
> I don't know if is a problem but I think who isn't normal.
> Any help will be apprtiate.
> Regards,
> Rodrigo
Continuos Starting up database 'myDatabase'
Hi group. I have this problem. When I review the Sql Log appears the same
message second by second for each database in th server.
Starting up database 'myDatabase'.
Starting up database 'myDatabase'.
Starting up database 'myDatabase'.
Starting up database 'myDatabase'.
I don't know if is a problem but I think who isn't normal.
Any help will be apprtiate.
Regards,
RodrigoRodrigo wrote:
> Hi group. I have this problem. When I review the Sql Log appears the same
> message second by second for each database in th server.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
> I don't know if is a problem but I think who isn't normal.
> Any help will be apprtiate.
> Regards,
> Rodrigo|||Looks like you have the database option 'Auto close' set.
Rodrigo wrote:
> Hi group. I have this problem. When I review the Sql Log appears the same
> message second by second for each database in th server.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
> I don't know if is a problem but I think who isn't normal.
> Any help will be apprtiate.
> Regards,
> Rodrigo
message second by second for each database in th server.
Starting up database 'myDatabase'.
Starting up database 'myDatabase'.
Starting up database 'myDatabase'.
Starting up database 'myDatabase'.
I don't know if is a problem but I think who isn't normal.
Any help will be apprtiate.
Regards,
RodrigoRodrigo wrote:
> Hi group. I have this problem. When I review the Sql Log appears the same
> message second by second for each database in th server.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
> I don't know if is a problem but I think who isn't normal.
> Any help will be apprtiate.
> Regards,
> Rodrigo|||Looks like you have the database option 'Auto close' set.
Rodrigo wrote:
> Hi group. I have this problem. When I review the Sql Log appears the same
> message second by second for each database in th server.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
> Starting up database 'myDatabase'.
> I don't know if is a problem but I think who isn't normal.
> Any help will be apprtiate.
> Regards,
> Rodrigo
Friday, February 10, 2012
consolidating multiple data/log files
Hello:
I've recently migrated a 6.5 datbase to 2000. As it was
under 6.5, the database was configured with multiple data
and log devices, which has been retained in its migrated
2000 database.
I'd like to consolidate multiple .ndf & .ldf files to a
single .mdf & .ldf file, respectively for its data and
log. Any ideas how & if its possible?
Thanks.
Rob,
First do a DBCC SHRINKFILE(..., EMPTYFILE) followed by
ALTER DATABASE <databasename>
DROP FILE ..
Refer BooksOnLiune for syntax'ndetails.
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1cfff01c4534e$ad942c20$a501280a@.phx.gbl...
> Hello:
> I've recently migrated a 6.5 datbase to 2000. As it was
> under 6.5, the database was configured with multiple data
> and log devices, which has been retained in its migrated
> 2000 database.
> I'd like to consolidate multiple .ndf & .ldf files to a
> single .mdf & .ldf file, respectively for its data and
> log. Any ideas how & if its possible?
> Thanks.
I've recently migrated a 6.5 datbase to 2000. As it was
under 6.5, the database was configured with multiple data
and log devices, which has been retained in its migrated
2000 database.
I'd like to consolidate multiple .ndf & .ldf files to a
single .mdf & .ldf file, respectively for its data and
log. Any ideas how & if its possible?
Thanks.
Rob,
First do a DBCC SHRINKFILE(..., EMPTYFILE) followed by
ALTER DATABASE <databasename>
DROP FILE ..
Refer BooksOnLiune for syntax'ndetails.
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1cfff01c4534e$ad942c20$a501280a@.phx.gbl...
> Hello:
> I've recently migrated a 6.5 datbase to 2000. As it was
> under 6.5, the database was configured with multiple data
> and log devices, which has been retained in its migrated
> 2000 database.
> I'd like to consolidate multiple .ndf & .ldf files to a
> single .mdf & .ldf file, respectively for its data and
> log. Any ideas how & if its possible?
> Thanks.
consolidating multiple data/log files
Hello:
I've recently migrated a 6.5 datbase to 2000. As it was
under 6.5, the database was configured with multiple data
and log devices, which has been retained in its migrated
2000 database.
I'd like to consolidate multiple .ndf & .ldf files to a
single .mdf & .ldf file, respectively for its data and
log. Any ideas how & if its possible?
Thanks.Rob,
First do a DBCC SHRINKFILE(..., EMPTYFILE) followed by
ALTER DATABASE <databasename>
DROP FILE ..
Refer BooksOnLiune for syntax'ndetails.
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1cfff01c4534e$ad942c20$a501280a@.phx
.gbl...
> Hello:
> I've recently migrated a 6.5 datbase to 2000. As it was
> under 6.5, the database was configured with multiple data
> and log devices, which has been retained in its migrated
> 2000 database.
> I'd like to consolidate multiple .ndf & .ldf files to a
> single .mdf & .ldf file, respectively for its data and
> log. Any ideas how & if its possible?
> Thanks.
I've recently migrated a 6.5 datbase to 2000. As it was
under 6.5, the database was configured with multiple data
and log devices, which has been retained in its migrated
2000 database.
I'd like to consolidate multiple .ndf & .ldf files to a
single .mdf & .ldf file, respectively for its data and
log. Any ideas how & if its possible?
Thanks.Rob,
First do a DBCC SHRINKFILE(..., EMPTYFILE) followed by
ALTER DATABASE <databasename>
DROP FILE ..
Refer BooksOnLiune for syntax'ndetails.
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1cfff01c4534e$ad942c20$a501280a@.phx
.gbl...
> Hello:
> I've recently migrated a 6.5 datbase to 2000. As it was
> under 6.5, the database was configured with multiple data
> and log devices, which has been retained in its migrated
> 2000 database.
> I'd like to consolidate multiple .ndf & .ldf files to a
> single .mdf & .ldf file, respectively for its data and
> log. Any ideas how & if its possible?
> Thanks.
consolidating multiple data/log files
Hello:
I've recently migrated a 6.5 datbase to 2000. As it was
under 6.5, the database was configured with multiple data
and log devices, which has been retained in its migrated
2000 database.
I'd like to consolidate multiple .ndf & .ldf files to a
single .mdf & .ldf file, respectively for its data and
log. Any ideas how & if its possible?
Thanks.Rob,
First do a DBCC SHRINKFILE(..., EMPTYFILE) followed by
ALTER DATABASE <databasename>
DROP FILE ..
Refer BooksOnLiune for syntax'ndetails.
--
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1cfff01c4534e$ad942c20$a501280a@.phx.gbl...
> Hello:
> I've recently migrated a 6.5 datbase to 2000. As it was
> under 6.5, the database was configured with multiple data
> and log devices, which has been retained in its migrated
> 2000 database.
> I'd like to consolidate multiple .ndf & .ldf files to a
> single .mdf & .ldf file, respectively for its data and
> log. Any ideas how & if its possible?
> Thanks.
I've recently migrated a 6.5 datbase to 2000. As it was
under 6.5, the database was configured with multiple data
and log devices, which has been retained in its migrated
2000 database.
I'd like to consolidate multiple .ndf & .ldf files to a
single .mdf & .ldf file, respectively for its data and
log. Any ideas how & if its possible?
Thanks.Rob,
First do a DBCC SHRINKFILE(..., EMPTYFILE) followed by
ALTER DATABASE <databasename>
DROP FILE ..
Refer BooksOnLiune for syntax'ndetails.
--
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1cfff01c4534e$ad942c20$a501280a@.phx.gbl...
> Hello:
> I've recently migrated a 6.5 datbase to 2000. As it was
> under 6.5, the database was configured with multiple data
> and log devices, which has been retained in its migrated
> 2000 database.
> I'd like to consolidate multiple .ndf & .ldf files to a
> single .mdf & .ldf file, respectively for its data and
> log. Any ideas how & if its possible?
> Thanks.
Subscribe to:
Posts (Atom)