Determine when a Service Pack was applied?

We are running SQL Server 2000 with SP3. Is there a way to determine
the date the last service pack was applied?
Thanks
Randy K wrote:
> We are running SQL Server 2000 with SP3. Is there a way to determine
> the date the last service pack was applied?
> Thanks
You could look in the error log and see when that version was first
booted (assuming they have no been overwritten). Use SQL EM or use the
undocumented procedures from Query Analyzer:
Exec sp_enumerrorlogs -- returns each error log and it's Archive #
Exec sp_readerrorlog -- no parameter for current log or pass in archive
# to read another log
David Gugick - SQL Server MVP
Quest Software
|||Look for the Sqlsp.log in your Windows directory. It's the logfile from
your last Service pack installation.
Markus

Determine when a Service Pack was applied?

We are running SQL Server 2000 with SP3. Is there a way to determine
the date the last service pack was applied?
ThanksRandy K wrote:
> We are running SQL Server 2000 with SP3. Is there a way to determine
> the date the last service pack was applied?
> Thanks
You could look in the error log and see when that version was first
booted (assuming they have no been overwritten). Use SQL EM or use the
undocumented procedures from Query Analyzer:
Exec sp_enumerrorlogs -- returns each error log and it's Archive #
Exec sp_readerrorlog -- no parameter for current log or pass in archive
# to read another log
David Gugick - SQL Server MVP
Quest Software|||Look for the Sqlsp.log in your Windows directory. It's the logfile from
your last Service pack installation.
Markus

Determine the database objects' sizes?

I have a database whose size is over 50GB. What is the easy way to determine
the size of each database objects (tables)? I want to find out the ones that
take most of the space. Thanks a lot,
FLThis uses an undocumented and unsupported system procedure.
EXEC sp_MSForEachTable 'EXEC sp_spaceused ''?'''
Another method would be to do this:
SELECT 'EXEC sp_spaceused '+TABLE_NAME+' FROM INFORMATION_SCHEMA.TABLES
Run that in Query Analyzer, using Results to TEXT, copy the output to the
top pane and run that...
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> I have a database whose size is over 50GB. What is the easy way to
determine
> the size of each database objects (tables)? I want to find out the ones
that
> take most of the space. Thanks a lot,
> FL
>
>|||FLX
1)sp_spaceused in the BOL
--
2) Vays has written a great SP to show big tables
CREATE PROC sp_show_huge_tables
(
@.top int = NULL,
@.include_system_tables bit = 0
)
AS
/*
To see the top three biggest user or system tables in your database:
EXEC sp_show_huge_tables 3, 1
****************************************
************************************
*********************/
BEGIN
IF @.top > 0
SET ROWCOUNT @.top
SELECT [Table Name], (SELECT rows FROM sysindexes s WHERE s.indid < 2 AN
D
s.id = OBJECT_ID(a.[Table Name])) AS [Row count], [Total space u
sed (MB)]
FROM
(
SELECT QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id)) AS
[Table Name],
CONVERT(numeric(15,2),(((CONVERT(numeric
(15,2),SUM(i.reserved)) * (SELECT
low FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = 'E')) /
1024.)/1024.)) AS [Total space used (MB)]
FROM sysindexes i (NOLOCK)
INNER JOIN
sysobjects o (NOLOCK)
ON
i.id = o.id AND
((@.include_system_tables = 1 AND o.type IN ('U', 'S')) OR o.type = 'U')
AND
((@.include_system_tables = 1)OR (OBJECTPROPERTY(i.id, 'IsMSShipped') = 0))
WHERE indid IN (0, 1, 255)
GROUP BY QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))
) as a
ORDER BY [Total space used (MB)] DESC
SET ROWCOUNT 0
END
GO
--
3)SELECT TOP 5
CAST(sysobjects.[name] AS VARCHAR) AS [TABLE_NAME],
sysindexes.[rows] AS [NO_OF_ROWS],
sysindexes.reserved AS [RESERVED_SPACE],
sysindexes.used AS [USED_SPACE]
FROM sysobjects
INNER JOIN sysindexes
ON sysobjects.[id] = sysindexes.[id]
WHERE sysindexes.indid < 2
AND sysobjects.type = 'U'
ORDER BY used DESC
"FLX" <nospam@.hotmail.com> wrote in message
news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> I have a database whose size is over 50GB. What is the easy way to
determine
> the size of each database objects (tables)? I want to find out the ones
that
> take most of the space. Thanks a lot,
> FL
>
>|||And for both sets of responses, remember to update statistics first
(sysindexes won't necessarily be up to date).
http://www.aspfaq.com/
(Reverse address to reply.)
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#MXp$pu1EHA.1564@.TK2MSFTNGP09.phx.gbl...
> FLX
> 1)sp_spaceused in the BOL
> --
> 2) Vays has written a great SP to show big tables
> CREATE PROC sp_show_huge_tables
> (
> @.top int = NULL,
> @.include_system_tables bit = 0
> )
> AS
> /*
> To see the top three biggest user or system tables in your database:
> EXEC sp_show_huge_tables 3, 1
>
****************************************
************************************[vbc
ol=seagreen]
> *********************/
> BEGIN
> IF @.top > 0
> SET ROWCOUNT @.top
> SELECT [Table Name], (SELECT rows FROM sysindexes s WHERE s.indid < 2
AND
> s.id = OBJECT_ID(a.[Table Name])) AS [Row count], [Total space
used (MB)]
> FROM
> (
> SELECT QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))[/vbcol]
AS
> [Table Name],
> CONVERT(numeric(15,2),(((CONVERT(numeric
(15,2),SUM(i.reserved)) *
(SELECT
> low FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = 'E'))
/
> 1024.)/1024.)) AS [Total space used (MB)]
> FROM sysindexes i (NOLOCK)
> INNER JOIN
> sysobjects o (NOLOCK)
> ON
> i.id = o.id AND
> ((@.include_system_tables = 1 AND o.type IN ('U', 'S')) OR o.type = 'U')
> AND
> ((@.include_system_tables = 1)OR (OBJECTPROPERTY(i.id, 'IsMSShipped') =
0))
> WHERE indid IN (0, 1, 255)
> GROUP BY QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))
> ) as a
> ORDER BY [Total space used (MB)] DESC
>
> SET ROWCOUNT 0
> END
>
> GO
> --
> 3)SELECT TOP 5
> CAST(sysobjects.[name] AS VARCHAR) AS [TABLE_NAME],
> sysindexes.[rows] AS [NO_OF_ROWS],
> sysindexes.reserved AS [RESERVED_SPACE],
> sysindexes.used AS [USED_SPACE]
> FROM sysobjects
> INNER JOIN sysindexes
> ON sysobjects.[id] = sysindexes.[id]
> WHERE sysindexes.indid < 2
> AND sysobjects.type = 'U'
> ORDER BY used DESC
> "FLX" <nospam@.hotmail.com> wrote in message
> news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> determine
> that
>|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uwZI6nu1EHA.3908@.TK2MSFTNGP12.phx.gbl...
> This uses an undocumented and unsupported system procedure.
> EXEC sp_MSForEachTable 'EXEC sp_spaceused ''?'''
> Another method would be to do this:
> SELECT 'EXEC sp_spaceused '+TABLE_NAME+' FROM INFORMATION_SCHEMA.TABLES
> Run that in Query Analyzer, using Results to TEXT, copy the output to the
> top pane and run that...
>
Cool.
FYI, I found there is a typo (extra single quote) in the query. It should
be:
SELECT 'EXEC sp_spaceused '+ TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
FL

> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> determine
> that
>

Determine the database objects' sizes?

I have a database whose size is over 50GB. What is the easy way to determine
the size of each database objects (tables)? I want to find out the ones that
take most of the space. Thanks a lot,
FL
This uses an undocumented and unsupported system procedure.
EXEC sp_MSForEachTable 'EXEC sp_spaceused ''?'''
Another method would be to do this:
SELECT 'EXEC sp_spaceused '+TABLE_NAME+' FROM INFORMATION_SCHEMA.TABLES
Run that in Query Analyzer, using Results to TEXT, copy the output to the
top pane and run that...
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> I have a database whose size is over 50GB. What is the easy way to
determine
> the size of each database objects (tables)? I want to find out the ones
that
> take most of the space. Thanks a lot,
> FL
>
>
|||FLX
1)sp_spaceused in the BOL
2) Vays has written a great SP to show big tables
CREATE PROC sp_show_huge_tables
(
@.top int = NULL,
@.include_system_tables bit = 0
)
AS
/*
To see the top three biggest user or system tables in your database:
EXEC sp_show_huge_tables 3, 1
************************************************** **************************
*********************/
BEGIN
IF @.top > 0
SET ROWCOUNT @.top
SELECT [Table Name], (SELECT rows FROM sysindexes s WHERE s.indid < 2 AND
s.id = OBJECT_ID(a.[Table Name])) AS [Row count], [Total space used (MB)]
FROM
(
SELECT QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id)) AS
[Table Name],
CONVERT(numeric(15,2),(((CONVERT(numeric(15,2),SUM (i.reserved)) * (SELECT
low FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = 'E')) /
1024.)/1024.)) AS [Total space used (MB)]
FROM sysindexes i (NOLOCK)
INNER JOIN
sysobjects o (NOLOCK)
ON
i.id = o.id AND
((@.include_system_tables = 1 AND o.type IN ('U', 'S')) OR o.type = 'U')
AND
((@.include_system_tables = 1)OR (OBJECTPROPERTY(i.id, 'IsMSShipped') = 0))
WHERE indid IN (0, 1, 255)
GROUP BY QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))
) as a
ORDER BY [Total space used (MB)] DESC
SET ROWCOUNT 0
END
GO
3)SELECT TOP 5
CAST(sysobjects.[name] AS VARCHAR) AS [TABLE_NAME],
sysindexes.[rows] AS [NO_OF_ROWS],
sysindexes.reserved AS [RESERVED_SPACE],
sysindexes.used AS [USED_SPACE]
FROM sysobjects
INNER JOIN sysindexes
ON sysobjects.[id] = sysindexes.[id]
WHERE sysindexes.indid < 2
AND sysobjects.type = 'U'
ORDER BY used DESC
"FLX" <nospam@.hotmail.com> wrote in message
news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> I have a database whose size is over 50GB. What is the easy way to
determine
> the size of each database objects (tables)? I want to find out the ones
that
> take most of the space. Thanks a lot,
> FL
>
>
|||And for both sets of responses, remember to update statistics first
(sysindexes won't necessarily be up to date).
http://www.aspfaq.com/
(Reverse address to reply.)
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#MXp$pu1EHA.1564@.TK2MSFTNGP09.phx.gbl...
> FLX
> 1)sp_spaceused in the BOL
> --
> 2) Vays has written a great SP to show big tables
> CREATE PROC sp_show_huge_tables
> (
> @.top int = NULL,
> @.include_system_tables bit = 0
> )
> AS
> /*
> To see the top three biggest user or system tables in your database:
> EXEC sp_show_huge_tables 3, 1
>
************************************************** **************************
> *********************/
> BEGIN
> IF @.top > 0
> SET ROWCOUNT @.top
> SELECT [Table Name], (SELECT rows FROM sysindexes s WHERE s.indid < 2 AND
> s.id = OBJECT_ID(a.[Table Name])) AS [Row count], [Total space used (MB)]
> FROM
> (
> SELECT QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))
AS
> [Table Name],
> CONVERT(numeric(15,2),(((CONVERT(numeric(15,2),SUM (i.reserved)) *
(SELECT
> low FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = 'E'))
/
> 1024.)/1024.)) AS [Total space used (MB)]
> FROM sysindexes i (NOLOCK)
> INNER JOIN
> sysobjects o (NOLOCK)
> ON
> i.id = o.id AND
> ((@.include_system_tables = 1 AND o.type IN ('U', 'S')) OR o.type = 'U')
> AND
> ((@.include_system_tables = 1)OR (OBJECTPROPERTY(i.id, 'IsMSShipped') =
0))
> WHERE indid IN (0, 1, 255)
> GROUP BY QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))
> ) as a
> ORDER BY [Total space used (MB)] DESC
>
> SET ROWCOUNT 0
> END
>
> GO
> --
> 3)SELECT TOP 5
> CAST(sysobjects.[name] AS VARCHAR) AS [TABLE_NAME],
> sysindexes.[rows] AS [NO_OF_ROWS],
> sysindexes.reserved AS [RESERVED_SPACE],
> sysindexes.used AS [USED_SPACE]
> FROM sysobjects
> INNER JOIN sysindexes
> ON sysobjects.[id] = sysindexes.[id]
> WHERE sysindexes.indid < 2
> AND sysobjects.type = 'U'
> ORDER BY used DESC
> "FLX" <nospam@.hotmail.com> wrote in message
> news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> determine
> that
>
|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uwZI6nu1EHA.3908@.TK2MSFTNGP12.phx.gbl...
> This uses an undocumented and unsupported system procedure.
> EXEC sp_MSForEachTable 'EXEC sp_spaceused ''?'''
> Another method would be to do this:
> SELECT 'EXEC sp_spaceused '+TABLE_NAME+' FROM INFORMATION_SCHEMA.TABLES
> Run that in Query Analyzer, using Results to TEXT, copy the output to the
> top pane and run that...
>
Cool.
FYI, I found there is a typo (extra single quote) in the query. It should
be:
SELECT 'EXEC sp_spaceused '+ TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
FL

> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> determine
> that
>