Does anyone know a utility/batch script to Warn when close to Max DB size

Hello Group
Does anyone know of a utility or Batch script (osql or Windows cmd or mix)
to Warn an Admin when a MSDE DB is closing in on 2 GB in size (or 2005
Express closes in on 4 GB).
Any leads or tips will be appriciated highly
Best regards
Uffe
hi Uffe,
Uffe Bak wrote:
> Hello Group
> Does anyone know of a utility or Batch script (osql or Windows cmd or
> mix) to Warn an Admin when a MSDE DB is closing in on 2 GB in size
> (or 2005 Express closes in on 4 GB).
> Any leads or tips will be appriciated highly
> Best regards
> Uffe
in MSDE you can perhaps schedule a job defined to include a way to gather
that kind of data, using direct access to the sysfiles database table or via
DBCC SHOWFILESTATS..
you can then validate that data against a userdefined treshold and notify
someone via NET SEND or mail via SMPT alternative, like exploded in
http://www.karaszi.com/sqlserver/info_no_mapi.asp,
http://www.dbmaint.com/SmtpAlerter.asp , thus defining a kind of alert..
PRINT 'MSDE';
PRINT '--';
USE Northwind;
PRINT 'sp_spaceused';
EXEC sp_spaceused @.updateusage = 'TRUE';
PRINT '--';
PRINT 'DBCC SHOWFILESTATS';
CREATE TABLE #tmp_sfs (
fileid int,
filegroup int,
totalextents int,
usedextents int,
name varchar(1024),
filename varchar(1024)
);
INSERT INTO #tmp_sfs
EXECUTE('DBCC SHOWFILESTATS');
SELECT LEFT(DB_NAME(),10), (SUM(totalextents) * 64) / 1024 AS [totalextents
in MB], (SUM(usedextents) * 64) /1024 AS [usedextents in MB]
FROM #tmp_sfs;
DROP TABLE #tmp_sfs;
PRINT '--';
PRINT 'sysdatabase query';
SELECT LEFT(DB_NAME(),10) AS [Database], sum(convert(float,size)) * (8192.0
/1024.0) /1024.0 AS [Size in MB for Data files]
FROM dbo.sysfiles
WHERE (status & 0x40) <> 0x40;
SQLExpress does not provide the SQL Server Agent, so you can perhaps rely on
the native OS scheduler for something similar.. as regards the SMPT
integration, you have to (possibly) write your own CLR based mailer
solution.. I personally will... then, from the OS scheduler, you'll execute
SqlCmd command line tool to perform the SQLExpress connection and data
retrival...
just an idea..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thanks to Andrea and Satya for quick answers
I will look into your suggestions and return if I find a solution.
I also looked up some Scripting options, so there are as usual more than one
road to Rome
Best regards
Uffe Bak

Does a tool like this exist?

Hi:
I am looking for a tool/utility that can compare 2 tables (indexes and
constraints etc.) and create a SQL script containing 'ALTER TABLE ...'
statements that would update the second table so it is the same as the first
table.
Does anything like this exist? It's a bit of a long-shot isn't it?
TIA,
MartinH.
Red Gate has a tool called SQL Compare
http://www.red-gate.com/
--Keith
"Martin Hart - Memory Soft, S.L." <memorysoftsl _at_ infotelecom _dot_ es>
wrote in message news:O8IXekOQFHA.244@.TK2MSFTNGP12.phx.gbl...
> Hi:
> I am looking for a tool/utility that can compare 2 tables (indexes and
> constraints etc.) and create a SQL script containing 'ALTER TABLE ...'
> statements that would update the second table so it is the same as the
> first table.
> Does anything like this exist? It's a bit of a long-shot isn't it?
> TIA,
> MartinH.
>
|||Hi,
SQLCompare is an excellent tool to compare and generate the script for the
differences between databases.
Try out the 14 day trial.
http://www.red-gate.com/SQL_Compare.htm
Thanks
Hari
SQL Server MVP
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:%23ux6lzOQFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Red Gate has a tool called SQL Compare
> http://www.red-gate.com/
> --Keith
> "Martin Hart - Memory Soft, S.L." <memorysoftsl _at_ infotelecom _dot_ es>
> wrote in message news:O8IXekOQFHA.244@.TK2MSFTNGP12.phx.gbl...
>
|||Martin Hart - Memory Soft, S.L. wrote:
> Hi:
> I am looking for a tool/utility that can compare 2 tables (indexes and
> constraints etc.) and create a SQL script containing 'ALTER TABLE ...'
> statements that would update the second table so it is the same as
> the first table.
> Does anything like this exist? It's a bit of a long-shot isn't it?
>
Imceda offers Speed Change Manager. 14-day trial on web site.
David Gugick
Imceda Software
www.imceda.com
|||Keith:
Thanks, looks *very* interesting.
Regards,
MartinH.
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> escribi en el mensaje
news:%23ux6lzOQFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Red Gate has a tool called SQL Compare
> http://www.red-gate.com/
> --Keith
> "Martin Hart - Memory Soft, S.L." <memorysoftsl _at_ infotelecom _dot_ es>
> wrote in message news:O8IXekOQFHA.244@.TK2MSFTNGP12.phx.gbl...
>
|||Innovartis does DB Ghost - more than a comparison tool - 30 day trial
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Martin Hart - Memory Soft, S.L." wrote:

> Hi:
> I am looking for a tool/utility that can compare 2 tables (indexes and
> constraints etc.) and create a SQL script containing 'ALTER TABLE ...'
> statements that would update the second table so it is the same as the first
> table.
> Does anything like this exist? It's a bit of a long-shot isn't it?
> TIA,
> MartinH.
>
>
|||Could you tell me Where is the web site ?
Thanks a lot
"mark baekdal" <markbaekdal@.discussions.microsoft.com> glsD:F9AC3991-7F4F-4C29-BA6D-0D52E03FB1C5@.microsoft.com...[vbcol=seagreen]
> Innovartis does DB Ghost - more than a comparison tool - 30 day trial
>
> regards,
> Mark Baekdal
> http://www.dbghost.com
> http://www.innovartis.co.uk
> +44 (0)208 241 1762
> Build, Comparison and Synchronization from Source Control = Database
> change
> management for SQL Server
>
>
> "Martin Hart - Memory Soft, S.L." wrote:
|||http://www.dbghost.com
"Agnes" wrote:

> Could you tell me Where is the web site ?
> Thanks a lot
> "mark baekdal" <markbaekdal@.discussions.microsoft.com> ???g?ó?l￥ó·s?D:F9AC3991-7F4F-4C29-BA6D-0D52E03FB1C5@.microsoft.com...
>
>

Documenting tables/fields - utility?

As part of a project documentation I want to list out the fields of all the tables to say Excel to plug in a description of the field and other notes.

Is there a tool in EM that would facilitate? Or, is there a utility that would work or a SQL script? Any recommendations is greatly appreciated.

Thanks,

PeterI'll never understand what motivates people to want to take DATA from a DATABASE and store it in a SPREADSHEET.
Tables and columns are already enumerated within the database, and a description of each column can be entered through Enterprise Manager.
You can query the system tables to list all the tables with their columns and descriptions and (if you absolutely insist...) export the results to an Excel file.|||Erwin is good for this. Which reminds me I am about a month and a half behind on that.|||Hey - for a blind guy, you sure help me see the light.

Thanks blindman.|||I keep a bottle of hand-sanitizer with me for occasions when a client requires me to open up Excel, but otherwise all the data I deal with stays in databases.|||LOL - I like that line about having to use hand sanitizer...I will be using that one myself.

I did find a quick query syntax at:

http://searchvb.techtarget.com/tip/1,289483,sid8_gci876303,00.html?bucket=ETA

SELECT
table_name=sysobjects.name,
column_name=syscolumns.name,
datatype=systypes.name,
length=syscolumns.length
FROM sysobjects inner JOIN syscolumns ON sysobjects.id = syscolumns.id
inner join systypes on syscolumns.xtype=systypes.xusertype
where sysobjects.xtype='U'
order by sysobjects.name,syscolumns.colid

I altered the above to bring in the description that a user can enter against each field in the tables:

SELECT
table_name=sysobjects.name,
column_name=syscolumns.name,
datatype=systypes.name,
length=syscolumns.length,
descr=sysproperties.value
FROM sysobjects inner JOIN syscolumns ON sysobjects.id = syscolumns.id
inner join systypes on syscolumns.xtype=systypes.xusertype
left join sysproperties on sysproperties.id=syscolumns.id and syscolumns.colid=sysproperties.smallid
where sysobjects.xtype='U'
order by sysobjects.name,syscolumns.colid

This is fine for my immediate needs.|||If you liked that one, you'll love this one ... and it's guarenteed not to become obsolete with sql server upgrades ...


select * from information_schema.columns|||Tom -

Thanks - another good one for quick data generation.

Documenting a SQL Server 2000 Database

I am looking for an easy way to document the specifications of my tables, views, etc. In MS Access, one could use the "Documenter" utility to print out field names, data types, field sizes, and comments. Does SQL Server 2000 have a similar utility?

If not...
1. Why not? This would seem to be very useful.
2. What is the recommended way to document database specifications?

My ultimate goal is to develop an administration manual for our database.

Please note: I looked at the database designer diagram utility which is great for visually representing the relationships between tables. However, I am looking for a utility that will print out the field names, data types, field sizes and comments related to the fields.

Any help would be appreciated.

Thanks :)I've used the INFORMATION_SCHEMA views, such as COLUMNS to get at this type of information.

This thread might help:view post 353693

Also, to get at the comments, you will likely need to use the system function fn_listextendedproperty.

Terri|||The other thing I forgot to mention was the system stored proceduresp_help.

Terri|||tmorton,

Your suggestions are perfect! Both the sp_help and fn_listextendedproperty provide the information I am looking for.

Follow-up question...

If I want to obtain the column information from both of these procedures for a particular table, how would I structure the query such that there would be a single result set? For example, a result set that would have column name, data type, and length from sp_help, and description from fn_listextendedproperty?

I apologize if my question seems rudimentary. I have limited experience with combining stored procedures and functions.

Thanks for your help :)|||I finally figured out how to obtain what I need.

Below you will see the stored procedure that I now use to obtain the following information: 1. Table name, 2. Column name, 3. Column position, 4. Allowance of nulls, 5. Maximum length, and 6. Field description. I used INFORMATION_SCHEMA and fn_listextendedproperty. The stored procedure requires that the table name be specified (i.e., @.TableName parameter).

Here it goes...

CREATE PROCEDURE A_GetTableSpecs_Desc
(
@.TableNamevarchar(100)
)
AS
SELECT
TABLE_NAME AS TableName,
COLUMN_NAME AS ColumnName,
ORDINAL_POSITION AS Position,
IS_NULLABLE AS AllowNulls,
CHARACTER_MAXIMUM_LENGTH AS MaxLength,
value AS FieldDescription
FROM INFORMATION_SCHEMA.COLUMNS AS MyColumns
LEFT JOIN
(
SELECT objname, name, value
FROM ::fn_listextendedproperty
(
NULL,
'user',
'dbo',
'table',
@.TableName,
'column',
default
)
) AS MyExtendedProperty
ON MyColumns.COLUMN_NAME = MyExtendedProperty.objname
WHERE MyColumns.TABLE_NAME = @.TableName
ORDER BY Position
RETURN
GO

Any better suggestions would be much appreciated. Thanks :)