Error: 14151, Severity: 18, State: 1. Replication-Replication Distribution Subsystem: agent failed. Violation of PRIMARY KEY constraint ”. Cannot insert duplicate key in object ”.
Cause and solution
There were some problems when transmit the data.
Reinitialize the subscriptions to fix the data issue.
RECOVERY PENDING mode means
SQL Server has encountered a resource-related error during recovery. The database is not damaged, but files may be missing or system resource limitations may be preventing it from starting. The database is unavailable. Additional action by the user is required to resolve the error and let the recovery process be completed.
The database couldn’t be started up because SQL server couldn’t open the database files due to resource limitations at that time.
We can see in the errorlog, there sth like the file is used by another process..
In the event log:
Information 8/26/2014 3:01:53 AM Service Control Manager 7036 None The Microsoft Software Shadow Copy Provider service entered the running state.
Information 8/26/2014 3:01:53 AM Virtual Disk Service 3 None Service started.
Information 8/26/2014 3:01:51 AM Service Control Manager 7036 None The Virtual Disk service entered the stopped state.
Warning 8/26/2014 3:01:51 AM Ntfs 57 (2) The system failed to flush data to the transaction log. Corruption may occur.
Error 8/26/2014 3:01:51 AM Ntfs 137 (2) The default transaction resource manager on volume \\?\Volume{cd35bea2-2507-11e4-b266-0050560300b5} encountered a non-retryable error and could not start. The data contains the error code.
Warning 8/26/2014 3:01:51 AM Ntfs 57 (2) The system failed to flush data to the transaction log. Corruption may occur.
Information 8/26/2014 3:01:51 AM Virtual Disk Service 4 None Service stopped.
Information 8/26/2014 3:01:49 AM Service Control Manager 7036 None The Virtual Disk service entered the running state.
Warning 8/26/2014 3:01:49 AM Ntfs 57 (2) The system failed to flush data to the transaction log. Corruption may occur.
Information 8/26/2014 3:01:49 AM Virtual Disk Service 3 None Service started.
Solution:
Checking the files first (both data file and log file, fortunately they are there not missing)
Detach the database
Bring it online manually to make the recovery complete
Another thing database option AutoClose is enabled which it’s not recommended.
When set to ON, the database is shut down cleanly and its resources are freed after the last user exits. The database automatically reopens when a user tries to use the database again.
Ref:
http://sqlmag.com/blog/worst-practice-allowing-autoclose-sql-server-databases
These items are more detailed and much more help to understand the Plan Costing and Parallel Plans
http://sqlblog.com/blogs/paul_white/archive/2010/09/01/inside-the-optimizer-plan-costing.aspxhttp://www.qdpma.com/CBO/CBO07_ParallelismI.htmlhttp://www.qdpma.com/CBO/CBO08_ParallelismII.htmlhttp://www.qdpma.com/CBO/CBO09_ParallelismIII.html
Msg 8152, Level 16, State 2, Line 54
String or binary data would be truncated. [SQLSTATE 22001] (Error 8152) The statement has been terminated. [SQLSTATE 01000] (Error 3621). The step failed.
Cause & Solution:
This error occurs whan the size of data that is inserted into a column is greater than the size of the column.
modify the column type
alter table [tb_name] alter column [col_name] [varchar](30) NOT NULL
SELECT COLUMN_NAME, TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE ‘column name’
Error met when export from sql server to access(because atable has a column DEFINITION cannot convert in long text in Access.):
– Setting Destination Connection (Error)
Messages
* Error 0xc0204016: SSIS.Pipeline: The “output column “DEFINITION” (131)” has a length that is not valid. The length must be between 0 and 4000.
(SQL Server Import and Export Wizard)
* Exception from HRESULT: 0xC0204016 (Microsoft.SqlServer.DTSPipelineWrap)
– Validating (Stopped)
Cause:
There is a limit on the size of unicode character strings at 4000. Non-unicode character strings have a limit of 8000. Ensure that you don’t have type conversions between varchar and nvarchar.
Create a .mdb file
http://www.experts-exchange.com/Database/MS_Access/Q_26331331.html
For Microsoft Access provider
http://help.dugeo.com/m/faq/l/176948-exporting-sql-database-to-access
For M.. Access 2007 provider
use .accdb file
user dbname
go
1. Table
SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,*
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( ‘db_name’)
and object_id = ‘object_id’
2.DB level
SET ANSI_WARNINGS OFF;
SET NOCOUNT ON;
GO
WITH agg AS
(
SELECT
last_user_seek,
last_user_scan,
last_user_lookup,
last_user_update
FROM
sys.dm_db_index_usage_stats
WHERE
database_id = DB_ID()
)
SELECT
last_read = MAX(last_read),
last_write = MAX(last_write)
FROM
(
SELECT last_user_seek, NULL FROM agg
UNION ALL
SELECT last_user_scan, NULL FROM agg
UNION ALL
SELECT last_user_lookup, NULL FROM agg
UNION ALL
SELECT NULL, last_user_update FROM agg
) AS x (last_read, last_write);
3.each table
SET ANSI_WARNINGS OFF;
SET NOCOUNT ON;
GO
WITH agg AS
(
SELECT
[object_id],
last_user_seek,
last_user_scan,
last_user_lookup,
last_user_update
FROM
sys.dm_db_index_usage_stats
WHERE
database_id = DB_ID()
)
SELECT
[Schema] = OBJECT_SCHEMA_NAME([object_id]),
[Table_Or_View] = OBJECT_NAME([object_id]),
last_read = MAX(last_read),
last_write = MAX(last_write)
FROM
(
SELECT [object_id], last_user_seek, NULL FROM agg
UNION ALL
SELECT [object_id], last_user_scan, NULL FROM agg
UNION ALL
SELECT [object_id], last_user_lookup, NULL FROM agg
UNION ALL
SELECT [object_id], NULL, last_user_update FROM agg
) AS x ([object_id], last_read, last_write)
GROUP BY
OBJECT_SCHEMA_NAME([object_id]),
OBJECT_NAME([object_id])
ORDER BY 1,2;
4. in fact, the above retures NULL, for Index last update, can use this
USE your_db;
SELECT t.name AS Table_Name
,i.name AS Index_Name
,i.type_desc AS Index_Type
,STATS_DATE(i.object_id,i.index_id) AS Date_Updated
FROM sys.indexes i
JOIN sys.tables t
ON t.object_id = i.object_id
WHERE i.type > 0
ORDER BY t.name ASC
,i.type_desc ASC
,i.name ASC;
The counters are initialized to empty whenever the SQL Server (MSSQLSERVER) service is started. In addition, whenever a database is detached or is shut down (for example, because AUTO_CLOSE is set to ON), all rows associated with the database are removed.
Ref:
http://dba.stackexchange.com/questions/985/how-to-detect-any-changes-to-a-database-ddl-and-dml
Noted:
cannot determined by data file modified date:
Think of the data file as a container,for SQL Server, the timestamp in Windows has nothing to do with the data you are changing inside the file; it has to do with the last time the file itself has changed, such as a service restart, or – more commonly – a growth or shrink event (either user- or system-initiated). Which could happen when you’ve changed data inside the file, but won’t happen every time
http://dba.stackexchange.com/questions/49619/wrong-last-modified-date-of-ndf-file-on-sql-server
http://www.sqlservercentral.com/articles/DAC/111543/
sqlcmd -S <Servername> -A -E
or
sqlcmd -S admin:<Servername> -E
sqlcmd -S -E -d -t 65535 -i “” -o “”
sqlcmd -S -U -P -d -t 65535 -i -o
(-t means query_timeout)
To put .bat run in background, we can use:
start /b C:\sql.bat
SHAOJINGCAO