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 File Used Space

How is it, that when I delete records in my Test environment that the "data
used" within the data file decreases, but not so when I delete records from
my production environment?
Message posted via http://www.droptable.com
See my reply to your other message.
Andrew J. Kelly SQL MVP
"Robert Richards via droptable.com" <forum@.nospam.droptable.com> wrote in
message news:2cc2a3ac2e7643468a1c4107212c0865@.droptable.co m...
> How is it, that when I delete records in my Test environment that the
> "data
> used" within the data file decreases, but not so when I delete records
> from
> my production environment?
> --
> Message posted via http://www.droptable.com
|||hi
why dont you post it again so that others can go through the post aswell
best Regards,
Chandra
http://groups.msn.com/SQLResource/
http://chanduas.blogspot.com/
*** Sent via Developersdex http://www.codecomments.com ***

Data File Used Space

How is it, that when I delete records in my Test environment that the "data
used" within the data file decreases, but not so when I delete records from
my production environment?
Message posted via http://www.droptable.comSee my reply to your other message.
Andrew J. Kelly SQL MVP
"Robert Richards via droptable.com" <forum@.nospam.droptable.com> wrote in
message news:2cc2a3ac2e7643468a1c4107212c0865@.SQ
droptable.com...
> How is it, that when I delete records in my Test environment that the
> "data
> used" within the data file decreases, but not so when I delete records
> from
> my production environment?
> --
> Message posted via http://www.droptable.com|||hi
why dont you post it again so that others can go through the post aswell
best Regards,
Chandra
http://groups.msn.com/SQLResource/
http://chanduas.blogspot.com/
---
*** Sent via Developersdex http://www.codecomments.com ***sql

Data File Used Space

How is it, that when I delete records in my Test environment that the "data
used" within the data file decreases, but not so when I delete records from
my production environment?
--
Message posted via http://www.sqlmonster.comSee my reply to your other message.
--
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.nospam.SQLMonster.com> wrote in
message news:2cc2a3ac2e7643468a1c4107212c0865@.SQLMonster.com...
> How is it, that when I delete records in my Test environment that the
> "data
> used" within the data file decreases, but not so when I delete records
> from
> my production environment?
> --
> Message posted via http://www.sqlmonster.com

Data connection error - Please help

Thanks in advance to anyone that can help me with this.

We have software in a production environment that uses a DSN-Less connection to access an Access Database. The software was written in VB6 and uses MDAC 2.8

99% of customers have no issues with the software connecting the the database however once in while we encounter a customer that get's the following error:

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

Except for the DB path the rest of the connection string is hardcoded in the app so there is no difference in the name of the driver used on 99% of the working installations and the 1% that fail. The debug log returned by the customer shows that the following connection string is being used:

Driver={Microsoft Access Driver (*.mdb)};Uid=xxxx;Pwd=xxx;Dbq=C:\Windows\SomeSubDir\ourAccessDB;

We have verfied the following:

1. Our Access DB must be placed under a subdirectory of the main Windows directory. There is special reason for this but it would take too long to explain. However, I have confirmed that the database can be accessed and the user has full permissions on these folders. The can create new files in the same directory, etc.

2. Customer is running Windows XP SP2. Has MDAC 2.8 installed as well as the MS Jet SP8

3. Customer confirms that the entry "Microsoft Access Drive (*.mdb)" shows up in the driver tab of the ODBC panel.

4. I have discovered that I can only reproduce that error on our development systems if I change the connection string to use a driver that doesn't exist (e.g. Driver={Mosoft Access Driver (*.mdb)}

5. Latest users states that this is a "clean" system that is fairly new.

Any ideas would be greatly appreciated.

Take a look at this support article

http://support.microsoft.com/default.aspx/kb/271908

Hope this helps

Data added before publication created does not replicate - why?

Hello,

I have a small three server development environment where I am getting my feet wet with replication. I have set up peer-to-peer transactional replication and it works fine for data added to the publication's table after the publication was created. However, rows in the table that existed prior to the publication's creation have never replicated. If any of the "old" rows are edited they cause an error on the subscribing servers when the replicator attempts to apply updates to rows that do not exist.

How can I get the old rows that predate the publication to replicate?

Thanks,

BCB

I believe to setup peer-to-peer replication, you'll need to initialize the data at each node through backup and restore. You can find more info here.

http://msdn2.microsoft.com/en-us/library/ms146914.aspx

Gary