How can I compact MS SQL 2000 Database?

Now my database is contain a big size and still grow up. I think may I can compact it to small size like ms-access.
How can I compact MS SQL 2000 Database?You can shrink the databse by any of the following option.

1.You can use the syntax dbcc shrinkdb <databasename> on query analyzer

or

2.selse sqlserver -> enterprise manager ->select the database -> right clik this and select the option shink

for more information u can use books online shrink database option|||To prevent the database from unwanted growth, you can set the 'Auto shrink' option (in Enterprise Manager, right click on your database, choose properties, select options tag and check the auto shrink option).

How can i check file size equal zero or not with scrip task ?

Dear all,

Could you tell me which code of vb scrip that i can check file size as follow ?

Dim FSO, Fyl
Set FSO = CreateObject("Scripting.FileSystemObject")
Set Fyl = CreateObject("File")

If FSO.FileExists("C:\temp\lcbseg.log") Then
Set Fyl = FSO.GetFile("C:\temp\lcbseg.log")
If Fyl.Size > 0 Then
Main = DTSStepScriptResult_ExecuteTask
Else
Main = DTSStepScriptResult_DontExecuteTask
End If
End if

Thanks

Best regards,

Kusuma

You need to do this in two stages, since there is no workflow script anymore.

Here is a sample for nthe Script Task. It uses a variable called FileSize, into which it stores the file size, surpirse!

Public Sub Main()

Dim info As System.IO.FileInfo = New System.IO.FileInfo("C:\File.txt")

Dim variables As New Variables

Dts.VariableDispenser.LockOneForWrite("FileSize", variables)

variables(0).Value = info.Length

variables.Unlock()

Dts.TaskResult = Dts.Results.Success

End Sub

Now you would use a precedenc constraint from the sript task to the next task, but be sure to include an expression. Double-click to get the UI, and try and expression like this-

@.[Use::FileSize] > 0

|||

I've tried to follow your code DarrenSQLIS, but it has the error "FileSize" for read/write access with error 0xC0010001 "The variable cannot be found. how can i fix this problem?

So i've some questions where can i declare FileSize variable. It's on SSIS menu=> varible,isn't it? and what's type of FileSize variable. For next what's property of expresstion to use @.[Use::FileSize] > 0.

Many thanks.

Best Regards,

Kusuma

|||I know you asked how do check for a file size equal to non zero in a script task, which can certainly be done with a script task as Darren illustrates.

Another technique for checking file existence and size simultaneously is use of "WMI Data Reader Task" with WQL. Use of the WMI data reader task will require a WMI connection manager, which is quick simple to configure, and available through the task's UI.

For example, the following WQL query checks for the existence and size of c:\boot.ini. simultaneously.

Select FileSize From CIM_Datafile Where Name = 'C:\\boot.ini'

After the WMI task, a downstream task can constrained by the following expression, which evaluates to true only for an existent, non zero byte file.

@.FileLength > "0\r\n"

NOTE, in this case, @.FileLength is a string variable, unlike the script task case. The reason the variable for file size is a string rather than a uint64 is that the WMI Data Reader task writes string and/or object variable types. The backslash is a special character to WMI, and as such is doubled up (escaped) in the WQL query.|||

KUS wrote:

Dear all,

Could you tell me which code of vb scrip that i can check file size as follow ?

Dim FSO, Fyl
Set FSO = CreateObject("Scripting.FileSystemObject")
Set Fyl = CreateObject("File")

If FSO.FileExists("C:\temp\lcbseg.log") Then
Set Fyl = FSO.GetFile("C:\temp\lcbseg.log")
If Fyl.Size > 0 Then
Main = DTSStepScriptResult_ExecuteTask
Else
Main = DTSStepScriptResult_DontExecuteTask
End If
End if

Thanks

Best regards,

Kusuma

|||Hey hi . Could you tell me a VB code so that I can check a file size in a folder and mail to abc@.xyz.com if the file size is less than 10MB?|||First of all read the code above, it shows you how to get the file size into a variable. Then use an SMTP Send Mail Task to send the mail. To restrict the email to only files < 10MB, add an expression on the precendence constraint between the Script Task and Mail Task, @.Var < 10000

How can i check file size equal zero or not with scrip task ?

Dear all,

Could you tell me which code of vb scrip that i can check file size as follow ?

Dim FSO, Fyl
Set FSO = CreateObject("Scripting.FileSystemObject")
Set Fyl = CreateObject("File")

If FSO.FileExists("C:\temp\lcbseg.log") Then
Set Fyl = FSO.GetFile("C:\temp\lcbseg.log")
If Fyl.Size > 0 Then
Main = DTSStepScriptResult_ExecuteTask
Else
Main = DTSStepScriptResult_DontExecuteTask
End If
End if

Thanks

Best regards,

Kusuma

You need to do this in two stages, since there is no workflow script anymore.

Here is a sample for nthe Script Task. It uses a variable called FileSize, into which it stores the file size, surpirse!

Public Sub Main()

Dim info As System.IO.FileInfo = New System.IO.FileInfo("C:\File.txt")

Dim variables As New Variables

Dts.VariableDispenser.LockOneForWrite("FileSize", variables)

variables(0).Value = info.Length

variables.Unlock()

Dts.TaskResult = Dts.Results.Success

End Sub

Now you would use a precedenc constraint from the sript task to the next task, but be sure to include an expression. Double-click to get the UI, and try and expression like this-

@.[Use::FileSize] > 0

|||

I've tried to follow your code DarrenSQLIS, but it has the error "FileSize" for read/write access with error 0xC0010001 "The variable cannot be found. how can i fix this problem?

So i've some questions where can i declare FileSize variable. It's on SSIS menu=> varible,isn't it? and what's type of FileSize variable. For next what's property of expresstion to use @.[Use::FileSize] > 0.

Many thanks.

Best Regards,

Kusuma

|||I know you asked how do check for a file size equal to non zero in a script task, which can certainly be done with a script task as Darren illustrates.

Another technique for checking file existence and size simultaneously is use of "WMI Data Reader Task" with WQL. Use of the WMI data reader task will require a WMI connection manager, which is quick simple to configure, and available through the task's UI.

For example, the following WQL query checks for the existence and size of c:\boot.ini. simultaneously.

Select FileSize From CIM_Datafile Where Name = 'C:\\boot.ini'

After the WMI task, a downstream task can constrained by the following expression, which evaluates to true only for an existent, non zero byte file.

@.FileLength > "0\r\n"

NOTE, in this case, @.FileLength is a string variable, unlike the script task case. The reason the variable for file size is a string rather than a uint64 is that the WMI Data Reader task writes string and/or object variable types. The backslash is a special character to WMI, and as such is doubled up (escaped) in the WQL query.
|||

KUS wrote:

Dear all,

Could you tell me which code of vb scrip that i can check file size as follow ?

Dim FSO, Fyl
Set FSO = CreateObject("Scripting.FileSystemObject")
Set Fyl = CreateObject("File")

If FSO.FileExists("C:\temp\lcbseg.log") Then
Set Fyl = FSO.GetFile("C:\temp\lcbseg.log")
If Fyl.Size > 0 Then
Main = DTSStepScriptResult_ExecuteTask
Else
Main = DTSStepScriptResult_DontExecuteTask
End If
End if

Thanks

Best regards,

Kusuma

|||Hey hi . Could you tell me a VB code so that I can check a file size in a folder and mail to abc@.xyz.com if the file size is less than 10MB?|||First of all read the code above, it shows you how to get the file size into a variable. Then use an SMTP Send Mail Task to send the mail. To restrict the email to only files < 10MB, add an expression on the precendence constraint between the Script Task and Mail Task, @.Var < 10000

How calculate space that occupied?

I want to know following memory space related question.

1 . How can i get a Database Size by means of a SQL query?

2. How can i Get a field size (not allocated space) .. ie. i am
storing some textual data to a field..
after a insertion i want to know how much space has been occupied by
that particular field .

ex sno product_name Description
1 sample1 dfkjsdkfj kldsjfkdjk
sdkjdfskdjk vcmvxcvmcvnksdjfkdsn m

Here i want know the space occupied by the field name "description"
for the product id=1.

Thanks in adavance

Regards
Visu.On Jun 12, 12:35 pm, visu <k.vis...@.gmail.comwrote:

Quote:

Originally Posted by

I want to know following memory space related question.
>
1 . How can i get a Database Size by means of a SQL query?
>
2. How can i Get a field size (not allocated space) .. ie. i am
storing some textual data to a field..
after a insertion i want to know how much space has been occupied by
that particular field .
>
ex sno product_name Description
1 sample1 dfkjsdkfj kldsjfkdjk
sdkjdfskdjk vcmvxcvmcvnksdjfkdsn m
>
Here i want know the space occupied by the field name "description"
for the product id=1.
>
Thanks in adavance
>
Regards
Visu.

1. How can i get a Database Size by means of a SQL query

sp_helpdb 'dbname'

2. Here i want know the space occupied by the field name "description"
for the product id=1.

SELECT SUM(DATALENGTH(description)) FROM table
WHERE product_id=1.|||On Jun 12, 10:47 am, M A Srinivas <masri...@.gmail.comwrote:

Quote:

Originally Posted by

On Jun 12, 12:35 pm, visu <k.vis...@.gmail.comwrote:
>
>
>
>
>

Quote:

Originally Posted by

I want to know following memory space related question.

>

Quote:

Originally Posted by

1 . How can i get a Database Size by means of a SQL query?

>

Quote:

Originally Posted by

2. How can i Get a field size (not allocated space) .. ie. i am
storing some textual data to a field..
after a insertion i want to know how much space has been occupied by
that particular field .

>

Quote:

Originally Posted by

ex sno product_name Description
1 sample1 dfkjsdkfj kldsjfkdjk
sdkjdfskdjk vcmvxcvmcvnksdjfkdsn m

>

Quote:

Originally Posted by

Here i want know the space occupied by the field name "description"
for the product id=1.

>

Quote:

Originally Posted by

Thanks in adavance

>

Quote:

Originally Posted by

Regards
Visu.

>
1. How can i get a Database Size by means of a SQL query
>
sp_helpdb 'dbname'
>
2. Here i want know the space occupied by the field name "description"
for the product id=1.
>
SELECT SUM(DATALENGTH(description)) FROM table
WHERE product_id=1.- Hide quoted text -
>
- Show quoted text -

Use the length function

SELECT LENGTH(description) FROM tablename WHERE id = 1

This will return the length of the description field for the row
identified by id = 1.|||undercups (dwang@.woodace.co.uk) writes:

Quote:

Originally Posted by

Use the length function
>
SELECT LENGTH(description) FROM tablename WHERE id = 1
>
This will return the length of the description field for the row
identified by id = 1.

There is no LENGTH function in SQL Server.

You may be thinking of len(), but since Visu asked for the space
consumption, it does not fit the bill for two reasons:

1) It ignores trailing spaces.
2) It counts characters, not bytes, which makes a difference for nvarchar.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||M A Srinivas (masri999@.gmail.com) writes:

Quote:

Originally Posted by

2. Here i want know the space occupied by the field name "description"
for the product id=1.
>
SELECT SUM(DATALENGTH(description)) FROM table
WHERE product_id=1.

Should be:

SELECT SUM(2 + DATALENGTH(description)) FROM table
WHERE product_id=1.

Add 2 for the length stored for the field.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx