Cleaning an MSDE database...

L

lidds

Well-known member
Joined
Nov 9, 2004
Messages
210
If I wanted to clean-up my MSDE database, how would I do this? Would I use the Sql VACUUM command which I have found on the internet.

http://www.krasline.ru/Library/postgress/manual/sql-vacuum-1.htm

Has anyone done something like this in vb.net (some code would be great)? I also presume it would be best to make a back-up copy the database before doing this clean-up? Has anyone encountered problems or errors I should look out for?

Cheers

Simon
 
What do you mean by
clean-up my MSDE database
Click to expand...
?
If you simply want to remove freespace then look at the DBCC ShrinkDB command, if you wish to check for potential errors in the db structures then something like DBCC checkdb may be more useful.
 
The DBCC ShrinkDB seems to be what I need. Ive just read up about the command on the internet, and it seems that this command will only work on a database that one individual is working on (logical) I was wondering if yourself / anyone has got some vb.net code to achieve the shrinking of a DB, locking of the DB so as to acheive shrinking and maybe error trapping??

Cheers

Simon
 
Something like
Code: 
        Dim conn As New SqlClient.SqlConnection("Data Source=localhost;Initial Catalog=master;Integrated Security=true")
        Dim cmd As New SqlClient.SqlCommand("DBCC Shrinkdatabase (Northwind)", conn)

        Try
            conn.Open()
            cmd.ExecuteNonQuery()
        Catch ex As SqlClient.SqlException
            handle error here
            MessageBox.Show(ex.Message)
        Finally
            conn.Close()
        End Try
should do the trick. Also if you are using SQL 2000 (not sure about 7) you need to use shrinkdatabase not shrinkdb like I said earlier ;)
 
Back
Top