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 Restore Problem

I've created a Restore Job with owner sa, run as dbo, to refresh a Test database from Production.

After ONE successful refresh from the Production database, this
job fails with:
Msg 3101, Sev 16: Exclusive access could not be obtained because the
database is in use. [SQLSTATE 42000]
Msg 3013, Sev 16: RESTORE DATABASE is terminating abnormally. [SQLSTATE
42000]

There are no users connected to the database or processes running against it.

The syntax is as follows:
ALTER DATABASE DBA_pfaids_test SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE DBA_pfaids_test
FROM DISK = 'd:\PFAIDS\restore\PRODbackup.bak'
WITH REPLACE,
MOVE 'SS2K_prod_empty_Data' TO 'D:\MSSQL\DATA\DBA_pfaids_test_Data.MDF',
MOVE 'SS2K_prod_empty_Log' TO 'D:\MSSQL\DATA\DBA_pfaids_test_Log.LDF'

ALTER DATABASE DBA_pfaids_test SET MULTI_USER

Does this have something to do with the initial refresh from Production?
How can I get it to work?

Thanks.
MaryYou probably have either a (some) thoughtless user(s) who leave their connections open at the end of the day, or scheduled jobs that access the database.

You cannot put a database in single user mode while others are attached. You could preface your script with a call to sp_who2 and capture the returned data in a table. Then use a cursor to inspect the table, and when you find a spid attached to the database you want to restore, kill the spid.

Once you have finished with the cursor, close and deallocate. repeat until no losers (oops ... 'xcuse me ... users) are left in the database. When that occurs, execute the alter to put it into single_user, and proceed with the restore.

Things would run so much better if we just didn't have to let users into our databases ;)|||Thanks for you reply.

Actually, users do not access this database. I created it for testing purposes only, and they don't even know it exists. The restore works fine in Enterprise Manager, but fails when doing it with T-SQL. (I am trying to automate the restore).|||Things would run so much better if we just didn't have to let users into our databases ;)

~"This job would be great if it were'nt for the customers"
-Clerks|||The microsoft tools are getting a bit annoying with how "helpful" they are trying to be. If you have not already, shut down enterprise manager, and close the object browser in Query Analyzer. These two things are the top cause of this error. If that does not help, check to see if you have any extra windows open in Query Analyzer.|||Also check if the QA window you're trying to do the restore in isn't connected to the same database (switch to master to be sure).

Lex

Database Restore failed, now inaccessible and can not restore.

I have seen this before. A 2000 restore fails, leaving the database thinking it is being restored but the restore job failed and errors when it is restarted. EM is clueless. I believe there is a proc to reset some flag. Can you share it with me?

Thanks!

sp_resetstatus|||I don't know if being marked "suspect" is the same as being marked as being restored. Is it?|||

if you have backup files, then try to restore it from QA. the problem may be when you restore the data file and log file ; the datafile step may be success and the log file may not. untill unless it find WITH RECOVERy option the database status will be "restoring" . So what you do is , take the backup set and restore it with someother name using WITH Move option

Madhu

|||no. 'suspect' is not the same as 'restoring'. sp_resetstatus only handles 'suspect' status.

To get out of restoring, you will need to recover the database. Try:

restore database <db> with recovery

Database reindex

I had job set up do to database reindex using database maintenance
plans...since loading SP4 for sql 2000 these jobs are not running ...i can
do start job and that fine but i need them to run early morning of hours
Any ideasHi Tracey
Have you checked that the jobs and the job schedule are both enabled? What
does the job history say? Have you got any information in the SQL Server log
or the Windows Event Log at the times these jobs are supposed to run? Have
you tried re-creating the maintenance plan or jobs?
John
"TRACEY" wrote:

> I had job set up do to database reindex using database maintenance
> plans...since loading SP4 for sql 2000 these jobs are not running ...i ca
n
> do start job and that fine but i need them to run early morning of hours
> Any ideas|||TRACEY
If I undestsood you , under Optimization Tab choose schedule to create a
job
"TRACEY" <TRACEY@.discussions.microsoft.com> wrote in message
news:3F05031E-F96D-484D-83CF-A4F87A9DD259@.microsoft.com...
>I had job set up do to database reindex using database maintenance
> plans...since loading SP4 for sql 2000 these jobs are not running ...i
> can
> do start job and that fine but i need them to run early morning of hours
> Any ideas|||That how i set up the job..
under Optimization Tab choose schedule to create a
job
Its enabled...and there nothing in the job history nothing in the sql
logs...nothing in events...i just got it to fire off at night and then in
morning i see nothing ...no history
I can just right click it and boom it starts the index which i really don't
want to do during activity..
This only happened since SP4 ........
Im almost going to write a script to do the dbreindex ...but i like to
figure out why..
The only thing we did do was for some of the tables we put these in separate
file groups (the vendor erp creates temp files for each person called
X_.owner) so we put these in file groups so i can see when they get large
...and when i can then run a utility to clear them out...but the data
remains where it is and the log files.|||Hi Tracey
If there is nothing in the job history is sounds like the schedule is wrong.
If you schedule it for a different time rather than manually run it, can you
see the job starting?
John
"TRACEY" wrote:

> That how i set up the job..
> under Optimization Tab choose schedule to create a
> job
> Its enabled...and there nothing in the job history nothing in the sql
> logs...nothing in events...i just got it to fire off at night and then in
> morning i see nothing ...no history
> I can just right click it and boom it starts the index which i really don'
t
> want to do during activity..
> This only happened since SP4 ........
> Im almost going to write a script to do the dbreindex ...but i like to
> figure out why..
> The only thing we did do was for some of the tables we put these in separa
te
> file groups (the vendor erp creates temp files for each person called
> X_.owner) so we put these in file groups so i can see when they get large
> ...and when i can then run a utility to clear them out...but the data
> remains where it is and the log files.
>
>|||Sunday it ran this was the normal day i had it set all the other days monday
thursday failed....odd but at least it ran last night.
I checked the logs
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
'SQL1'
Starting maintenance plan 'Reindex Databases Costpoint' on 10/29/2006
2:00:03 AM
[1] Database DeltekTC: Index Rebuild (leaving 100%% free space)...
I have it set to Database maintenance plan optimizations set to regorganize
data and index pages
reorganize pages with the orginal amount of free space ....thats why i get
100%
That means ever time record insert its going to split ha.....
how to change it to 90
Do i select the change free space per page percentage to 10%
will that be DBCC REINDEX 'DB','90'...?
And do you do master, model,
Well im glad it ran last night...
Thanks|||Hi Tracey
In general you would want to pass 0 to the maintainance plan to re-organise
the indexes using their original value. In any given database you may want t
o
have some indexes with fill factors or 0 or 100 (if the data is static!) and
others with less, therefore resetting the index fill factor across the board
is not necessarily a good thing. If you want to change the fill factor for a
give index use
DBCC DBREINDEX ( 'database.owner.table_name' , index_name, fillfactor )
Analyse the information from DBCC SHOWCONTIG or
sys.dm_db_index_physical_stats instead (SQL 2005) for information on what
indexes are fragmented.
John
"TRACEY" wrote:

> Sunday it ran this was the normal day i had it set all the other days mond
ay
> thursday failed....odd but at least it ran last night.
> I checked the logs
> Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
> 'SQL1'
> Starting maintenance plan 'Reindex Databases Costpoint' on 10/29/2006
> 2:00:03 AM
> [1] Database DeltekTC: Index Rebuild (leaving 100%% free space)...
> I have it set to Database maintenance plan optimizations set to regorganiz
e
> data and index pages
> reorganize pages with the orginal amount of free space ....thats why i ge
t
> 100%
> That means ever time record insert its going to split ha.....
> how to change it to 90
> Do i select the change free space per page percentage to 10%
> will that be DBCC REINDEX 'DB','90'...?
> And do you do master, model,
> Well im glad it ran last night...
> Thanks|||Took me ages to find the thread.
How do you bookmark it so i can go to it.
I have it set to Database maintenance plan optimizations set to regorganize
data and index pages
reorganize pages with the orginal amount of free space
Any ideas why its reporting 100%
That means ever time record insert its going to split
Why is it not taking the default fill factor in the tables.
Thanks|||Hi Tracey
That will depend on what newsreader client you are using. You can set
Outlook Express to only display message threads that you have participated
in, or you can search the technet discussion groups using (say) your email
address. Here is a link for the thread http://tinyurl.com/y3ntk4
The SQL Maint utility uses a value of 100 for the RebldIdx to specify that
you use the original values as this works on free space and not the
fillfactor. DBCC DBREINDEX uses 0 to maintain the current fill factors. You
can reindex specific indexes using this command to change the fill factor an
d
then use a maintenance plan or your own job to keep it (and all other
indexes) at their own level. Another method to restore the fillfactors would
be to drop and re-create the indexes if you already have scripts that for
their original definions. This would be the case if you use a source code
control system such as Visual Source Safe for you database code.
Having a fill factor of 100% is not necessarily a bad thing, if you have
table that contains very static data you may want to use 100% fill factor,
similarly if the table is being treated as an ISAM structure then a high fil
l
factor may be appropriate if you rarely update existing rows.
John
"TRACEY" wrote:

> Took me ages to find the thread.
> How do you bookmark it so i can go to it.
> I have it set to Database maintenance plan optimizations set to regorganiz
e
> data and index pages
> reorganize pages with the orginal amount of free space
> Any ideas why its reporting 100%
> That means ever time record insert its going to split
> Why is it not taking the default fill factor in the tables.
> Thanks

Database reindex

I had job set up do to database reindex using database maintenance
plans...since loading SP4 for sql 2000 these jobs are not running ...i can
do start job and that fine but i need them to run early morning of hours
Any ideasHi Tracey
Have you checked that the jobs and the job schedule are both enabled? What
does the job history say? Have you got any information in the SQL Server log
or the Windows Event Log at the times these jobs are supposed to run? Have
you tried re-creating the maintenance plan or jobs?
John
"TRACEY" wrote:
> I had job set up do to database reindex using database maintenance
> plans...since loading SP4 for sql 2000 these jobs are not running ...i can
> do start job and that fine but i need them to run early morning of hours
> Any ideas|||TRACEY
If I undestsood you , under Optimization Tab choose schedule to create a
job
"TRACEY" <TRACEY@.discussions.microsoft.com> wrote in message
news:3F05031E-F96D-484D-83CF-A4F87A9DD259@.microsoft.com...
>I had job set up do to database reindex using database maintenance
> plans...since loading SP4 for sql 2000 these jobs are not running ...i
> can
> do start job and that fine but i need them to run early morning of hours
> Any ideas|||That how i set up the job..
under Optimization Tab choose schedule to create a
job
Its enabled...and there nothing in the job history nothing in the sql
logs...nothing in events...i just got it to fire off at night and then in
morning i see nothing ...no history
I can just right click it and boom it starts the index which i really don't
want to do during activity..
This only happened since SP4 ........
Im almost going to write a script to do the dbreindex ...but i like to
figure out why..
The only thing we did do was for some of the tables we put these in separate
file groups (the vendor erp creates temp files for each person called
X_.owner) so we put these in file groups so i can see when they get large
...and when i can then run a utility to clear them out...but the data
remains where it is and the log files.|||Hi Tracey
If there is nothing in the job history is sounds like the schedule is wrong.
If you schedule it for a different time rather than manually run it, can you
see the job starting?
John
"TRACEY" wrote:
> That how i set up the job..
> under Optimization Tab choose schedule to create a
> job
> Its enabled...and there nothing in the job history nothing in the sql
> logs...nothing in events...i just got it to fire off at night and then in
> morning i see nothing ...no history
> I can just right click it and boom it starts the index which i really don't
> want to do during activity..
> This only happened since SP4 ........
> Im almost going to write a script to do the dbreindex ...but i like to
> figure out why..
> The only thing we did do was for some of the tables we put these in separate
> file groups (the vendor erp creates temp files for each person called
> X_.owner) so we put these in file groups so i can see when they get large
> ...and when i can then run a utility to clear them out...but the data
> remains where it is and the log files.
>
>|||Sunday it ran this was the normal day i had it set all the other days monday
thursday failed....odd but at least it ran last night.
I checked the logs
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
'SQL1'
Starting maintenance plan 'Reindex Databases Costpoint' on 10/29/2006
2:00:03 AM
[1] Database DeltekTC: Index Rebuild (leaving 100%% free space)...
I have it set to Database maintenance plan optimizations set to regorganize
data and index pages
reorganize pages with the orginal amount of free space ....thats why i get
100%
That means ever time record insert its going to split ha.....
how to change it to 90
Do i select the change free space per page percentage to 10%
will that be DBCC REINDEX 'DB','90'...?
And do you do master, model,
Well im glad it ran last night...
Thanks|||Hi Tracey
In general you would want to pass 0 to the maintainance plan to re-organise
the indexes using their original value. In any given database you may want to
have some indexes with fill factors or 0 or 100 (if the data is static!) and
others with less, therefore resetting the index fill factor across the board
is not necessarily a good thing. If you want to change the fill factor for a
give index use
DBCC DBREINDEX ( 'database.owner.table_name' , index_name, fillfactor )
Analyse the information from DBCC SHOWCONTIG or
sys.dm_db_index_physical_stats instead (SQL 2005) for information on what
indexes are fragmented.
John
"TRACEY" wrote:
> Sunday it ran this was the normal day i had it set all the other days monday
> thursday failed....odd but at least it ran last night.
> I checked the logs
> Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
> 'SQL1'
> Starting maintenance plan 'Reindex Databases Costpoint' on 10/29/2006
> 2:00:03 AM
> [1] Database DeltekTC: Index Rebuild (leaving 100%% free space)...
> I have it set to Database maintenance plan optimizations set to regorganize
> data and index pages
> reorganize pages with the orginal amount of free space ....thats why i get
> 100%
> That means ever time record insert its going to split ha.....
> how to change it to 90
> Do i select the change free space per page percentage to 10%
> will that be DBCC REINDEX 'DB','90'...?
> And do you do master, model,
> Well im glad it ran last night...
> Thanks|||Took me ages to find the thread.
How do you bookmark it so i can go to it.
I have it set to Database maintenance plan optimizations set to regorganize
data and index pages
reorganize pages with the orginal amount of free space
Any ideas why its reporting 100%
That means ever time record insert its going to split
Why is it not taking the default fill factor in the tables.
Thanks|||Hi Tracey
That will depend on what newsreader client you are using. You can set
Outlook Express to only display message threads that you have participated
in, or you can search the technet discussion groups using (say) your email
address. Here is a link for the thread http://tinyurl.com/y3ntk4
The SQL Maint utility uses a value of 100 for the RebldIdx to specify that
you use the original values as this works on free space and not the
fillfactor. DBCC DBREINDEX uses 0 to maintain the current fill factors. You
can reindex specific indexes using this command to change the fill factor and
then use a maintenance plan or your own job to keep it (and all other
indexes) at their own level. Another method to restore the fillfactors would
be to drop and re-create the indexes if you already have scripts that for
their original definions. This would be the case if you use a source code
control system such as Visual Source Safe for you database code.
Having a fill factor of 100% is not necessarily a bad thing, if you have
table that contains very static data you may want to use 100% fill factor,
similarly if the table is being treated as an ISAM structure then a high fill
factor may be appropriate if you rarely update existing rows.
John
"TRACEY" wrote:
> Took me ages to find the thread.
> How do you bookmark it so i can go to it.
> I have it set to Database maintenance plan optimizations set to regorganize
> data and index pages
> reorganize pages with the orginal amount of free space
> Any ideas why its reporting 100%
> That means ever time record insert its going to split
> Why is it not taking the default fill factor in the tables.
> Thanks

Database refresh

Hi,
I want to refresh one of the database from production server to QA server
every first day of the month. How can I do it through scheduling a job? Any
one has scripts handy?
Also, will it transfer all the logins when I refresh the database. If not,
how can I achieve this task too?
Thank you guys
David
You asked two questions
1)
Perform BACKUP /RESTORE DATABASE somethng like that
BACKUP DATABASE myname TO DISK =
N'\\Remote(QA)\backup\myname .BAK'
Create a job which executes this command , just make sure that you drop the
old verison of your database on QA server\
2)
Search on internet for "sp_help_revlogin" stored procedure provided by MS to
move logins
"David" <David@.discussions.microsoft.com> wrote in message
news:CA6FF754-AA50-46D3-9BD6-0FF8594AD2AA@.microsoft.com...
> Hi,
> I want to refresh one of the database from production server to QA server
> every first day of the month. How can I do it through scheduling a job?
> Any
> one has scripts handy?
> Also, will it transfer all the logins when I refresh the database. If not,
> how can I achieve this task too?
> Thank you guys

Database refresh

Hi,
I want to refresh one of the database from production server to QA server
every first day of the month. How can I do it through scheduling a job? Any
one has scripts handy?
Also, will it transfer all the logins when I refresh the database. If not,
how can I achieve this task too?
Thank you guysDavid
You asked two questions
1)
Perform BACKUP /RESTORE DATABASE somethng like that
BACKUP DATABASE myname TO DISK =
N'\\Remote(QA)\backup\myname .BAK'
Create a job which executes this command , just make sure that you drop the
old verison of your database on QA server\
2)
Search on internet for "sp_help_revlogin" stored procedure provided by MS to
move logins
"David" <David@.discussions.microsoft.com> wrote in message
news:CA6FF754-AA50-46D3-9BD6-0FF8594AD2AA@.microsoft.com...
> Hi,
> I want to refresh one of the database from production server to QA server
> every first day of the month. How can I do it through scheduling a job?
> Any
> one has scripts handy?
> Also, will it transfer all the logins when I refresh the database. If not,
> how can I achieve this task too?
> Thank you guys

Database refresh

Hi,
I want to refresh one of the database from production server to QA server
every first day of the month. How can I do it through scheduling a job? Any
one has scripts handy?
Also, will it transfer all the logins when I refresh the database. If not,
how can I achieve this task too?
Thank you guysDavid
You asked two questions
1)
Perform BACKUP /RESTORE DATABASE somethng like that
BACKUP DATABASE myname TO DISK = N'\\Remote(QA)\backup\myname .BAK'
Create a job which executes this command , just make sure that you drop the
old verison of your database on QA server\
2)
Search on internet for "sp_help_revlogin" stored procedure provided by MS to
move logins
"David" <David@.discussions.microsoft.com> wrote in message
news:CA6FF754-AA50-46D3-9BD6-0FF8594AD2AA@.microsoft.com...
> Hi,
> I want to refresh one of the database from production server to QA server
> every first day of the month. How can I do it through scheduling a job?
> Any
> one has scripts handy?
> Also, will it transfer all the logins when I refresh the database. If not,
> how can I achieve this task too?
> Thank you guys