re: What do I have to do when my MSDE reaches the limit of 2gb? I've tried to clear the database but there's no space available to execute that. --------------------------------- Fabio
Here is a list of line commands. Items (1-4) comprise the procedure I use to resolve the issue you have described. HTH Jim McFarlen NOTE: You must be logged in with local administrator privileges on the MSDE or SQL machine or the following procedures will not work. Before performing any maintenance on your database, you must first shutdown the Event Collector to prevent damage to the database. Restart the Event Collector only after you have completed the maintenance. To PURGE the ISSED, do the following: Open a command prompt from the machine running MSDE. Type osql -E and press enter; 1> is displayed. Type use issed and press enter; 2> is displayed. Type exec iss_purgeevents '1/1/2002 00:00:00','12/31/2002 00:00:00',100 and press enter; 3> is displayed. NOTE: The date and time will need to be modified so that the command will only clear a specific date range. The command above will clear anything entered in the database between January 1, 2002 at midnight, and December 31, 2002 at midnight. Type go and press enter; 1> is displayed. The specified event range has now been cleared from the ISSED database. 1) To CLEAR the ISSED, do the following: Open a command prompt from the machine running MSDE. Type osql -E and press enter; 1> is displayed. Type use issed and press enter; 2> is displayed. Type exec iss_truncateevents and press enter; 3> is displayed. Type go and press enter; 1> is displayed. All events have now been cleared from the ISSED database. 2) To SHRINK the ISSED, follow these steps: Open a command prompt from the machine running MSDE. Type osql -E and press enter; 1> is displayed. Type dbcc shrinkdatabase (issed,0) and press enter, 2> is displayed. Type go and press enter, 1> is displayed. The ISSED data file has been reduced to it's minimum size. NOTE: When using the MSDE on a system with limited disk space, it may be a good idea to leave the data file at it's maximum size. This will ensure that other applications do not allocate the space that is need for the data file to grow to it's maximum size. 3) To INCREASE THE MAXIMUM SIZE of the ISSED, do the following: Open a command prompt from the machine running MSDE. Type osql -E and press enter; 1> is displayed. Type alter database issed modify file (name=issdata, maxsize=2gb) and press enter; 2> is displayed. Type go and press enter; 1> is displayed. The maximum size of the ISSED database has been increased to two gigabytes. NOTE: This is the upper limit for the size of an MSDE database. SQL Server provides a more robust database, and allows the database to grow to fill the disk, if specified. Under no circumstance should you increase the maximum size of an MSDE database to more than two gigabytes. Data corruption and loss will occur. 4) To INCREASE THE MAXIMUM SIZE of the ISSED transaction log, do the following: Open a command prompt from the machine running MSDE. Type osql -E and press enter; 1> is displayed. Type alter database issed modify file (name=isslog, maxsize=2gb) and press enter; 2> is displayed. Type go and press enter; 1> is displayed. The maximum size of the ISSED transaction log has been increased to two gigabytes. NOTE: This is the upper limit for the size of an MSDE database. SQL Server provides a more robust database, and allows the log to grow to fill the disk, if specified. Under no circumstance should you increase the maximum size of an MSDE database to more than two gigabytes. Data corruption and loss will occur. To BACKUP the ISSED database, follow these steps: NOTE: Please ensure that the directory "c:\program files\microsoft sql server\mssql\backup\" exists before beginning! Open a command prompt from the machine running MSDE. Type osql -E and press enter; 1> is displayed. Type use master and press enter; 2> is displayed. Type exec sp_addumpdevice 'disk', 'issedbackup', 'c:\program files\microsoft sql server\mssql\backup\issed.bak' and press enter; 3> is displayed. Type backup database issed to issedbackup and press enter; 4> is displayed. Type go and press enter; 1> is displayed. The ISSED database has been backed up. To RESTORE the ISSED from backup, follow these steps: Open a command prompt from the machine running MSDE. Type osql -E and press enter; 1> is displayed. Type restore database issed from issedbackup and press enter; 2> is displayed. Type go and press enter; 1> is displayed. The ISSED database has been restored from backup. To CHANGE the EventCollector or other SQL password, follow these steps: Open a command prompt from the machine running MSDE. Type the following: EXEC sp_password NULL, 'password', 'EventCollector' (where 'password' is the new password you would like to set and 'EventCollector' can be interchanged with any account you which to set a new password on) _______________________________________________ ISSForum mailing list [EMAIL PROTECTED] TO UNSUBSCRIBE OR CHANGE YOUR SUBSCRIPTION, go to https://atla-mm1.iss.net/mailman/listinfo
