I try to access a remote SP.
Server1 has a working DTS-service running
Server2 cannot start the DTS due to the following error:
"An error 1060 - (the specified service does not exist as an installes service) occured while performing this service operation on the MSDTC service".
Ok, I got it, the service isn't running (since I get this mess. when I'm tryin to start the service).
Can't find any service in the opsys services. Tried to reinstall the MSSQL but the MSDTC was/is allready installed.
Please help!
By the way, I'm running MSSQL 2000.
thanksIs the dtc service started ?|||Originally posted by rnealejr
Is the dtc service started ?
Nope, and that's the problem. I get the errormessage when I try to start the service in enterprise manager.
I can't find any corresponding service in the opsys services. It seems like the service isn't installed...but it is??!!
Showing posts with label guess. Show all posts
Showing posts with label guess. Show all posts
Sunday, March 25, 2012
Wednesday, March 7, 2012
Backups and log file growth issues
I've been looking through this forum and can only guess that SQL2005 has changed in how backups and log file growth issues are handled. I tried the All Tasks feature from the GUI to perform a backup, however, the Truncate the Transaction log feature is greyed out so I can not select it. In SQL2000 I used the following to backup my databases and the log files were never a problem:
BACKUP DATABASE [DSS] TO DISK =
N'G:\MSSQL\Backup\DSSbackup.BAK'
WITH INIT , NOUNLOAD , NAME = N'DSS backup',
SKIP , STATS = 10, NOFORMAT
Could someone assist me with a proper T-SQL to place in a job to backup my database and keep the log file under control and / or explain why the Truncate transaction log feature is greyed out?
Thanks!
Additional information: I have used the scripts below to backup my database and logfile including using the INIT to truncate the log file. My database file is 9.2 GB and my log file is 9.9 GB. After running the scripts, a transaction log backup file is created (512 KB), however my ldf log file remains 9.9 GB. Why are the transactions not being written to the database file and log file entries being released?
/* This script backs up the DSS database to disk, overwriting any
previous backup
*/
BACKUP DATABASE [DSS]
TO DISK = N'g:\mssql\backup\database\DSS.bak'
WITH DESCRIPTION = N'DSS Full Database Backup'
, INIT
, NAME = N'DSS - Full Database Backup'
GO
/* Backup validation to ensure the file is valid before storing it */
RESTORE VERIFYONLY
FROM DISK = N'g:\mssql\backup\database\DSS.bak'
WITH FILE = 1
GO
/* This script backs up the DSS log file and truncates the file */
BACKUP LOG [DSS]
TO DISK = N'g:\mssql\backup\logfiles\DSS_log.bak'
WITH
INIT
, NAME = N'DSS-Transaction Log Backup'
GO
Thanks!
I have few observation
(a) in this script what you are doing is you are overwriting the earlier backup with the current one... which is not at all recommended. if the backup file got corrupted means... you will endup without any backup.. this is a serious issue. You should not use INIT in this case i believe.
(b) to transaction log size under control , you should increase the frequency of the TL backup. If currently you are configured TL backup daily then make it hourly with out INIT option. If you overwrite the backup of TL then there is no point in taking the TL Backup
(c) if you do not want POINT-in-Time restore you can change the Recovery model to Simple , which will control the size to a certain limit
(d) If you want to trucate the LOg then use WIth TRUNCATE ONLY option with the backup script
EG.
backup log [DSS] with truncate_only
dbcc shrinkfile(DSS_Log)
Read more about TL architecture and Backup method /Recovery model in BOL
Read these article also :-
FileSize -How to stop the log file from growing
http://www.support.microsoft.com/?id=873235
FileSize -Log file filling up
http://www.support.microsoft.com/?id=110139
FileSize -Log File Grows too big
http://www.support.microsoft.com/?id=317375
FileSize -Log File issues
http://www.nigelrivett.net/TransactionLogFileGrows_1.html
FileSize -Shrinking Log in SQL Server 2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=272318
Madhu
|||
Thank you for your response.
(a)The backup database and log files are backed up to tape regularily so I am not in danger of losing the backup.
(b) & (c) This is a new database, not yet released to production, so I will plan accordingly as you suggested to ensure a point-in-time recovery is available.
(d) As this database is new, I will try your script commands to reduce the log file this one time.
This would fall into the class of a one-off increase. I just created this database and imported several SQL2000 tables. I then re-wrote the SQL2000 stored procedures which truncate and reload the tables, so I feel this caused the log file increase. I will try the DBCC shrink this time, however, I do not plan to use it regularily.
Thanks again for your response!
|||I performed a full database backup and a transaction log backup. I ran the following query:
DBCC SHRINKFILE (DSS_log)
..and received the following message:
Cannot shrink log file 2 (DSS_log) because all logical log files are in use.
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
I am assuming there may be uncommitted transactions in the log file. Is there a T-SQL query to force all transactions to be committed and then erased from the log file?
Backups and log file growth issues
I've been looking through this forum and can only guess that SQL2005 has changed in how backups and log file growth issues are handled. I tried the All Tasks feature from the GUI to perform a backup, however, the Truncate the Transaction log feature is greyed out so I can not select it. In SQL2000 I used the following to backup my databases and the log files were never a problem:
BACKUP DATABASE [DSS] TO DISK =
N'G:\MSSQL\Backup\DSSbackup.BAK'
WITH INIT , NOUNLOAD , NAME = N'DSS backup',
SKIP , STATS = 10, NOFORMAT
Could someone assist me with a proper T-SQL to place in a job to backup my database and keep the log file under control and / or explain why the Truncate transaction log feature is greyed out?
Thanks!
Additional information: I have used the scripts below to backup my database and logfile including using the INIT to truncate the log file. My database file is 9.2 GB and my log file is 9.9 GB. After running the scripts, a transaction log backup file is created (512 KB), however my ldf log file remains 9.9 GB. Why are the transactions not being written to the database file and log file entries being released?
/* This script backs up the DSS database to disk, overwriting any
previous backup
*/
BACKUP DATABASE [DSS]
TO DISK = N'g:\mssql\backup\database\DSS.bak'
WITH DESCRIPTION = N'DSS Full Database Backup'
, INIT
, NAME = N'DSS - Full Database Backup'
GO
/* Backup validation to ensure the file is valid before storing it */
RESTORE VERIFYONLY
FROM DISK = N'g:\mssql\backup\database\DSS.bak'
WITH FILE = 1
GO
/* This script backs up the DSS log file and truncates the file */
BACKUP LOG [DSS]
TO DISK = N'g:\mssql\backup\logfiles\DSS_log.bak'
WITH
INIT
, NAME = N'DSS-Transaction Log Backup'
GO
Thanks!
I have few observation
(a) in this script what you are doing is you are overwriting the earlier backup with the current one... which is not at all recommended. if the backup file got corrupted means... you will endup without any backup.. this is a serious issue. You should not use INIT in this case i believe.
(b) to transaction log size under control , you should increase the frequency of the TL backup. If currently you are configured TL backup daily then make it hourly with out INIT option. If you overwrite the backup of TL then there is no point in taking the TL Backup
(c) if you do not want POINT-in-Time restore you can change the Recovery model to Simple , which will control the size to a certain limit
(d) If you want to trucate the LOg then use WIth TRUNCATE ONLY option with the backup script
EG.
backup log [DSS] with truncate_only
dbcc shrinkfile(DSS_Log)
Read more about TL architecture and Backup method /Recovery model in BOL
Read these article also :-
FileSize -How to stop the log file from growing
http://www.support.microsoft.com/?id=873235
FileSize -Log file filling up
http://www.support.microsoft.com/?id=110139
FileSize -Log File Grows too big
http://www.support.microsoft.com/?id=317375
FileSize -Log File issues
http://www.nigelrivett.net/TransactionLogFileGrows_1.html
FileSize -Shrinking Log in SQL Server 2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=272318
Madhu
|||
Thank you for your response.
(a)The backup database and log files are backed up to tape regularily so I am not in danger of losing the backup.
(b) & (c) This is a new database, not yet released to production, so I will plan accordingly as you suggested to ensure a point-in-time recovery is available.
(d) As this database is new, I will try your script commands to reduce the log file this one time.
This would fall into the class of a one-off increase. I just created this database and imported several SQL2000 tables. I then re-wrote the SQL2000 stored procedures which truncate and reload the tables, so I feel this caused the log file increase. I will try the DBCC shrink this time, however, I do not plan to use it regularily.
Thanks again for your response!
|||I performed a full database backup and a transaction log backup. I ran the following query:
DBCC SHRINKFILE (DSS_log)
..and received the following message:
Cannot shrink log file 2 (DSS_log) because all logical log files are in use.
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
I am assuming there may be uncommitted transactions in the log file. Is there a T-SQL query to force all transactions to be committed and then erased from the log file?
Saturday, February 25, 2012
Backups
I guess I'm not understander your responce. Do I need a
specific job to "Update statistics used by the query
optimizer"?
Are you asking a question or responding to the question already asked in this thread?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lee" <anonymous@.discussions.microsoft.com> wrote in message
news:2479d01c45f79$e19f6de0$a501280a@.phx.gbl...
> I guess I'm not understander your responce. Do I need a
> specific job to "Update statistics used by the query
> optimizer"?
specific job to "Update statistics used by the query
optimizer"?
Are you asking a question or responding to the question already asked in this thread?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lee" <anonymous@.discussions.microsoft.com> wrote in message
news:2479d01c45f79$e19f6de0$a501280a@.phx.gbl...
> I guess I'm not understander your responce. Do I need a
> specific job to "Update statistics used by the query
> optimizer"?
Backups
I guess I'm not understander your responce. Do I need a
specific job to "Update statistics used by the query
optimizer"?Are you asking a question or responding to the question already asked in thi
s thread?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lee" <anonymous@.discussions.microsoft.com> wrote in message
news:2479d01c45f79$e19f6de0$a501280a@.phx
.gbl...
> I guess I'm not understander your responce. Do I need a
> specific job to "Update statistics used by the query
> optimizer"?
specific job to "Update statistics used by the query
optimizer"?Are you asking a question or responding to the question already asked in thi
s thread?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lee" <anonymous@.discussions.microsoft.com> wrote in message
news:2479d01c45f79$e19f6de0$a501280a@.phx
.gbl...
> I guess I'm not understander your responce. Do I need a
> specific job to "Update statistics used by the query
> optimizer"?
Backups
I guess I'm not understander your responce. Do I need a
specific job to "Update statistics used by the query
optimizer"?Are you asking a question or responding to the question already asked in this thread?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lee" <anonymous@.discussions.microsoft.com> wrote in message
news:2479d01c45f79$e19f6de0$a501280a@.phx.gbl...
> I guess I'm not understander your responce. Do I need a
> specific job to "Update statistics used by the query
> optimizer"?
specific job to "Update statistics used by the query
optimizer"?Are you asking a question or responding to the question already asked in this thread?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lee" <anonymous@.discussions.microsoft.com> wrote in message
news:2479d01c45f79$e19f6de0$a501280a@.phx.gbl...
> I guess I'm not understander your responce. Do I need a
> specific job to "Update statistics used by the query
> optimizer"?
Subscribe to:
Posts (Atom)