How to rung SQL 2005 db on SQL 2000 server - no unique SQL 2005 st

I converted a database from Sql 2000 to Sql 2005 - no problems. Added
function to the database. I did not use any new functions unique to Sql
2005, as far as I know. I need to allw this database to run on both SQL 200
0
and SQL 2005. When I attempted to load the database on SQL 2000 local
server, I received the following message:
Error 602 - Could not find Row in SysIndex for ID 7, Object ID 1, Index ID 1
.
Run DBCC checktable on SysIndex.
I have never run this and I do not know if this is an on going problem. Any
information would be appreciated.
Thank You.
Jack
LitePipe ManagementYou cannot restore or attach a 2005 database to 2000, 2005 added stuff to th
e database file formats
that wasn't known when MS wrote 2000 (obviously). To downgrade, you have to
go the script,
export/import route (using BCP, DTS, SSIS etc).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"LitePipe" <LitePipe@.discussions.microsoft.com> wrote in message
news:5BB70FC5-0BD5-4306-AC5D-0ED417835C6B@.microsoft.com...
>I converted a database from Sql 2000 to Sql 2005 - no problems. Added
> function to the database. I did not use any new functions unique to Sql
> 2005, as far as I know. I need to allw this database to run on both SQL 2
000
> and SQL 2005. When I attempted to load the database on SQL 2000 local
> server, I received the following message:
> Error 602 - Could not find Row in SysIndex for ID 7, Object ID 1, Index ID
1.
> Run DBCC checktable on SysIndex.
> I have never run this and I do not know if this is an on going problem. A
ny
> information would be appreciated.
> Thank You.
> Jack
> LitePipe Management

How to retrieve the (MB) size of all tables in a database

I have a database under SQL2005 that in a 24 hour period has increased from
200MB to 2GB (this is the dbf file size not the log file size) and there are
no obvious reasons for the increase. We have also tried Shrinking both the D
B
and file with little reduction in size. Is there a way to query the database
and find out the sizes of tables etc to help with further investigation?
Looking at the properties of each in Management studio really isn't feasible
due to the number of objects. TIA
AntonyThe sp below will return all the tables in a database, by reserved size
desc (large to small)
Run this sp in the database of concern:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[BigTables]
AS
/ ****************************************
***********************************
***********
*
* BigTables.sql
* Bill Graziano (SQLTeam.com)
* graz@.sqlteam.com
* v1.1
*
****************************************
************************************
**********/
declare @.id int
declare @.type character(2)
declare @.pages int
declare @.dbname sysname
declare @.dbsize dec(15,0)
declare @.bytesperpage dec(15,0)
declare @.pagesperMB dec(15,0)
create table #spt_space
(
objid int null,
rows int null,
reserved dec(15) null,
data dec(15) null,
indexp dec(15) null,
unused dec(15) null
)
set nocount on
-- Create a cursor to loop through the user tables
declare c_tables cursor for
select id
from sysobjects
where xtype = 'U'
open c_tables
fetch next from c_tables
into @.id
while @.@.fetch_status = 0
begin
/* Code from sp_spaceused */
insert into #spt_space (objid, reserved)
select objid = @.id, sum(reserved)
from sysindexes
where indid in (0, 1, 255)
and id = @.id
select @.pages = sum(dpages)
from sysindexes
where indid < 2
and id = @.id
select @.pages = @.pages + isnull(sum(used), 0)
from sysindexes
where indid = 255
and id = @.id
update #spt_space
set data = @.pages
where objid = @.id
/* index: sum(used) where indid in (0, 1, 255) - data */
update #spt_space
set indexp = (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @.id)
- data
where objid = @.id
/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
update #spt_space
set unused = reserved
- (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @.id)
where objid = @.id
update #spt_space
set rows = i.rows
from sysindexes i
where i.indid < 2
and i.id = @.id
and objid = @.id
fetch next from c_tables
into @.id
end
select --top 25
Table_Name = (select left(name,25) from sysobjects where id = objid),
rows = convert(char(11), rows),
reserved_MB = ltrim(str(reserved * d.low / 1048576.,15,0) + ' ' +
'MB'),
data_MB = ltrim(str(data * d.low / 1048576.,15,0) + ' ' + 'MB'),
index_size_MB = ltrim(str(indexp * d.low / 1048576.,15,0) + ' ' +
'MB'),
unused_MB = ltrim(str(unused * d.low / 1048576.,15,0) + ' ' + 'MB')
from #spt_space, master.dbo.spt_values d
where d.number = 1
and d.type = 'E'
order by reserved desc
drop table #spt_space
close c_tables
deallocate c_tables|||Antony wrote:
> I have a database under SQL2005 that in a 24 hour period has increased fro
m
> 200MB to 2GB (this is the dbf file size not the log file size) and there a
re
> no obvious reasons for the increase. We have also tried Shrinking both the
DB
> and file with little reduction in size. Is there a way to query the databa
se
> and find out the sizes of tables etc to help with further investigation?
> Looking at the properties of each in Management studio really isn't feasib
le
> due to the number of objects. TIA
> Antony
There is a known bug with the auto-growth function in SQL 2005. Sounds
like that might be your problem.
http://connect.microsoft.com/SQLSer...=12717
7
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||This might be useful.
CREATE TABLE #Tables
( [name] nvarchar(20),
[rows] char(11),
[reserved] varchar(18),
[data] varchar(18),
[index_size] varchar(18),
[unused] varchar(18)
)
EXECUTE sp_MSForEachTable 'INSERT INTO #Tables EXECUTE sp_spaceused [?]'
SELECT
[name],
[data]
FROM #Tables
DROP TABLE #Tables
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Antony" <info AT webpc DOT biz> wrote in message news:u0L0oEKFHHA.3212@.TK2MSFTNGP02.phx.gbl
..
>I have a database under SQL2005 that in a 24 hour period has increased from
> 200MB to 2GB (this is the dbf file size not the log file size) and there a
re
> no obvious reasons for the increase. We have also tried Shrinking both the
DB
> and file with little reduction in size. Is there a way to query the databa
se
> and find out the sizes of tables etc to help with further investigation?
> Looking at the properties of each in Management studio really isn't feasib
le
> due to the number of objects. TIA
> Antony|||Thanks everyone for the advice. I found the table that was causing the issue
.
Antony
On 11/30/2006 12:04:10 PM, "Antony" wrote:
>I have a database under SQL2005 that in a 24 hour period has increased from
>200MB to 2GB (this is the dbf file size not the log file size) and there ar
e
>no obvious reasons for the increase. We have also tried Shrinking both the
DB
>and file with little reduction in size. Is there a way to query the databas
e
>and find out the sizes of tables etc to help with further investigation?
>Looking at the properties of each in Management studio really isn't feasibl
e
>due to the number of objects. TIA
>Antony

How to retrieve the (MB) size of all tables in a database

I have a database under SQL2005 that in a 24 hour period has increased from
200MB to 2GB (this is the dbf file size not the log file size) and there are
no obvious reasons for the increase. We have also tried Shrinking both the DB
and file with little reduction in size. Is there a way to query the database
and find out the sizes of tables etc to help with further investigation?
Looking at the properties of each in Management studio really isn't feasible
due to the number of objects. TIA
AntonyThe sp below will return all the tables in a database, by reserved size
desc (large to small)
Run this sp in the database of concern:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[BigTables]
AS
/**************************************************************************************
*
* BigTables.sql
* Bill Graziano (SQLTeam.com)
* graz@.sqlteam.com
* v1.1
*
**************************************************************************************/
declare @.id int
declare @.type character(2)
declare @.pages int
declare @.dbname sysname
declare @.dbsize dec(15,0)
declare @.bytesperpage dec(15,0)
declare @.pagesperMB dec(15,0)
create table #spt_space
(
objid int null,
rows int null,
reserved dec(15) null,
data dec(15) null,
indexp dec(15) null,
unused dec(15) null
)
set nocount on
-- Create a cursor to loop through the user tables
declare c_tables cursor for
select id
from sysobjects
where xtype = 'U'
open c_tables
fetch next from c_tables
into @.id
while @.@.fetch_status = 0
begin
/* Code from sp_spaceused */
insert into #spt_space (objid, reserved)
select objid = @.id, sum(reserved)
from sysindexes
where indid in (0, 1, 255)
and id = @.id
select @.pages = sum(dpages)
from sysindexes
where indid < 2
and id = @.id
select @.pages = @.pages + isnull(sum(used), 0)
from sysindexes
where indid = 255
and id = @.id
update #spt_space
set data = @.pages
where objid = @.id
/* index: sum(used) where indid in (0, 1, 255) - data */
update #spt_space
set indexp = (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @.id)
- data
where objid = @.id
/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
update #spt_space
set unused = reserved
- (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @.id)
where objid = @.id
update #spt_space
set rows = i.rows
from sysindexes i
where i.indid < 2
and i.id = @.id
and objid = @.id
fetch next from c_tables
into @.id
end
select --top 25
Table_Name = (select left(name,25) from sysobjects where id = objid),
rows = convert(char(11), rows),
reserved_MB = ltrim(str(reserved * d.low / 1048576.,15,0) + ' ' +
'MB'),
data_MB = ltrim(str(data * d.low / 1048576.,15,0) + ' ' + 'MB'),
index_size_MB = ltrim(str(indexp * d.low / 1048576.,15,0) + ' ' +
'MB'),
unused_MB = ltrim(str(unused * d.low / 1048576.,15,0) + ' ' + 'MB')
from #spt_space, master.dbo.spt_values d
where d.number = 1
and d.type = 'E'
order by reserved desc
drop table #spt_space
close c_tables
deallocate c_tables|||Antony wrote:
> I have a database under SQL2005 that in a 24 hour period has increased from
> 200MB to 2GB (this is the dbf file size not the log file size) and there are
> no obvious reasons for the increase. We have also tried Shrinking both the DB
> and file with little reduction in size. Is there a way to query the database
> and find out the sizes of tables etc to help with further investigation?
> Looking at the properties of each in Management studio really isn't feasible
> due to the number of objects. TIA
> Antony
There is a known bug with the auto-growth function in SQL 2005. Sounds
like that might be your problem.
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||This is a multi-part message in MIME format.
--=_NextPart_000_0468_01C71468.EBBF5E10
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
This might be useful.
CREATE TABLE #Tables ( [name] nvarchar(20),
[rows] char(11),
[reserved] varchar(18),
[data] varchar(18),
[index_size] varchar(18),
[unused] varchar(18)
)
EXECUTE sp_MSForEachTable 'INSERT INTO #Tables EXECUTE sp_spaceused [?]'
SELECT [name],
[data]
FROM #Tables
DROP TABLE #Tables
-- Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
You can't help someone get up a hill without getting a little closer to =the top yourself.
- H. Norman Schwarzkopf
"Antony" <info AT webpc DOT biz> wrote in message =news:u0L0oEKFHHA.3212@.TK2MSFTNGP02.phx.gbl...
>I have a database under SQL2005 that in a 24 hour period has increased =from
> 200MB to 2GB (this is the dbf file size not the log file size) and =there are
> no obvious reasons for the increase. We have also tried Shrinking both =the DB
> and file with little reduction in size. Is there a way to query the =database
> and find out the sizes of tables etc to help with further =investigation?
> Looking at the properties of each in Management studio really isn't =feasible
> due to the number of objects. TIA
> Antony
--=_NextPart_000_0468_01C71468.EBBF5E10
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

This might be useful.
CREATE TABLE =#Tables ( [name] nvarchar(20), [rows] char(11), [reserved] varchar(18), [data] varchar(18), [index_size] varchar(18), =[unused] varchar(18) )
EXECUTE sp_MSForEachTable ='INSERT INTO #Tables EXECUTE sp_spaceused [?]'
SELECT [name], [data]FROM #Tables
DROP TABLE #Tables
-- Arnie Rowland, =Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
You can't help someone get up a hill =without getting a little closer to the top yourself.- H. Norman Schwarzkopf
"Antony" =wrote in message news:u0L0oEKFHHA.3212@.TK2MSFTNGP02.phx.gbl...>I =have a database under SQL2005 that in a 24 hour period has increased from> 200MB =to 2GB (this is the dbf file size not the log file size) and there are> =no obvious reasons for the increase. We have also tried Shrinking both the DB> and file with little reduction in size. Is there a way to =query the database> and find out the sizes of tables etc to help with =further investigation?> Looking at the properties of each in Management =studio really isn't feasible> due to the number of objects. TIA> Antony

---

--=_NextPart_000_0468_01C71468.EBBF5E10--|||Thanks everyone for the advice. I found the table that was causing the issue.
Antony
On 11/30/2006 12:04:10 PM, "Antony" wrote:
>I have a database under SQL2005 that in a 24 hour period has increased from
>200MB to 2GB (this is the dbf file size not the log file size) and there are
>no obvious reasons for the increase. We have also tried Shrinking both the DB
>and file with little reduction in size. Is there a way to query the database
>and find out the sizes of tables etc to help with further investigation?
>Looking at the properties of each in Management studio really isn't feasible
>due to the number of objects. TIA
>Antony

How to retrieve current date and time from SQL2005 server.

well i'm using java to connect to SQL 2005 server. i need to retrieve it's current time and date on the sql 2005 server and use it on my remote desktop

thanks.

Hi,

You can use the following query :

SELECT getdate() as myDate

wich will give you somthing like "2006-10-27 11:29:15.373"

Regards

How to Restore SQL2K Full bkup then tran logs?

I am upgrading from SQL2000 to SQL2005 - I want to restore a full SQL2000
backup, then the transaction log backups to 2005. Am getting error below w/
full in standby - How to resolve? Thanks.
RESTORE DATABASE [MyDB] FROM DISK = N'\\srvimgdb2\bkData\MyDB.bak'
WITH FILE = 1, NOUNLOAD, STATS = 10, STANDBY = N'E:\dbData\standbyMyDB.txt',
REPLACE,
MOVE N'MyDB_Data' TO N'E:\dbData\MyDB_Data.mdf',
MOVE N'MyDB_Log' TO N'C:\dbLogs\MyDB_log.ldf'
Msg 3180, Level 16, State 1, Line 2
This backup cannot be restored using WITH STANDBY because a database upgrade
is needed. Reissue the RESTORE without WITH STANDBY.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.Chris wrote:
> I am upgrading from SQL2000 to SQL2005 - I want to restore a full SQL2000
> backup, then the transaction log backups to 2005. Am getting error below w/
> full in standby - How to resolve? Thanks.
> RESTORE DATABASE [MyDB] FROM DISK = N'\\srvimgdb2\bkData\MyDB.bak'
> WITH FILE = 1, NOUNLOAD, STATS = 10, STANDBY = N'E:\dbData\standbyMyDB.txt',
> REPLACE,
> MOVE N'MyDB_Data' TO N'E:\dbData\MyDB_Data.mdf',
> MOVE N'MyDB_Log' TO N'C:\dbLogs\MyDB_log.ldf'
>
> Msg 3180, Level 16, State 1, Line 2
> This backup cannot be restored using WITH STANDBY because a database upgrade
> is needed. Reissue the RESTORE without WITH STANDBY.
> Msg 3013, Level 16, State 1, Line 2
> RESTORE DATABASE is terminating abnormally.
Try WITH NORECOVERY instead of WITH STANDBY
Tracy McKibben
MCDBA
http://www.realsqlguy.com

How to Restore SQL2K Full bkup then tran logs?

I am upgrading from SQL2000 to SQL2005 - I want to restore a full SQL2000
backup, then the transaction log backups to 2005. Am getting error below w/
full in standby - How to resolve? Thanks.
RESTORE DATABASE [MyDB] FROM DISK = N'\\srvimgdb2\bkData\MyDB.bak'
WITH FILE = 1, NOUNLOAD, STATS = 10, STANDBY = N'E:\dbData\standbyMyDB.txt',
REPLACE,
MOVE N'MyDB_Data' TO N'E:\dbData\MyDB_Data.mdf',
MOVE N'MyDB_Log' TO N'C:\dbLogs\MyDB_log.ldf'
Msg 3180, Level 16, State 1, Line 2
This backup cannot be restored using WITH STANDBY because a database upgrade
is needed. Reissue the RESTORE without WITH STANDBY.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.Chris wrote:
> I am upgrading from SQL2000 to SQL2005 - I want to restore a full SQL2000
> backup, then the transaction log backups to 2005. Am getting error below w
/
> full in standby - How to resolve? Thanks.
> RESTORE DATABASE [MyDB] FROM DISK = N'\\srvimgdb2\bkData\MyDB.bak'
> WITH FILE = 1, NOUNLOAD, STATS = 10, STANDBY = N'E:\dbData\standbyMyDB.txt
',
> REPLACE,
> MOVE N'MyDB_Data' TO N'E:\dbData\MyDB_Data.mdf',
> MOVE N'MyDB_Log' TO N'C:\dbLogs\MyDB_log.ldf'
>
> Msg 3180, Level 16, State 1, Line 2
> This backup cannot be restored using WITH STANDBY because a database upgra
de
> is needed. Reissue the RESTORE without WITH STANDBY.
> Msg 3013, Level 16, State 1, Line 2
> RESTORE DATABASE is terminating abnormally.
Try WITH NORECOVERY instead of WITH STANDBY
Tracy McKibben
MCDBA
http://www.realsqlguy.com