Encrypted data size by original size, algorithm ?

I want to know encrypted data's size for designing database field size.

For example, cardnumber varchar(20) Encrypted by Triple_DES and PassPhrase, How match size does need to encrypted data store field.

I think the size does not depend to PassPhrase char length.

Regards,
Yoshihiro Kawabata

The simplest way is to just encrypt the largest piece of data that you will store using the encryption algorithm of your choice and take note of the size of the resulting blob. That will be the size of the field. There is also a formula that allows you to compute this, but it also includes the size of a header which might expand in future versions of SQL Server. I suggest to always leave several bytes more to account for changes in the format of encrypted data.

Thanks
Laurentiu

|||

Here's a post that describes how to determine the length of the encrypted data for SQL Server 2005:

http://blogs.msdn.com/yukondoit/archive/2005/11/24/496521.aspx

Thanks
Laurentiu

Encrypted data size by original size, algorithm ?

I want to know encrypted data's size for designing database field size.

For example, cardnumber varchar(20) Encrypted by Triple_DES and PassPhrase, How match size does need to encrypted data store field.

I think the size does not depend to PassPhrase char length.

Regards,
Yoshihiro Kawabata

The simplest way is to just encrypt the largest piece of data that you will store using the encryption algorithm of your choice and take note of the size of the resulting blob. That will be the size of the field. There is also a formula that allows you to compute this, but it also includes the size of a header which might expand in future versions of SQL Server. I suggest to always leave several bytes more to account for changes in the format of encrypted data.

Thanks
Laurentiu

|||

Here's a post that describes how to determine the length of the encrypted data for SQL Server 2005:

http://blogs.msdn.com/yukondoit/archive/2005/11/24/496521.aspx

Thanks
Laurentiu

Emptying Log file

I have a database with mdf size 10 gb and ldf size 8 gb. i want to
reduce the size of ldf file how can i do it.
Regards,
Farid.
*** Sent via Developersdex http://www.examnotes.net ***You can shrink transaction log size.
1. DBCC ShrinkDatabase
2. Dbcc ShrinkFile --log
3. Auto shrink option
"Ghulam Farid"?? ??? ??:

>
> I have a database with mdf size 10 gb and ldf size 8 gb. i want to
> reduce the size of ldf file how can i do it.
> Regards,
> Farid.
> *** Sent via Developersdex http://www.examnotes.net ***
>

Empty Log files and Data files

Hi!
I'm using replication with two database on SQL 2000,when begin, the log files size is 50mb and the data files size is 150mb. But now the log files size is 2Gb and the data files size is 4Gb. I would like to decrease the log files and the data files ? How do i do this?
(I using Truncate and shrink doesn't change )
Thanks!!!For log file, after replicating, take a full backup of your database, then truncate log.. This must reduce the log file..

I have no other idea about reducing the data file size.

Regards|||Is this on the source, destination or distribution?
Backup the log or change the recovery model to simple then shrink the log.

For data - shrink the data file.

Are you sure that the data is replicating and it's not building up because transactions can't be cleared?
That would end up with a lot of data in the distribution database and log space used.

Empty Database Not Shrinking

SQL Server 2K database with size 117GB. I have dropped the all the tables.
So No user tables are there in the database.
Then tried for shrinking using dbcc shrinkfile and dbcc shrinkdatabase. But
its not reducing the size of the database.
When I take a backup, the backup size is coming close to 200MB only. Log
file is not big, its close to 100 MB in size.
I only have dbo permissions on this database, not sysadmin and I am not sure
about the initial size of the database.
How can I shrink this database?What do the following yield?
USE [your_database];
GO
SELECT name,size FROM sysfiles;
SELECT TOP 10
table_or_view = OBJECT_NAME(id), rows
FROM sysindexes
WHERE indid IN (0,1)
AND OBJECTPROPERTY(id, 'IsMsShipped') = 0
ORDER BY rows DESC;
--
Aaron Bertrand
SQL Server MVP
"Bachelor Boy" <bachelorboy@.gmail.com> wrote in message
news:47DBFAFA-63E1-4592-B917-509DEC782FA7@.microsoft.com...
> SQL Server 2K database with size 117GB. I have dropped the all the tables.
> So No user tables are there in the database.
> Then tried for shrinking using dbcc shrinkfile and dbcc shrinkdatabase.
> But its not reducing the size of the database.
> When I take a backup, the backup size is coming close to 200MB only. Log
> file is not big, its close to 100 MB in size.
> I only have dbo permissions on this database, not sysadmin and I am not
> sure about the initial size of the database.
> How can I shrink this database?|||Here is the query results
name size
-- --
SNRDEV_Data 14712960
SNRDEV_Log 128000
(2 row(s) affected)
table_or_view rows
-- --
dtproperties 0
(1 row(s) affected)
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uq1U4cS2HHA.3916@.TK2MSFTNGP02.phx.gbl...
> What do the following yield?
> USE [your_database];
> GO
> SELECT name,size FROM sysfiles;
> SELECT TOP 10
> table_or_view = OBJECT_NAME(id), rows
> FROM sysindexes
> WHERE indid IN (0,1)
> AND OBJECTPROPERTY(id, 'IsMsShipped') = 0
> ORDER BY rows DESC;
> --
> Aaron Bertrand
> SQL Server MVP
> "Bachelor Boy" <bachelorboy@.gmail.com> wrote in message
> news:47DBFAFA-63E1-4592-B917-509DEC782FA7@.microsoft.com...
>> SQL Server 2K database with size 117GB. I have dropped the all the
>> tables. So No user tables are there in the database.
>> Then tried for shrinking using dbcc shrinkfile and dbcc shrinkdatabase.
>> But its not reducing the size of the database.
>> When I take a backup, the backup size is coming close to 200MB only. Log
>> file is not big, its close to 100 MB in size.
>> I only have dbo permissions on this database, not sysadmin and I am not
>> sure about the initial size of the database.
>> How can I shrink this database?
>|||Ok, so maybe some system table is taking up space?
SELECT TOP 10
table_or_view = OBJECT_NAME(id), rows
FROM sysindexes
WHERE indid IN (0,1)
AND OBJECTPROPERTY(id, 'IsMsShipped') = 1
ORDER BY rows DESC;
You said basically that shrinking doesn't work. But what *exactly* is the
result of
DBCC SHRINKFILER(SNRDEV_Data, 1)
?
Of course, final silly question, you are running these queries from SNRDEV,
right? And that is the database you are trying to shrink?
--
Aaron Bertrand
SQL Server MVP
"Bachelor Boy" <bachelorboy@.gmail.com> wrote in message
news:53CC20A1-2B62-4BBD-9E62-70E932D679DA@.microsoft.com...
> Here is the query results
> name size
> -- --
> SNRDEV_Data 14712960
> SNRDEV_Log 128000
> (2 row(s) affected)
> table_or_view rows
> -- --
> dtproperties 0
> (1 row(s) affected)|||I don't think you will be able to shrink the data file beyond its minimum
configured size (see ALTER DATABASE).
Why don't you just drop the database and re-create it at a more appropriate
size?
--
Aaron Bertrand
SQL Server MVP
"Bachelor Boy" <bachelorboy@.gmail.com> wrote in message
news:53CC20A1-2B62-4BBD-9E62-70E932D679DA@.microsoft.com...
> Here is the query results
> name size
> -- --
> SNRDEV_Data 14712960
> SNRDEV_Log 128000
> (2 row(s) affected)
> table_or_view rows
> -- --
> dtproperties 0
> (1 row(s) affected)
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:uq1U4cS2HHA.3916@.TK2MSFTNGP02.phx.gbl...
>> What do the following yield?
>> USE [your_database];
>> GO
>> SELECT name,size FROM sysfiles;
>> SELECT TOP 10
>> table_or_view = OBJECT_NAME(id), rows
>> FROM sysindexes
>> WHERE indid IN (0,1)
>> AND OBJECTPROPERTY(id, 'IsMsShipped') = 0
>> ORDER BY rows DESC;
>> --
>> Aaron Bertrand
>> SQL Server MVP
>> "Bachelor Boy" <bachelorboy@.gmail.com> wrote in message
>> news:47DBFAFA-63E1-4592-B917-509DEC782FA7@.microsoft.com...
>> SQL Server 2K database with size 117GB. I have dropped the all the
>> tables. So No user tables are there in the database.
>> Then tried for shrinking using dbcc shrinkfile and dbcc shrinkdatabase.
>> But its not reducing the size of the database.
>> When I take a backup, the backup size is coming close to 200MB only. Log
>> file is not big, its close to 100 MB in size.
>> I only have dbo permissions on this database, not sysadmin and I am not
>> sure about the initial size of the database.
>> How can I shrink this database?
>>|||DBCC commands are not showing any specific messages, It says the regular,
"completed, please contact admin in any error"
I am running the quries from the SNRDEV database.
sysdepends is the table with max rows, that's 283 rows. rest of the tables
are having less than 100 rows
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uvepewS2HHA.4476@.TK2MSFTNGP06.phx.gbl...
> Ok, so maybe some system table is taking up space?
> SELECT TOP 10
> table_or_view = OBJECT_NAME(id), rows
> FROM sysindexes
> WHERE indid IN (0,1)
> AND OBJECTPROPERTY(id, 'IsMsShipped') = 1
> ORDER BY rows DESC;
> You said basically that shrinking doesn't work. But what *exactly* is the
> result of
> DBCC SHRINKFILER(SNRDEV_Data, 1)
> ?
> Of course, final silly question, you are running these queries from
> SNRDEV, right? And that is the database you are trying to shrink?
> --
> Aaron Bertrand
> SQL Server MVP
> "Bachelor Boy" <bachelorboy@.gmail.com> wrote in message
> news:53CC20A1-2B62-4BBD-9E62-70E932D679DA@.microsoft.com...
>> Here is the query results
>> name size
>> -- --
>> SNRDEV_Data 14712960
>> SNRDEV_Log 128000
>> (2 row(s) affected)
>> table_or_view rows
>> -- --
>> dtproperties 0
>> (1 row(s) affected)
>

Empty Database Not Shrinking

SQL Server 2K database with size 117GB. I have dropped the all the tables.
So No user tables are there in the database.
Then tried for shrinking using dbcc shrinkfile and dbcc shrinkdatabase. But
its not reducing the size of the database.
When I take a backup, the backup size is coming close to 200MB only. Log
file is not big, its close to 100 MB in size.
I only have dbo permissions on this database, not sysadmin and I am not sure
about the initial size of the database.
How can I shrink this database?
What do the following yield?
USE [your_database];
GO
SELECT name,size FROM sysfiles;
SELECT TOP 10
table_or_view = OBJECT_NAME(id), rows
FROM sysindexes
WHERE indid IN (0,1)
AND OBJECTPROPERTY(id, 'IsMsShipped') = 0
ORDER BY rows DESC;
Aaron Bertrand
SQL Server MVP
"Bachelor Boy" <bachelorboy@.gmail.com> wrote in message
news:47DBFAFA-63E1-4592-B917-509DEC782FA7@.microsoft.com...
> SQL Server 2K database with size 117GB. I have dropped the all the tables.
> So No user tables are there in the database.
> Then tried for shrinking using dbcc shrinkfile and dbcc shrinkdatabase.
> But its not reducing the size of the database.
> When I take a backup, the backup size is coming close to 200MB only. Log
> file is not big, its close to 100 MB in size.
> I only have dbo permissions on this database, not sysadmin and I am not
> sure about the initial size of the database.
> How can I shrink this database?
|||Here is the query results
name size
-- --
SNRDEV_Data 14712960
SNRDEV_Log 128000
(2 row(s) affected)
table_or_view rows
-- --
dtproperties 0
(1 row(s) affected)
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uq1U4cS2HHA.3916@.TK2MSFTNGP02.phx.gbl...
> What do the following yield?
> USE [your_database];
> GO
> SELECT name,size FROM sysfiles;
> SELECT TOP 10
> table_or_view = OBJECT_NAME(id), rows
> FROM sysindexes
> WHERE indid IN (0,1)
> AND OBJECTPROPERTY(id, 'IsMsShipped') = 0
> ORDER BY rows DESC;
> --
> Aaron Bertrand
> SQL Server MVP
> "Bachelor Boy" <bachelorboy@.gmail.com> wrote in message
> news:47DBFAFA-63E1-4592-B917-509DEC782FA7@.microsoft.com...
>
|||Ok, so maybe some system table is taking up space?
SELECT TOP 10
table_or_view = OBJECT_NAME(id), rows
FROM sysindexes
WHERE indid IN (0,1)
AND OBJECTPROPERTY(id, 'IsMsShipped') = 1
ORDER BY rows DESC;
You said basically that shrinking doesn't work. But what *exactly* is the
result of
DBCC SHRINKFILER(SNRDEV_Data, 1)
?
Of course, final silly question, you are running these queries from SNRDEV,
right? And that is the database you are trying to shrink?
Aaron Bertrand
SQL Server MVP
"Bachelor Boy" <bachelorboy@.gmail.com> wrote in message
news:53CC20A1-2B62-4BBD-9E62-70E932D679DA@.microsoft.com...
> Here is the query results
> name size
> -- --
> SNRDEV_Data 14712960
> SNRDEV_Log 128000
> (2 row(s) affected)
> table_or_view rows
> -- --
> dtproperties 0
> (1 row(s) affected)
|||I don't think you will be able to shrink the data file beyond its minimum
configured size (see ALTER DATABASE).
Why don't you just drop the database and re-create it at a more appropriate
size?
Aaron Bertrand
SQL Server MVP
"Bachelor Boy" <bachelorboy@.gmail.com> wrote in message
news:53CC20A1-2B62-4BBD-9E62-70E932D679DA@.microsoft.com...[vbcol=seagreen]
> Here is the query results
> name size
> -- --
> SNRDEV_Data 14712960
> SNRDEV_Log 128000
> (2 row(s) affected)
> table_or_view rows
> -- --
> dtproperties 0
> (1 row(s) affected)
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:uq1U4cS2HHA.3916@.TK2MSFTNGP02.phx.gbl...
|||DBCC commands are not showing any specific messages, It says the regular,
"completed, please contact admin in any error"
I am running the quries from the SNRDEV database.
sysdepends is the table with max rows, that's 283 rows. rest of the tables
are having less than 100 rows
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uvepewS2HHA.4476@.TK2MSFTNGP06.phx.gbl...
> Ok, so maybe some system table is taking up space?
> SELECT TOP 10
> table_or_view = OBJECT_NAME(id), rows
> FROM sysindexes
> WHERE indid IN (0,1)
> AND OBJECTPROPERTY(id, 'IsMsShipped') = 1
> ORDER BY rows DESC;
> You said basically that shrinking doesn't work. But what *exactly* is the
> result of
> DBCC SHRINKFILER(SNRDEV_Data, 1)
> ?
> Of course, final silly question, you are running these queries from
> SNRDEV, right? And that is the database you are trying to shrink?
> --
> Aaron Bertrand
> SQL Server MVP
> "Bachelor Boy" <bachelorboy@.gmail.com> wrote in message
> news:53CC20A1-2B62-4BBD-9E62-70E932D679DA@.microsoft.com...
>

Empty Database Not Shrinking

SQL Server 2K database with size 117GB. I have dropped the all the tables.
So No user tables are there in the database.
Then tried for shrinking using dbcc shrinkfile and dbcc shrinkdatabase. But
its not reducing the size of the database.
When I take a backup, the backup size is coming close to 200MB only. Log
file is not big, its close to 100 MB in size.
I only have dbo permissions on this database, not sysadmin and I am not sure
about the initial size of the database.
How can I shrink this database?What do the following yield?
USE [your_database];
GO
SELECT name,size FROM sysfiles;
SELECT TOP 10
table_or_view = OBJECT_NAME(id), rows
FROM sysindexes
WHERE indid IN (0,1)
AND OBJECTPROPERTY(id, 'IsMsShipped') = 0
ORDER BY rows DESC;
Aaron Bertrand
SQL Server MVP
"Bachelor Boy" <bachelorboy@.gmail.com> wrote in message
news:47DBFAFA-63E1-4592-B917-509DEC782FA7@.microsoft.com...
> SQL Server 2K database with size 117GB. I have dropped the all the tables.
> So No user tables are there in the database.
> Then tried for shrinking using dbcc shrinkfile and dbcc shrinkdatabase.
> But its not reducing the size of the database.
> When I take a backup, the backup size is coming close to 200MB only. Log
> file is not big, its close to 100 MB in size.
> I only have dbo permissions on this database, not sysadmin and I am not
> sure about the initial size of the database.
> How can I shrink this database?|||Here is the query results
name size
-- --
SNRDEV_Data 14712960
SNRDEV_Log 128000
(2 row(s) affected)
table_or_view rows
-- --
dtproperties 0
(1 row(s) affected)
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:uq1U4cS2HHA.3916@.TK2MSFTNGP02.phx.gbl...
> What do the following yield?
> USE [your_database];
> GO
> SELECT name,size FROM sysfiles;
> SELECT TOP 10
> table_or_view = OBJECT_NAME(id), rows
> FROM sysindexes
> WHERE indid IN (0,1)
> AND OBJECTPROPERTY(id, 'IsMsShipped') = 0
> ORDER BY rows DESC;
> --
> Aaron Bertrand
> SQL Server MVP
> "Bachelor Boy" <bachelorboy@.gmail.com> wrote in message
> news:47DBFAFA-63E1-4592-B917-509DEC782FA7@.microsoft.com...
>|||Ok, so maybe some system table is taking up space?
SELECT TOP 10
table_or_view = OBJECT_NAME(id), rows
FROM sysindexes
WHERE indid IN (0,1)
AND OBJECTPROPERTY(id, 'IsMsShipped') = 1
ORDER BY rows DESC;
You said basically that shrinking doesn't work. But what *exactly* is the
result of
DBCC SHRINKFILER(SNRDEV_Data, 1)
?
Of course, final silly question, you are running these queries from SNRDEV,
right? And that is the database you are trying to shrink?
Aaron Bertrand
SQL Server MVP
"Bachelor Boy" <bachelorboy@.gmail.com> wrote in message
news:53CC20A1-2B62-4BBD-9E62-70E932D679DA@.microsoft.com...
> Here is the query results
> name size
> -- --
> SNRDEV_Data 14712960
> SNRDEV_Log 128000
> (2 row(s) affected)
> table_or_view rows
> -- --
> dtproperties 0
> (1 row(s) affected)|||I don't think you will be able to shrink the data file beyond its minimum
configured size (see ALTER DATABASE).
Why don't you just drop the database and re-create it at a more appropriate
size?
Aaron Bertrand
SQL Server MVP
"Bachelor Boy" <bachelorboy@.gmail.com> wrote in message
news:53CC20A1-2B62-4BBD-9E62-70E932D679DA@.microsoft.com...[vbcol=seagreen]
> Here is the query results
> name size
> -- --
> SNRDEV_Data 14712960
> SNRDEV_Log 128000
> (2 row(s) affected)
> table_or_view rows
> -- --
> dtproperties 0
> (1 row(s) affected)
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:uq1U4cS2HHA.3916@.TK2MSFTNGP02.phx.gbl...|||DBCC commands are not showing any specific messages, It says the regular,
"completed, please contact admin in any error"
I am running the quries from the SNRDEV database.
sysdepends is the table with max rows, that's 283 rows. rest of the tables
are having less than 100 rows
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:uvepewS2HHA.4476@.TK2MSFTNGP06.phx.gbl...
> Ok, so maybe some system table is taking up space?
> SELECT TOP 10
> table_or_view = OBJECT_NAME(id), rows
> FROM sysindexes
> WHERE indid IN (0,1)
> AND OBJECTPROPERTY(id, 'IsMsShipped') = 1
> ORDER BY rows DESC;
> You said basically that shrinking doesn't work. But what *exactly* is the
> result of
> DBCC SHRINKFILER(SNRDEV_Data, 1)
> ?
> Of course, final silly question, you are running these queries from
> SNRDEV, right? And that is the database you are trying to shrink?
> --
> Aaron Bertrand
> SQL Server MVP
> "Bachelor Boy" <bachelorboy@.gmail.com> wrote in message
> news:53CC20A1-2B62-4BBD-9E62-70E932D679DA@.microsoft.com...
>