Is there a way to look and see who changed a property of a db. I have a db and when I came in this morning it was set to single user. And both our db maint plan and our scheduled jobs that load up the data warehouse failed because of this. I want to check when and who changed this but I don't see anywhere that I can see this.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
changes to db
- Thread starter swoodring
- Start date
Unfortunately I'm pretty sure you can't. Well I'm not aware of system tables that retain this kind of information. SQL Server internal audit trails hardly exist unless you actually put some in your self.
The only thing which might help is the SQL logs held under the Management tab within Enterprise Manager.
If you have a look in here you and look for something like the following:
Closing file D:\sqldb\TESTSPv1_Data.MDF.
Closing file E:\sqllogs\TESTSPv1_Log.LDF.
Opening file D:\sqldb\TESTSPv1_Data.MDF.
Starting up database 'TESTSPv1'.
Where TESTSPv1 is your database name.
This will give you the time hen your database went into single user mode and which spid was responsible. Unfortunately it won't tell you who was using that connection but at least it's something to start from.
Rick.
The only thing which might help is the SQL logs held under the Management tab within Enterprise Manager.
If you have a look in here you and look for something like the following:
Closing file D:\sqldb\TESTSPv1_Data.MDF.
Closing file E:\sqllogs\TESTSPv1_Log.LDF.
Opening file D:\sqldb\TESTSPv1_Data.MDF.
Starting up database 'TESTSPv1'.
Where TESTSPv1 is your database name.
This will give you the time hen your database went into single user mode and which spid was responsible. Unfortunately it won't tell you who was using that connection but at least it's something to start from.
Rick.
- Thread starter
- #3
The only place I see that is right where its doing the maint plan on the db - database integrity. And it failed on the db in question. Do you know does the db maint plan where it checks data integrity put the db in single user mode? If I look at the text file that the maint plan created when it got to the db in question it had the following:
7] Database PBDSS: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 924: [Microsoft][ODBC SQL Server Driver][SQL Server]Database 'PBDSS' is already open and can only have one user at a time.
[Microsoft][ODBC SQL Server Driver][SQL Server]Only the owner of object 'dtproperties' can run DBCC CHECKTABLE on it.
[Microsoft][ODBC SQL Server Driver][SQL Server]Database 'PBDSS' is already open and can only have one user at a time.
[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC execution completed. If DBCC printed error messages, contact your system administrator.
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]Database 'PBDSS' is already open and can only have one user at a time.
[Microsoft][ODBC SQL Server Driver][SQL Server]Only the owner of object 'dtproperties' can run DBCC CHECKTABLE on it.
[Microsoft][ODBC SQL Server Driver][SQL Server]Database 'PBDSS' is already open and can only have one user at a time.
[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC execution completed. If DBCC printed error messages, contact your system administrator.
** Execution Time: 0 hrs, 8 mins, 57 secs **
It actually repeated this message multiple times its like it kept trying for a bit. So to me it looks like it was in single mode before it got here. But not quite positive.
7] Database PBDSS: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 924: [Microsoft][ODBC SQL Server Driver][SQL Server]Database 'PBDSS' is already open and can only have one user at a time.
[Microsoft][ODBC SQL Server Driver][SQL Server]Only the owner of object 'dtproperties' can run DBCC CHECKTABLE on it.
[Microsoft][ODBC SQL Server Driver][SQL Server]Database 'PBDSS' is already open and can only have one user at a time.
[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC execution completed. If DBCC printed error messages, contact your system administrator.
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]Database 'PBDSS' is already open and can only have one user at a time.
[Microsoft][ODBC SQL Server Driver][SQL Server]Only the owner of object 'dtproperties' can run DBCC CHECKTABLE on it.
[Microsoft][ODBC SQL Server Driver][SQL Server]Database 'PBDSS' is already open and can only have one user at a time.
[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC execution completed. If DBCC printed error messages, contact your system administrator.
** Execution Time: 0 hrs, 8 mins, 57 secs **
It actually repeated this message multiple times its like it kept trying for a bit. So to me it looks like it was in single mode before it got here. But not quite positive.
Yes depending on what is selected within the maintenance plan wizard then it is possible it will put the database into single user mode. Some tasks prefer the DB to be in this mode when running on the database.
I have never used the wizard to set up any tasks myself. I use the required DBCC tasks and others from within my own procedures as this way I have more control.
Rick.
I have never used the wizard to set up any tasks myself. I use the required DBCC tasks and others from within my own procedures as this way I have more control.
Rick.
- Status
Similar threads
- Locked
- Question