Hi everyone!
I have a database sized about 500MB and it used to have about 2-300MB log file. But now the log file is about 4GB and I don't know when it grew up that big. I don't have any issues now, but is it OK or is there something I can do to make it smaller? Th
ank you for your time.
Greg
Hi Greg,
Are you backing up the log file? You want to do regular
backups of the log - my first guess would be that you
probably aren't doing those. When you backup the log, it
truncates (which is not the same as physically shrinking)
the log. It just means that parts of the log get marked as
inactive so these parts (virtual log files or VLFs) can be
used to record future transactions. Otherwise, the log just
keeps growing to record the transactions and nothing is
"reused" or overwritten.
Another option is if the transaction log is not part of your
recovery plan, you can set the database to simple recovery
model although this isn't typically used with production
databases.
If you need to get the log down to a more reasonable size,
you can use dbcc shrinkfile. You'd want to shrink it to the
size it needs to be for your database as continual
shrinking, growing of the log consumes resources. You can
find info on shrinking the log in the following article:
INF: Shrinking the Transaction Log in SQL Server 2000 with
DBCC SHRINKFILE
http://support.microsoft.com/?id=272318
-Sue
On Tue, 27 Jul 2004 14:21:04 -0700, "Greg"
<Greg@.discussions.microsoft.com> wrote:
>Hi everyone!
>I have a database sized about 500MB and it used to have about 2-300MB log file. But now the log file is about 4GB and I don't know when it grew up that big. I don't have any issues now, but is it OK or is there something I can do to make it smaller? T
hank you for your time.
>Greg
Showing posts with label 500mb. Show all posts
Showing posts with label 500mb. Show all posts
Monday, March 26, 2012
Log file...
Hi everyone!
I have a database sized about 500MB and it used to have about 2-300MB log fi
le. But now the log file is about 4GB and I don't know when it grew up that
big. I don't have any issues now, but is it OK or is there something I can
do to make it smaller? Th
ank you for your time.
GregHi Greg,
Are you backing up the log file? You want to do regular
backups of the log - my first guess would be that you
probably aren't doing those. When you backup the log, it
truncates (which is not the same as physically shrinking)
the log. It just means that parts of the log get marked as
inactive so these parts (virtual log files or VLFs) can be
used to record future transactions. Otherwise, the log just
keeps growing to record the transactions and nothing is
"reused" or overwritten.
Another option is if the transaction log is not part of your
recovery plan, you can set the database to simple recovery
model although this isn't typically used with production
databases.
If you need to get the log down to a more reasonable size,
you can use dbcc shrinkfile. You'd want to shrink it to the
size it needs to be for your database as continual
shrinking, growing of the log consumes resources. You can
find info on shrinking the log in the following article:
INF: Shrinking the Transaction Log in SQL Server 2000 with
DBCC SHRINKFILE
http://support.microsoft.com/?id=272318
-Sue
On Tue, 27 Jul 2004 14:21:04 -0700, "Greg"
<Greg@.discussions.microsoft.com> wrote:
>Hi everyone!
>I have a database sized about 500MB and it used to have about 2-300MB log file. Bu
t now the log file is about 4GB and I don't know when it grew up that big. I don't
have any issues now, but is it OK or is there something I can do to make it smaller?
T
hank you for your time.
>Greg
I have a database sized about 500MB and it used to have about 2-300MB log fi
le. But now the log file is about 4GB and I don't know when it grew up that
big. I don't have any issues now, but is it OK or is there something I can
do to make it smaller? Th
ank you for your time.
GregHi Greg,
Are you backing up the log file? You want to do regular
backups of the log - my first guess would be that you
probably aren't doing those. When you backup the log, it
truncates (which is not the same as physically shrinking)
the log. It just means that parts of the log get marked as
inactive so these parts (virtual log files or VLFs) can be
used to record future transactions. Otherwise, the log just
keeps growing to record the transactions and nothing is
"reused" or overwritten.
Another option is if the transaction log is not part of your
recovery plan, you can set the database to simple recovery
model although this isn't typically used with production
databases.
If you need to get the log down to a more reasonable size,
you can use dbcc shrinkfile. You'd want to shrink it to the
size it needs to be for your database as continual
shrinking, growing of the log consumes resources. You can
find info on shrinking the log in the following article:
INF: Shrinking the Transaction Log in SQL Server 2000 with
DBCC SHRINKFILE
http://support.microsoft.com/?id=272318
-Sue
On Tue, 27 Jul 2004 14:21:04 -0700, "Greg"
<Greg@.discussions.microsoft.com> wrote:
>Hi everyone!
>I have a database sized about 500MB and it used to have about 2-300MB log file. Bu
t now the log file is about 4GB and I don't know when it grew up that big. I don't
have any issues now, but is it OK or is there something I can do to make it smaller?
T
hank you for your time.
>Greg
Monday, February 20, 2012
Locks in tempdb
I am runninga stored procedure on a test box (1GB CPU and 500MB RAM) and it
takes about 20 minutes. The locks max out at about 2400 (sp_lock). I run it
on a production box and it is taking hours and in some cases not even coming
close to finishing. The locks on the production box keep going up. The last
time I issued a kill command there were 20000 locks for this bad spid.
The test box does not have anything running on it besides anti virus
software. The production box does have some very low intensive stuff running
on it.
- The procedure in question is using dynamic sql
- I set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
Does anyone have any obvious rabbit holes I could go down?
Thanks,
Mike
are u using some syntax such as
select xxx into #abc from xyz ?
that will definitely create lots of lock in tempdb.
If so, try avoid it.
Kan
"Mike S" <sagaeta@.yahoo.com> wrote in message
news:edvn8ILKEHA.1156@.TK2MSFTNGP09.phx.gbl...
> I am runninga stored procedure on a test box (1GB CPU and 500MB RAM) and
it
> takes about 20 minutes. The locks max out at about 2400 (sp_lock). I run
it
> on a production box and it is taking hours and in some cases not even
coming
> close to finishing. The locks on the production box keep going up. The
last
> time I issued a kill command there were 20000 locks for this bad spid.
> The test box does not have anything running on it besides anti virus
> software. The production box does have some very low intensive stuff
running
> on it.
> - The procedure in question is using dynamic sql
> - I set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> Does anyone have any obvious rabbit holes I could go down?
> Thanks,
> Mike
>
|||Could you post the proc in question? It could be any number of things.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Mike S" <sagaeta@.yahoo.com> wrote in message
news:edvn8ILKEHA.1156@.TK2MSFTNGP09.phx.gbl...
> I am runninga stored procedure on a test box (1GB CPU and 500MB RAM) and
it
> takes about 20 minutes. The locks max out at about 2400 (sp_lock). I run
it
> on a production box and it is taking hours and in some cases not even
coming
> close to finishing. The locks on the production box keep going up. The
last
> time I issued a kill command there were 20000 locks for this bad spid.
> The test box does not have anything running on it besides anti virus
> software. The production box does have some very low intensive stuff
running
> on it.
> - The procedure in question is using dynamic sql
> - I set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> Does anyone have any obvious rabbit holes I could go down?
> Thanks,
> Mike
>
takes about 20 minutes. The locks max out at about 2400 (sp_lock). I run it
on a production box and it is taking hours and in some cases not even coming
close to finishing. The locks on the production box keep going up. The last
time I issued a kill command there were 20000 locks for this bad spid.
The test box does not have anything running on it besides anti virus
software. The production box does have some very low intensive stuff running
on it.
- The procedure in question is using dynamic sql
- I set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
Does anyone have any obvious rabbit holes I could go down?
Thanks,
Mike
are u using some syntax such as
select xxx into #abc from xyz ?
that will definitely create lots of lock in tempdb.
If so, try avoid it.
Kan
"Mike S" <sagaeta@.yahoo.com> wrote in message
news:edvn8ILKEHA.1156@.TK2MSFTNGP09.phx.gbl...
> I am runninga stored procedure on a test box (1GB CPU and 500MB RAM) and
it
> takes about 20 minutes. The locks max out at about 2400 (sp_lock). I run
it
> on a production box and it is taking hours and in some cases not even
coming
> close to finishing. The locks on the production box keep going up. The
last
> time I issued a kill command there were 20000 locks for this bad spid.
> The test box does not have anything running on it besides anti virus
> software. The production box does have some very low intensive stuff
running
> on it.
> - The procedure in question is using dynamic sql
> - I set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> Does anyone have any obvious rabbit holes I could go down?
> Thanks,
> Mike
>
|||Could you post the proc in question? It could be any number of things.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Mike S" <sagaeta@.yahoo.com> wrote in message
news:edvn8ILKEHA.1156@.TK2MSFTNGP09.phx.gbl...
> I am runninga stored procedure on a test box (1GB CPU and 500MB RAM) and
it
> takes about 20 minutes. The locks max out at about 2400 (sp_lock). I run
it
> on a production box and it is taking hours and in some cases not even
coming
> close to finishing. The locks on the production box keep going up. The
last
> time I issued a kill command there were 20000 locks for this bad spid.
> The test box does not have anything running on it besides anti virus
> software. The production box does have some very low intensive stuff
running
> on it.
> - The procedure in question is using dynamic sql
> - I set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> Does anyone have any obvious rabbit holes I could go down?
> Thanks,
> Mike
>
Locks in tempdb
I am runninga stored procedure on a test box (1GB CPU and 500MB RAM) and it
takes about 20 minutes. The locks max out at about 2400 (sp_lock). I run it
on a production box and it is taking hours and in some cases not even coming
close to finishing. The locks on the production box keep going up. The last
time I issued a kill command there were 20000 locks for this bad spid.
The test box does not have anything running on it besides anti virus
software. The production box does have some very low intensive stuff running
on it.
- The procedure in question is using dynamic sql
- I set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
Does anyone have any obvious rabbit holes I could go down?
Thanks,
Mikeare u using some syntax such as
select xxx into #abc from xyz '
that will definitely create lots of lock in tempdb.
If so, try avoid it.
Kan
"Mike S" <sagaeta@.yahoo.com> wrote in message
news:edvn8ILKEHA.1156@.TK2MSFTNGP09.phx.gbl...
> I am runninga stored procedure on a test box (1GB CPU and 500MB RAM) and
it
> takes about 20 minutes. The locks max out at about 2400 (sp_lock). I run
it
> on a production box and it is taking hours and in some cases not even
coming
> close to finishing. The locks on the production box keep going up. The
last
> time I issued a kill command there were 20000 locks for this bad spid.
> The test box does not have anything running on it besides anti virus
> software. The production box does have some very low intensive stuff
running
> on it.
> - The procedure in question is using dynamic sql
> - I set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> Does anyone have any obvious rabbit holes I could go down?
> Thanks,
> Mike
>|||Could you post the proc in question? It could be any number of things.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Mike S" <sagaeta@.yahoo.com> wrote in message
news:edvn8ILKEHA.1156@.TK2MSFTNGP09.phx.gbl...
> I am runninga stored procedure on a test box (1GB CPU and 500MB RAM) and
it
> takes about 20 minutes. The locks max out at about 2400 (sp_lock). I run
it
> on a production box and it is taking hours and in some cases not even
coming
> close to finishing. The locks on the production box keep going up. The
last
> time I issued a kill command there were 20000 locks for this bad spid.
> The test box does not have anything running on it besides anti virus
> software. The production box does have some very low intensive stuff
running
> on it.
> - The procedure in question is using dynamic sql
> - I set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> Does anyone have any obvious rabbit holes I could go down?
> Thanks,
> Mike
>
takes about 20 minutes. The locks max out at about 2400 (sp_lock). I run it
on a production box and it is taking hours and in some cases not even coming
close to finishing. The locks on the production box keep going up. The last
time I issued a kill command there were 20000 locks for this bad spid.
The test box does not have anything running on it besides anti virus
software. The production box does have some very low intensive stuff running
on it.
- The procedure in question is using dynamic sql
- I set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
Does anyone have any obvious rabbit holes I could go down?
Thanks,
Mikeare u using some syntax such as
select xxx into #abc from xyz '
that will definitely create lots of lock in tempdb.
If so, try avoid it.
Kan
"Mike S" <sagaeta@.yahoo.com> wrote in message
news:edvn8ILKEHA.1156@.TK2MSFTNGP09.phx.gbl...
> I am runninga stored procedure on a test box (1GB CPU and 500MB RAM) and
it
> takes about 20 minutes. The locks max out at about 2400 (sp_lock). I run
it
> on a production box and it is taking hours and in some cases not even
coming
> close to finishing. The locks on the production box keep going up. The
last
> time I issued a kill command there were 20000 locks for this bad spid.
> The test box does not have anything running on it besides anti virus
> software. The production box does have some very low intensive stuff
running
> on it.
> - The procedure in question is using dynamic sql
> - I set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> Does anyone have any obvious rabbit holes I could go down?
> Thanks,
> Mike
>|||Could you post the proc in question? It could be any number of things.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Mike S" <sagaeta@.yahoo.com> wrote in message
news:edvn8ILKEHA.1156@.TK2MSFTNGP09.phx.gbl...
> I am runninga stored procedure on a test box (1GB CPU and 500MB RAM) and
it
> takes about 20 minutes. The locks max out at about 2400 (sp_lock). I run
it
> on a production box and it is taking hours and in some cases not even
coming
> close to finishing. The locks on the production box keep going up. The
last
> time I issued a kill command there were 20000 locks for this bad spid.
> The test box does not have anything running on it besides anti virus
> software. The production box does have some very low intensive stuff
running
> on it.
> - The procedure in question is using dynamic sql
> - I set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> Does anyone have any obvious rabbit holes I could go down?
> Thanks,
> Mike
>
Locks in tempdb
I am runninga stored procedure on a test box (1GB CPU and 500MB RAM) and it
takes about 20 minutes. The locks max out at about 2400 (sp_lock). I run it
on a production box and it is taking hours and in some cases not even coming
close to finishing. The locks on the production box keep going up. The last
time I issued a kill command there were 20000 locks for this bad spid.
The test box does not have anything running on it besides anti virus
software. The production box does have some very low intensive stuff running
on it.
- The procedure in question is using dynamic sql
- I set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
Does anyone have any obvious rabbit holes I could go down?
Thanks,
Mikeare u using some syntax such as
select xxx into #abc from xyz '
that will definitely create lots of lock in tempdb.
If so, try avoid it.
Kan
"Mike S" <sagaeta@.yahoo.com> wrote in message
news:edvn8ILKEHA.1156@.TK2MSFTNGP09.phx.gbl...
> I am runninga stored procedure on a test box (1GB CPU and 500MB RAM) and
it
> takes about 20 minutes. The locks max out at about 2400 (sp_lock). I run
it
> on a production box and it is taking hours and in some cases not even
coming
> close to finishing. The locks on the production box keep going up. The
last
> time I issued a kill command there were 20000 locks for this bad spid.
> The test box does not have anything running on it besides anti virus
> software. The production box does have some very low intensive stuff
running
> on it.
> - The procedure in question is using dynamic sql
> - I set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> Does anyone have any obvious rabbit holes I could go down?
> Thanks,
> Mike
>|||Could you post the proc in question? It could be any number of things.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Mike S" <sagaeta@.yahoo.com> wrote in message
news:edvn8ILKEHA.1156@.TK2MSFTNGP09.phx.gbl...
> I am runninga stored procedure on a test box (1GB CPU and 500MB RAM) and
it
> takes about 20 minutes. The locks max out at about 2400 (sp_lock). I run
it
> on a production box and it is taking hours and in some cases not even
coming
> close to finishing. The locks on the production box keep going up. The
last
> time I issued a kill command there were 20000 locks for this bad spid.
> The test box does not have anything running on it besides anti virus
> software. The production box does have some very low intensive stuff
running
> on it.
> - The procedure in question is using dynamic sql
> - I set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> Does anyone have any obvious rabbit holes I could go down?
> Thanks,
> Mike
>
takes about 20 minutes. The locks max out at about 2400 (sp_lock). I run it
on a production box and it is taking hours and in some cases not even coming
close to finishing. The locks on the production box keep going up. The last
time I issued a kill command there were 20000 locks for this bad spid.
The test box does not have anything running on it besides anti virus
software. The production box does have some very low intensive stuff running
on it.
- The procedure in question is using dynamic sql
- I set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
Does anyone have any obvious rabbit holes I could go down?
Thanks,
Mikeare u using some syntax such as
select xxx into #abc from xyz '
that will definitely create lots of lock in tempdb.
If so, try avoid it.
Kan
"Mike S" <sagaeta@.yahoo.com> wrote in message
news:edvn8ILKEHA.1156@.TK2MSFTNGP09.phx.gbl...
> I am runninga stored procedure on a test box (1GB CPU and 500MB RAM) and
it
> takes about 20 minutes. The locks max out at about 2400 (sp_lock). I run
it
> on a production box and it is taking hours and in some cases not even
coming
> close to finishing. The locks on the production box keep going up. The
last
> time I issued a kill command there were 20000 locks for this bad spid.
> The test box does not have anything running on it besides anti virus
> software. The production box does have some very low intensive stuff
running
> on it.
> - The procedure in question is using dynamic sql
> - I set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> Does anyone have any obvious rabbit holes I could go down?
> Thanks,
> Mike
>|||Could you post the proc in question? It could be any number of things.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Mike S" <sagaeta@.yahoo.com> wrote in message
news:edvn8ILKEHA.1156@.TK2MSFTNGP09.phx.gbl...
> I am runninga stored procedure on a test box (1GB CPU and 500MB RAM) and
it
> takes about 20 minutes. The locks max out at about 2400 (sp_lock). I run
it
> on a production box and it is taking hours and in some cases not even
coming
> close to finishing. The locks on the production box keep going up. The
last
> time I issued a kill command there were 20000 locks for this bad spid.
> The test box does not have anything running on it besides anti virus
> software. The production box does have some very low intensive stuff
running
> on it.
> - The procedure in question is using dynamic sql
> - I set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> Does anyone have any obvious rabbit holes I could go down?
> Thanks,
> Mike
>
Subscribe to:
Posts (Atom)