Can't shrink a database

Hi there,
I am running SQL2005 SP1 Standard.
I have a database that was originally a SQL2K db that was rolled up to 2K5.
Everything was fine with it until I truncated a few tables and dropped a
large column & index on a few tables. Since then the following symptoms are
present:
1) The size of the datafile and log file are about 2.5x the size of the
actual data
2) The DB properties ALWAYS shows "Space Available" as "0.00 MB"
3) The shrink dialog with the file type set to "Log" shows avaialble free
space to be 98% but shrinking it does not release any of the free space.
I have tried backing up the database, deleting it and restoring it but the
inflated log file still exists. I've also tried creating a new blank DB
(versus letting the restore create it) and restoring into that but the
problem persists.
Does anyone have any ideas on how to shrink this database down?
Thanks,
--
-Steven1) Is it the data or the log file which is "too big"?
2) Read about DBCC UPDATEUSAGE
3) See http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steven Berkovitz" <mbcdev@.community.nospam> wrote in message
news:F4B6BA40-9143-4D52-BC8F-3FB9CFBEB133@.microsoft.com...
> Hi there,
> I am running SQL2005 SP1 Standard.
> I have a database that was originally a SQL2K db that was rolled up to 2K5.
> Everything was fine with it until I truncated a few tables and dropped a
> large column & index on a few tables. Since then the following symptoms are
> present:
> 1) The size of the datafile and log file are about 2.5x the size of the
> actual data
> 2) The DB properties ALWAYS shows "Space Available" as "0.00 MB"
> 3) The shrink dialog with the file type set to "Log" shows avaialble free
> space to be 98% but shrinking it does not release any of the free space.
> I have tried backing up the database, deleting it and restoring it but the
> inflated log file still exists. I've also tried creating a new blank DB
> (versus letting the restore create it) and restoring into that but the
> problem persists.
> Does anyone have any ideas on how to shrink this database down?
> Thanks,
> --
> -Steven|||Hi Steven,
I would like to know the issue status and whether or not you need further
research.
You can try setting the recovery mode to simple, backing up your database
completely, truncating the log with BACKUP LOG ... WITH TRUNCATE ONLY, and
then use DBCC SHRINKFILE to shrink the log file.
Have a great day!
Charles Wang
Microsoft Online Community Support

Can't shrink a database

Hi there,
I am running SQL2005 SP1 Standard.
I have a database that was originally a SQL2K db that was rolled up to 2K5.
Everything was fine with it until I truncated a few tables and dropped a
large column & index on a few tables. Since then the following symptoms are
present:
1) The size of the datafile and log file are about 2.5x the size of the
actual data
2) The DB properties ALWAYS shows "Space Available" as "0.00 MB"
3) The shrink dialog with the file type set to "Log" shows avaialble free
space to be 98% but shrinking it does not release any of the free space.
I have tried backing up the database, deleting it and restoring it but the
inflated log file still exists. I've also tried creating a new blank DB
(versus letting the restore create it) and restoring into that but the
problem persists.
Does anyone have any ideas on how to shrink this database down?
Thanks,
--
-Steven1) Is it the data or the log file which is "too big"?
2) Read about DBCC UPDATEUSAGE
3) See http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steven Berkovitz" <mbcdev@.community.nospam> wrote in message
news:F4B6BA40-9143-4D52-BC8F-3FB9CFBEB133@.microsoft.com...
> Hi there,
> I am running SQL2005 SP1 Standard.
> I have a database that was originally a SQL2K db that was rolled up to 2K5
.
> Everything was fine with it until I truncated a few tables and dropped a
> large column & index on a few tables. Since then the following symptoms a
re
> present:
> 1) The size of the datafile and log file are about 2.5x the size of the
> actual data
> 2) The DB properties ALWAYS shows "Space Available" as "0.00 MB"
> 3) The shrink dialog with the file type set to "Log" shows avaialble free
> space to be 98% but shrinking it does not release any of the free space.
> I have tried backing up the database, deleting it and restoring it but the
> inflated log file still exists. I've also tried creating a new blank DB
> (versus letting the restore create it) and restoring into that but the
> problem persists.
> Does anyone have any ideas on how to shrink this database down?
> Thanks,
> --
> -Steven|||Hi Steven,
I would like to know the issue status and whether or not you need further
research.
You can try setting the recovery mode to simple, backing up your database
completely, truncating the log with BACKUP LOG ... WITH TRUNCATE ONLY, and
then use DBCC SHRINKFILE to shrink the log file.
Have a great day!
Charles Wang
Microsoft Online Community Supportsql

Can''t run SSIS package via Agent

I've seen many postings on various forums on how to get SQL agent to execute an SSIS package. I have one that was originally created via the import wizard in SSMS, later modified in VS2005, and then re-imported in SSMS using the Object Explorer interface to load the dtsx file into SQL storage. I've tried several package protection options when importing the package: "Don't save sensitive data" (the package has no passwords in it), server storage and roles, and a specified package password (which I entered as a /DECRYPT command line parameter in the Agent job step). Agent is running using a domain admin account. No matter what I've tried, I still get an error during job execution that it failed to decrypt the password XML node. The package runs just fine when executed manually in the SSIS Object Explorer.

How can I resolve this?

Are you sure you have been talking about the same packge? You have clearly tried the options, including those which mean there is no encryption to decrypt, so it makes no sense. I have often got confused between versions or a similar mix up when frustated and trying to debug an issue like this.

|||

And by this, I believe that darren means versions of the same package. I have had similar problems in the past where I will continue to bang my head against the wall knowing that something should be working before remembering that I didn't copy the latest version of the package over to the release folder, or from the release folder to the sql package storage etc...

Anywho, I would try pushing it out with encrypt sensitive with password and if it asks you for a password when you are setting up the package in the job step you know that you have the correct version... If you use the sql package store on your ssis server, I would use the serverstorage method of encryption.

|||

Bear with me -- this is my first SSIS project; I've only used DTS in the past. I have only one version of the dtsx file. I've been setting the protection options when I import the file into SSIS via the object explorer. When you say "push it out with 'encrypt sensitive with password'", do you mean there is some way to set that option in the VS designer IDE? When I tried the pw option on import to SSIS, I did not get prompted for the pw when I created the Agent job step. Instead, I put a /DECRYPT switch in the job step's command line options. Please explain.

(And thanks very much for your help!)

|||yes there is a way to set this in the visual studios designer. Right click on the background of the control flow task of your package and select properties. About half way down the list there is a property for "protection level" where there is a drop down for the different types of encryption. If you choose encrypt sensitive w/ password it will prompt you for a password and a confirm password to encrypt the package with. After you have filled this out, any time that you go to the job step maintenance screen it will prompt you for the password (to verify your right to see the connection strings and whatnot).|||

That worked! I found the property setting in the designer and set it as you suggested. When I re-imported the dtsx file into SSIS on the production server, I also set the same protection option and entered the package pw set in the designer. I then deleted the old Agent job step and created a new one. It did not prompt for the package password, but it did execute the SSIS package this time. I saw no place in the various settings tabs for a package pw, nor did it prompt me for one, and I did not alter the command line defaults. Should it have prompted for a pw?

At any rate, the Agent job now works, and I am most grateful!

edit: BTW, when I set the 'Encrypt sensitive with password' option in the designer, it did not prompt for a package pw. There was a line in the property sheet a few lines up for entering a password.

Can''t run SSIS package via Agent

I've seen many postings on various forums on how to get SQL agent to execute an SSIS package. I have one that was originally created via the import wizard in SSMS, later modified in VS2005, and then re-imported in SSMS using the Object Explorer interface to load the dtsx file into SQL storage. I've tried several package protection options when importing the package: "Don't save sensitive data" (the package has no passwords in it), server storage and roles, and a specified package password (which I entered as a /DECRYPT command line parameter in the Agent job step). Agent is running using a domain admin account. No matter what I've tried, I still get an error during job execution that it failed to decrypt the password XML node. The package runs just fine when executed manually in the SSIS Object Explorer.

How can I resolve this?

Are you sure you have been talking about the same packge? You have clearly tried the options, including those which mean there is no encryption to decrypt, so it makes no sense. I have often got confused between versions or a similar mix up when frustated and trying to debug an issue like this.

|||

And by this, I believe that darren means versions of the same package. I have had similar problems in the past where I will continue to bang my head against the wall knowing that something should be working before remembering that I didn't copy the latest version of the package over to the release folder, or from the release folder to the sql package storage etc...

Anywho, I would try pushing it out with encrypt sensitive with password and if it asks you for a password when you are setting up the package in the job step you know that you have the correct version... If you use the sql package store on your ssis server, I would use the serverstorage method of encryption.

|||

Bear with me -- this is my first SSIS project; I've only used DTS in the past. I have only one version of the dtsx file. I've been setting the protection options when I import the file into SSIS via the object explorer. When you say "push it out with 'encrypt sensitive with password'", do you mean there is some way to set that option in the VS designer IDE? When I tried the pw option on import to SSIS, I did not get prompted for the pw when I created the Agent job step. Instead, I put a /DECRYPT switch in the job step's command line options. Please explain.

(And thanks very much for your help!)

|||yes there is a way to set this in the visual studios designer. Right click on the background of the control flow task of your package and select properties. About half way down the list there is a property for "protection level" where there is a drop down for the different types of encryption. If you choose encrypt sensitive w/ password it will prompt you for a password and a confirm password to encrypt the package with. After you have filled this out, any time that you go to the job step maintenance screen it will prompt you for the password (to verify your right to see the connection strings and whatnot).|||

That worked! I found the property setting in the designer and set it as you suggested. When I re-imported the dtsx file into SSIS on the production server, I also set the same protection option and entered the package pw set in the designer. I then deleted the old Agent job step and created a new one. It did not prompt for the package password, but it did execute the SSIS package this time. I saw no place in the various settings tabs for a package pw, nor did it prompt me for one, and I did not alter the command line defaults. Should it have prompted for a pw?

At any rate, the Agent job now works, and I am most grateful!

edit: BTW, when I set the 'Encrypt sensitive with password' option in the designer, it did not prompt for a package pw. There was a line in the property sheet a few lines up for entering a password.

Can''t run SSIS package via Agent

I've seen many postings on various forums on how to get SQL agent to execute an SSIS package. I have one that was originally created via the import wizard in SSMS, later modified in VS2005, and then re-imported in SSMS using the Object Explorer interface to load the dtsx file into SQL storage. I've tried several package protection options when importing the package: "Don't save sensitive data" (the package has no passwords in it), server storage and roles, and a specified package password (which I entered as a /DECRYPT command line parameter in the Agent job step). Agent is running using a domain admin account. No matter what I've tried, I still get an error during job execution that it failed to decrypt the password XML node. The package runs just fine when executed manually in the SSIS Object Explorer.

How can I resolve this?

Are you sure you have been talking about the same packge? You have clearly tried the options, including those which mean there is no encryption to decrypt, so it makes no sense. I have often got confused between versions or a similar mix up when frustated and trying to debug an issue like this.

|||

And by this, I believe that darren means versions of the same package. I have had similar problems in the past where I will continue to bang my head against the wall knowing that something should be working before remembering that I didn't copy the latest version of the package over to the release folder, or from the release folder to the sql package storage etc...

Anywho, I would try pushing it out with encrypt sensitive with password and if it asks you for a password when you are setting up the package in the job step you know that you have the correct version... If you use the sql package store on your ssis server, I would use the serverstorage method of encryption.

|||

Bear with me -- this is my first SSIS project; I've only used DTS in the past. I have only one version of the dtsx file. I've been setting the protection options when I import the file into SSIS via the object explorer. When you say "push it out with 'encrypt sensitive with password'", do you mean there is some way to set that option in the VS designer IDE? When I tried the pw option on import to SSIS, I did not get prompted for the pw when I created the Agent job step. Instead, I put a /DECRYPT switch in the job step's command line options. Please explain.

(And thanks very much for your help!)

|||yes there is a way to set this in the visual studios designer. Right click on the background of the control flow task of your package and select properties. About half way down the list there is a property for "protection level" where there is a drop down for the different types of encryption. If you choose encrypt sensitive w/ password it will prompt you for a password and a confirm password to encrypt the package with. After you have filled this out, any time that you go to the job step maintenance screen it will prompt you for the password (to verify your right to see the connection strings and whatnot).|||

That worked! I found the property setting in the designer and set it as you suggested. When I re-imported the dtsx file into SSIS on the production server, I also set the same protection option and entered the package pw set in the designer. I then deleted the old Agent job step and created a new one. It did not prompt for the package password, but it did execute the SSIS package this time. I saw no place in the various settings tabs for a package pw, nor did it prompt me for one, and I did not alter the command line defaults. Should it have prompted for a pw?

At any rate, the Agent job now works, and I am most grateful!

edit: BTW, when I set the 'Encrypt sensitive with password' option in the designer, it did not prompt for a package pw. There was a line in the property sheet a few lines up for entering a password.

Can''t run SSIS package via Agent

I've seen many postings on various forums on how to get SQL agent to execute an SSIS package. I have one that was originally created via the import wizard in SSMS, later modified in VS2005, and then re-imported in SSMS using the Object Explorer interface to load the dtsx file into SQL storage. I've tried several package protection options when importing the package: "Don't save sensitive data" (the package has no passwords in it), server storage and roles, and a specified package password (which I entered as a /DECRYPT command line parameter in the Agent job step). Agent is running using a domain admin account. No matter what I've tried, I still get an error during job execution that it failed to decrypt the password XML node. The package runs just fine when executed manually in the SSIS Object Explorer.

How can I resolve this?

Are you sure you have been talking about the same packge? You have clearly tried the options, including those which mean there is no encryption to decrypt, so it makes no sense. I have often got confused between versions or a similar mix up when frustated and trying to debug an issue like this.

|||

And by this, I believe that darren means versions of the same package. I have had similar problems in the past where I will continue to bang my head against the wall knowing that something should be working before remembering that I didn't copy the latest version of the package over to the release folder, or from the release folder to the sql package storage etc...

Anywho, I would try pushing it out with encrypt sensitive with password and if it asks you for a password when you are setting up the package in the job step you know that you have the correct version... If you use the sql package store on your ssis server, I would use the serverstorage method of encryption.

|||

Bear with me -- this is my first SSIS project; I've only used DTS in the past. I have only one version of the dtsx file. I've been setting the protection options when I import the file into SSIS via the object explorer. When you say "push it out with 'encrypt sensitive with password'", do you mean there is some way to set that option in the VS designer IDE? When I tried the pw option on import to SSIS, I did not get prompted for the pw when I created the Agent job step. Instead, I put a /DECRYPT switch in the job step's command line options. Please explain.

(And thanks very much for your help!)

|||yes there is a way to set this in the visual studios designer. Right click on the background of the control flow task of your package and select properties. About half way down the list there is a property for "protection level" where there is a drop down for the different types of encryption. If you choose encrypt sensitive w/ password it will prompt you for a password and a confirm password to encrypt the package with. After you have filled this out, any time that you go to the job step maintenance screen it will prompt you for the password (to verify your right to see the connection strings and whatnot).|||

That worked! I found the property setting in the designer and set it as you suggested. When I re-imported the dtsx file into SSIS on the production server, I also set the same protection option and entered the package pw set in the designer. I then deleted the old Agent job step and created a new one. It did not prompt for the package password, but it did execute the SSIS package this time. I saw no place in the various settings tabs for a package pw, nor did it prompt me for one, and I did not alter the command line defaults. Should it have prompted for a pw?

At any rate, the Agent job now works, and I am most grateful!

edit: BTW, when I set the 'Encrypt sensitive with password' option in the designer, it did not prompt for a package pw. There was a line in the property sheet a few lines up for entering a password.

Can''t run SSIS package via Agent

I've seen many postings on various forums on how to get SQL agent to execute an SSIS package. I have one that was originally created via the import wizard in SSMS, later modified in VS2005, and then re-imported in SSMS using the Object Explorer interface to load the dtsx file into SQL storage. I've tried several package protection options when importing the package: "Don't save sensitive data" (the package has no passwords in it), server storage and roles, and a specified package password (which I entered as a /DECRYPT command line parameter in the Agent job step). Agent is running using a domain admin account. No matter what I've tried, I still get an error during job execution that it failed to decrypt the password XML node. The package runs just fine when executed manually in the SSIS Object Explorer.

How can I resolve this?

Are you sure you have been talking about the same packge? You have clearly tried the options, including those which mean there is no encryption to decrypt, so it makes no sense. I have often got confused between versions or a similar mix up when frustated and trying to debug an issue like this.

|||

And by this, I believe that darren means versions of the same package. I have had similar problems in the past where I will continue to bang my head against the wall knowing that something should be working before remembering that I didn't copy the latest version of the package over to the release folder, or from the release folder to the sql package storage etc...

Anywho, I would try pushing it out with encrypt sensitive with password and if it asks you for a password when you are setting up the package in the job step you know that you have the correct version... If you use the sql package store on your ssis server, I would use the serverstorage method of encryption.

|||

Bear with me -- this is my first SSIS project; I've only used DTS in the past. I have only one version of the dtsx file. I've been setting the protection options when I import the file into SSIS via the object explorer. When you say "push it out with 'encrypt sensitive with password'", do you mean there is some way to set that option in the VS designer IDE? When I tried the pw option on import to SSIS, I did not get prompted for the pw when I created the Agent job step. Instead, I put a /DECRYPT switch in the job step's command line options. Please explain.

(And thanks very much for your help!)

|||yes there is a way to set this in the visual studios designer. Right click on the background of the control flow task of your package and select properties. About half way down the list there is a property for "protection level" where there is a drop down for the different types of encryption. If you choose encrypt sensitive w/ password it will prompt you for a password and a confirm password to encrypt the package with. After you have filled this out, any time that you go to the job step maintenance screen it will prompt you for the password (to verify your right to see the connection strings and whatnot).|||

That worked! I found the property setting in the designer and set it as you suggested. When I re-imported the dtsx file into SSIS on the production server, I also set the same protection option and entered the package pw set in the designer. I then deleted the old Agent job step and created a new one. It did not prompt for the package password, but it did execute the SSIS package this time. I saw no place in the various settings tabs for a package pw, nor did it prompt me for one, and I did not alter the command line defaults. Should it have prompted for a pw?

At any rate, the Agent job now works, and I am most grateful!

edit: BTW, when I set the 'Encrypt sensitive with password' option in the designer, it did not prompt for a package pw. There was a line in the property sheet a few lines up for entering a password.

Can''t run SSIS package via Agent

I've seen many postings on various forums on how to get SQL agent to execute an SSIS package. I have one that was originally created via the import wizard in SSMS, later modified in VS2005, and then re-imported in SSMS using the Object Explorer interface to load the dtsx file into SQL storage. I've tried several package protection options when importing the package: "Don't save sensitive data" (the package has no passwords in it), server storage and roles, and a specified package password (which I entered as a /DECRYPT command line parameter in the Agent job step). Agent is running using a domain admin account. No matter what I've tried, I still get an error during job execution that it failed to decrypt the password XML node. The package runs just fine when executed manually in the SSIS Object Explorer.

How can I resolve this?

Are you sure you have been talking about the same packge? You have clearly tried the options, including those which mean there is no encryption to decrypt, so it makes no sense. I have often got confused between versions or a similar mix up when frustated and trying to debug an issue like this.

|||

And by this, I believe that darren means versions of the same package. I have had similar problems in the past where I will continue to bang my head against the wall knowing that something should be working before remembering that I didn't copy the latest version of the package over to the release folder, or from the release folder to the sql package storage etc...

Anywho, I would try pushing it out with encrypt sensitive with password and if it asks you for a password when you are setting up the package in the job step you know that you have the correct version... If you use the sql package store on your ssis server, I would use the serverstorage method of encryption.

|||

Bear with me -- this is my first SSIS project; I've only used DTS in the past. I have only one version of the dtsx file. I've been setting the protection options when I import the file into SSIS via the object explorer. When you say "push it out with 'encrypt sensitive with password'", do you mean there is some way to set that option in the VS designer IDE? When I tried the pw option on import to SSIS, I did not get prompted for the pw when I created the Agent job step. Instead, I put a /DECRYPT switch in the job step's command line options. Please explain.

(And thanks very much for your help!)

|||yes there is a way to set this in the visual studios designer. Right click on the background of the control flow task of your package and select properties. About half way down the list there is a property for "protection level" where there is a drop down for the different types of encryption. If you choose encrypt sensitive w/ password it will prompt you for a password and a confirm password to encrypt the package with. After you have filled this out, any time that you go to the job step maintenance screen it will prompt you for the password (to verify your right to see the connection strings and whatnot).|||

That worked! I found the property setting in the designer and set it as you suggested. When I re-imported the dtsx file into SSIS on the production server, I also set the same protection option and entered the package pw set in the designer. I then deleted the old Agent job step and created a new one. It did not prompt for the package password, but it did execute the SSIS package this time. I saw no place in the various settings tabs for a package pw, nor did it prompt me for one, and I did not alter the command line defaults. Should it have prompted for a pw?

At any rate, the Agent job now works, and I am most grateful!

edit: BTW, when I set the 'Encrypt sensitive with password' option in the designer, it did not prompt for a package pw. There was a line in the property sheet a few lines up for entering a password.