Need Help on SQL Server MSDE 2000 Desktop Engine.

Posted September 21, 2011

Hi Sujeet,

How are you ? Your website looks very impressive and keep up your good work.

I just need some solution from you for one SQL server related issue. One of our application is running on MSDE 2000 Desktop Engine which has a limit of 2 GB DB size. Now this size has reached the upper limit and hence the application is failing often. I found that sysjobhistory table of MSDB database has 33 lakhs rows.

So will truncating all those rows reduce the size of table? How to do that.....?

But still some of my constraints are as follows:

1. I don't have SQL server enterprise manager installed on the local server. When I try to connect from remote server and try to delete the rows, it was failing due to timeout issue.

2. I also try to install SQL editor in my desktop and try to connect to the DB but i failed because of firewall issues.

So I need a method or command to truncate the data to be executed in the local server. I tried with OSQL command prompt, but I was not able to succeed in that also...!!

So please help me to get rid of this issue....thanks a lot.

Regards,
Raghunath.S.D
System Administrator
Kingdom of Bahrain

3 comments

Posted in UserQueries

Tagged as msde 2000 desktop engine

Comments

Sujeet September 22, 2011

Hi Raghunath,
Thanks for putting up your question to me. As you said that the database size has reached its upper limit of 2 GB, it seems that you should consider upgrading and going for another SQL Server edition. Even shrinking may not help you in long run as a live application would keep on putting data and increase the DB size. So, moving to another SQL Server edition would need a careful planning though, let me know if you intend to do so and I can give you some tips around that too.
Now back to your question, deleting or shrinking MSDB database is not that straight forward and is bit tricky. As a temporary resolution, you can go for deleting the 30 lakh rows and shrink the MSDB database. Simply deleting the rows is not going to help, you need to shrink the database as well after that. For the scripts and how to do this, you can check my article on “How To Shrink a large MSDB database” at http://sqldbaguru.com/shrinkmsdb
Connect to SQL Server Desktop Engine (MSDE 2000)
To connect to MSDE 2000, follow these steps:
1. Open a command prompt on the computer that is hosting the instance of MSDE 2000 to which you want to connect.
2. Type the following command, and then press ENTER:
osql -E
This connects you to the local, default instance of MSDE 2000 by using Windows Authentication.
To connect to a named instance of MSDE 2000, type:
osql -E -S servername\instancename
3. When the prompt appears as 1> then it indicates that osql is started. Now, you can interactively enter the Transact-SQL statements and the results appear on the command prompt line.

This should help you!

Regards,
Sujeet

Raghunath.S.D September 26, 2011

Hi Sujeet,

Thanks for your support...it worked very well.

But I was end up with other issue, while deleting rows, the transactional log filled up the drive and I tried to cancel the transaction.

Now the problem is we have very less space left in the database drive and am not able to connect to the MSDB database.

So I am not able to truncate transactional logs. Recreation of MSDB will be result in loss of data.

Please provide your suggestions for this issue..

Thanks,
Raghunath.S.D

Sujeet September 26, 2011

Hi Raghu,
First of all, I would like to know the Recovery Mode of the msdb database. Please change it to SIMPLE, if it is in other mode.
To check the recovery mode:
SELECT name, databasepropertyex (name,'Recovery') FROM master..sysdatabases

To set the recovery mode to simple:
USE master;
ALTER DATABASE database_name SET RECOVERY SIMPLE;

Try shrinking the msdb transaction log file:
USE msdb;
GO

SELECT file_id, name
FROM sys.database_files;
GO
DBCC SHRINKFILE (2, TRUNCATEONLY);

Let me know if this resolves your issue.

Thanks,
Sujeet

Displaying all 3 comments

SQL DBA Guru

“Your issues, Our solutions”

Need Help on SQL Server MSDE 2000 Desktop Engine.

Comments

Add comment

Get the Expert's Advice.