Database start trigger 2005

Hi,

I have searched high and low, and can not find any mention of SQL2005 and mount/start database DDL triggers.

I need a trigger/job/event to update a column in a table to indicate that the database has been restarted. I can not have an application connect to the database and set this value as I am afraid one of my other computers may connect first and get the wrong state from the database.

Is there any way to have SQL2005 update a table on MOUNT/Service Start/DB start?

Regards,
Derek

I solved it on my own. I use a StoredProcedure in the MASTER database and set it to start automatically.

~Derek

database replication / publication ?

Hi,
I'd like to keep an extra copy for database X on SQL server A to SQL server
B. The database X doesn't update very often during the day (but the computer
does have other jobs to run at tthe work hour). Both server A and B run SQL
server 2000 and on the same network. Currently the size of database X's MDF
and LDF are around 6 GB (3GB each). It would be nice to keep database X
always be in sync with its copy, but it is not a must. If the database
replication/publication takes a lot of CPU or is time comsuing, it is OK to
do it with a nightly job.
What is the good practice in the case? Thanks for sharing your thoughts.
Shawn,
for an extra copy of X on a standby server, and where latency is allowed,
I'd consider using Log Shipping. This article helps explain some of the key
differences: http://www.replicationanswers.com/Standby.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

database refusing to update - timeout

hi...i have a table which has about 100 cols and about 13000 rows...today
it has had problems updating...i kept getting timeout...i run queries from
ASP scripts...it is not any larger than other tables that work fine...it
does however have several cols which are text datatype. if i was viewing
the table in EM the updates would fail...when i closed the table view it
usually updated...is there an explanation for this behaviour...
also
the whole db is about 250MB on backup but when restored it is about 700
including the transaction log...i thought the transaction log might be too
big...it has been set to grow as big as it likes...is there a way to get the
db to update everything into the db files thus reducing the log to
nothing?...this probably sounds ignorant to the experts out there, i am a
developer who is also becoming a reluctant dba...
thanks for any advice offered
gdpThe update needs to take an exclusive lock which it won't
get if there are other locks including shared locks - see
books online topics Understanding Locking in SQL Server and
Lock compatability.
A backup file and the data files are two different
structures so there will be size differences. In terms of
shrinking the log file size to almost nothing, the log will
then need to grow to log subsequent transactions.
Consequently, you would be wasting resources by shrinking,
then having the log grow, then shrinking. It's best to leave
the log at the size it needs to grow to between backups so
that you aren't wasting resources. If the log file sizes is
excessive and you need to shrink it down a bit, you can use
dbcc shrinkfile. You can find more information on this in
the following article:
INF: Shrinking the Transaction Log in SQL Server 2000 with
DBCC SHRINKFILE
http://support.microsoft.com/?id=272318
You can keep the log at a reasonable size by performing
regular log backups. The backups will truncate the log. But
truncating a log file does not reduce it's physical size.
Truncation marks the parts of the log no longer needed for
recovery as inactive, allowing these parts of the log to be
reused for new transactions. To physically reduce the size
of the log, you need to use dbcc shrinkfile. But as I
already said, shrinking, growing, shrinking, growing isn't a
good use of SQL Server resources.
-Sue
On Mon, 3 Nov 2003 21:07:37 -0000, "gdp"
<gp014i0613@.blueyonder.co.uk> wrote:
>hi...i have a table which has about 100 cols and about 13000 rows...today
>it has had problems updating...i kept getting timeout...i run queries from
>ASP scripts...it is not any larger than other tables that work fine...it
>does however have several cols which are text datatype. if i was viewing
>the table in EM the updates would fail...when i closed the table view it
>usually updated...is there an explanation for this behaviour...
>also
>the whole db is about 250MB on backup but when restored it is about 700
>including the transaction log...i thought the transaction log might be too
>big...it has been set to grow as big as it likes...is there a way to get the
>db to update everything into the db files thus reducing the log to
>nothing?...this probably sounds ignorant to the experts out there, i am a
>developer who is also becoming a reluctant dba...
>thanks for any advice offered
>gdp
>

Database Readonly

I copied over a small website to 2003 Web Server and got:

Exception Details:System.Data.SqlClient.SqlException:Failed to update database"D:\INETPUB\WWWROOT\LUNCHCOUNT\APP_DATA\LUNCHCOUNT.MDF" because thedatabase is read-only.

It works just fine in VS - what to do?

Thanks

Whoops - should have followed the 10 minute rule. Found the folder didn't have Write turned on it permissions.|||Check if the MDF file is readonly file and change the permissions.

database owner with restrictions

I have group of users that I would like to have the ability to create tables
as dbo owners but would deny them insert,update and delete roles in some
tables.
It that scenario possible? If it is how?
Thanks in advance,
Tony
icwgroupdb_ddladmin role will allow the user to do DDL operations (i.e.
create/delete/alter <obj>). See this for other predefined roles:
http://msdn.microsoft.com/library/en-us/adminsql/ad_security_6ndx.asp
-oj
"tony-icwgroup" <tonyicwgroup@.discussions.microsoft.com> wrote in message
news:CA57FF63-5ABE-4B7C-8D3F-21150CE2BC56@.microsoft.com...
>I have group of users that I would like to have the ability to create
>tables
> as dbo owners but would deny them insert,update and delete roles in some
> tables.
> It that scenario possible? If it is how?
> Thanks in advance,
> Tony
> icwgroup

database owner with restrictions

I have group of users that I would like to have the ability to create tables
as dbo owners but would deny them insert,update and delete roles in some
tables.
It that scenario possible? If it is how?
Thanks in advance,
Tony
icwgroup
db_ddladmin role will allow the user to do DDL operations (i.e.
create/delete/alter <obj>). See this for other predefined roles:
http://msdn.microsoft.com/library/en...urity_6ndx.asp
-oj
"tony-icwgroup" <tonyicwgroup@.discussions.microsoft.com> wrote in message
news:CA57FF63-5ABE-4B7C-8D3F-21150CE2BC56@.microsoft.com...
>I have group of users that I would like to have the ability to create
>tables
> as dbo owners but would deny them insert,update and delete roles in some
> tables.
> It that scenario possible? If it is how?
> Thanks in advance,
> Tony
> icwgroup

database owner with restrictions

I have group of users that I would like to have the ability to create tables
as dbo owners but would deny them insert,update and delete roles in some
tables.
It that scenario possible? If it is how?
Thanks in advance,
Tony
icwgroupdb_ddladmin role will allow the user to do DDL operations (i.e.
create/delete/alter <obj> ). See this for other predefined roles:
http://msdn.microsoft.com/library/e...curity_6ndx.asp
-oj
"tony-icwgroup" <tonyicwgroup@.discussions.microsoft.com> wrote in message
news:CA57FF63-5ABE-4B7C-8D3F-21150CE2BC56@.microsoft.com...
>I have group of users that I would like to have the ability to create
>tables
> as dbo owners but would deny them insert,update and delete roles in some
> tables.
> It that scenario possible? If it is how?
> Thanks in advance,
> Tony
> icwgroup