Delete backup from backup device?

I dump my databases to backup devices each night.

However would like to purge old backups - say older than a week - from the device.

Is it a case that I have to drop and re-create the devices every 7 days or can SQL do this for me?

I have seen the RETAINDAYS claus but it applies to tape backups only?

ThanksTake help from database maintenance plan to delete the backups older than x days.

If you go to "Database Maintenance Plans" and follow the wizard for a new maintenance plan, you can set up an automatic backup, that will create full backups that are datetime stamped (on the file name) in the directory of your choosing. You can configure it to delete backups that are older than X weeks as well.sql

Delete all Indexes

I have several databases that are supposed to have the same structure, over
time some things (mostly indexes) have been changed, added, etc. differently
on each database. I would like to write a script that would strip all user
tables of their indexes and then run a script that would add the indexes I
want on them. I have no problem writing the script to create the indexes.
What I need is a script that will delete read the system table and delete
all of the indexes in a particular database (including primary keys).
If anybody has such a thing and wouldn't mind sharing it, I would be most
grateful.Here is the actual error:
An explicit DROP INDEX is not allowed on index
'tblTableName.PrimaryKeyName_PK'. It is being used for PRIMARY KEY
constraint enforcement.
"Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:OY7JRpGTDHA.1912@.tk2msftngp13.phx.gbl...
> John,
> Execute the output of the below query .
> SELECT 'DROP INDEX ' + OBJECT_NAME([id]) + '.' + [name]+CHAR(13)+'GO'
> FROM sysindexes
> WHERE indid BETWEEN 1 AND 250
> AND OBJECTPROPERTY([id], 'IsMsShipped') = 0
> AND INDEXPROPERTY([id], [name], 'IsStatistics') = 0
>
> --
> Dinesh.
> SQL Server FAQ at
> http://www.tkdinesh.com
> "John Hamilton" <jhamil@.nowhere.com> wrote in message
> news:uFM7uiGTDHA.2852@.tk2msftngp13.phx.gbl...
> > I have several databases that are supposed to have the same structure,
> over
> > time some things (mostly indexes) have been changed, added, etc.
> differently
> > on each database. I would like to write a script that would strip all
> user
> > tables of their indexes and then run a script that would add the indexes
I
> > want on them. I have no problem writing the script to create the
indexes.
> > What I need is a script that will delete read the system table and
delete
> > all of the indexes in a particular database (including primary keys).
> >
> > If anybody has such a thing and wouldn't mind sharing it, I would be
most
> > grateful.
> >
> >
>|||You need to use DROP CONSTRAINT on constraints.
--
Andrew J. Kelly
SQL Server MVP
"John Hamilton" <jhamil@.nowhere.com> wrote in message
news:ukge0sHTDHA.2020@.TK2MSFTNGP11.phx.gbl...
> Here is the actual error:
> An explicit DROP INDEX is not allowed on index
> 'tblTableName.PrimaryKeyName_PK'. It is being used for PRIMARY KEY
> constraint enforcement.
> "Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
> news:OY7JRpGTDHA.1912@.tk2msftngp13.phx.gbl...
> > John,
> >
> > Execute the output of the below query .
> >
> > SELECT 'DROP INDEX ' + OBJECT_NAME([id]) + '.' + [name]+CHAR(13)+'GO'
> > FROM sysindexes
> > WHERE indid BETWEEN 1 AND 250
> > AND OBJECTPROPERTY([id], 'IsMsShipped') = 0
> > AND INDEXPROPERTY([id], [name], 'IsStatistics') = 0
> >
> >
> > --
> > Dinesh.
> > SQL Server FAQ at
> > http://www.tkdinesh.com
> >
> > "John Hamilton" <jhamil@.nowhere.com> wrote in message
> > news:uFM7uiGTDHA.2852@.tk2msftngp13.phx.gbl...
> > > I have several databases that are supposed to have the same structure,
> > over
> > > time some things (mostly indexes) have been changed, added, etc.
> > differently
> > > on each database. I would like to write a script that would strip all
> > user
> > > tables of their indexes and then run a script that would add the
indexes
> I
> > > want on them. I have no problem writing the script to create the
> indexes.
> > > What I need is a script that will delete read the system table and
> delete
> > > all of the indexes in a particular database (including primary keys).
> > >
> > > If anybody has such a thing and wouldn't mind sharing it, I would be
> most
> > > grateful.
> > >
> > >
> >
> >
>|||What is the best way to seperate which are regular indexes and which are
primary keys?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e1E7LwHTDHA.2148@.TK2MSFTNGP12.phx.gbl...
> You need to use DROP CONSTRAINT on constraints.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "John Hamilton" <jhamil@.nowhere.com> wrote in message
> news:ukge0sHTDHA.2020@.TK2MSFTNGP11.phx.gbl...
> > Here is the actual error:
> >
> > An explicit DROP INDEX is not allowed on index
> > 'tblTableName.PrimaryKeyName_PK'. It is being used for PRIMARY KEY
> > constraint enforcement.
> >
> > "Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
> > news:OY7JRpGTDHA.1912@.tk2msftngp13.phx.gbl...
> > > John,
> > >
> > > Execute the output of the below query .
> > >
> > > SELECT 'DROP INDEX ' + OBJECT_NAME([id]) + '.' + [name]+CHAR(13)+'GO'
> > > FROM sysindexes
> > > WHERE indid BETWEEN 1 AND 250
> > > AND OBJECTPROPERTY([id], 'IsMsShipped') = 0
> > > AND INDEXPROPERTY([id], [name], 'IsStatistics') = 0
> > >
> > >
> > > --
> > > Dinesh.
> > > SQL Server FAQ at
> > > http://www.tkdinesh.com
> > >
> > > "John Hamilton" <jhamil@.nowhere.com> wrote in message
> > > news:uFM7uiGTDHA.2852@.tk2msftngp13.phx.gbl...
> > > > I have several databases that are supposed to have the same
structure,
> > > over
> > > > time some things (mostly indexes) have been changed, added, etc.
> > > differently
> > > > on each database. I would like to write a script that would strip
all
> > > user
> > > > tables of their indexes and then run a script that would add the
> indexes
> > I
> > > > want on them. I have no problem writing the script to create the
> > indexes.
> > > > What I need is a script that will delete read the system table and
> > delete
> > > > all of the indexes in a particular database (including primary
keys).
> > > >
> > > > If anybody has such a thing and wouldn't mind sharing it, I would be
> > most
> > > > grateful.
> > > >
> > > >
> > >
> > >
> >
> >
>|||You can loop through the sysconstraints table first to remove all the
constraints and then do the indexes.
--
Andrew J. Kelly
SQL Server MVP
"John Hamilton" <jhamil@.nowhere.com> wrote in message
news:e6gR0AITDHA.2180@.TK2MSFTNGP10.phx.gbl...
> What is the best way to seperate which are regular indexes and which are
> primary keys?
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:e1E7LwHTDHA.2148@.TK2MSFTNGP12.phx.gbl...
> > You need to use DROP CONSTRAINT on constraints.
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "John Hamilton" <jhamil@.nowhere.com> wrote in message
> > news:ukge0sHTDHA.2020@.TK2MSFTNGP11.phx.gbl...
> > > Here is the actual error:
> > >
> > > An explicit DROP INDEX is not allowed on index
> > > 'tblTableName.PrimaryKeyName_PK'. It is being used for PRIMARY KEY
> > > constraint enforcement.
> > >
> > > "Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
> > > news:OY7JRpGTDHA.1912@.tk2msftngp13.phx.gbl...
> > > > John,
> > > >
> > > > Execute the output of the below query .
> > > >
> > > > SELECT 'DROP INDEX ' + OBJECT_NAME([id]) + '.' +
[name]+CHAR(13)+'GO'
> > > > FROM sysindexes
> > > > WHERE indid BETWEEN 1 AND 250
> > > > AND OBJECTPROPERTY([id], 'IsMsShipped') = 0
> > > > AND INDEXPROPERTY([id], [name], 'IsStatistics') = 0
> > > >
> > > >
> > > > --
> > > > Dinesh.
> > > > SQL Server FAQ at
> > > > http://www.tkdinesh.com
> > > >
> > > > "John Hamilton" <jhamil@.nowhere.com> wrote in message
> > > > news:uFM7uiGTDHA.2852@.tk2msftngp13.phx.gbl...
> > > > > I have several databases that are supposed to have the same
> structure,
> > > > over
> > > > > time some things (mostly indexes) have been changed, added, etc.
> > > > differently
> > > > > on each database. I would like to write a script that would strip
> all
> > > > user
> > > > > tables of their indexes and then run a script that would add the
> > indexes
> > > I
> > > > > want on them. I have no problem writing the script to create the
> > > indexes.
> > > > > What I need is a script that will delete read the system table and
> > > delete
> > > > > all of the indexes in a particular database (including primary
> keys).
> > > > >
> > > > > If anybody has such a thing and wouldn't mind sharing it, I would
be
> > > most
> > > > > grateful.
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Thanks...that did it.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uL1KjTITDHA.3144@.tk2msftngp13.phx.gbl...
> You can loop through the sysconstraints table first to remove all the
> constraints and then do the indexes.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "John Hamilton" <jhamil@.nowhere.com> wrote in message
> news:e6gR0AITDHA.2180@.TK2MSFTNGP10.phx.gbl...
> > What is the best way to seperate which are regular indexes and which are
> > primary keys?
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:e1E7LwHTDHA.2148@.TK2MSFTNGP12.phx.gbl...
> > > You need to use DROP CONSTRAINT on constraints.
> > >
> > > --
> > >
> > > Andrew J. Kelly
> > > SQL Server MVP
> > >
> > >
> > > "John Hamilton" <jhamil@.nowhere.com> wrote in message
> > > news:ukge0sHTDHA.2020@.TK2MSFTNGP11.phx.gbl...
> > > > Here is the actual error:
> > > >
> > > > An explicit DROP INDEX is not allowed on index
> > > > 'tblTableName.PrimaryKeyName_PK'. It is being used for PRIMARY KEY
> > > > constraint enforcement.
> > > >
> > > > "Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
> > > > news:OY7JRpGTDHA.1912@.tk2msftngp13.phx.gbl...
> > > > > John,
> > > > >
> > > > > Execute the output of the below query .
> > > > >
> > > > > SELECT 'DROP INDEX ' + OBJECT_NAME([id]) + '.' +
> [name]+CHAR(13)+'GO'
> > > > > FROM sysindexes
> > > > > WHERE indid BETWEEN 1 AND 250
> > > > > AND OBJECTPROPERTY([id], 'IsMsShipped') = 0
> > > > > AND INDEXPROPERTY([id], [name], 'IsStatistics') = 0
> > > > >
> > > > >
> > > > > --
> > > > > Dinesh.
> > > > > SQL Server FAQ at
> > > > > http://www.tkdinesh.com
> > > > >
> > > > > "John Hamilton" <jhamil@.nowhere.com> wrote in message
> > > > > news:uFM7uiGTDHA.2852@.tk2msftngp13.phx.gbl...
> > > > > > I have several databases that are supposed to have the same
> > structure,
> > > > > over
> > > > > > time some things (mostly indexes) have been changed, added, etc.
> > > > > differently
> > > > > > on each database. I would like to write a script that would
strip
> > all
> > > > > user
> > > > > > tables of their indexes and then run a script that would add the
> > > indexes
> > > > I
> > > > > > want on them. I have no problem writing the script to create
the
> > > > indexes.
> > > > > > What I need is a script that will delete read the system table
and
> > > > delete
> > > > > > all of the indexes in a particular database (including primary
> > keys).
> > > > > >
> > > > > > If anybody has such a thing and wouldn't mind sharing it, I
would
> be
> > > > most
> > > > > > grateful.
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

Defragment all indexes in a database?

How can I defragment all the indexes in my databases using dbcc indexdefrag?Have you read this?
http://msdn.microsoft.com/library/d...
0o9.asp
ML|||This defrags only 1 index at a time. I would like to defrag all the indexes
in the database with one command
"ML" wrote:

> Have you read this?
> http://msdn.microsoft.com/library/d...r />
_30o9.asp
>
> ML|||Google the usage for the undocumented SP called sp_MSforeachtable. It can be
used to execute a command for each table in a database.
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:039DB153-5857-483C-8BDF-97DB1AD6B3AE@.microsoft.com...
> This defrags only 1 index at a time. I would like to defrag all the
> indexes
> in the database with one command
> "ML" wrote:
>|||Here, I hope this helps.
http://milambda.blogspot.com/2005/0...in-current.html
ML|||I work for a software house which has developed database defragmentation sof
tware for SQL Server. It can defrag more than one index at a time and has sc
heduling facilities built in.
If anyone is interested in beta-testing it, I would like to hear from you as
it is close to launch. My company is willing to offer a free copy of the so
ftware to anyone who can provide genuine and useful feedback. Please send me
a private message from the members' section if you are interested.
Regards,
Martin
[QUOTE]Originally posted by Mike
How can I defragment all the indexes in my databases using dbcc indexdefrag? [/QUOTE
]

Defrag Tool?

I need to know if there are any good tools available to defrag/reindex
SQL2000 databases? I have to manage my own server and I am not a SQL guru. I
run Diskkeeper to take care of the physical files, but I need an easy and
effective way to defrag/reindex the SQL databases.
Thanks,
DerekHi Derek,
You can use DBCC DBREINDEX to reindex and DBCC INDEXDEFRAG to defragment the
indexes. DBCC DBREINDEX will defragment as well, so you don't have to run
them both. DBRINDEX will take out locks on your tables though, so you can
only run it when your database is not in use or very quiet.
You can run DBCC DBREINDEX on all tables with:
EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
And you can create a script to defrag all indexes with:
SELECT 'SELECT ''Defragging index ' + name + ' on table ' + OBJECT_NAME (id)
+ ''''+ CHAR(13) +
'DBCC INDEXDEFRAG(0, ' + CONVERT(VARCHAR, id) + ', ' + CONVERT(VARCHAR,
indid) + ')' + CHAR(13) +
'DBCC SHRINKFILE(1, TRUNCATEONLY)'
FROM sysindexes
WHERE indid BETWEEN 1 AND 254
AND OBJECTPROPERTY(id, 'IsMSShipped') = 0
AND INDEXPROPERTY(id, name, 'IsStatistics') = 0
ORDER BY OBJECT_NAME(id), indid
--
Jacco Schalkwijk
SQL Server MVP
"Derek" <derekb@.nospamderekb.com> wrote in message
news:%239PFWyS6DHA.2628@.TK2MSFTNGP10.phx.gbl...
> I need to know if there are any good tools available to defrag/reindex
> SQL2000 databases? I have to manage my own server and I am not a SQL guru.
I
> run Diskkeeper to take care of the physical files, but I need an easy and
> effective way to defrag/reindex the SQL databases.
> Thanks,
> Derek
>|||A disk defrag doesn't really work with SQL Server, apart
from getting the files in one continuous chunk of disk
space.
However you can defrag the files themselves with the DBCC
INDEXDEFRAG command.
J
>--Original Message--
>I need to know if there are any good tools available to
defrag/reindex
>SQL2000 databases? I have to manage my own server and I
am not a SQL guru. I
>run Diskkeeper to take care of the physical files, but I
need an easy and
>effective way to defrag/reindex the SQL databases.
>Thanks,
>Derek
>
>.
>|||Thanks. Just to make sure, I would ut the following into Query Analyzer and
run:
EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
Also, How do I make my databases offline to run?
Thanks much!
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:uzS2ZQX6DHA.2496@.TK2MSFTNGP09.phx.gbl...
> Hi Derek,
> You can use DBCC DBREINDEX to reindex and DBCC INDEXDEFRAG to defragment
the
> indexes. DBCC DBREINDEX will defragment as well, so you don't have to run
> them both. DBRINDEX will take out locks on your tables though, so you can
> only run it when your database is not in use or very quiet.
> You can run DBCC DBREINDEX on all tables with:
> EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
> And you can create a script to defrag all indexes with:
> SELECT 'SELECT ''Defragging index ' + name + ' on table ' + OBJECT_NAME
(id)
> + ''''+ CHAR(13) +
> 'DBCC INDEXDEFRAG(0, ' + CONVERT(VARCHAR, id) + ', ' + CONVERT(VARCHAR,
> indid) + ')' + CHAR(13) +
> 'DBCC SHRINKFILE(1, TRUNCATEONLY)'
> FROM sysindexes
> WHERE indid BETWEEN 1 AND 254
> AND OBJECTPROPERTY(id, 'IsMSShipped') = 0
> AND INDEXPROPERTY(id, name, 'IsStatistics') = 0
> ORDER BY OBJECT_NAME(id), indid
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Derek" <derekb@.nospamderekb.com> wrote in message
> news:%239PFWyS6DHA.2628@.TK2MSFTNGP10.phx.gbl...
> > I need to know if there are any good tools available to defrag/reindex
> > SQL2000 databases? I have to manage my own server and I am not a SQL
guru.
> I
> > run Diskkeeper to take care of the physical files, but I need an easy
and
> > effective way to defrag/reindex the SQL databases.
> >
> > Thanks,
> > Derek
> >
> >
>|||Hi Derek,
SQL Server databases do not have to be taken offline to be reindexed. The
reindexing process will block other processes from accessing the tables and
indexes that are being reindexed though, which might be inconvenient if the
reindexing takes a long time.
If that is an issue, you can prevent users from connecting to the database
with (SQL Server 2000 only):
ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
and after the reindexing you can give them access again with:
ALTER DATABASE <database name> SET MULTI_USER
--
Jacco Schalkwijk
SQL Server MVP
"Derek" <derekb@.nospamderekb.com> wrote in message
news:%23PZdXPZ6DHA.2524@.TK2MSFTNGP11.phx.gbl...
> Thanks. Just to make sure, I would ut the following into Query Analyzer
and
> run:
> EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
> Also, How do I make my databases offline to run?
> Thanks much!
>
> "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> news:uzS2ZQX6DHA.2496@.TK2MSFTNGP09.phx.gbl...
> > Hi Derek,
> >
> > You can use DBCC DBREINDEX to reindex and DBCC INDEXDEFRAG to defragment
> the
> > indexes. DBCC DBREINDEX will defragment as well, so you don't have to
run
> > them both. DBRINDEX will take out locks on your tables though, so you
can
> > only run it when your database is not in use or very quiet.
> >
> > You can run DBCC DBREINDEX on all tables with:
> > EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
> >
> > And you can create a script to defrag all indexes with:
> > SELECT 'SELECT ''Defragging index ' + name + ' on table ' + OBJECT_NAME
> (id)
> > + ''''+ CHAR(13) +
> > 'DBCC INDEXDEFRAG(0, ' + CONVERT(VARCHAR, id) + ', ' + CONVERT(VARCHAR,
> > indid) + ')' + CHAR(13) +
> > 'DBCC SHRINKFILE(1, TRUNCATEONLY)'
> > FROM sysindexes
> > WHERE indid BETWEEN 1 AND 254
> > AND OBJECTPROPERTY(id, 'IsMSShipped') = 0
> > AND INDEXPROPERTY(id, name, 'IsStatistics') = 0
> > ORDER BY OBJECT_NAME(id), indid
> >
> > --
> > Jacco Schalkwijk
> > SQL Server MVP
> >
> >
> > "Derek" <derekb@.nospamderekb.com> wrote in message
> > news:%239PFWyS6DHA.2628@.TK2MSFTNGP10.phx.gbl...
> > > I need to know if there are any good tools available to defrag/reindex
> > > SQL2000 databases? I have to manage my own server and I am not a SQL
> guru.
> > I
> > > run Diskkeeper to take care of the physical files, but I need an easy
> and
> > > effective way to defrag/reindex the SQL databases.
> > >
> > > Thanks,
> > > Derek
> > >
> > >
> >
> >
>|||Thanks, I should have been more specific. Diskkeeper takes care of the
external files, I was referring to defrag internally. But I think Jacco has
me taken care of. Thanks anyway!
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:889901c3e97b$4907e810$a401280a@.phx.gbl...
> A disk defrag doesn't really work with SQL Server, apart
> from getting the files in one continuous chunk of disk
> space.
> However you can defrag the files themselves with the DBCC
> INDEXDEFRAG command.
> J
>
> >--Original Message--
> >I need to know if there are any good tools available to
> defrag/reindex
> >SQL2000 databases? I have to manage my own server and I
> am not a SQL guru. I
> >run Diskkeeper to take care of the physical files, but I
> need an easy and
> >effective way to defrag/reindex the SQL databases.
> >
> >Thanks,
> >Derek
> >
> >
> >.
> >|||Thanks! I will try this later tonight
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:%23Cwm3YZ6DHA.2952@.tk2msftngp13.phx.gbl...
> Hi Derek,
> SQL Server databases do not have to be taken offline to be reindexed. The
> reindexing process will block other processes from accessing the tables
and
> indexes that are being reindexed though, which might be inconvenient if
the
> reindexing takes a long time.
> If that is an issue, you can prevent users from connecting to the database
> with (SQL Server 2000 only):
> ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> and after the reindexing you can give them access again with:
> ALTER DATABASE <database name> SET MULTI_USER
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Derek" <derekb@.nospamderekb.com> wrote in message
> news:%23PZdXPZ6DHA.2524@.TK2MSFTNGP11.phx.gbl...
> > Thanks. Just to make sure, I would ut the following into Query Analyzer
> and
> > run:
> >
> > EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
> >
> > Also, How do I make my databases offline to run?
> >
> > Thanks much!
> >
> >
> > "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> > news:uzS2ZQX6DHA.2496@.TK2MSFTNGP09.phx.gbl...
> > > Hi Derek,
> > >
> > > You can use DBCC DBREINDEX to reindex and DBCC INDEXDEFRAG to
defragment
> > the
> > > indexes. DBCC DBREINDEX will defragment as well, so you don't have to
> run
> > > them both. DBRINDEX will take out locks on your tables though, so you
> can
> > > only run it when your database is not in use or very quiet.
> > >
> > > You can run DBCC DBREINDEX on all tables with:
> > > EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
> > >
> > > And you can create a script to defrag all indexes with:
> > > SELECT 'SELECT ''Defragging index ' + name + ' on table ' +
OBJECT_NAME
> > (id)
> > > + ''''+ CHAR(13) +
> > > 'DBCC INDEXDEFRAG(0, ' + CONVERT(VARCHAR, id) + ', ' +
CONVERT(VARCHAR,
> > > indid) + ')' + CHAR(13) +
> > > 'DBCC SHRINKFILE(1, TRUNCATEONLY)'
> > > FROM sysindexes
> > > WHERE indid BETWEEN 1 AND 254
> > > AND OBJECTPROPERTY(id, 'IsMSShipped') = 0
> > > AND INDEXPROPERTY(id, name, 'IsStatistics') = 0
> > > ORDER BY OBJECT_NAME(id), indid
> > >
> > > --
> > > Jacco Schalkwijk
> > > SQL Server MVP
> > >
> > >
> > > "Derek" <derekb@.nospamderekb.com> wrote in message
> > > news:%239PFWyS6DHA.2628@.TK2MSFTNGP10.phx.gbl...
> > > > I need to know if there are any good tools available to
defrag/reindex
> > > > SQL2000 databases? I have to manage my own server and I am not a SQL
> > guru.
> > > I
> > > > run Diskkeeper to take care of the physical files, but I need an
easy
> > and
> > > > effective way to defrag/reindex the SQL databases.
> > > >
> > > > Thanks,
> > > > Derek
> > > >
> > > >
> > >
> > >
> >
> >
>

Defrag Tool?

I need to know if there are any good tools available to defrag/reindex
SQL2000 databases? I have to manage my own server and I am not a SQL guru. I
run Diskkeeper to take care of the physical files, but I need an easy and
effective way to defrag/reindex the SQL databases.
Thanks,
DerekHi Derek,
You can use DBCC DBREINDEX to reindex and DBCC INDEXDEFRAG to defragment the
indexes. DBCC DBREINDEX will defragment as well, so you don't have to run
them both. DBRINDEX will take out locks on your tables though, so you can
only run it when your database is not in use or very quiet.
You can run DBCC DBREINDEX on all tables with:
EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
And you can create a script to defrag all indexes with:
SELECT 'SELECT ''Defragging index ' + name + ' on table ' + OBJECT_NAME (id)
+ ''''+ CHAR(13) +
'DBCC INDEXDEFRAG(0, ' + CONVERT(VARCHAR, id) + ', ' + CONVERT(VARCHAR,
indid) + ')' + CHAR(13) +
'DBCC SHRINKFILE(1, TRUNCATEONLY)'
FROM sysindexes
WHERE indid BETWEEN 1 AND 254
AND OBJECTPROPERTY(id, 'IsMSShipped') = 0
AND INDEXPROPERTY(id, name, 'IsStatistics') = 0
ORDER BY OBJECT_NAME(id), indid
Jacco Schalkwijk
SQL Server MVP
"Derek" <derekb@.nospamderekb.com> wrote in message
news:%239PFWyS6DHA.2628@.TK2MSFTNGP10.phx.gbl...

quote:

---

> I need to know if there are any good tools available to defrag/reindex
> SQL2000 databases? I have to manage my own server and I am not a SQL guru.

---

I

quote:

---

> run Diskkeeper to take care of the physical files, but I need an easy and
> effective way to defrag/reindex the SQL databases.
> Thanks,
> Derek
>

---

|||Thanks. Just to make sure, I would ut the following into Query Analyzer and
run:
EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
Also, How do I make my databases offline to run?
Thanks much!
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:uzS2ZQX6DHA.2496@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Hi Derek,
> You can use DBCC DBREINDEX to reindex and DBCC INDEXDEFRAG to defragment

---

the

quote:

---

> indexes. DBCC DBREINDEX will defragment as well, so you don't have to run
> them both. DBRINDEX will take out locks on your tables though, so you can
> only run it when your database is not in use or very quiet.
> You can run DBCC DBREINDEX on all tables with:
> EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
> And you can create a script to defrag all indexes with:
> SELECT 'SELECT ''Defragging index ' + name + ' on table ' + OBJECT_NAME

---

(id)

quote:

---

> + ''''+ CHAR(13) +
> 'DBCC INDEXDEFRAG(0, ' + CONVERT(VARCHAR, id) + ', ' + CONVERT(VARCHAR,
> indid) + ')' + CHAR(13) +
> 'DBCC SHRINKFILE(1, TRUNCATEONLY)'
> FROM sysindexes
> WHERE indid BETWEEN 1 AND 254
> AND OBJECTPROPERTY(id, 'IsMSShipped') = 0
> AND INDEXPROPERTY(id, name, 'IsStatistics') = 0
> ORDER BY OBJECT_NAME(id), indid
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Derek" <derekb@.nospamderekb.com> wrote in message
> news:%239PFWyS6DHA.2628@.TK2MSFTNGP10.phx.gbl...
guru.[QUOTE]
> I
and[QUOTE]
>

---

|||Hi Derek,
SQL Server databases do not have to be taken offline to be reindexed. The
reindexing process will block other processes from accessing the tables and
indexes that are being reindexed though, which might be inconvenient if the
reindexing takes a long time.
If that is an issue, you can prevent users from connecting to the database
with (SQL Server 2000 only):
ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
and after the reindexing you can give them access again with:
ALTER DATABASE <database name> SET MULTI_USER
Jacco Schalkwijk
SQL Server MVP
"Derek" <derekb@.nospamderekb.com> wrote in message
news:%23PZdXPZ6DHA.2524@.TK2MSFTNGP11.phx.gbl...

quote:

---

> Thanks. Just to make sure, I would ut the following into Query Analyzer

---

and

quote:

---

> run:
> EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
> Also, How do I make my databases offline to run?
> Thanks much!
>
> "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> news:uzS2ZQX6DHA.2496@.TK2MSFTNGP09.phx.gbl...
> the
run[QUOTE]
can[QUOTE]
> (id)
> guru.
> and
>

---

|||Thanks! I will try this later tonight
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:%23Cwm3YZ6DHA.2952@.tk2msftngp13.phx.gbl...

quote:

---

> Hi Derek,
> SQL Server databases do not have to be taken offline to be reindexed. The
> reindexing process will block other processes from accessing the tables

---

and

quote:

---

> indexes that are being reindexed though, which might be inconvenient if

---

the

quote:

---

> reindexing takes a long time.
> If that is an issue, you can prevent users from connecting to the database
> with (SQL Server 2000 only):
> ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> and after the reindexing you can give them access again with:
> ALTER DATABASE <database name> SET MULTI_USER
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Derek" <derekb@.nospamderekb.com> wrote in message
> news:%23PZdXPZ6DHA.2524@.TK2MSFTNGP11.phx.gbl...
> and
defragment[QUOTE]
> run
> can
OBJECT_NAME[QUOTE]
CONVERT(VARCHAR,[QUOTE]
defrag/reindex[QUOTE]
easy[QUOTE]
>

---

Defrag data disk

Hi,
The disks on which my databases are stored is heavily
fragmented. This because of wrong database grow settings.
Can I use the Windows system tool: Disk Defragmenter to
defragment my disk or will SQL not like this?
Thanks,
Jeroen.I believe you can, so long as the block size <= 4KB.
You will need to stop sql server if you want the data files defragemented
"Jeroen" <nieuwdamsigt@.hotmail.com> wrote in message
news:006101c3c31f$9e391750$a101280a@.phx.gbl...
> Hi,
> The disks on which my databases are stored is heavily
> fragmented. This because of wrong database grow settings.
> Can I use the Windows system tool: Disk Defragmenter to
> defragment my disk or will SQL not like this?
> Thanks,
> Jeroen.|||"Jeroen" <nieuwdamsigt@.hotmail.com> wrote in message
news:006101c3c31f$9e391750$a101280a@.phx.gbl...
> Hi,
> The disks on which my databases are stored is heavily
> fragmented. This because of wrong database grow settings.
> Can I use the Windows system tool: Disk Defragmenter to
> defragment my disk or will SQL not like this?
>
Also your disks may appear more fragmented than they really are.
If you have a few large files that are in 2 fragments, you disk can report
as being 90% fragmented without this being a big deal.
David

Defining the Scope of SCOPE_IDENTITY

A couple of Web applications in different SQL Server 2000 databases use SCOPE_IDENTITY to retrieve the key value of a record that was just inserted. It works--most of the time. However, from time to time the identity value is not retrieved. Evidence suggests that in these cases, a null value is being retrieved. This has forced me to come up with less-than-ideal workarounds for the missing identity value.

Does anyone have any idea why SCOPE_IDENTITY sometimes fails to retrieve the identity value and transmit it back to the Web page? Could a network issue cause the problem? Is there anything I can do other than rewrite the apps to use a different algorithm than using SCOPE_IDENTITY? Thanks.

I am not aware of any issues with SCOPE_IDENTITY(); this might be an application / connection issue and not a problem with SCOPE_IDENTITY(). I am certainly interested in the outcome of this. Can somebody please check me on this?|||

If you are using embedded SQL in your application it might be worth placing this logic into a stored procedure and calling that from your application. That should avoid any comms problems as the procedure will run or not run as a single call (and not have a problem between statements in the operation).

|||

Yes, the web app uses embedded SQL in classic ASP. The application was written in classic ASP and there has never been a good reason to rewrite it. The web app is the only application that performs DML on the table--there are no separate triggers or other ways into the table.

How could an embedded SQL statement in a single Web page cause scope problems? One Web page consulted during the research on this problem said this situation should be treated as a single scope.

I will probably try the stored procedure method. But I am curious as to why all sources practically demand that SCOPE_IDENTITY be used within a stored procedure when it is allowed to work in other situations.

Thanks for the input.

Defining Alerts

Dear All,
Recently all our production databases were marked
as 'Suspect', anyway after a bit of work it was repaired.
However the only reason we found this out was the users
couldn't get in.
What I would like to do is define some alerts such
as 'database Production is not working', or replication is
no longer working.
I know where to do this, what I cannot find is background
information on what alert to raise (I have tried BOL on
Microsoft). Can anyone provide me background info, for
instance does alert 025 mean any error to a database which
stops it working ?
Thanks
J
Hi
Alert 025 is reased when a Error, with severity level 25 occurs.
You must check for error levels 20 and higher, those are very critcal.
The DB suspect error is Error 926, Severity Level 14
Regards
Mike
"Julie" wrote:

> Dear All,
> Recently all our production databases were marked
> as 'Suspect', anyway after a bit of work it was repaired.
> However the only reason we found this out was the users
> couldn't get in.
> What I would like to do is define some alerts such
> as 'database Production is not working', or replication is
> no longer working.
> I know where to do this, what I cannot find is background
> information on what alert to raise (I have tried BOL on
> Microsoft). Can anyone provide me background info, for
> instance does alert 025 mean any error to a database which
> stops it working ?
> Thanks
> J
>
|||Thanks Mike,
Is there any background information I can read up on ?
J

>--Original Message--
>Hi
>Alert 025 is reased when a Error, with severity level 25
occurs.
>You must check for error levels 20 and higher, those are
very critcal.[vbcol=seagreen]
>The DB suspect error is Error 926, Severity Level 14
>Regards
>Mike
>"Julie" wrote:
repaired.[vbcol=seagreen]
is[vbcol=seagreen]
background[vbcol=seagreen]
which
>.
>
|||Hi
http://msdn.microsoft.com/library/de...ar_cs_6x0l.asp
There is not much other infromation out there apart from what is in BOL.
Regards
Mike
"Julie" wrote:

> Thanks Mike,
> Is there any background information I can read up on ?
> J
>
> occurs.
> very critcal.
> repaired.
> is
> background
> which
>
|||Thanks Mike

>--Original Message--
>Hi
>http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/architec/8_ar_cs_6x0l.asp
>There is not much other infromation out there apart from
what is in BOL.[vbcol=seagreen]
>Regards
>Mike
>"Julie" wrote:
25[vbcol=seagreen]
are[vbcol=seagreen]
users[vbcol=seagreen]
replication[vbcol=seagreen]
on[vbcol=seagreen]
for
>.
>

Defining Alerts

Dear All,
Recently all our production databases were marked
as 'Suspect', anyway after a bit of work it was repaired.
However the only reason we found this out was the users
couldn't get in.
What I would like to do is define some alerts such
as 'database Production is not working', or replication is
no longer working.
I know where to do this, what I cannot find is background
information on what alert to raise (I have tried BOL on
Microsoft). Can anyone provide me background info, for
instance does alert 025 mean any error to a database which
stops it working ?
Thanks
JHi
Alert 025 is reased when a Error, with severity level 25 occurs.
You must check for error levels 20 and higher, those are very critcal.
The DB suspect error is Error 926, Severity Level 14
Regards
Mike
"Julie" wrote:
> Dear All,
> Recently all our production databases were marked
> as 'Suspect', anyway after a bit of work it was repaired.
> However the only reason we found this out was the users
> couldn't get in.
> What I would like to do is define some alerts such
> as 'database Production is not working', or replication is
> no longer working.
> I know where to do this, what I cannot find is background
> information on what alert to raise (I have tried BOL on
> Microsoft). Can anyone provide me background info, for
> instance does alert 025 mean any error to a database which
> stops it working ?
> Thanks
> J
>|||Thanks Mike,
Is there any background information I can read up on ?
J
>--Original Message--
>Hi
>Alert 025 is reased when a Error, with severity level 25
occurs.
>You must check for error levels 20 and higher, those are
very critcal.
>The DB suspect error is Error 926, Severity Level 14
>Regards
>Mike
>"Julie" wrote:
>> Dear All,
>> Recently all our production databases were marked
>> as 'Suspect', anyway after a bit of work it was
repaired.
>> However the only reason we found this out was the users
>> couldn't get in.
>> What I would like to do is define some alerts such
>> as 'database Production is not working', or replication
is
>> no longer working.
>> I know where to do this, what I cannot find is
background
>> information on what alert to raise (I have tried BOL on
>> Microsoft). Can anyone provide me background info, for
>> instance does alert 025 mean any error to a database
which
>> stops it working ?
>> Thanks
>> J
>.
>|||Hi
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_cs_6x0l.asp
There is not much other infromation out there apart from what is in BOL.
Regards
Mike
"Julie" wrote:
> Thanks Mike,
> Is there any background information I can read up on ?
> J
>
> >--Original Message--
> >Hi
> >
> >Alert 025 is reased when a Error, with severity level 25
> occurs.
> >
> >You must check for error levels 20 and higher, those are
> very critcal.
> >
> >The DB suspect error is Error 926, Severity Level 14
> >
> >Regards
> >Mike
> >
> >"Julie" wrote:
> >
> >> Dear All,
> >> Recently all our production databases were marked
> >> as 'Suspect', anyway after a bit of work it was
> repaired.
> >> However the only reason we found this out was the users
> >> couldn't get in.
> >>
> >> What I would like to do is define some alerts such
> >> as 'database Production is not working', or replication
> is
> >> no longer working.
> >>
> >> I know where to do this, what I cannot find is
> background
> >> information on what alert to raise (I have tried BOL on
> >> Microsoft). Can anyone provide me background info, for
> >> instance does alert 025 mean any error to a database
> which
> >> stops it working ?
> >>
> >> Thanks
> >> J
> >>
> >.
> >
>|||Thanks Mike
>--Original Message--
>Hi
>http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/architec/8_ar_cs_6x0l.asp
>There is not much other infromation out there apart from
what is in BOL.
>Regards
>Mike
>"Julie" wrote:
>> Thanks Mike,
>> Is there any background information I can read up on ?
>> J
>>
>> >--Original Message--
>> >Hi
>> >
>> >Alert 025 is reased when a Error, with severity level
25
>> occurs.
>> >
>> >You must check for error levels 20 and higher, those
are
>> very critcal.
>> >
>> >The DB suspect error is Error 926, Severity Level 14
>> >
>> >Regards
>> >Mike
>> >
>> >"Julie" wrote:
>> >
>> >> Dear All,
>> >> Recently all our production databases were marked
>> >> as 'Suspect', anyway after a bit of work it was
>> repaired.
>> >> However the only reason we found this out was the
users
>> >> couldn't get in.
>> >>
>> >> What I would like to do is define some alerts such
>> >> as 'database Production is not working', or
replication
>> is
>> >> no longer working.
>> >>
>> >> I know where to do this, what I cannot find is
>> background
>> >> information on what alert to raise (I have tried BOL
on
>> >> Microsoft). Can anyone provide me background info,
for
>> >> instance does alert 025 mean any error to a database
>> which
>> >> stops it working ?
>> >>
>> >> Thanks
>> >> J
>> >>
>> >.
>> >
>.
>

Defining Alerts

Dear All,
Recently all our production databases were marked
as 'Suspect', anyway after a bit of work it was repaired.
However the only reason we found this out was the users
couldn't get in.
What I would like to do is define some alerts such
as 'database Production is not working', or replication is
no longer working.
I know where to do this, what I cannot find is background
information on what alert to raise (I have tried BOL on
Microsoft). Can anyone provide me background info, for
instance does alert 025 mean any error to a database which
stops it working ?
Thanks
JHi
Alert 025 is reased when a Error, with severity level 25 occurs.
You must check for error levels 20 and higher, those are very critcal.
The DB suspect error is Error 926, Severity Level 14
Regards
Mike
"Julie" wrote:

> Dear All,
> Recently all our production databases were marked
> as 'Suspect', anyway after a bit of work it was repaired.
> However the only reason we found this out was the users
> couldn't get in.
> What I would like to do is define some alerts such
> as 'database Production is not working', or replication is
> no longer working.
> I know where to do this, what I cannot find is background
> information on what alert to raise (I have tried BOL on
> Microsoft). Can anyone provide me background info, for
> instance does alert 025 mean any error to a database which
> stops it working ?
> Thanks
> J
>|||Thanks Mike,
Is there any background information I can read up on ?
J

>--Original Message--
>Hi
>Alert 025 is reased when a Error, with severity level 25
occurs.
>You must check for error levels 20 and higher, those are
very critcal.
>The DB suspect error is Error 926, Severity Level 14
>Regards
>Mike
>"Julie" wrote:
>
repaired.[vbcol=seagreen]
is[vbcol=seagreen]
background[vbcol=seagreen]
which[vbcol=seagreen]
>.
>|||Hi
http://msdn.microsoft.com/library/d...br />
6x0l.asp
There is not much other infromation out there apart from what is in BOL.
Regards
Mike
"Julie" wrote:

> Thanks Mike,
> Is there any background information I can read up on ?
> J
>
> occurs.
> very critcal.
> repaired.
> is
> background
> which
>|||Thanks Mike

>--Original Message--
>Hi
>http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/architec/8_ar_cs_6x0l.asp
>There is not much other infromation out there apart from
what is in BOL.
>Regards
>Mike
>"Julie" wrote:
>
25[vbcol=seagreen]
are[vbcol=seagreen]
users[vbcol=seagreen]
replication[vbcol=seagreen]
on[vbcol=seagreen]
for[vbcol=seagreen]
>.
>

Define MySQL data source in a SQL Server Analysis Service Project

I want to use MySQL database as a data source fo an Analysis Service Project.

But there is no Provider that support MySQL Databases.

If anybody have any experiance about this, Please help me.

Thanks

Hi,

I know there are some providers out there, but I don't have any experience using them, but I do know they exist. Here is one that I found doing a quick search.

http://sourceforge.net/projects/myoledb/

David

|||

Loading data from MySQL database is not officially supported by Analysis Services.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Just because its not supported, doesn't mean it can't be done.

They only problem you may face in trying this provider is that it may not support all of the properties that Analysis Services requires to be implemented. But its worth a shot.

David

default values for database fields yes or no??

Building the database I have come across different databases some that add a default value for every field and some that don't. I feel it is a hassle to add a default value, keep track if it is added.

I guess with a default value there would be no "NULL" values in the database but one could also make sure in the C# code that all the fields have a value when inputed and on the way out check for nulls.

What is the right way??

Pros and cons.......

Newbie

Like a table that holds car information. If you have a field that holds the number of wheels, I would make that a default of 4.

Sort of like, if you don't mention how many wheels the car has, I'm going to assume 4. Now if the car has more/less than 4, you can tell me about it, and I'll remember.

It's not really what is "right" and what is "wrong". You can also say there is no default, and you don't tell me the number of wheels of every car, I'm not going to accept it. It forces you to make a choice for every record. Or you can have each record allow nulls, in which case if you don't mention it, we'll still accept it, but we won't make any assumption on the number of wheels.

My rule of thumb is, if it's necessary field, then no default value, and does not accept nulls.

If you can assume a value if one isn't specified (It rarely isn't a particular value), then I'll make it does not accept nulls, with a default value.

If I really don't care about the field at all, and it's a fluff field that I won't use, then I'll accept nulls and no default.

Default SQL 2000 Compatability Level problem

I have a SQL 200 server that creates all new databases in compatability level
65. This is creating problems for an application that requires level 80 to
create and populate. How do I change the default level for any new databases?
Chris Millette
MCP/Network Administrator
Community Bank & Trust
The process that creates the database deletes it if it cannot complete the
entire process. I did find a scipt that changes the model db level and that
resolved the issue.
Chris Millette
MCP/Network Administrator
Community Bank & Trust
"Hari Prasad" wrote:
[vbcol=seagreen]
> Change the compatibility level of Model database to 80; after that what ever
> database you create newly
> the compatibility level will be 80.
> Thanks
> Hari
> "Millette" wrote:

Default SQL 2000 Compatability Level problem

I have a SQL 200 server that creates all new databases in compatability level
65. This is creating problems for an application that requires level 80 to
create and populate. How do I change the default level for any new databases?
--
Chris Millette
MCP/Network Administrator
Community Bank & TrustChange the compatibility level of Model database to 80; after that what ever
database you create newly
the compatibility level will be 80.
Thanks
Hari
"Millette" wrote:
> I have a SQL 200 server that creates all new databases in compatability level
> 65. This is creating problems for an application that requires level 80 to
> create and populate. How do I change the default level for any new databases?
> --
> Chris Millette
> MCP/Network Administrator
> Community Bank & Trust|||The process that creates the database deletes it if it cannot complete the
entire process. I did find a scipt that changes the model db level and that
resolved the issue.
--
Chris Millette
MCP/Network Administrator
Community Bank & Trust
"Hari Prasad" wrote:
> Change the compatibility level of Model database to 80; after that what ever
> database you create newly
> the compatibility level will be 80.
> Thanks
> Hari
> "Millette" wrote:
> > I have a SQL 200 server that creates all new databases in compatability level
> > 65. This is creating problems for an application that requires level 80 to
> > create and populate. How do I change the default level for any new databases?
> > --
> > Chris Millette
> > MCP/Network Administrator
> > Community Bank & Trust

Default SQL 2000 Compatability Level problem

I have a SQL 200 server that creates all new databases in compatability leve
l
65. This is creating problems for an application that requires level 80 to
create and populate. How do I change the default level for any new databases
?
--
Chris Millette
MCP/Network Administrator
Community Bank & TrustChange the compatibility level of Model database to 80; after that what ever
database you create newly
the compatibility level will be 80.
Thanks
Hari
"Millette" wrote:

> I have a SQL 200 server that creates all new databases in compatability le
vel
> 65. This is creating problems for an application that requires level 80 to
> create and populate. How do I change the default level for any new databas
es?
> --
> Chris Millette
> MCP/Network Administrator
> Community Bank & Trust|||The process that creates the database deletes it if it cannot complete the
entire process. I did find a scipt that changes the model db level and that
resolved the issue.
--
Chris Millette
MCP/Network Administrator
Community Bank & Trust
"Hari Prasad" wrote:
[vbcol=seagreen]
> Change the compatibility level of Model database to 80; after that what ev
er
> database you create newly
> the compatibility level will be 80.
> Thanks
> Hari
> "Millette" wrote:
>

default settings when creating a database

Hello,
Some users create new databases using the enterprise manager console (right
click in the database section and then selecting new database).
Once created this database has default settings like "Auto shrink" checked
or "Auto close" unchecked (in the option tab).
Is there a way to set these default settings so each time a new database is
created it has the right options?
thanks
Yes, set the correct options on the Model database. That database is the
baseline from which all other databases on a server are created.
<grille11@.yahoo.com> wrote in message
news:cjju9f$bb5$1@.reader1.imaginet.fr...
> Hello,
> Some users create new databases using the enterprise manager console
(right
> click in the database section and then selecting new database).
> Once created this database has default settings like "Auto shrink" checked
> or "Auto close" unchecked (in the option tab).
> Is there a way to set these default settings so each time a new database
is
> created it has the right options?
> thanks
>
|||I could have searched a little more for this one I guess. Thanks!
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OMxDsF9pEHA.2864@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Yes, set the correct options on the Model database. That database is the
> baseline from which all other databases on a server are created.
>
> <grille11@.yahoo.com> wrote in message
> news:cjju9f$bb5$1@.reader1.imaginet.fr...
> (right
checked
> is
>

default settings when creating a database

Hello,
Some users create new databases using the enterprise manager console (right
click in the database section and then selecting new database).
Once created this database has default settings like "Auto shrink" checked
or "Auto close" unchecked (in the option tab).
Is there a way to set these default settings so each time a new database is
created it has the right options?
thanksYes, set the correct options on the Model database. That database is the
baseline from which all other databases on a server are created.
<grille11@.yahoo.com> wrote in message
news:cjju9f$bb5$1@.reader1.imaginet.fr...
> Hello,
> Some users create new databases using the enterprise manager console
(right
> click in the database section and then selecting new database).
> Once created this database has default settings like "Auto shrink" checked
> or "Auto close" unchecked (in the option tab).
> Is there a way to set these default settings so each time a new database
is
> created it has the right options?
> thanks
>|||I could have searched a little more for this one I guess. Thanks!
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OMxDsF9pEHA.2864@.TK2MSFTNGP12.phx.gbl...
> Yes, set the correct options on the Model database. That database is the
> baseline from which all other databases on a server are created.
>
> <grille11@.yahoo.com> wrote in message
> news:cjju9f$bb5$1@.reader1.imaginet.fr...
> > Hello,
> >
> > Some users create new databases using the enterprise manager console
> (right
> > click in the database section and then selecting new database).
> > Once created this database has default settings like "Auto shrink"
checked
> > or "Auto close" unchecked (in the option tab).
> > Is there a way to set these default settings so each time a new database
> is
> > created it has the right options?
> >
> > thanks
> >
> >
>

Default Public Database Role

Does anyone know the reason for this role, and if there
are any impact if I revoke/deny all permissions on this
role from the system databases (tables/SP/etc)?
Reason: All SQL logins default to this role, and can see
all system table contents.See Public role in SQL Books Online.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.