Hi,
Unfortunately, we are a small company and don't have a 'DBA' so I'm stuck wi
th any SQL server admin. My question concerns the size of the log file. I
have a small database where the .MDF file is around 8MB but the .LDF file
is around 180MB. I can onl
y figure that there is record of every transaction since day one. In our s
ituation, I am only concerned with transactions that persist for the duratio
n of one logical programatic update. We're never going to rollback to any t
ime in the past, etc.
Is there some way to tell sql server to limit the amount of info saved in th
e log file.
Thanks,
johnJohn
If you 'don't carry about your data' you can put the database in SIMPLE
recovery mode, otherwise perfom BACKUP LOG or/and DBCC SHRINKFILE
For more details please refer to the BOL
"JohnR" <anonymous@.discussions.microsoft.com> wrote in message
news:10A26988-292E-4A2D-A306-EAB6DFF4E6C7@.microsoft.com...
> Hi,
> Unfortunately, we are a small company and don't have a 'DBA' so I'm stuck
with any SQL server admin. My question concerns the size of the log file.
I have a small database where the .MDF file is around 8MB but the .LDF
file is around 180MB. I can only figure that there is record of every
transaction since day one. In our situation, I am only concerned with
transactions that persist for the duration of one logical programatic
update. We're never going to rollback to any time in the past, etc.
> Is there some way to tell sql server to limit the amount of info saved in
the log file.
> Thanks,
> john|||Hi,
Is there some way to tell sql server to limit the amount of info saved in
the log file.
No.
But you could restrict the log file size using Enterprise manager ,
Database, property, transaction log ....
To restrict the Transaction log file growth and if you do not require any
rollbacks, set the database with SIMPLE recovery model. In SIMPLE recovery
model the log file will be cleraed as soon as commit is done.
How to set the recvery to simple
--
ALTER database <dbname> set recovery SIMPLE
How to reduce the current transaction log file size
----
Execute the below script:-
backup log <dbname with with truncate_only
go
dbcc shrinkfile('logical_ldf_name',truncateon
ly)
How to check the log size of a db and usage
---
dbcc SQLPERF(LOGSPACE)
Thanks
Hari
MCDBA
"JohnR" <anonymous@.discussions.microsoft.com> wrote in message
news:10A26988-292E-4A2D-A306-EAB6DFF4E6C7@.microsoft.com...
> Hi,
> Unfortunately, we are a small company and don't have a 'DBA' so I'm stuck
with any SQL server admin. My question concerns the size of the log file.
I have a small database where the .MDF file is around 8MB but the .LDF
file is around 180MB. I can only figure that there is record of every
transaction since day one. In our situation, I am only concerned with
transactions that persist for the duration of one logical programatic
update. We're never going to rollback to any time in the past, etc.
> Is there some way to tell sql server to limit the amount of info saved in
the log file.
> Thanks,
> john|||Thanks, that did the trick.|||And , if you are NOT going to put the database in simple recovery mode, you
will have to back up the log on a regular basis or the log will become full,
and no users will be able to insert, update or delete
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"JohnR" <anonymous@.discussions.microsoft.com> wrote in message
news:10A26988-292E-4A2D-A306-EAB6DFF4E6C7@.microsoft.com...
> Hi,
> Unfortunately, we are a small company and don't have a 'DBA' so I'm stuck
with any SQL server admin. My question concerns the size of the log file.
I have a small database where the .MDF file is around 8MB but the .LDF
file is around 180MB. I can only figure that there is record of every
transaction since day one. In our situation, I am only concerned with
transactions that persist for the duration of one logical programatic
update. We're never going to rollback to any time in the past, etc.
> Is there some way to tell sql server to limit the amount of info saved in
the log file.
> Thanks,
> john
Showing posts with label dba. Show all posts
Showing posts with label dba. Show all posts
Friday, March 23, 2012
log file size
Hi
Unfortunately, we are a small company and don't have a 'DBA' so I'm stuck with any SQL server admin. My question concerns the size of the log file. I have a small database where the .MDF file is around 8MB but the .LDF file is around 180MB. I can only figure that there is record of every transaction since day one. In our situation, I am only concerned with transactions that persist for the duration of one logical programatic update. We're never going to rollback to any time in the past, etc
Is there some way to tell sql server to limit the amount of info saved in the log file
Thanks
johnJohn
If you 'don't carry about your data' you can put the database in SIMPLE
recovery mode, otherwise perfom BACKUP LOG or/and DBCC SHRINKFILE
For more details please refer to the BOL
"JohnR" <anonymous@.discussions.microsoft.com> wrote in message
news:10A26988-292E-4A2D-A306-EAB6DFF4E6C7@.microsoft.com...
> Hi,
> Unfortunately, we are a small company and don't have a 'DBA' so I'm stuck
with any SQL server admin. My question concerns the size of the log file.
I have a small database where the .MDF file is around 8MB but the .LDF
file is around 180MB. I can only figure that there is record of every
transaction since day one. In our situation, I am only concerned with
transactions that persist for the duration of one logical programatic
update. We're never going to rollback to any time in the past, etc.
> Is there some way to tell sql server to limit the amount of info saved in
the log file.
> Thanks,
> john|||Hi,
Is there some way to tell sql server to limit the amount of info saved in
the log file.
No.
But you could restrict the log file size using Enterprise manager ,
Database, property, transaction log ....
To restrict the Transaction log file growth and if you do not require any
rollbacks, set the database with SIMPLE recovery model. In SIMPLE recovery
model the log file will be cleraed as soon as commit is done.
How to set the recvery to simple
--
ALTER database <dbname> set recovery SIMPLE
How to reduce the current transaction log file size
----
Execute the below script:-
backup log <dbname with with truncate_only
go
dbcc shrinkfile('logical_ldf_name',truncateonly)
How to check the log size of a db and usage
---
dbcc SQLPERF(LOGSPACE)
Thanks
Hari
MCDBA
"JohnR" <anonymous@.discussions.microsoft.com> wrote in message
news:10A26988-292E-4A2D-A306-EAB6DFF4E6C7@.microsoft.com...
> Hi,
> Unfortunately, we are a small company and don't have a 'DBA' so I'm stuck
with any SQL server admin. My question concerns the size of the log file.
I have a small database where the .MDF file is around 8MB but the .LDF
file is around 180MB. I can only figure that there is record of every
transaction since day one. In our situation, I am only concerned with
transactions that persist for the duration of one logical programatic
update. We're never going to rollback to any time in the past, etc.
> Is there some way to tell sql server to limit the amount of info saved in
the log file.
> Thanks,
> john|||And , if you are NOT going to put the database in simple recovery mode, you
will have to back up the log on a regular basis or the log will become full,
and no users will be able to insert, update or delete
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"JohnR" <anonymous@.discussions.microsoft.com> wrote in message
news:10A26988-292E-4A2D-A306-EAB6DFF4E6C7@.microsoft.com...
> Hi,
> Unfortunately, we are a small company and don't have a 'DBA' so I'm stuck
with any SQL server admin. My question concerns the size of the log file.
I have a small database where the .MDF file is around 8MB but the .LDF
file is around 180MB. I can only figure that there is record of every
transaction since day one. In our situation, I am only concerned with
transactions that persist for the duration of one logical programatic
update. We're never going to rollback to any time in the past, etc.
> Is there some way to tell sql server to limit the amount of info saved in
the log file.
> Thanks,
> john
Unfortunately, we are a small company and don't have a 'DBA' so I'm stuck with any SQL server admin. My question concerns the size of the log file. I have a small database where the .MDF file is around 8MB but the .LDF file is around 180MB. I can only figure that there is record of every transaction since day one. In our situation, I am only concerned with transactions that persist for the duration of one logical programatic update. We're never going to rollback to any time in the past, etc
Is there some way to tell sql server to limit the amount of info saved in the log file
Thanks
johnJohn
If you 'don't carry about your data' you can put the database in SIMPLE
recovery mode, otherwise perfom BACKUP LOG or/and DBCC SHRINKFILE
For more details please refer to the BOL
"JohnR" <anonymous@.discussions.microsoft.com> wrote in message
news:10A26988-292E-4A2D-A306-EAB6DFF4E6C7@.microsoft.com...
> Hi,
> Unfortunately, we are a small company and don't have a 'DBA' so I'm stuck
with any SQL server admin. My question concerns the size of the log file.
I have a small database where the .MDF file is around 8MB but the .LDF
file is around 180MB. I can only figure that there is record of every
transaction since day one. In our situation, I am only concerned with
transactions that persist for the duration of one logical programatic
update. We're never going to rollback to any time in the past, etc.
> Is there some way to tell sql server to limit the amount of info saved in
the log file.
> Thanks,
> john|||Hi,
Is there some way to tell sql server to limit the amount of info saved in
the log file.
No.
But you could restrict the log file size using Enterprise manager ,
Database, property, transaction log ....
To restrict the Transaction log file growth and if you do not require any
rollbacks, set the database with SIMPLE recovery model. In SIMPLE recovery
model the log file will be cleraed as soon as commit is done.
How to set the recvery to simple
--
ALTER database <dbname> set recovery SIMPLE
How to reduce the current transaction log file size
----
Execute the below script:-
backup log <dbname with with truncate_only
go
dbcc shrinkfile('logical_ldf_name',truncateonly)
How to check the log size of a db and usage
---
dbcc SQLPERF(LOGSPACE)
Thanks
Hari
MCDBA
"JohnR" <anonymous@.discussions.microsoft.com> wrote in message
news:10A26988-292E-4A2D-A306-EAB6DFF4E6C7@.microsoft.com...
> Hi,
> Unfortunately, we are a small company and don't have a 'DBA' so I'm stuck
with any SQL server admin. My question concerns the size of the log file.
I have a small database where the .MDF file is around 8MB but the .LDF
file is around 180MB. I can only figure that there is record of every
transaction since day one. In our situation, I am only concerned with
transactions that persist for the duration of one logical programatic
update. We're never going to rollback to any time in the past, etc.
> Is there some way to tell sql server to limit the amount of info saved in
the log file.
> Thanks,
> john|||And , if you are NOT going to put the database in simple recovery mode, you
will have to back up the log on a regular basis or the log will become full,
and no users will be able to insert, update or delete
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"JohnR" <anonymous@.discussions.microsoft.com> wrote in message
news:10A26988-292E-4A2D-A306-EAB6DFF4E6C7@.microsoft.com...
> Hi,
> Unfortunately, we are a small company and don't have a 'DBA' so I'm stuck
with any SQL server admin. My question concerns the size of the log file.
I have a small database where the .MDF file is around 8MB but the .LDF
file is around 180MB. I can only figure that there is record of every
transaction since day one. In our situation, I am only concerned with
transactions that persist for the duration of one logical programatic
update. We're never going to rollback to any time in the past, etc.
> Is there some way to tell sql server to limit the amount of info saved in
the log file.
> Thanks,
> john
log file size
Hi,
Unfortunately, we are a small company and don't have a 'DBA' so I'm stuck with any SQL server admin. My question concerns the size of the log file. I have a small database where the .MDF file is around 8MB but the .LDF file is around 180MB. I can onl
y figure that there is record of every transaction since day one. In our situation, I am only concerned with transactions that persist for the duration of one logical programatic update. We're never going to rollback to any time in the past, etc.
Is there some way to tell sql server to limit the amount of info saved in the log file.
Thanks,
john
John
If you 'don't carry about your data' you can put the database in SIMPLE
recovery mode, otherwise perfom BACKUP LOG or/and DBCC SHRINKFILE
For more details please refer to the BOL
"JohnR" <anonymous@.discussions.microsoft.com> wrote in message
news:10A26988-292E-4A2D-A306-EAB6DFF4E6C7@.microsoft.com...
> Hi,
> Unfortunately, we are a small company and don't have a 'DBA' so I'm stuck
with any SQL server admin. My question concerns the size of the log file.
I have a small database where the .MDF file is around 8MB but the .LDF
file is around 180MB. I can only figure that there is record of every
transaction since day one. In our situation, I am only concerned with
transactions that persist for the duration of one logical programatic
update. We're never going to rollback to any time in the past, etc.
> Is there some way to tell sql server to limit the amount of info saved in
the log file.
> Thanks,
> john
|||Hi,
Is there some way to tell sql server to limit the amount of info saved in
the log file.
No.
But you could restrict the log file size using Enterprise manager ,
Database, property, transaction log ....
To restrict the Transaction log file growth and if you do not require any
rollbacks, set the database with SIMPLE recovery model. In SIMPLE recovery
model the log file will be cleraed as soon as commit is done.
How to set the recvery to simple
ALTER database <dbname> set recovery SIMPLE
How to reduce the current transaction log file size
Execute the below script:-
backup log <dbname with with truncate_only
go
dbcc shrinkfile('logical_ldf_name',truncateonly)
How to check the log size of a db and usage
dbcc SQLPERF(LOGSPACE)
Thanks
Hari
MCDBA
"JohnR" <anonymous@.discussions.microsoft.com> wrote in message
news:10A26988-292E-4A2D-A306-EAB6DFF4E6C7@.microsoft.com...
> Hi,
> Unfortunately, we are a small company and don't have a 'DBA' so I'm stuck
with any SQL server admin. My question concerns the size of the log file.
I have a small database where the .MDF file is around 8MB but the .LDF
file is around 180MB. I can only figure that there is record of every
transaction since day one. In our situation, I am only concerned with
transactions that persist for the duration of one logical programatic
update. We're never going to rollback to any time in the past, etc.
> Is there some way to tell sql server to limit the amount of info saved in
the log file.
> Thanks,
> john
|||Thanks, that did the trick.
|||And , if you are NOT going to put the database in simple recovery mode, you
will have to back up the log on a regular basis or the log will become full,
and no users will be able to insert, update or delete
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"JohnR" <anonymous@.discussions.microsoft.com> wrote in message
news:10A26988-292E-4A2D-A306-EAB6DFF4E6C7@.microsoft.com...
> Hi,
> Unfortunately, we are a small company and don't have a 'DBA' so I'm stuck
with any SQL server admin. My question concerns the size of the log file.
I have a small database where the .MDF file is around 8MB but the .LDF
file is around 180MB. I can only figure that there is record of every
transaction since day one. In our situation, I am only concerned with
transactions that persist for the duration of one logical programatic
update. We're never going to rollback to any time in the past, etc.
> Is there some way to tell sql server to limit the amount of info saved in
the log file.
> Thanks,
> john
Unfortunately, we are a small company and don't have a 'DBA' so I'm stuck with any SQL server admin. My question concerns the size of the log file. I have a small database where the .MDF file is around 8MB but the .LDF file is around 180MB. I can onl
y figure that there is record of every transaction since day one. In our situation, I am only concerned with transactions that persist for the duration of one logical programatic update. We're never going to rollback to any time in the past, etc.
Is there some way to tell sql server to limit the amount of info saved in the log file.
Thanks,
john
John
If you 'don't carry about your data' you can put the database in SIMPLE
recovery mode, otherwise perfom BACKUP LOG or/and DBCC SHRINKFILE
For more details please refer to the BOL
"JohnR" <anonymous@.discussions.microsoft.com> wrote in message
news:10A26988-292E-4A2D-A306-EAB6DFF4E6C7@.microsoft.com...
> Hi,
> Unfortunately, we are a small company and don't have a 'DBA' so I'm stuck
with any SQL server admin. My question concerns the size of the log file.
I have a small database where the .MDF file is around 8MB but the .LDF
file is around 180MB. I can only figure that there is record of every
transaction since day one. In our situation, I am only concerned with
transactions that persist for the duration of one logical programatic
update. We're never going to rollback to any time in the past, etc.
> Is there some way to tell sql server to limit the amount of info saved in
the log file.
> Thanks,
> john
|||Hi,
Is there some way to tell sql server to limit the amount of info saved in
the log file.
No.
But you could restrict the log file size using Enterprise manager ,
Database, property, transaction log ....
To restrict the Transaction log file growth and if you do not require any
rollbacks, set the database with SIMPLE recovery model. In SIMPLE recovery
model the log file will be cleraed as soon as commit is done.
How to set the recvery to simple
ALTER database <dbname> set recovery SIMPLE
How to reduce the current transaction log file size
Execute the below script:-
backup log <dbname with with truncate_only
go
dbcc shrinkfile('logical_ldf_name',truncateonly)
How to check the log size of a db and usage
dbcc SQLPERF(LOGSPACE)
Thanks
Hari
MCDBA
"JohnR" <anonymous@.discussions.microsoft.com> wrote in message
news:10A26988-292E-4A2D-A306-EAB6DFF4E6C7@.microsoft.com...
> Hi,
> Unfortunately, we are a small company and don't have a 'DBA' so I'm stuck
with any SQL server admin. My question concerns the size of the log file.
I have a small database where the .MDF file is around 8MB but the .LDF
file is around 180MB. I can only figure that there is record of every
transaction since day one. In our situation, I am only concerned with
transactions that persist for the duration of one logical programatic
update. We're never going to rollback to any time in the past, etc.
> Is there some way to tell sql server to limit the amount of info saved in
the log file.
> Thanks,
> john
|||Thanks, that did the trick.
|||And , if you are NOT going to put the database in simple recovery mode, you
will have to back up the log on a regular basis or the log will become full,
and no users will be able to insert, update or delete
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"JohnR" <anonymous@.discussions.microsoft.com> wrote in message
news:10A26988-292E-4A2D-A306-EAB6DFF4E6C7@.microsoft.com...
> Hi,
> Unfortunately, we are a small company and don't have a 'DBA' so I'm stuck
with any SQL server admin. My question concerns the size of the log file.
I have a small database where the .MDF file is around 8MB but the .LDF
file is around 180MB. I can only figure that there is record of every
transaction since day one. In our situation, I am only concerned with
transactions that persist for the duration of one logical programatic
update. We're never going to rollback to any time in the past, etc.
> Is there some way to tell sql server to limit the amount of info saved in
the log file.
> Thanks,
> john
Monday, March 19, 2012
Log file growing very large?
Hello All,
All you SQL dba guru's.
I have na MSDE server. All seems to be working with app but I looked at the
server log file and saw that it has grown to 65 gigs. The mdf file is only
50 megs.
What is causing the log file to grow so large.?
Can I just dump the log file?
Thanks, Bill
You are using FULL recovery mode, and you've never performed a backup or
dumped the transaction logs?
http://www.aspfaq.com/
(Reverse address to reply.)
"Bill D" <Bill@.test.com> wrote in message
news:eFPpo#5gEHA.3536@.TK2MSFTNGP12.phx.gbl...
> Hello All,
> All you SQL dba guru's.
> I have na MSDE server. All seems to be working with app but I looked at
the
> server log file and saw that it has grown to 65 gigs. The mdf file is
only
> 50 megs.
> What is causing the log file to grow so large.?
> Can I just dump the log file?
> Thanks, Bill
>
|||Hi,
Add on to Aaron,
Looks like the recovery model for your database is FULL or
BULK_LOGGED.Please change the recovery model for the database to
SIMPLE if your database is non production. Use the below command to set the
database to SIMPLE.
ALTER database <DBNAME> set recovery SIMPLE
If you do not have the 65 G to take a backup or if you do not want the
transaction log backup. You could truncate the transaction
log and shrink the LDF file.
backup log <dbname> with truncate_only
go
DBCC SHRINKFILE (db1_log1_logical_name,truncateonly)
If you need the transaction log backup do:-
backup log <dbname> to disk='d:\backup\dbname.trn' ( This might fail since
you do not have hard disk space)
go
DBCC SHRINKFILE (db1_log1_logical_name,truncateonly)
Now execute the below command to see log file size and usage.
DBCC SQLPERF(LOGSPACE)
Note:
If you need to keep the recovery model to FULL then schedule a transaction
log backup in frequent intervals (atleast 30 minutes once).
This will keep the log file size under control.
Thanks
Hari
MCDBA
"Bill D" <Bill@.test.com> wrote in message
news:eFPpo#5gEHA.3536@.TK2MSFTNGP12.phx.gbl...
> Hello All,
> All you SQL dba guru's.
> I have na MSDE server. All seems to be working with app but I looked at
the
> server log file and saw that it has grown to 65 gigs. The mdf file is
only
> 50 megs.
> What is causing the log file to grow so large.?
> Can I just dump the log file?
> Thanks, Bill
>
|||Hello and thanks,
does performing a "backup log <dbname> to disk='d:\backup\dbname.trn' "
clear out and reduce the size of the log as part of its process?
Or do I need to do a "backup log <dbname> with truncate_only" also?
Thanks,
Bill
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OpOydK6gEHA.3992@.TK2MSFTNGP11.phx.gbl...
> Hi,
>
> Add on to Aaron,
> Looks like the recovery model for your database is FULL or
> BULK_LOGGED.Please change the recovery model for the database to
> SIMPLE if your database is non production. Use the below command to set
the
> database to SIMPLE.
> ALTER database <DBNAME> set recovery SIMPLE
> If you do not have the 65 G to take a backup or if you do not want the
> transaction log backup. You could truncate the transaction
> log and shrink the LDF file.
> backup log <dbname> with truncate_only
> go
> DBCC SHRINKFILE (db1_log1_logical_name,truncateonly)
> If you need the transaction log backup do:-
> backup log <dbname> to disk='d:\backup\dbname.trn' ( This might fail
since
> you do not have hard disk space)
> go
> DBCC SHRINKFILE (db1_log1_logical_name,truncateonly)
> Now execute the below command to see log file size and usage.
> DBCC SQLPERF(LOGSPACE)
>
> Note:
> If you need to keep the recovery model to FULL then schedule a transaction
> log backup in frequent intervals (atleast 30 minutes once).
> This will keep the log file size under control.
> Thanks
> Hari
> MCDBA
>
> "Bill D" <Bill@.test.com> wrote in message
> news:eFPpo#5gEHA.3536@.TK2MSFTNGP12.phx.gbl...
> the
> only
>
|||Hi,
Either one will do.
If you need the transaction log backup execute below;
backup log <dbname> to disk='d:\backup\dbname.trn
Incase if you do not require the transaction log backup go for;
backup log <dbname> with truncate_only
The above commands will clear the log , bit to reduce the physical LDF file
size you will have to execute the below command:-
DBCC SHRINKFILE (db1_log1_logical_name,truncateonly)
Thanks
Hari
MCDBA
"Bill D" <Bill@.test.com> wrote in message
news:ONVLKt6gEHA.632@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Hello and thanks,
> does performing a "backup log <dbname> to disk='d:\backup\dbname.trn' "
> clear out and reduce the size of the log as part of its process?
> Or do I need to do a "backup log <dbname> with truncate_only" also?
> Thanks,
> Bill
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:OpOydK6gEHA.3992@.TK2MSFTNGP11.phx.gbl...
> the
> since
transaction[vbcol=seagreen]
at
>
All you SQL dba guru's.
I have na MSDE server. All seems to be working with app but I looked at the
server log file and saw that it has grown to 65 gigs. The mdf file is only
50 megs.
What is causing the log file to grow so large.?
Can I just dump the log file?
Thanks, Bill
You are using FULL recovery mode, and you've never performed a backup or
dumped the transaction logs?
http://www.aspfaq.com/
(Reverse address to reply.)
"Bill D" <Bill@.test.com> wrote in message
news:eFPpo#5gEHA.3536@.TK2MSFTNGP12.phx.gbl...
> Hello All,
> All you SQL dba guru's.
> I have na MSDE server. All seems to be working with app but I looked at
the
> server log file and saw that it has grown to 65 gigs. The mdf file is
only
> 50 megs.
> What is causing the log file to grow so large.?
> Can I just dump the log file?
> Thanks, Bill
>
|||Hi,
Add on to Aaron,
Looks like the recovery model for your database is FULL or
BULK_LOGGED.Please change the recovery model for the database to
SIMPLE if your database is non production. Use the below command to set the
database to SIMPLE.
ALTER database <DBNAME> set recovery SIMPLE
If you do not have the 65 G to take a backup or if you do not want the
transaction log backup. You could truncate the transaction
log and shrink the LDF file.
backup log <dbname> with truncate_only
go
DBCC SHRINKFILE (db1_log1_logical_name,truncateonly)
If you need the transaction log backup do:-
backup log <dbname> to disk='d:\backup\dbname.trn' ( This might fail since
you do not have hard disk space)
go
DBCC SHRINKFILE (db1_log1_logical_name,truncateonly)
Now execute the below command to see log file size and usage.
DBCC SQLPERF(LOGSPACE)
Note:
If you need to keep the recovery model to FULL then schedule a transaction
log backup in frequent intervals (atleast 30 minutes once).
This will keep the log file size under control.
Thanks
Hari
MCDBA
"Bill D" <Bill@.test.com> wrote in message
news:eFPpo#5gEHA.3536@.TK2MSFTNGP12.phx.gbl...
> Hello All,
> All you SQL dba guru's.
> I have na MSDE server. All seems to be working with app but I looked at
the
> server log file and saw that it has grown to 65 gigs. The mdf file is
only
> 50 megs.
> What is causing the log file to grow so large.?
> Can I just dump the log file?
> Thanks, Bill
>
|||Hello and thanks,
does performing a "backup log <dbname> to disk='d:\backup\dbname.trn' "
clear out and reduce the size of the log as part of its process?
Or do I need to do a "backup log <dbname> with truncate_only" also?
Thanks,
Bill
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OpOydK6gEHA.3992@.TK2MSFTNGP11.phx.gbl...
> Hi,
>
> Add on to Aaron,
> Looks like the recovery model for your database is FULL or
> BULK_LOGGED.Please change the recovery model for the database to
> SIMPLE if your database is non production. Use the below command to set
the
> database to SIMPLE.
> ALTER database <DBNAME> set recovery SIMPLE
> If you do not have the 65 G to take a backup or if you do not want the
> transaction log backup. You could truncate the transaction
> log and shrink the LDF file.
> backup log <dbname> with truncate_only
> go
> DBCC SHRINKFILE (db1_log1_logical_name,truncateonly)
> If you need the transaction log backup do:-
> backup log <dbname> to disk='d:\backup\dbname.trn' ( This might fail
since
> you do not have hard disk space)
> go
> DBCC SHRINKFILE (db1_log1_logical_name,truncateonly)
> Now execute the below command to see log file size and usage.
> DBCC SQLPERF(LOGSPACE)
>
> Note:
> If you need to keep the recovery model to FULL then schedule a transaction
> log backup in frequent intervals (atleast 30 minutes once).
> This will keep the log file size under control.
> Thanks
> Hari
> MCDBA
>
> "Bill D" <Bill@.test.com> wrote in message
> news:eFPpo#5gEHA.3536@.TK2MSFTNGP12.phx.gbl...
> the
> only
>
|||Hi,
Either one will do.
If you need the transaction log backup execute below;
backup log <dbname> to disk='d:\backup\dbname.trn
Incase if you do not require the transaction log backup go for;
backup log <dbname> with truncate_only
The above commands will clear the log , bit to reduce the physical LDF file
size you will have to execute the below command:-
DBCC SHRINKFILE (db1_log1_logical_name,truncateonly)
Thanks
Hari
MCDBA
"Bill D" <Bill@.test.com> wrote in message
news:ONVLKt6gEHA.632@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Hello and thanks,
> does performing a "backup log <dbname> to disk='d:\backup\dbname.trn' "
> clear out and reduce the size of the log as part of its process?
> Or do I need to do a "backup log <dbname> with truncate_only" also?
> Thanks,
> Bill
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:OpOydK6gEHA.3992@.TK2MSFTNGP11.phx.gbl...
> the
> since
transaction[vbcol=seagreen]
at
>
Monday, March 12, 2012
Log File Flush Training for Newby
Sorry to ask such a basic question but I have inherited this DBA position and I have no SQL training...
I have a log file that is 148GB (yes GB) but available space is 18GB. I am backing up to \\server\share temporarily but want to free up the space. How do I flush it? I ran maintenance to shrink but not nuch success. It was not setup right initially, as the max size is 2,048,512 (MB). I'm guessing the initial dba was thinking it was KB. Anyway, I need to flush (or whatever it is called now) to reduce the size.
We had BackupExec but they got rid of that for Symantec Backup Exec System Restore which backs up the whole server as an image so it does not backup and flush the logs.
Any suggestions?
Thanks in advance.Hi,
see this kb for more information: http://support.microsoft.com/default.aspx/kb/873235
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Log File Filling Up
We have a 1gig log file that is filling up in about 20 minutes. We really don't have your typical DBA's. I am a developer and I am trying to figure out why it is filling up so fast.
The last thing I have done to try and see where the problem is to run a trace file. I 'think' I was able to have the trace on when the log file filled up again. However, I'm not really sure what to look for or what is causing this problem. Can someone please help?
I'm running Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
We have lots of apps going against the DB (front-end, batch processes, etc). However, the acting 'DBA' says we have many more DB's that are much larger, with the same size log file and just as many transactions and the problem does not happen with them.
I don't know if this helps but it always seems to be the same person that encounters the log file full error. When I look at the trace file and I can see the 'rollback' next to her id - I'm assuming that's when she got the error. So, I figured I would look at the last insert or update that happened before the error. But, I don't know if it was the last transaction that caused the error or was the one that 'put it over the top' as far as filling up. This is a big problem for our users as it happens about twice a day.
i think that may due to:bulk deletion /insertion or update as a one transaction with commiting once at end.
Let developer divide that transaction to small transaction and commit or rollback in case of error.
That is done using begin transaction.
review the SQL commands and how transaction is handled.|||
It also makes a difference whether your database is in simple or full recovery model.
With full recovery, log space will not be reused until a log backup is performed.
With simple recovery model, log space is reused when all transactions with records in that area of the log have either committed or rolled back.
|||You can switch your recovery model from full to bulk-insert when importing tons of data. Then of course put it back and don't foget a full DB backup to protect the data in case you need to recover the database.Log File Filling Up
We have a 1gig log file that is filling up in about 20 minutes. We really don't have your typical DBA's. I am a developer and I am trying to figure out why it is filling up so fast.
The last thing I have done to try and see where the problem is to run a trace file. I 'think' I was able to have the trace on when the log file filled up again. However, I'm not really sure what to look for or what is causing this problem. Can someone please help?
I'm running Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
We have lots of apps going against the DB (front-end, batch processes, etc). However, the acting 'DBA' says we have many more DB's that are much larger, with the same size log file and just as many transactions and the problem does not happen with them.
I don't know if this helps but it always seems to be the same person that encounters the log file full error. When I look at the trace file and I can see the 'rollback' next to her id - I'm assuming that's when she got the error. So, I figured I would look at the last insert or update that happened before the error. But, I don't know if it was the last transaction that caused the error or was the one that 'put it over the top' as far as filling up. This is a big problem for our users as it happens about twice a day.
i think that may due to:bulk deletion /insertion or update as a one transaction with commiting once at end.
Let developer divide that transaction to small transaction and commit or rollback in case of error.
That is done using begin transaction.
review the SQL commands and how transaction is handled.|||
It also makes a difference whether your database is in simple or full recovery model.
With full recovery, log space will not be reused until a log backup is performed.
With simple recovery model, log space is reused when all transactions with records in that area of the log have either committed or rolled back.
|||You can switch your recovery model from full to bulk-insert when importing tons of data. Then of course put it back and don't foget a full DB backup to protect the data in case you need to recover the database.
Subscribe to:
Posts (Atom)