Shrink MASTER.DAT in SQL 6.5?

[MartijnM]

Is there a way to shrink the master database in SQL 6.5? If I try to edit the options for master.dat, I can't set the (greyed out) option for 'Single Use', yet without it I can't shrink it. Stopping the server obviously doesn't help, since I can't connect to the database that way...

Any suggetstions are appreciated!

MM



Martijn Middelplaats
CCDA, CCNA, MCSA, MCSE
martijn@middelplaats.net

 

[tlbroadbent]

SQL Server 6.5 doesn't have a "shrink database" capability. You have to rebuild master to change the device size.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[MartijnM]

Er....yes I can shrink databases in SQL 6.5. It shows up as an option in the Edit Database screen....

I can also use the featue on other databases on this server, as long as I configure them for Single User.

So the problem is, that I can not figure out how to set that option for the master database. Is there a way?

Tnx,

MM

Martijn Middelplaats
CCDA, CCNA, MCSA, MCSE
martijn@middelplaats.net

 

[tlbroadbent]

Sorry, but you cannot shrink SQL Server 6.5 database files. File size is not dynamic in SQL Server 6.5. That feature was not introduced until SQL Server 7.0.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[MartijnM]

OK. I am officially confused now.

I am more than willing to accept what you're telling me, if it wasn't for the fact that I am looking at this very feature, on my screen, right now. Would it help if I send you the screen shots? I am by no means an accomplished SQL person, but I have trouble understanding what you're telling me, if what I see in the management tool is so obviously contradictory.

I appreciate your help in this matter.



Martijn Middelplaats
CCDA, CCNA, MCSA, MCSE
martijn at middelplaats dot net

 

[tlbroadbent]

Are you using a SQL Server 7 or 2000 Enterprise Manager to administer the SQL 6.5 server?

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[MartijnM]

Terry,

No, we're using Enterprise Manager version 6.50.201 on a SQL 6.5 server.

Would you like me to post the screenshots somewhere?

Tnx,

M

Martijn Middelplaats
CCDA, CCNA, MCSA, MCSE
martijn at middelplaats dot net

 

[tlbroadbent]

I apologize. I have a faulty memory. I just pulled out my old SQL Server 6.5 manuals and read about DBCC SHRINKDB and using Enterprise Manager to shrink a database. The refresher course was helpful.

The manual states that you must use DBCC SHRINKDB to shrink the master or tempdb databases. In addition, you must start the server in Single user mode. Use the command line -m parameter. (Reference: SQL Server Administrator's Companion, page 131)

Again, I'm sorry for the delay in finding the correct answer. I haven't used SQL Server 6.5 for several years and could not recall shrinking databases.

Version 6.5's DBCC SHRINKDB was replaced by DBCC SHRINKDATABASE and DBCC SHRINKFILE in SQL 7 and higher. SQL Server 7 and higher provide a much greater degree of control than available in SQL Server 6.5. In addition, growth and shrinkage can be handled automatically by SQL Server in these later versions. The changes were so significant that it was like new functionality.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[MartijnM]

Terry, you're an absolute gem. And there's no need to apologize whatsoever; I am thrilled that you took the time to check this out and came up with the answer. It is very much appreciated!

I will try your suggestion and post back the results...

Thanks again!

Martijn


Martijn Middelplaats
CCDA, CCNA, MCSA, MCSE
martijn at middelplaats dot net