Data File will not shrink

I have an issue, and hopefully I can explain this fully enough. We
have a 3 server environment, all running SQL Server 2000 SP4 on
Windows 2000 Server. Server A, Server B, and Server C. Server A
replicates to Server B, Server B then replicates to Server C.
Server B has a database that two data files and two log files on two
separate drives. So drive D has 1 datafile and 1 log file, and drive
E has the same. Drive E is running out of disk space and drive D had
some to spare. In Enterprise Manager, I went to the Shrink Database
menu for that database, and I choosed 'Empty the file (data will
migrate to other files in the file group) option. That finished
successfully. But now I see that only 2 mb is being used, but the
database size is still 22358 mb. I have tried the other shrink
options with no luck.
So I have tried:
-Compress Pages and then truncate free space from the file
-Truncate free space from the other end of file
-Shrink file to "min size"
-I have also tried checking the 'Move pages to beginning of file
before shrinking, then retrying the steps above.
Also, we have tried shrinking the transaction table using a stored
proceedure called sp_forceshrink_log as well as a few other things we
have seen online.
After all this, the file size is still 20+ gb. My question is this: Is
there anything else we can try to free up the close to 20 gigs of
space? Also, this database is in the middle of a replicated chain,
what effect would there be in deleting the empty file in the
properties of the database. Is that even possible with replication
enabled?
Thanks for any help you can provide.
JonIf you want to shrink a log file, you should do it immediately after
you backup the transaction log. If you do it later, you will get a
warning like "Cannot shrink log file 2 (LogFile) because all logical
log files are in use." and the file will not be shrinked.
If you get a warning saying "The log was not truncated because records
at the beginning of the log are pending replication. Ensure the Log
Reader Agent is running or use sp_repldone to mark transactions as
distributed", you should see this thread, for example:
http://groups.google.com/group/comp.databases.ms-sqlserver/browse_frm/th
read/23ca52b9df791e59/
In any case, you should not delete the transaction log file (especially
if the database is in a transactional replication chain).
--
Razvan Socol
SQL Server MVP|||Thanks for your response, but the issue is not with the log file, but
the data file. I emptied the contents of one data file to another
data file on another drive. So I have 2 mb used in the file, but the
file size is 20+ gb on the physical drive. I am unable to shrink the
file size using the options found in the "Shrink Database" window. So
I am looking for alternatives to getting the data file size down.
Jon
On Feb 3, 2:42 am, "Razvan Socol" <rso...@.gmail.com> wrote:
> If you want to shrink a log file, you should do it immediately after
> you backup the transaction log. If you do it later, you will get a
> warning like "Cannot shrink log file 2 (LogFile) because all logical
> log files are in use." and the file will not be shrinked.
> If you get a warning saying "The log was not truncated because records
> at the beginning of the log are pending replication. Ensure the Log
> Reader Agent is running or use sp_repldone to mark transactions as
> distributed", you should see this thread, for example:http://groups.google.com/group/comp.databases.ms-sqlserver/browse_frm/th
> read/23ca52b9df791e59/
> In any case, you should not delete the transaction log file (especially
> if the database is in a transactional replication chain).
> --
> Razvan Socol
> SQL Server MVP

Data Driven Subscriptions unavailable, please help

I'm running sql2000 Standard with SP4 applied. I also have Reporting Services 2000 with service packs running on the same machine. I'm trying to get a data-driven subscription, but the button to access is unavailable.

I've assigned the account I'm working under the Publisher role and have modified this role to have all permissions (including managing subscriptions). I have stored credentials with my data source as well.

I can create a standard subscription with no problems, but just don't get the button to do the data-driven option.

Any assistance is appreciated.

You are running RS 2000 Standard Edition. Data driven subscriptions are only available on Enterprise Edition and Developer Edition.

The same applies to data driven subscriptions on RS 2005: http://www.microsoft.com/sql/technologies/reporting/rsfeatures.mspx

-- Robert

|||Is it possible to run the enterprise edition of Reporting Services on the Standard edition of SQL 2000?|||

Actually, you can run a Reporting Services Enterprise Edition with its metadata store on a SQL Server Standard Edition.

-- Robert

|||We have the same issue and we are using the enterprise version. The user who is trying to set-up the data-driven subscription is also an admin on the report server settings...|||

Does anybody know the license implications if you install reporting services 2000 enterprise on a sql server 2000 standard install?

|||Does anybody know the license implications if you install reporting services 2000 enterprise on a sql server 2000 standard install?

Data Driven Subscriptions unavailable, please help

I'm running sql2000 Standard with SP4 applied. I also have Reporting Services 2000 with service packs running on the same machine. I'm trying to get a data-driven subscription, but the button to access is unavailable.

I've assigned the account I'm working under the Publisher role and have modified this role to have all permissions (including managing subscriptions). I have stored credentials with my data source as well.

I can create a standard subscription with no problems, but just don't get the button to do the data-driven option.

Any assistance is appreciated.

You are running RS 2000 Standard Edition. Data driven subscriptions are only available on Enterprise Edition and Developer Edition.

The same applies to data driven subscriptions on RS 2005: http://www.microsoft.com/sql/technologies/reporting/rsfeatures.mspx

-- Robert

|||Is it possible to run the enterprise edition of Reporting Services on the Standard edition of SQL 2000?|||

Actually, you can run a Reporting Services Enterprise Edition with its metadata store on a SQL Server Standard Edition.

-- Robert

|||We have the same issue and we are using the enterprise version. The user who is trying to set-up the data-driven subscription is also an admin on the report server settings...|||

Does anybody know the license implications if you install reporting services 2000 enterprise on a sql server 2000 standard install?

|||Does anybody know the license implications if you install reporting services 2000 enterprise on a sql server 2000 standard install?sql

Data Driven Subscriptions unavailable, please help

I'm running sql2000 Standard with SP4 applied. I also have Reporting Services 2000 with service packs running on the same machine. I'm trying to get a data-driven subscription, but the button to access is unavailable.

I've assigned the account I'm working under the Publisher role and have modified this role to have all permissions (including managing subscriptions). I have stored credentials with my data source as well.

I can create a standard subscription with no problems, but just don't get the button to do the data-driven option.

Any assistance is appreciated.

You are running RS 2000 Standard Edition. Data driven subscriptions are only available on Enterprise Edition and Developer Edition.

The same applies to data driven subscriptions on RS 2005: http://www.microsoft.com/sql/technologies/reporting/rsfeatures.mspx

-- Robert

|||Is it possible to run the enterprise edition of Reporting Services on the Standard edition of SQL 2000?|||

Actually, you can run a Reporting Services Enterprise Edition with its metadata store on a SQL Server Standard Edition.

-- Robert

|||We have the same issue and we are using the enterprise version. The user who is trying to set-up the data-driven subscription is also an admin on the report server settings...|||

Does anybody know the license implications if you install reporting services 2000 enterprise on a sql server 2000 standard install?

|||Does anybody know the license implications if you install reporting services 2000 enterprise on a sql server 2000 standard install?

Data Download via ODBC

Server is SQL 2000 SP4. We are trying to pull some data from a Progress 9.1d
server via ODBC. If I go to the local packages, open the package in question
and execute it, everything runs fine and it gets the data. If I try and run
the job manually from the job list in the SQL server agent, it fails with
the following error showing in the job history:
Executed as user: <service account>. DTSRun: Loading... DTSRun:
Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun
OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1,
Error = -2147467259 (80004005) Error string: Specified driver could
not be loaded due to system error 126 (MERANT 3.60 32-BIT Progress SQL92
v9.1D). Error source: Microsoft OLE DB Provider for ODBC Drivers
Help file: Help context: 0 Error Detail Records:
Error: -2147467259 (80004005); Provider Error: 160 (A0) Error
string: Specified driver could not be loaded due to system error 126
(MERANT 3.60 32-BIT Progress SQL92 v9.1D). Error source: Microsoft OLE
DB Provider for ODBC Drivers Help file: Help context: 0
DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun: Package execution
complete. Process Exit Code 1. The step failed.
All I've been able to find about the error code 126 is the ODBC driver and
error -2147467259 seems to indicate a problem with the ODBC driver. But if
the driver was the problem, I don't understand why it only fails when run as
a SQL server agent job. I tried changing the service account to an admin
account instead, but it didn't make a difference.
Not sure where else to turn, so I'm hoping some people here might have some
ideas.
Thanks
Brian
First, when you execute a package manually it runs from your
PC and under your security context. When you schedule it as
a job, it runs on the server and under the security context
of the Agent service account if the job is owned by a
sysadmin (otherwise it executes under the proxy account
security context).
Did you install the Merant drivers on the server? Error 126
is "Module could not be found" and the other error you are
getting is for unable to load driver. Looks like all the
needed pieces may not be installed, setup on the server -
only on your PC.
-Sue
On Mon, 26 Feb 2007 16:23:15 -0500, "Brian"
<wouldntulike2know> wrote:

>Server is SQL 2000 SP4. We are trying to pull some data from a Progress 9.1d
>server via ODBC. If I go to the local packages, open the package in question
>and execute it, everything runs fine and it gets the data. If I try and run
>the job manually from the job list in the SQL server agent, it fails with
>the following error showing in the job history:
>Executed as user: <service account>. DTSRun: Loading... DTSRun:
>Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun
>OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
>DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1,
>Error = -2147467259 (80004005) Error string: Specified driver could
>not be loaded due to system error 126 (MERANT 3.60 32-BIT Progress SQL92
>v9.1D). Error source: Microsoft OLE DB Provider for ODBC Drivers
>Help file: Help context: 0 Error Detail Records:
> Error: -2147467259 (80004005); Provider Error: 160 (A0) Error
>string: Specified driver could not be loaded due to system error 126
>(MERANT 3.60 32-BIT Progress SQL92 v9.1D). Error source: Microsoft OLE
>DB Provider for ODBC Drivers Help file: Help context: 0
>DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun: Package execution
>complete. Process Exit Code 1. The step failed.
>All I've been able to find about the error code 126 is the ODBC driver and
>error -2147467259 seems to indicate a problem with the ODBC driver. But if
>the driver was the problem, I don't understand why it only fails when run as
>a SQL server agent job. I tried changing the service account to an admin
>account instead, but it didn't make a difference.
>Not sure where else to turn, so I'm hoping some people here might have some
>ideas.
>Thanks
>Brian
>
|||"Sue Hoegemeier" <Sue_H@.nomail.please> wrote
> First, when you execute a package manually it runs from your
> PC and under your security context.
Sorry I wasn't clear. Everything I am trying is on the server.

> Did you install the Merant drivers on the server?
Yes. They test and work correctly when I execute the package, but not when I
run the job logged on with the same authority. I also tried using the
service account authority and that didn't work either.
|||Try logging onto the server using the service account that
SQL agent runs under. While logged in using this account,
install the Merant drivers.
And it never hurts to run Component checker to make sure the
MDAC installation is correct. You can download the tool
from:
http://msdn2.microsoft.com/en-us/data/aa937730.aspx
-Sue
On Tue, 27 Feb 2007 09:47:50 -0500, "Brian"
<wouldntulike2know> wrote:

>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote
>Sorry I wasn't clear. Everything I am trying is on the server.
>
>Yes. They test and work correctly when I execute the package, but not when I
>run the job logged on with the same authority. I also tried using the
>service account authority and that didn't work either.
>

Damaged databases

Hi

I recently had a hard disk failure on which was running Win2003 SQL2000 SP4.

This server is a sharepoint backend server, I repaired the disk to the point that it could be mirrored across to a new and then set about looking at the databases. There was a schedule in place to backup all 4 DBs daily, the backup process at no time after verifying the backup had any errors. All but one of the DBs reports this error when I attempt to run DBCC or similar query I/O error (bad page ID) detected during read at offset etc. (error 823) I have restored back to the earliest DBs I have and I still get the same error.

Any ideas on how I can recover as much as possible if not all of the DBs? What is the best way to restore from a transaction log? I have found a few articles on it and they all take a slightly different approach for differnet situations however I'm not entirely sure which applies to me.

All help will be very much appreciated.

Rob

Sounds like your backups are corrupt too - I suspect that the disk hardware was going bad for a while and all the backups have backed up corrupted data. Does the backup process backup the databases to the same disk hardware? How often do you run DBCC CHECKDB on your databases to verify their integrity?

You can't recover from just a transaction log - you need to restore a full backup first. What's the DBCC error you get (post the output of "DBCC CHECKDB (yourdb) WITH ALL_ERRORMSGS, NO_INFOMSGS")? Your only option may be to extract what data you can into a new database.

Thanks

Damaged databases

Hi

I recently had a hard disk failure on which was running Win2003 SQL2000 SP4.

This server is a sharepoint backend server, I repaired the disk to the point that it could be mirrored across to a new and then set about looking at the databases. There was a schedule in place to backup all 4 DBs daily, the backup process at no time after verifying the backup had any errors. All but one of the DBs reports this error when I attempt to run DBCC or similar query I/O error (bad page ID) detected during read at offset etc. (error 823) I have restored back to the earliest DBs I have and I still get the same error.

Any ideas on how I can recover as much as possible if not all of the DBs? What is the best way to restore from a transaction log? I have found a few articles on it and they all take a slightly different approach for differnet situations however I'm not entirely sure which applies to me.

All help will be very much appreciated.

Rob

Sounds like your backups are corrupt too - I suspect that the disk hardware was going bad for a while and all the backups have backed up corrupted data. Does the backup process backup the databases to the same disk hardware? How often do you run DBCC CHECKDB on your databases to verify their integrity?

You can't recover from just a transaction log - you need to restore a full backup first. What's the DBCC error you get (post the output of "DBCC CHECKDB (yourdb) WITH ALL_ERRORMSGS, NO_INFOMSGS")? Your only option may be to extract what data you can into a new database.

Thanks