Database Name

Hi,

Can anyone tell me how to return the current database name using T-SQL.

I have a stored proc that runs in a number of databases and would like qualify some dynamic sql with the database name. I know DB_Name exists but requires the DB_ID and DB_ID needs the DB_Name. Is there a function that just returns the current database i.e.

USE TestDB

GO

SELECT currentDBName?

Use the following function..

Select db_name()

|||

Cheers...

sql

Database Mirrororing Monitor - error retrieving data

Hello,
I can't see data for mirror database in Database Mirrororing Monitor.
The mirroring is configured and runs properly between 2 locations. I can
even register mirrored database with both peers - it works the same with
principal and mirrored databases in registration process. I'm using windows
authentication and the account has sysadmin role in both server instances.
However upon registration, the principal database appears in state
Synchronized while mirror database appears with "Error retrieving data" in
Current Role, Mirroring State and Witness Connections columns.
Both SQL servers are 64-bit Enterprise 2005 SP2 versions on 64-bit Windows
Server 2003 SP2.
Could someone help me to setup the monitoring?
Thanks a lot
eXavierHello eXavier,
Can you please answer the following questions:
1. What version of SQL Server 2005 are you running? The build number
resembles
9.0.xxxx.
2. Can you please zip and send me the error logs for each of the servers.
3. Run the following SQL statements and please return the results to me in
an
output file
a. Select * from sys.databases (run this on principle and mirror server)
b. Select * from sys.database_mirroring (run this on principle and mirror
server)
c. Select * from sys.database_mirroring_witnesses (run this on the witness
server)
d. Select * from sys.database_mirroring_endpoints (run on all servers)
e. Select * from sys.tcp_endpoints (run on all servers)
f. Select * from sys.server_principals (run on all servers)
g. Select * from sys.database_recovery_status (run on principal and mirror
server)
h. Select * from msdb.dbo.backupset (run on principal server only)
i. Select * from sys.dm_os_wait_stats (run on principal and mirror servers)
j. Select * from sys.sysprocesses (run on all servers)
k. Verify the connectivity between the servers using Telnet and send the
results.
To do this:
l. Open a command prompt.
m. Run telnet <machine name> <port number> where machine name is the fully
qualified domain name and port number is the port number for the mirroring
endpoint.
n. Run this from the principal to the mirror and from the mirror to the
principal.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.sql

Database Maintenance Plan -> SQLMAINT.exe Error

Having a problem with a Database Maintenance Plan that runs a tran log
backup. The job has been running successfully until very recently. (The
failures seem to coincide with the installation of the Feb 2005 CTP.) When
the job runs, it fails almost immediately. No report is generated. No
Maintenance Plan History is recorded in the Plan History Window. The only
info that gets recorded is that the step failed in the Sql Agent Job history
for the job.
Here is output from running it directly from Query Analyzer.
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
8C5C65BA-47A0-4605-8768-EA414FFE829A -Rpt "C:\MSSQL\Log\Backup\Transaction
Log Bkup6.txt" -DelTxtRpt 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia
DISK -BkUpLog "E:\MSSQL\DATA" -DelBkUps 4DAYS -CrBkSubDir -BkExt "TRN"'
/* -- definitely finds the 8.0 (Sql 2000) utility (somehow! - since it is
not in the path and not in current directory)
-- but then that util fails - probably a dll overlaided by the sql2005 beta
install
output
-----------
NULL
Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.760
Copyright (C) Microsoft Corporation, 1995 - 1998
NULL
NULL
(5 row(s) affected)
Server: Msg 22029, Level 16, State 1, Line 0
sqlmaint.exe failed.
Not much info there, huh?
Any help would be appreciated.
Check to make sure the db is not in simple recovery mode.
Andrew J. Kelly SQL MVP
"Frank Castora" <fcc8b_sqlserver@.hotmail.com> wrote in message
news:Oi%23d6A$IFHA.3916@.TK2MSFTNGP14.phx.gbl...
> Having a problem with a Database Maintenance Plan that runs a tran log
> backup. The job has been running successfully until very recently. (The
> failures seem to coincide with the installation of the Feb 2005 CTP.) When
> the job runs, it fails almost immediately. No report is generated. No
> Maintenance Plan History is recorded in the Plan History Window. The only
> info that gets recorded is that the step failed in the Sql Agent Job
> history for the job.
> Here is output from running it directly from Query Analyzer.
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> 8C5C65BA-47A0-4605-8768-EA414FFE829A -Rpt "C:\MSSQL\Log\Backup\Transaction
> Log Bkup6.txt" -DelTxtRpt 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia
> DISK -BkUpLog "E:\MSSQL\DATA" -DelBkUps 4DAYS -CrBkSubDir -BkExt "TRN"'
> /* -- definitely finds the 8.0 (Sql 2000) utility (somehow! - since it is
> not in the path and not in current directory)
> -- but then that util fails - probably a dll overlaided by the sql2005
> beta install
> output
> ----------
--
> NULL
> Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.760
> Copyright (C) Microsoft Corporation, 1995 - 1998
> NULL
> NULL
> (5 row(s) affected)
> Server: Msg 22029, Level 16, State 1, Line 0
> sqlmaint.exe failed.
> Not much info there, huh?
> Any help would be appreciated.
>
|||Checked that already, all dbs are in full. It appears as if the job isnt
even getting off the ground. It returns failure in a few seconds (< 5
seconds). Previously, if we had included a DB in simple mode in the job, the
job would run, but exit with a return code of 1 and a report would be
created. It appears the job is failing before it tried to hit any of the
databases.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eTz5ke$IFHA.2356@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Check to make sure the db is not in simple recovery mode.
> --
> Andrew J. Kelly SQL MVP
>
> "Frank Castora" <fcc8b_sqlserver@.hotmail.com> wrote in message
> news:Oi%23d6A$IFHA.3916@.TK2MSFTNGP14.phx.gbl...
--
>
|||I solved the problem. It appears that the uninstallation of Sql server 2005
interferes/unregisters the sqldmo.dll library. I reregistered the DLL and
all is well. Thanks!
"Frank Castora" <fcc8b_sqlserver@.hotmail.com> wrote in message
news:Oi%23d6A$IFHA.3916@.TK2MSFTNGP14.phx.gbl...
> Having a problem with a Database Maintenance Plan that runs a tran log
> backup. The job has been running successfully until very recently. (The
> failures seem to coincide with the installation of the Feb 2005 CTP.) When
> the job runs, it fails almost immediately. No report is generated. No
> Maintenance Plan History is recorded in the Plan History Window. The only
> info that gets recorded is that the step failed in the Sql Agent Job
> history for the job.
> Here is output from running it directly from Query Analyzer.
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> 8C5C65BA-47A0-4605-8768-EA414FFE829A -Rpt "C:\MSSQL\Log\Backup\Transaction
> Log Bkup6.txt" -DelTxtRpt 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia
> DISK -BkUpLog "E:\MSSQL\DATA" -DelBkUps 4DAYS -CrBkSubDir -BkExt "TRN"'
> /* -- definitely finds the 8.0 (Sql 2000) utility (somehow! - since it is
> not in the path and not in current directory)
> -- but then that util fails - probably a dll overlaided by the sql2005
> beta install
> output
> ----------
--
> NULL
> Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.760
> Copyright (C) Microsoft Corporation, 1995 - 1998
> NULL
> NULL
> (5 row(s) affected)
> Server: Msg 22029, Level 16, State 1, Line 0
> sqlmaint.exe failed.
> Not much info there, huh?
> Any help would be appreciated.
>
|||You should NOT be running SQL2005 on a production server. I won't even run
it in anything other than a virtual server myself.
Andrew J. Kelly SQL MVP
"Frank Castora" <fcc8b_sqlserver@.hotmail.com> wrote in message
news:eBiPnu$IFHA.1476@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
>I solved the problem. It appears that the uninstallation of Sql server
>2005 interferes/unregisters the sqldmo.dll library. I reregistered the DLL
>and all is well. Thanks!
> "Frank Castora" <fcc8b_sqlserver@.hotmail.com> wrote in message
> news:Oi%23d6A$IFHA.3916@.TK2MSFTNGP14.phx.gbl...
--
>
|||This is a DEV server, not PROD.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:udfY5pFJFHA.3064@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> You should NOT be running SQL2005 on a production server. I won't even
> run it in anything other than a virtual server myself.
> --
> Andrew J. Kelly SQL MVP
>
> "Frank Castora" <fcc8b_sqlserver@.hotmail.com> wrote in message
> news:eBiPnu$IFHA.1476@.TK2MSFTNGP09.phx.gbl...
--
>

Database Maintenance Plan -> SQLMAINT.exe Error

Having a problem with a Database Maintenance Plan that runs a tran log
backup. The job has been running successfully until very recently. (The
failures seem to coincide with the installation of the Feb 2005 CTP.) When
the job runs, it fails almost immediately. No report is generated. No
Maintenance Plan History is recorded in the Plan History Window. The only
info that gets recorded is that the step failed in the Sql Agent Job history
for the job.
Here is output from running it directly from Query Analyzer.
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
8C5C65BA-47A0-4605-8768-EA414FFE829A -Rpt "C:\MSSQL\Log\Backup\Transaction
Log Bkup6.txt" -DelTxtRpt 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia
DISK -BkUpLog "E:\MSSQL\DATA" -DelBkUps 4DAYS -CrBkSubDir -BkExt "TRN"'
/* -- definitely finds the 8.0 (Sql 2000) utility (somehow! - since it is
not in the path and not in current directory)
-- but then that util fails - probably a dll overlaided by the sql2005 beta
install
output
----
----
----
--
NULL
Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.760
Copyright (C) Microsoft Corporation, 1995 - 1998
NULL
NULL
(5 row(s) affected)
Server: Msg 22029, Level 16, State 1, Line 0
sqlmaint.exe failed.
Not much info there, huh?
Any help would be appreciated.Check to make sure the db is not in simple recovery mode.
Andrew J. Kelly SQL MVP
"Frank Castora" <fcc8b_sqlserver@.hotmail.com> wrote in message
news:Oi%23d6A$IFHA.3916@.TK2MSFTNGP14.phx.gbl...
> Having a problem with a Database Maintenance Plan that runs a tran log
> backup. The job has been running successfully until very recently. (The
> failures seem to coincide with the installation of the Feb 2005 CTP.) When
> the job runs, it fails almost immediately. No report is generated. No
> Maintenance Plan History is recorded in the Plan History Window. The only
> info that gets recorded is that the step failed in the Sql Agent Job
> history for the job.
> Here is output from running it directly from Query Analyzer.
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> 8C5C65BA-47A0-4605-8768-EA414FFE829A -Rpt "C:\MSSQL\Log\Backup\Transaction
> Log Bkup6.txt" -DelTxtRpt 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia
> DISK -BkUpLog "E:\MSSQL\DATA" -DelBkUps 4DAYS -CrBkSubDir -BkExt "TRN"'
> /* -- definitely finds the 8.0 (Sql 2000) utility (somehow! - since it is
> not in the path and not in current directory)
> -- but then that util fails - probably a dll overlaided by the sql2005
> beta install
> output
> ----
----
----
--
--
> NULL
> Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.760
> Copyright (C) Microsoft Corporation, 1995 - 1998
> NULL
> NULL
> (5 row(s) affected)
> Server: Msg 22029, Level 16, State 1, Line 0
> sqlmaint.exe failed.
> Not much info there, huh?
> Any help would be appreciated.
>|||Checked that already, all dbs are in full. It appears as if the job isnt
even getting off the ground. It returns failure in a few seconds (< 5
seconds). Previously, if we had included a DB in simple mode in the job, the
job would run, but exit with a return code of 1 and a report would be
created. It appears the job is failing before it tried to hit any of the
databases.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eTz5ke$IFHA.2356@.TK2MSFTNGP12.phx.gbl...
> Check to make sure the db is not in simple recovery mode.
> --
> Andrew J. Kelly SQL MVP
>
> "Frank Castora" <fcc8b_sqlserver@.hotmail.com> wrote in message
> news:Oi%23d6A$IFHA.3916@.TK2MSFTNGP14.phx.gbl...
--[vbcol=seagreen]
>|||I solved the problem. It appears that the uninstallation of Sql server 2005
interferes/unregisters the sqldmo.dll library. I reregistered the DLL and
all is well. Thanks!
"Frank Castora" <fcc8b_sqlserver@.hotmail.com> wrote in message
news:Oi%23d6A$IFHA.3916@.TK2MSFTNGP14.phx.gbl...
> Having a problem with a Database Maintenance Plan that runs a tran log
> backup. The job has been running successfully until very recently. (The
> failures seem to coincide with the installation of the Feb 2005 CTP.) When
> the job runs, it fails almost immediately. No report is generated. No
> Maintenance Plan History is recorded in the Plan History Window. The only
> info that gets recorded is that the step failed in the Sql Agent Job
> history for the job.
> Here is output from running it directly from Query Analyzer.
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> 8C5C65BA-47A0-4605-8768-EA414FFE829A -Rpt "C:\MSSQL\Log\Backup\Transaction
> Log Bkup6.txt" -DelTxtRpt 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia
> DISK -BkUpLog "E:\MSSQL\DATA" -DelBkUps 4DAYS -CrBkSubDir -BkExt "TRN"'
> /* -- definitely finds the 8.0 (Sql 2000) utility (somehow! - since it is
> not in the path and not in current directory)
> -- but then that util fails - probably a dll overlaided by the sql2005
> beta install
> output
> ----
----
----
--
--
> NULL
> Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.760
> Copyright (C) Microsoft Corporation, 1995 - 1998
> NULL
> NULL
> (5 row(s) affected)
> Server: Msg 22029, Level 16, State 1, Line 0
> sqlmaint.exe failed.
> Not much info there, huh?
> Any help would be appreciated.
>|||You should NOT be running SQL2005 on a production server. I won't even run
it in anything other than a virtual server myself.
Andrew J. Kelly SQL MVP
"Frank Castora" <fcc8b_sqlserver@.hotmail.com> wrote in message
news:eBiPnu$IFHA.1476@.TK2MSFTNGP09.phx.gbl...
>I solved the problem. It appears that the uninstallation of Sql server
>2005 interferes/unregisters the sqldmo.dll library. I reregistered the DLL
>and all is well. Thanks!
> "Frank Castora" <fcc8b_sqlserver@.hotmail.com> wrote in message
> news:Oi%23d6A$IFHA.3916@.TK2MSFTNGP14.phx.gbl...
--[vbcol=seagreen]
>|||This is a DEV server, not PROD.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:udfY5pFJFHA.3064@.TK2MSFTNGP12.phx.gbl...
> You should NOT be running SQL2005 on a production server. I won't even
> run it in anything other than a virtual server myself.
> --
> Andrew J. Kelly SQL MVP
>
> "Frank Castora" <fcc8b_sqlserver@.hotmail.com> wrote in message
> news:eBiPnu$IFHA.1476@.TK2MSFTNGP09.phx.gbl...
--[vbcol=seagreen]
>

Database Maintenance Plan -> SQLMAINT.exe Error

Having a problem with a Database Maintenance Plan that runs a tran log
backup. The job has been running successfully until very recently. (The
failures seem to coincide with the installation of the Feb 2005 CTP.) When
the job runs, it fails almost immediately. No report is generated. No
Maintenance Plan History is recorded in the Plan History Window. The only
info that gets recorded is that the step failed in the Sql Agent Job history
for the job.
Here is output from running it directly from Query Analyzer.
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
8C5C65BA-47A0-4605-8768-EA414FFE829A -Rpt "C:\MSSQL\Log\Backup\Transaction
Log Bkup6.txt" -DelTxtRpt 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia
DISK -BkUpLog "E:\MSSQL\DATA" -DelBkUps 4DAYS -CrBkSubDir -BkExt "TRN"'
/* -- definitely finds the 8.0 (Sql 2000) utility (somehow! - since it is
not in the path and not in current directory)
-- but then that util fails - probably a dll overlaided by the sql2005 beta
install
output
-----------
NULL
Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.760
Copyright (C) Microsoft Corporation, 1995 - 1998
NULL
NULL
(5 row(s) affected)
Server: Msg 22029, Level 16, State 1, Line 0
sqlmaint.exe failed.
Not much info there, huh?
Any help would be appreciated.Check to make sure the db is not in simple recovery mode.
--
Andrew J. Kelly SQL MVP
"Frank Castora" <fcc8b_sqlserver@.hotmail.com> wrote in message
news:Oi%23d6A$IFHA.3916@.TK2MSFTNGP14.phx.gbl...
> Having a problem with a Database Maintenance Plan that runs a tran log
> backup. The job has been running successfully until very recently. (The
> failures seem to coincide with the installation of the Feb 2005 CTP.) When
> the job runs, it fails almost immediately. No report is generated. No
> Maintenance Plan History is recorded in the Plan History Window. The only
> info that gets recorded is that the step failed in the Sql Agent Job
> history for the job.
> Here is output from running it directly from Query Analyzer.
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> 8C5C65BA-47A0-4605-8768-EA414FFE829A -Rpt "C:\MSSQL\Log\Backup\Transaction
> Log Bkup6.txt" -DelTxtRpt 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia
> DISK -BkUpLog "E:\MSSQL\DATA" -DelBkUps 4DAYS -CrBkSubDir -BkExt "TRN"'
> /* -- definitely finds the 8.0 (Sql 2000) utility (somehow! - since it is
> not in the path and not in current directory)
> -- but then that util fails - probably a dll overlaided by the sql2005
> beta install
> output
> -----------
> NULL
> Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.760
> Copyright (C) Microsoft Corporation, 1995 - 1998
> NULL
> NULL
> (5 row(s) affected)
> Server: Msg 22029, Level 16, State 1, Line 0
> sqlmaint.exe failed.
> Not much info there, huh?
> Any help would be appreciated.
>|||Checked that already, all dbs are in full. It appears as if the job isnt
even getting off the ground. It returns failure in a few seconds (< 5
seconds). Previously, if we had included a DB in simple mode in the job, the
job would run, but exit with a return code of 1 and a report would be
created. It appears the job is failing before it tried to hit any of the
databases.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eTz5ke$IFHA.2356@.TK2MSFTNGP12.phx.gbl...
> Check to make sure the db is not in simple recovery mode.
> --
> Andrew J. Kelly SQL MVP
>
> "Frank Castora" <fcc8b_sqlserver@.hotmail.com> wrote in message
> news:Oi%23d6A$IFHA.3916@.TK2MSFTNGP14.phx.gbl...
>> Having a problem with a Database Maintenance Plan that runs a tran log
>> backup. The job has been running successfully until very recently. (The
>> failures seem to coincide with the installation of the Feb 2005 CTP.)
>> When the job runs, it fails almost immediately. No report is generated.
>> No Maintenance Plan History is recorded in the Plan History Window. The
>> only info that gets recorded is that the step failed in the Sql Agent Job
>> history for the job.
>> Here is output from running it directly from Query Analyzer.
>> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
>> 8C5C65BA-47A0-4605-8768-EA414FFE829A -Rpt
>> "C:\MSSQL\Log\Backup\Transaction Log Bkup6.txt" -DelTxtRpt
>> 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog
>> "E:\MSSQL\DATA" -DelBkUps 4DAYS -CrBkSubDir -BkExt "TRN"'
>> /* -- definitely finds the 8.0 (Sql 2000) utility (somehow! - since it is
>> not in the path and not in current directory)
>> -- but then that util fails - probably a dll overlaided by the sql2005
>> beta install
>> output
>> -----------
>> NULL
>> Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.760
>> Copyright (C) Microsoft Corporation, 1995 - 1998
>> NULL
>> NULL
>> (5 row(s) affected)
>> Server: Msg 22029, Level 16, State 1, Line 0
>> sqlmaint.exe failed.
>> Not much info there, huh?
>> Any help would be appreciated.
>|||I solved the problem. It appears that the uninstallation of Sql server 2005
interferes/unregisters the sqldmo.dll library. I reregistered the DLL and
all is well. Thanks!
"Frank Castora" <fcc8b_sqlserver@.hotmail.com> wrote in message
news:Oi%23d6A$IFHA.3916@.TK2MSFTNGP14.phx.gbl...
> Having a problem with a Database Maintenance Plan that runs a tran log
> backup. The job has been running successfully until very recently. (The
> failures seem to coincide with the installation of the Feb 2005 CTP.) When
> the job runs, it fails almost immediately. No report is generated. No
> Maintenance Plan History is recorded in the Plan History Window. The only
> info that gets recorded is that the step failed in the Sql Agent Job
> history for the job.
> Here is output from running it directly from Query Analyzer.
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> 8C5C65BA-47A0-4605-8768-EA414FFE829A -Rpt "C:\MSSQL\Log\Backup\Transaction
> Log Bkup6.txt" -DelTxtRpt 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia
> DISK -BkUpLog "E:\MSSQL\DATA" -DelBkUps 4DAYS -CrBkSubDir -BkExt "TRN"'
> /* -- definitely finds the 8.0 (Sql 2000) utility (somehow! - since it is
> not in the path and not in current directory)
> -- but then that util fails - probably a dll overlaided by the sql2005
> beta install
> output
> -----------
> NULL
> Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.760
> Copyright (C) Microsoft Corporation, 1995 - 1998
> NULL
> NULL
> (5 row(s) affected)
> Server: Msg 22029, Level 16, State 1, Line 0
> sqlmaint.exe failed.
> Not much info there, huh?
> Any help would be appreciated.
>|||You should NOT be running SQL2005 on a production server. I won't even run
it in anything other than a virtual server myself.
--
Andrew J. Kelly SQL MVP
"Frank Castora" <fcc8b_sqlserver@.hotmail.com> wrote in message
news:eBiPnu$IFHA.1476@.TK2MSFTNGP09.phx.gbl...
>I solved the problem. It appears that the uninstallation of Sql server
>2005 interferes/unregisters the sqldmo.dll library. I reregistered the DLL
>and all is well. Thanks!
> "Frank Castora" <fcc8b_sqlserver@.hotmail.com> wrote in message
> news:Oi%23d6A$IFHA.3916@.TK2MSFTNGP14.phx.gbl...
>> Having a problem with a Database Maintenance Plan that runs a tran log
>> backup. The job has been running successfully until very recently. (The
>> failures seem to coincide with the installation of the Feb 2005 CTP.)
>> When the job runs, it fails almost immediately. No report is generated.
>> No Maintenance Plan History is recorded in the Plan History Window. The
>> only info that gets recorded is that the step failed in the Sql Agent Job
>> history for the job.
>> Here is output from running it directly from Query Analyzer.
>> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
>> 8C5C65BA-47A0-4605-8768-EA414FFE829A -Rpt
>> "C:\MSSQL\Log\Backup\Transaction Log Bkup6.txt" -DelTxtRpt
>> 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog
>> "E:\MSSQL\DATA" -DelBkUps 4DAYS -CrBkSubDir -BkExt "TRN"'
>> /* -- definitely finds the 8.0 (Sql 2000) utility (somehow! - since it is
>> not in the path and not in current directory)
>> -- but then that util fails - probably a dll overlaided by the sql2005
>> beta install
>> output
>> -----------
>> NULL
>> Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.760
>> Copyright (C) Microsoft Corporation, 1995 - 1998
>> NULL
>> NULL
>> (5 row(s) affected)
>> Server: Msg 22029, Level 16, State 1, Line 0
>> sqlmaint.exe failed.
>> Not much info there, huh?
>> Any help would be appreciated.
>|||This is a DEV server, not PROD. :)
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:udfY5pFJFHA.3064@.TK2MSFTNGP12.phx.gbl...
> You should NOT be running SQL2005 on a production server. I won't even
> run it in anything other than a virtual server myself.
> --
> Andrew J. Kelly SQL MVP
>
> "Frank Castora" <fcc8b_sqlserver@.hotmail.com> wrote in message
> news:eBiPnu$IFHA.1476@.TK2MSFTNGP09.phx.gbl...
>>I solved the problem. It appears that the uninstallation of Sql server
>>2005 interferes/unregisters the sqldmo.dll library. I reregistered the DLL
>>and all is well. Thanks!
>> "Frank Castora" <fcc8b_sqlserver@.hotmail.com> wrote in message
>> news:Oi%23d6A$IFHA.3916@.TK2MSFTNGP14.phx.gbl...
>> Having a problem with a Database Maintenance Plan that runs a tran log
>> backup. The job has been running successfully until very recently. (The
>> failures seem to coincide with the installation of the Feb 2005 CTP.)
>> When the job runs, it fails almost immediately. No report is generated.
>> No Maintenance Plan History is recorded in the Plan History Window. The
>> only info that gets recorded is that the step failed in the Sql Agent
>> Job history for the job.
>> Here is output from running it directly from Query Analyzer.
>> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
>> 8C5C65BA-47A0-4605-8768-EA414FFE829A -Rpt
>> "C:\MSSQL\Log\Backup\Transaction Log Bkup6.txt" -DelTxtRpt
>> 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog
>> "E:\MSSQL\DATA" -DelBkUps 4DAYS -CrBkSubDir -BkExt "TRN"'
>> /* -- definitely finds the 8.0 (Sql 2000) utility (somehow! - since it
>> is not in the path and not in current directory)
>> -- but then that util fails - probably a dll overlaided by the sql2005
>> beta install
>> output
>> -----------
>> NULL
>> Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.760
>> Copyright (C) Microsoft Corporation, 1995 - 1998
>> NULL
>> NULL
>> (5 row(s) affected)
>> Server: Msg 22029, Level 16, State 1, Line 0
>> sqlmaint.exe failed.
>> Not much info there, huh?
>> Any help would be appreciated.
>>
>

database maintenance for companies that run 24/7

For a company like an airline that runs 24/7 and the database is always
being accessed and they can't shut down, how do they do maintenance on their
databases? How do they do things like defrag or rebuild indexes or shrink
files?
Do they have two databases and they switch back and forth?
Thanks,
Dan
> For a company like an airline that runs 24/7 and the database is always
> being accessed and they can't shut down, how do they do maintenance on
their
> databases?
Clustering, perhaps? We use it here...
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
|||Generally they use dbcc indexdefrag instead of dbcc dbreindex, you just need to be more diligent with its application.
It also becomes critical to size the database correctly so that it does not go offline for database growths/shrinks.
|||They do what they have to based on their specific requirements. Almost no
one that is in a 24 x 7 will be doing any shrinking of files and it should
rarely be done anyway. If it's truly 24 x 7 then DBREINDEX may not be an
option and INDEDXDEFRAG might be the way to go. It is impossableto go into
enough detail of how to maintain a 24 x 7 operation in a newsgroup. If you
have this type of requirement and are asking these questions I would highly
recommend you hire a qualified consultant.
Andrew J. Kelly SQL MVP
"Dan" <ddonahue@.archermalmo.com> wrote in message
news:%23T1z%23uaPEHA.252@.TK2MSFTNGP10.phx.gbl...
> For a company like an airline that runs 24/7 and the database is always
> being accessed and they can't shut down, how do they do maintenance on
their
> databases? How do they do things like defrag or rebuild indexes or shrink
> files?
> Do they have two databases and they switch back and forth?
> Thanks,
> Dan
>
|||Good points. I was thinking more along the lines of applying security
fixes, windows update, etc.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:#Qh#aDbPEHA.4036@.TK2MSFTNGP12.phx.gbl...
> They do what they have to based on their specific requirements. Almost
no
> one that is in a 24 x 7 will be doing any shrinking of files and it should
> rarely be done anyway. If it's truly 24 x 7 then DBREINDEX may not be an
> option and INDEDXDEFRAG might be the way to go. It is impossableto go
into
> enough detail of how to maintain a 24 x 7 operation in a newsgroup. If you
> have this type of requirement and are asking these questions I would
highly
> recommend you hire a qualified consultant.
|||Ken and Andrew,
So, even though INDEXDEFRAG isn't as efficient as DBREINDEX, I guess it is
sufficient. Isn't there fragmentation that also happens to the data on the
disk as well as to the indexes? How would you handle that kind of
fragmentation?
Dan
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:%23Qh%23aDbPEHA.4036@.TK2MSFTNGP12.phx.gbl...
> They do what they have to based on their specific requirements. Almost
no
> one that is in a 24 x 7 will be doing any shrinking of files and it should
> rarely be done anyway. If it's truly 24 x 7 then DBREINDEX may not be an
> option and INDEDXDEFRAG might be the way to go. It is impossableto go
into
> enough detail of how to maintain a 24 x 7 operation in a newsgroup. If you
> have this type of requirement and are asking these questions I would
highly[vbcol=seagreen]
> recommend you hire a qualified consultant.
> --
> Andrew J. Kelly SQL MVP
>
> "Dan" <ddonahue@.archermalmo.com> wrote in message
> news:%23T1z%23uaPEHA.252@.TK2MSFTNGP10.phx.gbl...
> their
shrink
>
|||Depending on what you mean by 'as efficient' (disk space, log space, cpu
usage, IOs, elapsed time), I didn't design INDEXDEFRAG to be more efficient
that DBREINDEX - it's meant to be an online alternative to rebuilding
indexes. You should read the whitepaper below for more details.
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dan" <ddonahue@.archermalmo.com> wrote in message
news:#FdM04bPEHA.556@.tk2msftngp13.phx.gbl...
> Ken and Andrew,
> So, even though INDEXDEFRAG isn't as efficient as DBREINDEX, I guess it
is[vbcol=seagreen]
> sufficient. Isn't there fragmentation that also happens to the data on the
> disk as well as to the indexes? How would you handle that kind of
> fragmentation?
> Dan
> "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> news:%23Qh%23aDbPEHA.4036@.TK2MSFTNGP12.phx.gbl...
> no
should[vbcol=seagreen]
an[vbcol=seagreen]
> into
you[vbcol=seagreen]
> highly
always
> shrink
>
|||Thanks. I will.
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:uRVJRJcPEHA.252@.TK2MSFTNGP10.phx.gbl...
> Depending on what you mean by 'as efficient' (disk space, log space, cpu
> usage, IOs, elapsed time), I didn't design INDEXDEFRAG to be more
efficient
> that DBREINDEX - it's meant to be an online alternative to rebuilding
> indexes. You should read the whitepaper below for more details.
>
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
> Regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Dan" <ddonahue@.archermalmo.com> wrote in message
> news:#FdM04bPEHA.556@.tk2msftngp13.phx.gbl...
> is
the[vbcol=seagreen]
Almost[vbcol=seagreen]
> should
> an
> you
> always
on
>

database maintenance for companies that run 24/7

For a company like an airline that runs 24/7 and the database is always
being accessed and they can't shut down, how do they do maintenance on their
databases? How do they do things like defrag or rebuild indexes or shrink
files?
Do they have two databases and they switch back and forth?
Thanks,
Dan> For a company like an airline that runs 24/7 and the database is always
> being accessed and they can't shut down, how do they do maintenance on
their
> databases?
Clustering, perhaps? We use it here...
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Generally they use dbcc indexdefrag instead of dbcc dbreindex, you just need to be more diligent with its application
It also becomes critical to size the database correctly so that it does not go offline for database growths/shrinks.|||They do what they have to based on their specific requirements. Almost no
one that is in a 24 x 7 will be doing any shrinking of files and it should
rarely be done anyway. If it's truly 24 x 7 then DBREINDEX may not be an
option and INDEDXDEFRAG might be the way to go. It is impossableto go into
enough detail of how to maintain a 24 x 7 operation in a newsgroup. If you
have this type of requirement and are asking these questions I would highly
recommend you hire a qualified consultant.
--
Andrew J. Kelly SQL MVP
"Dan" <ddonahue@.archermalmo.com> wrote in message
news:%23T1z%23uaPEHA.252@.TK2MSFTNGP10.phx.gbl...
> For a company like an airline that runs 24/7 and the database is always
> being accessed and they can't shut down, how do they do maintenance on
their
> databases? How do they do things like defrag or rebuild indexes or shrink
> files?
> Do they have two databases and they switch back and forth?
> Thanks,
> Dan
>|||Good points. I was thinking more along the lines of applying security
fixes, windows update, etc.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:#Qh#aDbPEHA.4036@.TK2MSFTNGP12.phx.gbl...
> They do what they have to based on their specific requirements. Almost
no
> one that is in a 24 x 7 will be doing any shrinking of files and it should
> rarely be done anyway. If it's truly 24 x 7 then DBREINDEX may not be an
> option and INDEDXDEFRAG might be the way to go. It is impossableto go
into
> enough detail of how to maintain a 24 x 7 operation in a newsgroup. If you
> have this type of requirement and are asking these questions I would
highly
> recommend you hire a qualified consultant.|||Ken and Andrew,
So, even though INDEXDEFRAG isn't as efficient as DBREINDEX, I guess it is
sufficient. Isn't there fragmentation that also happens to the data on the
disk as well as to the indexes? How would you handle that kind of
fragmentation?
Dan
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:%23Qh%23aDbPEHA.4036@.TK2MSFTNGP12.phx.gbl...
> They do what they have to based on their specific requirements. Almost
no
> one that is in a 24 x 7 will be doing any shrinking of files and it should
> rarely be done anyway. If it's truly 24 x 7 then DBREINDEX may not be an
> option and INDEDXDEFRAG might be the way to go. It is impossableto go
into
> enough detail of how to maintain a 24 x 7 operation in a newsgroup. If you
> have this type of requirement and are asking these questions I would
highly
> recommend you hire a qualified consultant.
> --
> Andrew J. Kelly SQL MVP
>
> "Dan" <ddonahue@.archermalmo.com> wrote in message
> news:%23T1z%23uaPEHA.252@.TK2MSFTNGP10.phx.gbl...
> > For a company like an airline that runs 24/7 and the database is always
> > being accessed and they can't shut down, how do they do maintenance on
> their
> > databases? How do they do things like defrag or rebuild indexes or
shrink
> > files?
> >
> > Do they have two databases and they switch back and forth?
> >
> > Thanks,
> >
> > Dan
> >
> >
>|||Depending on what you mean by 'as efficient' (disk space, log space, cpu
usage, IOs, elapsed time), I didn't design INDEXDEFRAG to be more efficient
that DBREINDEX - it's meant to be an online alternative to rebuilding
indexes. You should read the whitepaper below for more details.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dan" <ddonahue@.archermalmo.com> wrote in message
news:#FdM04bPEHA.556@.tk2msftngp13.phx.gbl...
> Ken and Andrew,
> So, even though INDEXDEFRAG isn't as efficient as DBREINDEX, I guess it
is
> sufficient. Isn't there fragmentation that also happens to the data on the
> disk as well as to the indexes? How would you handle that kind of
> fragmentation?
> Dan
> "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> news:%23Qh%23aDbPEHA.4036@.TK2MSFTNGP12.phx.gbl...
> > They do what they have to based on their specific requirements. Almost
> no
> > one that is in a 24 x 7 will be doing any shrinking of files and it
should
> > rarely be done anyway. If it's truly 24 x 7 then DBREINDEX may not be
an
> > option and INDEDXDEFRAG might be the way to go. It is impossableto go
> into
> > enough detail of how to maintain a 24 x 7 operation in a newsgroup. If
you
> > have this type of requirement and are asking these questions I would
> highly
> > recommend you hire a qualified consultant.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Dan" <ddonahue@.archermalmo.com> wrote in message
> > news:%23T1z%23uaPEHA.252@.TK2MSFTNGP10.phx.gbl...
> > > For a company like an airline that runs 24/7 and the database is
always
> > > being accessed and they can't shut down, how do they do maintenance on
> > their
> > > databases? How do they do things like defrag or rebuild indexes or
> shrink
> > > files?
> > >
> > > Do they have two databases and they switch back and forth?
> > >
> > > Thanks,
> > >
> > > Dan
> > >
> > >
> >
> >
>|||Thanks. I will.
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:uRVJRJcPEHA.252@.TK2MSFTNGP10.phx.gbl...
> Depending on what you mean by 'as efficient' (disk space, log space, cpu
> usage, IOs, elapsed time), I didn't design INDEXDEFRAG to be more
efficient
> that DBREINDEX - it's meant to be an online alternative to rebuilding
> indexes. You should read the whitepaper below for more details.
>
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> Regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Dan" <ddonahue@.archermalmo.com> wrote in message
> news:#FdM04bPEHA.556@.tk2msftngp13.phx.gbl...
> > Ken and Andrew,
> >
> > So, even though INDEXDEFRAG isn't as efficient as DBREINDEX, I guess it
> is
> > sufficient. Isn't there fragmentation that also happens to the data on
the
> > disk as well as to the indexes? How would you handle that kind of
> > fragmentation?
> >
> > Dan
> >
> > "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> > news:%23Qh%23aDbPEHA.4036@.TK2MSFTNGP12.phx.gbl...
> > > They do what they have to based on their specific requirements.
Almost
> > no
> > > one that is in a 24 x 7 will be doing any shrinking of files and it
> should
> > > rarely be done anyway. If it's truly 24 x 7 then DBREINDEX may not be
> an
> > > option and INDEDXDEFRAG might be the way to go. It is impossableto go
> > into
> > > enough detail of how to maintain a 24 x 7 operation in a newsgroup. If
> you
> > > have this type of requirement and are asking these questions I would
> > highly
> > > recommend you hire a qualified consultant.
> > >
> > > --
> > > Andrew J. Kelly SQL MVP
> > >
> > >
> > > "Dan" <ddonahue@.archermalmo.com> wrote in message
> > > news:%23T1z%23uaPEHA.252@.TK2MSFTNGP10.phx.gbl...
> > > > For a company like an airline that runs 24/7 and the database is
> always
> > > > being accessed and they can't shut down, how do they do maintenance
on
> > > their
> > > > databases? How do they do things like defrag or rebuild indexes or
> > shrink
> > > > files?
> > > >
> > > > Do they have two databases and they switch back and forth?
> > > >
> > > > Thanks,
> > > >
> > > > Dan
> > > >
> > > >
> > >
> > >
> >
> >
>