Increase in Database size after restoration

we have taken a production database back up of size 1.8 GB.
we restored the database on a standalone system and the size of the database
was same.
We executed some scripts through which some records got inserted.
After the inserts the database size has drasticallly increased to 5.8 GB.
The database whose size is 5.8 GB was due to the increase in reservedspace
and unused spaces of the tables where in the records got inserted.
Then we exported the database through DTS and imported the data and shruk
the database to which the size of the database got reduced to 2 GB.
I wanted to know what is the reason for the increase in database size when
we executed the insert scripts. And how to avoid it. Or any approach by which
we can find the solution.
Read this article
http://www.sql-server-performance.co...e_settings.asp
"CUBak_DBSize" <CUBak_DBSize@.discussions.microsoft.com> wrote in message
news:3D114A81-F276-4FB9-A878-6C6712058129@.microsoft.com...
> we have taken a production database back up of size 1.8 GB.
> we restored the database on a standalone system and the size of the
database
> was same.
> We executed some scripts through which some records got inserted.
> After the inserts the database size has drasticallly increased to 5.8 GB.
> The database whose size is 5.8 GB was due to the increase in
reservedspace
> and unused spaces of the tables where in the records got inserted.
> Then we exported the database through DTS and imported the data and shruk
> the database to which the size of the database got reduced to 2 GB.
> I wanted to know what is the reason for the increase in database size when
> we executed the insert scripts. And how to avoid it. Or any approach by
which
> we can find the solution.
>

Increase in Database size after restoration

we have taken a production database back up of size 1.8 GB.
we restored the database on a standalone system and the size of the database
was same.
We executed some scripts through which some records got inserted.
After the inserts the database size has drasticallly increased to 5.8 GB.
The database whose size is 5.8 GB was due to the increase in reservedspace
and unused spaces of the tables where in the records got inserted.
Then we exported the database through DTS and imported the data and shruk
the database to which the size of the database got reduced to 2 GB.
I wanted to know what is the reason for the increase in database size when
we executed the insert scripts. And how to avoid it. Or any approach by whic
h
we can find the solution.Read this article
http://www.sql-server-performance.c...se_settings.asp
"CUBak_DBSize" <CUBak_DBSize@.discussions.microsoft.com> wrote in message
news:3D114A81-F276-4FB9-A878-6C6712058129@.microsoft.com...
> we have taken a production database back up of size 1.8 GB.
> we restored the database on a standalone system and the size of the
database
> was same.
> We executed some scripts through which some records got inserted.
> After the inserts the database size has drasticallly increased to 5.8 GB.
> The database whose size is 5.8 GB was due to the increase in
reservedspace
> and unused spaces of the tables where in the records got inserted.
> Then we exported the database through DTS and imported the data and shruk
> the database to which the size of the database got reduced to 2 GB.
> I wanted to know what is the reason for the increase in database size when
> we executed the insert scripts. And how to avoid it. Or any approach by
which
> we can find the solution.
>

Increase in Database size after restoration

we have taken a production database back up of size 1.8 GB.
we restored the database on a standalone system and the size of the database
was same.
We executed some scripts through which some records got inserted.
After the inserts the database size has drasticallly increased to 5.8 GB.
The database whose size is 5.8 GB was due to the increase in reservedspace
and unused spaces of the tables where in the records got inserted.
Then we exported the database through DTS and imported the data and shruk
the database to which the size of the database got reduced to 2 GB.
I wanted to know what is the reason for the increase in database size when
we executed the insert scripts. And how to avoid it. Or any approach by which
we can find the solution.Read this article
http://www.sql-server-performance.com/database_settings.asp
"CUBak_DBSize" <CUBak_DBSize@.discussions.microsoft.com> wrote in message
news:3D114A81-F276-4FB9-A878-6C6712058129@.microsoft.com...
> we have taken a production database back up of size 1.8 GB.
> we restored the database on a standalone system and the size of the
database
> was same.
> We executed some scripts through which some records got inserted.
> After the inserts the database size has drasticallly increased to 5.8 GB.
> The database whose size is 5.8 GB was due to the increase in
reservedspace
> and unused spaces of the tables where in the records got inserted.
> Then we exported the database through DTS and imported the data and shruk
> the database to which the size of the database got reduced to 2 GB.
> I wanted to know what is the reason for the increase in database size when
> we executed the insert scripts. And how to avoid it. Or any approach by
which
> we can find the solution.
>

increase in CPU usage on lock and unlock of the system

Ours is a windows based application.
When we open the application the CPU usage is 0% and the Memory Usage
is 54,324Kb
When I open a specific form in a module, the CPU usage is 0% and the
Memory Usage increases accordingly (67,730 Kb).
Now when I lock and unlock the System the CPU usage increases to 50%
with the Memory Usage being 67,730 kb.
I have made use of a .Net memory profiler and had taken snapshots
before locking and unlocking the system.
The following is the result after comparing both the versions.
NameSpace Name Total New Removed Delta Total
Max Min Delta
System WeakRefrence 1681 613 0 613 26896
16 16 9808
System.Reflection RunTimeMethodInfo 1229 8
0 8 29496 24 24 192
System.Windows.Forms NativeMethods.TracKMOU.. 10
5 0 5 240 24 24 120
System Runtimetype 2725 4 0 4 4360
16 16 64
System String 37232 5 1 4 2256 102456
18 458
Can anybody let me know the reason for the increase in CPU usage and
how I can overcome this.
And why is there a drastic increase in the number of instances for
WeakReference class when the operation is just locking and unlocking
and nothing to do with the application.
Thanks in advance
sowmyaIs this question related to SQL Server?. If so, please explain what you
mean by locking/unlocking. Perhaps you intended to post to a .Net forum.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<sowmya.rangineni@.gmail.com> wrote in message
news:1182142975.732292.25730@.m36g2000hse.googlegroups.com...
> Ours is a windows based application.
> When we open the application the CPU usage is 0% and the Memory Usage
> is 54,324Kb
> When I open a specific form in a module, the CPU usage is 0% and the
> Memory Usage increases accordingly (67,730 Kb).
>
> Now when I lock and unlock the System the CPU usage increases to 50%
> with the Memory Usage being 67,730 kb.
>
> I have made use of a .Net memory profiler and had taken snapshots
> before locking and unlocking the system.
>
> The following is the result after comparing both the versions.
>
> NameSpace Name Total New Removed Delta Total
> Max Min Delta
> System WeakRefrence 1681 613 0 613 26896
> 16 16 9808
> System.Reflection RunTimeMethodInfo 1229 8
> 0 8 29496 24 24 192
> System.Windows.Forms NativeMethods.TracKMOU.. 10
> 5 0 5 240 24 24 120
> System Runtimetype 2725 4 0 4 4360
> 16 16 64
> System String 37232 5 1 4 2256 102456
> 18 458
>
> Can anybody let me know the reason for the increase in CPU usage and
> how I can overcome this.
>
> And why is there a drastic increase in the number of instances for
> WeakReference class when the operation is just locking and unlocking
> and nothing to do with the application.
> Thanks in advance
> sowmya
>

Incorrect syntax near 'HelloWorld'

Hello,

I'm testing a CLR SP in SQL Server 2005 using Visual Basic .NET Express

The code is :

Imports System.Data.SqlServer
Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlClient
Public Class SQLCLR
Public Shared Sub HelloWorld()
SqlContext.Pipe.Send("Hello World from SQLCLR !!")
End Sub
End Class

I built the project and copy the HelloWorld.dll to a local dir.
Then I registed the assembly in SQL SERVER:

create assembly HelloWorld from 'd:\xi\HelloWorld.dll' with permission_set = safe

This is successfull but when I execute:

create procedure HelloWorld as external name HelloWorld.SQLCLR.HelloWorld

Msg 6505, Level 16, State 1, Procedure HelloWorld, Line 1
Could not find Type 'SQLCLR' in assembly 'HelloWorld'.

I don't know what's happening since the name of the class is right (SQLCLR)

Any advice would appreciated...

I am guessing that you have used Visual Studio 2005 to develop your assembly. Visual Studio adds a default namespace and you need to incorporate that in the external name.
(I think the default namespace is same as the name of the assembly) Try:

create procedure HelloWorld as external name
HelloWorld.[HelloWorld.SQLCLR].HelloWorld

Look at project properties in VS and see if there is a default namespace. Either remove that or add the namespace in your external name.

Hope that helps.

Thanks,
-Vineet.

Incorrect syntax near 0. Where is the 0 at??

I am not sure how to fix this??

Imports

System.Data

Imports

System.Web.UI.WebControls.FormView

Imports

System.Data.SqlClient

Partial

Class AssignmentInherits System.Web.UI.PageProtectedSub UpdateButton_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)Handles UpdateButton.ClickDim empIDAsIntegerDim nbrIDAsIntegerDim equidAsIntegerDim UpdateQueryDim empequipidAs SqlCommandDim mySQLConnectionAsString = System.Configuration.ConfigurationManager.ConnectionStrings("DBConnectionString").ToStringDim myConnectionAsNew SqlConnection(mySQLConnection)

myConnection.Open()

empID = (Session(

"UserEmployeeID"))

equid = (Session(

"UserEquipID"))IfCType(empID,Integer) =NothingThen

empID = (DropDownList1.SelectedValue)

'Response.Write("Insert: " & empID)'Response.End()Me.SqlDataSource1.InsertCommand ="INSERT INTO [EMPLOYEES_EQUIP] ([EquipID], [EmployeeID]) VALUES " & equid &" " & empID &""Me.SqlDataSource1.InsertCommand = SqlDataSourceCommandType.TextMe.SqlDataSource1.Insert()Else

empequipid =

New SqlCommand("Select EmpEquipID From EMPLOYEES_EQUIP Where EmployeeID = " & empID &" AND EquipID = " & equid &" ", myConnection)

empequipid.CommandType = CommandType.Text

nbrID = empequipid.ExecuteScalar

'Response.Write("Select EmpEquipID: " & nbrID)'Response.End()

empID = (DropDownList1.SelectedValue)

'Response.Write("<br>Update EmployeeID: " & empID)'Response.End()Me.SqlDataSource1.UpdateCommand ="UPDATE EMPLOYEES_EQUIP SET EmployeeID = " & empID &" WHERE EmpEquipID = " & nbrID &" "Me.SqlDataSource1.UpdateCommand = SqlDataSourceCommandType.TextMe.SqlDataSource1.Update() <--Right here is displaying "Incorrect syntax near '0'."

'Added to output the Update Query

UpdateQuery =

"UPDATE EMPLOYEES_EQUIP SET EmployeeID = " & empID &" WHERE EmpEquipID = " & nbrID &" "'Response.Write(UpdateQuery)'Response.End()EndIf'Updating the session to new EmployeeID

Session(

"UserEployeeID") = (DropDownList1.SelectedValue)'Response.Write("<br>UserEmployeeID Session: " & Session("UserEmployeeID"))'Response.End()'-- Turn On Employee Information Table

EmpInfo.Visible =

True'-- Hides the Employee Name's Drop Down List table.

maintable.Visible =

FalseEndSub

End

Class

This will not generate a valid SQL statement. Missing parenthesis, values are separated by commas not spaces, and containts a possible security hole:

Me.SqlDataSource1.InsertCommand ="INSERT INTO [EMPLOYEES_EQUIP] ([EquipID], [EmployeeID]) VALUES " & equid &" " & empID &""

This is setting the wrong property:

Me.SqlDataSource1.InsertCommand = SqlDataSourceCommandType.Text

The & " " at the end of this is unneccessary, as well as contains a possible secutiy hole:

Me.SqlDataSource1.UpdateCommand ="UPDATE EMPLOYEES_EQUIP SET EmployeeID = " & empID &" WHERE EmpEquipID = " & nbrID &" "

This sets the wrong property, and the cause of your error message:

Me.SqlDataSource1.UpdateCommand = SqlDataSourceCommandType.Text

The following is redundant code (Exists in both IF paths and should be moved outside of the if):

empID = (DropDownList1.SelectedValue)

The following is useless code and isn't referenced anywhere. Should comment it out, or remove:

UpdateQuery =

"UPDATE EMPLOYEES_EQUIP SET EmployeeID = " & empID &" WHERE EmpEquipID = " & nbrID &" "

Useless & " " at end, and possible security hole:

empequipid =

New SqlCommand("Select EmpEquipID From EMPLOYEES_EQUIP Where EmployeeID = " & empID &" AND EquipID = " & equid &" ", myConnection)

The following wasn't assigned a type (String?) and becomes unneeded once the above changes have been made:

Dim UpdateQuery

|||ahh I got it to work now! Thank you!

Incorrect SQL syntax near......

Why am I getting this error.

[SqlException (0x80131904): Incorrect syntax near '.123'.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +857418
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +735030
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +149
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +886
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +415
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135
WebApplication10.Edit.dgrdEdit_UpdateCommand(Object source, DataGridCommandEventArgs e) in C:\Inventory\edit.aspx.vb:186
System.Web.UI.WebControls.DataGrid.OnUpdateCommand(DataGridCommandEventArgs e) +105
System.Web.UI.WebControls.DataGrid.OnBubbleEvent(Object source, EventArgs e) +471
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
System.Web.UI.WebControls.DataGridItem.OnBubbleEvent(Object source, EventArgs e) +117
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e) +115
System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) +163
System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +174
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102

Your SQL statement is not well formed. You normally get this error when generating SQL on the fly and one of your variables doesn't exist, or is text but you're not adding quotes etc.

Write out your SQL to the screen instead of running it agaist the database to make sure it is valid.

Incorrect Operating system error 112(There is not enough space on the disk.) -

MSsql server gives this error while there is 24 GB physical disk space available.
I think an bug.
I have a disk of 100 GB All the files together are 100 GB big, but an part is windows-nt compressed so the pysical disk space of these files is 77 GB.
I tried to increase the tempdb by hand, but still gives this message.
If I delete files from the disk I can increase the tempdb.Forgot to mention is SQL2000 sp2 on W2K server sp1sql