Clear SQl server Logs by run xp_cmdshell

how i can clear sql server logs by run xp_cmdshell stored procedure.
(my user in sql server is a admin).
i don't want use enterprise manager . i want write a query and use at xp_cmdshell .
befor thx.What logs are you talking about... SQLServer has error logs that you can cycle, the server keeps 5 logs active.

Or for the Transaction logs, you can use TQL to backup the transaction logs and truncate.

But do you use the Transaction logs, ie are you using them for Disaster recovery or such...
You can also turn off the transaction logs or at least turn them down. This is done buy changeing the databases logging method from Full (All Transactions Logged), to Bulk (Only bulk loading transactions), or Simple (No Logging).

There are also artilcles on SQLServercentral.com and MSDN on useing TSQL to shrink the logs and reduce wasted space.

Remember that All enterprise manager is is a front end for TSQL Commands to the database, all commands can be run from code.

Clean Log files

Hi All,
I have problem with my logs files, they are going so much especially when I
import data.
The best way that I found to clean the log files is:
1. Detach database
2. Delete the log file from the data folder
3. Attach database
Obviously I can't do this during the day until nobody is using the database.
I have 16 databases.
Can I do this using an script?
Any help will be appreciate... tks in advance.
JFB> The best way that I found to clean the log files is:
> 1. Detach database
> 2. Delete the log file from the data folder
> 3. Attach database
That may well be the best way to corrupt your data. The best way to
control log size is to choose the right recovery model, allocate a
sensible size to start with and then take regular Log and Database
backups as appropriate. If you are still in development then perhaps
database backups are sufficienct in which case choose the Simple
Recovery model.
David Portas
SQL Server MVP
--
http://support.microsoft.com/?kbid=873235|||Much of http://www.aspfaq.com/2446 applies to all databases, not just
tempdb.
Also see http://www.aspfaq.com/2471
"JFB" <help@.jfb.com> wrote in message
news:Od$WBFTjFHA.2156@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I have problem with my logs files, they are going so much especially when
> I import data.
> The best way that I found to clean the log files is:
> 1. Detach database
> 2. Delete the log file from the data folder
> 3. Attach database
> Obviously I can't do this during the day until nobody is using the
> database. I have 16 databases.
> Can I do this using an script?
> Any help will be appreciate... tks in advance.
> JFB
>|||By "cleaning" do you mean truncating the log or reducing the physical size?
If so, first check the recovery model set for your database.
If logs are not required to be maintained, then you can simply issue a
BACKUP LOG statement with NO_LOG option. To reduce the physical file size,
use DBCC SHRINKFILE. Details of these commands with examples can be found in
SQL Server Books Online.
Anith|||If you are not using BCP.EXE or the BULK INSERT statement to import your
data, then you should consider this. In SQL Server Books Online, read the
article titled "Logged and Minimally Logged Bulk Copy Operations".
Basically, the requirements for non-logged bulk copy operations are:
- The recovery model is simple or bulk-logged.
- The target table is not being replicated.
- The target table does not have any triggers.
- The target table has either 0 rows or no indexes.
- The TABLOCK hint is specified.
If this is an OLTP database, you can use the ALTER DATABASE.. statment to
switch between different recovery models as needed.
You may also want to consider dropping indexes (especially the clustered
index) before the import and re-creating them again afterward. This will
also have the affect of preventing index fragmentation and increasing the
load performance.
If this database is a 24x7 transactional system with many users, then you
may want to re-consider the process by which you import new data and avoid
massive import operations all together.
"JFB" <help@.jfb.com> wrote in message
news:Od$WBFTjFHA.2156@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I have problem with my logs files, they are going so much especially when
> I import data.
> The best way that I found to clean the log files is:
> 1. Detach database
> 2. Delete the log file from the data folder
> 3. Attach database
> Obviously I can't do this during the day until nobody is using the
> database. I have 16 databases.
> Can I do this using an script?
> Any help will be appreciate... tks in advance.
> JFB
>|||Hi,
Looks like you do not want the Transaction log backup. There are 2 ways to
clear the Log file online.
1. Take the transaction log backup and then shrink the LDF file.
Script:
Backup log <dbname> to disk='c:\backup\dbname.trn'
go
use dbname
go
dbcc shrinkfile('logical_ldf_name',size_to_sh
rink_in_MB)
2. Truncate the Transaction log and Shrink.
Script:
Backup log <dbname> with Truncate_only
go
use dbname
go
dbcc shrinkfile('logical_ldf_name',size_to_sh
rink_in_MB)
Thanks
Hari
SQL Server MVP
"JFB" <help@.jfb.com> wrote in message
news:Od$WBFTjFHA.2156@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I have problem with my logs files, they are going so much especially when
> I import data.
> The best way that I found to clean the log files is:
> 1. Detach database
> 2. Delete the log file from the data folder
> 3. Attach database
> Obviously I can't do this during the day until nobody is using the
> database. I have 16 databases.
> Can I do this using an script?
> Any help will be appreciate... tks in advance.
> JFB
>

Checksum problem on system database

Hey there,
we're having an issue with the SQL (MSDE) database that sharepoint uses.
The service starts and immediately stops. In the logs (in dutch) there's
an error: "Het checksumbestand van de systeemdatabase heeft een
ongeldige ondertekening." freely translated it says "The checksumfile
from the system database has a invalid signature".
Anyone know how to solve this or where start searching? Does SQL have
something like eseutil I might try to repair the database?
TIA
hi,
Freaky wrote:
> Hey there,
> we're having an issue with the SQL (MSDE) database that sharepoint
> uses. The service starts and immediately stops. In the logs (in
> dutch) there's an error: "Het checksumbestand van de systeemdatabase
> heeft een ongeldige ondertekening." freely translated it says "The
> checksumfile from the system database has a invalid signature".
> Anyone know how to solve this or where start searching? Does SQL have
> something like eseutil I might try to repair the database?
if you have a valid master database backup, you can restore it as indicated
in http://msdn2.microsoft.com/en-us/library/Aa173557(SQL.80).aspx and
http://msdn2.microsoft.com/en-us/library/Aa176749(SQL.80).aspx ... if you
don't, you are in trouble, as MSDE does not provide the Rebuild utility
(rebuildm.exe) and the scripts to re-generate it.. if this is the case, you
have to uninstall and reinstall the MSDE instance.. you can or course keep
your user's databases files an re-attach them once re-installed..
regards
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
-- remove DMO to reply
|||Hey Andrea,
thx for the re. I was already afraid I would have to reinstall. It's the
instance from sharepoint. We do maintenance at a lot of customers and
some have full SQL versions so perhaps I could use the rebuildm tool
there. But reinstalling sharepoint and replacing the database STS and
STS_Config might be faster as I think those are the only ones that matter.
Anyways thanks again
Andrea Montanari wrote:
> hi,
> Freaky wrote:
> if you have a valid master database backup, you can restore it as indicated
> in http://msdn2.microsoft.com/en-us/library/Aa173557(SQL.80).aspx and
> http://msdn2.microsoft.com/en-us/library/Aa176749(SQL.80).aspx ... if you
> don't, you are in trouble, as MSDE does not provide the Rebuild utility
> (rebuildm.exe) and the scripts to re-generate it.. if this is the case, you
> have to uninstall and reinstall the MSDE instance.. you can or course keep
> your user's databases files an re-attach them once re-installed..
> regards

Checking the integrity of the FT catalog/index

What would be an efficient way to check for the integrity of a FT
catalog or index in SQL 2000? Beside monitoring the event logs, I'm
planning to write a script to parse out a string of text from a text
column of a random row from a table has FT indexes, then go back and do
a FT search on that string/words to make sure the catalog is ok.
SQL 2005 has cidump, does SQL 2000 has any equivalent utility?
Thanks,
Hai
Hai,
The MSSearch service does its own internal integrity checking by design, so
little to no addition checking is normally required. However, I would
recommend that you monitor the free space, memory and cpu usage via the
"Microsoft Search" Performance counters. The following blog entry has links
to the more common SQL FTS issues.
SQL Server 2000 Full-Text Search Resources and Links
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.entry
323739 "INF: SQL Server 2000 Full-Text Search Deployment White Paper" will
have more information about the MSSearch perfmon counters.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
<tran.hai@.gmail.com> wrote in message
news:1130170118.779530.104390@.z14g2000cwz.googlegr oups.com...
> What would be an efficient way to check for the integrity of a FT
> catalog or index in SQL 2000? Beside monitoring the event logs, I'm
> planning to write a script to parse out a string of text from a text
> column of a random row from a table has FT indexes, then go back and do
> a FT search on that string/words to make sure the catalog is ok.
> SQL 2005 has cidump, does SQL 2000 has any equivalent utility?
> Thanks,
> Hai
>
|||Thanks John!