Solved

Compact MSDE Database

Posted on 2004-10-16
12
1,701 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
 

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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Restore Procedure question 4 31
ms sql stored procedure 22 77
Complex SQL 10 34
MSSQL Frequency of Years From Days Field 2 16
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.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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 setup several different housekeeping processes for a SQL Server.

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now