BACKUP DATABASE [DAILY LEARNING] TO BLOG [QIUSHIYANG.COM]
It’s common nowaday that taking long time to run DBCC CHECK for TB level database. Now there is a way to consume more IO resource to run DBCC CHECK faster, by using flag 2562 and 2549. This is only availble for SQL 2008 R2 CU11 now.
My 2 cents, pay attention to tempdb usage when enabling these flags as the trade-off is tempdb size and io. And dont set them as startup parameter, just enable them in the session level when you are ready for the trade-off.
Use the script below, you can check the last time of different user database backup, pay attention to those never been backup!
SELECT db.Name AS DatabaseName,
COALESCE(CONVERT(VARCHAR(50), MAX(bs.backup_finish_date), 120),”) AS LastBackUpTime,
CASE ISNULL( bs.type,’X')
WHEN ‘D’ THEN ‘Full backup’
WHEN ‘L’ THEN ‘Log backup’
WHEN ‘X’ THEN ‘No backup’
WHEN ‘I’ THEN ‘Differential database backup’
WHEN ‘F’ THEN ‘File or filegroup backup’
WHEN ‘G’ THEN ‘Differential file backup’
WHEN ‘P’ THEN ‘Partial backup’
WHEN ‘Q’ THEN ‘Differential partial backup’
END AS TYPE
FROM sys.sysdatabases db
LEFT OUTER JOIN msdb.dbo.backupset bs ON bs.database_name = db.name
WHERE db.dbid > 4
GROUP BY db.Name,bs.type
Good luck.
For SQL 2000, the only way to find out long running transaction is to run DBCC OPENTRAN.
Begin from SQL 2005, you can query DMV with the script below:
SET NOCOUNT ON
select db_name(database_id) as dbname,tr.transaction_id,ses.session_id,database_transaction_begin_time
from sys.dm_tran_database_transactions as tr
join sys.dm_tran_session_transactions as ses
on tr.transaction_id = ses.transaction_id
where is_user_transaction = 1
and database_transaction_begin_time < (GETDATE()-’00:05:00′)
It will help you to find out transactions which running longer than 5 min.
Perfmon counter is handy tool for DBA to glance what happening in OS/Instance level. As DBA, you’d better to make sure related perfmon counters exist daily. Rebuild missing counter may request restart instance or reboot server, you may have difficulty to do so when you operating a critical system during business hour.
Today we share case 1: You can’t find the perfmon counter when you install 32bit SQL Server on 64bit OS. (We call it WOW- Windows on Windows).
Uhhhh, the best practice is to install 64bit SQL Server instead of 32bit on 64bit hardware. But if you have no choice or this is a legacy system..
1. Stop Performance Logs & Alerts services.
2. Run regedit. Find HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SysmonLog, change ImagePath from “%SystemRoot%\System32\smlogsvc.exe” to “%SystemRoot%\SysWow64\smlogsvc.exe”.
3. Start Performance Logs & Alerts services.
4. Run perfmon from %systemroot%\SysWOW64\perfmon.exe.
5. Create the log.
—- script to check each database’s space usage and size of next growth(MB)
—- for sql 2005 and later version
exec
sp_msforeachdb
‘USE[?];exec sp_spaceused’
—- here we use the undocumented sp_msforeachdb
go
select
db_name(database_id)
as DB_NAME,
name as
File_Name,
case is_percent_growth
when 1
then size * growth / 100.0 /128
—-use 100.0 to trigger the implicit data conversion
when 0
then growth /128.0
end
as Next_Growth_MB
from
sys.master_files
go