?
Solved

Compact MSDE Database

Posted on 2004-10-16
12
Medium Priority
?
1,730 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
11 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
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
Suggested Courses

621 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