Can someone send me the command to import the application and system log of
the eventlogs into SQL Server for say 2 different servers ?
Does the command have to run every few mins ? How does it handle
duplicates,etc. ? I would like to have the logs in the database no later
than say 15 mins from the time they make it in the respective log. But I
dont know how log parser is smart enough to do that unless I run it every 5
mins but then again, what does it scan for and only ensures it does not
insert duplicates
Thanks
You have to write something custom to do this. You can do it with an SSIS
WMI data reader task. The class to look at is win32_ntlogevent.
Other options include powershell to from xp_cmdshell or a CLR function to
get to WMI.
Jason Massie
http://statisticsio.com
"Hassan" <hassan@.test.com> wrote in message
news:ut0pOZvKIHA.5360@.TK2MSFTNGP03.phx.gbl...
> Can someone send me the command to import the application and system log
> of the eventlogs into SQL Server for say 2 different servers ?
> Does the command have to run every few mins ? How does it handle
> duplicates,etc. ? I would like to have the logs in the database no later
> than say 15 mins from the time they make it in the respective log. But I
> dont know how log parser is smart enough to do that unless I run it every
> 5 mins but then again, what does it scan for and only ensures it does not
> insert duplicates
> Thanks
>
Showing posts with label application. Show all posts
Showing posts with label application. Show all posts
Friday, March 30, 2012
Log of Executed SQL
I am sure this is a very newbie questions, but...
How do you view what SQL statements have been run on your DB?
I have written an application in C#, and for some reasons some rows are being dropped. I would like to look up what SQL has been run against the Database to make it lose the rows. I was able to do this in MySql, but can not figure out how to do it in SQL Server/Express 2005.
Thanks for any help,
Normally you would use a nice tool like SQL Profiler to give you this data. But in your case (you don't get this tool with Express) I would take a look at sp_trace_create in Books Online and start from there.|||Thanks! That is exactly what I was looking for.
Log of Executed SQL
I am sure this is a very newbie questions, but...
How do you view what SQL statements have been run on your DB?
I have written an application in C#, and for some reasons some rows are being dropped. I would like to look up what SQL has been run against the Database to make it lose the rows. I was able to do this in MySql, but can not figure out how to do it in SQL Server/Express 2005.
Thanks for any help,
Normally you would use a nice tool like SQL Profiler to give you this data. But in your case (you don't get this tool with Express) I would take a look at sp_trace_create in Books Online and start from there.|||Thanks! That is exactly what I was looking for.sql
Log logins
On a huge Sql-Server 7 installation, we have various client applications
distributed along the Lan, accessing one main database.
distributed along the Lan, accessing one main database.
Each application accesses the db using one out of around ten logins. Most of
them, have only DBDataReader right on the db, as they are consultation
consolles only.
In order to monitor db usage, the customer requires some kind of log of user
access.
My need, mainly, is to INSERT a record into a log table, recording Date,
Time, Login, Host of each access.
But, and this is the problem, the job has to be done by the server itself,
not by each client, because of various reasons:
1) we don't like to increase rights of logins
2) we don't plan to change anything in our custom client application
3) few of those client applications have been developed by foreign
suppliers, so we cannot change them.
My question is: does it exist any kind of authentication LOG, which I can
work on?
Or, is it possible to activate a kind of TRIGGER, reacting on login
authentication?
Thanks in advance
AlbertoIf you right-click on the server name in enterprise manager (EM) and click
on properties, click on the SECURITY TAB.
You can then audit sucessfull logins, login failures, or all. There is only
2 things you have to remember.
1. You will have to read either the SQL server error log or the Application
log of Windows.
2. If people use generic logins or shared logins, you might not be able to
detect who it was that logged in.
Hope this helps
Oscar...
"Albe V" <vaccariTOGLI_QUESTO@.hotmail.com> wrote in message
news:JAZKa.125587$Ny5.3548606@.twister2.libero.it.. .
> On a huge Sql-Server 7 installation, we have various client applications
> distributed along the Lan, accessing one main database.
> Each application accesses the db using one out of around ten logins. Most
of
> them, have only DBDataReader right on the db, as they are consultation
> consolles only.
> In order to monitor db usage, the customer requires some kind of log of
user
> access.
> My need, mainly, is to INSERT a record into a log table, recording Date,
> Time, Login, Host of each access.
> But, and this is the problem, the job has to be done by the server itself,
> not by each client, because of various reasons:
> 1) we don't like to increase rights of logins
> 2) we don't plan to change anything in our custom client application
> 3) few of those client applications have been developed by foreign
> suppliers, so we cannot change them.
> My question is: does it exist any kind of authentication LOG, which I can
> work on?
> Or, is it possible to activate a kind of TRIGGER, reacting on login
> authentication?
> Thanks in advance
> Alberto|||Albe V (vaccariTOGLI_QUESTO@.hotmail.com) writes:
> On a huge Sql-Server 7 installation, we have various client applications
> distributed along the Lan, accessing one main database.
> Each application accesses the db using one out of around ten logins.
> Most of them, have only DBDataReader right on the db, as they are
> consultation consolles only.
> In order to monitor db usage, the customer requires some kind of log of
> user access.
> My need, mainly, is to INSERT a record into a log table, recording Date,
> Time, Login, Host of each access.
The best tool for this is the SQL Profiler. Set up a trace that captures
login events. I don't have the SQL7 docs available, so I prefer to not
give any details, but refer you to Books Online. On SQL2000 you can set
C2 auditing, which enures that you don't loose events if the log runs
out of disk space. (That throttles the server instead.) But I don't think
C2 is available on SQL7. Then again, with only ten users, you are not
likely to fill the log if you only filter logins.
Beware, though, that applications that may be written so that they
connect and reconnect frequently, for instance once per query.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Log is growing crazy when run DBCC INDEXDEFRAG or DBREINDEX
We have SQL 2K Enterprise Edition with SP3. Server is dedicated database
server used for JDE application. Database size is 100 Gig. This DB is
configured for replication (only 25 tables). This database is also
configured for Log ship to a stand by server where we run our reports.
When we run DBCC DBREINDEX or DBCC INDEXDEFRAG on this database, the log
file start growing crazy, which makes replication and log ship to break.
Our concern is how can we avoid growing log file while DBREINDEX or
INDEXDEFRAG running?
Your response is appreciated.
Thanks,
AbbasBackup or truncate the transaction log frequently during
the processes is the only thing I can think of. Or set
the Database Recovery model to Simple. Any other ideas?
>--Original Message--
>We have SQL 2K Enterprise Edition with SP3. Server is
dedicated database
>server used for JDE application. Database size is 100
Gig. This DB is
>configured for replication (only 25 tables). This
database is also
>configured for Log ship to a stand by server where we run
our reports.
>When we run DBCC DBREINDEX or DBCC INDEXDEFRAG on this
database, the log
>file start growing crazy, which makes replication and log
ship to break.
>Our concern is how can we avoid growing log file while
DBREINDEX or
>INDEXDEFRAG running?
>Your response is appreciated.
>Thanks,
>Abbas
>
>.
>|||*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||You can't. These actions, like all in the server are logged. These type of
actions will send a lot of data to the log files. I suggest doing them in
small batches so the logs can recover in between the reindexing. If done
often or there is little fragmentation INDEXDEFRAGmay produce less log
entries.
--
Andrew J. Kelly
SQL Server MVP
"Moh Abb" <mabbas@.aligntech.com> wrote in message
news:%23kkvKL%23cDHA.1828@.TK2MSFTNGP10.phx.gbl...
> We have SQL 2K Enterprise Edition with SP3. Server is dedicated database
> server used for JDE application. Database size is 100 Gig. This DB is
> configured for replication (only 25 tables). This database is also
> configured for Log ship to a stand by server where we run our reports.
> When we run DBCC DBREINDEX or DBCC INDEXDEFRAG on this database, the log
> file start growing crazy, which makes replication and log ship to break.
> Our concern is how can we avoid growing log file while DBREINDEX or
> INDEXDEFRAG running?
> Your response is appreciated.
> Thanks,
> Abbas
>
>|||Hello
You can't avoid this, but you can accommodate yourself to this :-)
I'm using the system of two connected jobs. One of them runs
DBCC INDEXDEFRAG and second periodically (every minute)
checks log state and stops first job when log have more than 70%
of space filled. And the system waits for the next log backup and
starts again. In general controlling job can start log backup instead
of stopping defragmentation.
> When we run DBCC DBREINDEX or DBCC INDEXDEFRAG on this database, the log
> file start growing crazy, which makes replication and log ship to break.
> Our concern is how can we avoid growing log file while DBREINDEX or
> INDEXDEFRAG running?
Serge Shakhovsql
server used for JDE application. Database size is 100 Gig. This DB is
configured for replication (only 25 tables). This database is also
configured for Log ship to a stand by server where we run our reports.
When we run DBCC DBREINDEX or DBCC INDEXDEFRAG on this database, the log
file start growing crazy, which makes replication and log ship to break.
Our concern is how can we avoid growing log file while DBREINDEX or
INDEXDEFRAG running?
Your response is appreciated.
Thanks,
AbbasBackup or truncate the transaction log frequently during
the processes is the only thing I can think of. Or set
the Database Recovery model to Simple. Any other ideas?
>--Original Message--
>We have SQL 2K Enterprise Edition with SP3. Server is
dedicated database
>server used for JDE application. Database size is 100
Gig. This DB is
>configured for replication (only 25 tables). This
database is also
>configured for Log ship to a stand by server where we run
our reports.
>When we run DBCC DBREINDEX or DBCC INDEXDEFRAG on this
database, the log
>file start growing crazy, which makes replication and log
ship to break.
>Our concern is how can we avoid growing log file while
DBREINDEX or
>INDEXDEFRAG running?
>Your response is appreciated.
>Thanks,
>Abbas
>
>.
>|||*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||You can't. These actions, like all in the server are logged. These type of
actions will send a lot of data to the log files. I suggest doing them in
small batches so the logs can recover in between the reindexing. If done
often or there is little fragmentation INDEXDEFRAGmay produce less log
entries.
--
Andrew J. Kelly
SQL Server MVP
"Moh Abb" <mabbas@.aligntech.com> wrote in message
news:%23kkvKL%23cDHA.1828@.TK2MSFTNGP10.phx.gbl...
> We have SQL 2K Enterprise Edition with SP3. Server is dedicated database
> server used for JDE application. Database size is 100 Gig. This DB is
> configured for replication (only 25 tables). This database is also
> configured for Log ship to a stand by server where we run our reports.
> When we run DBCC DBREINDEX or DBCC INDEXDEFRAG on this database, the log
> file start growing crazy, which makes replication and log ship to break.
> Our concern is how can we avoid growing log file while DBREINDEX or
> INDEXDEFRAG running?
> Your response is appreciated.
> Thanks,
> Abbas
>
>|||Hello
You can't avoid this, but you can accommodate yourself to this :-)
I'm using the system of two connected jobs. One of them runs
DBCC INDEXDEFRAG and second periodically (every minute)
checks log state and stops first job when log have more than 70%
of space filled. And the system waits for the next log backup and
starts again. In general controlling job can start log backup instead
of stopping defragmentation.
> When we run DBCC DBREINDEX or DBCC INDEXDEFRAG on this database, the log
> file start growing crazy, which makes replication and log ship to break.
> Our concern is how can we avoid growing log file while DBREINDEX or
> INDEXDEFRAG running?
Serge Shakhovsql
Monday, March 12, 2012
Log File Filling Up
Hi All,
Recently we have experienced a problem with our transaction log file filling
up. The application is a moderatley busy e-commerce site. The database is
set to simple recovery mode. The transaction log space is 100mb. This is
filling up on a daily basis.
I have two questions:
- What sort of things should I be looking for to solve this? (My hosting
company have suggested transactions are not completing) ?
- Is there a command (Like sp_helpdb) that returns the trans log size?
Many Thanks,
Simon.
-
* Please reply to group for the benefit of all
* Found the answer to your own question? Post it!
* Get a useful reply to one of your posts?...post an answer to another one
* Search first, post later : http://www.google.co.uk/groups
* Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!Your hosting company may be right. Run DBCC OPENTRAN on your DB. If the
same SPID comes up with the same date/time, then you have an open
transaction. Only once that tran is committed or rolled back will you be
able to truncate the log.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Simon Harris" <too-much-spam@.makes-you-fat.com> wrote in message
news:gg_fg.1444$lQ.247@.newsfe3-gui.ntli.net...
Hi All,
Recently we have experienced a problem with our transaction log file filling
up. The application is a moderatley busy e-commerce site. The database is
set to simple recovery mode. The transaction log space is 100mb. This is
filling up on a daily basis.
I have two questions:
- What sort of things should I be looking for to solve this? (My hosting
company have suggested transactions are not completing) ?
- Is there a command (Like sp_helpdb) that returns the trans log size?
Many Thanks,
Simon.
-
* Please reply to group for the benefit of all
* Found the answer to your own question? Post it!
* Get a useful reply to one of your posts?...post an answer to another one
* Search first, post later : http://www.google.co.uk/groups
* Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!|||Xref: TK2MSFTNGP01.phx.gbl microsoft.public.sqlserver.server:436392
Thank you for your reply Tom.
When you say "If the same SPID comes up with the same date/time" what
SPID/Date Time should I be comparing to?
Am I correct in thinking that the transaction log is automatically truncated
following completion of a successful transaction? (When in Simple recovery
mode)
Can you define an 'open transaction'? What circumstances may cause this?
Do you have any hints/tips as to how I may diagnose what code/queries are
causing this?
Sorry for all the questions, thanks again.
Simon.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OJH2iqmhGHA.4044@.TK2MSFTNGP03.phx.gbl...
> Your hosting company may be right. Run DBCC OPENTRAN on your DB. If the
> same SPID comes up with the same date/time, then you have an open
> transaction. Only once that tran is committed or rolled back will you be
> able to truncate the log.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Simon Harris" <too-much-spam@.makes-you-fat.com> wrote in message
> news:gg_fg.1444$lQ.247@.newsfe3-gui.ntli.net...
> Hi All,
> Recently we have experienced a problem with our transaction log file
> filling
> up. The application is a moderatley busy e-commerce site. The database is
> set to simple recovery mode. The transaction log space is 100mb. This is
> filling up on a daily basis.
> I have two questions:
> - What sort of things should I be looking for to solve this? (My hosting
> company have suggested transactions are not completing) ?
> - Is there a command (Like sp_helpdb) that returns the trans log size?
> Many Thanks,
> Simon.
> --
> -
> * Please reply to group for the benefit of all
> * Found the answer to your own question? Post it!
> * Get a useful reply to one of your posts?...post an answer to another one
> * Search first, post later : http://www.google.co.uk/groups
> * Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!
>|||Thank you for your reply Tom.
When you say "If the same SPID comes up with the same date/time" what
SPID/Date Time should I be comparing to?
Am I correct in thinking that the transaction log is automatically truncated
following completion of a successful transaction? (When in Simple recovery
mode)
Can you define an 'open transaction'? What circumstances may cause this?
Do you have any hints/tips as to how I may diagnose what code/queries are
causing this?
Sorry for all the questions, thanks again.
Simon.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OJH2iqmhGHA.4044@.TK2MSFTNGP03.phx.gbl...
> Your hosting company may be right. Run DBCC OPENTRAN on your DB. If the
> same SPID comes up with the same date/time, then you have an open
> transaction. Only once that tran is committed or rolled back will you be
> able to truncate the log.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Simon Harris" <too-much-spam@.makes-you-fat.com> wrote in message
> news:gg_fg.1444$lQ.247@.newsfe3-gui.ntli.net...
> Hi All,
> Recently we have experienced a problem with our transaction log file
> filling
> up. The application is a moderatley busy e-commerce site. The database is
> set to simple recovery mode. The transaction log space is 100mb. This is
> filling up on a daily basis.
> I have two questions:
> - What sort of things should I be looking for to solve this? (My hosting
> company have suggested transactions are not completing) ?
> - Is there a command (Like sp_helpdb) that returns the trans log size?
> Many Thanks,
> Simon.
> --
> -
> * Please reply to group for the benefit of all
> * Found the answer to your own question? Post it!
> * Get a useful reply to one of your posts?...post an answer to another one
> * Search first, post later : http://www.google.co.uk/groups
> * Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!
>
---
I am using the free version of SPAMfighter for private users.
It has removed 5334 spam emails to date.
Paying users do not have this message in their emails.
Get the free SPAMfighter here: http://www.spamfighter.com/len|||Check out "DBCC OPENTRAN" in the BOL. The output it gives you the date/time
of the longest-running transaction for a given DB. The SPID (system process
ID). If the date/time never changes as you repeatedly run DBCC OPENTRAN,
then you have a long-running transaction. You could then run DBCC
INPUTBUFFER on that SPID to see what the last command was.
An explicit transaction begins with a BEGIN TRAN. None of the work that you
do from that point forward is made permanent until you issue a COMMIT TRAN
on that same connection. Once that occurs, the log can be truncated up to
that point. An implicit transaction is an INSERT, UPDATE or DELETE. Check
out the BOL for Transactions for more details.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Simon Harris" <too-much-spam@.makes-you-fat.com> wrote in message
news:r9%fg.1734$lQ.1657@.newsfe3-gui.ntli.net...
Thank you for your reply Tom.
When you say "If the same SPID comes up with the same date/time" what
SPID/Date Time should I be comparing to?
Am I correct in thinking that the transaction log is automatically truncated
following completion of a successful transaction? (When in Simple recovery
mode)
Can you define an 'open transaction'? What circumstances may cause this?
Do you have any hints/tips as to how I may diagnose what code/queries are
causing this?
Sorry for all the questions, thanks again.
Simon.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OJH2iqmhGHA.4044@.TK2MSFTNGP03.phx.gbl...
> Your hosting company may be right. Run DBCC OPENTRAN on your DB. If the
> same SPID comes up with the same date/time, then you have an open
> transaction. Only once that tran is committed or rolled back will you be
> able to truncate the log.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Simon Harris" <too-much-spam@.makes-you-fat.com> wrote in message
> news:gg_fg.1444$lQ.247@.newsfe3-gui.ntli.net...
> Hi All,
> Recently we have experienced a problem with our transaction log file
> filling
> up. The application is a moderatley busy e-commerce site. The database is
> set to simple recovery mode. The transaction log space is 100mb. This is
> filling up on a daily basis.
> I have two questions:
> - What sort of things should I be looking for to solve this? (My hosting
> company have suggested transactions are not completing) ?
> - Is there a command (Like sp_helpdb) that returns the trans log size?
> Many Thanks,
> Simon.
> --
> -
> * Please reply to group for the benefit of all
> * Found the answer to your own question? Post it!
> * Get a useful reply to one of your posts?...post an answer to another one
> * Search first, post later : http://www.google.co.uk/groups
> * Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!
>|||Seems my hosting company do not give me the relevent permissions to run DBCC
OPENTRAN.
---
I am using the free version of SPAMfighter for private users.
It has removed 5334 spam emails to date.
Paying users do not have this message in their emails.
Get the free SPAMfighter here: http://www.spamfighter.com/len|||Perhaps you can have their DBA run it.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Simon Harris" <too-much-spam@.makes-you-fat.com> wrote in message
news:yL%fg.1320$W93.238@.newsfe6-win.ntli.net...
Seems my hosting company do not give me the relevent permissions to run DBCC
OPENTRAN.
---
I am using the free version of SPAMfighter for private users.
It has removed 5334 spam emails to date.
Paying users do not have this message in their emails.
Get the free SPAMfighter here: http://www.spamfighter.com/len
Recently we have experienced a problem with our transaction log file filling
up. The application is a moderatley busy e-commerce site. The database is
set to simple recovery mode. The transaction log space is 100mb. This is
filling up on a daily basis.
I have two questions:
- What sort of things should I be looking for to solve this? (My hosting
company have suggested transactions are not completing) ?
- Is there a command (Like sp_helpdb) that returns the trans log size?
Many Thanks,
Simon.
-
* Please reply to group for the benefit of all
* Found the answer to your own question? Post it!
* Get a useful reply to one of your posts?...post an answer to another one
* Search first, post later : http://www.google.co.uk/groups
* Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!Your hosting company may be right. Run DBCC OPENTRAN on your DB. If the
same SPID comes up with the same date/time, then you have an open
transaction. Only once that tran is committed or rolled back will you be
able to truncate the log.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Simon Harris" <too-much-spam@.makes-you-fat.com> wrote in message
news:gg_fg.1444$lQ.247@.newsfe3-gui.ntli.net...
Hi All,
Recently we have experienced a problem with our transaction log file filling
up. The application is a moderatley busy e-commerce site. The database is
set to simple recovery mode. The transaction log space is 100mb. This is
filling up on a daily basis.
I have two questions:
- What sort of things should I be looking for to solve this? (My hosting
company have suggested transactions are not completing) ?
- Is there a command (Like sp_helpdb) that returns the trans log size?
Many Thanks,
Simon.
-
* Please reply to group for the benefit of all
* Found the answer to your own question? Post it!
* Get a useful reply to one of your posts?...post an answer to another one
* Search first, post later : http://www.google.co.uk/groups
* Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!|||Xref: TK2MSFTNGP01.phx.gbl microsoft.public.sqlserver.server:436392
Thank you for your reply Tom.
When you say "If the same SPID comes up with the same date/time" what
SPID/Date Time should I be comparing to?
Am I correct in thinking that the transaction log is automatically truncated
following completion of a successful transaction? (When in Simple recovery
mode)
Can you define an 'open transaction'? What circumstances may cause this?
Do you have any hints/tips as to how I may diagnose what code/queries are
causing this?
Sorry for all the questions, thanks again.
Simon.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OJH2iqmhGHA.4044@.TK2MSFTNGP03.phx.gbl...
> Your hosting company may be right. Run DBCC OPENTRAN on your DB. If the
> same SPID comes up with the same date/time, then you have an open
> transaction. Only once that tran is committed or rolled back will you be
> able to truncate the log.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Simon Harris" <too-much-spam@.makes-you-fat.com> wrote in message
> news:gg_fg.1444$lQ.247@.newsfe3-gui.ntli.net...
> Hi All,
> Recently we have experienced a problem with our transaction log file
> filling
> up. The application is a moderatley busy e-commerce site. The database is
> set to simple recovery mode. The transaction log space is 100mb. This is
> filling up on a daily basis.
> I have two questions:
> - What sort of things should I be looking for to solve this? (My hosting
> company have suggested transactions are not completing) ?
> - Is there a command (Like sp_helpdb) that returns the trans log size?
> Many Thanks,
> Simon.
> --
> -
> * Please reply to group for the benefit of all
> * Found the answer to your own question? Post it!
> * Get a useful reply to one of your posts?...post an answer to another one
> * Search first, post later : http://www.google.co.uk/groups
> * Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!
>|||Thank you for your reply Tom.
When you say "If the same SPID comes up with the same date/time" what
SPID/Date Time should I be comparing to?
Am I correct in thinking that the transaction log is automatically truncated
following completion of a successful transaction? (When in Simple recovery
mode)
Can you define an 'open transaction'? What circumstances may cause this?
Do you have any hints/tips as to how I may diagnose what code/queries are
causing this?
Sorry for all the questions, thanks again.
Simon.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OJH2iqmhGHA.4044@.TK2MSFTNGP03.phx.gbl...
> Your hosting company may be right. Run DBCC OPENTRAN on your DB. If the
> same SPID comes up with the same date/time, then you have an open
> transaction. Only once that tran is committed or rolled back will you be
> able to truncate the log.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Simon Harris" <too-much-spam@.makes-you-fat.com> wrote in message
> news:gg_fg.1444$lQ.247@.newsfe3-gui.ntli.net...
> Hi All,
> Recently we have experienced a problem with our transaction log file
> filling
> up. The application is a moderatley busy e-commerce site. The database is
> set to simple recovery mode. The transaction log space is 100mb. This is
> filling up on a daily basis.
> I have two questions:
> - What sort of things should I be looking for to solve this? (My hosting
> company have suggested transactions are not completing) ?
> - Is there a command (Like sp_helpdb) that returns the trans log size?
> Many Thanks,
> Simon.
> --
> -
> * Please reply to group for the benefit of all
> * Found the answer to your own question? Post it!
> * Get a useful reply to one of your posts?...post an answer to another one
> * Search first, post later : http://www.google.co.uk/groups
> * Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!
>
---
I am using the free version of SPAMfighter for private users.
It has removed 5334 spam emails to date.
Paying users do not have this message in their emails.
Get the free SPAMfighter here: http://www.spamfighter.com/len|||Check out "DBCC OPENTRAN" in the BOL. The output it gives you the date/time
of the longest-running transaction for a given DB. The SPID (system process
ID). If the date/time never changes as you repeatedly run DBCC OPENTRAN,
then you have a long-running transaction. You could then run DBCC
INPUTBUFFER on that SPID to see what the last command was.
An explicit transaction begins with a BEGIN TRAN. None of the work that you
do from that point forward is made permanent until you issue a COMMIT TRAN
on that same connection. Once that occurs, the log can be truncated up to
that point. An implicit transaction is an INSERT, UPDATE or DELETE. Check
out the BOL for Transactions for more details.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Simon Harris" <too-much-spam@.makes-you-fat.com> wrote in message
news:r9%fg.1734$lQ.1657@.newsfe3-gui.ntli.net...
Thank you for your reply Tom.
When you say "If the same SPID comes up with the same date/time" what
SPID/Date Time should I be comparing to?
Am I correct in thinking that the transaction log is automatically truncated
following completion of a successful transaction? (When in Simple recovery
mode)
Can you define an 'open transaction'? What circumstances may cause this?
Do you have any hints/tips as to how I may diagnose what code/queries are
causing this?
Sorry for all the questions, thanks again.
Simon.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OJH2iqmhGHA.4044@.TK2MSFTNGP03.phx.gbl...
> Your hosting company may be right. Run DBCC OPENTRAN on your DB. If the
> same SPID comes up with the same date/time, then you have an open
> transaction. Only once that tran is committed or rolled back will you be
> able to truncate the log.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Simon Harris" <too-much-spam@.makes-you-fat.com> wrote in message
> news:gg_fg.1444$lQ.247@.newsfe3-gui.ntli.net...
> Hi All,
> Recently we have experienced a problem with our transaction log file
> filling
> up. The application is a moderatley busy e-commerce site. The database is
> set to simple recovery mode. The transaction log space is 100mb. This is
> filling up on a daily basis.
> I have two questions:
> - What sort of things should I be looking for to solve this? (My hosting
> company have suggested transactions are not completing) ?
> - Is there a command (Like sp_helpdb) that returns the trans log size?
> Many Thanks,
> Simon.
> --
> -
> * Please reply to group for the benefit of all
> * Found the answer to your own question? Post it!
> * Get a useful reply to one of your posts?...post an answer to another one
> * Search first, post later : http://www.google.co.uk/groups
> * Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!
>|||Seems my hosting company do not give me the relevent permissions to run DBCC
OPENTRAN.
---
I am using the free version of SPAMfighter for private users.
It has removed 5334 spam emails to date.
Paying users do not have this message in their emails.
Get the free SPAMfighter here: http://www.spamfighter.com/len|||Perhaps you can have their DBA run it.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Simon Harris" <too-much-spam@.makes-you-fat.com> wrote in message
news:yL%fg.1320$W93.238@.newsfe6-win.ntli.net...
Seems my hosting company do not give me the relevent permissions to run DBCC
OPENTRAN.
---
I am using the free version of SPAMfighter for private users.
It has removed 5334 spam emails to date.
Paying users do not have this message in their emails.
Get the free SPAMfighter here: http://www.spamfighter.com/len
Log File Filling Up
Hi All,
Recently we have experienced a problem with our transaction log file filling
up. The application is a moderatley busy e-commerce site. The database is
set to simple recovery mode. The transaction log space is 100mb. This is
filling up on a daily basis.
I have two questions:
- What sort of things should I be looking for to solve this? (My hosting
company have suggested transactions are not completing) ?
- Is there a command (Like sp_helpdb) that returns the trans log size?
Many Thanks,
Simon.
--
-
* Please reply to group for the benefit of all
* Found the answer to your own question? Post it!
* Get a useful reply to one of your posts?...post an answer to another one
* Search first, post later : http://www.google.co.uk/groups
* Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!Your hosting company may be right. Run DBCC OPENTRAN on your DB. If the
same SPID comes up with the same date/time, then you have an open
transaction. Only once that tran is committed or rolled back will you be
able to truncate the log.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Simon Harris" <too-much-spam@.makes-you-fat.com> wrote in message
news:gg_fg.1444$lQ.247@.newsfe3-gui.ntli.net...
Hi All,
Recently we have experienced a problem with our transaction log file filling
up. The application is a moderatley busy e-commerce site. The database is
set to simple recovery mode. The transaction log space is 100mb. This is
filling up on a daily basis.
I have two questions:
- What sort of things should I be looking for to solve this? (My hosting
company have suggested transactions are not completing) ?
- Is there a command (Like sp_helpdb) that returns the trans log size?
Many Thanks,
Simon.
--
-
* Please reply to group for the benefit of all
* Found the answer to your own question? Post it!
* Get a useful reply to one of your posts?...post an answer to another one
* Search first, post later : http://www.google.co.uk/groups
* Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!|||Thank you for your reply Tom.
When you say "If the same SPID comes up with the same date/time" what
SPID/Date Time should I be comparing to?
Am I correct in thinking that the transaction log is automatically truncated
following completion of a successful transaction? (When in Simple recovery
mode)
Can you define an 'open transaction'? What circumstances may cause this?
Do you have any hints/tips as to how I may diagnose what code/queries are
causing this?
Sorry for all the questions, thanks again.
Simon.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OJH2iqmhGHA.4044@.TK2MSFTNGP03.phx.gbl...
> Your hosting company may be right. Run DBCC OPENTRAN on your DB. If the
> same SPID comes up with the same date/time, then you have an open
> transaction. Only once that tran is committed or rolled back will you be
> able to truncate the log.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Simon Harris" <too-much-spam@.makes-you-fat.com> wrote in message
> news:gg_fg.1444$lQ.247@.newsfe3-gui.ntli.net...
> Hi All,
> Recently we have experienced a problem with our transaction log file
> filling
> up. The application is a moderatley busy e-commerce site. The database is
> set to simple recovery mode. The transaction log space is 100mb. This is
> filling up on a daily basis.
> I have two questions:
> - What sort of things should I be looking for to solve this? (My hosting
> company have suggested transactions are not completing) ?
> - Is there a command (Like sp_helpdb) that returns the trans log size?
> Many Thanks,
> Simon.
> --
> -
> * Please reply to group for the benefit of all
> * Found the answer to your own question? Post it!
> * Get a useful reply to one of your posts?...post an answer to another one
> * Search first, post later : http://www.google.co.uk/groups
> * Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!
>|||Thank you for your reply Tom.
When you say "If the same SPID comes up with the same date/time" what
SPID/Date Time should I be comparing to?
Am I correct in thinking that the transaction log is automatically truncated
following completion of a successful transaction? (When in Simple recovery
mode)
Can you define an 'open transaction'? What circumstances may cause this?
Do you have any hints/tips as to how I may diagnose what code/queries are
causing this?
Sorry for all the questions, thanks again.
Simon.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OJH2iqmhGHA.4044@.TK2MSFTNGP03.phx.gbl...
> Your hosting company may be right. Run DBCC OPENTRAN on your DB. If the
> same SPID comes up with the same date/time, then you have an open
> transaction. Only once that tran is committed or rolled back will you be
> able to truncate the log.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Simon Harris" <too-much-spam@.makes-you-fat.com> wrote in message
> news:gg_fg.1444$lQ.247@.newsfe3-gui.ntli.net...
> Hi All,
> Recently we have experienced a problem with our transaction log file
> filling
> up. The application is a moderatley busy e-commerce site. The database is
> set to simple recovery mode. The transaction log space is 100mb. This is
> filling up on a daily basis.
> I have two questions:
> - What sort of things should I be looking for to solve this? (My hosting
> company have suggested transactions are not completing) ?
> - Is there a command (Like sp_helpdb) that returns the trans log size?
> Many Thanks,
> Simon.
> --
> -
> * Please reply to group for the benefit of all
> * Found the answer to your own question? Post it!
> * Get a useful reply to one of your posts?...post an answer to another one
> * Search first, post later : http://www.google.co.uk/groups
> * Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!
>
---
I am using the free version of SPAMfighter for private users.
It has removed 5334 spam emails to date.
Paying users do not have this message in their emails.
Get the free SPAMfighter here: http://www.spamfighter.com/len|||Check out "DBCC OPENTRAN" in the BOL. The output it gives you the date/time
of the longest-running transaction for a given DB. The SPID (system process
ID). If the date/time never changes as you repeatedly run DBCC OPENTRAN,
then you have a long-running transaction. You could then run DBCC
INPUTBUFFER on that SPID to see what the last command was.
An explicit transaction begins with a BEGIN TRAN. None of the work that you
do from that point forward is made permanent until you issue a COMMIT TRAN
on that same connection. Once that occurs, the log can be truncated up to
that point. An implicit transaction is an INSERT, UPDATE or DELETE. Check
out the BOL for Transactions for more details.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Simon Harris" <too-much-spam@.makes-you-fat.com> wrote in message
news:r9%fg.1734$lQ.1657@.newsfe3-gui.ntli.net...
Thank you for your reply Tom.
When you say "If the same SPID comes up with the same date/time" what
SPID/Date Time should I be comparing to?
Am I correct in thinking that the transaction log is automatically truncated
following completion of a successful transaction? (When in Simple recovery
mode)
Can you define an 'open transaction'? What circumstances may cause this?
Do you have any hints/tips as to how I may diagnose what code/queries are
causing this?
Sorry for all the questions, thanks again.
Simon.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OJH2iqmhGHA.4044@.TK2MSFTNGP03.phx.gbl...
> Your hosting company may be right. Run DBCC OPENTRAN on your DB. If the
> same SPID comes up with the same date/time, then you have an open
> transaction. Only once that tran is committed or rolled back will you be
> able to truncate the log.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Simon Harris" <too-much-spam@.makes-you-fat.com> wrote in message
> news:gg_fg.1444$lQ.247@.newsfe3-gui.ntli.net...
> Hi All,
> Recently we have experienced a problem with our transaction log file
> filling
> up. The application is a moderatley busy e-commerce site. The database is
> set to simple recovery mode. The transaction log space is 100mb. This is
> filling up on a daily basis.
> I have two questions:
> - What sort of things should I be looking for to solve this? (My hosting
> company have suggested transactions are not completing) ?
> - Is there a command (Like sp_helpdb) that returns the trans log size?
> Many Thanks,
> Simon.
> --
> -
> * Please reply to group for the benefit of all
> * Found the answer to your own question? Post it!
> * Get a useful reply to one of your posts?...post an answer to another one
> * Search first, post later : http://www.google.co.uk/groups
> * Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!
>|||Seems my hosting company do not give me the relevent permissions to run DBCC
OPENTRAN.
---
I am using the free version of SPAMfighter for private users.
It has removed 5334 spam emails to date.
Paying users do not have this message in their emails.
Get the free SPAMfighter here: http://www.spamfighter.com/len|||Perhaps you can have their DBA run it.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Simon Harris" <too-much-spam@.makes-you-fat.com> wrote in message
news:yL%fg.1320$W93.238@.newsfe6-win.ntli.net...
Seems my hosting company do not give me the relevent permissions to run DBCC
OPENTRAN.
---
I am using the free version of SPAMfighter for private users.
It has removed 5334 spam emails to date.
Paying users do not have this message in their emails.
Get the free SPAMfighter here: http://www.spamfighter.com/len
Recently we have experienced a problem with our transaction log file filling
up. The application is a moderatley busy e-commerce site. The database is
set to simple recovery mode. The transaction log space is 100mb. This is
filling up on a daily basis.
I have two questions:
- What sort of things should I be looking for to solve this? (My hosting
company have suggested transactions are not completing) ?
- Is there a command (Like sp_helpdb) that returns the trans log size?
Many Thanks,
Simon.
--
-
* Please reply to group for the benefit of all
* Found the answer to your own question? Post it!
* Get a useful reply to one of your posts?...post an answer to another one
* Search first, post later : http://www.google.co.uk/groups
* Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!Your hosting company may be right. Run DBCC OPENTRAN on your DB. If the
same SPID comes up with the same date/time, then you have an open
transaction. Only once that tran is committed or rolled back will you be
able to truncate the log.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Simon Harris" <too-much-spam@.makes-you-fat.com> wrote in message
news:gg_fg.1444$lQ.247@.newsfe3-gui.ntli.net...
Hi All,
Recently we have experienced a problem with our transaction log file filling
up. The application is a moderatley busy e-commerce site. The database is
set to simple recovery mode. The transaction log space is 100mb. This is
filling up on a daily basis.
I have two questions:
- What sort of things should I be looking for to solve this? (My hosting
company have suggested transactions are not completing) ?
- Is there a command (Like sp_helpdb) that returns the trans log size?
Many Thanks,
Simon.
--
-
* Please reply to group for the benefit of all
* Found the answer to your own question? Post it!
* Get a useful reply to one of your posts?...post an answer to another one
* Search first, post later : http://www.google.co.uk/groups
* Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!|||Thank you for your reply Tom.
When you say "If the same SPID comes up with the same date/time" what
SPID/Date Time should I be comparing to?
Am I correct in thinking that the transaction log is automatically truncated
following completion of a successful transaction? (When in Simple recovery
mode)
Can you define an 'open transaction'? What circumstances may cause this?
Do you have any hints/tips as to how I may diagnose what code/queries are
causing this?
Sorry for all the questions, thanks again.
Simon.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OJH2iqmhGHA.4044@.TK2MSFTNGP03.phx.gbl...
> Your hosting company may be right. Run DBCC OPENTRAN on your DB. If the
> same SPID comes up with the same date/time, then you have an open
> transaction. Only once that tran is committed or rolled back will you be
> able to truncate the log.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Simon Harris" <too-much-spam@.makes-you-fat.com> wrote in message
> news:gg_fg.1444$lQ.247@.newsfe3-gui.ntli.net...
> Hi All,
> Recently we have experienced a problem with our transaction log file
> filling
> up. The application is a moderatley busy e-commerce site. The database is
> set to simple recovery mode. The transaction log space is 100mb. This is
> filling up on a daily basis.
> I have two questions:
> - What sort of things should I be looking for to solve this? (My hosting
> company have suggested transactions are not completing) ?
> - Is there a command (Like sp_helpdb) that returns the trans log size?
> Many Thanks,
> Simon.
> --
> -
> * Please reply to group for the benefit of all
> * Found the answer to your own question? Post it!
> * Get a useful reply to one of your posts?...post an answer to another one
> * Search first, post later : http://www.google.co.uk/groups
> * Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!
>|||Thank you for your reply Tom.
When you say "If the same SPID comes up with the same date/time" what
SPID/Date Time should I be comparing to?
Am I correct in thinking that the transaction log is automatically truncated
following completion of a successful transaction? (When in Simple recovery
mode)
Can you define an 'open transaction'? What circumstances may cause this?
Do you have any hints/tips as to how I may diagnose what code/queries are
causing this?
Sorry for all the questions, thanks again.
Simon.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OJH2iqmhGHA.4044@.TK2MSFTNGP03.phx.gbl...
> Your hosting company may be right. Run DBCC OPENTRAN on your DB. If the
> same SPID comes up with the same date/time, then you have an open
> transaction. Only once that tran is committed or rolled back will you be
> able to truncate the log.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Simon Harris" <too-much-spam@.makes-you-fat.com> wrote in message
> news:gg_fg.1444$lQ.247@.newsfe3-gui.ntli.net...
> Hi All,
> Recently we have experienced a problem with our transaction log file
> filling
> up. The application is a moderatley busy e-commerce site. The database is
> set to simple recovery mode. The transaction log space is 100mb. This is
> filling up on a daily basis.
> I have two questions:
> - What sort of things should I be looking for to solve this? (My hosting
> company have suggested transactions are not completing) ?
> - Is there a command (Like sp_helpdb) that returns the trans log size?
> Many Thanks,
> Simon.
> --
> -
> * Please reply to group for the benefit of all
> * Found the answer to your own question? Post it!
> * Get a useful reply to one of your posts?...post an answer to another one
> * Search first, post later : http://www.google.co.uk/groups
> * Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!
>
---
I am using the free version of SPAMfighter for private users.
It has removed 5334 spam emails to date.
Paying users do not have this message in their emails.
Get the free SPAMfighter here: http://www.spamfighter.com/len|||Check out "DBCC OPENTRAN" in the BOL. The output it gives you the date/time
of the longest-running transaction for a given DB. The SPID (system process
ID). If the date/time never changes as you repeatedly run DBCC OPENTRAN,
then you have a long-running transaction. You could then run DBCC
INPUTBUFFER on that SPID to see what the last command was.
An explicit transaction begins with a BEGIN TRAN. None of the work that you
do from that point forward is made permanent until you issue a COMMIT TRAN
on that same connection. Once that occurs, the log can be truncated up to
that point. An implicit transaction is an INSERT, UPDATE or DELETE. Check
out the BOL for Transactions for more details.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Simon Harris" <too-much-spam@.makes-you-fat.com> wrote in message
news:r9%fg.1734$lQ.1657@.newsfe3-gui.ntli.net...
Thank you for your reply Tom.
When you say "If the same SPID comes up with the same date/time" what
SPID/Date Time should I be comparing to?
Am I correct in thinking that the transaction log is automatically truncated
following completion of a successful transaction? (When in Simple recovery
mode)
Can you define an 'open transaction'? What circumstances may cause this?
Do you have any hints/tips as to how I may diagnose what code/queries are
causing this?
Sorry for all the questions, thanks again.
Simon.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OJH2iqmhGHA.4044@.TK2MSFTNGP03.phx.gbl...
> Your hosting company may be right. Run DBCC OPENTRAN on your DB. If the
> same SPID comes up with the same date/time, then you have an open
> transaction. Only once that tran is committed or rolled back will you be
> able to truncate the log.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Simon Harris" <too-much-spam@.makes-you-fat.com> wrote in message
> news:gg_fg.1444$lQ.247@.newsfe3-gui.ntli.net...
> Hi All,
> Recently we have experienced a problem with our transaction log file
> filling
> up. The application is a moderatley busy e-commerce site. The database is
> set to simple recovery mode. The transaction log space is 100mb. This is
> filling up on a daily basis.
> I have two questions:
> - What sort of things should I be looking for to solve this? (My hosting
> company have suggested transactions are not completing) ?
> - Is there a command (Like sp_helpdb) that returns the trans log size?
> Many Thanks,
> Simon.
> --
> -
> * Please reply to group for the benefit of all
> * Found the answer to your own question? Post it!
> * Get a useful reply to one of your posts?...post an answer to another one
> * Search first, post later : http://www.google.co.uk/groups
> * Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!
>|||Seems my hosting company do not give me the relevent permissions to run DBCC
OPENTRAN.
---
I am using the free version of SPAMfighter for private users.
It has removed 5334 spam emails to date.
Paying users do not have this message in their emails.
Get the free SPAMfighter here: http://www.spamfighter.com/len|||Perhaps you can have their DBA run it.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Simon Harris" <too-much-spam@.makes-you-fat.com> wrote in message
news:yL%fg.1320$W93.238@.newsfe6-win.ntli.net...
Seems my hosting company do not give me the relevent permissions to run DBCC
OPENTRAN.
---
I am using the free version of SPAMfighter for private users.
It has removed 5334 spam emails to date.
Paying users do not have this message in their emails.
Get the free SPAMfighter here: http://www.spamfighter.com/len
Log file becomes ungrowable every morning.
Have a site with a wierd problem. A 3rd party application is accessing a SQL
2000 database. Each morning, the event log contains a heap of 17055:5144
errors, "Autogrow of file 'data_log" in Database 'data' cancelled or timed
out in 30547 ms. Use ALTER DATABASE to set a smaller FILEGROWTH or to set a
new size" Users report errors that seem directly related to this problem.
The solution is to cycle the SQL service, this will see things work again
until the next morning. I have seen this error before, but there has always
been a good reason. In this case, I cannot find one. There is ample disk
space, and the log is set to grow by 10% as needed. AV is excluded for this
data, and there are no quotas in place. CHKDSK reports no errors, and
fragmentation is not excessive. Any ideas where to look next?
Machine is running SBS-2003, SQL2000 & everything else is fully updated as
of a week ago.
Thanks.On 23.04.2007 08:12, Mal Osborne wrote:[vbcol=seagreen]
> Have a site with a wierd problem. A 3rd party application is accessing a S
QL
> 2000 database. Each morning, the event log contains a heap of 17055:5144
> errors, "Autogrow of file 'data_log" in Database 'data' cancelled or timed
> out in 30547 ms. Use ALTER DATABASE to set a smaller FILEGROWTH or to set
a
> new size" Users report errors that seem directly related to this problem.
> The solution is to cycle the SQL service, this will see things work again
> until the next morning. I have seen this error before, but there has alwa
ys
> been a good reason. In this case, I cannot find one. There is ample disk
> space, and the log is set to grow by 10% as needed. AV is excluded for this[/vbcol
]
Well, then change it to a fixed size. With an increase of 10% every
resize needs more disk space - and thus takes longer or runs the risk of
running out of disk space. Btw, did you check space on the device? Do
you backup or shrink TX logs?
robert|||"Mal Osborne" <Mal Osborne@.discussions.microsoft.com> wrote in message
news:37B56334-A6E6-4628-8BFF-E51BE4F1C7CD@.microsoft.com...
> Have a site with a wierd problem. A 3rd party application is accessing a
> SQL
> 2000 database. Each morning, the event log contains a heap of 17055:5144
> errors, "Autogrow of file 'data_log" in Database 'data' cancelled or timed
> out in 30547 ms. Use ALTER DATABASE to set a smaller FILEGROWTH or to set
> a
> new size" Users report errors that seem directly related to this problem.
> The solution is to cycle the SQL service, this will see things work again
> until the next morning. I have seen this error before, but there has
> always
> been a good reason. In this case, I cannot find one. There is ample disk
> space, and the log is set to grow by 10% as needed. AV is excluded for
> this
> data, and there are no quotas in place. CHKDSK reports no errors, and
> fragmentation is not excessive. Any ideas where to look next?
Not sure why it's not permitted to grow.
But in any event, two questions:
Are you doing any form of index rebuild over night?
Are you doing transaction log backups on a regular basis?
> Machine is running SBS-2003, SQL2000 & everything else is fully updated as
> of a week ago.
> Thanks.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
2000 database. Each morning, the event log contains a heap of 17055:5144
errors, "Autogrow of file 'data_log" in Database 'data' cancelled or timed
out in 30547 ms. Use ALTER DATABASE to set a smaller FILEGROWTH or to set a
new size" Users report errors that seem directly related to this problem.
The solution is to cycle the SQL service, this will see things work again
until the next morning. I have seen this error before, but there has always
been a good reason. In this case, I cannot find one. There is ample disk
space, and the log is set to grow by 10% as needed. AV is excluded for this
data, and there are no quotas in place. CHKDSK reports no errors, and
fragmentation is not excessive. Any ideas where to look next?
Machine is running SBS-2003, SQL2000 & everything else is fully updated as
of a week ago.
Thanks.On 23.04.2007 08:12, Mal Osborne wrote:[vbcol=seagreen]
> Have a site with a wierd problem. A 3rd party application is accessing a S
QL
> 2000 database. Each morning, the event log contains a heap of 17055:5144
> errors, "Autogrow of file 'data_log" in Database 'data' cancelled or timed
> out in 30547 ms. Use ALTER DATABASE to set a smaller FILEGROWTH or to set
a
> new size" Users report errors that seem directly related to this problem.
> The solution is to cycle the SQL service, this will see things work again
> until the next morning. I have seen this error before, but there has alwa
ys
> been a good reason. In this case, I cannot find one. There is ample disk
> space, and the log is set to grow by 10% as needed. AV is excluded for this[/vbcol
]
Well, then change it to a fixed size. With an increase of 10% every
resize needs more disk space - and thus takes longer or runs the risk of
running out of disk space. Btw, did you check space on the device? Do
you backup or shrink TX logs?
robert|||"Mal Osborne" <Mal Osborne@.discussions.microsoft.com> wrote in message
news:37B56334-A6E6-4628-8BFF-E51BE4F1C7CD@.microsoft.com...
> Have a site with a wierd problem. A 3rd party application is accessing a
> SQL
> 2000 database. Each morning, the event log contains a heap of 17055:5144
> errors, "Autogrow of file 'data_log" in Database 'data' cancelled or timed
> out in 30547 ms. Use ALTER DATABASE to set a smaller FILEGROWTH or to set
> a
> new size" Users report errors that seem directly related to this problem.
> The solution is to cycle the SQL service, this will see things work again
> until the next morning. I have seen this error before, but there has
> always
> been a good reason. In this case, I cannot find one. There is ample disk
> space, and the log is set to grow by 10% as needed. AV is excluded for
> this
> data, and there are no quotas in place. CHKDSK reports no errors, and
> fragmentation is not excessive. Any ideas where to look next?
Not sure why it's not permitted to grow.
But in any event, two questions:
Are you doing any form of index rebuild over night?
Are you doing transaction log backups on a regular basis?
> Machine is running SBS-2003, SQL2000 & everything else is fully updated as
> of a week ago.
> Thanks.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
Log file becomes ungrowable every morning.
Have a site with a wierd problem. A 3rd party application is accessing a SQL
2000 database. Each morning, the event log contains a heap of 17055:5144
errors, "Autogrow of file 'data_log" in Database 'data' cancelled or timed
out in 30547 ms. Use ALTER DATABASE to set a smaller FILEGROWTH or to set a
new size" Users report errors that seem directly related to this problem.
The solution is to cycle the SQL service, this will see things work again
until the next morning. I have seen this error before, but there has always
been a good reason. In this case, I cannot find one. There is ample disk
space, and the log is set to grow by 10% as needed. AV is excluded for this
data, and there are no quotas in place. CHKDSK reports no errors, and
fragmentation is not excessive. Any ideas where to look next?
Machine is running SBS-2003, SQL2000 & everything else is fully updated as
of a week ago.
Thanks.
On 23.04.2007 08:12, Mal Osborne wrote:
> Have a site with a wierd problem. A 3rd party application is accessing a SQL
> 2000 database. Each morning, the event log contains a heap of 17055:5144
> errors, "Autogrow of file 'data_log" in Database 'data' cancelled or timed
> out in 30547 ms. Use ALTER DATABASE to set a smaller FILEGROWTH or to set a
> new size" Users report errors that seem directly related to this problem.
> The solution is to cycle the SQL service, this will see things work again
> until the next morning. I have seen this error before, but there has always
> been a good reason. In this case, I cannot find one. There is ample disk
> space, and the log is set to grow by 10% as needed. AV is excluded for this
Well, then change it to a fixed size. With an increase of 10% every
resize needs more disk space - and thus takes longer or runs the risk of
running out of disk space. Btw, did you check space on the device? Do
you backup or shrink TX logs?
robert
|||"Mal Osborne" <Mal Osborne@.discussions.microsoft.com> wrote in message
news:37B56334-A6E6-4628-8BFF-E51BE4F1C7CD@.microsoft.com...
> Have a site with a wierd problem. A 3rd party application is accessing a
> SQL
> 2000 database. Each morning, the event log contains a heap of 17055:5144
> errors, "Autogrow of file 'data_log" in Database 'data' cancelled or timed
> out in 30547 ms. Use ALTER DATABASE to set a smaller FILEGROWTH or to set
> a
> new size" Users report errors that seem directly related to this problem.
> The solution is to cycle the SQL service, this will see things work again
> until the next morning. I have seen this error before, but there has
> always
> been a good reason. In this case, I cannot find one. There is ample disk
> space, and the log is set to grow by 10% as needed. AV is excluded for
> this
> data, and there are no quotas in place. CHKDSK reports no errors, and
> fragmentation is not excessive. Any ideas where to look next?
Not sure why it's not permitted to grow.
But in any event, two questions:
Are you doing any form of index rebuild over night?
Are you doing transaction log backups on a regular basis?
> Machine is running SBS-2003, SQL2000 & everything else is fully updated as
> of a week ago.
> Thanks.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
2000 database. Each morning, the event log contains a heap of 17055:5144
errors, "Autogrow of file 'data_log" in Database 'data' cancelled or timed
out in 30547 ms. Use ALTER DATABASE to set a smaller FILEGROWTH or to set a
new size" Users report errors that seem directly related to this problem.
The solution is to cycle the SQL service, this will see things work again
until the next morning. I have seen this error before, but there has always
been a good reason. In this case, I cannot find one. There is ample disk
space, and the log is set to grow by 10% as needed. AV is excluded for this
data, and there are no quotas in place. CHKDSK reports no errors, and
fragmentation is not excessive. Any ideas where to look next?
Machine is running SBS-2003, SQL2000 & everything else is fully updated as
of a week ago.
Thanks.
On 23.04.2007 08:12, Mal Osborne wrote:
> Have a site with a wierd problem. A 3rd party application is accessing a SQL
> 2000 database. Each morning, the event log contains a heap of 17055:5144
> errors, "Autogrow of file 'data_log" in Database 'data' cancelled or timed
> out in 30547 ms. Use ALTER DATABASE to set a smaller FILEGROWTH or to set a
> new size" Users report errors that seem directly related to this problem.
> The solution is to cycle the SQL service, this will see things work again
> until the next morning. I have seen this error before, but there has always
> been a good reason. In this case, I cannot find one. There is ample disk
> space, and the log is set to grow by 10% as needed. AV is excluded for this
Well, then change it to a fixed size. With an increase of 10% every
resize needs more disk space - and thus takes longer or runs the risk of
running out of disk space. Btw, did you check space on the device? Do
you backup or shrink TX logs?
robert
|||"Mal Osborne" <Mal Osborne@.discussions.microsoft.com> wrote in message
news:37B56334-A6E6-4628-8BFF-E51BE4F1C7CD@.microsoft.com...
> Have a site with a wierd problem. A 3rd party application is accessing a
> SQL
> 2000 database. Each morning, the event log contains a heap of 17055:5144
> errors, "Autogrow of file 'data_log" in Database 'data' cancelled or timed
> out in 30547 ms. Use ALTER DATABASE to set a smaller FILEGROWTH or to set
> a
> new size" Users report errors that seem directly related to this problem.
> The solution is to cycle the SQL service, this will see things work again
> until the next morning. I have seen this error before, but there has
> always
> been a good reason. In this case, I cannot find one. There is ample disk
> space, and the log is set to grow by 10% as needed. AV is excluded for
> this
> data, and there are no quotas in place. CHKDSK reports no errors, and
> fragmentation is not excessive. Any ideas where to look next?
Not sure why it's not permitted to grow.
But in any event, two questions:
Are you doing any form of index rebuild over night?
Are you doing transaction log backups on a regular basis?
> Machine is running SBS-2003, SQL2000 & everything else is fully updated as
> of a week ago.
> Thanks.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
Log file becomes ungrowable every morning.
Have a site with a wierd problem. A 3rd party application is accessing a SQL
2000 database. Each morning, the event log contains a heap of 17055:5144
errors, "Autogrow of file 'data_log" in Database 'data' cancelled or timed
out in 30547 ms. Use ALTER DATABASE to set a smaller FILEGROWTH or to set a
new size" Users report errors that seem directly related to this problem.
The solution is to cycle the SQL service, this will see things work again
until the next morning. I have seen this error before, but there has always
been a good reason. In this case, I cannot find one. There is ample disk
space, and the log is set to grow by 10% as needed. AV is excluded for this
data, and there are no quotas in place. CHKDSK reports no errors, and
fragmentation is not excessive. Any ideas where to look next?
Machine is running SBS-2003, SQL2000 & everything else is fully updated as
of a week ago.
Thanks.On 23.04.2007 08:12, Mal Osborne wrote:
> Have a site with a wierd problem. A 3rd party application is accessing a SQL
> 2000 database. Each morning, the event log contains a heap of 17055:5144
> errors, "Autogrow of file 'data_log" in Database 'data' cancelled or timed
> out in 30547 ms. Use ALTER DATABASE to set a smaller FILEGROWTH or to set a
> new size" Users report errors that seem directly related to this problem.
> The solution is to cycle the SQL service, this will see things work again
> until the next morning. I have seen this error before, but there has always
> been a good reason. In this case, I cannot find one. There is ample disk
> space, and the log is set to grow by 10% as needed. AV is excluded for this
Well, then change it to a fixed size. With an increase of 10% every
resize needs more disk space - and thus takes longer or runs the risk of
running out of disk space. Btw, did you check space on the device? Do
you backup or shrink TX logs?
robert|||"Mal Osborne" <Mal Osborne@.discussions.microsoft.com> wrote in message
news:37B56334-A6E6-4628-8BFF-E51BE4F1C7CD@.microsoft.com...
> Have a site with a wierd problem. A 3rd party application is accessing a
> SQL
> 2000 database. Each morning, the event log contains a heap of 17055:5144
> errors, "Autogrow of file 'data_log" in Database 'data' cancelled or timed
> out in 30547 ms. Use ALTER DATABASE to set a smaller FILEGROWTH or to set
> a
> new size" Users report errors that seem directly related to this problem.
> The solution is to cycle the SQL service, this will see things work again
> until the next morning. I have seen this error before, but there has
> always
> been a good reason. In this case, I cannot find one. There is ample disk
> space, and the log is set to grow by 10% as needed. AV is excluded for
> this
> data, and there are no quotas in place. CHKDSK reports no errors, and
> fragmentation is not excessive. Any ideas where to look next?
Not sure why it's not permitted to grow.
But in any event, two questions:
Are you doing any form of index rebuild over night?
Are you doing transaction log backups on a regular basis?
> Machine is running SBS-2003, SQL2000 & everything else is fully updated as
> of a week ago.
> Thanks.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
2000 database. Each morning, the event log contains a heap of 17055:5144
errors, "Autogrow of file 'data_log" in Database 'data' cancelled or timed
out in 30547 ms. Use ALTER DATABASE to set a smaller FILEGROWTH or to set a
new size" Users report errors that seem directly related to this problem.
The solution is to cycle the SQL service, this will see things work again
until the next morning. I have seen this error before, but there has always
been a good reason. In this case, I cannot find one. There is ample disk
space, and the log is set to grow by 10% as needed. AV is excluded for this
data, and there are no quotas in place. CHKDSK reports no errors, and
fragmentation is not excessive. Any ideas where to look next?
Machine is running SBS-2003, SQL2000 & everything else is fully updated as
of a week ago.
Thanks.On 23.04.2007 08:12, Mal Osborne wrote:
> Have a site with a wierd problem. A 3rd party application is accessing a SQL
> 2000 database. Each morning, the event log contains a heap of 17055:5144
> errors, "Autogrow of file 'data_log" in Database 'data' cancelled or timed
> out in 30547 ms. Use ALTER DATABASE to set a smaller FILEGROWTH or to set a
> new size" Users report errors that seem directly related to this problem.
> The solution is to cycle the SQL service, this will see things work again
> until the next morning. I have seen this error before, but there has always
> been a good reason. In this case, I cannot find one. There is ample disk
> space, and the log is set to grow by 10% as needed. AV is excluded for this
Well, then change it to a fixed size. With an increase of 10% every
resize needs more disk space - and thus takes longer or runs the risk of
running out of disk space. Btw, did you check space on the device? Do
you backup or shrink TX logs?
robert|||"Mal Osborne" <Mal Osborne@.discussions.microsoft.com> wrote in message
news:37B56334-A6E6-4628-8BFF-E51BE4F1C7CD@.microsoft.com...
> Have a site with a wierd problem. A 3rd party application is accessing a
> SQL
> 2000 database. Each morning, the event log contains a heap of 17055:5144
> errors, "Autogrow of file 'data_log" in Database 'data' cancelled or timed
> out in 30547 ms. Use ALTER DATABASE to set a smaller FILEGROWTH or to set
> a
> new size" Users report errors that seem directly related to this problem.
> The solution is to cycle the SQL service, this will see things work again
> until the next morning. I have seen this error before, but there has
> always
> been a good reason. In this case, I cannot find one. There is ample disk
> space, and the log is set to grow by 10% as needed. AV is excluded for
> this
> data, and there are no quotas in place. CHKDSK reports no errors, and
> fragmentation is not excessive. Any ideas where to look next?
Not sure why it's not permitted to grow.
But in any event, two questions:
Are you doing any form of index rebuild over night?
Are you doing transaction log backups on a regular basis?
> Machine is running SBS-2003, SQL2000 & everything else is fully updated as
> of a week ago.
> Thanks.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
Log file auto shrink question
Hi, I am trying to automate shrinking the transaction log file on SQL server.
Every so often we get errors with our application using SQL server, in which I resolve by running the backup log and shrink log commands. However, recently I got the error: Could not allocate space for object 'table_name' in database 'database_name' because the 'Primary' file group is full. To resolve this I had to create another transaction log and then run the backup log and shrink log commands.
I know need to automate the process of shrinking the log file. I have checked and the Auto Shrink checkbox is ticked but these errors still occur.
How can I delete the additional log file I created and automate this task of shrinking the log file within SQL server? Any help would be appreciated...ThanksThe error mentioned does not refer to transaction log, but rather to data device of your database. Usually it's caused by either BULK INSERT/BCP...IN or an INSERT/UPDATE where the amount of resulting data exceeds the growth capacity of the database. These operations also affect the transaction log but the error would be different if that was the case. There are multiple sources on the net with similar approach. You can check here (http://www.codeproject.com/database/ShrinkingSQLServerTransLo.asp) for a fancy SQL-DMO version of it. For a known technique to handle shrinking of transaction log files using T-SQL go to this (http://dbforums.com/t515230.html) post.
Every so often we get errors with our application using SQL server, in which I resolve by running the backup log and shrink log commands. However, recently I got the error: Could not allocate space for object 'table_name' in database 'database_name' because the 'Primary' file group is full. To resolve this I had to create another transaction log and then run the backup log and shrink log commands.
I know need to automate the process of shrinking the log file. I have checked and the Auto Shrink checkbox is ticked but these errors still occur.
How can I delete the additional log file I created and automate this task of shrinking the log file within SQL server? Any help would be appreciated...ThanksThe error mentioned does not refer to transaction log, but rather to data device of your database. Usually it's caused by either BULK INSERT/BCP...IN or an INSERT/UPDATE where the amount of resulting data exceeds the growth capacity of the database. These operations also affect the transaction log but the error would be different if that was the case. There are multiple sources on the net with similar approach. You can check here (http://www.codeproject.com/database/ShrinkingSQLServerTransLo.asp) for a fancy SQL-DMO version of it. For a known technique to handle shrinking of transaction log files using T-SQL go to this (http://dbforums.com/t515230.html) post.
Friday, February 24, 2012
Log Backup Job works, but reports failure.
All the transaction logs are created, but the Application log entry below
appears, and the job shows as having failed.
Any ideas, anyone?
Thanks,
Jim
Event Type: Warning
Event Source: SQLSERVERAGENT
Event Category: Job Engine
Event ID: 208
Date: 8/2/2005
Time: 9:47:24 AM
User: N/A
Computer: GENOME-MARS
Description:
SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
'DB Maintenance Plan 1'' (0xAF8C5DECB6A71142915AA55ED294E0B4) - Status:
Failed - Invoked on: 2005-08-02 09:07:42 - Message: The job failed. The Job
was invoked by User UAB\jmoon. The last step to run was step 1 (Step 1).
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
Start Enterprise Mangler. Drill down to Management | Database Maintenance
Plans. Right click and select "Maintenance Plan History...". Set the
filters to isolate the plan that failed. Select the entry for the plan and
the failure date, click the "Properties..." button to see the details of why
it failed.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Jim" <please.reply@.group> wrote in message
news:upRvnl4lFHA.3656@.TK2MSFTNGP09.phx.gbl...
> All the transaction logs are created, but the Application log entry below
> appears, and the job shows as having failed.
> Any ideas, anyone?
> Thanks,
> Jim
> --
> Event Type: Warning
> Event Source: SQLSERVERAGENT
> Event Category: Job Engine
> Event ID: 208
> Date: 8/2/2005
> Time: 9:47:24 AM
> User: N/A
> Computer: GENOME-MARS
> Description:
> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance
> Plan 'DB Maintenance Plan 1'' (0xAF8C5DECB6A71142915AA55ED294E0B4) -
> Status: Failed - Invoked on: 2005-08-02 09:07:42 - Message: The job
> failed. The Job was invoked by User UAB\jmoon. The last step to run was
> step 1 (Step 1).
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> ----
>
|||Here's the explanation!
http://support.microsoft.com/default...&Product=sql2k
Simple recovery model is the culprit--can't do transaction log backups...
Jim
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:u%23LtDp4lFHA.2628@.tk2msftngp13.phx.gbl...
> Start Enterprise Mangler. Drill down to Management | Database Maintenance
> Plans. Right click and select "Maintenance Plan History...". Set the
> filters to isolate the plan that failed. Select the entry for the plan
> and the failure date, click the "Properties..." button to see the details
> of why it failed.
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Jim" <please.reply@.group> wrote in message
> news:upRvnl4lFHA.3656@.TK2MSFTNGP09.phx.gbl...
>
appears, and the job shows as having failed.
Any ideas, anyone?
Thanks,
Jim
Event Type: Warning
Event Source: SQLSERVERAGENT
Event Category: Job Engine
Event ID: 208
Date: 8/2/2005
Time: 9:47:24 AM
User: N/A
Computer: GENOME-MARS
Description:
SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
'DB Maintenance Plan 1'' (0xAF8C5DECB6A71142915AA55ED294E0B4) - Status:
Failed - Invoked on: 2005-08-02 09:07:42 - Message: The job failed. The Job
was invoked by User UAB\jmoon. The last step to run was step 1 (Step 1).
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
Start Enterprise Mangler. Drill down to Management | Database Maintenance
Plans. Right click and select "Maintenance Plan History...". Set the
filters to isolate the plan that failed. Select the entry for the plan and
the failure date, click the "Properties..." button to see the details of why
it failed.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Jim" <please.reply@.group> wrote in message
news:upRvnl4lFHA.3656@.TK2MSFTNGP09.phx.gbl...
> All the transaction logs are created, but the Application log entry below
> appears, and the job shows as having failed.
> Any ideas, anyone?
> Thanks,
> Jim
> --
> Event Type: Warning
> Event Source: SQLSERVERAGENT
> Event Category: Job Engine
> Event ID: 208
> Date: 8/2/2005
> Time: 9:47:24 AM
> User: N/A
> Computer: GENOME-MARS
> Description:
> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance
> Plan 'DB Maintenance Plan 1'' (0xAF8C5DECB6A71142915AA55ED294E0B4) -
> Status: Failed - Invoked on: 2005-08-02 09:07:42 - Message: The job
> failed. The Job was invoked by User UAB\jmoon. The last step to run was
> step 1 (Step 1).
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> ----
>
|||Here's the explanation!
http://support.microsoft.com/default...&Product=sql2k
Simple recovery model is the culprit--can't do transaction log backups...
Jim
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:u%23LtDp4lFHA.2628@.tk2msftngp13.phx.gbl...
> Start Enterprise Mangler. Drill down to Management | Database Maintenance
> Plans. Right click and select "Maintenance Plan History...". Set the
> filters to isolate the plan that failed. Select the entry for the plan
> and the failure date, click the "Properties..." button to see the details
> of why it failed.
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Jim" <please.reply@.group> wrote in message
> news:upRvnl4lFHA.3656@.TK2MSFTNGP09.phx.gbl...
>
Log Backup Job works, but reports failure.
All the transaction logs are created, but the Application log entry below
appears, and the job shows as having failed.
Any ideas, anyone?
Thanks,
Jim
--
Event Type: Warning
Event Source: SQLSERVERAGENT
Event Category: Job Engine
Event ID: 208
Date: 8/2/2005
Time: 9:47:24 AM
User: N/A
Computer: GENOME-MARS
Description:
SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
'DB Maintenance Plan 1'' (0xAF8C5DECB6A71142915AA55ED294E0B4) - Status:
Failed - Invoked on: 2005-08-02 09:07:42 - Message: The job failed. The Job
was invoked by User UAB\jmoon. The last step to run was step 1 (Step 1).
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
----
--Start Enterprise Mangler. Drill down to Management | Database Maintenance
Plans. Right click and select "Maintenance Plan History...". Set the
filters to isolate the plan that failed. Select the entry for the plan and
the failure date, click the "Properties..." button to see the details of why
it failed.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Jim" <please.reply@.group> wrote in message
news:upRvnl4lFHA.3656@.TK2MSFTNGP09.phx.gbl...
> All the transaction logs are created, but the Application log entry below
> appears, and the job shows as having failed.
> Any ideas, anyone?
> Thanks,
> Jim
> --
> Event Type: Warning
> Event Source: SQLSERVERAGENT
> Event Category: Job Engine
> Event ID: 208
> Date: 8/2/2005
> Time: 9:47:24 AM
> User: N/A
> Computer: GENOME-MARS
> Description:
> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance
> Plan 'DB Maintenance Plan 1'' (0xAF8C5DECB6A71142915AA55ED294E0B4) -
> Status: Failed - Invoked on: 2005-08-02 09:07:42 - Message: The job
> failed. The Job was invoked by User UAB\jmoon. The last step to run was
> step 1 (Step 1).
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> ----
--
>|||Here's the explanation!
http://support.microsoft.com/defaul...2&Product=sql2k
Simple recovery model is the culprit--can't do transaction log backups...
Jim
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:u%23LtDp4lFHA.2628@.tk2msftngp13.phx.gbl...
> Start Enterprise Mangler. Drill down to Management | Database Maintenance
> Plans. Right click and select "Maintenance Plan History...". Set the
> filters to isolate the plan that failed. Select the entry for the plan
> and the failure date, click the "Properties..." button to see the details
> of why it failed.
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Jim" <please.reply@.group> wrote in message
> news:upRvnl4lFHA.3656@.TK2MSFTNGP09.phx.gbl...
>
appears, and the job shows as having failed.
Any ideas, anyone?
Thanks,
Jim
--
Event Type: Warning
Event Source: SQLSERVERAGENT
Event Category: Job Engine
Event ID: 208
Date: 8/2/2005
Time: 9:47:24 AM
User: N/A
Computer: GENOME-MARS
Description:
SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
'DB Maintenance Plan 1'' (0xAF8C5DECB6A71142915AA55ED294E0B4) - Status:
Failed - Invoked on: 2005-08-02 09:07:42 - Message: The job failed. The Job
was invoked by User UAB\jmoon. The last step to run was step 1 (Step 1).
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
----
--Start Enterprise Mangler. Drill down to Management | Database Maintenance
Plans. Right click and select "Maintenance Plan History...". Set the
filters to isolate the plan that failed. Select the entry for the plan and
the failure date, click the "Properties..." button to see the details of why
it failed.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Jim" <please.reply@.group> wrote in message
news:upRvnl4lFHA.3656@.TK2MSFTNGP09.phx.gbl...
> All the transaction logs are created, but the Application log entry below
> appears, and the job shows as having failed.
> Any ideas, anyone?
> Thanks,
> Jim
> --
> Event Type: Warning
> Event Source: SQLSERVERAGENT
> Event Category: Job Engine
> Event ID: 208
> Date: 8/2/2005
> Time: 9:47:24 AM
> User: N/A
> Computer: GENOME-MARS
> Description:
> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance
> Plan 'DB Maintenance Plan 1'' (0xAF8C5DECB6A71142915AA55ED294E0B4) -
> Status: Failed - Invoked on: 2005-08-02 09:07:42 - Message: The job
> failed. The Job was invoked by User UAB\jmoon. The last step to run was
> step 1 (Step 1).
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> ----
--
>|||Here's the explanation!
http://support.microsoft.com/defaul...2&Product=sql2k
Simple recovery model is the culprit--can't do transaction log backups...
Jim
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:u%23LtDp4lFHA.2628@.tk2msftngp13.phx.gbl...
> Start Enterprise Mangler. Drill down to Management | Database Maintenance
> Plans. Right click and select "Maintenance Plan History...". Set the
> filters to isolate the plan that failed. Select the entry for the plan
> and the failure date, click the "Properties..." button to see the details
> of why it failed.
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Jim" <please.reply@.group> wrote in message
> news:upRvnl4lFHA.3656@.TK2MSFTNGP09.phx.gbl...
>
Log all performed SQL Statements
Hi there,
I have a little problem concerning the replication of a SQL Server database. The situation is as follows:
We have an application and a SQL server database running at location A. We now want to have a copy at a Location B of the application and the database. Location B does not need to change anything in the program or the database - it's all just read.
The two locations are only connected by a very slow VPN line once in while. The program files can be transferred by creating a compressed backup file and sending it over the VPN line. The database, however is too large - even compressed it takes far too long to send it. The replication with SQL Server did not work out as well.
Now my idea was to perform only once a full backup at Site A and then send only the differential backups to Site B. Since they are performing full backups at Site A every night, unfortunately this does not work either.
Now my question is, if there is any possibility to do some thing like this. I thought it would be good to just create a log file storing every SQL Statement that has been performed on the Site A server and just send this text file. Is this possible?
Thank you very much for you help in advance. Any other solutions or hints are appreciated as well.
So long
TheSentinel85
P.S. I am using MSDE / SQL Server 2000
Transactional replication is just design for this case.It will replay every command at Server B that already issued at Server A.
It's based on logs. And it will keep the data at Server B consistent with Server A within the same transactional.
Also SQL Server 2000 and MSDE already support it.
Thanks,
Zuomin
|||
Hm...well I just tried to use the replication but it just did not work. It justs does not synchronize the databases and I don't get any errors.
Do you have any good online resource where I can read about replication of SQL Server?
Thanks,
TheSentinel85
|||Sure. Here are some:Plan: http://msdn2.microsoft.com/en-us/library/aa179423(SQL.80).aspx
Implement: http://msdn2.microsoft.com/en-us/library/aa237152(SQL.80).aspx
Also for prototype, you can setup the replication from Enterprise Manager. The generate the script as start up.
Thanks,
Zuomin
|||
I have now tried to set up the Replication...again.
But I just get the thing to work. I can set up the server for distribution and I can add a publication. On the B site I can add a Subscription (Pull). The problem is: it just does not synchronize the databases.
Thanks for the web pages but I think that is quite the same what is found in the help file - and that was the first resource I looked at.
Is there anything I need to take special care of when setting up the replication?
Thanks,
TheSentinel85
|||Sync replication:Option 1:
run executalble file, distrib.exe for transactional replication, replmerg.exe for merge replication.
Option 2:
Set up SQL Agent Job do the sync.
Thanks,
Zuomin
|||
Have you looked at log shipping to a read only standby database? Depending on the data change volumne, it may or may not be an answer, but is another approach to replication.
And what do you mean that replication did not synchronize the databases? I did not see in the thread how you knew?
|||Thanks to you both.
@.ZUOMIN:
ok I will try it manually.
@.JRPM:
Isn't the log empty if they do a complete backup at site A every night? Or is log shipping different from the transaction log backup?
How I know it did not replicate? The database was just empty and the SQL Agent did not do anything.
EDIT: I just saw that log shipping is not working as it is only available in SQL Server Enterprise Edition.... :-(
So long
|||You could still perform log shipping within Standard edition, http://www.sql-server-performance.com/articles/clustering/log_shipping_70_p1.aspx fyi.
|||Don't give up on replication - it can be a little confusing at first, but it is actually quite useful. Check out these links to help you find the problem:
http://technet.microsoft.com/en-us/library/ms151756.aspx
Buck Woody|||Hi everyone,
sry I didn't respond a little bit faster. I have now set up the whole thing in a different way. What I do now is perform a Trace on INSERTS, UPDATES and EXEC of SPs on the Site A server and just send this trace file to my other server. I will have to see if this costs too much performance, but I can not imagine that. The trace file upload which is done via FTP should be much quicker than replicating or log shipping or anything, as the compressed trace has only some hundrer kb.
Hopefully we are going to test the whole thing in the next days...
I will let you know what the results are....
Thank you all so far for your suggestions...
So long
TheSentinel85
Log all performed SQL Statements
Hi there,
I have a little problem concerning the replication of a SQL Server database. The situation is as follows:
We have an application and a SQL server database running at location A. We now want to have a copy at a Location B of the application and the database. Location B does not need to change anything in the program or the database - it's all just read.
The two locations are only connected by a very slow VPN line once in while. The program files can be transferred by creating a compressed backup file and sending it over the VPN line. The database, however is too large - even compressed it takes far too long to send it. The replication with SQL Server did not work out as well.
Now my idea was to perform only once a full backup at Site A and then send only the differential backups to Site B. Since they are performing full backups at Site A every night, unfortunately this does not work either.
Now my question is, if there is any possibility to do some thing like this. I thought it would be good to just create a log file storing every SQL Statement that has been performed on the Site A server and just send this text file. Is this possible?
Thank you very much for you help in advance. Any other solutions or hints are appreciated as well.
So long
TheSentinel85
P.S. I am using MSDE / SQL Server 2000
Transactional replication is just design for this case.It will replay every command at Server B that already issued at Server A.
It's based on logs. And it will keep the data at Server B consistent with Server A within the same transactional.
Also SQL Server 2000 and MSDE already support it.
Thanks,
Zuomin
|||
Hm...well I just tried to use the replication but it just did not work. It justs does not synchronize the databases and I don't get any errors.
Do you have any good online resource where I can read about replication of SQL Server?
Thanks,
TheSentinel85
|||Sure. Here are some:Plan: http://msdn2.microsoft.com/en-us/library/aa179423(SQL.80).aspx
Implement: http://msdn2.microsoft.com/en-us/library/aa237152(SQL.80).aspx
Also for prototype, you can setup the replication from Enterprise Manager. The generate the script as start up.
Thanks,
Zuomin
|||
I have now tried to set up the Replication...again.
But I just get the thing to work. I can set up the server for distribution and I can add a publication. On the B site I can add a Subscription (Pull). The problem is: it just does not synchronize the databases.
Thanks for the web pages but I think that is quite the same what is found in the help file - and that was the first resource I looked at.
Is there anything I need to take special care of when setting up the replication?
Thanks,
TheSentinel85
|||Sync replication:Option 1:
run executalble file, distrib.exe for transactional replication, replmerg.exe for merge replication.
Option 2:
Set up SQL Agent Job do the sync.
Thanks,
Zuomin
|||
Have you looked at log shipping to a read only standby database? Depending on the data change volumne, it may or may not be an answer, but is another approach to replication.
And what do you mean that replication did not synchronize the databases? I did not see in the thread how you knew?
|||Thanks to you both.
@.ZUOMIN:
ok I will try it manually.
@.JRPM:
Isn't the log empty if they do a complete backup at site A every night? Or is log shipping different from the transaction log backup?
How I know it did not replicate? The database was just empty and the SQL Agent did not do anything.
EDIT: I just saw that log shipping is not working as it is only available in SQL Server Enterprise Edition.... :-(
So long
|||You could still perform log shipping within Standard edition, http://www.sql-server-performance.com/articles/clustering/log_shipping_70_p1.aspx fyi.
|||Don't give up on replication - it can be a little confusing at first, but it is actually quite useful. Check out these links to help you find the problem:
http://technet.microsoft.com/en-us/library/ms151756.aspx
Buck Woody|||Hi everyone,
sry I didn't respond a little bit faster. I have now set up the whole thing in a different way. What I do now is perform a Trace on INSERTS, UPDATES and EXEC of SPs on the Site A server and just send this trace file to my other server. I will have to see if this costs too much performance, but I can not imagine that. The trace file upload which is done via FTP should be much quicker than replicating or log shipping or anything, as the compressed trace has only some hundrer kb.
Hopefully we are going to test the whole thing in the next days...
I will let you know what the results are....
Thank you all so far for your suggestions...
So long
TheSentinel85
Log all performed SQL Statements
Hi there,
I have a little problem concerning the replication of a SQL Server database. The situation is as follows:
We have an application and a SQL server database running at location A. We now want to have a copy at a Location B of the application and the database. Location B does not need to change anything in the program or the database - it's all just read.
The two locations are only connected by a very slow VPN line once in while. The program files can be transferred by creating a compressed backup file and sending it over the VPN line. The database, however is too large - even compressed it takes far too long to send it. The replication with SQL Server did not work out as well.
Now my idea was to perform only once a full backup at Site A and then send only the differential backups to Site B. Since they are performing full backups at Site A every night, unfortunately this does not work either.
Now my question is, if there is any possibility to do some thing like this. I thought it would be good to just create a log file storing every SQL Statement that has been performed on the Site A server and just send this text file. Is this possible?
Thank you very much for you help in advance. Any other solutions or hints are appreciated as well.
So long
TheSentinel85
P.S. I am using MSDE / SQL Server 2000
Transactional replication is just design for this case.It will replay every command at Server B that already issued at Server A.
It's based on logs. And it will keep the data at Server B consistent with Server A within the same transactional.
Also SQL Server 2000 and MSDE already support it.
Thanks,
Zuomin
|||
Hm...well I just tried to use the replication but it just did not work. It justs does not synchronize the databases and I don't get any errors.
Do you have any good online resource where I can read about replication of SQL Server?
Thanks,
TheSentinel85
|||Sure. Here are some:Plan: http://msdn2.microsoft.com/en-us/library/aa179423(SQL.80).aspx
Implement: http://msdn2.microsoft.com/en-us/library/aa237152(SQL.80).aspx
Also for prototype, you can setup the replication from Enterprise Manager. The generate the script as start up.
Thanks,
Zuomin
|||
I have now tried to set up the Replication...again.
But I just get the thing to work. I can set up the server for distribution and I can add a publication. On the B site I can add a Subscription (Pull). The problem is: it just does not synchronize the databases.
Thanks for the web pages but I think that is quite the same what is found in the help file - and that was the first resource I looked at.
Is there anything I need to take special care of when setting up the replication?
Thanks,
TheSentinel85
|||Sync replication:Option 1:
run executalble file, distrib.exe for transactional replication, replmerg.exe for merge replication.
Option 2:
Set up SQL Agent Job do the sync.
Thanks,
Zuomin
|||
Have you looked at log shipping to a read only standby database? Depending on the data change volumne, it may or may not be an answer, but is another approach to replication.
And what do you mean that replication did not synchronize the databases? I did not see in the thread how you knew?
|||Thanks to you both.
@.ZUOMIN:
ok I will try it manually.
@.JRPM:
Isn't the log empty if they do a complete backup at site A every night? Or is log shipping different from the transaction log backup?
How I know it did not replicate? The database was just empty and the SQL Agent did not do anything.
EDIT: I just saw that log shipping is not working as it is only available in SQL Server Enterprise Edition.... :-(
So long
|||You could still perform log shipping within Standard edition, http://www.sql-server-performance.com/articles/clustering/log_shipping_70_p1.aspx fyi.
|||Don't give up on replication - it can be a little confusing at first, but it is actually quite useful. Check out these links to help you find the problem:
http://technet.microsoft.com/en-us/library/ms151756.aspx
Buck Woody|||Hi everyone,
sry I didn't respond a little bit faster. I have now set up the whole thing in a different way. What I do now is perform a Trace on INSERTS, UPDATES and EXEC of SPs on the Site A server and just send this trace file to my other server. I will have to see if this costs too much performance, but I can not imagine that. The trace file upload which is done via FTP should be much quicker than replicating or log shipping or anything, as the compressed trace has only some hundrer kb.
Hopefully we are going to test the whole thing in the next days...
I will let you know what the results are....
Thank you all so far for your suggestions...
So long
TheSentinel85
Monday, February 20, 2012
Lockwood Tech Audit Toll
I am evaluating demo version of Lockwood Tech Audit Tool and having problems with "undo" module.
When I click "UNDO" it not only freezes application but entire PC, did any of you experienced same sort of problem ?
ThanksI am looking at Entegra from Lumigent, and so far so good. Have you tried that?
When I click "UNDO" it not only freezes application but entire PC, did any of you experienced same sort of problem ?
ThanksI am looking at Entegra from Lumigent, and so far so good. Have you tried that?
Locks and slow preformance?
Hello
I have one question about locks and slow preformance. I have some
application that connect to database, there i have also some ISAPI
application which connect to SQL. Ockey, when i restart SQL server,
performance is very good, but at the en of week preformance on sql server
fall to mybe 1/5 of time. So if some query take 2 seconds to execute at the
and of week it takes more than 10sec. I try to figure out what coold be
wrong, and i recognize if my locks increse mort than about 60 process (and
there are may sleeping locks) in Locks/Process ID the preformance fall down,
then i also try to kill some proces and aplication runs faster. I have all
default values,
So can somebody explain me why this happend
thanksHi
Ensure that you have suitable indexes for your queries. Run Profiler to see
what queries are being executed.
Run Sp_who2 to see if what is blocking what.
Regards
Mike
"Matej Kavèiè" wrote:
> Hello
> I have one question about locks and slow preformance. I have some
> application that connect to database, there i have also some ISAPI
> application which connect to SQL. Ockey, when i restart SQL server,
> performance is very good, but at the en of week preformance on sql server
> fall to mybe 1/5 of time. So if some query take 2 seconds to execute at the
> and of week it takes more than 10sec. I try to figure out what coold be
> wrong, and i recognize if my locks increse mort than about 60 process (and
> there are may sleeping locks) in Locks/Process ID the preformance fall down,
> then i also try to kill some proces and aplication runs faster. I have all
> default values,
> So can somebody explain me why this happend
> thanks
>
>
I have one question about locks and slow preformance. I have some
application that connect to database, there i have also some ISAPI
application which connect to SQL. Ockey, when i restart SQL server,
performance is very good, but at the en of week preformance on sql server
fall to mybe 1/5 of time. So if some query take 2 seconds to execute at the
and of week it takes more than 10sec. I try to figure out what coold be
wrong, and i recognize if my locks increse mort than about 60 process (and
there are may sleeping locks) in Locks/Process ID the preformance fall down,
then i also try to kill some proces and aplication runs faster. I have all
default values,
So can somebody explain me why this happend
thanksHi
Ensure that you have suitable indexes for your queries. Run Profiler to see
what queries are being executed.
Run Sp_who2 to see if what is blocking what.
Regards
Mike
"Matej Kavèiè" wrote:
> Hello
> I have one question about locks and slow preformance. I have some
> application that connect to database, there i have also some ISAPI
> application which connect to SQL. Ockey, when i restart SQL server,
> performance is very good, but at the en of week preformance on sql server
> fall to mybe 1/5 of time. So if some query take 2 seconds to execute at the
> and of week it takes more than 10sec. I try to figure out what coold be
> wrong, and i recognize if my locks increse mort than about 60 process (and
> there are may sleeping locks) in Locks/Process ID the preformance fall down,
> then i also try to kill some proces and aplication runs faster. I have all
> default values,
> So can somebody explain me why this happend
> thanks
>
>
Subscribe to:
Posts (Atom)