Solved

Compact MSDE Database

Posted on 2004-10-16
12
1,707 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
  • 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL STANDARD CORE 7 31
MS SQL: Return all results in a single row separated by commas 1 26
how to just get time from a date 6 32
sql server query 12 25
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

840 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