Maintenance Plan and Log size

Hi,

How do I delete a Maintenance Plan that I have prepared?

Also I have my data log growing beyond 8 GB of data. I have around 12 million rows of data in one table and more than 10 million waiting to come in. What do I need to do here? Have another data file for the same database? How does that work? Should I keep the same data file and allow it to grow to say 15-18 GB?

Also my transaction log keeps growing. It can now hold 8 GB of logs. All I am doing is a simple insert into the big table. I changed the recovery model to Simple ( from Full), and did a shrink log and then it shrank to 50 MB. But the space allocated still remains 8 GB. So now I am thinking that I can change it to say 250 MB and bring back the recovery model to Full. Is that permissible? Any backup log commands associated with a particular mode would be helpful. Please let me know.

ThanksHowdy

Easy - just right click the maint plan & delete it.

If you are importing data during a quiet time ( i.e. after hours ) and you dont need to keep the tran log, you could set the database recovery to SIMPLE while you are importing the data, then set it back to FULL after.
This stops the tran log becoming huge. You can flick the database into SIMPLE recovery mode, then run a manual checkpoint ( i.e. use QA & type CHECKPOINT then run it ) then run a DBCC SHRINKFILE command for both the database & log files.

The space allocated remains at 8 GB? Well, if you flick the database into SIMPLE recovery mode, run the shrinkfile command for the log file, you should remove all the emplty space from the tran log. The checkpoint command will write any dirty pages to the database, allowing a clean run at shrinking the tran log. Sometimes however, if you do the same thing in FULL recovery mode, you may not shrink the tran log if the database has uncompleted transactions, which can then stop the tran log shrinking as the LSNs in the tran log may be at odd spots that will stop the tran log from shrinking.

I'd keep the base size of the tran log as small as possible.

Cheers

SG.

Machine hangs after SQL QA query

I tried to delete some records from a table that has 10 million records
(delete <table> where xxx= 'yyy'). This does not have any matching index. So
it used up all the memory (500 MB) ,and the swap(virtual mem), and the whole
machine hanged.
Then today, I stopped the query, and waited for hours...
When I rebooted, it took hours to come up, and the sqlsrvr runs with 50% CPU
and huge memory (both). I believe its still rolling back the 'delete'
transactions.
Is there any way to clean up the resource, and start using the machine in a
normal way? (Im also in the process of increasing the memory to atleast 2GB).
Pl. note that I may not be able to open the EM or QA due to the underlying
heavy load/recovery process(?)...
Thanks a lot
Philipus
Message posted via http://www.droptable.com
Additional Info:
This host has AntiVirus enabled. But I dont think the MDB etc. are excluded
from scanning. But I dont think the QA query mentioned would have anything to
do with AV scan! Does it? What kind of configuration does the AV needs so it
both does its work properly, but at the same time does not hinder my SQL
server work?
-philipus
Philipus wrote:
>I tried to delete some records from a table that has 10 million records
>(delete <table> where xxx= 'yyy'). This does not have any matching index. So
>it used up all the memory (500 MB) ,and the swap(virtual mem), and the whole
>machine hanged.
>Then today, I stopped the query, and waited for hours...
>When I rebooted, it took hours to come up, and the sqlsrvr runs with 50% CPU
>and huge memory (both). I believe its still rolling back the 'delete'
>transactions.
>Is there any way to clean up the resource, and start using the machine in a
>normal way? (Im also in the process of increasing the memory to atleast 2GB).
>Pl. note that I may not be able to open the EM or QA due to the underlying
>heavy load/recovery process(?)...
>Thanks a lot
>Philipus
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200507/1
|||Can you open the SQL Server error log from the windows explorer? If the
database is still recovering you will see messages related to that in the
error log. I think you should just let the database recover.
Do you happen to have a backup that is good enough to replace the current
database?
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Philipus via droptable.com" <forum@.droptable.com> wrote in message
news:51A7C2ADFB58F@.droptable.com...
I tried to delete some records from a table that has 10 million records
(delete <table> where xxx= 'yyy'). This does not have any matching index. So
it used up all the memory (500 MB) ,and the swap(virtual mem), and the whole
machine hanged.
Then today, I stopped the query, and waited for hours...
When I rebooted, it took hours to come up, and the sqlsrvr runs with 50% CPU
and huge memory (both). I believe its still rolling back the 'delete'
transactions.
Is there any way to clean up the resource, and start using the machine in a
normal way? (Im also in the process of increasing the memory to atleast
2GB).
Pl. note that I may not be able to open the EM or QA due to the underlying
heavy load/recovery process(?)...
Thanks a lot
Philipus
Message posted via http://www.droptable.com
|||Since I was not able to do anything in the machine, I just left it for
another day, and it came up after clearing the rollback. Now its normal. The
error log has a huge list of ' roll back...recovering...'
Thanks reddy!
philipus
Narayana Vyas Kondreddi wrote:
>Can you open the SQL Server error log from the windows explorer? If the
>database is still recovering you will see messages related to that in the
>error log. I think you should just let the database recover.
>Do you happen to have a backup that is good enough to replace the current
>database?
>I tried to delete some records from a table that has 10 million records
>(delete <table> where xxx= 'yyy'). This does not have any matching index. So
>it used up all the memory (500 MB) ,and the swap(virtual mem), and the whole
>machine hanged.
>Then today, I stopped the query, and waited for hours...
>When I rebooted, it took hours to come up, and the sqlsrvr runs with 50% CPU
>and huge memory (both). I believe its still rolling back the 'delete'
>transactions.
>Is there any way to clean up the resource, and start using the machine in a
>normal way? (Im also in the process of increasing the memory to atleast
>2GB).
>Pl. note that I may not be able to open the EM or QA due to the underlying
>heavy load/recovery process(?)...
>Thanks a lot
>Philipus
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200507/1

Machine hangs after SQL QA query

I tried to delete some records from a table that has 10 million records
(delete <table> where xxx= 'yyy'). This does not have any matching index. So
it used up all the memory (500 MB) ,and the swap(virtual mem), and the whole
machine hanged.
Then today, I stopped the query, and waited for hours...
When I rebooted, it took hours to come up, and the sqlsrvr runs with 50% CPU
and huge memory (both). I believe its still rolling back the 'delete'
transactions.
Is there any way to clean up the resource, and start using the machine in a
normal way? (Im also in the process of increasing the memory to atleast 2GB).
Pl. note that I may not be able to open the EM or QA due to the underlying
heavy load/recovery process(?)...
Thanks a lot
Philipus
--
Message posted via http://www.sqlmonster.comAdditional Info:
This host has AntiVirus enabled. But I dont think the MDB etc. are excluded
from scanning. But I dont think the QA query mentioned would have anything to
do with AV scan! Does it? What kind of configuration does the AV needs so it
both does its work properly, but at the same time does not hinder my SQL
server work?
-philipus
Philipus wrote:
>I tried to delete some records from a table that has 10 million records
>(delete <table> where xxx= 'yyy'). This does not have any matching index. So
>it used up all the memory (500 MB) ,and the swap(virtual mem), and the whole
>machine hanged.
>Then today, I stopped the query, and waited for hours...
>When I rebooted, it took hours to come up, and the sqlsrvr runs with 50% CPU
>and huge memory (both). I believe its still rolling back the 'delete'
>transactions.
>Is there any way to clean up the resource, and start using the machine in a
>normal way? (Im also in the process of increasing the memory to atleast 2GB).
>Pl. note that I may not be able to open the EM or QA due to the underlying
>heavy load/recovery process(?)...
>Thanks a lot
>Philipus
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200507/1|||Can you open the SQL Server error log from the windows explorer? If the
database is still recovering you will see messages related to that in the
error log. I think you should just let the database recover.
Do you happen to have a backup that is good enough to replace the current
database?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Philipus via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:51A7C2ADFB58F@.SQLMonster.com...
I tried to delete some records from a table that has 10 million records
(delete <table> where xxx= 'yyy'). This does not have any matching index. So
it used up all the memory (500 MB) ,and the swap(virtual mem), and the whole
machine hanged.
Then today, I stopped the query, and waited for hours...
When I rebooted, it took hours to come up, and the sqlsrvr runs with 50% CPU
and huge memory (both). I believe its still rolling back the 'delete'
transactions.
Is there any way to clean up the resource, and start using the machine in a
normal way? (Im also in the process of increasing the memory to atleast
2GB).
Pl. note that I may not be able to open the EM or QA due to the underlying
heavy load/recovery process(?)...
Thanks a lot
Philipus
Message posted via http://www.sqlmonster.com|||Since I was not able to do anything in the machine, I just left it for
another day, and it came up after clearing the rollback. Now its normal. The
error log has a huge list of ' roll back...recovering...'
Thanks reddy!
philipus
Narayana Vyas Kondreddi wrote:
>Can you open the SQL Server error log from the windows explorer? If the
>database is still recovering you will see messages related to that in the
>error log. I think you should just let the database recover.
>Do you happen to have a backup that is good enough to replace the current
>database?
>I tried to delete some records from a table that has 10 million records
>(delete <table> where xxx= 'yyy'). This does not have any matching index. So
>it used up all the memory (500 MB) ,and the swap(virtual mem), and the whole
>machine hanged.
>Then today, I stopped the query, and waited for hours...
>When I rebooted, it took hours to come up, and the sqlsrvr runs with 50% CPU
>and huge memory (both). I believe its still rolling back the 'delete'
>transactions.
>Is there any way to clean up the resource, and start using the machine in a
>normal way? (Im also in the process of increasing the memory to atleast
>2GB).
>Pl. note that I may not be able to open the EM or QA due to the underlying
>heavy load/recovery process(?)...
>Thanks a lot
>Philipus
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200507/1

Machine hangs after SQL QA query

I tried to delete some records from a table that has 10 million records
(delete <table> where xxx= 'yyy'). This does not have any matching index. So
it used up all the memory (500 MB) ,and the swap(virtual mem), and the whole
machine hanged.
Then today, I stopped the query, and waited for hours...
When I rebooted, it took hours to come up, and the sqlsrvr runs with 50% CPU
and huge memory (both). I believe its still rolling back the 'delete'
transactions.
Is there any way to clean up the resource, and start using the machine in a
normal way? (Im also in the process of increasing the memory to atleast 2GB)
.
Pl. note that I may not be able to open the EM or QA due to the underlying
heavy load/recovery process(?)...
Thanks a lot
Philipus
Message posted via http://www.droptable.comAdditional Info:
This host has AntiVirus enabled. But I dont think the MDB etc. are excluded
from scanning. But I dont think the QA query mentioned would have anything t
o
do with AV scan! Does it? What kind of configuration does the AV needs so i
t
both does its work properly, but at the same time does not hinder my SQL
server work?
-philipus
Philipus wrote:
>I tried to delete some records from a table that has 10 million records
>(delete <table> where xxx= 'yyy'). This does not have any matching index. S
o
>it used up all the memory (500 MB) ,and the swap(virtual mem), and the whol
e
>machine hanged.
>Then today, I stopped the query, and waited for hours...
>When I rebooted, it took hours to come up, and the sqlsrvr runs with 50% CP
U
>and huge memory (both). I believe its still rolling back the 'delete'
>transactions.
>Is there any way to clean up the resource, and start using the machine in a
>normal way? (Im also in the process of increasing the memory to atleast 2GB
).
>Pl. note that I may not be able to open the EM or QA due to the underlying
>heavy load/recovery process(?)...
>Thanks a lot
>Philipus
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200507/1|||Can you open the SQL Server error log from the windows explorer? If the
database is still recovering you will see messages related to that in the
error log. I think you should just let the database recover.
Do you happen to have a backup that is good enough to replace the current
database?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Philipus via droptable.com" <forum@.droptable.com> wrote in message
news:51A7C2ADFB58F@.droptable.com...
I tried to delete some records from a table that has 10 million records
(delete <table> where xxx= 'yyy'). This does not have any matching index. So
it used up all the memory (500 MB) ,and the swap(virtual mem), and the whole
machine hanged.
Then today, I stopped the query, and waited for hours...
When I rebooted, it took hours to come up, and the sqlsrvr runs with 50% CPU
and huge memory (both). I believe its still rolling back the 'delete'
transactions.
Is there any way to clean up the resource, and start using the machine in a
normal way? (Im also in the process of increasing the memory to atleast
2GB).
Pl. note that I may not be able to open the EM or QA due to the underlying
heavy load/recovery process(?)...
Thanks a lot
Philipus
Message posted via http://www.droptable.com|||Since I was not able to do anything in the machine, I just left it for
another day, and it came up after clearing the rollback. Now its normal. The
error log has a huge list of ' roll back...recovering...'
Thanks reddy!
philipus
Narayana Vyas Kondreddi wrote:
>Can you open the SQL Server error log from the windows explorer? If the
>database is still recovering you will see messages related to that in the
>error log. I think you should just let the database recover.
>Do you happen to have a backup that is good enough to replace the current
>database?
>I tried to delete some records from a table that has 10 million records
>(delete <table> where xxx= 'yyy'). This does not have any matching index. S
o
>it used up all the memory (500 MB) ,and the swap(virtual mem), and the whol
e
>machine hanged.
>Then today, I stopped the query, and waited for hours...
>When I rebooted, it took hours to come up, and the sqlsrvr runs with 50% CP
U
>and huge memory (both). I believe its still rolling back the 'delete'
>transactions.
>Is there any way to clean up the resource, and start using the machine in a
>normal way? (Im also in the process of increasing the memory to atleast
>2GB).
>Pl. note that I may not be able to open the EM or QA due to the underlying
>heavy load/recovery process(?)...
>Thanks a lot
>Philipus
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200507/1