our mastlog.ldf file of Master database got deleted accidently by some user.
Is there anyway that the database could be started and new log file created
. master.mdf file is intact.
Thanks
ATDid you try restarting the server? If it won't start then you can rebuild
Master using the rebuildm utility (see Books Online for details). Then start
the server in single-user mode, restore Master from a backup (you do backup
the Master database don't you...?) or manually reattach your databases,
recreate logins, etc.
Make sure that the location of your database files is only accessible by the
administrator and the SQL Server service account so that this can't happen
again.
David Portas
SQL Server MVP
--|||David,
There is no backup available for master database. all I have is mdf file. Is
n't there any way that I can use this file to rebuild master database.
Thanks
AT|||HI
Maybe help for you:
I tested the following procedure, and successfully completed, but not guaran
tee your success.
And the future: Backup system databases periodically (daily)!
Copy to test server the master.mdf and rename
Use sp_attach_single_file_db wit another name than master (eg: NewMaser)
Use rebuilm.exe. (Copy SQL setup cd to hard disk and remove the read only fl
ag from files!)
Reinstall Service Packs and patches.
Stop the SQL server.
Create file backup from master.mdf and mastlog.ldf (Maybe You need restart
the restore)
Then start the server in single-user mode
--Like another way from here
Allow modifications to be made directly to the system catalogs
Update sysconfigures, syscurconfigs, tables from NewMaster database
Create insert statements and copy data from NewMaster to master database (US
E: WHERE NOT IN NEW MASTER..TABLE)
-sysaltfiles (or attach all production database and distribution if exist)
-sysservers (better: Recreate Linked Servers from documentation.)
-sysmessages (It is easy)
-sysxlogins (better: use the another way procedure for recreate logins)
-sysusers (or recreate users in master)
Disable modifications to be made directly to the system catalogs
Backup the master database
Test the server
Restart server
Backup master database
Another way:
-Recreate logins:
read the folowing: Microsoft Knowledge Base Article - 246133
< http://support.microsoft.com/defaul...133&Product=sql
>
Rewrite the sp_help_revlogin (use the attachaed database as source eg: FROM
master..sysxlogins --> FROM TESTSERVER.NewMaster.dbo.sysxlogins)
Recreate Linked Servers from documentation. (If You used remote server for r
eplication create linked server instead of remote)
Recreate users on master
Attach all production databases and distribution database.
Import custom sysmessages from NewMaster
Disable modifications to be made directly to the system catalogs
Backup the master database
Test the server
Restart server
Backup master database
At end:
Create scheduled backup for system databases.
And repair and repair until errors disappear. (The original values in NewMas
ter help You, do not drop!)
JBandi
Showing posts with label log. Show all posts
Showing posts with label log. Show all posts
Saturday, February 25, 2012
master4IDR ??
Does anyone know what the "4IDR" databases are? (Master/Model/MSDB)
MSSQL Log entry: Starting up database 'master4IDR'.
I do see the .mdf & .ldf, but I have no such db on this server.
What are these databases?
Thanx!
I've never see any such animals. My guess is that some 3rd party tool (like backup sw or similar)
has created these.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"JLS" <jlshoop@.hotmail.com> wrote in message news:eYXq6WBIFHA.2620@.tk2msftngp13.phx.gbl...
Does anyone know what the "4IDR" databases are? (Master/Model/MSDB)
MSSQL Log entry: Starting up database 'master4IDR'.
I do see the .mdf & .ldf, but I have no such db on this server.
What are these databases?
Thanx!
|||I did a google search on 'masteri4dr' and got several, but inconclusive,
hits. Many referred to third party backup software.
Try running the query below to identify the databases associated with these
files.
SELECT *
FROM master..sysaltfiles
WHERE filename LIKE '%4IDR.mdf%'
Hope this helps.
Dan Guzman
SQL Server MVP
"JLS" <jlshoop@.hotmail.com> wrote in message
news:eYXq6WBIFHA.2620@.tk2msftngp13.phx.gbl...
Does anyone know what the "4IDR" databases are? (Master/Model/MSDB)
MSSQL Log entry: Starting up database 'master4IDR'.
I do see the .mdf & .ldf, but I have no such db on this server.
What are these databases?
Thanx!
MSSQL Log entry: Starting up database 'master4IDR'.
I do see the .mdf & .ldf, but I have no such db on this server.
What are these databases?
Thanx!
I've never see any such animals. My guess is that some 3rd party tool (like backup sw or similar)
has created these.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"JLS" <jlshoop@.hotmail.com> wrote in message news:eYXq6WBIFHA.2620@.tk2msftngp13.phx.gbl...
Does anyone know what the "4IDR" databases are? (Master/Model/MSDB)
MSSQL Log entry: Starting up database 'master4IDR'.
I do see the .mdf & .ldf, but I have no such db on this server.
What are these databases?
Thanx!
|||I did a google search on 'masteri4dr' and got several, but inconclusive,
hits. Many referred to third party backup software.
Try running the query below to identify the databases associated with these
files.
SELECT *
FROM master..sysaltfiles
WHERE filename LIKE '%4IDR.mdf%'
Hope this helps.
Dan Guzman
SQL Server MVP
"JLS" <jlshoop@.hotmail.com> wrote in message
news:eYXq6WBIFHA.2620@.tk2msftngp13.phx.gbl...
Does anyone know what the "4IDR" databases are? (Master/Model/MSDB)
MSSQL Log entry: Starting up database 'master4IDR'.
I do see the .mdf & .ldf, but I have no such db on this server.
What are these databases?
Thanx!
master4IDR ??
Does anyone know what the "4IDR" databases are? (Master/Model/MSDB)
MSSQL Log entry: Starting up database 'master4IDR'.
I do see the .mdf & .ldf, but I have no such db on this server.
What are these databases?
Thanx!I've never see any such animals. My guess is that some 3rd party tool (like
backup sw or similar)
has created these.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"JLS" <jlshoop@.hotmail.com> wrote in message news:eYXq6WBIFHA.2620@.tk2msftng
p13.phx.gbl...
Does anyone know what the "4IDR" databases are? (Master/Model/MSDB)
MSSQL Log entry: Starting up database 'master4IDR'.
I do see the .mdf & .ldf, but I have no such db on this server.
What are these databases?
Thanx!|||I did a google search on 'masteri4dr' and got several, but inconclusive,
hits. Many referred to third party backup software.
Try running the query below to identify the databases associated with these
files.
SELECT *
FROM master..sysaltfiles
WHERE filename LIKE '%4IDR.mdf%'
Hope this helps.
Dan Guzman
SQL Server MVP
"JLS" <jlshoop@.hotmail.com> wrote in message
news:eYXq6WBIFHA.2620@.tk2msftngp13.phx.gbl...
Does anyone know what the "4IDR" databases are? (Master/Model/MSDB)
MSSQL Log entry: Starting up database 'master4IDR'.
I do see the .mdf & .ldf, but I have no such db on this server.
What are these databases?
Thanx!
MSSQL Log entry: Starting up database 'master4IDR'.
I do see the .mdf & .ldf, but I have no such db on this server.
What are these databases?
Thanx!I've never see any such animals. My guess is that some 3rd party tool (like
backup sw or similar)
has created these.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"JLS" <jlshoop@.hotmail.com> wrote in message news:eYXq6WBIFHA.2620@.tk2msftng
p13.phx.gbl...
Does anyone know what the "4IDR" databases are? (Master/Model/MSDB)
MSSQL Log entry: Starting up database 'master4IDR'.
I do see the .mdf & .ldf, but I have no such db on this server.
What are these databases?
Thanx!|||I did a google search on 'masteri4dr' and got several, but inconclusive,
hits. Many referred to third party backup software.
Try running the query below to identify the databases associated with these
files.
SELECT *
FROM master..sysaltfiles
WHERE filename LIKE '%4IDR.mdf%'
Hope this helps.
Dan Guzman
SQL Server MVP
"JLS" <jlshoop@.hotmail.com> wrote in message
news:eYXq6WBIFHA.2620@.tk2msftngp13.phx.gbl...
Does anyone know what the "4IDR" databases are? (Master/Model/MSDB)
MSSQL Log entry: Starting up database 'master4IDR'.
I do see the .mdf & .ldf, but I have no such db on this server.
What are these databases?
Thanx!
Monday, February 20, 2012
master..sysmessages
Hi
I am trying to create a job to monitor the error log in sql server. I
understand that the error types with their severity are in the table
master..sysmessages.
However, which table to I frequently query or monitor so that whenever the
error which I am looking for with specific severity, when occurs the job will
be fired?
What table stores this information, so that I can run the query against it?
Thanks
When the error occurs, you want it to be logged. That's how
you know the error happened. Otherwise, they aren't really
stored anywhere.
You can create an alert that will watch for the error and in
response to the error, it can execute a job. That's sounds
like what you are looking for. You can find more information
on alerts in SQL Server books online.
-Sue
On Tue, 22 Feb 2005 14:03:09 -0800, "Pari"
<Pari@.discussions.microsoft.com> wrote:
>Hi
>I am trying to create a job to monitor the error log in sql server. I
>understand that the error types with their severity are in the table
>master..sysmessages.
>However, which table to I frequently query or monitor so that whenever the
>error which I am looking for with specific severity, when occurs the job will
>be fired?
>What table stores this information, so that I can run the query against it?
>Thanks
|||No I do not want to create alerts, I just want to find out where and in which
table sql server stores the errors that occur?
"Sue Hoegemeier" wrote:
> When the error occurs, you want it to be logged. That's how
> you know the error happened. Otherwise, they aren't really
> stored anywhere.
> You can create an alert that will watch for the error and in
> response to the error, it can execute a job. That's sounds
> like what you are looking for. You can find more information
> on alerts in SQL Server books online.
> -Sue
> On Tue, 22 Feb 2005 14:03:09 -0800, "Pari"
> <Pari@.discussions.microsoft.com> wrote:
>
>
|||Not stored in any table.
-Sue
On Tue, 22 Feb 2005 14:57:08 -0800, "Pari"
<Pari@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>No I do not want to create alerts, I just want to find out where and in which
>table sql server stores the errors that occur?
>
>"Sue Hoegemeier" wrote:
|||I am using smtp and I cannot really use inbuilt sql alerts, as that requires
sql mail usage. Is there a way I can query the errors that occur so that I
can send out smtp e-mails?
Thanks
Paresh.
"Sue Hoegemeier" wrote:
> Not stored in any table.
> -Sue
> On Tue, 22 Feb 2005 14:57:08 -0800, "Pari"
> <Pari@.discussions.microsoft.com> wrote:
>
>
|||Alerts don't require SQL mail - I've often used them with
smtp mail. You don't need to use the notification part of
the alert and can just setup the response to be executing a
job. Alerts require SQL Agent to be running but not SQL
Mail. You can also download an smtp alerter for SQL Server
from:
http://www.dbmaint.com/SmtpAlerter.asp
But if you just don't want to use alerts, you essentially
would need to do the same thing setting up an alert would do
- you'd have to set the error up to be logged and then read
the log to find the error. If you find the error in the log,
then send out the email.
To log the error, you would use sp_altermessage.
For example, If I wanted the error for invalid object name
to be logged (error 208), I would use:
sp_altermessage 208, 'WITH_LOG', 'true'
If you query sysmessages before and after executing this,
you'll notice the dlevel value will change to reflect the
logging of this error.
Then whenever that error occurs, it will be logged. So you
would write or use a utility to read the log and search for
the string: Error: 208
in the SQL Server log or in the description in the
Application Event log. You can then send out the mail,
execute the job to email if you find the string.
And in terms of if you define an alert, the occurrence is of
the error is indicated in msdb..sysalerts. Last occurrence
and occurrence count are stored in sysalerts. You could also
work with this table but that would require setting up an
alert. You could use sp_update_alert to reset any of the
values for the occurrence of the alert.
I can't think of what else you would have for options in how
to do this but that hopefully covers most of the ways or
gives you enough information on how you could handle it.
-Sue
On Wed, 23 Feb 2005 06:07:07 -0800, "Pari"
<Pari@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I am using smtp and I cannot really use inbuilt sql alerts, as that requires
>sql mail usage. Is there a way I can query the errors that occur so that I
>can send out smtp e-mails?
>Thanks
>Paresh.
>"Sue Hoegemeier" wrote:
I am trying to create a job to monitor the error log in sql server. I
understand that the error types with their severity are in the table
master..sysmessages.
However, which table to I frequently query or monitor so that whenever the
error which I am looking for with specific severity, when occurs the job will
be fired?
What table stores this information, so that I can run the query against it?
Thanks
When the error occurs, you want it to be logged. That's how
you know the error happened. Otherwise, they aren't really
stored anywhere.
You can create an alert that will watch for the error and in
response to the error, it can execute a job. That's sounds
like what you are looking for. You can find more information
on alerts in SQL Server books online.
-Sue
On Tue, 22 Feb 2005 14:03:09 -0800, "Pari"
<Pari@.discussions.microsoft.com> wrote:
>Hi
>I am trying to create a job to monitor the error log in sql server. I
>understand that the error types with their severity are in the table
>master..sysmessages.
>However, which table to I frequently query or monitor so that whenever the
>error which I am looking for with specific severity, when occurs the job will
>be fired?
>What table stores this information, so that I can run the query against it?
>Thanks
|||No I do not want to create alerts, I just want to find out where and in which
table sql server stores the errors that occur?
"Sue Hoegemeier" wrote:
> When the error occurs, you want it to be logged. That's how
> you know the error happened. Otherwise, they aren't really
> stored anywhere.
> You can create an alert that will watch for the error and in
> response to the error, it can execute a job. That's sounds
> like what you are looking for. You can find more information
> on alerts in SQL Server books online.
> -Sue
> On Tue, 22 Feb 2005 14:03:09 -0800, "Pari"
> <Pari@.discussions.microsoft.com> wrote:
>
>
|||Not stored in any table.
-Sue
On Tue, 22 Feb 2005 14:57:08 -0800, "Pari"
<Pari@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>No I do not want to create alerts, I just want to find out where and in which
>table sql server stores the errors that occur?
>
>"Sue Hoegemeier" wrote:
|||I am using smtp and I cannot really use inbuilt sql alerts, as that requires
sql mail usage. Is there a way I can query the errors that occur so that I
can send out smtp e-mails?
Thanks
Paresh.
"Sue Hoegemeier" wrote:
> Not stored in any table.
> -Sue
> On Tue, 22 Feb 2005 14:57:08 -0800, "Pari"
> <Pari@.discussions.microsoft.com> wrote:
>
>
|||Alerts don't require SQL mail - I've often used them with
smtp mail. You don't need to use the notification part of
the alert and can just setup the response to be executing a
job. Alerts require SQL Agent to be running but not SQL
Mail. You can also download an smtp alerter for SQL Server
from:
http://www.dbmaint.com/SmtpAlerter.asp
But if you just don't want to use alerts, you essentially
would need to do the same thing setting up an alert would do
- you'd have to set the error up to be logged and then read
the log to find the error. If you find the error in the log,
then send out the email.
To log the error, you would use sp_altermessage.
For example, If I wanted the error for invalid object name
to be logged (error 208), I would use:
sp_altermessage 208, 'WITH_LOG', 'true'
If you query sysmessages before and after executing this,
you'll notice the dlevel value will change to reflect the
logging of this error.
Then whenever that error occurs, it will be logged. So you
would write or use a utility to read the log and search for
the string: Error: 208
in the SQL Server log or in the description in the
Application Event log. You can then send out the mail,
execute the job to email if you find the string.
And in terms of if you define an alert, the occurrence is of
the error is indicated in msdb..sysalerts. Last occurrence
and occurrence count are stored in sysalerts. You could also
work with this table but that would require setting up an
alert. You could use sp_update_alert to reset any of the
values for the occurrence of the alert.
I can't think of what else you would have for options in how
to do this but that hopefully covers most of the ways or
gives you enough information on how you could handle it.
-Sue
On Wed, 23 Feb 2005 06:07:07 -0800, "Pari"
<Pari@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I am using smtp and I cannot really use inbuilt sql alerts, as that requires
>sql mail usage. Is there a way I can query the errors that occur so that I
>can send out smtp e-mails?
>Thanks
>Paresh.
>"Sue Hoegemeier" wrote:
master..sysmessages
Hi
I am trying to create a job to monitor the error log in sql server. I
understand that the error types with their severity are in the table
master..sysmessages.
However, which table to I frequently query or monitor so that whenever the
error which I am looking for with specific severity, when occurs the job wil
l
be fired?
What table stores this information, so that I can run the query against it?
ThanksWhen the error occurs, you want it to be logged. That's how
you know the error happened. Otherwise, they aren't really
stored anywhere.
You can create an alert that will watch for the error and in
response to the error, it can execute a job. That's sounds
like what you are looking for. You can find more information
on alerts in SQL Server books online.
-Sue
On Tue, 22 Feb 2005 14:03:09 -0800, "Pari"
<Pari@.discussions.microsoft.com> wrote:
>Hi
>I am trying to create a job to monitor the error log in sql server. I
>understand that the error types with their severity are in the table
>master..sysmessages.
>However, which table to I frequently query or monitor so that whenever the
>error which I am looking for with specific severity, when occurs the job wi
ll
>be fired?
>What table stores this information, so that I can run the query against it?
>Thanks|||No I do not want to create alerts, I just want to find out where and in whic
h
table sql server stores the errors that occur?
"Sue Hoegemeier" wrote:
> When the error occurs, you want it to be logged. That's how
> you know the error happened. Otherwise, they aren't really
> stored anywhere.
> You can create an alert that will watch for the error and in
> response to the error, it can execute a job. That's sounds
> like what you are looking for. You can find more information
> on alerts in SQL Server books online.
> -Sue
> On Tue, 22 Feb 2005 14:03:09 -0800, "Pari"
> <Pari@.discussions.microsoft.com> wrote:
>
>|||Not stored in any table.
-Sue
On Tue, 22 Feb 2005 14:57:08 -0800, "Pari"
<Pari@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>No I do not want to create alerts, I just want to find out where and in whi
ch
>table sql server stores the errors that occur?
>
>"Sue Hoegemeier" wrote:
>|||I am using smtp and I cannot really use inbuilt sql alerts, as that requires
sql mail usage. Is there a way I can query the errors that occur so that I
can send out smtp e-mails?
Thanks
Paresh.
"Sue Hoegemeier" wrote:
> Not stored in any table.
> -Sue
> On Tue, 22 Feb 2005 14:57:08 -0800, "Pari"
> <Pari@.discussions.microsoft.com> wrote:
>
>|||Alerts don't require SQL mail - I've often used them with
smtp mail. You don't need to use the notification part of
the alert and can just setup the response to be executing a
job. Alerts require SQL Agent to be running but not SQL
Mail. You can also download an smtp alerter for SQL Server
from:
http://www.dbmaint.com/SmtpAlerter.asp
But if you just don't want to use alerts, you essentially
would need to do the same thing setting up an alert would do
- you'd have to set the error up to be logged and then read
the log to find the error. If you find the error in the log,
then send out the email.
To log the error, you would use sp_altermessage.
For example, If I wanted the error for invalid object name
to be logged (error 208), I would use:
sp_altermessage 208, 'WITH_LOG', 'true'
If you query sysmessages before and after executing this,
you'll notice the dlevel value will change to reflect the
logging of this error.
Then whenever that error occurs, it will be logged. So you
would write or use a utility to read the log and search for
the string: Error: 208
in the SQL Server log or in the description in the
Application Event log. You can then send out the mail,
execute the job to email if you find the string.
And in terms of if you define an alert, the occurrence is of
the error is indicated in msdb..sysalerts. Last occurrence
and occurrence count are stored in sysalerts. You could also
work with this table but that would require setting up an
alert. You could use sp_update_alert to reset any of the
values for the occurrence of the alert.
I can't think of what else you would have for options in how
to do this but that hopefully covers most of the ways or
gives you enough information on how you could handle it.
-Sue
On Wed, 23 Feb 2005 06:07:07 -0800, "Pari"
<Pari@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I am using smtp and I cannot really use inbuilt sql alerts, as that require
s
>sql mail usage. Is there a way I can query the errors that occur so that I
>can send out smtp e-mails?
>Thanks
>Paresh.
>"Sue Hoegemeier" wrote:
>
I am trying to create a job to monitor the error log in sql server. I
understand that the error types with their severity are in the table
master..sysmessages.
However, which table to I frequently query or monitor so that whenever the
error which I am looking for with specific severity, when occurs the job wil
l
be fired?
What table stores this information, so that I can run the query against it?
ThanksWhen the error occurs, you want it to be logged. That's how
you know the error happened. Otherwise, they aren't really
stored anywhere.
You can create an alert that will watch for the error and in
response to the error, it can execute a job. That's sounds
like what you are looking for. You can find more information
on alerts in SQL Server books online.
-Sue
On Tue, 22 Feb 2005 14:03:09 -0800, "Pari"
<Pari@.discussions.microsoft.com> wrote:
>Hi
>I am trying to create a job to monitor the error log in sql server. I
>understand that the error types with their severity are in the table
>master..sysmessages.
>However, which table to I frequently query or monitor so that whenever the
>error which I am looking for with specific severity, when occurs the job wi
ll
>be fired?
>What table stores this information, so that I can run the query against it?
>Thanks|||No I do not want to create alerts, I just want to find out where and in whic
h
table sql server stores the errors that occur?
"Sue Hoegemeier" wrote:
> When the error occurs, you want it to be logged. That's how
> you know the error happened. Otherwise, they aren't really
> stored anywhere.
> You can create an alert that will watch for the error and in
> response to the error, it can execute a job. That's sounds
> like what you are looking for. You can find more information
> on alerts in SQL Server books online.
> -Sue
> On Tue, 22 Feb 2005 14:03:09 -0800, "Pari"
> <Pari@.discussions.microsoft.com> wrote:
>
>|||Not stored in any table.
-Sue
On Tue, 22 Feb 2005 14:57:08 -0800, "Pari"
<Pari@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>No I do not want to create alerts, I just want to find out where and in whi
ch
>table sql server stores the errors that occur?
>
>"Sue Hoegemeier" wrote:
>|||I am using smtp and I cannot really use inbuilt sql alerts, as that requires
sql mail usage. Is there a way I can query the errors that occur so that I
can send out smtp e-mails?
Thanks
Paresh.
"Sue Hoegemeier" wrote:
> Not stored in any table.
> -Sue
> On Tue, 22 Feb 2005 14:57:08 -0800, "Pari"
> <Pari@.discussions.microsoft.com> wrote:
>
>|||Alerts don't require SQL mail - I've often used them with
smtp mail. You don't need to use the notification part of
the alert and can just setup the response to be executing a
job. Alerts require SQL Agent to be running but not SQL
Mail. You can also download an smtp alerter for SQL Server
from:
http://www.dbmaint.com/SmtpAlerter.asp
But if you just don't want to use alerts, you essentially
would need to do the same thing setting up an alert would do
- you'd have to set the error up to be logged and then read
the log to find the error. If you find the error in the log,
then send out the email.
To log the error, you would use sp_altermessage.
For example, If I wanted the error for invalid object name
to be logged (error 208), I would use:
sp_altermessage 208, 'WITH_LOG', 'true'
If you query sysmessages before and after executing this,
you'll notice the dlevel value will change to reflect the
logging of this error.
Then whenever that error occurs, it will be logged. So you
would write or use a utility to read the log and search for
the string: Error: 208
in the SQL Server log or in the description in the
Application Event log. You can then send out the mail,
execute the job to email if you find the string.
And in terms of if you define an alert, the occurrence is of
the error is indicated in msdb..sysalerts. Last occurrence
and occurrence count are stored in sysalerts. You could also
work with this table but that would require setting up an
alert. You could use sp_update_alert to reset any of the
values for the occurrence of the alert.
I can't think of what else you would have for options in how
to do this but that hopefully covers most of the ways or
gives you enough information on how you could handle it.
-Sue
On Wed, 23 Feb 2005 06:07:07 -0800, "Pari"
<Pari@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I am using smtp and I cannot really use inbuilt sql alerts, as that require
s
>sql mail usage. Is there a way I can query the errors that occur so that I
>can send out smtp e-mails?
>Thanks
>Paresh.
>"Sue Hoegemeier" wrote:
>
master..sysmessages
Hi
I am trying to create a job to monitor the error log in sql server. I
understand that the error types with their severity are in the table
master..sysmessages.
However, which table to I frequently query or monitor so that whenever the
error which I am looking for with specific severity, when occurs the job will
be fired?
What table stores this information, so that I can run the query against it?
ThanksWhen the error occurs, you want it to be logged. That's how
you know the error happened. Otherwise, they aren't really
stored anywhere.
You can create an alert that will watch for the error and in
response to the error, it can execute a job. That's sounds
like what you are looking for. You can find more information
on alerts in SQL Server books online.
-Sue
On Tue, 22 Feb 2005 14:03:09 -0800, "Pari"
<Pari@.discussions.microsoft.com> wrote:
>Hi
>I am trying to create a job to monitor the error log in sql server. I
>understand that the error types with their severity are in the table
>master..sysmessages.
>However, which table to I frequently query or monitor so that whenever the
>error which I am looking for with specific severity, when occurs the job will
>be fired?
>What table stores this information, so that I can run the query against it?
>Thanks|||No I do not want to create alerts, I just want to find out where and in which
table sql server stores the errors that occur?
"Sue Hoegemeier" wrote:
> When the error occurs, you want it to be logged. That's how
> you know the error happened. Otherwise, they aren't really
> stored anywhere.
> You can create an alert that will watch for the error and in
> response to the error, it can execute a job. That's sounds
> like what you are looking for. You can find more information
> on alerts in SQL Server books online.
> -Sue
> On Tue, 22 Feb 2005 14:03:09 -0800, "Pari"
> <Pari@.discussions.microsoft.com> wrote:
> >Hi
> >I am trying to create a job to monitor the error log in sql server. I
> >understand that the error types with their severity are in the table
> >master..sysmessages.
> >
> >However, which table to I frequently query or monitor so that whenever the
> >error which I am looking for with specific severity, when occurs the job will
> >be fired?
> >
> >What table stores this information, so that I can run the query against it?
> >
> >Thanks
>|||Not stored in any table.
-Sue
On Tue, 22 Feb 2005 14:57:08 -0800, "Pari"
<Pari@.discussions.microsoft.com> wrote:
>No I do not want to create alerts, I just want to find out where and in which
>table sql server stores the errors that occur?
>
>"Sue Hoegemeier" wrote:
>> When the error occurs, you want it to be logged. That's how
>> you know the error happened. Otherwise, they aren't really
>> stored anywhere.
>> You can create an alert that will watch for the error and in
>> response to the error, it can execute a job. That's sounds
>> like what you are looking for. You can find more information
>> on alerts in SQL Server books online.
>> -Sue
>> On Tue, 22 Feb 2005 14:03:09 -0800, "Pari"
>> <Pari@.discussions.microsoft.com> wrote:
>> >Hi
>> >I am trying to create a job to monitor the error log in sql server. I
>> >understand that the error types with their severity are in the table
>> >master..sysmessages.
>> >
>> >However, which table to I frequently query or monitor so that whenever the
>> >error which I am looking for with specific severity, when occurs the job will
>> >be fired?
>> >
>> >What table stores this information, so that I can run the query against it?
>> >
>> >Thanks
>>|||I am using smtp and I cannot really use inbuilt sql alerts, as that requires
sql mail usage. Is there a way I can query the errors that occur so that I
can send out smtp e-mails?
Thanks
Paresh.
"Sue Hoegemeier" wrote:
> Not stored in any table.
> -Sue
> On Tue, 22 Feb 2005 14:57:08 -0800, "Pari"
> <Pari@.discussions.microsoft.com> wrote:
> >No I do not want to create alerts, I just want to find out where and in which
> >table sql server stores the errors that occur?
> >
> >
> >
> >"Sue Hoegemeier" wrote:
> >
> >> When the error occurs, you want it to be logged. That's how
> >> you know the error happened. Otherwise, they aren't really
> >> stored anywhere.
> >> You can create an alert that will watch for the error and in
> >> response to the error, it can execute a job. That's sounds
> >> like what you are looking for. You can find more information
> >> on alerts in SQL Server books online.
> >>
> >> -Sue
> >>
> >> On Tue, 22 Feb 2005 14:03:09 -0800, "Pari"
> >> <Pari@.discussions.microsoft.com> wrote:
> >>
> >> >Hi
> >> >I am trying to create a job to monitor the error log in sql server. I
> >> >understand that the error types with their severity are in the table
> >> >master..sysmessages.
> >> >
> >> >However, which table to I frequently query or monitor so that whenever the
> >> >error which I am looking for with specific severity, when occurs the job will
> >> >be fired?
> >> >
> >> >What table stores this information, so that I can run the query against it?
> >> >
> >> >Thanks
> >>
> >>
>|||Alerts don't require SQL mail - I've often used them with
smtp mail. You don't need to use the notification part of
the alert and can just setup the response to be executing a
job. Alerts require SQL Agent to be running but not SQL
Mail. You can also download an smtp alerter for SQL Server
from:
http://www.dbmaint.com/SmtpAlerter.asp
But if you just don't want to use alerts, you essentially
would need to do the same thing setting up an alert would do
- you'd have to set the error up to be logged and then read
the log to find the error. If you find the error in the log,
then send out the email.
To log the error, you would use sp_altermessage.
For example, If I wanted the error for invalid object name
to be logged (error 208), I would use:
sp_altermessage 208, 'WITH_LOG', 'true'
If you query sysmessages before and after executing this,
you'll notice the dlevel value will change to reflect the
logging of this error.
Then whenever that error occurs, it will be logged. So you
would write or use a utility to read the log and search for
the string: Error: 208
in the SQL Server log or in the description in the
Application Event log. You can then send out the mail,
execute the job to email if you find the string.
And in terms of if you define an alert, the occurrence is of
the error is indicated in msdb..sysalerts. Last occurrence
and occurrence count are stored in sysalerts. You could also
work with this table but that would require setting up an
alert. You could use sp_update_alert to reset any of the
values for the occurrence of the alert.
I can't think of what else you would have for options in how
to do this but that hopefully covers most of the ways or
gives you enough information on how you could handle it.
-Sue
On Wed, 23 Feb 2005 06:07:07 -0800, "Pari"
<Pari@.discussions.microsoft.com> wrote:
>I am using smtp and I cannot really use inbuilt sql alerts, as that requires
>sql mail usage. Is there a way I can query the errors that occur so that I
>can send out smtp e-mails?
>Thanks
>Paresh.
>"Sue Hoegemeier" wrote:
>> Not stored in any table.
>> -Sue
>> On Tue, 22 Feb 2005 14:57:08 -0800, "Pari"
>> <Pari@.discussions.microsoft.com> wrote:
>> >No I do not want to create alerts, I just want to find out where and in which
>> >table sql server stores the errors that occur?
>> >
>> >
>> >
>> >"Sue Hoegemeier" wrote:
>> >
>> >> When the error occurs, you want it to be logged. That's how
>> >> you know the error happened. Otherwise, they aren't really
>> >> stored anywhere.
>> >> You can create an alert that will watch for the error and in
>> >> response to the error, it can execute a job. That's sounds
>> >> like what you are looking for. You can find more information
>> >> on alerts in SQL Server books online.
>> >>
>> >> -Sue
>> >>
>> >> On Tue, 22 Feb 2005 14:03:09 -0800, "Pari"
>> >> <Pari@.discussions.microsoft.com> wrote:
>> >>
>> >> >Hi
>> >> >I am trying to create a job to monitor the error log in sql server. I
>> >> >understand that the error types with their severity are in the table
>> >> >master..sysmessages.
>> >> >
>> >> >However, which table to I frequently query or monitor so that whenever the
>> >> >error which I am looking for with specific severity, when occurs the job will
>> >> >be fired?
>> >> >
>> >> >What table stores this information, so that I can run the query against it?
>> >> >
>> >> >Thanks
>> >>
>> >>
>>
I am trying to create a job to monitor the error log in sql server. I
understand that the error types with their severity are in the table
master..sysmessages.
However, which table to I frequently query or monitor so that whenever the
error which I am looking for with specific severity, when occurs the job will
be fired?
What table stores this information, so that I can run the query against it?
ThanksWhen the error occurs, you want it to be logged. That's how
you know the error happened. Otherwise, they aren't really
stored anywhere.
You can create an alert that will watch for the error and in
response to the error, it can execute a job. That's sounds
like what you are looking for. You can find more information
on alerts in SQL Server books online.
-Sue
On Tue, 22 Feb 2005 14:03:09 -0800, "Pari"
<Pari@.discussions.microsoft.com> wrote:
>Hi
>I am trying to create a job to monitor the error log in sql server. I
>understand that the error types with their severity are in the table
>master..sysmessages.
>However, which table to I frequently query or monitor so that whenever the
>error which I am looking for with specific severity, when occurs the job will
>be fired?
>What table stores this information, so that I can run the query against it?
>Thanks|||No I do not want to create alerts, I just want to find out where and in which
table sql server stores the errors that occur?
"Sue Hoegemeier" wrote:
> When the error occurs, you want it to be logged. That's how
> you know the error happened. Otherwise, they aren't really
> stored anywhere.
> You can create an alert that will watch for the error and in
> response to the error, it can execute a job. That's sounds
> like what you are looking for. You can find more information
> on alerts in SQL Server books online.
> -Sue
> On Tue, 22 Feb 2005 14:03:09 -0800, "Pari"
> <Pari@.discussions.microsoft.com> wrote:
> >Hi
> >I am trying to create a job to monitor the error log in sql server. I
> >understand that the error types with their severity are in the table
> >master..sysmessages.
> >
> >However, which table to I frequently query or monitor so that whenever the
> >error which I am looking for with specific severity, when occurs the job will
> >be fired?
> >
> >What table stores this information, so that I can run the query against it?
> >
> >Thanks
>|||Not stored in any table.
-Sue
On Tue, 22 Feb 2005 14:57:08 -0800, "Pari"
<Pari@.discussions.microsoft.com> wrote:
>No I do not want to create alerts, I just want to find out where and in which
>table sql server stores the errors that occur?
>
>"Sue Hoegemeier" wrote:
>> When the error occurs, you want it to be logged. That's how
>> you know the error happened. Otherwise, they aren't really
>> stored anywhere.
>> You can create an alert that will watch for the error and in
>> response to the error, it can execute a job. That's sounds
>> like what you are looking for. You can find more information
>> on alerts in SQL Server books online.
>> -Sue
>> On Tue, 22 Feb 2005 14:03:09 -0800, "Pari"
>> <Pari@.discussions.microsoft.com> wrote:
>> >Hi
>> >I am trying to create a job to monitor the error log in sql server. I
>> >understand that the error types with their severity are in the table
>> >master..sysmessages.
>> >
>> >However, which table to I frequently query or monitor so that whenever the
>> >error which I am looking for with specific severity, when occurs the job will
>> >be fired?
>> >
>> >What table stores this information, so that I can run the query against it?
>> >
>> >Thanks
>>|||I am using smtp and I cannot really use inbuilt sql alerts, as that requires
sql mail usage. Is there a way I can query the errors that occur so that I
can send out smtp e-mails?
Thanks
Paresh.
"Sue Hoegemeier" wrote:
> Not stored in any table.
> -Sue
> On Tue, 22 Feb 2005 14:57:08 -0800, "Pari"
> <Pari@.discussions.microsoft.com> wrote:
> >No I do not want to create alerts, I just want to find out where and in which
> >table sql server stores the errors that occur?
> >
> >
> >
> >"Sue Hoegemeier" wrote:
> >
> >> When the error occurs, you want it to be logged. That's how
> >> you know the error happened. Otherwise, they aren't really
> >> stored anywhere.
> >> You can create an alert that will watch for the error and in
> >> response to the error, it can execute a job. That's sounds
> >> like what you are looking for. You can find more information
> >> on alerts in SQL Server books online.
> >>
> >> -Sue
> >>
> >> On Tue, 22 Feb 2005 14:03:09 -0800, "Pari"
> >> <Pari@.discussions.microsoft.com> wrote:
> >>
> >> >Hi
> >> >I am trying to create a job to monitor the error log in sql server. I
> >> >understand that the error types with their severity are in the table
> >> >master..sysmessages.
> >> >
> >> >However, which table to I frequently query or monitor so that whenever the
> >> >error which I am looking for with specific severity, when occurs the job will
> >> >be fired?
> >> >
> >> >What table stores this information, so that I can run the query against it?
> >> >
> >> >Thanks
> >>
> >>
>|||Alerts don't require SQL mail - I've often used them with
smtp mail. You don't need to use the notification part of
the alert and can just setup the response to be executing a
job. Alerts require SQL Agent to be running but not SQL
Mail. You can also download an smtp alerter for SQL Server
from:
http://www.dbmaint.com/SmtpAlerter.asp
But if you just don't want to use alerts, you essentially
would need to do the same thing setting up an alert would do
- you'd have to set the error up to be logged and then read
the log to find the error. If you find the error in the log,
then send out the email.
To log the error, you would use sp_altermessage.
For example, If I wanted the error for invalid object name
to be logged (error 208), I would use:
sp_altermessage 208, 'WITH_LOG', 'true'
If you query sysmessages before and after executing this,
you'll notice the dlevel value will change to reflect the
logging of this error.
Then whenever that error occurs, it will be logged. So you
would write or use a utility to read the log and search for
the string: Error: 208
in the SQL Server log or in the description in the
Application Event log. You can then send out the mail,
execute the job to email if you find the string.
And in terms of if you define an alert, the occurrence is of
the error is indicated in msdb..sysalerts. Last occurrence
and occurrence count are stored in sysalerts. You could also
work with this table but that would require setting up an
alert. You could use sp_update_alert to reset any of the
values for the occurrence of the alert.
I can't think of what else you would have for options in how
to do this but that hopefully covers most of the ways or
gives you enough information on how you could handle it.
-Sue
On Wed, 23 Feb 2005 06:07:07 -0800, "Pari"
<Pari@.discussions.microsoft.com> wrote:
>I am using smtp and I cannot really use inbuilt sql alerts, as that requires
>sql mail usage. Is there a way I can query the errors that occur so that I
>can send out smtp e-mails?
>Thanks
>Paresh.
>"Sue Hoegemeier" wrote:
>> Not stored in any table.
>> -Sue
>> On Tue, 22 Feb 2005 14:57:08 -0800, "Pari"
>> <Pari@.discussions.microsoft.com> wrote:
>> >No I do not want to create alerts, I just want to find out where and in which
>> >table sql server stores the errors that occur?
>> >
>> >
>> >
>> >"Sue Hoegemeier" wrote:
>> >
>> >> When the error occurs, you want it to be logged. That's how
>> >> you know the error happened. Otherwise, they aren't really
>> >> stored anywhere.
>> >> You can create an alert that will watch for the error and in
>> >> response to the error, it can execute a job. That's sounds
>> >> like what you are looking for. You can find more information
>> >> on alerts in SQL Server books online.
>> >>
>> >> -Sue
>> >>
>> >> On Tue, 22 Feb 2005 14:03:09 -0800, "Pari"
>> >> <Pari@.discussions.microsoft.com> wrote:
>> >>
>> >> >Hi
>> >> >I am trying to create a job to monitor the error log in sql server. I
>> >> >understand that the error types with their severity are in the table
>> >> >master..sysmessages.
>> >> >
>> >> >However, which table to I frequently query or monitor so that whenever the
>> >> >error which I am looking for with specific severity, when occurs the job will
>> >> >be fired?
>> >> >
>> >> >What table stores this information, so that I can run the query against it?
>> >> >
>> >> >Thanks
>> >>
>> >>
>>
Master..sysaltfiles contains incorrect info
Dear all,
I had a database which contained 2 log files, one of the log files was
deleted - however, master..sysaltfiles still shows 2 log files in
existance...
The database is open, and working OK, so second log file is definately not
being used ( it no longer exists on disk)...
So how do I update master..sysaltfiles to be correct ?
I restarted SQL Server, did not resolve issue ( ie sysaltfiles still shows 2
files) . I'm loathe to do a direct delete on master..sysaltfiles until I can
discover if this kind of 'corruption' is not unknown... but do I have any
other choice ?
Hi
I don't know why you have got the extra entry, was there any message in the
SQL Server log when you re-started?
What happens when you try to remove the file using:
ALTER DATABASE database REMOVE FILE logical_file_name
John
"SteveH" <SteveH@.discussions.microsoft.com> wrote in message
news:7C75A085-7563-4D94-9E50-353ECAFF820B@.microsoft.com...
> Dear all,
> I had a database which contained 2 log files, one of the log files was
> deleted - however, master..sysaltfiles still shows 2 log files in
> existance...
> The database is open, and working OK, so second log file is definately not
> being used ( it no longer exists on disk)...
> So how do I update master..sysaltfiles to be correct ?
> I restarted SQL Server, did not resolve issue ( ie sysaltfiles still shows
> 2
> files) . I'm loathe to do a direct delete on master..sysaltfiles until I
> can
> discover if this kind of 'corruption' is not unknown... but do I have any
> other choice ?
I had a database which contained 2 log files, one of the log files was
deleted - however, master..sysaltfiles still shows 2 log files in
existance...
The database is open, and working OK, so second log file is definately not
being used ( it no longer exists on disk)...
So how do I update master..sysaltfiles to be correct ?
I restarted SQL Server, did not resolve issue ( ie sysaltfiles still shows 2
files) . I'm loathe to do a direct delete on master..sysaltfiles until I can
discover if this kind of 'corruption' is not unknown... but do I have any
other choice ?
Hi
I don't know why you have got the extra entry, was there any message in the
SQL Server log when you re-started?
What happens when you try to remove the file using:
ALTER DATABASE database REMOVE FILE logical_file_name
John
"SteveH" <SteveH@.discussions.microsoft.com> wrote in message
news:7C75A085-7563-4D94-9E50-353ECAFF820B@.microsoft.com...
> Dear all,
> I had a database which contained 2 log files, one of the log files was
> deleted - however, master..sysaltfiles still shows 2 log files in
> existance...
> The database is open, and working OK, so second log file is definately not
> being used ( it no longer exists on disk)...
> So how do I update master..sysaltfiles to be correct ?
> I restarted SQL Server, did not resolve issue ( ie sysaltfiles still shows
> 2
> files) . I'm loathe to do a direct delete on master..sysaltfiles until I
> can
> discover if this kind of 'corruption' is not unknown... but do I have any
> other choice ?
Master..sysaltfiles contains incorrect info
Dear all,
I had a database which contained 2 log files, one of the log files was
deleted - however, master..sysaltfiles still shows 2 log files in
existance...
The database is open, and working OK, so second log file is definately not
being used ( it no longer exists on disk)...
So how do I update master..sysaltfiles to be correct ?
I restarted SQL Server, did not resolve issue ( ie sysaltfiles still shows 2
files) . I'm loathe to do a direct delete on master..sysaltfiles until I can
discover if this kind of 'corruption' is not unknown... but do I have any
other choice ?Hi
I don't know why you have got the extra entry, was there any message in the
SQL Server log when you re-started?
What happens when you try to remove the file using:
ALTER DATABASE database REMOVE FILE logical_file_name
John
"SteveH" <SteveH@.discussions.microsoft.com> wrote in message
news:7C75A085-7563-4D94-9E50-353ECAFF820B@.microsoft.com...
> Dear all,
> I had a database which contained 2 log files, one of the log files was
> deleted - however, master..sysaltfiles still shows 2 log files in
> existance...
> The database is open, and working OK, so second log file is definately not
> being used ( it no longer exists on disk)...
> So how do I update master..sysaltfiles to be correct ?
> I restarted SQL Server, did not resolve issue ( ie sysaltfiles still shows
> 2
> files) . I'm loathe to do a direct delete on master..sysaltfiles until I
> can
> discover if this kind of 'corruption' is not unknown... but do I have any
> other choice ?
I had a database which contained 2 log files, one of the log files was
deleted - however, master..sysaltfiles still shows 2 log files in
existance...
The database is open, and working OK, so second log file is definately not
being used ( it no longer exists on disk)...
So how do I update master..sysaltfiles to be correct ?
I restarted SQL Server, did not resolve issue ( ie sysaltfiles still shows 2
files) . I'm loathe to do a direct delete on master..sysaltfiles until I can
discover if this kind of 'corruption' is not unknown... but do I have any
other choice ?Hi
I don't know why you have got the extra entry, was there any message in the
SQL Server log when you re-started?
What happens when you try to remove the file using:
ALTER DATABASE database REMOVE FILE logical_file_name
John
"SteveH" <SteveH@.discussions.microsoft.com> wrote in message
news:7C75A085-7563-4D94-9E50-353ECAFF820B@.microsoft.com...
> Dear all,
> I had a database which contained 2 log files, one of the log files was
> deleted - however, master..sysaltfiles still shows 2 log files in
> existance...
> The database is open, and working OK, so second log file is definately not
> being used ( it no longer exists on disk)...
> So how do I update master..sysaltfiles to be correct ?
> I restarted SQL Server, did not resolve issue ( ie sysaltfiles still shows
> 2
> files) . I'm loathe to do a direct delete on master..sysaltfiles until I
> can
> discover if this kind of 'corruption' is not unknown... but do I have any
> other choice ?
Master..sysaltfiles contains incorrect info
Dear all,
I had a database which contained 2 log files, one of the log files was
deleted - however, master..sysaltfiles still shows 2 log files in
existance...
The database is open, and working OK, so second log file is definately not
being used ( it no longer exists on disk)...
So how do I update master..sysaltfiles to be correct ?
I restarted SQL Server, did not resolve issue ( ie sysaltfiles still shows 2
files) . I'm loathe to do a direct delete on master..sysaltfiles until I can
discover if this kind of 'corruption' is not unknown... but do I have any
other choice ?Hi
I don't know why you have got the extra entry, was there any message in the
SQL Server log when you re-started?
What happens when you try to remove the file using:
ALTER DATABASE database REMOVE FILE logical_file_name
John
"SteveH" <SteveH@.discussions.microsoft.com> wrote in message
news:7C75A085-7563-4D94-9E50-353ECAFF820B@.microsoft.com...
> Dear all,
> I had a database which contained 2 log files, one of the log files was
> deleted - however, master..sysaltfiles still shows 2 log files in
> existance...
> The database is open, and working OK, so second log file is definately not
> being used ( it no longer exists on disk)...
> So how do I update master..sysaltfiles to be correct ?
> I restarted SQL Server, did not resolve issue ( ie sysaltfiles still shows
> 2
> files) . I'm loathe to do a direct delete on master..sysaltfiles until I
> can
> discover if this kind of 'corruption' is not unknown... but do I have any
> other choice ?
I had a database which contained 2 log files, one of the log files was
deleted - however, master..sysaltfiles still shows 2 log files in
existance...
The database is open, and working OK, so second log file is definately not
being used ( it no longer exists on disk)...
So how do I update master..sysaltfiles to be correct ?
I restarted SQL Server, did not resolve issue ( ie sysaltfiles still shows 2
files) . I'm loathe to do a direct delete on master..sysaltfiles until I can
discover if this kind of 'corruption' is not unknown... but do I have any
other choice ?Hi
I don't know why you have got the extra entry, was there any message in the
SQL Server log when you re-started?
What happens when you try to remove the file using:
ALTER DATABASE database REMOVE FILE logical_file_name
John
"SteveH" <SteveH@.discussions.microsoft.com> wrote in message
news:7C75A085-7563-4D94-9E50-353ECAFF820B@.microsoft.com...
> Dear all,
> I had a database which contained 2 log files, one of the log files was
> deleted - however, master..sysaltfiles still shows 2 log files in
> existance...
> The database is open, and working OK, so second log file is definately not
> being used ( it no longer exists on disk)...
> So how do I update master..sysaltfiles to be correct ?
> I restarted SQL Server, did not resolve issue ( ie sysaltfiles still shows
> 2
> files) . I'm loathe to do a direct delete on master..sysaltfiles until I
> can
> discover if this kind of 'corruption' is not unknown... but do I have any
> other choice ?
Master, Tempdb, Msdb, Model?
I am looking for an opinion here...
What do most people do about these databases, leave the data & log file on the same drive
-or-
Move the log files to a separate drive?
I have the info on how to move the log files, and I do place my user database & logs on separate drives, but I am not sure of what a "best practice" is for the Sql system databases.
Any opinions are appreciated!
I would put the temp DB on a seperate drive as well
http://sqlservercode.blogspot.com/
"JLS" wrote:
> I am looking for an opinion here...
> What do most people do about these databases, leave the data & log file on the same drive
> -or-
> Move the log files to a separate drive?
> I have the info on how to move the log files, and I do place my user database & logs on separate drives, but I am not sure of what a "best practice" is for the Sql system databases.
> Any opinions are appreciated!
>
|||And also move the log file to a separate file
"JLS" wrote:
> I am looking for an opinion here...
> What do most people do about these databases, leave the data & log file on the same drive
> -or-
> Move the log files to a separate drive?
> I have the info on how to move the log files, and I do place my user database & logs on separate drives, but I am not sure of what a "best practice" is for the Sql system databases.
> Any opinions are appreciated!
>
|||I use to have data and log on same drive for system databases. The possible exception is tempdb.
This can be a high-activity database, so same reasoning goes for tempdb as for other databases.
Also, moving database files for system databases is a bit special, so read KB 224071.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23nzSw2PxFHA.2132@.TK2MSFTNGP15.phx.gbl...
I am looking for an opinion here...
What do most people do about these databases, leave the data & log file on the same drive
-or-
Move the log files to a separate drive?
I have the info on how to move the log files, and I do place my user database & logs on separate
drives, but I am not sure of what a "best practice" is for the Sql system databases.
Any opinions are appreciated!
|||Thanx everyone!
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23nzSw2PxFHA.2132@.TK2MSFTNGP15.phx.gbl...
I am looking for an opinion here...
What do most people do about these databases, leave the data & log file on the same drive
-or-
Move the log files to a separate drive?
I have the info on how to move the log files, and I do place my user database & logs on separate drives, but I am not sure of what a "best practice" is for the Sql system databases.
Any opinions are appreciated!
What do most people do about these databases, leave the data & log file on the same drive
-or-
Move the log files to a separate drive?
I have the info on how to move the log files, and I do place my user database & logs on separate drives, but I am not sure of what a "best practice" is for the Sql system databases.
Any opinions are appreciated!
I would put the temp DB on a seperate drive as well
http://sqlservercode.blogspot.com/
"JLS" wrote:
> I am looking for an opinion here...
> What do most people do about these databases, leave the data & log file on the same drive
> -or-
> Move the log files to a separate drive?
> I have the info on how to move the log files, and I do place my user database & logs on separate drives, but I am not sure of what a "best practice" is for the Sql system databases.
> Any opinions are appreciated!
>
|||And also move the log file to a separate file
"JLS" wrote:
> I am looking for an opinion here...
> What do most people do about these databases, leave the data & log file on the same drive
> -or-
> Move the log files to a separate drive?
> I have the info on how to move the log files, and I do place my user database & logs on separate drives, but I am not sure of what a "best practice" is for the Sql system databases.
> Any opinions are appreciated!
>
|||I use to have data and log on same drive for system databases. The possible exception is tempdb.
This can be a high-activity database, so same reasoning goes for tempdb as for other databases.
Also, moving database files for system databases is a bit special, so read KB 224071.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23nzSw2PxFHA.2132@.TK2MSFTNGP15.phx.gbl...
I am looking for an opinion here...
What do most people do about these databases, leave the data & log file on the same drive
-or-
Move the log files to a separate drive?
I have the info on how to move the log files, and I do place my user database & logs on separate
drives, but I am not sure of what a "best practice" is for the Sql system databases.
Any opinions are appreciated!
|||Thanx everyone!
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23nzSw2PxFHA.2132@.TK2MSFTNGP15.phx.gbl...
I am looking for an opinion here...
What do most people do about these databases, leave the data & log file on the same drive
-or-
Move the log files to a separate drive?
I have the info on how to move the log files, and I do place my user database & logs on separate drives, but I am not sure of what a "best practice" is for the Sql system databases.
Any opinions are appreciated!
Master, Tempdb, Msdb, Model?
I am looking for an opinion here...
What do most people do about these databases, leave the data & log file on t
he same drive
-or-
Move the log files to a separate drive?
I have the info on how to move the log files, and I do place my user databas
e & logs on separate drives, but I am not sure of what a "best practice" is
for the Sql system databases.
Any opinions are appreciated!I would put the temp DB on a seperate drive as well
http://sqlservercode.blogspot.com/
"JLS" wrote:
> I am looking for an opinion here...
> What do most people do about these databases, leave the data & log file on
the same drive
> -or-
> Move the log files to a separate drive?
> I have the info on how to move the log files, and I do place my user datab
ase & logs on separate drives, but I am not sure of what a "best practice" i
s for the Sql system databases.
> Any opinions are appreciated!
>|||And also move the log file to a separate file
"JLS" wrote:
> I am looking for an opinion here...
> What do most people do about these databases, leave the data & log file on
the same drive
> -or-
> Move the log files to a separate drive?
> I have the info on how to move the log files, and I do place my user datab
ase & logs on separate drives, but I am not sure of what a "best practice" i
s for the Sql system databases.
> Any opinions are appreciated!
>|||I use to have data and log on same drive for system databases. The possible
exception is tempdb.
This can be a high-activity database, so same reasoning goes for tempdb as f
or other databases.
Also, moving database files for system databases is a bit special, so read K
B 224071.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23nzSw2PxFHA.2132@.TK2MSFT
NGP15.phx.gbl...
I am looking for an opinion here...
What do most people do about these databases, leave the data & log file on t
he same drive
-or-
Move the log files to a separate drive?
I have the info on how to move the log files, and I do place my user databas
e & logs on separate
drives, but I am not sure of what a "best practice" is for the Sql system da
tabases.
Any opinions are appreciated!|||Thanx everyone!
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23nzSw2PxFHA.2132@.TK2MSFT
NGP15.phx.gbl...
I am looking for an opinion here...
What do most people do about these databases, leave the data & log file on t
he same drive
-or-
Move the log files to a separate drive?
I have the info on how to move the log files, and I do place my user databas
e & logs on separate drives, but I am not sure of what a "best practice" is
for the Sql system databases.
Any opinions are appreciated!
What do most people do about these databases, leave the data & log file on t
he same drive
-or-
Move the log files to a separate drive?
I have the info on how to move the log files, and I do place my user databas
e & logs on separate drives, but I am not sure of what a "best practice" is
for the Sql system databases.
Any opinions are appreciated!I would put the temp DB on a seperate drive as well
http://sqlservercode.blogspot.com/
"JLS" wrote:
> I am looking for an opinion here...
> What do most people do about these databases, leave the data & log file on
the same drive
> -or-
> Move the log files to a separate drive?
> I have the info on how to move the log files, and I do place my user datab
ase & logs on separate drives, but I am not sure of what a "best practice" i
s for the Sql system databases.
> Any opinions are appreciated!
>|||And also move the log file to a separate file
"JLS" wrote:
> I am looking for an opinion here...
> What do most people do about these databases, leave the data & log file on
the same drive
> -or-
> Move the log files to a separate drive?
> I have the info on how to move the log files, and I do place my user datab
ase & logs on separate drives, but I am not sure of what a "best practice" i
s for the Sql system databases.
> Any opinions are appreciated!
>|||I use to have data and log on same drive for system databases. The possible
exception is tempdb.
This can be a high-activity database, so same reasoning goes for tempdb as f
or other databases.
Also, moving database files for system databases is a bit special, so read K
B 224071.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23nzSw2PxFHA.2132@.TK2MSFT
NGP15.phx.gbl...
I am looking for an opinion here...
What do most people do about these databases, leave the data & log file on t
he same drive
-or-
Move the log files to a separate drive?
I have the info on how to move the log files, and I do place my user databas
e & logs on separate
drives, but I am not sure of what a "best practice" is for the Sql system da
tabases.
Any opinions are appreciated!|||Thanx everyone!
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23nzSw2PxFHA.2132@.TK2MSFT
NGP15.phx.gbl...
I am looking for an opinion here...
What do most people do about these databases, leave the data & log file on t
he same drive
-or-
Move the log files to a separate drive?
I have the info on how to move the log files, and I do place my user databas
e & logs on separate drives, but I am not sure of what a "best practice" is
for the Sql system databases.
Any opinions are appreciated!
Master Transaction Log backup fails
:eek:
Okay. I changed the times that the transaction logs are backed up, via the built in maintenance schedule. It was then that I started to get failures only on the Transaction log backup for the master. The error that I get from the history log is Backup can not be performed on this database. This sub task is ignored. If I look in the file that is saved to disk :
Starting maintenance plan 'DB Maintenance Plan1' on 09/11/2004 02:30:00
Backup can not be performed on database 'master'. This sub task is ignored.
[1] Database onyx: Transaction Log Backup...
Destination: [R:\BACKUP\onyx\onyx_tlog_200411090230.TRN]
** Execution Time: 0 hrs, 0 mins, 23 secs **
[2] Database onyx: Verifying Backup...
** Execution Time: 0 hrs, 0 mins, 8 secs **
Deleting old text reports... 1 file(s) deleted.
End of maintenance plan 'DB Maintenance Plan1' on 09/11/2004 02:30:31
SQLMAINT.EXE Process Exit Code: 1 (Failed)
I changed the backup back to its original time as this was the only change made. This has not resolved the problem. As I am new to SQL and still finding my feet All the other SQL maintenance plans that I changed are working fine.
Thanks
SteveCheck to make sure the database isnt in simple recovery mode. This would make transaction log backups fail. By default you cannot do a transaction log backup on the master database.
If you dont know how to check, do the following:
1. Right click the master database and click properties.
2. Click on the options tab.
3. Make sure recovery mode is set to full if you want to backup the transaction log.
-Mike|||The transaction log for the master database can not be backed up, or restored. You should do FULL backups of the master database, but transaction backups will not work.|||Actually, yes, you are correct. I dont know why I didnt remember this. Just do full backups instead of transaction log backups. The master database is usually very small so backups dont take a lot of disk space and full backups are much easier to restore.
-Mike|||No shame in that, Mike. I actually had a Microsoft technician tell me to put tempdb in Full Recovery mode, once. I don't know if he still works in the SQL Server group, anymore.|||;) Gents,
Thank youi for your help. I have looked into the issue and have submitted a few concerns to my management.
Many thanks
Okay. I changed the times that the transaction logs are backed up, via the built in maintenance schedule. It was then that I started to get failures only on the Transaction log backup for the master. The error that I get from the history log is Backup can not be performed on this database. This sub task is ignored. If I look in the file that is saved to disk :
Starting maintenance plan 'DB Maintenance Plan1' on 09/11/2004 02:30:00
Backup can not be performed on database 'master'. This sub task is ignored.
[1] Database onyx: Transaction Log Backup...
Destination: [R:\BACKUP\onyx\onyx_tlog_200411090230.TRN]
** Execution Time: 0 hrs, 0 mins, 23 secs **
[2] Database onyx: Verifying Backup...
** Execution Time: 0 hrs, 0 mins, 8 secs **
Deleting old text reports... 1 file(s) deleted.
End of maintenance plan 'DB Maintenance Plan1' on 09/11/2004 02:30:31
SQLMAINT.EXE Process Exit Code: 1 (Failed)
I changed the backup back to its original time as this was the only change made. This has not resolved the problem. As I am new to SQL and still finding my feet All the other SQL maintenance plans that I changed are working fine.
Thanks
SteveCheck to make sure the database isnt in simple recovery mode. This would make transaction log backups fail. By default you cannot do a transaction log backup on the master database.
If you dont know how to check, do the following:
1. Right click the master database and click properties.
2. Click on the options tab.
3. Make sure recovery mode is set to full if you want to backup the transaction log.
-Mike|||The transaction log for the master database can not be backed up, or restored. You should do FULL backups of the master database, but transaction backups will not work.|||Actually, yes, you are correct. I dont know why I didnt remember this. Just do full backups instead of transaction log backups. The master database is usually very small so backups dont take a lot of disk space and full backups are much easier to restore.
-Mike|||No shame in that, Mike. I actually had a Microsoft technician tell me to put tempdb in Full Recovery mode, once. I don't know if he still works in the SQL Server group, anymore.|||;) Gents,
Thank youi for your help. I have looked into the issue and have submitted a few concerns to my management.
Many thanks
Master transaction log
Quick question - why is it not possible to backup the master database's transaction log
TIA
John BrownJohn Brown wrote:
> Quick question - why is it not possible to backup the master
> database's transaction log? TIA,
> John Brown
See:
PRB: Transaction Log Backups of Master Database Are Not Allowed
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/q285/2/88.asp&NoWebContent=1
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks Sebastian, this is helpful, but what I wondered is is there any logical reason why a master transaction log backup is not possible
Regards
Joh|||John Brown wrote:
> Thanks Sebastian, this is helpful, but what I wondered is is there
> any logical reason why a master transaction log backup is not
> possible? Regards,
Yes, it is. Master database is setup in the SIMPLE recovery mode, when
transaction log is truncated on checkpoints and its space occupied by
commited transactions is constantly reused. That is why SIMPLE recovery
model does not allow transaction logs backups. It is a small database with
only a few transactions from time to time, SIMPLE recovery model is optimal
for this system database.
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.
TIA
John BrownJohn Brown wrote:
> Quick question - why is it not possible to backup the master
> database's transaction log? TIA,
> John Brown
See:
PRB: Transaction Log Backups of Master Database Are Not Allowed
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/q285/2/88.asp&NoWebContent=1
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks Sebastian, this is helpful, but what I wondered is is there any logical reason why a master transaction log backup is not possible
Regards
Joh|||John Brown wrote:
> Thanks Sebastian, this is helpful, but what I wondered is is there
> any logical reason why a master transaction log backup is not
> possible? Regards,
Yes, it is. Master database is setup in the SIMPLE recovery mode, when
transaction log is truncated on checkpoints and its space occupied by
commited transactions is constantly reused. That is why SIMPLE recovery
model does not allow transaction logs backups. It is a small database with
only a few transactions from time to time, SIMPLE recovery model is optimal
for this system database.
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.
Master transaction log
Quick question - why is it not possible to backup the master database's tran
saction log?
TIA,
John BrownJohn Brown wrote:
> Quick question - why is it not possible to backup the master
> database's transaction log? TIA,
> John Brown
See:
PRB: Transaction Log Backups of Master Database Are Not Allowed
http://support.microsoft.com/defaul...&NoWebContent=1
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks Sebastian, this is helpful, but what I wondered is is there any logic
al reason why a master transaction log backup is not possible?
Regards,
John|||John Brown wrote:
> Thanks Sebastian, this is helpful, but what I wondered is is there
> any logical reason why a master transaction log backup is not
> possible? Regards,
Yes, it is. Master database is setup in the SIMPLE recovery mode, when
transaction log is truncated on checkpoints and its space occupied by
commited transactions is constantly reused. That is why SIMPLE recovery
model does not allow transaction logs backups. It is a small database with
only a few transactions from time to time, SIMPLE recovery model is optimal
for this system database.
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.
saction log?
TIA,
John BrownJohn Brown wrote:
> Quick question - why is it not possible to backup the master
> database's transaction log? TIA,
> John Brown
See:
PRB: Transaction Log Backups of Master Database Are Not Allowed
http://support.microsoft.com/defaul...&NoWebContent=1
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks Sebastian, this is helpful, but what I wondered is is there any logic
al reason why a master transaction log backup is not possible?
Regards,
John|||John Brown wrote:
> Thanks Sebastian, this is helpful, but what I wondered is is there
> any logical reason why a master transaction log backup is not
> possible? Regards,
Yes, it is. Master database is setup in the SIMPLE recovery mode, when
transaction log is truncated on checkpoints and its space occupied by
commited transactions is constantly reused. That is why SIMPLE recovery
model does not allow transaction logs backups. It is a small database with
only a few transactions from time to time, SIMPLE recovery model is optimal
for this system database.
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to:
Posts (Atom)