dbcc shrinkdatabase vs dbcc shrinkfile

Hello all,
I just chopped off millions of stale records from a table bringing the
row count from 85M to 5 and also freeing up around 30GB of data. What
is the safest way to shrink the database to reclaim this freed up space?Hello,
I recommend you to use DBCC SHRINKFILE to shrink the MDF and LDF file
seperately. Before executing the SHRINKFILE use the command SP_SPACEUSED
to identify the unallocated space.
Thanks
Hari
"AF" <af.at.work@.gmail.com> wrote in message
news:1168388869.505893.31950@.i56g2000hsf.googlegroups.com...
> Hello all,
> I just chopped off millions of stale records from a table bringing the
> row count from 85M to 5 and also freeing up around 30GB of data. What
> is the safest way to shrink the database to reclaim this freed up space?
>|||Hari Prasad wrote:
> Hello,
> I recommend you to use DBCC SHRINKFILE to shrink the MDF and LDF file
> seperately. Before executing the SHRINKFILE use the command SP_SPACEUSED
> to identify the unallocated space.
> Thanks
> Hari
>
> "AF" <af.at.work@.gmail.com> wrote in message
> news:1168388869.505893.31950@.i56g2000hsf.googlegroups.com...
> > Hello all,
> >
> > I just chopped off millions of stale records from a table bringing the
> > row count from 85M to 5 and also freeing up around 30GB of data. What
> > is the safest way to shrink the database to reclaim this freed up space?
> >
Thanks Hari, just curious -- why separately?
These are the results of sp_spaceused but how do I interpret these to
use dbcc shrinkfile?
name size unallocated space
mydb 39536.81 MB 504.16 MB
reserved data index size unused
8589984 KB 7921840 KB 647688 KB 20456 KB|||AF wrote:
> Hari Prasad wrote:
> > Hello,
> >
> > I recommend you to use DBCC SHRINKFILE to shrink the MDF and LDF file
> > seperately. Before executing the SHRINKFILE use the command SP_SPACEUSED
> > to identify the unallocated space.
> >
> > Thanks
> > Hari
> >
> >
> > "AF" <af.at.work@.gmail.com> wrote in message
> > news:1168388869.505893.31950@.i56g2000hsf.googlegroups.com...
> > > Hello all,
> > >
> > > I just chopped off millions of stale records from a table bringing the
> > > row count from 85M to 5 and also freeing up around 30GB of data. What
> > > is the safest way to shrink the database to reclaim this freed up space?
> > >
> Thanks Hari, just curious -- why separately?
> These are the results of sp_spaceused but how do I interpret these to
> use dbcc shrinkfile?
> name size unallocated space
> mydb 39536.81 MB 504.16 MB
> reserved data index size unused
> 8589984 KB 7921840 KB 647688 KB 20456 KB
This is the usage reported from SSEM:
mydb.mdf
7721MB USED
1172MB FREE
mydb.ldf
158MB USED
30486MB FREEsql

dbcc showcontig with tablersults

DBCC SHOWCONTIG WITH TABLERESULTS will show a ROW column for an index's row count. However, on an important table with quite a few indexes that I am using as a baseline, ROW is NULL for all indexes. Why? BOL definines ROWS as:-

"Number of data or index records at that level of the index. For a heap, this is the number of data records in the entire heap."

By the way, if I select from sysindexes for a one of the above indexes, there is a correct rowcount for the index.

Any ideas?

CliveI found it. I was using the 'WITH FAST' parameter. Without FAST, it returns the row count.

Clive

DBCC PAGE

Can anyone tell me how to get the FileNumber & PageNumber
that a particualar row of data is physically stored on?
I need to check the physical contents of row using DBCC
PAGE but this needs to be given the FileNumber &
PageNumber as parameters.
Steve Morgan
MCP, MCDBA, MCT
Reach Telecom SQL Server DBA
Please reply to the group, not to me!look at www.sqlmag.com for InstantDoc #8097.
"Steve Morgan" <steve.morgan@.reachtelecom.co.ukNOSPAM> wrote in message
news:008301c37882$cc197d50$a401280a@.phx.gbl...
> Can anyone tell me how to get the FileNumber & PageNumber
> that a particualar row of data is physically stored on?
> I need to check the physical contents of row using DBCC
> PAGE but this needs to be given the FileNumber &
> PageNumber as parameters.
>
> Steve Morgan
> MCP, MCDBA, MCT
> Reach Telecom SQL Server DBA
> Please reply to the group, not to me!|||There is no direct way to map a row to a page number.
As the author of SQL Mag's article 8097, I can say that usually I use DBCC
IND to list all the file number, page number values for a table, and then I
start guessing. If there is a clustered index, you can narrow it down pretty
well, and DBCC IND also shows you the next and previous page so you can
build the linked list of ordered pages.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Steve Morgan" <steve.morgan@.reachtelecom.co.ukNOSPAM> wrote in message
news:008301c37882$cc197d50$a401280a@.phx.gbl...
> Can anyone tell me how to get the FileNumber & PageNumber
> that a particualar row of data is physically stored on?
> I need to check the physical contents of row using DBCC
> PAGE but this needs to be given the FileNumber &
> PageNumber as parameters.
>
> Steve Morgan
> MCP, MCDBA, MCT
> Reach Telecom SQL Server DBA
> Please reply to the group, not to me!|||Go to www.sqlfe.com and download the tool. You will find it easy to find
the pages allocated to your table.
--
Barry McAuslin
"Steve Morgan" <steve.morgan@.reachtelecom.co.ukNOSPAM> wrote in message
news:008301c37882$cc197d50$a401280a@.phx.gbl...
> Can anyone tell me how to get the FileNumber & PageNumber
> that a particualar row of data is physically stored on?
> I need to check the physical contents of row using DBCC
> PAGE but this needs to be given the FileNumber &
> PageNumber as parameters.
>
> Steve Morgan
> MCP, MCDBA, MCT
> Reach Telecom SQL Server DBA
> Please reply to the group, not to me!|||Interesting tool!
But it still might be a choice like between EM and QA, GUI or text command.
I can find the pages that BELONG to a table really quickly with DBCC IND.
But does this tool answer the poster's original question... how to find the
page a particular row is on?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Barry McAuslin" <barry_mcauslin@.yahoo.com.nospam> wrote in message
news:OSERX5OeDHA.1680@.TK2MSFTNGP09.phx.gbl...
> Go to www.sqlfe.com and download the tool. You will find it easy to find
> the pages allocated to your table.
> --
> Barry McAuslin
> "Steve Morgan" <steve.morgan@.reachtelecom.co.ukNOSPAM> wrote in message
> news:008301c37882$cc197d50$a401280a@.phx.gbl...
> > Can anyone tell me how to get the FileNumber & PageNumber
> > that a particualar row of data is physically stored on?
> >
> > I need to check the physical contents of row using DBCC
> > PAGE but this needs to be given the FileNumber &
> > PageNumber as parameters.
> >
> >
> >
> > Steve Morgan
> > MCP, MCDBA, MCT
> > Reach Telecom SQL Server DBA
> >
> > Please reply to the group, not to me!
>|||Not directly, but it is more of a follow on to your post. If you view the
indexes, select the table you are after from the list, then the extents that
the table are in will be highlighted. Double clicking on the extent will
give you the DBCC PAGE output from the pages in the extent.
I got the idea from a tool by another database vendor, thought I would like
that for MS SQL. I couldn't find anything! So after reading your book and
working out how to put it all together I decided to write it in my spare
time. Excellent book by the way.
Thanks
--
Barry McAuslin
http://www.sqlfe.com
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OEuTSGTeDHA.2432@.TK2MSFTNGP09.phx.gbl...
> Interesting tool!
> But it still might be a choice like between EM and QA, GUI or text
command.
> I can find the pages that BELONG to a table really quickly with DBCC IND.
> But does this tool answer the poster's original question... how to find
the
> page a particular row is on?
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Barry McAuslin" <barry_mcauslin@.yahoo.com.nospam> wrote in message
> news:OSERX5OeDHA.1680@.TK2MSFTNGP09.phx.gbl...
> > Go to www.sqlfe.com and download the tool. You will find it easy to
find
> > the pages allocated to your table.
> >
> > --
> > Barry McAuslin
> >
> > "Steve Morgan" <steve.morgan@.reachtelecom.co.ukNOSPAM> wrote in message
> > news:008301c37882$cc197d50$a401280a@.phx.gbl...
> > > Can anyone tell me how to get the FileNumber & PageNumber
> > > that a particualar row of data is physically stored on?
> > >
> > > I need to check the physical contents of row using DBCC
> > > PAGE but this needs to be given the FileNumber &
> > > PageNumber as parameters.
> > >
> > >
> > >
> > > Steve Morgan
> > > MCP, MCDBA, MCT
> > > Reach Telecom SQL Server DBA
> > >
> > > Please reply to the group, not to me!
> >
> >
>|||Interesting idea, but maybe you can fix the tool, so it will also work
against a case sensitive server. When I switch to the log I am getting a
FileID not found, looks like you are creating temp tables with very
inconsistent naming and later query for the wrong column name (case wise).
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2003 All rights reserved.
"Barry McAuslin" <barry_mcauslin@.yahoo.com.nospam> wrote in message
news:OSERX5OeDHA.1680@.TK2MSFTNGP09.phx.gbl...
> Go to www.sqlfe.com and download the tool. You will find it easy to find
> the pages allocated to your table.
> --
> Barry McAuslin
> "Steve Morgan" <steve.morgan@.reachtelecom.co.ukNOSPAM> wrote in message
> news:008301c37882$cc197d50$a401280a@.phx.gbl...
> > Can anyone tell me how to get the FileNumber & PageNumber
> > that a particualar row of data is physically stored on?
> >
> > I need to check the physical contents of row using DBCC
> > PAGE but this needs to be given the FileNumber &
> > PageNumber as parameters.
> >
> >
> >
> > Steve Morgan
> > MCP, MCDBA, MCT
> > Reach Telecom SQL Server DBA
> >
> > Please reply to the group, not to me!
>|||Thanks for that. I will have a fix on the web site in a couple of days.
--
Barry McAuslin
Look inside your SQL Server files with SQL File Explorer.
Go to http://www.sqlfe.com for more information.
"Gert E.R. Drapers" <GertD@.Online.SQLDev.Net> wrote in message
news:uKewyL9eDHA.3104@.TK2MSFTNGP11.phx.gbl...
> Interesting idea, but maybe you can fix the tool, so it will also work
> against a case sensitive server. When I switch to the log I am getting a
> FileID not found, looks like you are creating temp tables with very
> inconsistent naming and later query for the wrong column name (case wise).
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> You assume all risk for your use.
> Copyright © SQLDev.Net 1991-2003 All rights reserved.
> "Barry McAuslin" <barry_mcauslin@.yahoo.com.nospam> wrote in message
> news:OSERX5OeDHA.1680@.TK2MSFTNGP09.phx.gbl...
> > Go to www.sqlfe.com and download the tool. You will find it easy to
find
> > the pages allocated to your table.
> >
> > --
> > Barry McAuslin
> >
> > "Steve Morgan" <steve.morgan@.reachtelecom.co.ukNOSPAM> wrote in message
> > news:008301c37882$cc197d50$a401280a@.phx.gbl...
> > > Can anyone tell me how to get the FileNumber & PageNumber
> > > that a particualar row of data is physically stored on?
> > >
> > > I need to check the physical contents of row using DBCC
> > > PAGE but this needs to be given the FileNumber &
> > > PageNumber as parameters.
> > >
> > >
> > >
> > > Steve Morgan
> > > MCP, MCDBA, MCT
> > > Reach Telecom SQL Server DBA
> > >
> > > Please reply to the group, not to me!
> >
> >
>

DBCC CLEANTABLE (SQL Server 2000) question

Hi,
I've got a 40mill row table and I've normalised one of the columns out
to a separate table. I have then dropped the original varchar(100)
column.
I want to reclaim the space as efficiently as possible.
I set recovery model to simple. Then ran DBCC CLEANTABLE with the
batch option set to 10000 rows.
Here I ran into a problem: it seems to block itself... anyone ever
encountered that before?
I then ran it without the batch parameter and it is still running
after 2 hours and the transaction log is still creeping up. Several GB
so far...
Any advice very welcome...
Cheers,
JamesJimLad schrieb:
> Hi,
> I've got a 40mill row table and I've normalised one of the columns out
> to a separate table. I have then dropped the original varchar(100)
> column.
> I want to reclaim the space as efficiently as possible.
> I set recovery model to simple. Then ran DBCC CLEANTABLE with the
> batch option set to 10000 rows.
> Here I ran into a problem: it seems to block itself... anyone ever
> encountered that before?
> I then ran it without the batch parameter and it is still running
> after 2 hours and the transaction log is still creeping up. Several GB
> so far...
> Any advice very welcome...
> Cheers,
> James
>
Instead of CLEANTABLE you could rebuild the indexes with DBCC REINDEX.
This has the same effect but is usually more resource-intensive than
cleantable.
hth
Gregor Stefka|||On May 1, 2:32 pm, Gregor Stefka <ste...@.zuehlke-bieker.de> wrote:
> JimLad schrieb:
>
> > Hi,
> > I've got a 40mill row table and I've normalised one of the columns out
> > to a separate table. I have then dropped the original varchar(100)
> > column.
> > I want to reclaim the space as efficiently as possible.
> > I set recovery model to simple. Then ran DBCC CLEANTABLE with the
> > batch option set to 10000 rows.
> > Here I ran into a problem: it seems to block itself... anyone ever
> > encountered that before?
> > I then ran it without the batch parameter and it is still running
> > after 2 hours and the transaction log is still creeping up. Several GB
> > so far...
> > Any advice very welcome...
> > Cheers,
> > James
> Instead of CLEANTABLE you could rebuild the indexes with DBCC REINDEX.
> This has the same effect but is usually more resource-intensive than
> cleantable.
> hth
> Gregor Stefka- Hide quoted text -
> - Show quoted text -
Hi,
When I say it is blocking itself I mean I'm getting PAGEIOLATCH_EX
waits when I set a value for the batch size. Is this normal? The wait
resource keeps changing, but I'm wondering why this is happening? Can
someone explain this for me?
Without the batch size being set, it took 3.25 hours to run and
created 10GB of log. Seems rather overlong to me. Am I doing something
wrong?
Wouldn't DBCC REINDEX result in even worse performance?
Cheers,
James|||On May 1, 5:41 pm, JimLad <jamesdbi...@.yahoo.co.uk> wrote:
> On May 1, 2:32 pm, Gregor Stefka <ste...@.zuehlke-bieker.de> wrote:
>
>
> > JimLad schrieb:
> > > Hi,
> > > I've got a 40mill row table and I've normalised one of the columns out
> > > to a separate table. I have then dropped the original varchar(100)
> > > column.
> > > I want to reclaim the space as efficiently as possible.
> > > I set recovery model to simple. Then ran DBCC CLEANTABLE with the
> > > batch option set to 10000 rows.
> > > Here I ran into a problem: it seems to block itself... anyone ever
> > > encountered that before?
> > > I then ran it without the batch parameter and it is still running
> > > after 2 hours and the transaction log is still creeping up. Several GB
> > > so far...
> > > Any advice very welcome...
> > > Cheers,
> > > James
> > Instead of CLEANTABLE you could rebuild the indexes with DBCC REINDEX.
> > This has the same effect but is usually more resource-intensive than
> > cleantable.
> > hth
> > Gregor Stefka- Hide quoted text -
> > - Show quoted text -
> Hi,
> When I say it is blocking itself I mean I'm getting PAGEIOLATCH_EX
> waits when I set a value for the batch size. Is this normal? The wait
> resource keeps changing, but I'm wondering why this is happening? Can
> someone explain this for me?
> Without the batch size being set, it took 3.25 hours to run and
> created 10GB of log. Seems rather overlong to me. Am I doing something
> wrong?
> Wouldn't DBCC REINDEX result in even worse performance?
> Cheers,
> James- Hide quoted text -
> - Show quoted text -
Hi,
In answer to my own questions:
PAGEIOLATCH_EX waits don't matter.
Reason for slow performance is due to amount of data written to
transaction log. 12GB is written with filegrowth set to 1MB! Most of
the 3hrs is spent growing the log!
So remembering that the original number of rows is 40mill, then if we
set the batch size on DBCC CLEANTABLE to 4,000,000 then the max size
of the transaction log will be ~1.2GB. This obviously assumes SIMPLE
recovery model.
So now rather than filegrowth in 1MB increments up to 1.2GB, which is
terribly inefficient and time consuming, we set the log size manually
or increase the autogrow value.
*************************************************************************************************
--This script temporarily changes recovery model to SIMPLE.
--A FULL backup must be taken PRIOR TO AND AFTER executing this
script...
--This script is not transaction safe. On error, RESTORE FROM
BACKUP...
--
*************************************************************************************************
SELECT DATABASEPROPERTYEX('db','Recovery') AS [Initial Recovery Model
(script leaves db in FULL recovery model)]
GO
-- Allow log to be truncated for these large amount of changes.
ALTER DATABASE [db] SET RECOVERY SIMPLE
GO
-- Drop the varchar or text column that you want to reclaim the space
for.
ALTER TABLE [table]
DROP COLUMN column_name
GO
ALTER DATABASE [db] MODIFY FILE (NAME = N'db_log', SIZE = 2000) -- Set
log size to slightly bigger than what is required for the data change.
ALTER DATABASE [db] MODIFY FILE (NAME = N'db_log', FILEGROWTH = 100)
-- or set a larger filegrowth size (first is better).
GO
DBCC CLEANTABLE ('db', 'table', 4000000) -- batches of 4mill rows.
this is 10% of the table so transaction log will only reach 1.2GB
instead of 12GB (in this case).
GO
ALTER DATABASE [db] MODIFY FILE (NAME = N'db_log', FILEGROWTH = 1) --
reset filegrowth to original value.
GO
-- Put db back into FULL recovery mode.
ALTER DATABASE [db] SET RECOVERY FULL
GO

DBCC CLEANTABLE (SQL Server 2000) question

Hi,
I've got a 40mill row table and I've normalised one of the columns out
to a separate table. I have then dropped the original varchar(100)
column.
I want to reclaim the space as efficiently as possible.
I set recovery model to simple. Then ran DBCC CLEANTABLE with the
batch option set to 10000 rows.
Here I ran into a problem: it seems to block itself... anyone ever
encountered that before?
I then ran it without the batch parameter and it is still running
after 2 hours and the transaction log is still creeping up. Several GB
so far...
Any advice very welcome...
Cheers,
JamesJimLad schrieb:
> Hi,
> I've got a 40mill row table and I've normalised one of the columns out
> to a separate table. I have then dropped the original varchar(100)
> column.
> I want to reclaim the space as efficiently as possible.
> I set recovery model to simple. Then ran DBCC CLEANTABLE with the
> batch option set to 10000 rows.
> Here I ran into a problem: it seems to block itself... anyone ever
> encountered that before?
> I then ran it without the batch parameter and it is still running
> after 2 hours and the transaction log is still creeping up. Several GB
> so far...
> Any advice very welcome...
> Cheers,
> James
>
Instead of CLEANTABLE you could rebuild the indexes with DBCC REINDEX.
This has the same effect but is usually more resource-intensive than
cleantable.
hth
Gregor Stefka|||On May 1, 2:32 pm, Gregor Stefka <ste...@.zuehlke-bieker.de> wrote:
> JimLad schrieb:
>
>
>
>
>
>
>
>
>
>
> Instead of CLEANTABLE you could rebuild the indexes with DBCC REINDEX.
> This has the same effect but is usually more resource-intensive than
> cleantable.
> hth
> Gregor Stefka- Hide quoted text -
> - Show quoted text -
Hi,
When I say it is blocking itself I mean I'm getting PAGEIOLATCH_EX
waits when I set a value for the batch size. Is this normal? The wait
resource keeps changing, but I'm wondering why this is happening? Can
someone explain this for me?
Without the batch size being set, it took 3.25 hours to run and
created 10GB of log. Seems rather overlong to me. Am I doing something
wrong?
Wouldn't DBCC REINDEX result in even worse performance?
Cheers,
James|||On May 1, 5:41 pm, JimLad <jamesdbi...@.yahoo.co.uk> wrote:
> On May 1, 2:32 pm, Gregor Stefka <ste...@.zuehlke-bieker.de> wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> Hi,
> When I say it is blocking itself I mean I'm getting PAGEIOLATCH_EX
> waits when I set a value for the batch size. Is this normal? The wait
> resource keeps changing, but I'm wondering why this is happening? Can
> someone explain this for me?
> Without the batch size being set, it took 3.25 hours to run and
> created 10GB of log. Seems rather overlong to me. Am I doing something
> wrong?
> Wouldn't DBCC REINDEX result in even worse performance?
> Cheers,
> James- Hide quoted text -
> - Show quoted text -
Hi,
In answer to my own questions:
PAGEIOLATCH_EX waits don't matter.
Reason for slow performance is due to amount of data written to
transaction log. 12GB is written with filegrowth set to 1MB! Most of
the 3hrs is spent growing the log!
So remembering that the original number of rows is 40mill, then if we
set the batch size on DBCC CLEANTABLE to 4,000,000 then the max size
of the transaction log will be ~1.2GB. This obviously assumes SIMPLE
recovery model.
So now rather than filegrowth in 1MB increments up to 1.2GB, which is
terribly inefficient and time consuming, we set the log size manually
or increase the autogrow value.
****************************************
************************************
*********************
--This script temporarily changes recovery model to SIMPLE.
--A FULL backup must be taken PRIOR TO AND AFTER executing this
script...
--This script is not transaction safe. On error, RESTORE FROM
BACKUP...
--
****************************************
************************************
*********************
SELECT DATABASEPROPERTYEX('db','Recovery') AS [Initial Recovery Model
(script leaves db in FULL recovery model)]
GO
-- Allow log to be truncated for these large amount of changes.
ALTER DATABASE [db] SET RECOVERY SIMPLE
GO
-- Drop the varchar or text column that you want to reclaim the space
for.
ALTER TABLE [table]
DROP COLUMN column_name
GO
ALTER DATABASE [db] MODIFY FILE (NAME = N'db_log', SIZE = 2000) -- Set
log size to slightly bigger than what is required for the data change.
ALTER DATABASE [db] MODIFY FILE (NAME = N'db_log', FILEGROWTH = 100)
-- or set a larger filegrowth size (first is better).
GO
DBCC CLEANTABLE ('db', 'table', 4000000) -- batches of 4mill rows.
this is 10% of the table so transaction log will only reach 1.2GB
instead of 12GB (in this case).
GO
ALTER DATABASE [db] MODIFY FILE (NAME = N'db_log', FILEGROWTH = 1) --
reset filegrowth to original value.
GO
-- Put db back into FULL recovery mode.
ALTER DATABASE [db] SET RECOVERY FULL
GO

DBCC CHECKTABLE

I am trying to attach a database and i get this msg:

Could not find row in sysindexes for database id 11, object id 1, index id 1.

Run DBCC CHECKTABLE on sysindexes

i dont know what dbcc checktable is or how to run.

pls help

This is fully documented in the Books Online.

You might even want to run a DBCC CHECKDB to make sure everything is alright.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||i ran DBCC CHECKDB on master and model and have no problems

I looked at bol for checktable and dont understand, i have not worked with SQL Srv or another db before. i dont know how to run on sysindexes as the msg states in the 1st post
how do i run it with REPAIR_ALLOW_DATA_LOSS , REPAIR_FAST or REPAIR_REBUILD
here is the whole msg:

TITLE: Microsoft SQL Server Management Studio

Attach database failed for Server 'BUSTED'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Could not find row in sysindexes for database ID 11, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes.
Could not open new database 'Asset5'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 602)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=602&LinkId=20476

BUTTONS:

OK

|||

i think you are trying to attach database from SQL Server 2005 to SQL Server 2000 which is not possible. Please check the version of source and target. If its from 2005 to 2000 then its not possible.

Madhu

|||

For the source database that you are trying to attach, run DBCC CHECKDB on that database. You need to check and see if the database you are trying to attach is corrupted. You would do that by executing the DBCC on the source database, not on the server you are trying to attach to.

Log onto the server where the source database is and change the database context to that database. Then execute

DBCC CHECKDB

You don't want to run with repair options to start with - you just want to do a checkdb first with no repair options and go from there. Using the repair allow data loss option isn't something you generally want to do anyway. There are other options before going down that road that should be explored. It all depends on what messages are returned when you do the DBCC CHECKDB

-Sue