Cleaning an MSDE database...

[lidds]

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

[PlausiblyDamp]

What do you mean by

clean-up my MSDE database

?

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.

[lidds]

The 'DBCC ShrinkDB ' seems to be what I need. I've 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

[PlausiblyDamp]

Something like

       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 ;)