I recently came across this message in the SQL Error Logs. This message indicates that another process, such as OS or another application on the database server is taking memory away from SQL Server.
Thankfully, finding the problem was a simple process for me (you may not be so lucky!). The error only occured out of hours and the times were relatively consistent. A quick call to the infrastructure team and I discovered they had turned on a replication process (HP StorageWorks Storage Mirroring) without letting me know. A quick rant and the process was disabled. I checked the logs the following morning and the errors had gone.
Read more…
I had a support request recently from one of our clients who wanted was trying to replicate our database to a reporting server. One of our legacy tables contains text data and transactional replication was failing with the error
SQL Server can be configured to allow a maximum size, in bytes, of text or image data which can be replicated. This applies to each insert, update, writetext or updatetext statement issued.
The default is 65536 bytes (64k). In our environment, 262144 bytes (256k) would be sufficient. Here is how to change this setting.
Read more…
As databases evolve, you may find that some indexes are no longer used. This is something I have seen a lot in my current job.
This script will allow you to see which indexes are used and whether they are used for seeks, scans or key lookups.
SELECT
SC.[name] + ‘.’ + OBJECT_NAME(S.[object_id]) AS [Object Name],
I.[name] AS [Index Name],
S.[user_seeks] AS [User Seeks],
S.[user_scans] AS [User Scans],
S.[user_lookups] AS [User Lookups]
FROM sys.dm_db_index_usage_stats S INNER JOIN sys.indexes I
ON I.[object_id] = S.[object_id] AND I.[index_id] = S.[index_id]
INNER JOIN sys.tables T
ON T.[object_id] = I.[object_id]
INNER JOIN sys.schemas SC
ON T.[schema_id] = SC.[schema_id]
WHERE
OBJECTPROPERTY(S.[object_id],’IsUserTable’) = 1 AND
OBJECTPROPERTY(S.[object_id], ‘IsMSShipped’) = 0 AND
S.[database_id] = DB_ID(DB_NAME())
ORDER BY
SC.[name] + ‘.’ + OBJECT_NAME(S.[object_id]), I.[index_id]
Just a quick script to list all schema’s, tables and column information from within a database.
SELECT
(S.[name] + ‘.’ + T.[name]) AS [Table],
C.[name] AS [Column Name],
D.[name] AS [Date Type],
C.max_length AS [Length],
C.precision AS [Precision],
C.scale AS [Scale],
C.is_nullable AS [Nullable],
C.is_identity AS [Identity Column],
C.[collation_name] AS [Collation]
FROM sys.tables T INNER JOIN sys.schemas S
ON T.schema_id = S.schema_id
INNER JOIN sys.columns C
ON T.object_id = C.object_id
INNER JOIN sys.types D
ON C.system_type_id = D.system_type_id
WHERE T.is_ms_shipped = 0
ORDER BY T.[name], C.column_id