Modification Logs

Is there a way to determine when a file was changed/modified? We're on
SQL 2000 and I need to know when a view was modified and by whom.
Thanks!By "file" I assume you mean that the view on the database has been
modified?

If so then there are several things you could do:

1. Get a transaction log examining tool which will let you scan the
transaction logs for the DDL command that modified the view. The "by
whom" depends on how your database security is set up. If, for
example, everyone is accustomed to using the "sa" account then this
won't tell you very much. If you have specific account set up for
each individual user then you'll have all the info you need.

2. If the answer to the above was the former then review your database
access security and ensure that only person-specific user accounts
have the privileges to make modifications.

3. Implement a change process for your SQL code - take a look at
www.dbghost.com for a tool that enables such a process.sql

Model DB is hung on a restore ??

Is there a way to stop a hung restore ?
Here are some error logs:
2003-12-09 12:35:20.94 server Microsoft SQL Server 2000 - 8.00.760 (Intel
X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
2003-12-09 12:35:20.94 server Copyright (C) 1988-2002 Microsoft Corporation.
2003-12-09 12:35:20.94 server All rights reserved.
2003-12-09 12:35:20.94 server Server Process ID is 3984.
2003-12-09 12:35:20.94 server Logging SQL Server messages in file
'e:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG'.
2003-12-09 12:35:20.96 server SQL Server is starting at priority class
'normal'(4 CPUs detected).
2003-12-09 12:35:21.10 server SQL Server configured for thread mode
processing.
2003-12-09 12:35:21.10 server Using dynamic lock allocation. [2500] Lock
Blocks, [5000] Lock Owner Blocks.
2003-12-09 12:35:21.17 server Attempting to initialize Distributed
Transaction Coordinator.
2003-12-09 12:35:23.25 spid3 Starting up database 'master'.
2003-12-09 12:35:23.32 server Using 'SSNETLIB.DLL' version '8.0.760'.
2003-12-09 12:35:23.32 spid5 Starting up database 'model'.
2003-12-09 12:35:23.32 spid3 Server name is 'W2KSVR'.
2003-12-09 12:35:23.32 spid8 Starting up database 'msdb'.
2003-12-09 12:35:23.32 spid9 Starting up database 'pubs'.
2003-12-09 12:35:23.32 spid10 Starting up database 'Northwind'.
2003-12-09 12:35:23.32 spid11 Starting up database 'StarBldr'.
2003-12-09 12:35:23.32 spid12 Starting up database 'SurfControl_WebFilter'.
2003-12-09 12:35:23.32 spid13 Starting up database 'BRCS'.
2003-12-09 12:35:23.32 spid14 Starting up database 'BEDB'.
2003-12-09 12:35:23.33 spid11 Bypassing recovery for database 'StarBldr'
because it is marked IN LOAD.
2003-12-09 12:35:23.33 spid5 Bypassing recovery for database 'model' because
it is marked IN LOAD.
2003-12-09 12:35:23.33 spid5 Database 'model' cannot be opened. It is in the
middle of a restore.
2003-12-09 12:35:23.33 spid13 Bypassing recovery for database 'BRCS' because
it is marked IN LOAD.
HERE IS THE ERRORLOG.1 file:
2003-12-09 12:33:19.02 server Microsoft SQL Server 2000 - 8.00.760 (Intel
X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
2003-12-09 12:33:19.02 server Copyright (C) 1988-2002 Microsoft Corporation.
2003-12-09 12:33:19.02 server All rights reserved.
2003-12-09 12:33:19.02 server Server Process ID is 4364.
2003-12-09 12:33:19.02 server Logging SQL Server messages in file
'e:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG'.
2003-12-09 12:33:19.02 server SQL Server is starting at priority class
'normal'(4 CPUs detected).
2003-12-09 12:33:19.16 server SQL Server configured for thread mode
processing.
2003-12-09 12:33:19.16 server Using dynamic lock allocation. [2500] Lock
Blocks, [5000] Lock Owner Blocks.
2003-12-09 12:33:19.24 server Attempting to initialize Distributed
Transaction Coordinator.
2003-12-09 12:33:21.30 spid3 Starting up database 'master'.
2003-12-09 12:33:21.35 server Using 'SSNETLIB.DLL' version '8.0.760'.
2003-12-09 12:33:21.35 spid5 Starting up database 'model'.
2003-12-09 12:33:21.36 spid3 Server name is 'W2KSVR'.
2003-12-09 12:33:21.36 spid8 Starting up database 'msdb'.
2003-12-09 12:33:21.36 spid9 Starting up database 'pubs'.
2003-12-09 12:33:21.36 spid10 Starting up database 'Northwind'.
2003-12-09 12:33:21.36 spid11 Starting up database 'StarBldr'.
2003-12-09 12:33:21.36 spid13 Starting up database 'BRCS'.
2003-12-09 12:33:21.36 spid12 Starting up database 'SurfControl_WebFilter'.
2003-12-09 12:33:21.36 spid14 Starting up database 'BEDB'.
2003-12-09 12:33:21.36 spid11 Bypassing recovery for database 'StarBldr'
because it is marked IN LOAD.
2003-12-09 12:33:21.36 spid5 Bypassing recovery for database 'model' because
it is marked IN LOAD.
2003-12-09 12:33:21.36 spid13 Bypassing recovery for database 'BRCS' because
it is marked IN LOAD.
2003-12-09 12:33:21.38 spid5 Database 'model' cannot be opened. It is in the
middle of a restore.
Any help would be grateful... Thanks
TIASee my other reply.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"_M_" <here@.gone.com> wrote in message
news:OI34LpovDHA.3116@.tk2msftngp13.phx.gbl...
> Is there a way to stop a hung restore ?
> Here are some error logs:
> 2003-12-09 12:35:20.94 server Microsoft SQL Server 2000 - 8.00.760 (Intel
> X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> 2003-12-09 12:35:20.94 server Copyright (C) 1988-2002 Microsoft
Corporation.
> 2003-12-09 12:35:20.94 server All rights reserved.
> 2003-12-09 12:35:20.94 server Server Process ID is 3984.
> 2003-12-09 12:35:20.94 server Logging SQL Server messages in file
> 'e:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG'.
> 2003-12-09 12:35:20.96 server SQL Server is starting at priority class
> 'normal'(4 CPUs detected).
> 2003-12-09 12:35:21.10 server SQL Server configured for thread mode
> processing.
> 2003-12-09 12:35:21.10 server Using dynamic lock allocation. [2500] Lock
> Blocks, [5000] Lock Owner Blocks.
> 2003-12-09 12:35:21.17 server Attempting to initialize Distributed
> Transaction Coordinator.
> 2003-12-09 12:35:23.25 spid3 Starting up database 'master'.
> 2003-12-09 12:35:23.32 server Using 'SSNETLIB.DLL' version '8.0.760'.
> 2003-12-09 12:35:23.32 spid5 Starting up database 'model'.
> 2003-12-09 12:35:23.32 spid3 Server name is 'W2KSVR'.
> 2003-12-09 12:35:23.32 spid8 Starting up database 'msdb'.
> 2003-12-09 12:35:23.32 spid9 Starting up database 'pubs'.
> 2003-12-09 12:35:23.32 spid10 Starting up database 'Northwind'.
> 2003-12-09 12:35:23.32 spid11 Starting up database 'StarBldr'.
> 2003-12-09 12:35:23.32 spid12 Starting up database
'SurfControl_WebFilter'.
> 2003-12-09 12:35:23.32 spid13 Starting up database 'BRCS'.
> 2003-12-09 12:35:23.32 spid14 Starting up database 'BEDB'.
> 2003-12-09 12:35:23.33 spid11 Bypassing recovery for database 'StarBldr'
> because it is marked IN LOAD.
> 2003-12-09 12:35:23.33 spid5 Bypassing recovery for database 'model'
because
> it is marked IN LOAD.
> 2003-12-09 12:35:23.33 spid5 Database 'model' cannot be opened. It is in
the
> middle of a restore.
> 2003-12-09 12:35:23.33 spid13 Bypassing recovery for database 'BRCS'
because
> it is marked IN LOAD.
> HERE IS THE ERRORLOG.1 file:
> 2003-12-09 12:33:19.02 server Microsoft SQL Server 2000 - 8.00.760 (Intel
> X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> 2003-12-09 12:33:19.02 server Copyright (C) 1988-2002 Microsoft
Corporation.
> 2003-12-09 12:33:19.02 server All rights reserved.
> 2003-12-09 12:33:19.02 server Server Process ID is 4364.
> 2003-12-09 12:33:19.02 server Logging SQL Server messages in file
> 'e:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG'.
> 2003-12-09 12:33:19.02 server SQL Server is starting at priority class
> 'normal'(4 CPUs detected).
> 2003-12-09 12:33:19.16 server SQL Server configured for thread mode
> processing.
> 2003-12-09 12:33:19.16 server Using dynamic lock allocation. [2500] Lock
> Blocks, [5000] Lock Owner Blocks.
> 2003-12-09 12:33:19.24 server Attempting to initialize Distributed
> Transaction Coordinator.
> 2003-12-09 12:33:21.30 spid3 Starting up database 'master'.
> 2003-12-09 12:33:21.35 server Using 'SSNETLIB.DLL' version '8.0.760'.
> 2003-12-09 12:33:21.35 spid5 Starting up database 'model'.
> 2003-12-09 12:33:21.36 spid3 Server name is 'W2KSVR'.
> 2003-12-09 12:33:21.36 spid8 Starting up database 'msdb'.
> 2003-12-09 12:33:21.36 spid9 Starting up database 'pubs'.
> 2003-12-09 12:33:21.36 spid10 Starting up database 'Northwind'.
> 2003-12-09 12:33:21.36 spid11 Starting up database 'StarBldr'.
> 2003-12-09 12:33:21.36 spid13 Starting up database 'BRCS'.
> 2003-12-09 12:33:21.36 spid12 Starting up database
'SurfControl_WebFilter'.
> 2003-12-09 12:33:21.36 spid14 Starting up database 'BEDB'.
> 2003-12-09 12:33:21.36 spid11 Bypassing recovery for database 'StarBldr'
> because it is marked IN LOAD.
> 2003-12-09 12:33:21.36 spid5 Bypassing recovery for database 'model'
because
> it is marked IN LOAD.
> 2003-12-09 12:33:21.36 spid13 Bypassing recovery for database 'BRCS'
because
> it is marked IN LOAD.
> 2003-12-09 12:33:21.38 spid5 Database 'model' cannot be opened. It is in
the
> middle of a restore.
> Any help would be grateful... Thanks
> TIA
>

Missing transaction logs

can anyone please point me in the right direction as to how to go about rebuilding the transaction log files on a database?

here is the scenario:

1 - transaction log drive failed and transaction log file is basically gone.

2 - database file is fine.

3 - backup is too old or for all intents and purposes non-existent.

the server initially showed the database as suspect. the database was detached and an attempt was made to attach it with a recovered copy of the transaction log file but apparently it was too corrupted and the server didn't like it.

any suggestions would be greatly appreciated.

by the way, after looking at some of the posts here, i tried ApexSQL Log and Red-Gate Rescue bundle but these tools seem to require a database to at least show up on the database list, even if it is suspect.

the database doesn't even show up on the list since it was detached.

thanks in advance.

For SQL 2005, you could try attaching using CREATE DATABASE with the ATTACH_REBUILD_LOG option. This will only work if the data file was shut down cleanly, though.

If this does not work, you might try creating a new database, setting it OFFLINE with ALTER DATABASE, deleting the data and log file, copy the data file from your old database to be the one from the new database, set the database ONLINE with ALTER DATABASE (which will likely fail), and then set the database to EMERGENCY with ALTER DATABASE. Then you can look at the "DBCC Operation in Emergency Mode" section in the topic "DBCC (Transact-SQL)" to try to salvage the database.

|||

have u tried sp_attach_single_file_db

Madhu

|||

this is a SQL 2000 box.

would this still work?

|||

I hadn't tried what you suggest.

i looked it up in the help and it seems pretty clear that the database has to be detached in just the right way and at least one transaction log file needs to be available.

but, following your suggestion i tried it anyway and the response was basically the same as trying to mount the database in the usual way. The response to the attempt was: Could not open new database 'Lab_Config'. CREATE DATABASE is aborted. Device activation error. The physical file name 'L:\SqlLogs\Lab_Config_log.LDF' may be incorrect.

Drive L: is the destroyed drive.

|||

here is a rundown of what i did. maybe it is not correct or you have a suggestion on how this procedure can better be used to resolve this issue.

the statement used was taken from the help section for this stored procedure and it is as follows:

EXEC sp_attach_single_file_db @.dbname = 'LabConfig',
@.physname = 'E:\DataBases\Data\Lab_Config.mdf'

the response was:

Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'LabConfig'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'L:\SqlLogs\\Lab_Config_log.LDF' may be incorrect.

Please note the double reverse slash in the path in the response! does that look right?

i have several databases on this server that are without transaction logs. i tried the above stored procedure on all of them and the double reverse slash was present in all the responses.

by the way, drive L: has been replaced and it is now functional. the directory in SqlLogs exists and is configured with Full Access for Everyone.

|||

there should not be any log in the L drive while u run this command. make sure that 'L:\SqlLogs\\Lab_Config_log.LDF' if exists in L drive cut & paste to some other location and try the same. And also it reminds me that, this command only works with singel log and datafile database.

Madhu

|||

the L:\sqllogs directory is empty.

and all the databases in this condition are single log and datafile databases.

|||

You're going to have to use undocumented commands to get around this - please contact Product Support who will be able to help you use them correctly.

Thanks