HI,
Q1. my database use "simple" recovery mode now. but i found that
the transaction log is still growth ... about 50Mb per day (database
size is about 1GB)... i found some forum said "if choose simple
recovery mode, no need to shrink the database" <- is it right?
Q2. If i create the maintenance plan, should i choose "reorganize data
and index pages", "update statistics used by query optimizer" and
"remove unused space from database files" (my database is "simple"
recovery mode) ?
Q3. or i just use schedule job to run a "dbcc shrinkfile" command and
backup the tran log to keep the size of tran log (my database is
"simple" recovery mode) ?
Q4 is "dbcc INDEXFRAG" commnad useful to keep the size of transaction
log (use the command with "dbcc shrinkfile" and backup tran log) '
any risk using "dbcc indexfrag?
Need your help ! thx a lot!
Kennethsee inline
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"chunman" <chunman@.iloveilove.com> wrote in message
news:3990957.0401280216.1035f23@.posting.google.com...
> HI,
> Q1. my database use "simple" recovery mode now. but i found that
> the transaction log is still growth ... about 50Mb per day (database
> size is about 1GB)... i found some forum said "if choose simple
> recovery mode, no need to shrink the database" <- is it right?
If your database was at some point in full recovery mode, and you have
switched to simple, you should probably use shrinkfile to reduce the size of
the log. Even if the db has ALWAYS been in simple recovery mode, all
transactions are logged, then the log is automatically truncated during
checkpoints. However the log must STILL grow to be large enought to handle
the largest single transaction you ever do ( plus everything else that
occurs during the largest transaction.). That might explain the size of your
log...
> Q2. If i create the maintenance plan, should i choose "reorganize data
> and index pages", "update statistics used by query optimizer" and
> "remove unused space from database files" (my database is "simple"
> recovery mode) ?
It is a normal index maintenance item to work on indexes... The reorg data
and index pages uses dbcc dbreindex ( which essentially drops and re-creates
all of the indexes.) the tables will be un-available during this time. A
less instrusive way to do index maintenance is to create a job that does
DBCC indexdefrag.. Indexdefrag attempts to do (essentially) the same thing
as dbreindex, but does not hold locks as much, so the tables will be more
available.
Index stats are automatically re-done when indexes are dropped/recreated.
However if you do index maintenance rarely, you may wish to update
statistics in-between index maintenance schedule times. Some people do this,
others do not, and reasonable people differ in their opinions. I try to do
index statistics with 100 sample as often as possible when the database
tables are being changed frequently.
The remove space is only necessary if you have deleted lots of records... I
choose NOT to have that in the plan, but to monitor that separately and make
my own decision (instead of automating this one).
> Q3. or i just use schedule job to run a "dbcc shrinkfile" command and
> backup the tran log to keep the size of tran log (my database is
> "simple" recovery mode) ?
If your database has always been in simple recovery mode, and you use
shrinkfile, it will probably re-grow to that same size as before whenever
the biggest transaction runs...
> Q4 is "dbcc INDEXFRAG" commnad useful to keep the size of transaction
> log (use the command with "dbcc shrinkfile" and backup tran log) '
> any risk using "dbcc indexfrag?
>
No, it is OK to use indexdefrag instead of dbreindex ( Many people do.)
> Need your help ! thx a lot!
> Kennethsql
Showing posts with label mode. Show all posts
Showing posts with label mode. Show all posts
Tuesday, March 27, 2012
Do I need to use "dbcc shrinkfile" command to shrink the transaction log?
HI,
Q1. my database use "simple" recovery mode now. but i found that
the transaction log is still growth ... about 50Mb per day (database
size is about 1GB)... i found some forum said "if choose simple
recovery mode, no need to shrink the database" <- is it right?
Q2. If i create the maintenance plan, should i choose "reorganize data
and index pages", "update statistics used by query optimizer" and
"remove unused space from database files" (my database is "simple"
recovery mode) ?
Q3. or i just use schedule job to run a "dbcc shrinkfile" command and
backup the tran log to keep the size of tran log (my database is
"simple" recovery mode) ?
Q4 is "dbcc INDEXFRAG" commnad useful to keep the size of transaction
log (use the command with "dbcc shrinkfile" and backup tran log) '
any risk using "dbcc indexfrag?
Need your help ! thx a lot!
Kennethsee inline
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"chunman" <chunman@.iloveilove.com> wrote in message
news:3990957.0401280216.1035f23@.posting.google.com...
If your database was at some point in full recovery mode, and you have
switched to simple, you should probably use shrinkfile to reduce the size of
the log. Even if the db has ALWAYS been in simple recovery mode, all
transactions are logged, then the log is automatically truncated during
checkpoints. However the log must STILL grow to be large enought to handle
the largest single transaction you ever do ( plus everything else that
occurs during the largest transaction.). That might explain the size of your
log...
It is a normal index maintenance item to work on indexes... The reorg data
and index pages uses dbcc dbreindex ( which essentially drops and re-creates
all of the indexes.) the tables will be un-available during this time. A
less instrusive way to do index maintenance is to create a job that does
DBCC indexdefrag.. Indexdefrag attempts to do (essentially) the same thing
as dbreindex, but does not hold locks as much, so the tables will be more
available.
Index stats are automatically re-done when indexes are dropped/recreated.
However if you do index maintenance rarely, you may wish to update
statistics in-between index maintenance schedule times. Some people do this,
others do not, and reasonable people differ in their opinions. I try to do
index statistics with 100 sample as often as possible when the database
tables are being changed frequently.
The remove space is only necessary if you have deleted lots of records... I
choose NOT to have that in the plan, but to monitor that separately and make
my own decision (instead of automating this one).
If your database has always been in simple recovery mode, and you use
shrinkfile, it will probably re-grow to that same size as before whenever
the biggest transaction runs...
No, it is OK to use indexdefrag instead of dbreindex ( Many people do.)
Q1. my database use "simple" recovery mode now. but i found that
the transaction log is still growth ... about 50Mb per day (database
size is about 1GB)... i found some forum said "if choose simple
recovery mode, no need to shrink the database" <- is it right?
Q2. If i create the maintenance plan, should i choose "reorganize data
and index pages", "update statistics used by query optimizer" and
"remove unused space from database files" (my database is "simple"
recovery mode) ?
Q3. or i just use schedule job to run a "dbcc shrinkfile" command and
backup the tran log to keep the size of tran log (my database is
"simple" recovery mode) ?
Q4 is "dbcc INDEXFRAG" commnad useful to keep the size of transaction
log (use the command with "dbcc shrinkfile" and backup tran log) '
any risk using "dbcc indexfrag?
Need your help ! thx a lot!
Kennethsee inline
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"chunman" <chunman@.iloveilove.com> wrote in message
news:3990957.0401280216.1035f23@.posting.google.com...
quote:
> HI,
> Q1. my database use "simple" recovery mode now. but i found that
> the transaction log is still growth ... about 50Mb per day (database
> size is about 1GB)... i found some forum said "if choose simple
> recovery mode, no need to shrink the database" <- is it right?
If your database was at some point in full recovery mode, and you have
switched to simple, you should probably use shrinkfile to reduce the size of
the log. Even if the db has ALWAYS been in simple recovery mode, all
transactions are logged, then the log is automatically truncated during
checkpoints. However the log must STILL grow to be large enought to handle
the largest single transaction you ever do ( plus everything else that
occurs during the largest transaction.). That might explain the size of your
log...
quote:
> Q2. If i create the maintenance plan, should i choose "reorganize data
> and index pages", "update statistics used by query optimizer" and
> "remove unused space from database files" (my database is "simple"
> recovery mode) ?
It is a normal index maintenance item to work on indexes... The reorg data
and index pages uses dbcc dbreindex ( which essentially drops and re-creates
all of the indexes.) the tables will be un-available during this time. A
less instrusive way to do index maintenance is to create a job that does
DBCC indexdefrag.. Indexdefrag attempts to do (essentially) the same thing
as dbreindex, but does not hold locks as much, so the tables will be more
available.
Index stats are automatically re-done when indexes are dropped/recreated.
However if you do index maintenance rarely, you may wish to update
statistics in-between index maintenance schedule times. Some people do this,
others do not, and reasonable people differ in their opinions. I try to do
index statistics with 100 sample as often as possible when the database
tables are being changed frequently.
The remove space is only necessary if you have deleted lots of records... I
choose NOT to have that in the plan, but to monitor that separately and make
my own decision (instead of automating this one).
quote:
> Q3. or i just use schedule job to run a "dbcc shrinkfile" command and
> backup the tran log to keep the size of tran log (my database is
> "simple" recovery mode) ?
If your database has always been in simple recovery mode, and you use
shrinkfile, it will probably re-grow to that same size as before whenever
the biggest transaction runs...
quote:
> Q4 is "dbcc INDEXFRAG" commnad useful to keep the size of transaction
> log (use the command with "dbcc shrinkfile" and backup tran log) '
> any risk using "dbcc indexfrag?
>
No, it is OK to use indexdefrag instead of dbreindex ( Many people do.)
quote:
> Need your help ! thx a lot!
> Kenneth
Monday, March 19, 2012
DMO and .sql
hi
I juzz wanted to know if i can execute an .sql script using SQL-DMO. The .sql file is generated using Generate SQL Scripts from the console mode of SQL Server.... if so.. how do i proceed .
thankxHave you looked at methods ExecuteWithResults and/or ExecuteImmediate in BOL. I've used these in the past, I had to open up the script file and read it into a variable. Then pass the variable to one of these 2 functions. If using any of these be careful with 'GO' within the script file. In the past it would error on this, I don't know about 2000.
I juzz wanted to know if i can execute an .sql script using SQL-DMO. The .sql file is generated using Generate SQL Scripts from the console mode of SQL Server.... if so.. how do i proceed .
thankxHave you looked at methods ExecuteWithResults and/or ExecuteImmediate in BOL. I've used these in the past, I had to open up the script file and read it into a variable. Then pass the variable to one of these 2 functions. If using any of these be careful with 'GO' within the script file. In the past it would error on this, I don't know about 2000.
Saturday, February 25, 2012
distribution db in suspect mode
Hi All !!!!
I clone sqlserver's files from one server(A) to another server(B), . On the
(A) server there were transactional replication from server(A) to subscriber
server(XX). There are ditributor and publiatio on same server(A).
After cloning sql files to server(B) i got the distribution and the
piblication databases in suspect mode. I can't perform anything while these
databases in suspecet mode. I can't also to disable distribution (this thing
will also help me.)
Any ideas??//
TNX in advance.
"Imagination is more important than knolwege" (Albert Einshtein)
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...ation/200509/1
These artivles should help:
http://www.karaszi.com/SQLServer/inf...suspect_db.asp
http://www.windowsitpro.com/Article/...D/492/492.html
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
I clone sqlserver's files from one server(A) to another server(B), . On the
(A) server there were transactional replication from server(A) to subscriber
server(XX). There are ditributor and publiatio on same server(A).
After cloning sql files to server(B) i got the distribution and the
piblication databases in suspect mode. I can't perform anything while these
databases in suspecet mode. I can't also to disable distribution (this thing
will also help me.)
Any ideas??//
TNX in advance.
"Imagination is more important than knolwege" (Albert Einshtein)
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...ation/200509/1
These artivles should help:
http://www.karaszi.com/SQLServer/inf...suspect_db.asp
http://www.windowsitpro.com/Article/...D/492/492.html
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Distribution Database
My distribution Database is set to Simple mode , but the log file is filling
up every few days. the error is as follow
The log file for database 'Distribution_CCMSQL' is full. Back up the
transaction log for the database to free up some log space..
what is going on how can I backup the transaction log when my database is in
Simple Mode.
Thanks,
run a dbcc opentran in there to see if there is an orphaned transaction.
Other than that I would put it into full and dump it every 15-20 minutes.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<msnews.microsoft.com> wrote in message
news:O4h99l7xHHA.3724@.TK2MSFTNGP06.phx.gbl...
> My distribution Database is set to Simple mode , but the log file is
> filling up every few days. the error is as follow
> The log file for database 'Distribution_CCMSQL' is full. Back up the
> transaction log for the database to free up some log space..
> what is going on how can I backup the transaction log when my database is
> in Simple Mode.
> Thanks,
>
up every few days. the error is as follow
The log file for database 'Distribution_CCMSQL' is full. Back up the
transaction log for the database to free up some log space..
what is going on how can I backup the transaction log when my database is in
Simple Mode.
Thanks,
run a dbcc opentran in there to see if there is an orphaned transaction.
Other than that I would put it into full and dump it every 15-20 minutes.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<msnews.microsoft.com> wrote in message
news:O4h99l7xHHA.3724@.TK2MSFTNGP06.phx.gbl...
> My distribution Database is set to Simple mode , but the log file is
> filling up every few days. the error is as follow
> The log file for database 'Distribution_CCMSQL' is full. Back up the
> transaction log for the database to free up some log space..
> what is going on how can I backup the transaction log when my database is
> in Simple Mode.
> Thanks,
>
Subscribe to:
Posts (Atom)