shrink SQL database in VB.net

[qfchen]

Hi

I need to backup sql database, below is my VB code to do backup, shrink and delete. But I have problem to shrink the database. Need your help to find what's wrong for the shrink potion. the error happened when execute CmdShrink.ExecuteNonQuery(). thanks

Dim Cnt As Integer
        Dim CmdBackup As New SqlClient.SqlCommand("backup database EM_AWARE to disk= 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\EMAwareBackup.bak'", Me.myConnection)
        CmdBackup.CommandTimeout = 3600
        Dim CmdShrink As New SqlClient.SqlCommand("shrink database EM_AWARE" me.myConnection)
        Dim CmdDelete = New SqlCommand("DELETE from EM_AWARE_20kV", myConnection)
 
        Try
            Me.myConnection.Open()
            CmdBackup.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox("error in frmESDMonitor, backup database:" & ex.Message.ToString)
        End Try
        Me.myConnection.Close()
 
        Cursor.Current = Cursors.WaitCursor
        Try
            Me.myConnection.Open()
            CmdShrink.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox("error in frmESDMonitor, shrink database:" & ex.Message.ToString)
        End Try
        Me.myConnection.Close()
 
        MsgBox("Going to delete all data")
        Try
            Me.myConnection.Open()
            myCommand.CommandTimeout = 1800
            Cnt = myCommand.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox("error in frmESDMonitor, delete database:" & ex.Message.ToString)
        End Try
        Me.myConnection.Close()
 
 
 

[PsychoCoder]

Take a look at using DBCC SHRINKFILE, like this

Dim CmdShrink As New SqlClient.SqlCommand("DBCC SHRINKFILE(EM_AWARE)" me.myConnection)
 

Where EM_AWARE is the name of the database you want to shrink. You'll also want to shrink the log file (most are named DBNAME_Log) where DBNAME is the name of your database, in your case it would probably be EM_AWARE_Log so after you shrink the db itself then shrink the log file

Dim CmdShrink As New SqlClient.SqlCommand("DBCC SHRINKFILE(EM_AWARE_Log)" me.myConnection)
 

[qfchen]

thank you very much, it works. the actual commands are:

Dim CmdDeleteRT As New SqlClient.SqlCommand("DELETE from EM_AWARE_RT", myConnection)
        CmdDeleteRT.CommandTimeout = 1800
 
        Dim CmdShrink As New SqlCommand("DBCC SHRINKDATABASE(EM_AWARE)", Me.myConnection)
        CmdShrink.CommandTimeout = 1800