I have an Access2000 app placed on a shred drive. The problem i am having is that users leave it opened and take long breaks. Sometimes they leave it overnight. I would like to make corrections to the form but can't. How can I save and close all users. I would like to send a message telling all users to save any changes and give them 1 minute before shutting down.
ThanksHow do you plan to force them out?
I guess you could look at the timer interval event in Access...|||We have this problem on a small Access DB attached to Sybase. As far as we know, there's nothing you can do. The best I've managed to date is to open the lock file, take the computer names in the lock file and NETMSG them, but it doesn't guarantee they'll close the DB.
Showing posts with label users. Show all posts
Showing posts with label users. Show all posts
Friday, March 30, 2012
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.
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.
Wednesday, March 21, 2012
Log file is not truncated inspite of regular log backup
I am taking regular log backup (every 30 mins). But still
the log file size of one database is 1110MB even when no
users are connected.
While using dbcc shrinkfile, i am getting the following
msg:
Cannot shrink log file 2 (Gsap_Log) because all logical
log files are in use.
DBCC OPENTRAN gives:
Replicated Transaction Information:
Oldest distributed LSN : (1134:527:1)
Oldest non-distributed LSN : (1134:1041:4)
Execution of the SP sp_repldone gives "The database is
not published."
EXEC sp_repldone @.xactid = NULL, @.xact_segno = NULL,
@.numtrans = 0, @.time = 0, @.reset = 1
Can anyone help me in truncating my log file?
Regards,
MangaiCheck out below KB articles:
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/default.aspx?scid=kb;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
Log File Grows too big
http://www.support.microsoft.com/?id=317375
Log file filling up
http://www.support.microsoft.com/?id=110139
Considerations for Autogrow and AutoShrink
http://www.support.microsoft.com/?id=315512
http://www.mssqlserver.com/faq/logs-shrinklog.asp
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mangai" <mangaiyarkarasi_@.hotmail.com> wrote in message
news:007001c3af1e$a1c81570$a001280a@.phx.gbl...
> I am taking regular log backup (every 30 mins). But still
> the log file size of one database is 1110MB even when no
> users are connected.
> While using dbcc shrinkfile, i am getting the following
> msg:
> Cannot shrink log file 2 (Gsap_Log) because all logical
> log files are in use.
> DBCC OPENTRAN gives:
> Replicated Transaction Information:
> Oldest distributed LSN : (1134:527:1)
> Oldest non-distributed LSN : (1134:1041:4)
> Execution of the SP sp_repldone gives "The database is
> not published."
> EXEC sp_repldone @.xactid = NULL, @.xact_segno = NULL,
> @.numtrans = 0, @.time = 0, @.reset = 1
>
> Can anyone help me in truncating my log file?
> Regards,
> Mangai
>|||Got the solution.
Just created a publication on the server for the database
and executed sp_repldone with null values. The log file
was truncated and I had shrinked the log file to 10 MB.
>--Original Message--
>I am taking regular log backup (every 30 mins). But
still
>the log file size of one database is 1110MB even when no
>users are connected.
>While using dbcc shrinkfile, i am getting the following
>msg:
>Cannot shrink log file 2 (Gsap_Log) because all logical
>log files are in use.
>DBCC OPENTRAN gives:
>Replicated Transaction Information:
> Oldest distributed LSN : (1134:527:1)
> Oldest non-distributed LSN : (1134:1041:4)
>Execution of the SP sp_repldone gives "The database is
>not published."
>EXEC sp_repldone @.xactid = NULL, @.xact_segno = NULL,
>@.numtrans = 0, @.time = 0, @.reset = 1
>
>Can anyone help me in truncating my log file?
>Regards,
>Mangai
>.
>
the log file size of one database is 1110MB even when no
users are connected.
While using dbcc shrinkfile, i am getting the following
msg:
Cannot shrink log file 2 (Gsap_Log) because all logical
log files are in use.
DBCC OPENTRAN gives:
Replicated Transaction Information:
Oldest distributed LSN : (1134:527:1)
Oldest non-distributed LSN : (1134:1041:4)
Execution of the SP sp_repldone gives "The database is
not published."
EXEC sp_repldone @.xactid = NULL, @.xact_segno = NULL,
@.numtrans = 0, @.time = 0, @.reset = 1
Can anyone help me in truncating my log file?
Regards,
MangaiCheck out below KB articles:
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/default.aspx?scid=kb;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
Log File Grows too big
http://www.support.microsoft.com/?id=317375
Log file filling up
http://www.support.microsoft.com/?id=110139
Considerations for Autogrow and AutoShrink
http://www.support.microsoft.com/?id=315512
http://www.mssqlserver.com/faq/logs-shrinklog.asp
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mangai" <mangaiyarkarasi_@.hotmail.com> wrote in message
news:007001c3af1e$a1c81570$a001280a@.phx.gbl...
> I am taking regular log backup (every 30 mins). But still
> the log file size of one database is 1110MB even when no
> users are connected.
> While using dbcc shrinkfile, i am getting the following
> msg:
> Cannot shrink log file 2 (Gsap_Log) because all logical
> log files are in use.
> DBCC OPENTRAN gives:
> Replicated Transaction Information:
> Oldest distributed LSN : (1134:527:1)
> Oldest non-distributed LSN : (1134:1041:4)
> Execution of the SP sp_repldone gives "The database is
> not published."
> EXEC sp_repldone @.xactid = NULL, @.xact_segno = NULL,
> @.numtrans = 0, @.time = 0, @.reset = 1
>
> Can anyone help me in truncating my log file?
> Regards,
> Mangai
>|||Got the solution.
Just created a publication on the server for the database
and executed sp_repldone with null values. The log file
was truncated and I had shrinked the log file to 10 MB.
>--Original Message--
>I am taking regular log backup (every 30 mins). But
still
>the log file size of one database is 1110MB even when no
>users are connected.
>While using dbcc shrinkfile, i am getting the following
>msg:
>Cannot shrink log file 2 (Gsap_Log) because all logical
>log files are in use.
>DBCC OPENTRAN gives:
>Replicated Transaction Information:
> Oldest distributed LSN : (1134:527:1)
> Oldest non-distributed LSN : (1134:1041:4)
>Execution of the SP sp_repldone gives "The database is
>not published."
>EXEC sp_repldone @.xactid = NULL, @.xact_segno = NULL,
>@.numtrans = 0, @.time = 0, @.reset = 1
>
>Can anyone help me in truncating my log file?
>Regards,
>Mangai
>.
>
Friday, February 24, 2012
Log all connections to a SQL Server
I need to log all users connecting to any database on my SQL Server 2000
cluster.
cluster.
Anyone know how I can accomplish this ?
Ta,
CJ.Hi
Some options:
On the security tab for the server you can set login auditing.
You can also set C2 auditing on for more information.
You can use profiler to log these events.
You can use a third party tool.
John
"Chris Sleightholme" <sleightholme@.ntlworld.com> wrote in message
news:r7Byd.945$Rz6.861@.newsfe2-win.ntli.net...
>I need to log all users connecting to any database on my SQL Server 2000
> cluster.
> Anyone know how I can accomplish this ?
> Ta,
> CJ.|||Try this in Query Analyzer,
use master
go
Select * from sysprocesses
This should give you all the fields you need.
Oscar...
"Chris Sleightholme" <sleightholme@.ntlworld.com> wrote in message
news:r7Byd.945$Rz6.861@.newsfe2-win.ntli.net...
> I need to log all users connecting to any database on my SQL Server 2000
> cluster.
> Anyone know how I can accomplish this ?
> Ta,
> CJ.
Log activities
With the exception of the Profiler, is there another way
to monitor the transaction taking place on the server? I
want to be able to see what users are doing like which
commands they executed and if it fails or not.
Thanks
EmmaEmma,
there are several third-party tools to do this sort of thing eg LogExplorer
could be useful - either looking at historical logs or real-time:
http://www.lumigent.com/products/le_sql/le_sql.htm.
Regards,
Paul Ibison
to monitor the transaction taking place on the server? I
want to be able to see what users are doing like which
commands they executed and if it fails or not.
Thanks
EmmaEmma,
there are several third-party tools to do this sort of thing eg LogExplorer
could be useful - either looking at historical logs or real-time:
http://www.lumigent.com/products/le_sql/le_sql.htm.
Regards,
Paul Ibison
Log activities
With the exception of the Profiler, is there another way
to monitor the transaction taking place on the server? I
want to be able to see what users are doing like which
commands they executed and if it fails or not.
Thanks
EmmaEmma,
there are several third-party tools to do this sort of thing eg LogExplorer
could be useful - either looking at historical logs or real-time:
http://www.lumigent.com/products/le_sql/le_sql.htm.
Regards,
Paul Ibison
to monitor the transaction taking place on the server? I
want to be able to see what users are doing like which
commands they executed and if it fails or not.
Thanks
EmmaEmma,
there are several third-party tools to do this sort of thing eg LogExplorer
could be useful - either looking at historical logs or real-time:
http://www.lumigent.com/products/le_sql/le_sql.htm.
Regards,
Paul Ibison
Log activities
With the exception of the Profiler, is there another way
to monitor the transaction taking place on the server? I
want to be able to see what users are doing like which
commands they executed and if it fails or not.
Thanks
Emma
Emma,
there are several third-party tools to do this sort of thing eg LogExplorer
could be useful - either looking at historical logs or real-time:
http://www.lumigent.com/products/le_sql/le_sql.htm.
Regards,
Paul Ibison
to monitor the transaction taking place on the server? I
want to be able to see what users are doing like which
commands they executed and if it fails or not.
Thanks
Emma
Emma,
there are several third-party tools to do this sort of thing eg LogExplorer
could be useful - either looking at historical logs or real-time:
http://www.lumigent.com/products/le_sql/le_sql.htm.
Regards,
Paul Ibison
Subscribe to:
Posts (Atom)