Solved

Compact MSDE Database

Posted on 2004-10-16
12
1,708 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 100 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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 34

Expert Comment

by:arbert
ID: 12330425
0
 
LVL 12

Accepted Solution

by:
pique_tech earned 100 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 50 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

735 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