SQL SERVER DBA, Linux and Azure

T-SQL scripts to find out when last stats update happened ?

select a.id as 'ObjectID', isnull(a.name,'Heap') as 'IndexName', b.name as 'TableName',
stats_date (id,indid) as stats_last_updated_time,st.auto_created
from sys.sysindexes as a
inner join sys.objects as b
inner join sys.stats st on st.object_id=b.object_id
on a.id = b.object_id
where b.type = 'U'

---------------------------------

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
,sp.rows --if you want to know how many rows unrem this
FROM
sys.indexes i JOIN
sys.tables t ON t.object_id = i.object_id JOIN
sys.partitions sp ON i.object_id = sp.object_id
and i.index_id = sp.index_id --new

WHERE
i.type > 0 and --if you want to see heap rem this
sp.rows > 0
ORDER BY
t.name ASC
,i.type_desc ASC
,i.name ASC

------------------------

How can you find the stored procedure used by SQL Job

If you have hundreds of sql jobs scheduled  to run everyday in SQLserver.  How quickly you can find the stored procedure used by sql job command.

Here is simple solution to find it.

use msdb
go
select sj.name as job_name, st.command
from  sysjobs sj
join sysjobsteps st
on sj.job_id = st.job_id
where st.command like '%uspMyBook%'

The mirror database has insufficient transaction log data

When you Setting Database Mirroring if you will get the error message like  "The mirror database has insufficient transaction log data".


The mirror database, "Northwind", has insufficient transaction log data to preserve the log backup chain of the principal database. This may happen if a log backup from the principal database has not been taken or has not been restored on the mirror database. (Microsoft SQL Server, Error: 1478)

Error : The mirror database has insufficient transaction log data
Solution :
1.. Take the Transaction Log backup from Principal server database and
2. Restore Log on Mirror database
 Restore Log on Northwind from disk = 'c:\mssql\backup\DBP_TLog.TRN' with NORECOVERY
3. Now try to setting up Database Mirroring again from Principal Server

Database RESTORE error Msg 3183, Level 16, State 2, Line 1

When you try to restore/verifyonly SQL backup you may be found this error some time because the backup may be corrupted.

Solution : Make sure to copy backup file into another device then restore, The issue is on tape/usb/disk

RESTORE VERIFYONLY FROM DISK = 'E:\MSSQL\Backup\MYDBTEST.BAK'

Msg 3242, Level 16, State 2, Line 2
The file on device '\\websql2\SQLBackup\MYDBTEST_2011-06-21-2040.FUL' is not a valid Microsoft Tape Format backup set.
Msg 3013, Level 16, State 1, Line 2
VERIFY DATABASE is terminating abnormally.


RESTORE DATABASE MYDBTEST
FROM DISK = 'E:\MSSQL\Backup\MYDBTEST.BAK'
WITH RECOVERY,
MOVE 'MYDBTEST_Data' TO 'E:\\MSSQL\DATA\MYDBTEST.mdf',
MOVE 'MYDBTEST_Log' TO 'E:\ MSSQL\DATA\MYDBTEST_log.ldf',
STATS = 5


05 percent processed.
.
.
.
.

85 percent processed.
90 percent processed.

Msg 3183, Level 16, State 2, Line 1
RESTORE detected an error on page (0:0) in database "MYDBTEST" as read from the backup set.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Error: 3041, Severity: 16, State: 1

Message:

BACKUP failed to complete the command BACKUP DATABASE db_name. Check the backup application log for detailed messages

Solution:  Make sure to take another full database backup.