DECLARE @SearchPhrase nvarchar(1000)
Adding fuel to my passion on SQL Server
DECLARE @SearchPhrase nvarchar(1000)
1: SELECT destination_database_name,max(restore_date) as restore_date
2: FROM msdb.dbo.restorehistory
3: WHERE destination_database_name = 'testDB’ ---- Place your database name here
4: GROUP BY destination_database_name
The result will look like;
testDB 2013-04-29 11:49:19.940
In SQL Server 2008 Management Studio,
We can not do
•Change the Allow Nulls setting for a column
•Reorder the columns
•Change the column data type
•Add a new column
How to fix / Resolution:
•Go to Tools -> Options -> Designers -> Tables and Designers
•Uncheck the Prevent Saving Changes that require table re-creation
When trying to restore the database with snapshots got the following error SQL message
SQL Server T-SQL Command
|restore database PRD_Accounts from disk ='H:\PRD_Accounts_backup_daily.bak' with replace, stats|
Msg 5094, Level 16, State 2, Line 1
The operation cannot be performed on a database with database snapshots or active DBCC replicas.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Since we cannot restore a database if we have any snapshots.
Please drop all snapshots and try database restore again.
1. To check the database snapshot and it’s source database
select database_id, name,source_database_id,Source_DBName=DB_NAME(source_database_id)
2. Drop the Snapshot database
|DROP DATABASE Accounts_Rpt|
3. Create script of snapshot database, before dropping it. If at all we need to re-establish the snapshot after restore we can use it
/****** Object: Database [Benefits_Rpt_sol48u] Script Date: 12/03/2012 00:01:02 ******/
Every time SQL Server Agent is started, the current SQL Server Agent error log is renamed to SQLAgent.1; SQLAgent.1 becomes SQLAgent.2, SQLAgent.2 becomes SQLAgent.3, and so on. sp_cycle_agent_errorlog enables you to cycle the error log files without stopping and starting the server.
This stored procedure must be run from the msdb database.
Error on executing of sp_cycle_agent_errorlog
Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: Access is denied.
Each time the stored procedure was run it just overwritten the current agent log without renaming it and because of this only one SQLAGENTOUT file was left.
SQL Mail Sent fail with errors below;
Error from the SQL Mail log:
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2012-11-07T22:09:50). Exception Message: Could not connect to mail server. (No connection could be made because the target machine actively refused it 184.108.40.206:25). )
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2012-01-17T11:13:58). Exception Message: Could not connect to mail server. (No such host is known). )
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2012-01-17T11:12:44). Exception Message: Cannot send mails to mail server. (Failure sending mail.). )
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2010-10-24T19:55:11). Exception Message: Could not connect to mail server. (A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond 220.127.116.11:25). )
From the error message it is clear that it is a communication issue between SQL Server host and the Mail Exchange Server.
Problem was related to Exchange Server not allowing relaying from SQL Server.
Please engage Network Administrator to add SQL Server host IP Address to allow to send emails taking below steps, like
On Mail server > Exchange Server Manager > SMTP Connector Properties
> Access Properties > Access Tab > Relay button > Add IP Address of SQL Server
Also cross check if the Anti Virus (AV) program running on the SQL Server host. AV blocks services from sending on TCP-25. Adjust the AV software accordingly. Add DATABASEMAIL90.EXE to the list of innocent programs in your AV.
It would be useful to cross check, when the Linked Server remote login password has changed.
In this case, linked server will wail due to wrong password. To fix this we have reset the correct password.
Before you run the following query, replace <database_name> and <schema_name.object_name> with valid names.
Before you run the following query, replace <database_name> and <n_days> with valid values.
It leverages on the system stored procedure sys.sp_testlinkedserver
SET NOCOUNT ON;
DECLARE srvname INSENSITIVE CURSOR FOR