Failed to obtain TransactionDispenserInterface and invalid LSN, databases suspec

All of a sudden I have 4 of my databases, including pubs that are all marked suspect and seem to be un-recoverable. I have followed the Resetting the suspect status directions as well as trying to attach only the .mdf files but still run into problems reading the .ldf file. I have not tried to do the procedure of creating another database with the same name and structure then swapping the files to trick SQl but will do so if I need to. Does anyone know why this happens all of a sudden to multile databases, of which Pubs I have never used for anything??

In my log files during startup I get this error

Failed to obtain TransactionDispenserInterface: Result Code = 0x8004d01b

Then this error for every database that is suspect, (the actual LSN numbers are different for each one).

The LSN (4:517:1) passed to log scan in database 'pubs' is invalid..
Is there anything else I can do to prevent this from happening or recover the DB's

ThanksThe log file is corrupted. The only good prevention is to have a good backup strategy. You need to look at whether your .mdf is also corrupted or not.|||Why would these become corrupted? Should I look for a SQL problem or a disk error??... Do you know the best way to track this?? I found it odd that 4 databases would end up like this at the same time

Thanks

Failed to copy objects from MS SQL Servert to MS SQL Server

I'm trying to copy objects and data between 2 SQL Server databases using the
import/export wizard in SQL Server 2000.
I select the source and destination database and leave everything as
default. When I try to run the DTS Package, it stalls at 22% for a few
minutes, then goes up to 96% or so very fast and gives the following message
quoted in the subject. ("Failed to copy objects...")
When I double-click the error, it reads the following:
"Need to run the object to perform this operation
[SQL-DMO]Code execution exception: EXCEPTION_ACCESS_VIOLATION"
I'm running the package as "sa", so I don't think I don't have privileges to
anything.
Check you have no application roles - these don't copy as they have
encrypted passwords and can break a copy DB operation.
--
Mary Bray [SQL Server MVP]
Please only reply to newsgroups
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:eBk%23TgpsEHA.3460@.TK2MSFTNGP15.phx.gbl...
> I'm trying to copy objects and data between 2 SQL Server databases using
> the
> import/export wizard in SQL Server 2000.
> I select the source and destination database and leave everything as
> default. When I try to run the DTS Package, it stalls at 22% for a few
> minutes, then goes up to 96% or so very fast and gives the following
> message
> quoted in the subject. ("Failed to copy objects...")
> When I double-click the error, it reads the following:
> "Need to run the object to perform this operation
> [SQL-DMO]Code execution exception: EXCEPTION_ACCESS_VIOLATION"
> I'm running the package as "sa", so I don't think I don't have privileges
> to
> anything.
>
|||Agoston Bejo wrote:
> I'm trying to copy objects and data between 2 SQL Server databases using the
> import/export wizard in SQL Server 2000.
> I select the source and destination database and leave everything as
> default. When I try to run the DTS Package, it stalls at 22% for a few
> minutes, then goes up to 96% or so very fast and gives the following message
> quoted in the subject. ("Failed to copy objects...")
> When I double-click the error, it reads the following:
> "Need to run the object to perform this operation
> [SQL-DMO]Code execution exception: EXCEPTION_ACCESS_VIOLATION"
> I'm running the package as "sa", so I don't think I don't have privileges to
> anything.
Are the two databases on the same machine? The same instance?
|||"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:eBk%23TgpsEHA.3460@.TK2MSFTNGP15.phx.gbl...
> I'm trying to copy objects and data between 2 SQL Server databases using
the
> import/export wizard in SQL Server 2000.
> I select the source and destination database and leave everything as
> default. When I try to run the DTS Package, it stalls at 22% for a few
> minutes, then goes up to 96% or so very fast and gives the following
message
> quoted in the subject. ("Failed to copy objects...")
> When I double-click the error, it reads the following:
> "Need to run the object to perform this operation
> [SQL-DMO]Code execution exception: EXCEPTION_ACCESS_VIOLATION"
> I'm running the package as "sa", so I don't think I don't have privileges
to
> anything.
My own experience has been that I cannot successfully copy a database from
one server to the other unless I log on to the source server locally using a
Domain Admin account and also use Windows authentication when promted by the
Copy Database wizard. I'm not at all sure why...
|||Ron Hinds wrote:
> "Agoston Bejo" <gusz1@.freemail.hu> wrote in message
> news:eBk%23TgpsEHA.3460@.TK2MSFTNGP15.phx.gbl...
>
> the
>
> message
>
> to
>
> My own experience has been that I cannot successfully copy a database from
> one server to the other unless I log on to the source server locally using a
> Domain Admin account and also use Windows authentication when promted by the
> Copy Database wizard. I'm not at all sure why...
The docs say that file sharing must be enabled between the machines, and
that the server must run as an admnistrator. Sucks, eh?
|||> Are the two databases on the same machine? The same instance?
Yes, they are. Same machine, same server instance, the one user set is sa
for both.
|||Hi
Checkout out google for this seems to be quite a common problem!
http://tinyurl.com/59xw4
This is one answer http://tinyurl.com/55ck8 , but threads seem to die out,
that may be because backup/restore is a better quicker option!
John
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:eSXs6W2sEHA.2300@.TK2MSFTNGP09.phx.gbl...
> Yes, they are. Same machine, same server instance, the one user set is sa
> for both.
>

Failed to copy objects from MS SQL Servert to MS SQL Server

I'm trying to copy objects and data between 2 SQL Server databases using the
import/export wizard in SQL Server 2000.
I select the source and destination database and leave everything as
default. When I try to run the DTS Package, it stalls at 22% for a few
minutes, then goes up to 96% or so very fast and gives the following message
quoted in the subject. ("Failed to copy objects...")
When I double-click the error, it reads the following:
"Need to run the object to perform this operation
[SQL-DMO]Code execution exception: EXCEPTION_ACCESS_VIOLATION"
I'm running the package as "sa", so I don't think I don't have privileges to
anything.
Check you have no application roles - these don't copy as they have
encrypted passwords and can break a copy DB operation.
--
Mary Bray [SQL Server MVP]
Please only reply to newsgroups
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:eBk%23TgpsEHA.3460@.TK2MSFTNGP15.phx.gbl...
> I'm trying to copy objects and data between 2 SQL Server databases using
> the
> import/export wizard in SQL Server 2000.
> I select the source and destination database and leave everything as
> default. When I try to run the DTS Package, it stalls at 22% for a few
> minutes, then goes up to 96% or so very fast and gives the following
> message
> quoted in the subject. ("Failed to copy objects...")
> When I double-click the error, it reads the following:
> "Need to run the object to perform this operation
> [SQL-DMO]Code execution exception: EXCEPTION_ACCESS_VIOLATION"
> I'm running the package as "sa", so I don't think I don't have privileges
> to
> anything.
>
|||Agoston Bejo wrote:
> I'm trying to copy objects and data between 2 SQL Server databases using the
> import/export wizard in SQL Server 2000.
> I select the source and destination database and leave everything as
> default. When I try to run the DTS Package, it stalls at 22% for a few
> minutes, then goes up to 96% or so very fast and gives the following message
> quoted in the subject. ("Failed to copy objects...")
> When I double-click the error, it reads the following:
> "Need to run the object to perform this operation
> [SQL-DMO]Code execution exception: EXCEPTION_ACCESS_VIOLATION"
> I'm running the package as "sa", so I don't think I don't have privileges to
> anything.
Are the two databases on the same machine? The same instance?
|||"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:eBk%23TgpsEHA.3460@.TK2MSFTNGP15.phx.gbl...
> I'm trying to copy objects and data between 2 SQL Server databases using
the
> import/export wizard in SQL Server 2000.
> I select the source and destination database and leave everything as
> default. When I try to run the DTS Package, it stalls at 22% for a few
> minutes, then goes up to 96% or so very fast and gives the following
message
> quoted in the subject. ("Failed to copy objects...")
> When I double-click the error, it reads the following:
> "Need to run the object to perform this operation
> [SQL-DMO]Code execution exception: EXCEPTION_ACCESS_VIOLATION"
> I'm running the package as "sa", so I don't think I don't have privileges
to
> anything.
My own experience has been that I cannot successfully copy a database from
one server to the other unless I log on to the source server locally using a
Domain Admin account and also use Windows authentication when promted by the
Copy Database wizard. I'm not at all sure why...
|||Ron Hinds wrote:
> "Agoston Bejo" <gusz1@.freemail.hu> wrote in message
> news:eBk%23TgpsEHA.3460@.TK2MSFTNGP15.phx.gbl...
>
> the
>
> message
>
> to
>
> My own experience has been that I cannot successfully copy a database from
> one server to the other unless I log on to the source server locally using a
> Domain Admin account and also use Windows authentication when promted by the
> Copy Database wizard. I'm not at all sure why...
The docs say that file sharing must be enabled between the machines, and
that the server must run as an admnistrator. Sucks, eh?
|||> Are the two databases on the same machine? The same instance?
Yes, they are. Same machine, same server instance, the one user set is sa
for both.
|||Hi
Checkout out google for this seems to be quite a common problem!
http://tinyurl.com/59xw4
This is one answer http://tinyurl.com/55ck8 , but threads seem to die out,
that may be because backup/restore is a better quicker option!
John
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:eSXs6W2sEHA.2300@.TK2MSFTNGP09.phx.gbl...
> Yes, they are. Same machine, same server instance, the one user set is sa
> for both.
>

Failed to copy objects from MS SQL Servert to MS SQL Server

I'm trying to copy objects and data between 2 SQL Server databases using the
import/export wizard in SQL Server 2000.
I select the source and destination database and leave everything as
default. When I try to run the DTS Package, it stalls at 22% for a few
minutes, then goes up to 96% or so very fast and gives the following message
quoted in the subject. ("Failed to copy objects...")
When I double-click the error, it reads the following:
"Need to run the object to perform this operation
[SQL-DMO]Code execution exception: EXCEPTION_ACCESS_VIOLATION"
I'm running the package as "sa", so I don't think I don't have privileges to
anything.Check you have no application roles - these don't copy as they have
encrypted passwords and can break a copy DB operation.
--
--
Mary Bray [SQL Server MVP]
Please only reply to newsgroups
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:eBk%23TgpsEHA.3460@.TK2MSFTNGP15.phx.gbl...
> I'm trying to copy objects and data between 2 SQL Server databases using
> the
> import/export wizard in SQL Server 2000.
> I select the source and destination database and leave everything as
> default. When I try to run the DTS Package, it stalls at 22% for a few
> minutes, then goes up to 96% or so very fast and gives the following
> message
> quoted in the subject. ("Failed to copy objects...")
> When I double-click the error, it reads the following:
> "Need to run the object to perform this operation
> [SQL-DMO]Code execution exception: EXCEPTION_ACCESS_VIOLATION"
> I'm running the package as "sa", so I don't think I don't have privileges
> to
> anything.
>|||Agoston Bejo wrote:
> I'm trying to copy objects and data between 2 SQL Server databases using the
> import/export wizard in SQL Server 2000.
> I select the source and destination database and leave everything as
> default. When I try to run the DTS Package, it stalls at 22% for a few
> minutes, then goes up to 96% or so very fast and gives the following message
> quoted in the subject. ("Failed to copy objects...")
> When I double-click the error, it reads the following:
> "Need to run the object to perform this operation
> [SQL-DMO]Code execution exception: EXCEPTION_ACCESS_VIOLATION"
> I'm running the package as "sa", so I don't think I don't have privileges to
> anything.
Are the two databases on the same machine? The same instance?|||"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:eBk%23TgpsEHA.3460@.TK2MSFTNGP15.phx.gbl...
> I'm trying to copy objects and data between 2 SQL Server databases using
the
> import/export wizard in SQL Server 2000.
> I select the source and destination database and leave everything as
> default. When I try to run the DTS Package, it stalls at 22% for a few
> minutes, then goes up to 96% or so very fast and gives the following
message
> quoted in the subject. ("Failed to copy objects...")
> When I double-click the error, it reads the following:
> "Need to run the object to perform this operation
> [SQL-DMO]Code execution exception: EXCEPTION_ACCESS_VIOLATION"
> I'm running the package as "sa", so I don't think I don't have privileges
to
> anything.
My own experience has been that I cannot successfully copy a database from
one server to the other unless I log on to the source server locally using a
Domain Admin account and also use Windows authentication when promted by the
Copy Database wizard. I'm not at all sure why...|||Ron Hinds wrote:
> "Agoston Bejo" <gusz1@.freemail.hu> wrote in message
> news:eBk%23TgpsEHA.3460@.TK2MSFTNGP15.phx.gbl...
>>I'm trying to copy objects and data between 2 SQL Server databases using
> the
>>import/export wizard in SQL Server 2000.
>>I select the source and destination database and leave everything as
>>default. When I try to run the DTS Package, it stalls at 22% for a few
>>minutes, then goes up to 96% or so very fast and gives the following
> message
>>quoted in the subject. ("Failed to copy objects...")
>>When I double-click the error, it reads the following:
>>"Need to run the object to perform this operation
>>[SQL-DMO]Code execution exception: EXCEPTION_ACCESS_VIOLATION"
>>I'm running the package as "sa", so I don't think I don't have privileges
> to
>>anything.
>
> My own experience has been that I cannot successfully copy a database from
> one server to the other unless I log on to the source server locally using a
> Domain Admin account and also use Windows authentication when promted by the
> Copy Database wizard. I'm not at all sure why...
The docs say that file sharing must be enabled between the machines, and
that the server must run as an admnistrator. Sucks, eh?|||> Are the two databases on the same machine? The same instance?
Yes, they are. Same machine, same server instance, the one user set is sa
for both.|||Hi
Checkout out google for this seems to be quite a common problem!
http://tinyurl.com/59xw4
This is one answer http://tinyurl.com/55ck8 , but threads seem to die out,
that may be because backup/restore is a better quicker option!
John
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:eSXs6W2sEHA.2300@.TK2MSFTNGP09.phx.gbl...
> > Are the two databases on the same machine? The same instance?
> Yes, they are. Same machine, same server instance, the one user set is sa
> for both.
>sql

Failed to copy objects from MS SQL Servert to MS SQL Server

I'm trying to copy objects and data between 2 SQL Server databases using the
import/export wizard in SQL Server 2000.
I select the source and destination database and leave everything as
default. When I try to run the DTS Package, it stalls at 22% for a few
minutes, then goes up to 96% or so very fast and gives the following message
quoted in the subject. ("Failed to copy objects...")
When I double-click the error, it reads the following:
"Need to run the object to perform this operation
[SQL-DMO]Code execution exception: EXCEPTION_ACCESS_VIOLATION"
I'm running the package as "sa", so I don't think I don't have privileges to
anything.Check you have no application roles - these don't copy as they have
encrypted passwords and can break a copy DB operation.
--
Mary Bray [SQL Server MVP]
Please only reply to newsgroups
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:eBk%23TgpsEHA.3460@.TK2MSFTNGP15.phx.gbl...
> I'm trying to copy objects and data between 2 SQL Server databases using
> the
> import/export wizard in SQL Server 2000.
> I select the source and destination database and leave everything as
> default. When I try to run the DTS Package, it stalls at 22% for a few
> minutes, then goes up to 96% or so very fast and gives the following
> message
> quoted in the subject. ("Failed to copy objects...")
> When I double-click the error, it reads the following:
> "Need to run the object to perform this operation
> [SQL-DMO]Code execution exception: EXCEPTION_ACCESS_VIOLATION"
> I'm running the package as "sa", so I don't think I don't have privileges
> to
> anything.
>|||Agoston Bejo wrote:
> I'm trying to copy objects and data between 2 SQL Server databases using t
he
> import/export wizard in SQL Server 2000.
> I select the source and destination database and leave everything as
> default. When I try to run the DTS Package, it stalls at 22% for a few
> minutes, then goes up to 96% or so very fast and gives the following messa
ge
> quoted in the subject. ("Failed to copy objects...")
> When I double-click the error, it reads the following:
> "Need to run the object to perform this operation
> [SQL-DMO]Code execution exception: EXCEPTION_ACCESS_VIOLATION"
> I'm running the package as "sa", so I don't think I don't have privileges
to
> anything.
Are the two databases on the same machine? The same instance?|||"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:eBk%23TgpsEHA.3460@.TK2MSFTNGP15.phx.gbl...
> I'm trying to copy objects and data between 2 SQL Server databases using
the
> import/export wizard in SQL Server 2000.
> I select the source and destination database and leave everything as
> default. When I try to run the DTS Package, it stalls at 22% for a few
> minutes, then goes up to 96% or so very fast and gives the following
message
> quoted in the subject. ("Failed to copy objects...")
> When I double-click the error, it reads the following:
> "Need to run the object to perform this operation
> [SQL-DMO]Code execution exception: EXCEPTION_ACCESS_VIOLATION"
> I'm running the package as "sa", so I don't think I don't have privileges
to
> anything.
My own experience has been that I cannot successfully copy a database from
one server to the other unless I log on to the source server locally using a
Domain Admin account and also use Windows authentication when promted by the
Copy Database wizard. I'm not at all sure why...|||Ron Hinds wrote:
> "Agoston Bejo" <gusz1@.freemail.hu> wrote in message
> news:eBk%23TgpsEHA.3460@.TK2MSFTNGP15.phx.gbl...
>
> the
>
> message
>
> to
>
>
> My own experience has been that I cannot successfully copy a database from
> one server to the other unless I log on to the source server locally using
a
> Domain Admin account and also use Windows authentication when promted by t
he
> Copy Database wizard. I'm not at all sure why...
The docs say that file sharing must be enabled between the machines, and
that the server must run as an admnistrator. Sucks, eh?|||> Are the two databases on the same machine? The same instance?
Yes, they are. Same machine, same server instance, the one user set is sa
for both.|||Hi
Checkout out google for this seems to be quite a common problem!
http://tinyurl.com/59xw4
This is one answer http://tinyurl.com/55ck8 , but threads seem to die out,
that may be because backup/restore is a better quicker option!
John
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:eSXs6W2sEHA.2300@.TK2MSFTNGP09.phx.gbl...
> Yes, they are. Same machine, same server instance, the one user set is sa
> for both.
>

Failed Maintenance Plan

Please advice.
I run a SQL2k SP4 on WIN2K SP4.
I have a maintenance plan to backup the databases at 01:30AM. This has alway
s been succesfull.
Strangely it did the backup at 02:25PM with no entry in SQL Error Log and ca
using subsequent backups to fail as the files created had the 02:25 badge he
nce could not be deleted.
Please help as to what caused the maintenance plan to backup at 02:25 and wh
at to do to avoid it.
Thanks for your kind response.I posted a reply to this yesterday. Here's that text:
My guess is that it wasn't Main Plan that did the backup, someone else did i
t (or explicitly started the main
plan job, or scheduled the main plan job at that hour). If it was executed a
s a job, you can view the job
history under EM, Management, Jobs. In any event, each backup is written to
the eventlog and the errorlog
file.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Timi" <anonymous@.discussions.microsoft.com> wrote in message
news:EA0A7329-EAA1-49B3-9DDB-1736B6F35A4C@.microsoft.com...
> Please advice.
> I run a SQL2k SP4 on WIN2K SP4.
> I have a maintenance plan to backup the databases at 01:30AM. This has alw
ays been succesfull.
> Strangely it did the backup at 02:25PM with no entry in SQL Error Log and causing
subsequent backups to fail
as the files created had the 02:25 badge hence could not be deleted.
> Please help as to what caused the maintenance plan to backup at 02:25 and
what to do to avoid it.
> Thanks for your kind response.
>|||Thanks for your response.
The maint plan was ran at 02:25AM as opposed to 01:30AM the scheduled time.|||can you check with your server guys if there was a problem
with the server which resulted in it being restarted on or
arounf 2:25 ?, but the best way of finding out is to check
the error log.
J

>--Original Message--
>Thanks for your response.
>The maint plan was ran at 02:25AM as opposed to 01:30AM
the scheduled time.
>.
>|||Agent does no run a job if it was down at the time it should have executed.
I doubt that this is the reason,
based on that. I bet a beer that someone started the job at that time.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Julie" <anonymous@.discussions.microsoft.com> wrote in message news:13fdd01c41357$84085e80$
a301280a@.phx.gbl...
> can you check with your server guys if there was a problem
> with the server which resulted in it being restarted on or
> arounf 2:25 ?, but the best way of finding out is to check
> the error log.
> J
>
> the scheduled time.|||Bet a beer against you and your SQL Server knowledge, never ;)
Anyway your right but my sneaky plan was to get Timm to check the server, as
we both agree thats where the problem is, and who actually started it.|||:-)
<<as we both agree thats where the problem is, and who actually started it.>
>
Yep.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:D6CB3367-9F9F-4633-B7FE-3FBEBFFA8B74@.microsoft.com...
> Bet a beer against you and your SQL Server knowledge, never ;)
> Anyway your right but my sneaky plan was to get Timm to check the server, as we bo
th agree thats where the
problem is, and who actually started it.

Failed Maintenance Plan

Please advice.
I run a SQL2k SP4 on WIN2K SP4.
I have a maintenance plan to backup the databases at 01:30AM. This has alway
s been successfull.
Strangely it did the backup at 02:25PM with no entry in SQL Error Log and ca
using subsequent backups to fail as the files created had the 02:25 badge he
nce could not be deleted.
Please help as to what caused the maintenece plan to backup at 02:25 and wha
t to do to avoid it.
Thanks for your kind response.My guess is that it wasn't Main Plan that did the backup, someone else did i
t (or explicitly started the main
plan job, or scheduled the main plan job at that hour). If it was executed a
s a job, you can view the job
history under EM, Management, Jobs. In any event, each backup is written to
the eventlog and the errorlog
file.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Timi" <anonymous@.discussions.microsoft.com> wrote in message
news:CDA33615-5054-47D3-8D06-0A52B8FB7544@.microsoft.com...
> Please advice.
> I run a SQL2k SP4 on WIN2K SP4.
> I have a maintenance plan to backup the databases at 01:30AM. This has alw
ays been successfull.
> Strangely it did the backup at 02:25PM with no entry in SQL Error Log and causing
subsequent backups to fail
as the files created had the 02:25 badge hence could not be deleted.
> Please help as to what caused the maintenece plan to backup at 02:25 and w
hat to do to avoid it.
> Thanks for your kind response.
>

extremely big database file... SQL Server error?

I have the following problem: the size of one of my production databases is
too big(60 gb), comparatively with the rest.
I compare the big db ("A") with another one ("B") that contains the same
tables.
I identify the larger table in db "A" (table X), which has 325000 records,
aprox. In db "B", table X has 88000 records, aprox. (less than 1/3 of the
records in "A")
I ran sp_spaceused (with updateusage = true) and obtain this values for the
total amount of space used by data for table X:
db "A": 1570392
db "B": 48688
the value in "A" is over 3 times the value in "B"... why?
The tables in the two databases are exactly the same (a nonclustered,
unique, primary key index, same constraints, same columns). In both cases,
the table has only one colum with data (the same column) and the rest columns
with "null" value.
Both db are in SQL Server 2000, on the same server. Also, exists a
maintenance plan that run every night that backup and shrink this databases,
but the size of "A" remains the same.
The command dbcc checkdb found 0 allocation errors and 0 consistency errors
in the db "A".
What is the problem? Could be an error of SQL Server?
I'll appreciate any help.
Ed. S.
Telematica Inc.
Shrinking your database files has the effect of fragmenting your indexes.
If you're always shrinking files, then you're shooting yourself in the foot.
Leave the file as big as necessary and rebuild your indexes.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"byteman" <byteman@.discussions.microsoft.com> wrote in message
news:40A188D8-05F2-4008-BE43-A41510FE83A0@.microsoft.com...
I have the following problem: the size of one of my production databases is
too big(60 gb), comparatively with the rest.
I compare the big db ("A") with another one ("B") that contains the same
tables.
I identify the larger table in db "A" (table X), which has 325000 records,
aprox. In db "B", table X has 88000 records, aprox. (less than 1/3 of the
records in "A")
I ran sp_spaceused (with updateusage = true) and obtain this values for the
total amount of space used by data for table X:
db "A": 1570392
db "B": 48688
the value in "A" is over 3 times the value in "B"... why?
The tables in the two databases are exactly the same (a nonclustered,
unique, primary key index, same constraints, same columns). In both cases,
the table has only one colum with data (the same column) and the rest
columns
with "null" value.
Both db are in SQL Server 2000, on the same server. Also, exists a
maintenance plan that run every night that backup and shrink this databases,
but the size of "A" remains the same.
The command dbcc checkdb found 0 allocation errors and 0 consistency errors
in the db "A".
What is the problem? Could be an error of SQL Server?
I'll appreciate any help.
Ed. S.
Telematica Inc.

extremely big database file... SQL Server error?

I have the following problem: the size of one of my production databases is
too big(60 gb), comparatively with the rest.
I compare the big db ("A") with another one ("B") that contains the same
tables.
I identify the larger table in db "A" (table X), which has 325000 records,
aprox. In db "B", table X has 88000 records, aprox. (less than 1/3 of the
records in "A")
I ran sp_spaceused (with updateusage = true) and obtain this values for the
total amount of space used by data for table X:
db "A": 1570392
db "B": 48688
the value in "A" is over 3 times the value in "B"... why?
The tables in the two databases are exactly the same (a nonclustered,
unique, primary key index, same constraints, same columns). In both cases,
the table has only one colum with data (the same column) and the rest columns
with "null" value.
Both db are in SQL Server 2000, on the same server. Also, exists a
maintenance plan that run every night that backup and shrink this databases,
but the size of "A" remains the same.
The command dbcc checkdb found 0 allocation errors and 0 consistency errors
in the db "A".
What is the problem? Could be an error of SQL Server?
I'll appreciate any help.
--
Ed. S.
Telematica Inc.Shrinking your database files has the effect of fragmenting your indexes.
If you're always shrinking files, then you're shooting yourself in the foot.
Leave the file as big as necessary and rebuild your indexes.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"byteman" <byteman@.discussions.microsoft.com> wrote in message
news:40A188D8-05F2-4008-BE43-A41510FE83A0@.microsoft.com...
I have the following problem: the size of one of my production databases is
too big(60 gb), comparatively with the rest.
I compare the big db ("A") with another one ("B") that contains the same
tables.
I identify the larger table in db "A" (table X), which has 325000 records,
aprox. In db "B", table X has 88000 records, aprox. (less than 1/3 of the
records in "A")
I ran sp_spaceused (with updateusage = true) and obtain this values for the
total amount of space used by data for table X:
db "A": 1570392
db "B": 48688
the value in "A" is over 3 times the value in "B"... why?
The tables in the two databases are exactly the same (a nonclustered,
unique, primary key index, same constraints, same columns). In both cases,
the table has only one colum with data (the same column) and the rest
columns
with "null" value.
Both db are in SQL Server 2000, on the same server. Also, exists a
maintenance plan that run every night that backup and shrink this databases,
but the size of "A" remains the same.
The command dbcc checkdb found 0 allocation errors and 0 consistency errors
in the db "A".
What is the problem? Could be an error of SQL Server?
I'll appreciate any help.
--
Ed. S.
Telematica Inc.

extremely big database file... SQL Server error?

I have the following problem: the size of one of my production databases is
too big(60 gb), comparatively with the rest.
I compare the big db ("A") with another one ("B") that contains the same
tables.
I identify the larger table in db "A" (table X), which has 325000 records,
aprox. In db "B", table X has 88000 records, aprox. (less than 1/3 of the
records in "A")
I ran sp_spaceused (with updateusage = true) and obtain this values for the
total amount of space used by data for table X:
db "A": 1570392
db "B": 48688
the value in "A" is over 3 times the value in "B"... why?
The tables in the two databases are exactly the same (a nonclustered,
unique, primary key index, same constraints, same columns). In both cases,
the table has only one colum with data (the same column) and the rest column
s
with "null" value.
Both db are in SQL Server 2000, on the same server. Also, exists a
maintenance plan that run every night that backup and shrink this databases,
but the size of "A" remains the same.
The command dbcc checkdb found 0 allocation errors and 0 consistency errors
in the db "A".
What is the problem? Could be an error of SQL Server?
I'll appreciate any help.
--
Ed. S.
Telematica Inc.Shrinking your database files has the effect of fragmenting your indexes.
If you're always shrinking files, then you're shooting yourself in the foot.
Leave the file as big as necessary and rebuild your indexes.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"byteman" <byteman@.discussions.microsoft.com> wrote in message
news:40A188D8-05F2-4008-BE43-A41510FE83A0@.microsoft.com...
I have the following problem: the size of one of my production databases is
too big(60 gb), comparatively with the rest.
I compare the big db ("A") with another one ("B") that contains the same
tables.
I identify the larger table in db "A" (table X), which has 325000 records,
aprox. In db "B", table X has 88000 records, aprox. (less than 1/3 of the
records in "A")
I ran sp_spaceused (with updateusage = true) and obtain this values for the
total amount of space used by data for table X:
db "A": 1570392
db "B": 48688
the value in "A" is over 3 times the value in "B"... why?
The tables in the two databases are exactly the same (a nonclustered,
unique, primary key index, same constraints, same columns). In both cases,
the table has only one colum with data (the same column) and the rest
columns
with "null" value.
Both db are in SQL Server 2000, on the same server. Also, exists a
maintenance plan that run every night that backup and shrink this databases,
but the size of "A" remains the same.
The command dbcc checkdb found 0 allocation errors and 0 consistency errors
in the db "A".
What is the problem? Could be an error of SQL Server?
I'll appreciate any help.
--
Ed. S.
Telematica Inc.