Database has grown too big (5 GB)

Hello
My database should have a normal size on about 500-1000 mb, but it now has
the size of 5 gb. I am talking about the MDF file, not the log file.
I think it is because of a wrong setup of the maintenance plan. Now I have
altered the optimization and checked "remove unused space from database
files", so it is set to shrink database when it grows beyond 50 Mb.
So do you think that next time the maintenance plan runs the Optimization,
the database size will be set to normal ?
I use MSSQL 2000, service pack 3, MDAC 2.8
\AndersIt depends on how much space is being used. Run a sp_spaceused inside the
database name. The reserved number (which equals data + log+ unused) is
roughly how much space to expect from shrinking, which can also be
accomplished by running dbcc shrinkfile (<data name>, 0) -- see SQL Server
Books online for details (which can be downloaded for free from Microsoft's
SQL site -- http://www.microsoft.com/sql)
--
*******************************************************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
*******************************************************************
"anders" <a.vindal@.techotel.dk> wrote in message
news:%23hSDU9I$DHA.2636@.TK2MSFTNGP09.phx.gbl...
> Hello
> My database should have a normal size on about 500-1000 mb, but it now has
> the size of 5 gb. I am talking about the MDF file, not the log file.
> I think it is because of a wrong setup of the maintenance plan. Now I have
> altered the optimization and checked "remove unused space from database
> files", so it is set to shrink database when it grows beyond 50 Mb.
> So do you think that next time the maintenance plan runs the Optimization,
> the database size will be set to normal ?
> I use MSSQL 2000, service pack 3, MDAC 2.8
>
>
> \Anders
>|||If after shrinking the DB, you see the same problem, then try the following:
Do you have clustered index on your tables? If not, add and see if you see
the change.
"anders" <a.vindal@.techotel.dk> wrote in message
news:%23hSDU9I$DHA.2636@.TK2MSFTNGP09.phx.gbl...
> Hello
> My database should have a normal size on about 500-1000 mb, but it now has
> the size of 5 gb. I am talking about the MDF file, not the log file.
> I think it is because of a wrong setup of the maintenance plan. Now I have
> altered the optimization and checked "remove unused space from database
> files", so it is set to shrink database when it grows beyond 50 Mb.
> So do you think that next time the maintenance plan runs the Optimization,
> the database size will be set to normal ?
> I use MSSQL 2000, service pack 3, MDAC 2.8
>
>
> \Anders
>

Database has grown too big (5 GB)

Hello
My database should have a normal size on about 500-1000 mb, but it now has
the size of 5 gb. I am talking about the MDF file, not the log file.
I think it is because of a wrong setup of the maintenance plan. Now I have
altered the optimization and checked "remove unused space from database
files", so it is set to shrink database when it grows beyond 50 Mb.
So do you think that next time the maintenance plan runs the Optimization,
the database size will be set to normal ?
I use MSSQL 2000, service pack 3, MDAC 2.8
\AndersIt depends on how much space is being used. Run a sp_spaceused inside the
database name. The reserved number (which equals data + log+ unused) is
roughly how much space to expect from shrinking, which can also be
accomplished by running dbcc shrinkfile (<data name>, 0) -- see SQL Server
Books online for details (which can be downloaded for free from Microsoft's
SQL site -- http://www.microsoft.com/sql)
****************************************
***************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
****************************************
***************************
"anders" <a.vindal@.techotel.dk> wrote in message
news:%23hSDU9I$DHA.2636@.TK2MSFTNGP09.phx.gbl...
> Hello
> My database should have a normal size on about 500-1000 mb, but it now has
> the size of 5 gb. I am talking about the MDF file, not the log file.
> I think it is because of a wrong setup of the maintenance plan. Now I have
> altered the optimization and checked "remove unused space from database
> files", so it is set to shrink database when it grows beyond 50 Mb.
> So do you think that next time the maintenance plan runs the Optimization,
> the database size will be set to normal ?
> I use MSSQL 2000, service pack 3, MDAC 2.8
>
>
> \Anders
>|||If after shrinking the DB, you see the same problem, then try the following:
Do you have clustered index on your tables? If not, add and see if you see
the change.
"anders" <a.vindal@.techotel.dk> wrote in message
news:%23hSDU9I$DHA.2636@.TK2MSFTNGP09.phx.gbl...
> Hello
> My database should have a normal size on about 500-1000 mb, but it now has
> the size of 5 gb. I am talking about the MDF file, not the log file.
> I think it is because of a wrong setup of the maintenance plan. Now I have
> altered the optimization and checked "remove unused space from database
> files", so it is set to shrink database when it grows beyond 50 Mb.
> So do you think that next time the maintenance plan runs the Optimization,
> the database size will be set to normal ?
> I use MSSQL 2000, service pack 3, MDAC 2.8
>
>
> \Anders
>

Database File Size

I have a Database which when I Right Click and go to Properties size is 52 GB

But the Size of MDF + NDF Files is 25 + 7 = 32 GB. Log file Size is 20 GB. So I am thinking -- Properties Size of DB includes size of Log Files too -- is that correct?

But when I do a Full Backup the .bak File Size is 26 GB -- does the Full Backup Shrink a DB ?

I thot Full Backup only Shrink the Log Files and could not find anywhere in BOL where it says BACKUP shrinks the empty space in Database -- can somebody confirm this?

Hi JaguarRDA,

Its because backup is always lower than original size of database, because backup copy only used pages.

|||

Yeah -- also Backups dont touch the Log Files -- that was a wrong statement on my behalf that they shrunk the log files

|||

JaguarRDA283361 wrote:

Yeah -- also Backups dont touch the Log Files --

Depends on the type of "recovery model", see

sql

database errors on dbcc

We had a failure of a machine, but got the .mdf file off only. The .ldf was
unrecoverable. After restoring it, and running dbcc checkdb i get these
errors (although all seems ok in the database otherwise)
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:46098). The PageId in the
page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:46099). The PageId in the
page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:46100). The PageId in the
page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:46101). The PageId in the
page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:46102). The PageId in the
page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:46103). The PageId in the
page header = (0:0).
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1010102639, index ID 255: Page (1:46097) could not be processed.
See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 1010102639, index ID 255, page (1:46097). Test
(m_freeCnt == freeCnt) failed. Values are 0 and 8094.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 1010102639, index ID 255, page (1:46097). Test
(emptySlotCnt == 0) failed. Values are 1 and 0.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1010102639, index ID 255: Page (1:46098) could not be processed.
See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1010102639, index ID 255: Page (1:46099) could not be processed.
See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1010102639, index ID 255: Page (1:46100) could not be processed.
See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1010102639, index ID 255: Page (1:46101) could not be processed.
See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1010102639, index ID 255: Page (1:46102) could not be processed.
See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1010102639, index ID 255: Page (1:46103) could not be processed.
See other errors for details.
DBCC results for myDatabase.
CHECKDB found 0 allocation errors and 6 consistency errors not associated
with any single object.
_____
DC G
Hi,
First and best option is restore from the latest backup into a new database
and restore the transaction logs, because the data consistency would have
been
lost since the LDF file is lost/corrupted.
If you do not have any backup files then try the below options:
DBCC CHECKDB('dbname','REPAIR_REBUILD ')
if you still have errors then try using
DBCC CHECKDB('DBNAME','REPAIR_ALLOW_DATA_LOSS')
The above command can be ran only in single user mode and will result in
data loss. so the comamnd is not at all recommended.
Thanks
Hari
MCDBA
"DC Gringo" <dcgringo@.visiontechnology.net> wrote in message
news:OMuhAlDnEHA.1800@.TK2MSFTNGP15.phx.gbl...
> We had a failure of a machine, but got the .mdf file off only. The .ldf
> was
> unrecoverable. After restoring it, and running dbcc checkdb i get these
> errors (although all seems ok in the database otherwise)
>
>
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (1:46098). The PageId in the
> page header = (0:0).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (1:46099). The PageId in the
> page header = (0:0).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (1:46100). The PageId in the
> page header = (0:0).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (1:46101). The PageId in the
> page header = (0:0).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (1:46102). The PageId in the
> page header = (0:0).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (1:46103). The PageId in the
> page header = (0:0).
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1010102639, index ID 255: Page (1:46097) could not be processed.
> See other errors for details.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 1010102639, index ID 255, page (1:46097). Test
> (m_freeCnt == freeCnt) failed. Values are 0 and 8094.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 1010102639, index ID 255, page (1:46097). Test
> (emptySlotCnt == 0) failed. Values are 1 and 0.
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1010102639, index ID 255: Page (1:46098) could not be processed.
> See other errors for details.
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1010102639, index ID 255: Page (1:46099) could not be processed.
> See other errors for details.
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1010102639, index ID 255: Page (1:46100) could not be processed.
> See other errors for details.
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1010102639, index ID 255: Page (1:46101) could not be processed.
> See other errors for details.
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1010102639, index ID 255: Page (1:46102) could not be processed.
> See other errors for details.
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1010102639, index ID 255: Page (1:46103) could not be processed.
> See other errors for details.
> DBCC results for myDatabase.
> CHECKDB found 0 allocation errors and 6 consistency errors not associated
> with any single object.
> --
> _____
> DC G
>
|||The most recent update of Books Online has explicit recommendation for most error numbers. Search
for your error numbers in Books Online and see what the recommendations are. Also, you might want to
read:
http://www.karaszi.com/SQLServer/inf...suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DC Gringo" <dcgringo@.visiontechnology.net> wrote in message
news:OMuhAlDnEHA.1800@.TK2MSFTNGP15.phx.gbl...
> We had a failure of a machine, but got the .mdf file off only. The .ldf was
> unrecoverable. After restoring it, and running dbcc checkdb i get these
> errors (although all seems ok in the database otherwise)
>
>
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (1:46098). The PageId in the
> page header = (0:0).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (1:46099). The PageId in the
> page header = (0:0).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (1:46100). The PageId in the
> page header = (0:0).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (1:46101). The PageId in the
> page header = (0:0).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (1:46102). The PageId in the
> page header = (0:0).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (1:46103). The PageId in the
> page header = (0:0).
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1010102639, index ID 255: Page (1:46097) could not be processed.
> See other errors for details.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 1010102639, index ID 255, page (1:46097). Test
> (m_freeCnt == freeCnt) failed. Values are 0 and 8094.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 1010102639, index ID 255, page (1:46097). Test
> (emptySlotCnt == 0) failed. Values are 1 and 0.
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1010102639, index ID 255: Page (1:46098) could not be processed.
> See other errors for details.
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1010102639, index ID 255: Page (1:46099) could not be processed.
> See other errors for details.
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1010102639, index ID 255: Page (1:46100) could not be processed.
> See other errors for details.
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1010102639, index ID 255: Page (1:46101) could not be processed.
> See other errors for details.
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1010102639, index ID 255: Page (1:46102) could not be processed.
> See other errors for details.
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1010102639, index ID 255: Page (1:46103) could not be processed.
> See other errors for details.
> DBCC results for myDatabase.
> CHECKDB found 0 allocation errors and 6 consistency errors not associated
> with any single object.
> --
> _____
> DC G
>

database errors on dbcc

We had a failure of a machine, but got the .mdf file off only. The .ldf was
unrecoverable. After restoring it, and running dbcc checkdb i get these
errors (although all seems ok in the database otherwise)
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:46098). The PageId in the
page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:46099). The PageId in the
page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:46100). The PageId in the
page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:46101). The PageId in the
page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:46102). The PageId in the
page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:46103). The PageId in the
page header = (0:0).
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1010102639, index ID 255: Page (1:46097) could not be processed.
See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 1010102639, index ID 255, page (1:46097). Test
(m_freeCnt == freeCnt) failed. Values are 0 and 8094.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 1010102639, index ID 255, page (1:46097). Test
(emptySlotCnt == 0) failed. Values are 1 and 0.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1010102639, index ID 255: Page (1:46098) could not be processed.
See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1010102639, index ID 255: Page (1:46099) could not be processed.
See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1010102639, index ID 255: Page (1:46100) could not be processed.
See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1010102639, index ID 255: Page (1:46101) could not be processed.
See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1010102639, index ID 255: Page (1:46102) could not be processed.
See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1010102639, index ID 255: Page (1:46103) could not be processed.
See other errors for details.
DBCC results for myDatabase.
CHECKDB found 0 allocation errors and 6 consistency errors not associated
with any single object.
--
_____
DC GHi,
First and best option is restore from the latest backup into a new database
and restore the transaction logs, because the data consistency would have
been
lost since the LDF file is lost/corrupted.
If you do not have any backup files then try the below options:
DBCC CHECKDB('dbname','REPAIR_REBUILD ')
if you still have errors then try using
DBCC CHECKDB('DBNAME','REPAIR_ALLOW_DATA_LOSS')
The above command can be ran only in single user mode and will result in
data loss. so the comamnd is not at all recommended.
Thanks
Hari
MCDBA
"DC Gringo" <dcgringo@.visiontechnology.net> wrote in message
news:OMuhAlDnEHA.1800@.TK2MSFTNGP15.phx.gbl...
> We had a failure of a machine, but got the .mdf file off only. The .ldf
> was
> unrecoverable. After restoring it, and running dbcc checkdb i get these
> errors (although all seems ok in the database otherwise)
>
>
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (1:46098). The PageId in the
> page header = (0:0).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (1:46099). The PageId in the
> page header = (0:0).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (1:46100). The PageId in the
> page header = (0:0).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (1:46101). The PageId in the
> page header = (0:0).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (1:46102). The PageId in the
> page header = (0:0).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (1:46103). The PageId in the
> page header = (0:0).
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1010102639, index ID 255: Page (1:46097) could not be processed.
> See other errors for details.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 1010102639, index ID 255, page (1:46097). Test
> (m_freeCnt == freeCnt) failed. Values are 0 and 8094.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 1010102639, index ID 255, page (1:46097). Test
> (emptySlotCnt == 0) failed. Values are 1 and 0.
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1010102639, index ID 255: Page (1:46098) could not be processed.
> See other errors for details.
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1010102639, index ID 255: Page (1:46099) could not be processed.
> See other errors for details.
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1010102639, index ID 255: Page (1:46100) could not be processed.
> See other errors for details.
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1010102639, index ID 255: Page (1:46101) could not be processed.
> See other errors for details.
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1010102639, index ID 255: Page (1:46102) could not be processed.
> See other errors for details.
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1010102639, index ID 255: Page (1:46103) could not be processed.
> See other errors for details.
> DBCC results for myDatabase.
> CHECKDB found 0 allocation errors and 6 consistency errors not associated
> with any single object.
> --
> _____
> DC G
>|||The most recent update of Books Online has explicit recommendation for most error numbers. Search
for your error numbers in Books Online and see what the recommendations are. Also, you might want to
read:
http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DC Gringo" <dcgringo@.visiontechnology.net> wrote in message
news:OMuhAlDnEHA.1800@.TK2MSFTNGP15.phx.gbl...
> We had a failure of a machine, but got the .mdf file off only. The .ldf was
> unrecoverable. After restoring it, and running dbcc checkdb i get these
> errors (although all seems ok in the database otherwise)
>
>
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (1:46098). The PageId in the
> page header = (0:0).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (1:46099). The PageId in the
> page header = (0:0).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (1:46100). The PageId in the
> page header = (0:0).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (1:46101). The PageId in the
> page header = (0:0).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (1:46102). The PageId in the
> page header = (0:0).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (1:46103). The PageId in the
> page header = (0:0).
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1010102639, index ID 255: Page (1:46097) could not be processed.
> See other errors for details.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 1010102639, index ID 255, page (1:46097). Test
> (m_freeCnt == freeCnt) failed. Values are 0 and 8094.
> Server: Msg 8939, Level 16, State 1, Line 1
> Table error: Object ID 1010102639, index ID 255, page (1:46097). Test
> (emptySlotCnt == 0) failed. Values are 1 and 0.
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1010102639, index ID 255: Page (1:46098) could not be processed.
> See other errors for details.
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1010102639, index ID 255: Page (1:46099) could not be processed.
> See other errors for details.
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1010102639, index ID 255: Page (1:46100) could not be processed.
> See other errors for details.
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1010102639, index ID 255: Page (1:46101) could not be processed.
> See other errors for details.
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1010102639, index ID 255: Page (1:46102) could not be processed.
> See other errors for details.
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1010102639, index ID 255: Page (1:46103) could not be processed.
> See other errors for details.
> DBCC results for myDatabase.
> CHECKDB found 0 allocation errors and 6 consistency errors not associated
> with any single object.
> --
> _____
> DC G
>sql

database detach/attach

An original database (eg A.mdf) is imported/exported to a different server as named B.mdf. The detach of A.mdf was unitentionally not done. It seems that both the database were updated.

A.mdf - 123000KB 25/12/2003
A_log.ldf - 14000KB 25/12/2003
B.mdf - 67000KB 25/12/2003
B_log.ldf - 1024KB 25/12/2003

In this case, which mdf & ldf should be the correct database?
If I I remove A.mdf database by "detach", will B.mdf database work?

SQL server 2000 and SP3 installed.

Assistance is appreciatedyou are exporting a database file and getting different sizes .. seems strange

sp_helpdb 'database_name'

on both servers

Copy and paste results over here

That might help|||The original database (eg A.mdf) is export/imported into a different server and the database is named as B.mdf. The 'detach' command from of the original database (A.mdf) was unintentionally
not done. As I have unintentionally did not do a 'detach' of the old database, both the database seems to be updated (as indicated from the date stamp) AFTER I use the application (VB6 business application) a day or two.

Once an import/export is done, is the detach of the old database compulsory?. In SQL Server Enterprise Manager->(Select say, A database)->All Task-> Detach database

A.mdf - 123,000KB, 2/1/2004 1142am
A_log.ldf - 10,000 KB, 2/1/2004 0133pm
B.mdf - 67,000KB, 2/1/2004 0902am
B_log.ldf - 1024KB, 2/1/2004 0902am

Exporting of the database is ok before the I started using the application.

How can I correct this? Advise is appreciated.

Regards,
Brian

Originally posted by Enigma
you are exporting a database file and getting different sizes .. seems strange

sp_helpdb 'database_name'

on both servers

Copy and paste results over here

That might help|||Are both databases supposed to be active - please describe more as to why you have 2 ? Where are you retrieving the database sizes ? Use sp_spaceused and post the results. Is the real question, what has changed between the 2 databases and how to find those records ?|||Originally posted by rnealejr
Are both databases supposed to be active - please describe more as to why you have 2 ? Where are you retrieving the database sizes ? Use sp_spaceused and post the results. Is the real question, what has changed between the 2 databases and how to find those records ?

the A.mdf and its log is the active database. 2 databases are active as 1 is live/production environment and the other is test/QA environment. the database sizes is seen from the window explorer.|||What needs to be corrected ? You keep mentioning detaching the A database - but why ? Is A production or test ? You need to run the stored procedure sp_spaceused to determine actual space used. Are you concerned that there might be activity on both databases - and you are not sure why ? Are you exporting the data from the production to the test periodically ? Since your log file for B does not appear to have grown, it appears that either minimal or no user activity is occuring on the B database (unless you are backing up the database/transaction log for B).|||Originally posted by rnealejr
What needs to be corrected ? You keep mentioning detaching the A database - but why ? Is A production or test ? You need to run the stored procedure sp_spaceused to determine actual space used. Are you concerned that there might be activity on both databases - and you are not sure why ? Are you exporting the data from the production to the test periodically ? Since your log file for B does not appear to have grown, it appears that either minimal or no user activity is occuring on the B database (unless you are backing up the database/transaction log for B).

if I were to remove (detach) the test/QA data (denotes by B.mdf and its log), will this cause any problem with production (denotes by A.mdf and its log) database?. I noted that the date/time has been updated on the same day even though my ODBC (live application) uses production database which is A.mdf.

What happen if a detach is not done after import/export from production to test/QA environment?. Do the production database get updated (by right it should since ODBC points to production) along with the test database?. I am concern that activities may be updated in the test environment - yes, I am not sure and I would like some feedback.|||There should be no connection between your A database and your B database (unless you have triggers/replication between the 2) - other than the fact that you exported the data from A to B. What method did you use to export the data from A to B ? Several activities can cause the date/time to change on B, but since the log is only 1 meg (the minimum) I would suspect that nothing has really changed on B. But if you are really concerned - dump the test database and copy the database from prod back to test.|||Originally posted by rnealejr
There should be no connection between your A database and your B database (unless you have triggers/replication between the 2) - other than the fact that you exported the data from A to B. What method did you use to export the data from A to B ? Several activities can cause the date/time to change on B, but since the log is only 1 meg (the minimum) I would suspect that nothing has really changed on B. But if you are really concerned - dump the test database and copy the database from prod back to test.

I agree with you. All I did was to simply import/export. no DTS used.
The strange thing I noted was that after the import/export from production to test (and no "detach" was done on test database), I ran the application and did the update. I found was that the test database time/date was updated AND the data went into production and the mdf and log of production did not change (seen from explorer).

Thanks for your feedback.|||It was probably just coincidence ... or a poltergeist (oooohhhhhh - supposed to be a spooky sound)

database creation durations

Trying to bring up a server at a DR site and running into what I think are
excessive durations for .mdf / .ndf file creation. It takes SQL server 1.5
hours to grab each 50 GB file. Disk subsystem at the site is a local
Ultra-Wide SCSI. RAID5 array.
Is 1.5 hours for a 50 GB excessive? What metrics can I use to monitor?
Oscar ElleseffIs this a 3 disk Raid 5? If so that is the worst performing Raid array you
can have for write intensive operations. Depending on the processors and
disks this may not be out of the question. If you upgrade to SQL2005 you
can take advantage of the zero-initialization feature for data files and
this will be cut down to probably a minute or less.
--
Andrew J. Kelly SQL MVP
"Oscar" <Oscar@.discussions.microsoft.com> wrote in message
news:B9334E0E-8AF1-4C79-9E0D-5E909C541E54@.microsoft.com...
> Trying to bring up a server at a DR site and running into what I think are
> excessive durations for .mdf / .ndf file creation. It takes SQL server
> 1.5
> hours to grab each 50 GB file. Disk subsystem at the site is a local
> Ultra-Wide SCSI. RAID5 array.
> Is 1.5 hours for a 50 GB excessive? What metrics can I use to monitor?
>
> Oscar Elleseff
>
>
>

database creation durations

Trying to bring up a server at a DR site and running into what I think are
excessive durations for .mdf / .ndf file creation. It takes SQL server 1.5
hours to grab each 50 GB file. Disk subsystem at the site is a local
Ultra-Wide SCSI. RAID5 array.
Is 1.5 hours for a 50 GB excessive? What metrics can I use to monitor?
Oscar Elleseff
Is this a 3 disk Raid 5? If so that is the worst performing Raid array you
can have for write intensive operations. Depending on the processors and
disks this may not be out of the question. If you upgrade to SQL2005 you
can take advantage of the zero-initialization feature for data files and
this will be cut down to probably a minute or less.
Andrew J. Kelly SQL MVP
"Oscar" <Oscar@.discussions.microsoft.com> wrote in message
news:B9334E0E-8AF1-4C79-9E0D-5E909C541E54@.microsoft.com...
> Trying to bring up a server at a DR site and running into what I think are
> excessive durations for .mdf / .ndf file creation. It takes SQL server
> 1.5
> hours to grab each 50 GB file. Disk subsystem at the site is a local
> Ultra-Wide SCSI. RAID5 array.
> Is 1.5 hours for a 50 GB excessive? What metrics can I use to monitor?
>
> Oscar Elleseff
>
>
>

database creation durations

Trying to bring up a server at a DR site and running into what I think are
excessive durations for .mdf / .ndf file creation. It takes SQL server 1.5
hours to grab each 50 GB file. Disk subsystem at the site is a local
Ultra-Wide SCSI. RAID5 array.
Is 1.5 hours for a 50 GB excessive? What metrics can I use to monitor?
Oscar ElleseffIs this a 3 disk Raid 5? If so that is the worst performing Raid array you
can have for write intensive operations. Depending on the processors and
disks this may not be out of the question. If you upgrade to SQL2005 you
can take advantage of the zero-initialization feature for data files and
this will be cut down to probably a minute or less.
Andrew J. Kelly SQL MVP
"Oscar" <Oscar@.discussions.microsoft.com> wrote in message
news:B9334E0E-8AF1-4C79-9E0D-5E909C541E54@.microsoft.com...
> Trying to bring up a server at a DR site and running into what I think are
> excessive durations for .mdf / .ndf file creation. It takes SQL server
> 1.5
> hours to grab each 50 GB file. Disk subsystem at the site is a local
> Ultra-Wide SCSI. RAID5 array.
> Is 1.5 hours for a 50 GB excessive? What metrics can I use to monitor?
>
> Oscar Elleseff
>
>
>