Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Compact MSDE Database

Posted on 2004-10-16
12
Medium Priority
?
1,724 Views
Last Modified: 2012-06-27

Hi,

I have a local MSDE database, which I access via Microsoft Access 2003.

The problem is that its size is getting quite large. (~ 5MB).

I have deleted all entries from the database, yet it remained the same size - Is there somehow I can company the database (same as I could do in Access) ?

Thanks,

Agmon.
0
Comment
Question by:l_agmon
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
12 Comments
 
LVL 6

Assisted Solution

by:curtis591
curtis591 earned 200 total points
ID: 12329440
From the books online

DBCC SHRINKFILE
Shrinks the size of the specified data file or log file for the related database.

Syntax
DBCC SHRINKFILE
    ( { file_name | file_id }
        { [ , target_size ]
            | [ , { EMPTYFILE | NOTRUNCATE | TRUNCATEONLY } ]
        }
    )

This example shrinks the size of a file named DataFil1 in the UserDB user database to 7 MB.

USE UserDB
GO
DBCC SHRINKFILE (DataFil1, 7)
GO

0
 
LVL 12

Expert Comment

by:pique_tech
ID: 12329487
But how is a user who uses MSDE through Access supposed to implement your suggestion without query analyzer?  Can one issue those kinds of commands via ADO or ADOX, or alternately, does MSDE install the command line isql (or osql--I never use it so I can't remember exactly what it is for SQL 2000).
0
 
LVL 6

Expert Comment

by:curtis591
ID: 12329815
You should be able to run that through ADO.  All you would need to do is execute the dbcc command.  The use command is just switching to the proper database.  I would assume you would be connected through ADO to the database you want to do this on.

 I have always just Enterprise Manager and query analyzer to connect to the MSDE server so I am not sure if it installs with the command prompt tools.  
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:l_agmon
ID: 12330283
Hmmm...

I don't have Query Analyzer nor Enterprise Manager installed. (They are not free for download).

Is there anoter way in whcih I can run that code?
0
 
LVL 34

Expert Comment

by:arbert
ID: 12330417
"You should be able to run that through ADO.  "

Run it as a passthru query in the Access query builder....Or use OSQL from the command prompt...
0
 
LVL 12

Accepted Solution

by:
pique_tech earned 200 total points
ID: 12330445
Here's the Microsoft reference to the appropriate SQL command, DBCC SHRINKDATABASE:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_3pd1.asp

This little bit of code *seems* to do what you want, but I cannot absolutely confirm as all my MSDE databases are already quite small.

Public Sub DBCCMSDE(ServerNamePath As String, DatabaseName As String)

    Dim cmd As ADODB.Command
    Set cmd = New ADODB.Command
   
    cmd.ActiveConnection = "Provider=SQLOLEDB;Data Source=" & ServerNamePath & ";" & _
                            "Initial Catalog=" & DatabaseName & ";" & _
                            "Trusted_Connection=Yes;"
    cmd.CommandType = adCmdText
    cmd.CommandText = "DBCC SHRINKDATABASE (" & DatabaseName & ", 0, TRUNCATEONLY)"
    cmd.Execute (False)
    MsgBox "Seems to have worked..."
    Set cmd = Nothing

End Sub
0
 
LVL 34

Assisted Solution

by:arbert
arbert earned 100 total points
ID: 12333706
DBCC Shrinkdatabase, though documented to do so, rarely shrinks the logfiles properly.  You're usually better off to BACKUP LOG with no_truncate and then issue a DBCC SHRINKFILE.
0
 

Author Comment

by:l_agmon
ID: 12580250
Hi All,

I did not manage to compact the database as suggested above, hence I do not really know who to award points to.

Regards,

Agmon.
0
 
LVL 34

Expert Comment

by:arbert
ID: 12580265
You should reply to the question and let us know it didn't work so we can assist you further.  There is no way we could know your problem was or wasn't solved without you telling us....
0
 

Author Comment

by:l_agmon
ID: 12580436
You are right. My apologies.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

664 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question