Log of database changes

Hi All
I would like to know if it is possible to figure out what user change the field names of one of my table
on my SQL Server
It seems that somebody change and after all nobody it guilty
Do you know
Thanks in advanc
RobertoYou should be able to find the log entry for ALTER TABLE or sp_rename if it
was recent enough.
http://www.lumigent.com/products/le_sql/le_sql.htm
http://www.lockwoodtech.com/index_lognavigator.htm
http://www.logpi.com/
http://www.apexsql.com/
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
(Reverse e-mail to reply.)
"Roberto C3" <anonymous@.discussions.microsoft.com> wrote in message
news:A4CEF9C9-5F8E-464C-BA3C-29DD271D1386@.microsoft.com...
> Hi All,
> I would like to know if it is possible to figure out what user change the
> field names of one of my tables
> on my SQL Server.
> It seems that somebody change and after all nobody it guilty.
> Do you know?
> Thanks in advance
> Roberto

Log issues

Hi All,
I have 2 issues with my log files.
1) I have a database where I combine 20 tables from 20
DBs into one very large table. Each table in the 20 DBs
are the same. My procedure uses 20 insert into
statements. For some reason my log file fills and the job
fails. Does anyone know how I can stop the file from
growing like this?
2) I created my Dbs with 400MB trans file. I only use
about 30MB. Is there a way to drop the size down to 50MB
and let it grow from there?
TIA
Joe
1) you can either change the recovery model to simple(if you do not wish to
log the process), however if you are deleting a large number of records in
one transaction(in which case, you will still exceed your configured trans
log size of 400Mb) then you will have to batch these deletes and do them in
smaller/manageable batches.
2) dbcc shrinkfile(<logicalname logfile>,50,turncateonly)
check out BOL for more info.
"JOE" wrote:

> Hi All,
> I have 2 issues with my log files.
> 1) I have a database where I combine 20 tables from 20
> DBs into one very large table. Each table in the 20 DBs
> are the same. My procedure uses 20 insert into
> statements. For some reason my log file fills and the job
> fails. Does anyone know how I can stop the file from
> growing like this?
> 2) I created my Dbs with 400MB trans file. I only use
> about 30MB. Is there a way to drop the size down to 50MB
> and let it grow from there?
> TIA
> Joe
>
|||Thanks for the info.
I will try the simple.
I do not delete but I do Truncate my table before I stert
the inserts. I Truncate because I know it does not hit
the Trans Log.
Joe

Log issues

Hi All,
I have 2 issues with my log files.
1) I have a database where I combine 20 tables from 20
DBs into one very large table. Each table in the 20 DBs
are the same. My procedure uses 20 insert into
statements. For some reason my log file fills and the job
fails. Does anyone know how I can stop the file from
growing like this?
2) I created my Dbs with 400MB trans file. I only use
about 30MB. Is there a way to drop the size down to 50MB
and let it grow from there?
TIA
JoeThanks for the info.
I will try the simple.
I do not delete but I do Truncate my table before I stert
the inserts. I Truncate because I know it does not hit
the Trans Log.
Joe

Log Ins

Hi all I was wondering how would I keep tabs on when users enter data in a database? Can I create a field or program a table to keep track of the users that enter in data so theres no mix up, to automatically keep track of users entering in dataHere is a sample script:

CREATE TABLE [Sample]
([SampleID] [uniqueidentifier] NOT NULL,
[Sample] [varchar] (50) NOT NULL,
[Modified] [datetime] NULL,
[Modifier] [varchar] (100) NULL)
ALTER TABLE [dbo].[Sample] WITH NOCHECK ADD
CONSTRAINT [PK_Sample] PRIMARY KEY CLUSTERED ([SampleID])

ALTER TABLE [dbo].[Sample] ADD CONSTRAINT [DF_Sample_SampleID] DEFAULT (newid()) FOR [SampleID]
ALTER TABLE [dbo].[Sample] ADD CONSTRAINT IX_Sample UNIQUE NONCLUSTERED ([Sample]) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ALTER TABLE [dbo].[Sample] ADD CONSTRAINT [DF_Sample_Modified] DEFAULT (getdate()) FOR [Modified]
ALTER TABLE [dbo].[Sample] ADD CONSTRAINT [DF_Sample_Modifier] DEFAULT (convert(varchar(100), host_name() + ':' + suser_sname() + '(' + current_user + ') - ' + app_name())) FOR [Modifier]
GO

CREATE TRIGGER TR_Sample_U ON [dbo].[Sample]
FOR UPDATE
AS
set nocount on
update [Sample]
set Modified = getdate(),
Modifier = convert(varchar(100), host_name() + ':' + Original_Login() + '(' + current_user + ') - ' + app_name())
from [Sample]
inner join inserted on [Sample].[SampleID] = inserted.[SampleID]
GO|||How are you these days? Thank you for your reply, I give it a shot|||I'm doing fine thank you.

Try this script. Just enter your table name in the appropriate variable and indicated whether you want to use GUIDs or Identities for a surrogate key, and execute the script in text mode. It will write a script that you can copy and paste to create your new table.

set nocount on
declare @.TableName varchar(50)
declare @.UseGUIDs varchar(4)
set @.TableName = 'SampleRecords'
set @.UseGUIDs = 'Y' --'Y' for GUIDs, 'N' for Identity

declare @.EntityName varchar(50)
declare @.IDName varchar(50)
declare @.Version char(4)

set @.Version = substring(@.@.Version, 22, 4)

if right(@.TableName, 3) = 'ies' set @.EntityName = left(@.TableName, len(@.TableName)-3) + 'y'
else if right(@.TableName, 4) = 'sses' set @.EntityName = left(@.TableName, len(@.TableName)-2)
else if right(@.TableName, 6) = 'status' set @.EntityName = @.TableName
else if right(@.TableName, 8) = 'statuses' set @.EntityName = left(@.TableName, len(@.TableName)-2)
else if right(@.TableName, 1) = 's' set @.EntityName = left(@.TableName, len(@.TableName)-1)
else set @.EntityName = @.TableName

if right(@.TableName, 3) = 'ies' set @.IDName = left(@.TableName, len(@.TableName)-3) + 'yID'
else if right(@.TableName, 4) = 'sses' set @.IDName = left(@.TableName, len(@.TableName)-2) + 'ID'
else if right(@.TableName, 6) = 'status' set @.IDName = @.TableName + 'ID'
else if right(@.TableName, 8) = 'statuses' set @.IDName = left(@.TableName, len(@.TableName)-2) + 'ID'
else if right(@.TableName, 1) = 's' set @.IDName = left(@.TableName, len(@.TableName)-1) + 'ID'
else set @.IDName = @.TableName + 'ID'

declare @.SQLString varchar(8000)
set @.SQLString =
'Use ' + case when db_name() in ('master', 'msdb', 'model') then '[XXX]' else db_name() end + '
' + replace('GXO' ,'X', '') + '

CREATE TABLE [' + @.TableName + ']
([' + @.IDName + '] ' + Case when @.UseGUIDs = 'Y' then '[uniqueidentifier] NOT NULL' else '[bigint] IDENTITY(1,1) NOT NULL' end + ',
[' + @.EntityName + '] [varchar] (50) NOT NULL,
[Modified] [datetime] NULL,
[Modifier] [varchar] (100) NULL)
ALTER TABLE [dbo].[' + @.TableName + '] WITH NOCHECK ADD
CONSTRAINT [PK_' + @.TableName + '] PRIMARY KEY CLUSTERED ([' + @.IDName + '])

' + Case when @.UseGUIDs = 'Y' then 'ALTER TABLE [dbo].[' + @.TableName + '] ADD CONSTRAINT [DF_' + @.TableName + '_' + @.IDName + '] DEFAULT (newid()) FOR [' + @.IDName + ']' else '' end + '
ALTER TABLE [dbo].[' + @.TableName + '] ADD CONSTRAINT IX_' + @.TableName + ' UNIQUE NONCLUSTERED ([' + @.EntityName + '])
ALTER TABLE [dbo].[' + @.TableName + '] ADD CONSTRAINT [DF_' + @.TableName + '_Modified] DEFAULT (getdate()) FOR [Modified]
ALTER TABLE [dbo].[' + @.TableName + '] ADD CONSTRAINT [DF_' + @.TableName + '_Modifier] DEFAULT (convert(varchar(100), host_name() + '':'' + suser_sname() + ''('' + current_user + '') - '' + app_name())) FOR [Modifier]
' + replace('GXO' ,'X', '') + '

CREATE TRIGGER TR_' + @.TableName + '_U ON [dbo].[' + @.TableName + ']
FOR UPDATE
AS
set nocount on
update [' + @.TableName + ']
set Modified = getdate(),
' + case when @.Version >= '2005'
then ' Modifier = convert(varchar(100), host_name() + '':'' + Original_Login() + ''('' + current_user + '') - '' + app_name())'
else ' Modifier = convert(varchar(100), host_name() + '':'' + suser_sname() + ''('' + current_user + '') - '' + app_name())'
end + '
from [' + @.TableName + ']
inner join inserted on [' + @.TableName + '].[' + @.IDName + '] = inserted.[' + @.IDName + ']
' + replace('GXO' ,'X', '') + ''

select @.SQLString

See how lazy I am?|||Hi all I was wondering how would I keep tabs on when users enter data in a database? Can I create a field or program a table to keep track of the users that enter in data so theres no mix up, to automatically keep track of users entering in data

Do you have sproc access only?|||I'm doing fine thank you.

Try this script. Just enter your table name in the appropriate variable and indicated whether you want to use GUIDs or Identities for a surrogate key, and execute the script in text mode. It will write a script that you can copy and paste to create your new table.

set nocount on
declare @.TableName varchar(50)
declare @.UseGUIDs varchar(4)
set @.TableName = 'SampleRecords'
set @.UseGUIDs = 'Y' --'Y' for GUIDs, 'N' for Identity

declare @.EntityName varchar(50)
declare @.IDName varchar(50)
declare @.Version char(4)

set @.Version = substring(@.@.Version, 22, 4)

if right(@.TableName, 3) = 'ies' set @.EntityName = left(@.TableName, len(@.TableName)-3) + 'y'
else if right(@.TableName, 4) = 'sses' set @.EntityName = left(@.TableName, len(@.TableName)-2)
else if right(@.TableName, 6) = 'status' set @.EntityName = @.TableName
else if right(@.TableName, 8) = 'statuses' set @.EntityName = left(@.TableName, len(@.TableName)-2)
else if right(@.TableName, 1) = 's' set @.EntityName = left(@.TableName, len(@.TableName)-1)
else set @.EntityName = @.TableName

if right(@.TableName, 3) = 'ies' set @.IDName = left(@.TableName, len(@.TableName)-3) + 'yID'
else if right(@.TableName, 4) = 'sses' set @.IDName = left(@.TableName, len(@.TableName)-2) + 'ID'
else if right(@.TableName, 6) = 'status' set @.IDName = @.TableName + 'ID'
else if right(@.TableName, 8) = 'statuses' set @.IDName = left(@.TableName, len(@.TableName)-2) + 'ID'
else if right(@.TableName, 1) = 's' set @.IDName = left(@.TableName, len(@.TableName)-1) + 'ID'
else set @.IDName = @.TableName + 'ID'

declare @.SQLString varchar(8000)
set @.SQLString =
'Use ' + case when db_name() in ('master', 'msdb', 'model') then '[XXX]' else db_name() end + '
' + replace('GXO' ,'X', '') + '

CREATE TABLE [' + @.TableName + ']
([' + @.IDName + '] ' + Case when @.UseGUIDs = 'Y' then '[uniqueidentifier] NOT NULL' else '[bigint] IDENTITY(1,1) NOT NULL' end + ',
[' + @.EntityName + '] [varchar] (50) NOT NULL,
[Modified] [datetime] NULL,
[Modifier] [varchar] (100) NULL)
ALTER TABLE [dbo].[' + @.TableName + '] WITH NOCHECK ADD
CONSTRAINT [PK_' + @.TableName + '] PRIMARY KEY CLUSTERED ([' + @.IDName + '])

' + Case when @.UseGUIDs = 'Y' then 'ALTER TABLE [dbo].[' + @.TableName + '] ADD CONSTRAINT [DF_' + @.TableName + '_' + @.IDName + '] DEFAULT (newid()) FOR [' + @.IDName + ']' else '' end + '
ALTER TABLE [dbo].[' + @.TableName + '] ADD CONSTRAINT IX_' + @.TableName + ' UNIQUE NONCLUSTERED ([' + @.EntityName + '])
ALTER TABLE [dbo].[' + @.TableName + '] ADD CONSTRAINT [DF_' + @.TableName + '_Modified] DEFAULT (getdate()) FOR [Modified]
ALTER TABLE [dbo].[' + @.TableName + '] ADD CONSTRAINT [DF_' + @.TableName + '_Modifier] DEFAULT (convert(varchar(100), host_name() + '':'' + suser_sname() + ''('' + current_user + '') - '' + app_name())) FOR [Modifier]
' + replace('GXO' ,'X', '') + '

CREATE TRIGGER TR_' + @.TableName + '_U ON [dbo].[' + @.TableName + ']
FOR UPDATE
AS
set nocount on
update [' + @.TableName + ']
set Modified = getdate(),
' + case when @.Version >= '2005'
then ' Modifier = convert(varchar(100), host_name() + '':'' + Original_Login() + ''('' + current_user + '') - '' + app_name())'
else ' Modifier = convert(varchar(100), host_name() + '':'' + suser_sname() + ''('' + current_user + '') - '' + app_name())'
end + '
from [' + @.TableName + ']
inner join inserted on [' + @.TableName + '].[' + @.IDName + '] = inserted.[' + @.IDName + ']
' + replace('GXO' ,'X', '') + ''

select @.SQLString

See how lazy I am?

I needed to figure something out for keeping track of who is entering data and when. I was looking on BOL to see if I can find something, I'm the main DBA so I have full access to sql enterprise 2005 which I am still learning about. But I think I like 2005 better|||Hi Blindman when I run it I get error messages

Msg 102, Level 15, State 1, Line 13
Incorrect syntax near ')'.
Msg 105, Level 15, State 1, Line 30
Unclosed quotation mark after the character string '
'.
Msg 102, Level 15, State 1, Line 30
Incorrect syntax near '
'.|||Make sure your query tool is not truncating the results. You will need to bump up the max resultset characters option to 8000.

Log files

I am getting the error message ,
"Could not allocate space for object '(SYSTEM table id: -1021390423)' in
database 'TEMPDB' because the 'DEFAULT' filegroup is full."
when i run a select stmt. I am thinking it's because my log file is full.
Can you pls tell me what i am supposed to do now?Read the error message closely, as it explains the problem very well. The pr
oblem is the tempdb
database. It is not the transaction log for tempdb, it is the database files
. They are not large
enough, and sometimes autogrow doesn't grow fast enough for the space needed
by the query
processing. Either pre.allocate storage for tempdb, or tweak the query (look
at query plan, add
indexes, modify the SQL etc).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"PH" <PH@.discussions.microsoft.com> wrote in message
news:E381B423-F43B-4D94-9E4C-802BE97EEF97@.microsoft.com...
>I am getting the error message ,
> "Could not allocate space for object '(SYSTEM table id: -1021390423)' in
> database 'TEMPDB' because the 'DEFAULT' filegroup is full."
> when i run a select stmt. I am thinking it's because my log file is full.
> Can you pls tell me what i am supposed to do now?|||How do I make them grow?
"Tibor Karaszi" wrote:

> Read the error message closely, as it explains the problem very well. The
problem is the tempdb
> database. It is not the transaction log for tempdb, it is the database fil
es. They are not large
> enough, and sometimes autogrow doesn't grow fast enough for the space need
ed by the query
> processing. Either pre.allocate storage for tempdb, or tweak the query (lo
ok at query plan, add
> indexes, modify the SQL etc).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "PH" <PH@.discussions.microsoft.com> wrote in message
> news:E381B423-F43B-4D94-9E4C-802BE97EEF97@.microsoft.com...
>
>|||Should i go to properties,data files and increase the size for space
allocated? Pls reply. Thanks in advance.
"PH" wrote:
> How do I make them grow?
> "Tibor Karaszi" wrote:
>|||Yes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"PH" <PH@.discussions.microsoft.com> wrote in message
news:BA24839D-8D6E-4263-999A-2CF015CFAAA0@.microsoft.com...
> Should i go to properties,data files and increase the size for space
> allocated? Pls reply. Thanks in advance.
> "PH" wrote:
>

Log file grew big ?

We have a table in SQL Server with 3 columns, and 1 of the columns is a
varchar(2000) column.
The database has a "simple" recovery model.
We insert data in the database every millisecond.
Every day, we delete data from the table that is older than 1 w old.
We then changed the column to be varchar(100), because the data is more
compact now.
Since we do this, I notice the log file grew from less than 100 meg to 2
gig. The data file is about 500 meg.
Why is the log file grew so much once I changed 1 of the column to be
varchar(100) ? Thank you very much.
Here is the stored procedure to delete data every day.
CREATE PROCEDURE DeleteDataInBatch
AS
declare @.LastCount smallint
set ROWCOUNT 5000
set @.LastCount = 1
while (@.LastCount > 0)
begin
begin tran
delete from ...
set @.LastCount = @.@.ROWCOUNT
commit tran
end
set ROWCOUNT 0
GO
Here is the stored procedure to insert data.
CREATE PROCEDURE InsertData
@.sContract varchar(8),
@.sData varchar(2000)
AS
insert into ...
GODid you use Enterprise manager to do the change? If so it usually creates a
new table behind the scenes, copies all the data over to it and then drops
the original table. All of this is fully logged and will require lots of
space.
Andrew J. Kelly SQL MVP
"fniles" <fniles@.pfmail.com> wrote in message
news:uhAArB1RFHA.3944@.TK2MSFTNGP10.phx.gbl...
> We have a table in SQL Server with 3 columns, and 1 of the columns is a
> varchar(2000) column.
> The database has a "simple" recovery model.
> We insert data in the database every millisecond.
> Every day, we delete data from the table that is older than 1 w old.
> We then changed the column to be varchar(100), because the data is more
> compact now.
> Since we do this, I notice the log file grew from less than 100 meg to 2
> gig. The data file is about 500 meg.
> Why is the log file grew so much once I changed 1 of the column to be
> varchar(100) ? Thank you very much.
> Here is the stored procedure to delete data every day.
> CREATE PROCEDURE DeleteDataInBatch
> AS
> declare @.LastCount smallint
> set ROWCOUNT 5000
> set @.LastCount = 1
> while (@.LastCount > 0)
> begin
> begin tran
> delete from ...
> set @.LastCount = @.@.ROWCOUNT
> commit tran
> end
> set ROWCOUNT 0
> GO
> Here is the stored procedure to insert data.
> CREATE PROCEDURE InsertData
> @.sContract varchar(8),
> @.sData varchar(2000)
> AS
> insert into ...
> GO
>|||> Did you use Enterprise manager to do the change?
Yes, I changed the column from varchar(2000) to varchar(100) in EM.
So, you think the log file grew big during the process of changing the
column from varchar(2000) to varchar(100) in EM ?
I though I checked the log file size after I did that, but I might be wrong.
With a log file 2 gig in size, will it slow down any querying, inserting or
deleting in the database ?
Do I need and can I shrink this log file size, or shall I leave it alone ?
Thank you very much.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OPBHJb1RFHA.164@.TK2MSFTNGP12.phx.gbl...
> Did you use Enterprise manager to do the change? If so it usually creates
> a new table behind the scenes, copies all the data over to it and then
> drops the original table. All of this is fully logged and will require
> lots of space.
> --
> Andrew J. Kelly SQL MVP
>
> "fniles" wrote in message news:uhAArB1RFHA.3944@.TK2MSFTNGP10.phx.gbl...
>|||I usually set my database log files to about 20 to 30 % of the total databas
e
space.
Also depends on how transaction intensive your database is.
I always restrict my log file size to a maximum value.
I do not thing you woul gain anything by having that big a log file size. I
woud have shrunk it after backing up the database and doing a dump of the
transaction log.
Nishant
"fniles" wrote:

> Yes, I changed the column from varchar(2000) to varchar(100) in EM.
> So, you think the log file grew big during the process of changing the
> column from varchar(2000) to varchar(100) in EM ?
> I though I checked the log file size after I did that, but I might be wron
g.
> With a log file 2 gig in size, will it slow down any querying, inserting o
r
> deleting in the database ?
> Do I need and can I shrink this log file size, or shall I leave it alone ?
> Thank you very much.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OPBHJb1RFHA.164@.TK2MSFTNGP12.phx.gbl...
>
>|||Thank you for your reply.
I do not want the log file to be that big.
We insert data to the database every miliseconds, currently the database is
about 500 meg in size and has about 15 million records in this table.
Is it too late to restrict the log file size now that it already grew to 2
gig ?
If I restrict the log file size, will it slow down inserting and deleting ?
How do I dump the transaction log ?
If I shrink the transaction log, will it slow down inserting and deleting
later ?
Thank you very much.
"NB" <NB@.discussions.microsoft.com> wrote in message
news:705DD5E1-3F87-4D71-86DD-AAFA78DE424A@.microsoft.com...
>I usually set my database log files to about 20 to 30 % of the total
>database
> space.
> Also depends on how transaction intensive your database is.
> I always restrict my log file size to a maximum value.
> I do not thing you woul gain anything by having that big a log file size.
> I
> woud have shrunk it after backing up the database and doing a dump of the
> transaction log.
>
> Nishant
> "fniles" wrote:
>|||There's no penalty to have a "too big" file. Having a small file does come w
ith a penalty, however:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"NB" <NB@.discussions.microsoft.com> wrote in message
news:705DD5E1-3F87-4D71-86DD-AAFA78DE424A@.microsoft.com...
>I usually set my database log files to about 20 to 30 % of the total databa
se
> space.
> Also depends on how transaction intensive your database is.
> I always restrict my log file size to a maximum value.
> I do not thing you woul gain anything by having that big a log file size.
I
> woud have shrunk it after backing up the database and doing a dump of the
> transaction log.
>
> Nishant
> "fniles" wrote:
>

log all queries

Hi!

In order to debug a program written for us I'd like to log all
queries to a database/a specific table in MS SQL Server 2000.

Are there any options to get a logging like this and how do I
enable it?

--
Marco Dieckhoff
icq# 22243433
GPG Key 0x1A6C95BA -- http://www.frankonia-brunonia.de/keysNot exactly. One clumsy option, is to turn on the profiler to track the
SELECT statements & dumb them into an ASCII file. On large databases with
heavy transactions, this may not be worth it though.

--
- Anith
( Please reply to newsgroups only )|||"Marco Dieckhoff" <dieck@.gmx.de> wrote in message
news:bj9m97$nes$2@.rzcomm2.rz.tu-bs.de...
> Hi!
> In order to debug a program written for us I'd like to log all
> queries to a database/a specific table in MS SQL Server 2000.
> Are there any options to get a logging like this and how do I
> enable it?
> --
> Marco Dieckhoff
> icq# 22243433
> GPG Key 0x1A6C95BA -- http://www.frankonia-brunonia.de/keys

You can use Profiler, and set a filter for the tables/databases that you're
interested in. You can either run the trace from the Profiler GUI, or by
creating a server-side trace with the sp_trace% procs. There is plenty of
information on this in Books Online.

Simon|||Hello,

How about writing a trigger attached to the table in question for
select/update/delete operations that will record data to some audit table?

TK

"Marco Dieckhoff" <dieck@.gmx.de> wrote in message
news:bj9m97$nes$2@.rzcomm2.rz.tu-bs.de...
> Hi!
> In order to debug a program written for us I'd like to log all
> queries to a database/a specific table in MS SQL Server 2000.
> Are there any options to get a logging like this and how do I
> enable it?
> --
> Marco Dieckhoff
> icq# 22243433
> GPG Key 0x1A6C95BA -- http://www.frankonia-brunonia.de/keys|||Select triggers are not in SQL 2000!!

If you access your tables by stored procedure then you can implement an
auditing function.

John

"Tom Kitta" <tom@.energyshop.com> wrote in message
news:B727b.28098$mk1.12186@.news02.bloor.is.net.cab le.rogers.com...
> Hello,
> How about writing a trigger attached to the table in question for
> select/update/delete operations that will record data to some audit table?
> TK
>
> "Marco Dieckhoff" <dieck@.gmx.de> wrote in message
> news:bj9m97$nes$2@.rzcomm2.rz.tu-bs.de...
> > Hi!
> > In order to debug a program written for us I'd like to log all
> > queries to a database/a specific table in MS SQL Server 2000.
> > Are there any options to get a logging like this and how do I
> > enable it?
> > --
> > Marco Dieckhoff
> > icq# 22243433
> > GPG Key 0x1A6C95BA -- http://www.frankonia-brunonia.de/keys

locks with select

Hey I have a terrible problem !!

I have a query, and It doesn't matter if it has finished, the table is still locked. Is there any clause to unlock the share mod lock (force) when the query has finished or any way to ensure that table has no locks to continue with another query or transaction ?

Could you give me any sentence ?

Thanks you !!Also, see if there are any open transactions in your database, or in tempdb if your query references temporary objects:
DBCC OPENTRAN('<your_database>')|||Is there any way to turn the data base in optimistic mode, or to execute that in optimistic mode ?|||Yes (http://www.dbforums.com/showpost.php?p=3690898&postcount=13).

-PatP|||Well, but I have a concurrency problem: Update sentence has been thrown while a select is being executed (of the same table), so the select is locking the update, and the query takes a lot of time, what I want to do, is to execute the query and not to wait a long time to be able to execute the update sentence, guaranteeing that I won't have many transactions stuck, and guaranteeing the resulset (of the query) won't have uncommited data.

Any Suggestion ?

Locks output from sysprocesses table

Can you explain the lock information output from
sysprocesses table that I received?
SPID waittype waittime lastwaittype
waitresources
155 0x0000 0 LCK_M_S KEY: 7:1:1 (b400b60e9149)
160 0x0000 0 PAGELATCH_UP 2:1:31965
166 0x0000 0 PAGEIOLATCH_SH 7:1:523911
183 0x0000 0 PAGELATCH_UP 2:1:31988
198 0x0000 0 LCK_M_S KEY: 7:1:1 (b400b60e9149)
216 0x0000 0 LCK_M_S KEY: 7:1:1 (8b00c4cca785)
217 0x0000 0 PAGELATCH_UP 2:1:31984
218 0x0000 0 PAGEIOLATCH_SH 7:1:1395322
219 0x0000 0 PAGEIOLATCH_SH 7:1:642128
228 0x0000 0 PAGEIOLATCH_SH 7:1:1605295
230 0x0000 0 PAGEIOLATCH_SH 7:1:1587592
Thank You,
MikeHi Mike
Right now it is showing that none of your processes are waiting for
anything. All of them have a waittype and waittime of 0 which means they are
NOT waiting. THe lastwaittype column, as the name implies, is the last thing
the process waited on, but there is no indication of how long ago or what
duration that wait was. The LCK-* waits are normal locks being waited for,
and the PAGEIOLATCH* waits are waiting for IO.
This looks totally boring and nothing to worry about.
Did you have some specific concerns about it?
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:1886001c41b62$9579a9e0$a301280a@.phx
.gbl...
> Can you explain the lock information output from
> sysprocesses table that I received?
> SPID waittype waittime lastwaittype
> waitresources
> 155 0x0000 0 LCK_M_S KEY: 7:1:1 (b400b60e9149)
> 160 0x0000 0 PAGELATCH_UP 2:1:31965
> 166 0x0000 0 PAGEIOLATCH_SH 7:1:523911
> 183 0x0000 0 PAGELATCH_UP 2:1:31988
> 198 0x0000 0 LCK_M_S KEY: 7:1:1 (b400b60e9149)
> 216 0x0000 0 LCK_M_S KEY: 7:1:1 (8b00c4cca785)
> 217 0x0000 0 PAGELATCH_UP 2:1:31984
> 218 0x0000 0 PAGEIOLATCH_SH 7:1:1395322
> 219 0x0000 0 PAGEIOLATCH_SH 7:1:642128
> 228 0x0000 0 PAGEIOLATCH_SH 7:1:1605295
> 230 0x0000 0 PAGEIOLATCH_SH 7:1:1587592
> Thank You,
> Mike

locks on table when update statistics

Does anybody know what kind of lock will put on the table
when sqlserver update the statistics?Only a schema stability lock, which means that you can't change the table,
but you can do everything else, and the statistics update won't block
anything.
--
Jacco Schalkwijk
SQL Server MVP
"jzhu" <hzhua16@.hotmail.com> wrote in message
news:03ef01c39e54$197dc460$a101280a@.phx.gbl...
> Does anybody know what kind of lock will put on the table
> when sqlserver update the statistics?

Locks on a table with nonclustered index

If i have a query such as
delete from table1
where col1 = 200
Say i had a non clustered index on col1 and thats the only index that i
have...
1) Can the execution plan include a non-clustered index scan if the
selectivity is low ?
2) If it does use a nonclustered index scan and maybe start of with row
locks and then escalates to table lock, how do you find out if the lock is
on the index pages or on the data pages or on both although in sp_lock it
would show an exclusive table lock ?
3) Even it it just row locks, what info can we get from the resource column
in sp_lock to tell us whether a row in the data page or index page is being
locked.. Would it just be data pages or would it be holding multiple row
locks ..some to delete its rows in data pages and some to delete the rows in
its index pages1. I doubt it would do an index scan in this case, but if the selectivity
were high enough, it could do an index seek.
2. I'm not sure exactly what you're asking. If there is a table lock X,
other processes cannot access the indexes on that table, so whether the
index pages are locked is irrelevant.
3. The resource column doesn't directly tell us, but if you have a page
number, you can use dbcc page to tell what kind of page it is. Also, you can
use the indid column, and if it is >1, then the page is from a nonclustered
index. You can have key locks (which are row locks in an index) on both
index keys and rows/keys in the table itself. In fact, if the table has nc
indexes, you'll usually see both.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"sql" <sql@.hotmail.com> wrote in message
news:u47QCA9hDHA.460@.TK2MSFTNGP12.phx.gbl...
> If i have a query such as
> delete from table1
> where col1 = 200
> Say i had a non clustered index on col1 and thats the only index that i
> have...
> 1) Can the execution plan include a non-clustered index scan if the
> selectivity is low ?
> 2) If it does use a nonclustered index scan and maybe start of with row
> locks and then escalates to table lock, how do you find out if the lock is
> on the index pages or on the data pages or on both although in sp_lock it
> would show an exclusive table lock ?
> 3) Even it it just row locks, what info can we get from the resource
column
> in sp_lock to tell us whether a row in the data page or index page is
being
> locked.. Would it just be data pages or would it be holding multiple row
> locks ..some to delete its rows in data pages and some to delete the rows
in
> its index pages
>
>

Locks on a table

For some reason, one table in our database (SQL 2000) keeps getting locks on
it so attempts to insert records on it seems to time out frequently. Any
ideas on what I might look for to keep this from happening? The table has
only a unique int for a PK and no other indexes. Thanks.David wrote:
> For some reason, one table in our database (SQL 2000) keeps getting locks on
> it so attempts to insert records on it seems to time out frequently. Any
> ideas on what I might look for to keep this from happening? The table has
> only a unique int for a PK and no other indexes. Thanks.
>
When the locking occurs, look at sp_lock, sp_who2, determine what spid
is locking the table. Then use DBCC INPUTBUFFER to see what that spid
is doing. My guess is that something is table-scanning the table, maybe
a sign that additional indexes are needed.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I'm not sure if this helps, but when I go into EM and look at the
Locks/process ID and click on a couple of the spid records, I see the
following entry in the objects list on a couple of the spid records:
Object = tempdb.dbo.##lockinfo67
Lock Type = TAB
Mode = X
Status = GRANT
Owner = Xact
Index = ##lockinfo67
Not sure if this helps.
David
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45ACFD6F.3060409@.realsqlguy.com...
> David wrote:
>> For some reason, one table in our database (SQL 2000) keeps getting locks
>> on it so attempts to insert records on it seems to time out frequently.
>> Any ideas on what I might look for to keep this from happening? The
>> table has only a unique int for a PK and no other indexes. Thanks.
> When the locking occurs, look at sp_lock, sp_who2, determine what spid is
> locking the table. Then use DBCC INPUTBUFFER to see what that spid is
> doing. My guess is that something is table-scanning the table, maybe a
> sign that additional indexes are needed.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||David wrote:
> I'm not sure if this helps, but when I go into EM and look at the
> Locks/process ID and click on a couple of the spid records, I see the
> following entry in the objects list on a couple of the spid records:
> Object = tempdb.dbo.##lockinfo67
> Lock Type = TAB
> Mode = X
> Status = GRANT
> Owner = Xact
> Index = ##lockinfo67
> Not sure if this helps.
>
Forget the GUI - go to Query Analyzer and run the stuff that I mentioned.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||OK. It looks like there is a missing index on a critical FK field on a
frequently used table. I think your comment about doing table scans is
probably correct. I'm going to add an index tonight and see if the problem
goes away.
David
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45AD017B.40400@.realsqlguy.com...
> David wrote:
>> I'm not sure if this helps, but when I go into EM and look at the
>> Locks/process ID and click on a couple of the spid records, I see the
>> following entry in the objects list on a couple of the spid records:
>> Object = tempdb.dbo.##lockinfo67
>> Lock Type = TAB
>> Mode = X
>> Status = GRANT
>> Owner = Xact
>> Index = ##lockinfo67
>> Not sure if this helps.
> Forget the GUI - go to Query Analyzer and run the stuff that I mentioned.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||David wrote:
> OK. It looks like there is a missing index on a critical FK field on a
> frequently used table. I think your comment about doing table scans is
> probably correct. I'm going to add an index tonight and see if the problem
> goes away.
>
That'll do it!
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||David wrote:
> OK. It looks like there is a missing index on a critical FK field on a
> frequently used table. I think your comment about doing table scans is
> probably correct. I'm going to add an index tonight and see if the problem
> goes away.
>
That'll do it!
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Locks on a table

For some reason, one table in our database (SQL 2000) keeps getting locks on
it so attempts to insert records on it seems to time out frequently. Any
ideas on what I might look for to keep this from happening? The table has
only a unique int for a PK and no other indexes. Thanks.David wrote:
> For some reason, one table in our database (SQL 2000) keeps getting locks
on
> it so attempts to insert records on it seems to time out frequently. Any
> ideas on what I might look for to keep this from happening? The table has
> only a unique int for a PK and no other indexes. Thanks.
>
When the locking occurs, look at sp_lock, sp_who2, determine what spid
is locking the table. Then use DBCC INPUTBUFFER to see what that spid
is doing. My guess is that something is table-scanning the table, maybe
a sign that additional indexes are needed.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I'm not sure if this helps, but when I go into EM and look at the
Locks/process ID and click on a couple of the spid records, I see the
following entry in the objects list on a couple of the spid records:
Object = tempdb.dbo.##lockinfo67
Lock Type = TAB
Mode = X
Status = GRANT
Owner = Xact
Index = ##lockinfo67
Not sure if this helps.
David
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45ACFD6F.3060409@.realsqlguy.com...
> David wrote:
> When the locking occurs, look at sp_lock, sp_who2, determine what spid is
> locking the table. Then use DBCC INPUTBUFFER to see what that spid is
> doing. My guess is that something is table-scanning the table, maybe a
> sign that additional indexes are needed.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||David wrote:
> I'm not sure if this helps, but when I go into EM and look at the
> Locks/process ID and click on a couple of the spid records, I see the
> following entry in the objects list on a couple of the spid records:
> Object = tempdb.dbo.##lockinfo67
> Lock Type = TAB
> Mode = X
> Status = GRANT
> Owner = Xact
> Index = ##lockinfo67
> Not sure if this helps.
>
Forget the GUI - go to Query Analyzer and run the stuff that I mentioned.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||OK. It looks like there is a missing index on a critical FK field on a
frequently used table. I think your comment about doing table scans is
probably correct. I'm going to add an index tonight and see if the problem
goes away.
David
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45AD017B.40400@.realsqlguy.com...
> David wrote:
> Forget the GUI - go to Query Analyzer and run the stuff that I mentioned.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||David wrote:
> OK. It looks like there is a missing index on a critical FK field on a
> frequently used table. I think your comment about doing table scans is
> probably correct. I'm going to add an index tonight and see if the proble
m
> goes away.
>
That'll do it!
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||David wrote:
> OK. It looks like there is a missing index on a critical FK field on a
> frequently used table. I think your comment about doing table scans is
> probably correct. I'm going to add an index tonight and see if the proble
m
> goes away.
>
That'll do it!
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Locks on a table

For some reason, one table in our database (SQL 2000) keeps getting locks on
it so attempts to insert records on it seems to time out frequently. Any
ideas on what I might look for to keep this from happening? The table has
only a unique int for a PK and no other indexes. Thanks.
David wrote:
> For some reason, one table in our database (SQL 2000) keeps getting locks on
> it so attempts to insert records on it seems to time out frequently. Any
> ideas on what I might look for to keep this from happening? The table has
> only a unique int for a PK and no other indexes. Thanks.
>
When the locking occurs, look at sp_lock, sp_who2, determine what spid
is locking the table. Then use DBCC INPUTBUFFER to see what that spid
is doing. My guess is that something is table-scanning the table, maybe
a sign that additional indexes are needed.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||I'm not sure if this helps, but when I go into EM and look at the
Locks/process ID and click on a couple of the spid records, I see the
following entry in the objects list on a couple of the spid records:
Object = tempdb.dbo.##lockinfo67
Lock Type = TAB
Mode = X
Status = GRANT
Owner = Xact
Index = ##lockinfo67
Not sure if this helps.
David
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45ACFD6F.3060409@.realsqlguy.com...
> David wrote:
> When the locking occurs, look at sp_lock, sp_who2, determine what spid is
> locking the table. Then use DBCC INPUTBUFFER to see what that spid is
> doing. My guess is that something is table-scanning the table, maybe a
> sign that additional indexes are needed.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||David wrote:
> I'm not sure if this helps, but when I go into EM and look at the
> Locks/process ID and click on a couple of the spid records, I see the
> following entry in the objects list on a couple of the spid records:
> Object = tempdb.dbo.##lockinfo67
> Lock Type = TAB
> Mode = X
> Status = GRANT
> Owner = Xact
> Index = ##lockinfo67
> Not sure if this helps.
>
Forget the GUI - go to Query Analyzer and run the stuff that I mentioned.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||OK. It looks like there is a missing index on a critical FK field on a
frequently used table. I think your comment about doing table scans is
probably correct. I'm going to add an index tonight and see if the problem
goes away.
David
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45AD017B.40400@.realsqlguy.com...
> David wrote:
> Forget the GUI - go to Query Analyzer and run the stuff that I mentioned.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||David wrote:
> OK. It looks like there is a missing index on a critical FK field on a
> frequently used table. I think your comment about doing table scans is
> probably correct. I'm going to add an index tonight and see if the problem
> goes away.
>
That'll do it!
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||David wrote:
> OK. It looks like there is a missing index on a critical FK field on a
> frequently used table. I think your comment about doing table scans is
> probably correct. I'm going to add an index tonight and see if the problem
> goes away.
>
That'll do it!
Tracy McKibben
MCDBA
http://www.realsqlguy.com